How to Simplify a Project Finance Model

We have earlier published a Tutorial titled “Project Finance Modelling Top 10 Mistakes”, describing a wide variety of problems in Project Finance Models which can be attributed to a lack of model simplicity. This article together with the examples in the accompanied workbook suggests ways to simplify a Project Finance Model.

Simple financial models will ensure transparency and ease of use. The end user should be able to quickly and easily understand the model, which also means reviewing models will be efficient and cost effective.

To demonstrate how to simplify a project finance model, we have developed an example of a financial model (“Sample Model”). Please refer to the Sample Model as you read through this Tutorial. 

• The assumptions are contained in the ‘Inputs’ worksheet. 
• ‘Complex’ worksheet shows approaches which are lacking in simplicity and transparency. 
• ‘Suggested’ worksheet suggests alternative approaches to simplify the model.

How to Break Complicated Calculations

One of the most important Best Practice Modelling is that formula should be kept as short as practically possible whilst maintaining simplicity. Examples below demonstrate how to break complicated calculations into several steps for better transparency.

Debt Disbursement to Fund Project Costs During Construction Period

‘Complex’: Instead of modelling

Cell J16 = IF(J3>=$E4,0,MIN(J12, MAX(0,$E16-J15)))

‘Suggested’: model 

• Step 1 - Calculate the Undrawn Facility amount

Cell J29 = MAX(0,$E29-K24)*K5 

• Step 2 – Calculate amount to be funded by debt which is the Minimum of Total Required Funding and Undrawn Facility

Cell J18 = - MIN(J17,J29) 

• Step 3 – Link Step 2 to the debt account

Cell J25 = - J18

By laying out the calculations as shown in ‘Suggested’ worksheet, it is clear how much of the Total Project Costs could be funded by Debt and thus any funding shortfall could easily be identified. 

Screenshot: Suggested Debt Disbursement Calculation

Calculating Net Product Price

‘Complex’: Instead of modelling

Cell J34 =(1-$F34)*I28*(1+J27)^((J4-J3+1)/Days_Year)*$E34*(J26<>0)

‘Suggested’: model 

• Step 1 – Link ‘Product Price: Real’ to the Operations Flag (The use of binary flags will be explained in the next section)

Cell P53 = $E53*P$6 

• Step 2 – Calculate the ‘Product Price: Nominal’ by multiplying the ‘Product Price: Real’ in Step 1 with the Escalation Index

Cell P55 = P53*P54 

• Step 3 – Calculate the ‘Freight Cost’

Cell P56 = -$E56*P55 

• Step 4 – Calculate the ‘Product Price: Net”

Cell P57 = SUM(P55:P56)

Screenshot: Net Product Price Calculation

How to Create Binary Flags

Flags are useful in reducing formula complexity, and give a visual understanding of the calculation. The example will show how to create the binary (1,0) flags for timing and its application to replace complicated IF statement (often multiple IF statements).

Binary Flags for Construction and Operations

Flags for Construction and Operations are shown in Rows 5 and 6 of the ‘Suggested’ worksheet. These flags are only needed to be calculated once and you may have noticed that they are then used in various calculations in the Sample Model.

How to create the Binary Flags 

• Create ‘Construction Flag’

Cell O5 = IF(AND(O$3>=$D5,O$4<=$E5),1,0) 

• Create ‘Operations Flag’

Cell P6 = IF(AND(P$3>=$D6,P$4<=$E6),1,0)

Screenshot: Binary Flags for Timing

Application of the Flags

One of the applications of the above Timing Flags is to calculate the period counter during Operations.

‘Complex’: Instead of modelling

Cell P25 = IF(AND(P3>=$E4,P4<=$F4),(O25+1),0)

‘Suggested’: Replace the IF statement with the Binary Flag

Cell P123 = (O123+1)*P6

How to Avoid Unusual Excel Functions

To maintain the transparency in a model, it is recommended that some unusual Excel functions are kept to a minimum unless there is no other alternative of solving a certain problem. It is recommended to avoid the following functions – ISERROR, VLOOKUP, HLOOKUP, INDIRECT, OFFSET, INDEX, MATCH. Example below shows an alternative to OFFSET function

Disbursement Profile of Construction CapEx

Refer to the Sample Model:

‘Complex’: Instead of modelling Cell J8 = IF(J3>=$E4, 0, OFFSET(Inputs!G$27,$F8,0))

‘Suggested’: Replace the OFFSET() with IF()

Cell J10 = LOOKUP($E10,Inputs!$C$28:$C$31,Inputs!$G$28:$G$31)*J5

How to Logically Group Relevant Calculations

The model could be structured such that relevant calculations are logically grouped under certain headings. By doing this, the end user will have clear structure and help to quickly understand the model.

How to Apply Data Grouping

This can be done by selecting the rows to be grouped then click Data – Group and Outline – Group.

Screenshot: Calculations Grouped under Headings

[Update 26 May 2009] The Excel Workbook had an error which has now been corrected. Thank you Daniel at Standard Chartered for finding the error and for pointing it out to us.

Want more Tutorials?

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