Project Finance by aliasghar84

VIEWS: 1,284 PAGES: 86

									Objectives of Project Finance Models

Project Finance Modelling

September 09

1

Exercises
•
•

Understand the Cash Process in Models
Evaluate Risk with Models
 Delay in Completion of Construction

 Break-even Price and PPA Price Components
 Downside Case Production and Operation Expenses

•

Work with Structural Enhancements
 Covenants

 Debt Service Reserves
 Liquidation Damages

•

Perform Financial Structuring with Model
 Debt Sizing and Cash Flow Waterfall



Senior
Subordinated

 Loan Tenor

 Credit Spread

Project Finance Modelling

September 09

2

Financial Models – Standard and Poors
• A good financial model should:
 Be relatively simple  Focus on key cash flow drivers  Clearly convey assumptions and conclusions

• Alternative Models
 Back of the Envelope
 Quickly run the impact of an acquisition on debt service coverage  Sensitivity of earnings to commodity price swings

 Deterministic
 Set a number of assumptions and translate into financial ratios and cash flow

 Stochastic
 Develop a range of possible inputs using Monte Carlo simulation. Used where there is a good and predictable history for value drivers.

Project Finance Modelling

September 09

3

Model Objectives - Overview
• Valuation
 Decision to invest



•
Development Stage
On-going

Basic Objective
 Measure whether the expected returns are worth taking the risk  Models must be able to somehow measure risk to be effective

 Value of project
 Debt Structuring

•

Contract Structuring
 Contract pricing
 Contract length

•

Risk Assessment
 Equity risk
 Credit analysis

•

Financial Structuring
 Covenants
 Gearing

 Debt service reserve
 Senior and subordinated debt

Project Finance Modelling

September 09

4

Objectives of Project Finance Modelling
• Valuation
 The basic objective of project finance is obviously to evaluate whether the project works – whether the benefits of the project outweigh the costs of the project. The value of a project is measured by whether the returns on the project – the IRR – exceeds the risk adjusted cost of capital. The question of whether the project works is evaluated using base case assumptions.

•

Structuring of the Debt Facility and Other Contracts
 The model is used to establish many terms in the various contracts that arise in project finance. The base case debt service cover establishes whether the gearing is appropriate; the base case can be used to set prices such as capacity charges in the project contract and the model is used to structure the appropriate repayment of the debt facility.

•

Credit Analysis
 The model is used by lenders to the project to evaluate risks associated with default on the loan agreement. Typical analysis involves assessing the level at which various operating parameters cause the debt service coverage ratio to fall below 1.0 at some point over the life of the project.

Project Finance Modelling

September 09

5

Objectives of Project Finance Modelling (Continued)
•

Repository of Information
 In working through the engineering, contracting, financing and other aspects of a project, the model serves as a database where important information is stored. If a new operating parameter is required or if a new contractual element is negotiated, the import of that new information is gauged in the model. If something matters to the economics of the project, it should influence the outcome of the project finance model.

•

Evaluating Whether Contract Provisions Appropriately Mitigate Risk
 The model should be used to determine whether the language in various contracts mitigates risk to parties in the way the terms in the are intended to operate. The model can test whether covenant provisions, debt service provisions, liquidation damage provisions and other language in fact mitigates risk to lenders and developers. The use of project finance models in evaluating contract language is described in the next slides.

Project Finance Modelling

September 09

6

Project Finance Model Objective: Testing Structural Enhancements
• A project finance model can be used to test whether agreements protect parties consistent with the intention of the contracts. In order to use the project finance model in this manner, the model must be run for a downside or a stress case scenario. In the base case:
 There is no delay in construction that causes the trigger of liquidation damages;
 There is no reduced operation cash flow to test covenants that limit distribution of dividends;

 There is no increased price that causes a cash sweep covenant to be used;
 There are no financial problems that require withdrawal of funds from the debt service reserve;

 The PPA or project contract remains in place with no penalties.

•

The process of testing covenants involves first defining reasonable risks in a downside case or stress case and then running the model in two cases:
 First, run the model without the structural enhancement;  Second, run the model with structural enhancements and determine whether the contracts in fact mitigate risk.

Project Finance Modelling

September 09

7

Model Objectives – Structuring of Funding and Debt Drawdown
• In modeling the construction of the facility, various issues related to the potential for cost over-runs and delays must be addressed:
 Who funds the construction of cost over-runs;  Who funds interest costs if there is a delay in construction (debt, equity or another party).  The model should accurately reflect what happens in a cost over-run or a delay scenario.

•

Example of the Kutubu Petroleum Development – The project construction received high gearing; some equity holders received equity interests for agreeing to fund construction over-runs. Example of the Freeport Development – The banks agreed to fund cost over-runs only up to 120% of the budgeted amounts.

•

Project Finance Modelling

September 09

8

Model Objectives – Covenants in Loan Facility •

In writing covenants for loan facilities, the model should evaluate whether the covenants protect lenders.
 The model should incorporate what happens to cash flow when covenants limit the distribution of cash flow;

 The model should show what happens to cash flows when covenants limit cash distributions from positive operating results;  The model should be able to quantify the costs and benefits of alternative types of covenants and criteria for the covenants.

•

Example of Covenant: If the debt service coverage is below 1.2, then no distributions are allowed; the cash flow that would be distributed is instead used to increase funding of reserve

Project Finance Modelling

September 09

9

Elements of Project Economics that are not in the Project Finance Model • For a modeler, it is tempting to assert that everything that matters to the economics of the project is included in the model. However it is important to understand what risks and contract elements of the project are not included in the model:
 If you written a wonderful construction contract that protects developers and bankers with liquation damages, if the contractor does not have financial resources to meet the liquidation damages, the contract could be meaningless. The financial condition of the contractor and the necessity of bonding, letters of credit or insurance is not quantified in the project finance model.  If you have written a solid PPA agreement that again protects developers and bankers, if the counterparty who buys power in the PPA agreement cannot make payments under the contract, the contract is again meaningless. The financial condition of the offtaker is not measured in the project finance model nor is the crucial issue of whether the off-taker has an incentive to get out of the contract.
Project Finance Modelling September 09 10

Structure of Project Finance Models

Project Finance Modelling

September 09

11

Sheet Layout – Project Model
• • Contents Input Sheets (Assumption Book)
  Different colors Arranging of inputs

•

Working Sheets



Arrangements by revenues, expenses and capital expenditures
Arrangements by capacity, demand, and cost structure

•

Monthly Construction Expenditures (Source and Use of Funds)
 Conversion from Annual



Computation of Interest During Construction

•

Debt Schedule (Sources of Funds)

•
•

Depreciation Schedule
Financial Statements



Source and Use of Funds
Income Statement




Balance Sheet
Cash Flow -- Waterfall

•

Output Sheets
  Valuation - IRR Debt Service Coverage Ratios

Project Finance Modelling

September 09

12

Modules in a Project Finance Model – How to Move from Assumptions to Valuation
• A project finance model begins with three primary inputs:
 Capital Expenditures  Revenues  Expenses

•

The project finance model accepts these inputs along with tax and finance assumptions and computes project and equity cash flow

•

Modules in project finance models should include:
 Assumptions
 Working Analysis  Sources and Uses  Financial Statements  Cash Flow and Valuation

Project Finance Modelling

September 09

13

Inputs, Outputs and Simple Computation of Cash Flow to Obtain Valuation
• Inputs to Derive Free Cash Flow
   Capital Expenditures Revenues Expenses




Debtors and Creditors
Tax Rates



Plant Life

• •

Calculations are simple: Revenues – Expenses – Working Capital Movement, net of tax Inputs to Derive Equity Cash Flow
  Debt to Capital Interest Rates




Debt Repayment
Debt Service Reserves

• •

The basic structure of project finance models are easy to understand without being a modelling expert The balance sheet must balance – by far the most effective check on calculations and balances should be zero at the end of the project life (debt, asset balance, reserve balance etc.)

Project Finance Modelling

September 09

14

Model Sheets in Project Finance Model

Inputs –
Prices, Costs, Capacity, Technical Parameters

Debt Schedule –
Debt Balance From Drawdown Debt Balance, Interest Expense

Working Sheet to
Derive Revenues Expenses and Working Capital

Depreciation –
Depreciation Expense Plant Balance

Outputs –
Free Cash Flow, Equity Cash Flow Value (IRR), DSCR

Source and Use of Funds –
Draw down, IDC, Equity Issues and Capital Expenditures

Annual Financials –
Income Statement, Cash Flow (CASH WATERFALL) and Balance Sheet

Project Finance Modelling

September 09

15

Model Components
Cash Flow Waterfall:

Senior debt service

Inputs
Operating - Capital Expenditures - Revenues - Operating Expenses - A/R and A/P Financial and Tax - Debt Leverage - Interest Rate - Debt Repayment - Tax Rate - Tax Depreciation

Senior debt service reserve Senior debt prepayments from covenants

Junior Debt Service
Junior debt re-payments of default Dividends

Mechanics
Construction Sources & Uses Income Statement Cash Flow Statement Balance Sheet Equity Cash Flow Project Free Cash Flow

Outputs
IRR – Equity IRR – Project Net Present Value Return on Investment Economic Profit Debt Service Coverage LLCR Payback Period Accounting Earnings

Project Finance Modelling

September 09

16

Results of Good Model Structure • In a well programmed model:
 The economics and the key risks of the project should be presented well and come off the page
 The model should be easy to follow where one can clearly trace the inputs, the mechanics and the outputs of the model

 The model should be stable which means that you can play with inputs equations in the model without fear of messing things up.  The model should reflect the structure of the project including key contracts; the timing of project completion; key risks and alternative debt structure terms.

Project Finance Modelling

September 09

17

Valuation with Project Finance Models
Modelling Issue
1. Measure cash flow over the entire life of the asset 2. Cash flows not really known until the project is in service – no history of cash flows

Project Finance Issue
 Limited Project Life  Commercial Operation Date

 Cash Flow  Waterfall and subordinated debt
 Non-Recourse  Structural Enhancements  Risk Allocation from contract

3. Value of debt and equity driven by cash flow 4. Measure the value of different securities supported by project cash flow 5. No portfolio of assets to diversify risk 6. Value of debt and equity depends on covenants, debt service reserve 7. Risk analysis depends on contracts used to allocate risk to different parties

Project Finance Modelling

September 09

18

Inputs
The most important thing in modeling has always been Garbage in Garbage Out

Project Finance Modelling

September 09

19

Structure of Inputs • One should be able to find all of the inputs in an easy manner and see how the inputs affect the outputs – this is why the financial statement page should not have any inputs
 All inputs should have a color convention so it is clear what numbers can be changed and what should not.  Separate inputs that vary by year (or month) and inputs that are constant.
 Other sheets should have links to the input page where the inputs are repeated on the top of the page

Project Finance Modelling

September 09

20

Single Input Sheet • If Inputs are all collected on a single sheet
 Can find where to change all items (don’t have to look around for switches and inputs)
 Easier to develop alternative scenarios with different assumptions  Possible exceptions for interest rate and maturity payments on debt issues

• In the real world, you develop a model with inputs in various places and then re-structure the spreadsheet to collect the inputs in a single sheet.

Project Finance Modelling

September 09

21

Input Sheet Example

Project Finance Modelling

September 09

22

Example of Difficult Inputs to Find

Inputs in a column far away from the sheet in a sheet that does not have other inputs

Project Finance Modelling

September 09

23

Use Hyperlinks to Document Assumptions
•

Given that the financial model is a database, I like to keep source documents in the spreadsheet, if possible. Hyperlinks can be used to trace each assumption to the original source. In the example below, the hyperlink in the assumption page refers to documents from an investment analyst presentation.

Assumption page with hyperlinks

Result of Hyperlink

•

Explanation of how to insert hyperlinks is shown in the excel background presentation.

Project Finance Modelling

September 09

24

Financial Statements And Working Sheets – No Inputs in Financial Statements

Putting a Number in a Financial Statement is an Obvious No

Project Finance Modelling

September 09

25

Example of Input Number in a Spreadsheet – Percentages and Factors Should be with Inputs

The 10% Factor should be shown explicitly in the spreadsheet

Project Finance Modelling

September 09

26

Corrected Sheet with Explicit Presentation of Inputs

II. Colocation Capex (90%) Core Infrastructure Civil Works/ MEP Network/ IT Services Subtotal Contingency Percent Contingency Sensitivity Factor Total Capex

Show the percentages in a separate line item

6,861,293 1,347,297 1,155,756 297,675 9,662,021 10% 1,073,558 100% 11,809,137

1,605,625 1,605,625 10% 178,403 100% 1,962,431

Project Finance Modelling

September 09

27

Use Excel Toolbars and Forms to Allow Sensitivity Cases from Multiple Locations
• You allow excel to revise inputs in multiple locations using the view toolbars forms and then using the combo box, the spinner box or the scroll bar. This allows you to keep the inputs together and also to adjust the inputs in sheets to examine the effect of the input.

•

Project Finance Modelling

September 09

28

Operating Assumptions in Model

The entire model is driven from only three basic operating inputs:  Capital expenditures  Revenues  Operating expenses • • When you get a model from someone else find these three inputs and work backwards The working sheet should develop projections of these inputs from value drivers such as price, market share, market growth, capacity additions, capacity utilization, cost of new capital and the cost structure. History should be presented along with forecasts for the value drivers

•

Project Finance Modelling

September 09

29

Capital Expenditure Inputs

There are four inputs generally required related to capital expenditures. These include:
1. The monetary amount of the capital expenditure without IDC 2. The capacity derived from the capital expenditure 3. The timing of the capital expenditures 4. The "soft costs" related to the capital expenditures • The modeling of capital expenditures is generally on a monthly basis were the draw-downs from equity and debt as well as interest during construction is derived. For simplicity, we begin by modeling capital expenditures on an annual basis. • Computation of monthly draw-downs is precisely the same as the annual computation, except that months are used instead of years.

Project Finance Modelling

September 09

30

Capital Expenditures, Capacity and Cost Structure •

In an economic endeavor, capital expenditures give you the capacity to produce something. The capital expenditures can generally be stated in terms of the maximum productive capacity. Some examples are:
 Capacity of an electric plant in MW ($/kW)  Capacity of oil or gas reserves in barrels or BTU  Capacity of a water desalination plant in M Gal  Capacity of a pipeline in maximum throughput  Capacity of a road in maximum traffic

Project Finance Modelling

September 09

31

Technical Parameters that Form the Basis of the Project Cost Structure

Power plant
Heat rate Heat content of fuel
Availability Capacity factor

Refinery
Product yield Throughput rate
Maintenance time Utilization rate

Mine
Ore yield Production Rate
Mining Prod Equipment avail

Maintenance cycle Cooling water avail

Product quality Catalyst life

Project Finance Modelling

September 09

32

Revenue Inputs

Revenue inputs come from price and quantity. Prices can commodity, contract-based or management determined.
 Commodity - Oil, gas, electricity, metals, wood etc.

 Contract - PPA, government tariff.  Management - Tolls, entry fees.

Volumes are derived from the capital expenditure maximum capacity and the capacity utilization.

Project Finance Modelling

September 09

33

Inputs to Project Finance Model from Documents
• Work in Project Contracts
 Define completion date – define specific provisions that determine when loan repayment begins and draws stop.  Define drawdown provisions – what happens if there are construction over-runs; which parties provide the added funding

• Project Finance Modeling
 Set-up the model with routines for construction period and operation period where formulas differ for the two periods.  Program model to accurately reflect debt and equity contributions in sources and uses statement

 Define mechanics of the debt service reserve and its use with covenants  Define the timing of debt repayments in the debt facility and definitions of default

 Program the model to make deposits and withdrawls from the debt service reserve  Program aspects of the loans including debt repayment and debt defaults

Project Finance Modelling

September 09

34

Workings Analysis
Work through inputs and build components for the Financial Statements

Project Finance Modelling

September 09

35

Workings Should Clearly Describe the Value Drivers •

Capital Expenditures to Grow the Company
 Investment Cost Per Unit Of Capacity  On-going maintenance capital expenditures

•

Revenues
 Product Prices (Price Setter or Price Taker)  Volumes produced –> Capacity x Capacity Utilization

•

Operating Expenses
 Resource cost -> Resource Price x Resource Use  Resource use -> Efficiency Factor x Volume  Other Fixed, Variable and Overhead Expenses

Project Finance Modelling

September 09

36

Example of Relation Between Value Drivers and Financial Model Inputs

Project Finance Modelling

September 09

37

Conversions from Output Quantity to Input Quantity •

The most difficult part of model construction is often the conversion of units and making sure that the currency is correct. You can use the conversion program provided and I suggest that you put all of the currency in single units - not thousands, millions and so forth. Then you can convert to the appropriate units of the model when you are finished.
Use the conversion program provided on the CD

•

•

Project Finance Modelling

September 09

38

Quantity from Capacity and Utilization

• Production assumptions are derived from maximum capacity. The maximum capacity must be related to a time dimension and generally apply a capacity utilization factor. For example:
 MWh = MW x hours x Capacity Factor  BBLs = Reserves x Ratio of withdrawals  Aluminum Tons = Capacity x Utilization

• The most difficult part of model construction is often the conversion of units and making sure that the currency is correct.

• You can use the conversion program provided and I suggest that you put all of the currency in single units - not thousands, millions and so forth. Then you can convert to the appropriate units of the model when you are finished.
Project Finance Modelling September 09 39

Cost Workings Analysis • Cost risks apply to labor and materials inputs, productivity, and operating expenses (“Opex”)
• Cost risk includes the effect of inflation.

• Typical cashflows will show 7-10 key items of costs with the remaining cost categories usually amounting to only 5-10% of Opex • Whether a contract exists or not, the position of the project in terms of cost of production relative to all the other producers of the given product is important. • Generally, a project in the lower half or lower third of the cost curve (sorted costs of other producers) is adequate.

Project Finance Modelling

September 09

40

Financial Statement Modeling: Sources and Uses

Project Finance Modelling

September 09

41

Separation of Construction and Operation Time Period
• Sources and Uses Statement During Construction
• No cash distributions to equity • Sources and uses of cash to determine equity and debt issuance • Debt drawdown and no debt maturities • Interest expense capitalized to construction

•
• •

Income Statement and Cash Flow Statement
Distribute cash flow to equity Dividend distribution from the cash flow statement at the end of the cash flow waterfall Debt repayment included in the cash flow Interest expensed in the income statement

• •

• No revenues, expenses or depreciation

•

Revenues, expenses and depreciation included

Project Finance Modelling

September 09

42

Modeling the Financial Structure
• Model Structure
 Sources and uses  Debt financing  Depreciation Schedule  Financial statements

• Time Periods
 Construction  Debt module  Operating  Financial statements
 Returns

Project Finance Modelling

September 09

43

Source and Use Statement •
•

Importance of statement in understanding the transaction – where the money goes and where it comes from
Time Periods – do not be afraid of monthly statements

•

Interest During Construction

Project Finance Modelling

September 09

44

Example of Sources and Uses – In a model this is computed on an annual or monthly basis
SOURCES OF FUNDS(1)

Principal amount of the old bonds...........................
Equity Contribution(2)......................................

$275,000,000
35,500,000

Revenues from operation of the Initial Units(3).............

20,089,000
------------

TOTAL SOURCES OF FUNDS................................

$330,589,000
============

USES OF PROCEEDS
EPC Contract................................................ Electric Interconnection Facilities......................... Gas Supply Facilities....................................... Spare Parts................................................. Development Cost............................................ Initial Working Capital..................................... $229,065,000 1,000,000 3,123,000 3,649,000 7,000,000 258,000

Project Management..........................................
Legal & Financing...........................................

4,643,000
5,114,000

Net Interest During Construction(4).........................
Expenses of operation of the Initial Units..................

52,757,000
4,831,000

Contingencies...............................................
Other.......................................................

11,962,000
7,187,000

-----------TOTAL PROJECT COSTS................................... $330,589,000 ============

Project Finance Modelling

September 09

45

Sources and Uses Statement During Construction
•

Interest During Construction
    IDC is capitalized to construction cost -- this means that interest is not included on the income statements, but it is included as a part of construction cost. Depreciation includes IDC in the base. IDC can be computed from the debt balance. Interest Income on Debt Reserves has similar computations.

•

Monthly versus Annual Sources and Uses
 


The Only Reason for Monthly Analysis of Construction is for Accurate Representation of IDC, Otherwise Annual Would Be Fine: Monthly sources and uses of funds statement computed in exactly the same format, but compute monthly interest
When computing interest expense, use the annual interest rate divided by twelve



Tabulate the monthly interest balance and replace the lines in the annual model with the sum of the monthly interest. (You could do this with debt balances as well, but that is not necessary.)

Project Finance Modelling

September 09

46

Draw downs in Source and Use Statement
• The manner in which draw downs occur from debt and equity can have large effects on the equity IRR of a project. The model should be able to test different draw down schedules.

• •

Operating income and interest income can be incorporated in the source and use statement. Negative arbitrage can be important in the source and use statement from a higher borrowing rate than a funding rate.

Financing During Construction
120%

100%

0%

0%

80%

60%

100%
40%

100%

98%

91%

84%

20%

Debt Financing Equity Financing

0%

2%
1968 1969 1970
Year

9%
1971

16%
1972

Project Finance Modelling

September 09

47

Debt Schedule

Project Finance Modelling

September 09

48

Debt Sheet • The debt module to model includes the total of all debt derived from the sources and uses statement.
• Each debt issue should show at minimum the beginning debt balance, debt draws, debt repayments, interest expense and ending debt balance.
 Use a separate module and put interest expense and debt repayments etc. in the financials
 Reflect the actual repayment structure and the quarterly or semiannual repayments.  Include interest expense in the income statement from the debt module - make sure that EBT subtracts interest expense.

Project Finance Modelling

September 09

49

Debt Service Reserves •

Debt service reserves should be modeled in a similar manner to debt issues:
 Show beginning balance, inflows to debt service reserve (from sources and uses), with drawl from debt service reserve (when debt matures) and ending balance

 Model should be flexible enough to show withdrawls from debt service reserve when the cash flow is negative and inflows to debt service reserve when the debt service reserve is not topped off.
 Modeling of the debt service reserve can become complex.

Project Finance Modelling

September 09

50

Example of Debt Amortization Schedule
• PRINCIPAL PAYMENT PERCENTAGE SCHEDULED AGGREGATE SCHEDULED

•
• • • • • •

DATES

PRINCIPAL
AMORTIZATION

PRINCIPAL
PAYMENT ------------------$ 4,302,000 $ 19,496,000 $ 18,981,000 $ 17,227,000

----------------July 2, 2001 July 2, 2002 July 2, 2003 July 2, 2004

---------------------1.272781% 5.768047% 5.615680% 5.096746%

•
• • • •

July 2, 2005
July 2, 2006 July 2, 2007 July 2, 2008 July 2, 2009

3.944675%
4.248225% 4.005030% 4.994083% 5.966568%

$ 13,333,000
$ 14,359,000 $ 13,537,000 $ 16,880,000 $ 20,167,000

•
• • • •

July 2, 2010
July 2, 2011 July 2, 2012 July 2, 2013 July 2, 2014

7.034911%
7.820710% 7.249704% 10.280473% 11.054438%

$ 23,778,000
$ 26,434,000 $ 24,504,000 $ 34,748,000 $ 37,364,000

•
• • • •

July 2, 2015
July 2, 2016 July 2, 2017 July 2, 2018 July 2, 2019

10.281361%
1.816272% 0.887574% 0.887574% 0.887574%

$ 34,751,000
$ 6,139,000 $ 3,000,000 $ 3,000,000 $ 3,000,000

•
• •

July 2, 2020

0.887574%
----------100.000000%

$ 3,000,000
-----------$338,000,000

Project Finance Modelling

September 09

51

Example of Amortization Schedule
•
•
• • • • <S> PRINCIPAL PAYMENT DATES ----------------PRINCIPAL AMOUNT PRINCIPAL AMOUNT PRINCIPAL AMOUNT

PAYABLE ON
SERIES A-1 SENIOR SECURED BONDS ----------------<C>

PAYABLE ON
SERIES B-1 SENIOR SECURED BONDS ----------------<C>

PAYABLE ON
SERIES C-1 SENIOR SECURED BONDS ----------------<C>

•
• • • • • • • • • • • • • • • • • • • • • • • • • • • •

December 15, 2000............................
June 15, 2001.............................. December 15, 2001............................ June 15, 2002.............................. December 15, 2002............................ June 15, 2003.............................. December 15, 2003............................ June 15, 2004.............................. December 15, 2004............................ June 15, 2005.............................. December 15, 2005............................ June 15, 2006.............................. December 15, 2006............................ June 15, 2007.............................. December 15, 2007............................ June 15, 2008.............................. December 15, 2008............................ June 15, 2009.............................. December 15, 2009............................ June 15, 2010.............................. December 15, 2010............................ June 15, 2011.............................. December 15, 2011............................ June 15, 2012.............................. December 15, 2012............................ June 15, 2013.............................. December 15, 2013............................ June 15, 2014.............................. December 15, 2014............................

$ 50,000,000
45,000,000 45,000,000 53,500,000 53,500,000 17,500,000 17,500,000 19,000,000 19,000,000

$

0
0 0 0 0 0 0 0 0 0 0 0 0 16,500,000 16,500,000 17,000,000 17,000,000 19,000,000 19,000,000 2,000,000 2,000,000 2,000,000 2,000,000 2,000,000 2,000,000 2,500,000 2,500,000 2,500,000 2,500,000

$

0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

•
• • • •

June 15, 2015..............................
December 15, 2015............................ June 15, 2016.............................. December 15, 2016............................ June 15, 2017..............................

3,000,000

0
3,000,000 3,000,000 3,000,000 3,500,000

•
• • • •

December 15, 2017............................
June 15, 2018.............................. December 15, 2018............................ June 15, 2019.............................. December 15, 2019............................

3,500,000
15,500,000 15,500,000 17,000,000 17,000,000

•
• • • • • • • • • • •

June 15, 2020..............................
December 15, 2020............................ June 15, 2021.............................. December 15, 2021............................ June 15, 2022.............................. December 15, 2022............................ June 15, 2023.............................. December 15, 2023............................ June 15, 2024.............................. December 15, 2024............................ -----------TOTAL.............................. $320,000,000 -----------$130,000,000

18,500,000
18,500,000 20,000,000 20,000,000 22,000,000 22,000,000 23,000,000 23,000,000 26,000,000 26,000,000 -----------$300

Project Finance Modelling

September 09

52

Income Statement, Cash Flow Statement and Balance Sheet

Project Finance Modelling

September 09

53

Income Statement and Cash Flow Statement After Construction
• Income Statement and Cash Flow Statement
 Compute cash flow for dividends

•

Complicated Part is Between Operating Cash Flow and Dividends
 Cash Flow to Senior Debt

 Use of cash flow
  Pre-payments from covenants Payment of debt service reserve



Defaults

 Cash Flow to Junior Tranches  Funding of Debt Service Reserves

•

Debt Repayment in Cash Flow

 

Time Periods -- for accuracy of interest expense
Incorporation of Re-payment Structure Flexibility to model defaults, pre-payments, reserves

Project Finance Modelling

September 09

54

Income Statement
•

Compute profit or loss from revenues and expenses:
 EBITDA is Revenues less Expenses  EBIT is EBITDA less Depreciation and Amortization  EBT is EBIT less net Interest  Net income is EBT less taxes.

•

Notes
 May compute net operating loss carry forward  Net interest should include interest income earned on reserves

Project Finance Modelling

September 09

55

Cash Flow Mechanics
• Separate Statement into normal components
 Operating
 Net income plus depreciation less working capital movements less gain on asset sale

 Investing
 Capital expenditures (including IDC), on-going capital expenditures and proceeds from asset sale

 Financing
 Bottom line is dividend distributions. Include financing inflows, debt repayments and flows from reserves.

• Cash flow before financing (similar to free cash flow) is the number that must be financed.

• Compute cash flow statement for construction period and the operating period.

Project Finance Modelling

September 09

56

Model Components and Project Contracts
•

Sources and Uses Statement
 EPC Contract  Construction Loan

•

Income Statement
 PPA Contract  Concession Agreement

 Supply Agreement

•

Cash Flow Statement
 Cash Flow Waterfall  Permanent Debt Agreement
 Debt Service Reserves  Covenants

Project Finance Modelling

September 09

57

Model Outputs

Project Finance Modelling

September 09

58

Structure of Outputs • Outputs should generally come from the financial statements and should not affect any of the calculations (you should be able to delete the outputs page without any impact on the model) • Outputs for comparative graphs can be saved in a separate sheet -- you can develop a macro using a paste as value method to compare scenarios
• Put macro buttons, spinner boxes, combo boxes and scroll bars on the summary page.

• Output Rule: You should be able to delete cells in the output sheet and summary sheet without affecting any of the previous sheets.
Project Finance Modelling September 09 59

Output Example – Project Finance

Try to summarize key inputs and key outputs on a single page and make the numbers jump out at you

Project Finance Modelling

September 09

60

Example of Summary Page with Graph
Payback (Yrs) 3.6 Discounted Payback (Yrs) 7.0

NPV (AED M) Year Summary Financial Data Free Cash Flow Equity Cash Flow Net Income Colocation utilization Base Case Low Case High Case Case Applied Managed Service Utilization Base Case Low Case High Case Case Applied Prices Colocation Managed Service 0 (12,609,137) (800,000) 1 136,584 (1,576,593) (256,994) 2 2,912,624 1,199,446 399,928 3 5,438,598 3,725,420 2,986,744 4 6,766,992 5,053,814 4,379,630 5 8,354,138 6,640,960 6,035,138 5.5

IRR (%) 23.1%

35% 30% 35% 35% 5% 5% 5% 5% Base 20,000 200,000

50% 30% 70% 50% 10% 5% 15% 10% Sensitivity 100% 100%

70% 30% 90% 70% 15% 5% 20% 15% Applied 20,000 200,000

80% 40% 90% 80% 20% 10% 30% 20%

90% 40% 90% 90% 30% 10% 40% 30%

Financing and Valuation Assumptions Cash Flow Growth in Perpetuity Discount factor Book Value Multiple Debt Financing Assumptions Debt Percent Repayment Period (Maturity) Per Effective Interest Rate Cost Parameters Hardware Percent Network Costs Rental Rate Additional Staff

0% 12% 1.00 100% 10 1 6.00% 60% 1,000,000 50 300,000

Payback Period 3.6 Discounted Payback 7.0 Project IRR 23.1% NPV 5.5

When the inputs are change, the graph changes and the title of the graph changes. This is an effective way to present results.

15,000,000 10,000,000
5,438,598 6,766,992 2,912,624 136,584

13,272,065

5,000,000 -5,000,000 -10,000,000 -15,000,000
-12,609,137

0

1

2

3

4

5

Payback Period 3.6 Discounted Payback 7.0 Project IRR 23.1% NPV 5.5
15,000,000
13,272,065

Project Finance Modelling
10,000,000
6,766,992

September 09

61

Complexities

Project Finance Modelling

September 09

62

Complexities • • • •
•

Debt Defaults Cash Flow Waterfall Net Operating Loss Terminal Value
Vintage Depreciation and On-going Capital Expenditures

Project Finance Modelling

September 09

63

Periodic Cash Flow Modeling • Monthly versus Annual Periodic Modeling
 Need to model with years on a vertical rather than a horizontal basis because you run out of room.
 Use look-up tables to find inputs from annual workings analysis and monthly construction etc.

 Adds accuracy where
 Debt service is quarterly etc.  Scrapping occurs at specific points

Project Finance Modelling

September 09

64

Cash Flow Waterfall •

Waterfall Issues
 Defaults and subsequent repayments of defaults before dividend distributions
 Model different priorities of debt  Model cash flow trap mechanisms  Evaluate Pre-payments from covenant violations  Compute Debt service reserve injections and withdrawls  Accumulation of debt service reserve after construction period

Project Finance Modelling

September 09

65

Modelling Defaults on Debt •

Modelling defaults on debt is important in credit analysis. Through modelling defaults, the probability of default and the loss given default can be evaluated through break-even analysis and through Monte Carlo simulation.
The following process shows how to model defaults:
 Set up the debt balance to incorporate defaults and re-payment of defaults  The default comes from an if statement in the cash flow statement

•

 The re-payment of default is the previous year default amount. This means the model attempts to fully repay the default in the year immediately following the default. If there is no cash flow to repay the default, the default increases by the amount of the default.

Project Finance Modelling

September 09

66

Modelling Defaults on Debt - Procedure •

The following illustrates the modelling process for defaults.
 Note how the default comes from the cash flow statement  The if statement in the cash flow statement  The repayment of default from the prior default

Project Finance Modelling

September 09

67

Maximum Default •
• •

When modelling the defaults on debt, it is possible that the cash flow is less than the total debt service.
In this case, the default is only the debt service that was not covered, not the negative of the cash flow. Therefore, the default formula should be:
 If(cash<0,min(-cash,debt service),0)  Notes:
 Cash is cash flow to tranche  Debt service includes the repayment of defaults

Project Finance Modelling

September 09

68

Analysis with Defaults •

Once defaults are modeled, one can evaluate the IRR earned on debt instruments and the amount of debt outstanding.
 The amount of debt outstanding is the loss given default.  The IRR allows you to compute a probability distribution for the loan facility

Project Finance Modelling

September 09

69

Computing Cash Flow for the Waterfall
• To model priorities in a cash flow waterfall the first step is setting up a the cash flow statement in a model that reflects the actual ordering of cash flow:
 Begin with the cash flow after capital expenditures and after all new financing and acquisitions

 Add back interest expense that was deducted because the interest will be accounted for on an issue by issue basis  Add the beginning balance of cash. Even though it seems odd to add the cash balances, these cash balances are available to pay off debt.
 The sum of these items gives the cash flow for the waterfall as illustrated below.
 Cash Flow After Capital Expenditures
 Add: New Debt Issues  Add: New Equity Issues

 Cash Flow before waterfall adjustments
 Add: Total Interest Expense  Add: Beginning Cash Balance

 Cash Flow for Waterfall

Project Finance Modelling

September 09

70

Cash Flow Priorities
• •
•

Once the cash flow for the waterfall is computed, you can compute the defaults on senior and junior debt. Subtract scheduled interest payments and maturities from the cash flow for waterfall
Also subtract attempts to re-pay earlier defaults

•

The difference is cash flow after senior debt that determines default – defaults are the driven by an if statement driven by whether there is negative cash flow.
Any defaults are added to cash flow to determine the cash flow to junior debt
 This step of the waterfall is illustrated below:
 Cash Flow for Waterfall
   Less: Scheduled Repayment Less: Interest on Senior Less: Repayment of earlier defaults Add: Default on Senior Debt Less: Scheduled Repayment Less: Interest on Junior Less: Repayment of earlier default

•




Cash Flow after Senior Debt
   

Cash Flow to Junior Debt

Project Finance Modelling

September 09

71

Cash Flow Traps and Dividends
•
•

After junior debt is evaluated, traps on cash and distributions can be evaluated.
You must subtract the cash balance that was added at the beginning of the waterfall

•

Cash Traps can be evaluated at this point that prevent excess cash going dividends before debt is paid
 This step of the waterfall is illustrated below:
 Cash Flow after Junior Debt
 Add: Default on Junior Debt  Less: Cash Balance Added Above

 Net Cash Flow
 Switch for Trapping Cash  Less: Cash Trapped  Add: Cash Withdrawn from Account

 Dividend Distributions

Project Finance Modelling

September 09

72

Free Cash Flow Adjustments in Project Finance Models • Free cash flow should be the same whether or not IDC was computed:
 Use direct capital expenditures rather than capital expenditures with IDC

 Make adjustments for the tax effect of IDC depreciation. The tax shield from IDC depreciation should not be part of free cash flow and it should be removed.

Project Finance Modelling

September 09

73

Depreciation Expense
• Compute straight line depreciation expense
• Multiply the accumulated plant balance from the balance sheet by the depreciation rate

• More complex depreciation modeling – vintage, accelerated, deferred taxes, multiple categories will be covered later
• Models may have separate pages for capital expenditure and depreciation analysis

Project Finance Modelling

September 09

74

Net Operating Loss
• •

Net operating loss should be part of a reasonable model. If earnings before tax is less than zero and a simple if statement is used, future years do not get credit for the earlier negative taxable income. Therefore, not including NOL will tend to understate value. To model the Net Operating Loss:
 First compute taxes without the NOL which allows negative taxes  Create a cork-screw that keeps track of the beginning balance and the additions and subtractions to the NOL
 The additions occur when there are negative taxes  The subtractions occur when there is positive tax and a balance in the beginning NOL

•

 The taxes paid are the taxes without NOL plus the inputs to the NOL minus the withdrawls from the NOL.

Project Finance Modelling

September 09

75

NOL Example •

The following example illustrates modelling of an NOL
 To model the NOL use the following:
 An if statement the adds to the NOL when the taxes before NOL are positive
 An if statement together with a minimum statement to withdraw from the NOL balance.

Project Finance Modelling

September 09

76

Expiration of NOL •
•

Generally, the NOL expires after a period of years (in the US this is a 6 year period).
To model expiration of the NOL, all you have to do is add another line in the NOL corkscrew:
 Add a line for reductions due to loss of NOL  Use the offset command to model expirations – the offset command with a negative parameter for the column can look back

 The formula only applies after the period of the NOL
 For example in the case of the US, this would be only after year 6 in the model unless you have data on existing NOL’s and how they arose.

Project Finance Modelling

September 09

77

Expiration of NOL •
•

The following example illustrates programming of the NOL with expiration after a certain length of time.
The two examples shows how expiration of the NOL can reduce its benefit if there is volatility in earnings:

Project Finance Modelling

September 09

78

Model Layout

Project Finance Modelling

September 09

79

Open Model
•

Check the logic of returns and debt costs. Project IRR should be above aftertax debt cost.

Project Finance Modelling

September 09

80

Reviewing an Actual Model - Inputs
•

Inputs have different colour. The inputs should be arranged logically.

Project Finance Modelling

September 09

81

Reviewing Model - Workings
•

Workings separate revenues, expenses and capital expenditures. Questions: Capital expenditure detail, explanation of financing and debt service reserve accounts.

Project Finance Modelling

September 09

82

Debt Schedule – Note How Semi-Annual Debt Repayments are Incorporated
• The debt repayments are modelled as period A and period B payments. Note the cork screw for debt analysis. Check the closing balance.

Project Finance Modelling

September 09

83

Sources and Uses - Monthly
•

Begin with uses – EPC and Financing costs that include IDC and other financing costs. Sources are debt and equity. Difference between sources and uses go into cash account.

Project Finance Modelling

September 09

84

Financial Statements during Operation
•

The next sheets include the income statement, cash flow statement and the balance sheet during the operation of the plant.

Project Finance Modelling

September 09

85

Compute DSCR and LLCR
•

The DSCR is computed with and without the amounts in the debt service account. Note the minimum debt service account can be below 1.0

Project Finance Modelling

September 09

86


								
To top