When reviewing a financial model it is important to understand the typical errors people make. Below are ideas on easy ways of finding the SUBTOTAL and AGGREGATE errors, in order of riskiness, when auditing a financial models.
It is useful to have a financial model auditing add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
Table of contents
Missing SUBTOTALS/ AGGREGATE in the input data
SUBTOTAL is a fairly common function which allows you to perform a calculation on a range of cells, and it will ignore any cell that also contains a SUBTOTAL function (so that you don’t double count subtotals). The risk is that one of the SUBTOTALS is replaced with, for example, a normal SUM. The error won’t be in the cell with the SUM function but rather in a subsequent cell which adds that cell where it should ignore it. AGGREGATE works in the same way, ignoring both AGGREGATE and SUBTOTAL functions.
As shown you can list all the SUBTOTALs and AGGREGATEs but in this case you need to look at them to see if the risk exists.
As shown below (1) which has the SUBTOTAL in it shows an answer of 9 even though it is obviously 6. The problem is that in (2) someone has overwritten the SUBTOTAL with a SUM function and now (1) does not know it must ignore it.
Treatment if hidden cells and errors in SUBTOTAL or AGGREGATE
Both SUBTOTAL and AGGREGATE have a way to ignore hidden cells, and in AGGREGATE’s case to also ignore errors. This means that if someone hides rows, the numbers will change or error messages will not be as obvious (error messages are useful sometimes in that they warn you of a problem).
For SUBTOTAL, if the first criteria is in the 100’s then hidden cells are ignored. So below, it just shows a 9 which means SUM. If it said 109 it would ignore hidden cells. Based on your understanding of the spreadsheet either could be a risk. Perhaps a user likes to hide rows to be able to see more of a spreadsheet but they don’t realise that it changes the numbers.
For AGGREGATE, the second criteria has a number of options. You need to see which was used. 3 means ignore hidden cells AND error messages. So below, even though cell E17 has a #NAME error (someone entered a function that Excel does not recognise e.g. SUMM), AGGREGATE still gets a total where perhaps it would be better to see that there is an error somewhere in the spreadsheet.
