Authors
David Colver
Abstract
A common application of spreadsheets is the development of models that deliver projections of the future financial statements of companies established to pursue ventures that are subject to project financing.
A survey of 11 such spreadsheets prepared by a range of organisations shows that the amount of self-testing included in such models ranges between one formula of testing for each three formulae of calculation, down to essentially no self-testing at all.
Sample
The tests we use fall into the following broad groups:
- Balance sheet balances. The most basic test is that the balance sheet balances.
- Financial statements add up. Compare every subtotal on every output sheet with the sum of its elements.
- Financial statements have expected signs. This is effective at spotting that loans have been over-repaid, or assets over-depreciated; the sign of the relevant balance sheet entry reverses.
- Sources match uses. Any statement of source and uses of funds shows the quantities in balance.
- Identities hold true. For example, capital costs and depreciation can be reconciled over the life of the project.
- Balance sheet clears out. A project that is subject to a finite life should show a balance sheet that is full of zeroes at the end.
- Reconcile cash flow. The bottom of the cash cascade gives the same net cash figure as the cash flow.
- Ratio inclusion analysis. Detect acts of omission in ratios.
- Tax reconciliation. A test, based on inclusion analysis, that proves the model's tax calculation.
- Yield analysis. Interest charged implicit in the aggregate cash flows can be reconciled to the specified interest rate assumption.
- Physical identities. For example, if a chemical plant uses as the feedstock for one part of the facility a chemical that is the output of another section, the two quantities should be tested for equality.
- Complete solution. Any macros used to iterate to a solution have converged.
- Inputs make sense. Examples are that dates fall within the model's timeline; that enough financing is provided to meet the costs of completing the project.
- Outputs meet participants' requirements. Loans are repaid on time, financial ratios exceed the thresholds set by stakeholders.
- Other: The particular details of a transaction may make it appropriate to have other tests not listed above.
Publication
2008, EuSpRIG
