How Checksums can ease financial modelling woeby
Cell-based modelling is a root cause of Excel Hell. James Power offers a few tips on how to avoid some common sources of trouble.
Some of the unavoidable issues you are likely to encounter when using Excel for financial modelling include:
- Simple errors in formula construction, returning error values such as: #VALUE!; #REF!; #NAME?; #N/A; etc.
- Errors in formulas dependent on other feeder cells that only become apparent later on, usually in different tabs to the tab you are currently working on, but missed because you cannot see them or are not alerted to them.
- Changing the spreadsheet structure which frequently creates errors containing the notation #REF! which ripples through financial statement rollups, thus making them unreadable.
At the most basic level, even very simple checksums can help you maintain the integrity of every spreadsheet you construct. However, most people fail to plan this aspect, usually due to time-pressures, but also on account of lack of know-how or even pure laziness! But why not build every new spreadsheet from a template already containing a basic Checksum structure?
Here’s how to do it
Step 1 Create a page purely for checksums. It can look something like this:
Each worksheet in the workbook must have a checksum. We will deal with the formula for each checksum shortly, because first you need to create some named ranges in your workbook, which constitutes Step 2.
Step 2 For each sheet in your workbook (except the checksums sheet because this will give you a circular reference), go to the arrow situated between the A and the 1 of each sheet and click on it so that it selects the whole sheet range. Then give this range a name similar to “INDEXSHT1”, just has been done in the screenshot below. You now have a named range which will detect any formula errors in the whole sheet, e.g.: #VALUE! ; #REF! ; #NAME? ; #N/A; etc.
Step 3:- Now for the checksum formula. Following our example, in Cell B10 on the Checksums page write the formula “=IF(ISERROR(SUM(INDEXSHT1)),FALSE,TRUE)” which will tell Excel to search for any error in the whole of Sheet 1. For Cells B11 and B12, obviously you will need to substitute the named range INDEXSHT1 for the corresponding ranges in the other sheets (e.g. INDEXSHT2, INDEXSHT2, etc.).
Step 4: Then add a summary checksum for all the individual sheet checksums (Cell B6 in the example below). This is important as we will see later. Do this by selecting all the cells with the sheet checksum formulas (B10:B12 in the example) and giving them a named range such as “SUMMARYCHECK”. We also give a name to Cell B6 (I have called it “SUMMARY”). Now add the formula to Cell B6: “=IF(COUNTIF(SUMMARYCHECK,FALSE),FALSE,TRUE)”.
So now, when you get any error in the relevant sheets, you will see the checksum turn from TRUE to FALSE and from green to red as can be seen in the above examples.
Now add some simple conditional formats to the checksum cells (green for TRUE, red for FALSE) to help make them more visibly identifiable. The next screenshot shows you how to do this.
You have now practically completed your exercise in creating your Checksums page.
Very simple, no?
But how do we get alerted to errors in other sheets that are due to formulas dependent on other feeder cells in different tabs (Point 2 of the issues mentioned at the beginning of this blog)?
Well, again this is a very simple conditional formatting exercise. In the same way that we conditionally formatted the checksum cells containing our checksum formulas we do the something similar for all the sheets in the workbook.
We select Cell A1 in each of the worksheets in the workbook and we conditionally format it to turn red if there are errors in any of the sheets. You’ve probably already guessed the formula, which involves Cell B6 (which we have already named “SUMMARY”) and which is a sum of all the checksums in our range “SUMMARYCHECK”. But here is the formula anyway: “=IF(SUMMARY=FALSE,TRUE,FALSE)”.
The effect is that we now get a very visible “red flag” in Cell A1 on each sheet if there happens to be an error in any part of the workbook, irrespective of what sheet it may be located on.
These are checksums in their simplest format. You can use them at a more advanced level by creating multiple checksums for a single sheet, perhaps referencing various important ranges rather than whole sheet ranges. This will help to pinpoint errors much more quickly and effectively. Furthermore, you can include any kind of formula such as those to identify mistakes or to aid reconciliations, e.g. “=IF(SUM(RANGE1)<>SUM(RANGE2),FALSE,TRUE)”.
I personally use a pre-designed template for every spreadsheet I construct to combat the laziness or time-pressured requirements that normally stop people from implementing checksums as a rule.
The template I use is similar to the one used in this example, but has the added benefit of a macro linked to a button on the checksums page. When clicked, the macro adds a sheet to the book and automatically carries out the steps discussed above (i.e. names the range for the new sheet range, inserts a new checksum for that page in the range B10:B12 or “SUMMARYCHECK”, and conditionally formats Cell A1 of the new sheet. Adding a new sheet using my template with checksums pre-incorporated actually now takes me less than a second. So no more excuses!
For further help or to request a free copy of my Checksums Template (with macros) for your own use (with no restriction on distribution to friends or colleagues) please contact me via email at james[AT]FD4Cast.com.
James power is the principal and development director of FD4CAST.