Share this content
Tags:

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

11th Sep 2008
Share this content
Kashflow logo

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

Tags:

Replies (4)

Please login or register to join the discussion.

avatar
By AnonymousUser
18th Sep 2008 11:02

Templates vs Bespoke solutions
David Toohey makes a good point about templates and these can have a very important role to play, saving accoutants a great deal of time and effort.

Often templates are not used though, where the user does not know how to populate the template automatically, or because they feel that their reporting requirement does not fit a standard template.

In my article (and my workshop) I have tried to address these issues by providing an approach to populating any template, or existing (manually completed) Excel report, from any raw data.

Glen John Feechan BA Hon. ACA
Not Just Numbers Ezine
Workshop - Advanced Management Reporting in Microsoft Excel

Thanks (0)
By David2e
18th Sep 2008 07:58

Use time more efficiently, and more profitably!
Glen touches on very good points, of which are the main core concept of our own business.

Excel Templates can without-a-doubt be developed to an extent where it can take seconds to produce reports, depending on the information to display.

Our Financial Management Reporting System is an excellent example of this, developed through use in practice to provide flexible reports that are easy to setup for almost any client using any software.

Why are accountants spending valuable time constantly messing with formula, trying to balance figures in Excel, when as Glen says the process can be automated just as we have done?

Our members save time by letting us do this work, so they can crack on providing better services to more clients.

David Toohey
The Accountants Circle

Thanks (0)
avatar
By AnonymousUser
15th Sep 2008 13:54

Accounting Spreadsheets Automate Trial Balance
While it may be valuable for medium and larger companies to use excel spreadsheets to produce a trial balance self employed and smaller limited companies have a unique advantage these days.

The DIY Accounting software packages for small limited companies are written on excel accounting spreadsheets which automatically produce a trial balance enabling it to be available in real time. The trial balance produced also has a audit check row to identify the posting source of any misbalances.

www.diyaccounting.co.uk

Thanks (0)
avatar
By AnonymousUser
12th Sep 2008 18:38

CIS
I'm writing regarding a small general builder for whom I submitted a tax return in 2006/07.Fom time to time the builder used the services of plumbers and electricians etc.The Inland Revenue have picked up on this and are demanding huge penalties(£3000) for my client's failure to submit a CIS36 Return.
Is there any remedy against this act ion being taken by them?
Mr Beard

Thanks (0)