Modelling Timing for Construction End

Introduction

This tutorial demonstrates the way to model the timing for construction in a project finance transaction, when it does not end in calendar period. Two methods are presented to suit project requirements.

In a project finance transaction, it is often required to model construction period monthly and operations quarterly. For reasons explained in this document, it is usually modelled such that the construction ends in a calendar quarter, i.e. March, June, September or December. In reality, the construction period does not necessarily end in calendar period.

How are we going to set up the model in this case? In this tutorial we demonstrate two ways of modelling the construction timing.

Method 1

We have prepared an example with the following assumptions.

• Construction commences 01 Jan 11
• 10 months of construction
• Construction ends 31 Oct 11
• Debt facility is available for drawdown until 31 Dec 11

In this case, actual construction ends 31 Oct (two months away from 31 Dec). For modelling purposes, the construction period should be set up so that it is extended to 31 Dec 11 for the following reasons.

• The construction debt facility is commonly structured to end in a calendar quarter (31 Dec 11)
• Repayment of debt can be calculated quarterly, which results in ease of computation and avoidance of complex formulas
• Modelling depreciation, debt service reserve account targets, debt repayment and ratios become complicated when there is a mix of monthly and quarterly timing
• Interest payments are usually aligned to be end of quarter
• The company’s financial statements usually end in a calendar quarter, resulting in clearer presentation of information

Screenshot 1: Construction inputs (Method 1)

Refer to Screenshot 1 to see how the model should be set up.

• Cell D11 (construction duration) : The input for construction duration is set up on a quarterly basis so that it always ends in a calendar period
• Cell E10 (operations start) : The Operations will always start at the beginning of calendar period because it is formulated as Construction end plus 1 day
• Row 21 (capex disbursement) : Note that the last construction capex disbursement is on 31-Oct-11 (month 10)

Calculation page

The calculation page is simple and clean. Refer to the snapshot below showing how the construction funding calculated on monthly basis and the operations on quarterly basis.

In this method, although the construction ends 31-Oct-11, the debt is assumed to be available until 31-Dec-11 so there is interest during construction calculated until then.

Screenshot 2 : Construction funding calculation (Method 1)

Method 2

Method 1 is simpler and straight-forward for modelling purposes. However, there could be cases when we need to model construction to end exactly on the construction end date which is on 31-Oct-11 in this case study. Perhaps the construction debt facility is only available up to the construction end date (31-Oct-11) and cannot be extended until the end of calendar period.

How are we going to set up the model to suit the above requirement but at the same time ensure that Operations are modelled in calendar quarters? One way to address this is to set up a model in the manner of Screenshot 3 below.

Screenshot 3 : Model timing (Method 2)

Method 2 is structured as follows

• Cell D11 (construction duration) : The duration is set up on monthly basis which is 10 months in this case
• Cell E11 (monthly operations duration) : The monthly period is extended during operations until it ends at a calendar period
• Cell F10 (quarterly operations duration) : The model reverts to quarterly at the beginning of a calendar period

In Method 2, we set up monthly inputs during the monthly operations duration before it reverts to quarterly inputs. Refer to Screenshot 4 & 5 and the Excel workbook for details.

Screenshot 4: Construction and Operations inputs (Method 2)

Screenshot 5 : Model timing calculation (Method 2)

Calculation page

Here are a few tips on modelling under Method 2 to avoid errors due to the switch from monthly to quarterly during operations.

• Clearly set up the model timing – refer to Screenshot 3
• Clearly set up the flags / counters for model timing such as shown in Screenshot 5, i.e. number of days (row 9), monthly operation counter (row 14), quarterly operations counter (row 10)
• To avoid long formulas, some of the calculations can be separated between monthly and quarterly, e.g. production calculation in Screenshot 6 (row 79:81) All calculations should be calculated based on the number of days in the period, e.g. interest, fixed costs and production numbers
• Do not model quarterly period to start from non calendar quarter periods because calculating annual summaries will not be accurate

Screenshot 6 : Production / Revenue calculation (Method 2)

Want more Tutorials?

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