Complete the financial model and incorporate tranching.

Build the profit and loss account and balance sheet. Compute retained profits, depreciation, dividends, internal rate of return, net present value, principal and profit payments. Automate the sukuk tranching calculation using Visual Basic for Applications (VBA).

NVFM353 Complete Financial Modelling

This is part two of the hands-on tutorial on financial modelling for project finance using Microsoft Excel.

Trainer

Helmi (click for profile)

SIDC-Approved

10 CPE points

Duration

1 day (9.00am-5.00pm)

Get brochure
1
Section 1A: Recall the development of the financial model

Recall the actions taken for Part 1 + Describe the layout of the assumptions and cashflows pages + Outline the debt sizing process and automated financing repayment schedule computation

2
Section 1B: Recall the development of the financial model

Outline the source and reasonableness of revenue and expense assumptions + Explain the computations for revenues, capex, opex, income tax and the sizing of debt and equity + Describe the finance service reserve account and reasonable assumptions

3
Section 2A: Construct a financial model that determines the value of a sukuk financing opportunity

Construct the profit and loss page + Compute earnings before interest tax depreciation and amortisation (EBITDA), profit before tax, profit after tax and retained profits

4
Section 2B: Construct a financial model that determines the value of a sukuk financing opportunity

Construct the balance sheet page + Calculate depreciation expense and accumulated depreciation + Compute cash, borrowings and shareholder’ funds

5
Section 3A: Validate that the data and assumptions used in the analysis are appropriate for the evaluation of the sukuk financing opportunity

Evaluate the reasonableness of finance service coverage ratio and finance-to-equity ratio + Recompute dividend payments constrained by retained profits + Compute shareholders internal rate of return and net present value

6
Section 3B: Validate that the data and assumptions used in the analysis are appropriate for the evaluation of the sukuk financing opportunity

Explain the relationship between yields-to-maturity and price of sukuk + Contrast spot and forward issuances + Evaluate yields-to-maturity and tenor against target investors and liquidity pool

7
Section 4A: Guide others to Interpret results from a project finance financial model for purposes of investment via sukuk financing

Differentiate financing repayment schedule for term facility and tranches for sukuk + Demonstrate manual tranching of sukuk constrained by trading lot, ending cash balances, finance service coverage ratio and finance-to-equity ratio

8
Section 4B: Guide others to Interpret results from a project finance financial model for purposes of investment via sukuk financing

Outline automated tranching process + Describe how trading lots complicate tranche computing + Compute tranching using Visual Basic for Applications (VBA) + Compute average life and average yield-to-maturity and compare against term facility

9
Section 4C: Guide others to Interpret results from a project finance financial model for purposes of investment via sukuk financing

Identify the results of the base case financial model + Input stress assumptions for construction costs, revenues and opex + Compare the results of the stress case financial model to the base case financial model