See-Through Modelling. Dominic Robertson
In outline, the methodology involves the following steps:
familiarisation – discussions with modellers and preparation of a block diagram documentation of the Model together with documentation describing its operation
low level review, including:
checking that SUM/total formulae appear valid
checking that all ranges in formulae, especially lookup functions (e.g. VLOOKUP, INDEX), appear valid
checking formulae exceptions from a map of the spreadsheet formulae (e.g. a unique formula embedded in the middle of a row of copied formulae)
following through all unique formulae to test for logical errors
checking formulae for consistent use of correct units (e.g. currency, tonnes, cubic metres, etc.)
checking that all lookup formulae seek an exact match (in Excel: the fourth parameter is set to FALSE), or if not that the range it references is sorted
checking that references in IF formulae appear valid
checking that all range names appear to cover the appropriate cell ranges
checking any ROUND functions to ensure that material accuracy is not lost
reviewing any #REF/#ERR, etc., cells to establish the cause
checking any array formulae
checking macro/program code
issue a list of queries and errors
it will be the modeller’s responsibility to correct any errors identified within the Model
once the errors have been corrected a re-review (maximum of four) will be performed to ensure that these changes have been made satisfactorily
after the Base Case Model has been reviewed sensitivities will be tested.
In order that the review is completed as effectively as possible access to someone who is familiar with the Model is needed at all times.
Model auditor example reports arrangement
After completing the preliminary investigation, a list of queries and exceptions will be issued. Any disagreements or concerns regarding the appropriateness or significance of the findings may be discussed. Once changes have been made as a result of these discussions, a final check will be carried out to review that the changes made are satisfactory. After this final check, the final report will be issued report, which it is anticipated will substantially take the form of the draft pro-forma report attached.
Model auditor example error/query categories
Model auditors produce reports with categorised queries as shown in Figure 14.
Figure 14: Model review error categories
Model auditors are most often required to check the arithmetic accuracy of the formulae and they do this in one of two ways. Classically this was the cell-by-cell audit, but more recently the re-performance method has gained followers and increased credibility. Both methods aim to bring out the potential arithmetic errors in the model formulae, and the auditors compile a list of queries for the modeller to answer based upon their analysis.
Model auditor example test – the forecast balance sheet test
Model auditors also perform a simple but effective test to find accounts that may not be dealt with properly. The test is called the forecast balance sheet test and consists of using the first forecast balance sheet as the last actual balance sheet in the model to see if the overall results are the same.
Another way to understand the mechanics of this test is to think of the update process where a new last closing balance sheet of actuals is added to the model and the model then creates a new forecast based upon this update.
For this test a new set of actuals is not used, but instead the first forecast numbers from the model all the way from profit and loss statement, the cash flow and the balance sheet, as well as the tax loss balance and any other actuals required to run the new forecast, are used.
The expectation is that the financial statements of the ‘updated’ model will be precisely the same as those of the model prior to the ‘update’. If this is not the case then each difference must be examined, understood and repaired.
In most cases the differences, and therefore errors, will relate to how the modeller is dealing with the last closing balance for any particular account, particularly with respect to accruals and creditors.
The FS_ref and FS_Diff sheets are essential for this test and subsequent analysis.
Class 5: Model build theory
To build a model the modeller should follow the well-trodden software development path of rapid application development. In this section I discuss the five categories of modelling work:
Specification
Design
Build
Test
Deliver.
1. Specification (S)
Specification means ‘the specification of the content and structure of the model’. The model content is made up of a logical machine into which data is input. This logical machine will use this input data to calculate defined outputs. The model structure is made up of organised content set in a specific timeframe.
The first step in building a new model is to define the timeframe of the model. By this I mean start dates, end dates, reporting dates and periodicity. The second step is to define and specify the main outputs in the model; these main outputs are the financial statements of the legal entity or company being modelled.
After these two steps each line of the financial statements is used to further define the content of the model.
2. Design (D)
The design of a model is about the structure, organisation and choice of logic. So model design is also about the choice of formulae. For a project finance company in operations there is limited design left to do given the sheer quantity of projects out in the market. In other words, there is a clear template that can be used.
However, there will always be occasions when some further design is necessary to overcome particular difficulties. These might arise from projects with more complicated operations, such as waste water projects. Or they might arise from more complicated finances, with more tranches of junior debt, such as mezzanine finance. Essentially the model is a pragmatic expression of a series of contracts between various parties and the project company. So design also means the nitty-gritty logic that describes these contracts.
There is good design and bad design.
Good design:
achieves the objectives in an elegant and transparent solution
knows its limitations – it is not the intention to build a model that can withstand a 100-year storm
flags errors and requires further design later as required
employs clear organisation and structure
has no duplication or repetition
minimises logical counter flows
minimises the number