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
- Scenario Manager
- Data Tables
- (Tornado Charts)
- Inputs for all modules
- 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
- Spend profile
- Uses (cons cost, fin fees, DSRA)
- Sources
- Revenue (price x vol)
- Opex
- Working capital
- Capex
- Senior debt
- Junior debt
- Debt metrics
- DSRA
- Working capital
- Acc. Depr
- Tax Depr
- Geared tax
- Ungeared tax
- 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
The Ultimate Project Finance Modeling Package
Everything you need to build and interpret project finance models for a transaction. Learn project finance modeling, debt sizing mechanics, running upside/downside cases and more.
Enroll Today- Next up, depreciation. (Also on the D&T tab.) This refers to the reduction in asset value of the assets that have been created during construction (and maintenance or expansion) of the project. These typically includes the financing costs which go into producing the asset. Why is depreciation important to calculate in a project finance model? PF models are clearly cash focused, so why include a non-cash item such as depreciation? In essence because depreciation impacts cashflow. It is a part of the taxable income calculation, which impacts cash tax paid. This shows up above the CFADS on the Cashflow Waterfall.
- Equity is where distributions to sponsors are calculated, in addition to cash returns to equity and to the project, and calculations of financial metrics like the Internal rate of return and net present value.
- Macros: If these are done well, they help the model to function smoothly by automating processes. The typical processes to automate are debt sizing, storing principal repayment schedules (for example, if running cases through the scenario manager) and copy/pasting the DSRA target balance.
Outputs
- CF Waterfall
- P&L
- Balance Sheet
- Financial summary
- Operational summary
- Charts
- Master macro
- Debt size
- DSRA
- The financial statements is where everything ties together in the cashflow waterfall, profit and loss (or income statement) and the balance sheet
- The cashflow waterfall is where CFADS, and CFAE and other cashflow items are generally calculated, so as you can imagine, there are many linkages coming back out of this sheet, I’ve listed a few here for example
- The summary tab contains key information for example the equity IRR, project IRR, debt size, minimum DSCR, key operational and financial summaries.
Other
There are a few other technical sheets which we won’t cover here, but add to the model infrastructure, like the Tech sheet, Checks sheet, log sheet and so on.
How this structure changes, or when to break the rules
In VERY rare circumstances, if the model is too big, consolidating calculations onto one sheet is required for the model to be fast.
The structure changes slightly when you need to consider multiple assets (e.g. think infrastructure fund that holds 31 different wind farms). In this situation you might want to consider having everything on one sheet. In VERY rare circumstances, if the model is too big, (like a treasury model I once built which calculated interest daily for a ten year time frame, for over 200 swaps and bonds of differing varieties) consolidating calculations onto one sheet is required for the model to be fast.
Or if you have to incorporate historical information into the model, this can be done in an inputs tab that is a cross between the Financial Statements, and an inputs tab. This is useful for operational project finance models — i.e. project financed assets in operations phase.
So that’s the basic structure of a project finance model, and gives you a fantastic overview of the distinctive features and how this fits together.
