Multi-Asset Cashflow Modelling (1)

Often project financing involves a structure to fund a multi-asset investment or projects of identical or very similar type. Let say the sponsor is considering a financing of ten (10) similar types of assets. All of the ten assets are located in various countries and are to be financed under a single debt facility.

This tutorial is in two parts. It covers how to model a project that involves some identical assets or investments in a quick and efficient way. Part one of this tutorial explains how to structure the workbook and inputs page. Part two shows how to edit and sum through all the worksheets at once making working with multiple sheets a breeze.

Screenshot 1: Multi-asset investments

In this tutorial, we will show you that with some careful planning and technique, we could model this type of transaction in a quick and efficient way. The aim is to create identical worksheets for all of the assets with no unique formulas across them. The steps are summarised below (the last two steps are covered in part-2).

• Setting-up the input page
• Setting-up worksheet for Asset #1
• Copy across the Asset #1 for all other assets
• Sum-through the worksheets
• Edit all the worksheets at once if necessary

The identical worksheets will also ensure that the end user can quickly and easily understand the model, and reviewing models will be efficient and cost-effective.

Case study of a multi-asset project cashflow model

The bullet points below describe the example used to illustrate multi-asset modelling. Please download the accompanied workbook for a better understanding.

• There are ten (10) similar assets considered for investment
• The assets are located across a number of locations
• Social and environmental evaluations for each asset have been completed, and assets are classified relating to the impacts, i.e. Category A (high), Category B (medium) and Category C (low)
• Each asset has individual construction cost assumptions
• Each asset also has unique forecast of production (T) and operating expenditure ($/T)
• The products are to be sold at market benchmark price
• The financial advisor is considering excluding “Category A” projects due to requirements under Equator Principles
• All of the included assets are to be financed under a project finance debt facility
• The debt facility has 6-year repayment terms.

The input section of the financial model

Recall that the assets have some asset specific details of inputs as well as common inputs. It might not always be possible, but it would be nice to segregate the inputs to “asset specific” section and “common inputs”.

Asset specific assumptions for the cashflow modelling

Screenshot 2 shows how to set-up the input for the asset details.

• Col C: This is set-up as number 1 to 10 and custom formatted as “Asset “&general (Ctr+1 to see format cells)
• Col F: Data validation of 1,0 with 1 = Include; 0 = Exclude – this will give flexibility for the user to include/exclude certain assets
• Col G,H,I: These are text inputs

Screenshot 2: Input for Asset details

Screenshot 3 illustrates how to set up the inputs for other asset specific assumptions, i.e. Construction and Production in this example. Note that C23:32 and C36:C45 are linked to the inputs in C9:C18 in Screenshot 2.

Screenshot 3: Input for other asset specifics assumptions

Common Assumptions in the inputs tab

The next section of the inputs page would be for the common assumptions for all the assets as illustrated in Screenshot 4.

Screenshot 4: Input section for common assumptions

Setting up worksheet for Asset #1

The next step is to set up the calculation worksheet for Asset 1. An important point to keep in mind is that we aim to create identical sheets for all the assets. This means a worksheet that enables us to replicate by simply copying across the sheets without the need to adjust the formulas for specific assets.

Reference cells

Set up a toggle cell for Asset #1 in cell E4. Cell E4 will be used to toggle the asset # and it will be the only cell that we will change when we copy across the worksheets for other assets. Cell E5 is to allow inclusion/exclusion of certain assets from the input page. We will show you in the next steps that all calculations relating to asset specific will be referenced to cell E4 and E5. Refer to formula below and Screenshots 2 and 5 for cell references.

Cell E4 = 1

Cell E5 = LOOKUP (E4, Inputs!C9:C18,Inputs!F9:F18)

Screenshot 5: Toggle cell for Asset 1

Link-in the asset-specific assumptions

The next step is to code-in the calculations for the asset specific. Screenshot 6 shows how to link the construction assumptions to the worksheet using the “LOOKUP” function referencing to the Asset # in cell E4.

Screenshot 6: Lookup is used to link-in the inputs

There will be certain cases where we will need to refer to more than one dimension data from the inputs. In this example it is the Production (Refer to Screenshot 3) – we would like to refer to the Asset # in Col C and Operating year in Row 35. In this case, we can’t use the LOOKUP function as it is limited to one dimension and the only robust solution would be to use INDEX (MATCH). Refer to Corality website for free tutorial on LOOKUP function.

Screenshot 7: Index (Match) is used to link-in the inputs

Copy across the worksheet

This step would be easy. All you need to do is to copy across the worksheet for Asset #1 for other assets (Asset #2 to #10). After copying the only cell that requires adjustment is cell E4, for example change cell E4 to 2 for Asset #2, and 3 for Asset #3. Now we have identical worksheets for Asset 1 to Asset 10.

Screenshot 8: Identical worksheets for Asset 1 to Asset 10 

Want more Tutorials?

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