How to use Excel to improve your Management reporting
One of most common uses for Excel is management reporting. So I thought I would spend some time explaining key features of Excel that can improve the way that this information can be generated.
That old adage “Garbage In – Garbage out” especially holds true for management reporting systems. In its simplest format, data can be typed in. However larger organisations need to pull in data from 3rd party data sources – often more than one of these may be involved e.g SAGE, an in house system or another commercial package.
Often it is possible to connect the data source via an ODBC connection or there will be an automated process to do this via a comprehensive vba macro. The things to consider when either of these are involved:-
- What happens if the connection or macro falls over – is there backup plan ?
- What about if the data structure changes in some way e.g the addition of an extra code
However automation can save a considerable amount of time. Through automation in this way I have saved clients several man months in manpower.
One approach that makes auditing considerably easier is to save the data sources in excel spreadsheets in a particular area on the network. Each tranche of data is stored in a seperate folder which is named with the relevant month.
Having gathered all the relevant data, the next stage is to process the input data into the outputs. In its simplest format, this can be through excel formulae but often it can be an automated process through macros. Again there are a number of several relevant points that need to be considered:
- How much automation will be undertaken ? Although I absolutely advocate it, I implement automation in sensible blocks. Instead of having one big process, I break down the process into distinct units. This enables my clients to cross check the data at key points in their management reporting process. A relevant example would be when the client consists of several companies – you would automate the month end of each company in the group first. This would be the end of one process at which point you would check the results before undertaking a second automation to produce the consolidated picture.
- Usability – It is often overlooked but the more user a friendly a system is, the better it will be. For example showing users the required filenames, and indicating that they have been found.
- Sense checking Any calculation process (irrespective of whether it is automated or not) needs to have cross checks to ensure that the correct outputs are being produced. One of the easiest ways of monitoring this can be through a special sheet set up for this purpose. This sheet could have all the required cross checks. Excel’s conditional formatting feature can be a valuable way of highlighting errors of this sort. Another useful tool for management reporting are Excel’s auditing tools – which can be used for tracking down errors in formulae. It is especially good for identifying circular references.
Workbook and Worksheet Design
The way the workbook and worksheets are set up (e.g colour coding to seperate out input and output cells) is fundamental to the success of an excel based management reporting solution. Hence it is worth spending some thinking time before building the solution.
Testing Without doubt the most misunderstood aspect of developing any system whether or not it is for management reporting or not. Everybody understands what it is and the need for it, but very few appreciate the level and depth of the testing required. The most important aspects are:-
- Testing needs to happen as part of developing the management reporting process – rather than at the end. If it is done at the end then there is a grave danger of you having missed something earlier which would have changed the way you would have done things.
- If a routine takes 3 minutes to run, then each test will take 3 minutes to run. So if you have to run the routine 200 times (not unheard of for a comprehensive month end) that is 600 minutes that needs to be set aside for testing.
- You need to give meaningful error messages – rather than code falling over
- You need to build in error trapping – prevent code falling over in the first place
Outputs Excel contains a wealth of tools to produce clear and meaningful outputs including graphs (pie charts, bar charts) and pivot tables. However the output needs to be useable – so it is worth having totals as formulae rather than hard coded values.
About the author
Kapil Kapur is the Managing Director of Fingertips Intelligence who helps their clients make better decisions by helping them manag their data more effectively. If you would like more information abuut how we can help you then please drop us a line on firstname.lastname@example.org