Two-day intensive course to build a complete working financial model.
Build the assumptions page, cashflow statement, profit and loss account and balance sheet. Compute development expenditure, revenues, expenses, income tax, debt sizing, yields-to-maturity, profit payments, retained profits, depreciation, dividends, reserve accounts, finance service coverage ratios, internal rate of return, net present value, principal and profit payments. Automate the sukuk tranching calculation using Visual Basic for Applications (VBA).
Financial Modelling Workshop
20 CPE points hands-on tutorial on financial modelling for project finance using Microsoft Excel.
Day 1: NVFM321 Financial Modelling Essentials
Describe project finance + Compare project finance with financing for corporates and assets + Explain the importance of financial modelling to project finance + Outline the financial modelling results to greenlight a project finance opportunity
Outline the assumptions page – construction costs, revenues, operating and maintenance expenses, income tax and financing + Outline the cashflows page – flags, cashflows, reserve accounts, financial ratios, sukuk, tax, sizing and dividends
Develop construction and operating period flags + Input data and perform step-by-step computations for revenues, capex, opex and tax + Explain debt sizing process + Describe finance service reserve account and perform computations
Explain purpose and types of finance service coverage ratio + Contrast finance service coverage ratio as financial covenant and for rating + Perform the computations for finance service coverage ratio
Differentiate repayment schedule for term facility and sukuk + Create automated repayment schedule with Visual Basic for Applications (VBA) + Explain average life and average yield + Input distribution finance service coverage ratio and compute dividends
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
Day 2: NVFM353 Complete Financial Modelling
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
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
Construct the profit and loss page + Compute earnings before interest tax depreciation and amortisation (EBITDA), profit before tax, profit after tax and retained profits
Construct the balance sheet page + Calculate depreciation expense and accumulated depreciation + Compute cash, borrowings and shareholder’ funds
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
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
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
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
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
