Integrity checks are a crucial part of a project finance model. They alert the user when any one or several conditions that need to be satisfied are indeed breached.
They can be broadly split into two categories:
- “integrity checks”
- “commercial signals”
Integrity checks – critical tests
Integrity checks must be passed at all times in all scenarios without exception. If anyone of them fails then there is a definite error in the model. As a bare minimum, I would expect the following checks:
- The balance sheet balances
- The line totals of the annual cashflow equals the line totals of the quarterly
- Sources of funds = Uses of funds
- Assets are not over depreciated
- Debt facilities are not over repaid
Tolerance levels on Integrity checks can be defined by the user at very small levels which are useful in eliminating floating point differences.
Commercial checks – non-critical but very important
Commercial checks are useful for flagging to the user that the mechanics in the model are working but that there is something of a more commercial nature that should be brought to the user’s attention. For example:
- Negative cash balances
- Debt is not repaid by the end of the model in a cash sweep % CFADS situation
- DSRA/c dropping below an intended target
Commercial checks such as these might be controlled with more structured tolerances such as cash going below $1m or the DSRA/c being below target in more than 2 consecutive periods.
Bringing it all together
It is no good having the checks built into the model if they are not consolidated into one area where the overall results can be clearly seen in one place. So with integrity and commercial checks built into a model the next steps are to consider building in a simple alert into maybe the top of each worksheet so that the adverse impact of a change can be immediately observed when a change is made to the model.
Advanced topics
For those of you that are happy with all of this and appreciate the power of data tables when combined with scenarios, consider having a master check for both integrity and signal checks and bringing both of those into each scenario as an output.
This approach means that the result of a change can be observed throughout all scenarios and not just the case at hand. The table below is a 1D data table using Scenario number as the “Column Input” variable. The final integrity and signals line is the sum of the rows above. This means that if the user makes a change that doesn’t affect the current case but causes a problem in another then the user is alerted without needing to run it to find out! I call this a crystal ball!
1D data table using Scenario number as the “Column Input” variable