independent evaluation and review of the Model is proposed to provide reasonable assurance that:
The Model does not contain material arithmetic or logical errors in either its base case or when used for two further agreed data sensitivity sets (the Construction Delay set, and the Availability Deductions set).
The principal accounting assumptions in the Model are materially consistent with UK Generally Accepted Accounting Principles (UK GAAP).
The tax charge liabilities and payments calculated in the Model are materially consistent with the assumptions made regarding certain tax matters, as stated on pages 11 and 12 of the Data Book.
There are no material inconsistencies between the assumptions as set out in a document prepared by <office> and provided to <model auditors> (“the Data Book”) and the Model.
The review specifically excludes examination of, or comment on, any aspect of the following:
appropriateness, accuracy or adequacy of any financial, contractual or other structures and arrangements.
input data and assumptions used other than as set out above.
achievability or appropriateness of outputs.
appropriateness of the assumptions made in relation to certain tax matters on pages 11 and 12 of the Data Book and their compliance with UK legislation and practice.
Model auditor example approach arrangement
The assignment will be carried out by following Model Review Methodology, which has been used previously on a wide variety of projects.
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:
