Expanding SUMs
When reconstructing , OAK replaces references to cells with the formulas in those cells, back to a number of levels of precedent specified by the user. But it doesn't pursue the process where it encounters functions that aggregate (derive a single value from) large ranges.
An exception is made for SUM, since this is the most common function used in spreadsheets.
OAK offers the option to expand this function. By expand, we mean replace the notation =SUM(A1:A3) by =A1+A2+A3. Once so expanded, the individual elements can have their precedents followed back an arbitrary number of levels.
A limit is placed on this action so that the resulting formula is not infeasibly large. It would be inappropriate to try to expand =SUM(A1:Z1000), because it would list 26,000 cell addresses, far more than can be fitted in a formula.
Confining reconstruction to SUMs
A further special treatment for addition is that OAK can be instructed to perform precedent substitution only to the extent that the result is a formula that contains +, - and SUM. This is specified by selecting "Confine to additions" on the dialog that is presented when OAK's Formula | Reconstruct command is invoked.
This action has a number of uses in extracting from financial statements, which have many subtotals provided for presentational reasons only, a report of the underlying detail devoid of those subtotals.
Original spreadsheet |
Reconstruction of Increase in retained earnings, confined to additions |
||
Revenue Cost of sales Gross margins Cash operating costs EBITDA Depreciation PBIT Interest PBT Tax charge PAT Dividends Increase in retained earnings
|
a b c =a-b d e =c-d f g = e-f h i = g-h j k =i-j l m =k-i
|
Revenue Cost of sales
Cash operating costs
Depreciation
Interest
Tax charge
Dividends Increase in retained earnings |
a b
d
f
h
i
l m=a-b-c-d-f-g-h-i |
It is also useful for turning cash flow statements that start with EBITDA or some other measure of earnings, into versions that start with Revenue.
If the calculation selected for reconstruction is not itself composed of + or - operators or the SUM function, the result is a rather useless report linking back to the calculation itself.