Project Finance Modelling Top ten mistakes

Financial models in project finance are often plagued with a wide variety of problems. The vast majority of these can be attributed to a lack of simplicity, a failure to clearly segregate different elements of the model, and the absence of certain key information.

This article sets out to highlight the ten most important issues that prevent the development of an outstanding financial model.

1. Assumptions and calculations are intertwined

Not separating assumptions from calculations is a very common problem in financial models. A structured approach, clearly segregating the two, adds to the overall credibility with the banks. Assumptions should also be formatted with units in order to decrease the likelihood of user error.

2. Calculations are too complex and compact

No formula should be longer than half the length of the Excel formula bar. If it is, it should be split into several lines. It is also recommended to avoid the following functions unless there is no other way of solving a certain problem - ISERROR, VLOOKUP, HLOOKUP, INDIRECT, OFFSET, INDEX, MATCH.

3. Rows contain more than one unique formula

Having more than one unique formula per row makes updating a model much more difficult, as a user needs to check for unique formulas before copying a formula across a row. It also increases the cost of having the model independently reviewed ('audited'), as the review firm will need to raise all instances of unique formulas as issues.

4. Mix of actual data and forecasts

Actual data or hard-coded forecasts are often interspersed throughout the model. By adding hard-coded numbers into a worksheet, the dynamic nature of a financial model is disabled and the model cannot be efficiently used for anything other than static presentation of the base case.

5. No built-in scenarios/sensitivity analysis

One of the main reasons for building a financial model for a financial transaction is to quantify the main risks in the project. If this analysis is performed by a user of the model instead of the developer, then the risk of mistakes is significantly higher. All good models have built-in infrastructure for a scenario manager and sensitivity analysis of the main operating and financial drivers.

6. Complicated or incorrect financing ratios

The definition of financing ratios (DSCR, LLCR, PLCR, etc) can be complicated, which makes it even more important to ensure that the calculation of them is not. The core section of a project finance model is the ratio analysis, and this is the section where banks will have their main focus. By calculating the ratios in a clear and transparent way it is easier to detect mistakes, avoid confusion and to make the credit assessment of the transaction smoother.

7. Automatic re-sculpting of debt repayment

Borrowing base facilities (based on LLCR/PLCR/RLCR) that are modelled using automated re-sizing for downside scenarios, effectively masks the credit quality of the transaction, and is an area of common confusion. Circular models are not accepted in the project finance industry. Even though there are plenty of valid applications of the 'iterations' functionality in Excel and in other areas of quantitative finance, it should not be used in project finance or any other cashflow transaction model. The banks will not accept the model, and the cost for the model review will be much higher than it would need to be had the model not been circular.

8. No Executive Summary

All financial models should have a comprehensive Executive Summary, including operational and financial summaries, credit tables, scenario analyses, sweep repayments, error checks, key plots, debt ratios, etc. The Executive Summary should be set up to print nicely on one to three pages, and should provide a good understanding of the proposed transaction to bankers and senior management.

9. Lack of integrated financial statements

All financial models should contain monthly, quarterly and annual Cashflow, Profit and Loss, and Balance Sheet financial statements. Excluding the Balance Sheet on the basis that the cashflow is the driver of the deal, is not an excuse for its omission.

10. Complicated, undocumented VBA macros

The inclusion of complicated, recorded macros with unclear purposes in a model will greatly diminish the confidence in the analysis. There are valid uses of VBA in financial models, but these must be transparent, easy to use, and provide clearly documented code that has not just been recorded and left unedited.

In the process of trying to model complex transactions, project finance models often fall into the trap of stifling the simplicity of a model. While sophisticated transactions can require large financial models, the step-by-step breakdown of the model should be simple, well segregated, and clearly defined.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...