Table of Contents
Anatomy of a project finance model
Below is a simplified representation of a Project finance model structure. Each of these blocks (e.g. “Cons”) represents a different calculation module. The cast of characters here is Ops = Operations, D&T = Depreciation & Tax, Cons = Construction, FS = Financial Statements:
The distinctive features of a project finance model
The distinctive features of a project finance model include:
- Construction focus: The Timing tab will often have timing that goes from monthly in construction to quarterly or semi-annual in operations.
- Debt sizing: The focus on optimizing debt leads to interactions between debt, cons & the macro tab.
- Many columns, no terminal value: Long term operations results in a longer model in general, and no terminal value calculation.
- Cash focus: Not a going concern & focus on cash leads to lender metrics, e.g. DSCR being a key output.
- Cashflow waterfall: Hierarchy in Cash Flows leads to the Cashflow Waterfall being the predominant statement on the Financial Statements tab.
- Reserve Accounts: Reserve accounts lead to having DSRA on the debt tab, MMRA & CILRA on the Ops tab, and covenants on the equity tab to make sure there aren’t distributions while these are under funded.
The connections between the modules
The connections between the modules are key to understanding a project finance model. The diagram below illustrates some of the key ones. The thicker blue arrows illustrate the flows coming out of the modules – for example revenue line items, opex line items etc.
Going through the smaller “one-to-one” type grey arrows, in order of the flow of the model:
- Drawdowns flow from Cons to the Debt tab. They are calculated on the Cons tab in order to match the timing between Uses of Capital and Sources of Capital. The debt tab usually represents the repayment of debt, hence the drawdown (or the refinanced amount from construction facility to term loan) transfers.
- [Bottom blue arrow in bold] from Calculation modules to the FS. All the calculation modules flow into the financial statements, calculating the various line items in the Cashflow Waterfall, for example CFADS.
- CFADS flows from the FS (CFW in particular) to the Debt tab. This is the crucial ingredient off which sculpting calculations are done, and debt ratios (DSCR, LLCR, PLCR) are calculated.
- Max Principal is calculated on the debt tab from sculpting calculations, and flows to the macro; along with funding required, which when applied to the gearing ratio, calculates the max debt size.
- Capex flows into the D&T tab, where it feeds into the depreciation calculations, which go into the tax calculations (which feed back into the FS).
- EBITDA flows from the P&L on the FS, to where it is involved in the Tax Computation, calculating tax paid which flows through to the FS (Cashflow Waterfall).
- CFAE (Cashflow Available for Equity) flows from the Cashflow Waterfall to the Equity tab to calculate the distributions (after factoring in the cash balance, covenant restrictions etc).
What is calculated on each module?
Now that we’ve talked about the flows between the sections, it is time to cover what goes into each section. This isn’t exactly going to be a Tom Clancy thriller, so feel free to use this as a reference section.
Model infrastructure tabs
Scenarios
- Scenario Manager
- Data Tables
- (Tornado Charts)
Inputs
- Inputs for all modules
Timing
- Date strip
- Flags
- Counters
- Escalations
- The inputs sheet: It’s self explanatory, and to be clear, there should be no inputs on any other sheets.
- Scenarios is where the scenario manager and data table are housed. This is a key feature of a model which allows sensitivities to be run – it really is the brain of the model, storing the key inputs and controlling which ones are fed through the model.
- The timing sheet is where the date bar is calculated at the top of the sheet, in addition to counters, which are the intermediate calculations (for example year of operation) that are needed to use in the call up or reference formulas at the top of the sheet.
Calculations tabs
Cons
- Spend profile
- Uses (cons cost, fin fees, DSRA)
- Sources
Ops
- Revenue (price x vol)
- Opex
- Working capital
- Capex
Debt
- Senior debt
- Junior debt
- Debt metrics
- DSRA
- Working capital
D&T
- Acc. Depr
- Tax Depr
- Geared tax
- Ungeared tax
Equity
- Distributions
- Share capital & SHL
- Equity project returns
- We’ve already discussed Construction. This tab (Cons) involves the calculation of Uses and Sources during construction. We’ve touched upon circularities which give rise to the need for Macros (i.e. VBA), the excel interface to which we house in the Macros sheet.
- Operations: Here is where the revenues generated and the expenses incurred during operation is calculated. We also adjust the calculations from an accrual basis to a cash basis, with working capital calculations
- We’ve partly touched on the debt tab: This is where your debt service is calculated for all facilities and all tranches of debt, where the DSRA is calculated, debt metrics, and a few other things
- Now to everyone’s favorite: Tax. The D&T tab is where tax & depreciation are calculated. Tax is calculated based off the P&L (EBITDA; less tax depreciation; less interest, less adjustments for tax losses) And this feeds into above the cashflow available for debt service. So the P&L expense gives rise to a cash item
