How to design better Excel management reporting systems

Introduction

One of most common uses for Microsoft Excel is to produce effective management reporting.  So I thought I would write a blog explaining how key features of Excel that can improve the way that this information can be generated.

Inputs

That old saying “Garbage In – Garbage out” seems highly relevant  for excel 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 – and there may be more than one such external system 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 create  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 no longer works – what is the backup plan ?
  • What about if the data structure changes  e.g the addition of an extra code

However automation can save a considerable amount of time / re work . 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.

Calculations in Excel Management Reporting systems

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 VBA macros. Again there are a number of several relevant points that need to be considered:-

  • How much automation will be required ? Although I absolutely advocate it, I always implement automation in sensible chunks. Instead of having one big process, I break down the process into distinct units. This enables my clients to cross check outputs 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 overall picture.
  • Usability – It is often overlooked but the more user a friendly a system is, the better it will be implemented. 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 this is the most misunderstood aspect of  any Excel Management Reporting system. Everybody understands what it is and the need for it, but very few appreciate the level and depth required. The most fundamental 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 4 minutes to run, then each test will take 4 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 bombing out
  • You need to build in error trapping – prevent code falling over in the first place

Outputs for Excel Management Reporting systems

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 an informal chat on how we can help you then please get in touch on 0845 163 0149.

/0 Comments/by

Ten top tips of spreadsheet design

I have been building spreadsheets, databases and systems for over 20 years. So I thought it appropriate to come up with a list of Top 10 spreadsheet design tips:-

1. Is a spreadsheet really the best solution ? It actually flabbergasts me how many people start using spreadsheets when a proper database is actually more appropriate. If you have multiple spreadsheets stacked upon each other, then it is a strong indication that a proper database solution is required rather a spreadsheet.

However it can be a good idea to start with a spreadsheet first to understand what you are trying to build. When you have grasped this you can think about having a proper database system. In essence the spreadsheet solution acts as a crude prototype for the real system. This was the approach that we adopted at the Department of Health for one of their systems.

2. At first – do nothing Regardless of whether or not it is a spreadsheet or database solution you require, don’t start any real work on the solution. Instead have some thinking time – preferably away from the desk. Use it to really understand what you are trying to acheive.

I remember one of my earliest bosses being gobsmacked that I was literally apparently doing nothing. Even when I explained my approach he was not convinced. Only later on when he saw what I came up with (which was I have to admit an ingenious solution and led to us winning a £ 10 m order) did he apologise, I saw him years later and after working with me, he changed the way he viewed people.

3. Think about Inputs, Calculations, Data and Outputs When building the spreadsheet have seperate tabs for each of these. Further, the accepted practice is to have inputs in the front tabs, then calculations, and finally the output tabs. Any data tabs should go towards the end of the workbook. It is actually good practice to colour these types of tab in seperate colours.

4. Colour Code Cells  There are essentially five types of cell in a spreadsheet:-

a) Those that require an input value

b) They need a value from a drop down

c) It contains a calculation

d) it is a total row / column

e) It is a label

So for each of these types of cell, use a different colour. Further, put the colour scheme at the top of each sheet so that it can be easily seen. Some people suggest that values that contain calculation cells should be protected – but this can be cumbersome to implement in a large spreadsheet.

I recommend that the row and total columns are made bold so that they stand out.

5. Work from left to right It may sound obvious but start at the left hand side of the worksheet and move towards the right. Further if your spreadsheet is something that shows time related values (i.e Jan, Feb, Mar etc) then use the same row and column for a particular month on each sheet. So if on one sheet Jan-15 is in cell C2, then it should be in the same cell on every sheet.

6. Use consistent formulae in any block of cells I have to say that of all the potential pitfalls / errors in a spreadsheet this one is the most dangerous. In each block of cells the formulae used need to be consistent. If not, it will be very hard to track down an error. If it is necessary to have different formulae break the block down into seperate blocks, each with consistent formulae. Then create a total block that sums across each of the individual blocks.

If there are only a couple of individidual blocks then one possible workaround is to use nested IF statements. However do not go overboard as these can be very memory consuming and slow down your spreadsheets performance.

7. Use Monthly, Quarterly and Yearly Totals for reconcilations If your data is time dependent then create monthly, quarterly and yearly blocks with corresponding totals. After this have totals for each of these ranges, and cross check the totals. Although it will significantly increase the size of your workbook, it will acts as a valuable audit for your spreadsheet. Indeed part of the initial thinking process for the workbook, should be devoted to working out what cross checks are needed in the model.

Further, having quarterly and yearly information acts a useful summary for management.

8. Use Graphs That old cliche that a picture is worth a thousand words, could be modified for a spreadsheet – a graph is worth a thousand cells. A good graph will be clearly labelled and use the correct format (i.e line . bar , pie) for the data being shown. Further any anomalies in the data ought to be picked up.

9. Have a summary sheet Almost all the spreadsheet models I develop are extremely complicated – some with over 50 worksheets. Hence a single summary sheet with a couple of graphs can be very useful.

10 Save your work – often I have been forever haunted when I lost a days work through my spreadsheet being too big and I was under presure to deliver. So save your work regularly , and use version control in case you need to go back to a previous version.

About the Author

Kapil Kapur is the Managing Director of Fingertips Intelligence who helps companies by managing their data more effectively. If you would like more information on how we can help your organisation  then get in touch on 0845 163 0149.

/0 Comments/by