Uses for reconstructions

understanding calculations

The most common use for the OAK Review | Formula | Reconstruct command is to understand quickly how a spreadsheet arrives at a calculation, and to extract that information quickly free from the distraction of many other calculations that are surrounding one of interest in large spreadsheet.  It performs a function similar to, but more powerful than, Excel's built-in Formulas | Formula auditing | Evaluate formula command.

Parallel reconstructions

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.

OAK's Formula | Reconstruct command can certainly help with this.  It generates a worksheet that is suitable as a framework for a parallel reconstruction, and it can give a high-level view of what the calculation is doing, and what inputs are being used.

However, it is important to resist the temptation to use the OAK output as the parallel reconstruction.  Parallel reconstructions are only powerful if they are orthogonal, that is, go about the calculation in an intentionally different fashion.  Reconstructer reports from OAK are merely mechanized restatements of the original calculation, and restate the same logic.  It is for the spreadsheet reviewer to use the OAK report as a useful starting point for the reconstruction, and to adapt so that it is indeed different in approach from the original calculation.

Assessing spreadsheet complexity

OAK includes a command for summarising workbooks, since this is a standard offering in products in the same general class as OAK.  However, Operis has kept careful records of the time taken to complete hundreds of model audit reviews, and finds that it has very little to do with the number of formulas in a spreadsheet, or how complex it is.  The R-squared between the number of man hours consumed in an assignment and the number of distinct formulas in a spreadsheet was observed to be less than 0.2.

In working out how to staff, schedule and price a model review assignment, Operis finds OAK reconstructions can be more illuminating.  A few moments having OAK deliver a reconstruction of the key revenue and cost calculations in a spreadsheet can quickly give an impression of whether the reconstruction reports consist of hundreds or even thousands of lines of material, or merely a handful of them.  It turns out that an experienced reviewer can use these to reach a more reliable estimate of the effort needed to prove a spreadsheet than can be gained from the spreadsheet statistics.