Search Not Just Numbers

Tuesday 18 February 2014

Excel Tip: Are you responsible for producing Management Accounts or Monthly Reports?

I know that many of you, like me, are accountants and a large number of you, no doubt, are responsible for producing monthly management accounts. I'm sure that many of you that aren't accountants, as Excel users, are responsible for producing some kind of regular monthly, weekly or daily report.

This week's post is more of a plea, to take a moment out of your busy schedule to stop and have a think about how you go about this regular reporting.

How long do you spend on this each month/week/day?

How long do you think you should be spending?

Let me give you some food for thought.

Is the data for your reports in the same place each month?

Are the reports you're required to produce in the same format every month/week/day?

It is very rare that the answer to either of these questions is no. For example, if we are talking about management accounts, the data is held in the accounting system in exactly the same format as it was last month - and the management accounts are produced in the same format as last month, usually in an Excel template.

Given Excel's ability to manipulate data - and perform the same manipulations time and time again, why would the process of producing these reports take more than a few seconds?

Simply create an Excel template that converts the raw data into the final report, and just paste or import the new raw data in each month!

Whichever way you go about it, I urge you to stop and think. Logically, it should only take a few seconds. Any longer means that you are manually doing things every month/week/day that Excel could be doing for you.

If you need any help, I have covered this in more detail previously.

My very first post on this blog (back in September 2008!) was on this subject and gives a brief overview of how you can go about this for a set of management accounts:

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

and more recently (November 2013) I wrote a far more detailed explanation of the process in a publication published by the IT Faculty of the Institute of Chartered Accountants in England & Wales (ICAEW) entitled:

Automating management accounts production in Excel: a simple approach

(This is free to members of the IT Faculty or the ICAEW's Excel community or £25 to purchase for everyone else, from the ICAEW at the link above).

If you want, I can even set it up for you as this is a job that I have done for many clients over the years.

Whatever you do, I urge you not to just go back to a time-taking and unnecessary monthly/weekly/daily process.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

2 comments:

  1. Good point. I have set up my spreadsheet reports so that -as you say- all I need to do is update the data behind them. This really only takes a few seconds to update the data.

    There is also a process to check the report balances back to the ledger, this again really only takes a minute or two at the most.

    Job done you would think. But there are 2 things that bug me about this process.

    1. Why should I have to use excel at all? Why cant my reports be automatically generated by the accounting system and emailed directly to the people who need them. Better still, why can't the people who need them, retrieve these reports themselves if they are so important? The minute you get human intervention in the reporting process, you open it up to errors.
    Accounting systems are notoriously bad at reporting, and management accountants across the globe now earn their living making sense of financial performance via excel (and other tools) because the accounting systems that hold this information ate so bad at reporting it.

    2. Most management reports, whether direct from the system or through excel manipulation require the management accountants to interpret them. Most managers don't understand what the numbers are saying. What burns my time with the reporting process these days is finding out and communicating what the numbers mean and what the underlying risk to the business is behind them.

    Sure, excel can be a very efficient way of providing reports, but we need to trade this off against the questions of:

    Why can't the system do this instead of an error-prone human being, and

    Is the audience capable of understanding the underlying message in the numbers.

    To me the second question is where a good management accountant should be focused.

    Unfortunately most of is seem preoccupied with the unnecessary process of producing error-riddled spreadsheets that few managers really understand.

    ReplyDelete
    Replies
    1. Ivor, very good points. Very rarely is the reporting in a Accounting System as flexible as we require Excel to be.

      As Excel is the tool of choice for the output, better that the process is automated to both eliminate human error and to free up the accountant's time for exactly the kind interpretation and analysis you describe.

      Delete