Spreadsheets in accounting – less is more
)
Posted: Fri 15th Nov 2024
Spreadsheets in accounting – this is mainly for bookkeepers and accountants. Still, CEOs and MDs might recognise some of the roadblocks and I suspect the points raised might apply equally well to sales, marketing operations and HR.
To be clear, I come to praise spreadsheets not to bury them, but you may not have the balance right. So let's begin where bookkeepers and accountants should always begin – accounting software.
Is your accounting software any good and are you making the best use?
Your accounting software should be capable of generating the overwhelming majority of the reports your business needs.
Produce those reports, share them and ensure they are understood and used to improve business performance.
Listen to feedback – sunlight is the best disinfectant – and if improvements to the reports can be made, then make them.
But choose the right accounting software. Some popular packages are simply rubbish at producing reports so you might need to change software.
Things to keep in mind:
Get proper training on your accounting software
Produce your sales invoices in your accounting software or CRM – but not in a spreadsheet
Work backwards from the reports. You need to make sure your data entry of transactions is going to feed nicely into your reports
Build a chart of accounts that is right for your business and build it to last
Make sure your bookkeeping is complete, accurate and up-to-date
From time to time, you may need to prepare reports in spreadsheets
Accounting software isn't infinitely flexible and some reports will need to be created in a spreadsheet where tools, such as pivot tables and lookup formulas can be brought into play.
Also, you may need to combine data from your finance system with data from other sources and other areas of your business.
In which case, make sure data that should be the same is the same!
For example, if you have customer numbers, make very sure that the sales and marketing teams have exactly the same customer numbers for each customer record as the finance team.
Pivot tables pivot around common data (there's a clue in the title).
If you get your data cleaning right, then you can generate very powerful and valuable insights by combining data from different data sources.
However, inconsistent data will hold back your business in a very significant way.
Take time to learn best practices for spreadsheets
There's a time and place for spreadsheets but learn how to use them well.
A good first principle is to assume that other people will be the audience or users of your spreadsheet and that they will either print it or save it as a PDF.
Straight away this means you have to build spreadsheets in a simple and well-structured way with a focus on clear outputs.
This is an article and not a spreadsheet tutorial, (you can find lots of them on YouTube) so I'm not going to go into depth on the tools and formulas you might utilise but here are my top 10 tips to get you started:
Be very clear what the purpose of the spreadsheet is
Create separate areas of the spreadsheet for inputs, calculations and outputs (the outputs might be your print ranges)
Have an empty row at the bottom of a column of numbers and put the total in the row below that (makes it easy to insert new rows into the column and ensure it will still add up correctly)
Use control formulas to prove that the result of one calculation that should agree with another calculation actually does agree ( i.e. if X43 should equal B9 then the control formula will be =X43 - B9 and the answer should be 0)
NEVER hide rows, columns or worksheets (you simply don't know the world of pain that hiding things can unleash)
Freeze rows and columns so they are always visible (make it easy for the user)
Format numbers with commas and an appropriate number of decimal places and display negative numbers in red and in brackets so they jump off the screen at the user
Ensure each separate sheet has a sensible title and page name
Use autosave or get into the habit of saving your work frequently
Put a page of notes at the back to explain what the spreadsheet is for and how it works ( I guarantee you won't make sense of your own notes the second time you read them)
There are many other hints, tips and online tutorials that will get you using spreadsheets better and don't overlook getting someone in to do some training across your business.
Spreadsheets in accounting (and in other disciplines)
To summarise:
choose good accounting software, get training and use it for the majority of your financial reports
use well-built spreadsheets to gather data and answer questions that your accounting software can't help you with
I think these ideas can be applied across your business. Think of sales management software, CRM systems, HR and other applications.
Are you getting everything you need from the main system or are you over-reliant on spreadsheets?
Relevant resources
You might also like…
