TRIANGULATING ON THE ANSWER
The single most effective technique for increasing the reliability of spreadsheets is to calculate key results more than once, using intentionally different methods. If the separate derivations give the same answers, one can have high confidence (but not certainty) that the results are dependable.
This approach can be taken within a spreadsheet...
•in a financial model that generates a balance sheet, it is a powerful check to derive the assets and liabilities separately, and to check that the resulting statement balances as it is meant to.
•the same is true for other financial presentations, such as a statement of sources and uses of funds. To calculate one of the elements as the balancing figure in the table is to squander a valuable opportunity for cross-checking one's work.
...or it can be done between spreadsheets
•One way to check a spreadsheet is to build a second version of all or part of it and check whether the two calculations give the same result. Another way is to key the assumptions into a template model that is thought to be correct, and see whether the difference between the answers is small enough not to matter very much.
•Using either of these methods, it will frequently be the case that the two calculations do not give the same answer and the analyst will wish to work out why. Is the fault in the model being tested, or the model against which it is being compared? Or is there perhaps something wrong with both of them?
EXAMPLE
In the simple spreadsheet below, the analyst has attempted to follow this precept. He has derived totals for each row, and for each column, and then derived grand totals from each of them. But the yellow cells show that these row grand total and the column grand total don't match.
Why don't the two totals match? OAK can help explain why not.
First, load the example spreadsheet.
Once the example spreadsheet is loaded into Excel:
1.Click on one of the yellow cells.
2.Hold the CTRL key down and click on the other of the yellow cells. (Holding the CTRL key down causes the second cell to be included in the selection without deselecting the first one, making a multi-area range.)
3.Now use OAK's Analyze Discrepancies command.
4.OAK will present a dialog. Just accept the default settings for now, by clicking on OK or pressing Enter.
OAK will generate a new worksheet.
NARRATIVE
This analysis sets out
•near the top: the 94 and the 82, the values in the two cells that we selected before invoking the Analyse discrepancies command.
•between the 94 and the 82: the discrepancy between the two values. We want this value to be zero, but here it is -12.
•below the 94, the hierarchy of precedent formulas and values for that result, fanning out leftwards; in column B, are the four row totals; and below and to the left of each of those, in column A, are the values from the table that make up each of those.
•below the 82, the hierarchy of precedent formulas and values for that result, fanning out rightwards; in column F, are the four column totals; and below and to the right of each of those, in column G, are the values from the table that make up each of those.
TIP: These cells don't just happen to have the same values as the ones in the first spreadsheet. They contain simple formulas that mean they are linked to the originals cells. You can follow any of the links back by selecting the relevant cell and pressing CTRL+[ (left square bracket). Then when you have examined the source cell, you can press F5, then Enter, to return to the analysis spreadsheet.
The number at the left extremity of the analysis (column A) and at the right extremity (column G) are all drawn from the body of the table, but have been arrived at by different methods. OAK has matched the values on the left and on the right by adding distinguishing colors, and by linking the cells it has matched with hyperlinks.
TIP: The cells in these analyses have two kinds of links: hyperlinks, which can be followed by the mouse and which associate matching items on the left and right side of the discrepancy analysis, and formula links, which can be followed using CTRL+[ (left square bracket) and are linked back to the spreadsheet being analysed.
The second number down in the leftmost column, a 12, has no coloring and no hyperlinks, because OAK has been unable to match it to any item in the rightmost column. That is a good indication that it may be the cause of the mismatch between the two numbers being examined. The fact that it coincides exactly with the discrepancy at the top of the analysis reinforces this suspicion.
Select the cell containing the 12 and press Ctrl+[ to follow the link formula back to its source in the spreadsheet under test. Now press Ctrl+] (right square bracket) to see what dependents that cell has. You will see it has just one, in the row totals, but none in the column totals. Examination of the second row total, which displays 22, shows that it omits the first row from total. In fact, Excel hinted this all along, by warning with a green triangle in the upper corner of the cell that it was different from its neighbors.
See also