OAK's Analyse Discrepancies command generates a new workbook containing a comparison of the derivation of two selected cells.
OAK seeks to highlight values that are common to the left and right halves of the report, and so contribute to each of the two cells being compared. It identifies like cells by color, and hyperlinks them together.
Why you might want to use it
Operis believes strongly that the most powerful way to verify a spreadsheet calculation is to demonstrate that the same answers can be derived using a completely different method. Developing a parallel reconstruction of key calculations is a central part of its approach to reviewing or auditing spreadsheet financial models.
It is not uncommon for a parallel reconstruction to give an answer that is different from the original model. If this did not happen at least occasionally, there would be no point in spending the time developing the reconstruction. In such cases, the question is: which model is correct? Is the model under test defective, or is our own reconstruction wrong? Often, both are wrong, which is even more time consuming to unravel.
OAK aims to accelerate the identification of the causes of discrepancies such as this. It generates a new workbook, with the two cells being compared in the centre, along with the difference between the two values. In the column to the left from the first cell being compared, and right from the second cell, are links to the cells that those formulas mention. Working left and right, there are further links to those cells' precedents. In this way the report presents a fan-out of the precedent hierarchy of each of the two cells.
How to use it
| 1 | Select two cells. (If they are not next to each other, click on one, then hold Ctrl down and click on the other to selecta multi-area range.) |
| 2 | Invoke the OAK4 | Formula | Analyze Discrepancy command. |
| 3 | OAK will present a dialog. |
| 4 | Click OK to generate a report. |
Most of the controls on the dialog are devoted to preventing the fan-out from becoming so large that the analysis takes to long to prepare. They provide for
| • | limiting the number of levels of formula precedent that OAK inspects: the maximum depth field at the top of the dialog |
| • | instructing OAK not to follow paths that are not presently active: the prune inactive paths option in the center of the dialog. This is particularly helpful for making sense of formulas that use lookup functions such as VLOOKUP and HLOOKUP, or use the (dangerous, in Operis's opinion) OFFSET or INDIRECT |
| • | not looking at the precedents of ranges that have very many cells, such as =SUM(A1:Z1000), which has 26,000 of them: the maximum precedent cells field in the center of the dialog. |
OAK considers references common to the left and right halves of the report, and so contributing to each of the two cells being compared, only if they originate from exactly the same cell on a worksheet. The options at the bottom of the dialog instruct OAK to apply the less stringent test of considering values to be equal if they are close in value, even if they are taken from different cells.
Wrinkles
Further detail of the ideas behind discrepancy analysis are to be found in the section of the help that discusses OAK Concepts.
When the command is invoked, OAK will complain if the selection is not suitable for discrepancy analysis, that is, it does not consist of two cells.
If the resulting discrepancy analysis has very few cells on it, perhaps as few as the original two cells and the discrepancy between them only, check whether the option is "Suppress display of all children with nodes without matches" turned on. If it is, try running the discrepancy analysis again with it turned off.
The discrepancy analysis has a number of other uses.
| 1 | Rather than comparing a calculation with a reconstruction of that calculation, it can be valuable to compare two neighboring cells in the same calculation, for example, Operating costs in 2011 and 2012. The fan-outs show which items are common to the two calculations and which are the ones that make them different. |
| 2 | Some models operate at a fine level of granularity in the start-up or construction phase, switching to a coarser resolution when operations have got underway; perhaps monthly yielding to semiannual. A discrepancy analysis can compare a figure picked from the monthly section with an equivalent in the semiannual section, such as Operating costs in 2010 and 2020, to see whether their derivations are consistent. |
| 3 | The fan-out can be a useful display when trying to understand what is behind a single cell. To generate one, click on the cell, and Ctrl-Click on a blank cell. That will satisify OAK's requirement that the analysis should compare two cells, but save half the time by only investigating a single value. |
What you could do if you didn't have OAK
Before OAK provided them, Operis analysts frequently built discrepancy analyses by hand. It's possible, but it takes a long time.
Sometimes the source of discrepancies can be identified by zeroing out selected inputs until the discrepancy disappears.
Excel has more different facilities than many people realise for investigating a cell's precedents. Of these, the Tools | Formula auditing | Evaluate formula command is the closest to discrepancy analysis in presentation.