Creating Practical Excel Reports Part 2 : Import, Adjust and Report

Follow the Process!!

In Part 1 of this series, we looked at some objectives to try and achieve with any Excel spreadsheet plus some basic principles to help achieve them.

One of the key principles was to SEPARATE, specifically to separate your workbook into at least three main parts.  The parts were :

• Data Import; 
• Adjust; 
• Report;

In many workbooks, the Adjust (and in some cases the Data Import) is mixed up with the Report part - this is quite common when the base data you have (some form of excel output from your system usually) needs only a couple of adjustments and some formatting to get to the report you want the users to see.

Well?  Keanu has spoken!

It can also happen inadvertently - you start off with a simple report, but users request grouping changes, or a split of a particular item.  The fastest way to do that (because "it is just one little change") is to whack a new row or subtotal into the report sheet...or (possibly worse) you just hardcode the adjustment into the report cell.

Another common situation is with getting data in.  In most spreadsheets, importing data is generally done on a separate sheet (or at least in a separate part of the report) and there is some form of linking to transfer that data to the report (or maybe you type the data into the report part directly).  The linking is often cell to cell which means that changes to the import data format throws out the final report.

In addition, it is really common to have some key data which is directly entered into the report part (foreign exchange rates, dates, non financial information (such as tonnes to calculate cost per tonne) to calculate performance ratios etc.  Either of these will cause problems down the track.

Now, we all know that these situations are going to occur, so how does splitting the workbook up make it any easier?

The simple answer is that having dedicated parts of the workbook which all have defined tasks make it far easier to deal with the usual changes.  The reason it helps is because each part has a small number of changes that impact it, and generally the changes are easier to make (because there are fewer changes in the first place and making changes generally doesn't mess up anything else).

Here's a diagram to hopefully make it clearer.

I said "hopefully"

In this diagram the orange boxes represent the different parts of the workbook (note that you can definitely have more than 1 sheet in any part (I know you knew that, but I figured I'd say it anyway;) ) while the pink boxes contain the key things you do in each part.

The most important part is that the arrows are ONE WAY.  Data comes in, gets adjusted, then gets reported.  If you find in your spreadsheet that you are EVER going back to a prior step, or mixing up your tasks, well, that's a problem.

Let's take a short look at the key tasks undertaken in each part.  We'll take a more detailed look at each process part in future posts, but for now we'll just hit the high spots.

Data Import

Obviously, this is the most critical part (but often the simplest) because if you don't bring in all the necessary data to your report, well, that really is a waste of time isn't it!

The key things you are looking to do here are:

a)  Ensure all your data is accounted for.  Use formulas to ensure that key totals agree to the base report or have been reconciled to some other source document.

b)  Apply grouping or summarisation keys to EACH row of your imported data.  This will often require some form of lookup - so you'll need a translation table to be able to apply the correct summary key - don't do this step manually if you can avoid it.

c)  Separate the workbook links from the import data.  This means that you should have a calculation column which picks the necessary column from the import data.  Your Adjustment formulas then reference this calculation column and NOT the column in the base report.  Doing this really minimises the impact of a change to the incoming data format.

d)  Have references to describe where the incoming data is coming from (eg system report names, other workbook names etc).

e)  Lock your reported data.  This is controversial, because having direct links to your system can save a heap of time.  However, it is not cool when you get questions on the issued report, you open your spreadsheet and POW! - you have a different number in your report.  When I have a direct link I normally copy and paste the data from the direct link into the feeder data sheet.  That way, if the data changes I can easily see in the detail where the change was.

When your data changes after you issue the report :(

Adjust

This section is obviously hugely variable, but there are some key things you want to achieve here:

a)  You must end up with a table which contains EVERY number you need in your report (except for simple row/column totals).  This is your report linking table - essentially you should effectively have an unformatted copy of the report here, on a single sheet.

b)  Every Adjustment you make must be clear - in particular hard coded numbers MUST be clearly identified and commented so people know when they should be changed and where they come from.

Report

Well, this is all about formatting, and commentary.  No calculations (not even ratios), just simple row/column totals.

a)  Format for distribution.  This section is all about the report, so lay out tables, charts etc so they are easy for the user to read.   Set print ranges and titles!

b) NO Calculations.  (row/column) totals excepted.  None.  No, not that one either.

* * * * * 

Thanks for reading this instalment!  The next part will look in more detail at how data should be moved between the different process parts.

Leave me a comment if you think there is something missing!  Will this work for you?