Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

Using only Microsoft Excel and pretty much any accounting package on the market, I have rarely found a company that could not be set up to complete their management accounts preparation (from trial balance) in seconds. However, almost every company I come across is taking hours, if not days or weeks - every month - to do this job. In addition, when the process is automated, very often they discover errors in the accounts prepared under their previous laborious method. Below I will give some simple tips to achieve this in your business. The rewards are huge, as the focus switches from grinding out the numbers to understanding and interpreting them, thereby adding real value to the business.

Key to achieving this is understanding that once you can produce a trial balance from your accounting package, the rest of the preparation can (and should) then be automated. You have all the numbers – you are doing the same things with them every month. This is what spreadsheets are for – automating the analysis and presentation of numbers.

The instructions below are intended to provide someone with a good working knowledge of Excel with enough information to complete the setup, alternatively please contact me if you would like to discuss getting some help (it rarely involves more than two or three days consultancy to set it all up from scratch).

Importing the trial balance

First of all you will need to pull the trial balance into Excel. Having done this once it can be refreshed (with one click) every month and/or every time you make a change to the data in the accounts package as a result of errors spotted when you see the information in its final format. Every modern accounts package I am aware of has odbc links into the data. Excel can import data from the tables in the accounts package into Excel using Microsoft Query (this comes on the Excel disk and you may already have it installed – if not, you will be prompted to put the disk in when you try to “Import External Data”.

Cross-reference table

Once you have the trial balance in Excel you need to populate your management accounts with this data. I know many companies that have imported the trial balance and then tried to automate this step directly and got into difficulties when there are changes to the trial balance, such as new or deleted accounts codes. The key to doing this is to introduce a step in between that is a lookup table between your nominal ledger codes and the final management accounts. Give each line in your final management accounts layout a code and then copy your nominal ledger codes (from the imported trial balance) onto a separate worksheet in the same spreadsheet. You then need to put the management accounts code that you want the nominal ledger code to appear in next to each nominal ledger code in this list. You can then use a look-up formula to pull the correct management accounts code alongside each row in the trial balance.

Analysing the data

Once this step is complete, you can use a Pivot Table to analyse the trial balance by management accounts code rather than by nominal ledger code. This Pivot Table will contain all of the numbers that will go in your final accounts. If there are any new nominal ledger codes, there will be a line in the pivot table with a management accounts code of #NA, as the nominal ledger code will not be found in the lookup table and therefore the lookup formula will have returned this error for the lines containing the new codes. Simply double-click on the total of #NA to get a list of the new codes. These can then be pasted to the bottom of the cross-reference table and given a management accounts code. One simple click to refresh the Pivot Table and these codes are distributed into their rightful homes.

Presentation

If you already use Excel to present your management accounts then you can copy the existing layout into this spreadsheet (on a separate sheet). You will need to insert a column to the left of the management accounts to enter the management accounts code for each line. You can then use the GETPIVOTDATA formula to look up the financial value for each code and pull it into the accounts. Typically you can copy the same formula for each line. I also tend to insert a second column to the left of the accounts which I use to enter a 1 or –1 against each code. The result can then be multiplied by this column to allow you to use the same formula even when you want to reverse the sign (for sales and cost of sales for example).

Two clicks

Every month you can now complete the management accounts (from trial balance) in two clicks – one to refresh the data from the accounts package, one to refresh the pivot table. This also allows you to view the final layout very early in the process so that you can use this to review for accruals and prepayments, etc., make your changes and then refresh again.

I hope this has given you some ideas on how to free you and your team from crunching numbers to enable you to provide valuable financial insights to the rest of the board.

If you wish to discuss this further, please give me a call on 0845 6439693 or email [email protected]. To read more articles like this sign up to the Not Just Numbers ezine at www.notjustnumbers.co.uk.
Glen Feechan