Financial Modelling For Project Finance - 2nd Edition

Drawing on nearly thirty years of real-world experience, Penny Lynch's second edition of her highly-successful workbook provides a detailed description of how to plan and build a pre-financial close Project Finance cash flow model. Providing sufficient theory to give the context for each modelling topic, it focuses on detailed practical methods. Topics covered include treatment of flexible timing assumptions, inflation, multiple currencies, circular calculations, debt and equity calculations, cover factors and IRRs and the use of the model to produce alternative base case scenarios and sensitivity cases. The workbook concludes with a series of exercises which work through the process of building a model from a set of illustrative data.

Financial Modelling for Project Finance, 2nd edition is accompanied by supporting spreadsheets illustrating the methods explained and providing an example solution to the exercises.

This second edition has been completely revised and updated and now reflects revisions to methods and techniques developed by the author during the time since the original edition was published, as well as new topics developed in response to market changes. Reflecting the emphasis on PFI deals in the project finance field, for example, a new section on optimisation covers the theory and practice of optimising revenues and/or funding structures to meet specific constraints such as cover factor and IRR requirements, whilst targetting outcomes such as lowest achievable NPV of project revenues.
The spreadsheets accompanying the workbook have been re-written to reflect the methods and principles in the new edition, and to include the new topics. Where relevant the new edition also describes processes for both 2003 and 2007 versions of Excel. SHOW LESS READ MORE >

1 Introduction
1.1 The need for the model
1.2 Purpose and uses of the model
1.2.1 Initial assessment of feasibility
1.2.2 Determining financing structure and facility amounts
1.2.3 Reflection of developing documentation
1.2.4 Establishing critical issues
1.2.5 Support of ongoing negotiations
1.2.6 Provision of figures for bid submission
1.2.7 Provision of information memorandum figures
1.2.8 Preparation of sensitivity analyses for potential lenders/investors
1.2.9 Use as part of the loan agreement
1.2.10 Use as part of project documentation
1.3 Development over the project life
1.3.1 Feasibility model
1.3.2 Model during project development
1.3.3 Final model for bid submission, raising finance, etc.
1.3.4 How much detail at each stage?
1.3.5 Who will use the model?
1.4 The need for flexibility, robustness and clarity
2 Model design

2.1 Basic principles
2.1.1 Always, sometimes, never
2.1.2 One model for all cases
2.1.3 The benefits of using a consistent basic layout
2.1.4 Data, calculations, results
2.1.5 The ‘Base Case'
2.1.6 Consistent signs
2.1.7 Real and nominal values
2.1.8 Manual calculation
2.1.9 Currency treatment
2.1.10 Circular code
2.1.11 Range names
2.1.12 Off-sheet references
2.2 Maintaining flexibility
2.2.1 Avoiding hard-wiring into formulae and model structure
2.2.2 Using ‘pinch-points'
2.3 The Golden Rules!
2.4 Model layout and structure
2.4.1 Basic structure
2.4.2 The flow of logic through the model
2.5 Basic page layout
2.5.1 Columns
2.5.2 Rows
2.5.3 Including nominal totals
2.6 Timeline
2.6.1 Frequency of model periods
2.6.2 Consistency of timescale within the model
2.6.3 Period included in the timeline
2.6.4 Extending the period covered by the model timeline

3 Handling timings
3.1 Flexible timings
3.1.1 Data inputs to control all timings
3.1.2 Defining the timeline
3.1.3 Positioning events relative to the model timeline
3.2 Increasing the number of time-periods per column
3.3 Reducing timescale within calculations
3.4 Changing timescale for presentation pages

4 Inflation
4.1 Value dates
4.2 Inflation factors
4.2.1 Decompounding
4.3 Average inflation
4.4 Contractual inflation
4.5 Real terms values

5 Controlling choices and options in the model
5.1 The use of switches to control data choices
5.2 The use of switches to control model calculations

6 The use of macros in project finance models
6.1 Why macros are a bad idea
6.1.1 Flexibility
6.1.2 Transparency
6.2 Why macros are a great idea
6.3 Some simple rules about macros in project finance models
6.4 Using macros
6.4.1 Running macros
6.4.2 Stopping macros
6.5 How to add a new macro to a model
6.5.1 Recording macros
6.5.2 Editing macros
6.5.3 Copying macros between models
6.6 Using buttons to run macros

7 Treatment of circular and iterative calculations
7.1 Circularity in project finance calculations
7.2 Drawbacks of circular code
7.2.1 Losing control of the model
7.2.2 Calculation time
7.2.3 ERR propagation
7.2.4 Control of accuracy
7.2.5 Ability to check and audit model
7.3 Avoiding circular calculations
7.3.1 Careful coding
7.4 Handling circular calculations without circular code
7.4.1 Successive approximation
7.4.2 Automating iteration – the recalc macro

8 Currency calculations
8.1 Values nominally fixed in the underlying currency
8.2 Values inflating in the underlying currency
8.3 Timing of adjustments
8.4 Calculation of currency adjustments for loans
8.5 Currency adjustments for tax and accounts
8.6 Flexible currency assignment
8.6.1 Inputs for flexible currency assignment
8.6.2 Flexible currency adjustment for simple costs and revenues
8.6.3 Flexible currency adjustment for loans and deposits

9 Scenario and sensitivity analysis
9.1 Common sensitivity cases
9.2 Use of switches
9.3 Use of strings to automatically identify runs
9.3.1 Operators and functions useful for handling strings
9.4 Case control tables
9.5 Creating a stored library of key values for scenarios and sensitivities
9.6 Creating tables of results for specific sensitivities

10 Cover factors
10.1 CFADS
10.2 Debt service cover ratios
10.2.1 Average debt service cover factors
10.3 NPV loan and project life cover factors
10.4 Including deposits in cover factors

11 Optimisation
11.1 Introduction
11.2 The theory behind the modelling
11.3 Iteration and damping factors
11.4 Optimising revenues
11.4.1 Applying LLCR constraints
11.4.2 Applying ADSCR constraints
11.4.3 Applying IRR constraints
11.4.4 Combining targets
11.4.5 Optimising the debt:equity ratio
11.5 Cost-based tariff calculations

12 The data sheet
12.1 Benefits of keeping input values in one area
12.2 Format and layout within the data sheet
12.3 Contents of data section
12.4 Input categories
12.4.1 Macroeconomic data
12.4.2 Tax and accounting data
12.4.3 Legal fees
12.5 The data validation menu
12.5.1 Protecting formulae on the data sheet
12.5.2 Restricting input values
12.6 Format of supplied data
12.6.1 Wrong frequency
12.6.2 Nominal values
12.6.3 Too complex
12.6.4 Too simple
12.6.5 From documentation
12.7 Confirmation of data values
12.8 Documentation of input data

13 The ‘Work' sheet
13.1 Purpose of worklines
13.2 Purpose of masks
13.3 Purpose of factors
13.4 Purpose of counters
13.5 Uses and calculation of period start and end dates
13.6 Uses and calculation of masks
13.7 Uses and calculation of factors
13.8 Uses and calculation of counters

14 Construction period costs

15 Funding
15.1 Laying out funding calculations on a ‘cascade' basis
15.2 Equity calculations
15.2.1 Equity amount
15.2.1.1 Total equity as an input amount
15.2.1.2 Total equity as a percentage of funding
15.2.2 Equity timing
15.2.2.1 Equity spent first as needed
15.2.2.2 Equity paid in at specified times
15.2.2.3 Equity paid in pro rata to debt
15.3 Loan calculations
15.3.1 Data for loans
15.3.2 Loan calculations
15.3.2.1 Facility size
15.3.2.2 Currency issues for facility size
15.3.2.3 Fees
15.3.2.4 Interest rate
15.3.2.5 Drawings
15.3.2.6 Currency adjustments for drawings
15.3.2.7 Principal repayments
15.3.2.8 Currency adjustments for loan repayments
15.3.2.9 Interest payable
15.3.2.10 Currency adjustments for interest calculations
15.3.2.11 Additional drawings to fund interest
15.3.2.12 Balance outstanding
15.3.2.13 Currency adjustments for loan balance outstanding

16 Operations
16.1 Operating levels
16.2 Input of operations values
16.3 Operating revenues
16.4 Operating costs
16.5 Stocks
16.6 Payables/receivables

17 Tax
17.1 Tax depreciation or capital allowances

18 Profit and loss summary

19 Cash cascade

20 Cash deposits
20.1 DSRA
20.2 MRA
20.3 Cash balance/overdraft

21 Investor returns
21.1 Calculating IRR
21.2 Calculation of specific investor returns
21.3 Calculating an IRR waterfall

22 Cover factor calculations
22.1 Calculating the debt service cover ratios
22.2 NPV loan and project life cover factors
22.2.1 Including deposits in NPV cover factors
22.3 Currency issues when modelling cover factors

23 Net cash flow (NCF) summary

24 Single page summary

25 Investment period sources and uses

26 Balance sheet

27 Model development
27.1 How to start
27.2 Further development of the model
27.2.1 Adding new data choices
27.2.2 Adding additional funding options
27.2.3 Adding timing breakdowns

28 Checking and debugging
28.1 Introduction
28.2 Debugging while developing the model
28.2.1 Debugging tips
28.2.2 Complex formulae
28.2.3 The ‘totals' column
28.2.4 Percentage values
28.2.5 Masks and counters
28.2.6 Inflation
28.2.7 Loan calculations
28.2.8 Pro rata equity
28.2.9 Funding calculation
28.2.10 Operations
28.2.11 Optimised revenues
28.2.12 Tax
28.2.13 Cover factors
28.2.14 Profit and loss
28.2.15 Returns
28.2.16 The net cash flow summary
28.2.17 Balance sheet
28.3 Removing unintentional circularities
28.3.1 Tracing circularity through the model
28.3.2 Checks after identifying rows involving circularity
28.4 Common errors
28.4.1 Units
28.4.2 Timing
28.4.3 Wrong sign
28.4.4 Periods
28.5 Checking the ‘completed' model
28.6 The importance of checking
28.7 Checking output
28.8 Using sensitivities to check the model
28.9 Checking the code
28.10 Documenting the code
28.11 Shadow models

29 Presentation
29.1 The use of graphics

30 Model review and audit
30.1 Reviewing someone else's model

31 Using models written elsewhere
31.1 Working with a third-party model
31.2 Additions and enhancements
31.3 Long-term use
31.4 Procedure

32 Examples and exercises
32.1 Introduction
32.2 Feasibility model
32.2.1 Initial information
32.2.2 Exercise 1
32.2.3 Notes on Exercise 1
32.2.4 Request for further data: sample solution to Exercise 1
32.2.5 Further data
32.2.6 Exercise 2
32.2.7 Notes on Exercise 2
32.2.8 Sample solution for Exercise 2a
32.2.9 Notes and example solution for Exercise 2b
32.3 Onward development
32.3.1 Exercise 3
32.3.2 Notes on Exercise 3
32.3.3 Sample solutions for Exercise 3
32.4 Alternative deal examples
32.4.1 Optimised purchase/offtake arrangements
32.4.2 Calculated subsidy/grant

After working for more than a decade with the project finance teams of two leading banks in the city of London, and for another eighteen years as an independent consultant, Penny Lynch has been involved in the use of spreadsheets for cash flow modelling since its inception in the early 1980s.

Nearly thirty years of subsequent real-world modelling experience has been steadily formalised into a well-defined philosophy and methodology as Penny has concurrently developed and presented a series of international training programs for leading funders, consultants and sponsors. She is ideally placed to understand the practical issues at the heart of project finance modelling, and the practical process of communicating them to new and experienced modellers alike.