See-Through Modelling. Dominic Robertson
This section on testing theory is divided into seven sections.
1 Ongoing modeller tests, performed during the component build process
2 Ongoing financial statement tests, carried out while compiling the financial statements or reviewing them for errors
3 Tests prior to delivery
4 End user actual update and re-forecast testing
5 Three fundamental financial statements checks
6 End user acceptance tests, carried out by the end user after delivery
7 Auditor tests, carried out during a professional model review process.
1. Ongoing modeller tests
As each modelling and business building block and result is prepared the modeller needs to be satisfied that the logic is accurate and working to specification. There are various techniques for this and here is a list of these:
Use all inputs test – make sure that all inputs that have a value of zero are actually tested with a non-zero value.
Greatly simplify the inputs test – use simple rounded numbers as inputs and then seek the local results in expectation of simple results.
Zero the opening balance test – if the opening balance has a value try temporarily setting this to zero and then checking the results.
Zero the inputs test – temporarily set non-zero inputs to zero to test the results.
Value the opening balance test.
Check dependents test – press Alt + T + D to check the dependents of a cell to visually check where it is used.
Check precedents test – press Alt + T + T to check precedents of a cell to visually check where it came from.
Map the logic test – use mapping software (such as Excel Savvy as described in ‘Excel add-ins and dashboards’) to map the sheet or workbook.
Check the sums test – use a calculator to check that the sums are referencing all the rows above that they should be referencing.
Paint the result into financial statements test – add sets of results to the financial statements.
Colour code dependent test – go through each dependent of a cell and colour-code the dependent cells with a distinct colour in order to check that there is no repetition.
2. ongoing financial statements tests
Adding the step results to the financial statements is a big test in itself. If this fails then the modeller needs to perform other tests to find out where the error is. An example is the large number test.
Once the results have been painted into the financial statements the modeller expects to see a balanced set of statements. If this is not the case then some part of the double-entry has not been carried out or is not working correctly. By increasing the inputs of the painted results to very large numbers the modeller can better see where the double-entry has failed on the financial statements and then track and repair the error.
3. Tests prior to delivery
The modeller should also perform a series of checks for proper delivery. These are:
1 check the look-forward and look-backward formulae
2 check for white text
3 check for proper capitalisation
4 check that counter flows are appropriately marked
5 check there are no external links
6 check units for each line item
7 check all inputs have been compiled in the inputs sheet
8 check spelling
9 home cursor and set view at 100% for all sheets
10 save the model on the cover sheet.
4. End user actual update and re-forecast testing
Updating and re-forecasting an operating model consists of two updates:
1 update actuals inputs
2 update forecast inputs.
To update the actuals it is important that the actual financial statements that form the basis of this update demonstrate complete integrity. This means that the three fundamental checks of any set of financial statements hold true.
5. Three fundamental financial statements checks
The three fundamental checks of financial statements are:
Retained Profit Balance in the balance sheet is the same as the Retained Profit Balance shown in the Profit & Loss and as a function of the constituent parts of the Profit & Loss.
Retained Cash Balance in the balance sheet is the same as the Retained Cash Balance shown in the Cash Flow Waterfall and as a function of the constituent parts of the Cash Flow Waterfall.
Balance Sheet balances.
6. End user acceptance testing
There are various tests that the end user has to perform in order to properly acceptance test the model. The tests coincide with the various phases of delivery, of which the first is the shadow delivery. To progress to the update stage of the build process the user has to populate the model with ‘real’ update inputs.
7. Auditor tests
Model auditors carry out a variety of tests to make sure of the accuracy of the model. To be precise, the model auditors are normally tasked with carrying out tests with the following scope.
Model auditor example of scope arrangement
An 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