Creating Practical Excel Reports Part 3 : Data Flows in the Excel Report Process

Not your data flows.....hopefully!

In Part 2 we looked in more detail at the three parts of the report process.  If you haven't read Part 2, then it is worthwhile doing that now, because otherwise a lot of what follows here won't make sense.

To recap VERY briefly, one of the keys to creating a practical excel report is to separate the parts of the process.  So, you separate the DATA from the ADJUSTMENTS from the REPORT.  Each of these sections has a specific purpose.  The key question now is "How do you connect these sections?".



Let's quickly revisit the key parts of our workbook so far.

Three sections, one way data flows and a clear separation of the sections within the process.  That's great, and the benefits you get from separating the parts of the process are huge.  However, there is one little thing needed to get the benefits - you actually have to connect these sections together!

In other words, you need to have a mechanism to transfer your data between these sections.  As with all things, if you pick the right mechanism, life will be easy -  your workbook will be logical, transparent and hence reliable, plus easy to check and reconcile.

But, if you pick the wrong mechanism, well, you'll still get some benefits of separation, but you'll lose some clarity and make it harder to reconcile and check.  Also, changes to the workbook will be harder, because you'll spend time trying to trace through links to work out where changes need to be made.

How can you tell if you picked the "wrong" mechanism?  Well, you'll spend a lot of time trying to work out where links go to and from, plus you'll have that slightly worried feeling in your gut...

I'm sure it'll be ok...

Practical Principles for Good Data Flows

So, what can you do to avoid that feeling?  There are a few simple things that tend to make life easier:

1)  Summarise data, preferably on the move

The objective here is to produce a report(s) from the data that you've pulled in.  Chances are, the report is going to be more summarised than the detailed data you pulled in, so you'll need to summarise on the way through the process.

This summarisation could be either in the number of rows (eg grouping detailed trial balance accounts into more summarised versions) or columns (eg summarising monthly data into quarters or half year).  Whichever it is, do that summarisation as soon as possible to minimise the amount of data (and links!) you are moving.

So, if you pull in monthly data and you want quarterly, just calculate your quarters on the data input pages - don't bring across multiple unnecessary columns into the Adjust section.

If you can, summarise on the move.  By this I mean use SUMIF formulas to group up details on the way through.  This works with both row and column summarisation.

2)  One way flows only

Make sure that your data flows only go one way.  That is, from DATA to ADJUST to REPORT.  There is NEVER a need to send data back the other way.

This is what happens when you do it backwards

3)  No direct links

When you are moving data between sections, skip direct links (eg =Data!A15).  Every movement should be done using SUMIF, VLOOKUP, INDEX(Match..) etc.  Direct links WILL come back to bite you.

4)  Build in consistency

Throughout your workbook, there will be numerous things that need to be consistent.  Examples usually include the month end date, the categories for the report etc.  Knowing this, build in consistency where possible.  Have a table of valid categories, then wherever these are used ensure you turn on the Excel validation to make sure that only valid categories are used.

Try and eliminate manual classification if possible.  For example, instead of having a user manually assign report categories to each import row, create a translation table so you can do it automatically.  Keeping consistency and automating where possible will result in a huge increase in flexibility, plus the workbook will be easier to understand.


Controlling the Data Flow

Ok, now that we have the principles, what next?  Well, the main thing that comes out of the application of the principles is that we need to add some more "required" sheets to our workbook.

These are required because the principles essentially require the use of some form of lookup formula to move data (either vlookup, sumif, index(match) etc). In order to keep these working well you'll need to define the lookup terms and keep them consistent.  The easiest way is to define them once - and then use that definition as a validation table.

Also, you'll want a translation table to ensure that the detailed import data is correctly mapped to the lookup terms (ie the report categories).  Trying to do this manually or with fixed, hardcoded data will cause issues.  So, a translation table is the best bet, because then the format of the import is less important.

Taking those two things together means that as a minimum your workbooks should contain pretty much the following...

As you can see, the Parameter sheet is really important.  Anything that multiple sheets rely on (think headings, factors, variables, etc) should be here.  On the Parameter sheet I tend to use named cells a lot - some people don't like named ranges but I find that they can really help when building a workbook because you can see from the name what it is you are referencing.

* * * * *

At the conclusion of the third part in this series, we've now got the basic sheets in our workbook sorted out.  In the next part we'll start looking at specific tips and tricks for each of the process sections.

What did you think?  Have I missed anything?