Spreadsheets in accounting – use fewer of them but learn how to build them better

Spreadsheets in accounting – this is mainly for bookkeepers and accountants but CEOs and MDs might recognise some of the road blocks 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 of yours?

Your accounting software should be capable of generating the overwhelming majority of the reports your business needs.

Produce those reports, share them, make sure 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 because some of the popular packages are simply rubbish at producing reports. You might need to change software.

Get proper training on your accounting software – particularly around reporting! (we can help you with QuickBooks, or Xero)

Produce your sales invoices in your accounting software or CRM software – 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 – see related links below.

Make sure your bookkeeping is complete, accurate and up-to-date.

=> The opposite of “Garbage In – Garbage Out”

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 from 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.

But inconsistent data will hold back your business in a very significant way.

=> Finance transformation

Take time to learn best practice 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 a blog 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 ten tips to get you started:

1. Be very clear what the purpose of the spreadsheet is
2. Create separate areas of the spreadsheet for inputs, calculations and outputs (the outputs might be your print ranges)
3. 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)
4. Use control formulas to prove that the result of one calculation that should agree to 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)
5. NEVER hide rows, columns or worksheets (you simply don’t know the world of pain that hiding things can unleash)
6. Freeze rows and columns so they are always visible (make it easy for the user to use)
7. 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
8. Ensure each separate sheet has a sensible title and page name
9. Use autosave or get into the habit of saving your work frequently
10. 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?

We do a lot of work with businesses to help them improve the quality of financial information and this is one area we frequently take a look at.

Let’s have a free-of-charge chat about your business – call Michael Austin on 020 7125 0270 or email info@bluedotconsulting.co.uk

Michael – @bluedotmichael

Related links:

Proof of Profit

Build a useful Xero chart of accounts

Why don’t businesses make better use of accounting software?

^{_{© Blue Dot Consulting Limited}}

Chartered Accountants – Bedford House, Fulham Green, London, SW6 3JW