Оглавление
Dominic Robertson. See-Through Modelling
Publishing Details
About the author
Contact the author
Preface. What this book covers
Who this book is for
How this book is structured
How to use this book
Introduction. The commercial problem
Operating the asset, after financial close and post-construction
Changing commercial objectives
Limitations of the financial close model
The practicalities of operating a project company
Balancing the interests of the shareholders and lenders
Reinvention of modelling methods
What accountants can and cannot do
Human error
The modelling solution. Different approaches in the modelling market
The right approach
Figure 1: The holistic modelling solution of See-Through Modelling
Lessons learnt from PFI. Use of Microsoft Excel for financial modelling
The strength of the operating model
The cash-centric approach of PFI
Macro-economic indexation
PART 1: THEORY
Chapter 1. Modelling theory. Definition of modelling
A short history of modelling
The four founding principles of modelling
1. A model is a model, not reality
2. A model must be as simple and clear as possible
3. A model must answer the commercial needs of the user
4. As above so below
The modelling mind-set
The body of modelling theory
Class 1: Model structure theory
Organisation
A structural look at the model components
Hierarchy and sheet layout
Figure 2: Business and modelling components allocated across sheets
Model flow
Links
At-source cell referencing
Daisy chains referencing
Figure 3: Example of a daisy chain link and a correct parallel reference
The tree analogy
Figure 4: Tree and root system
Model structure and the tree analogy
Navigating the model and the tree analogy
Figure 5: Trace dependents window
Class 2: Model content theory
Outputs, calculations and inputs
A detailed look at the model components
Figure 6: Model hierarchy, sheets and components
Business components
Modelling components
Formulae in modelling
Event flags and switches
Figure 7: Actual period flag
Figure 8: Switch
Indexation
Class 3: Model control theory
Forecast inputs in column format for running sensitivities
Figure 9: (Forecast) inputs in column format
Tracked outputs sets in column format for audit trail
Figure 10: Track sheet
Points of view
The time dimension
Modellers & managers: how the needs change
Figure 11: Modeller view of model components
Figure 12: Manager view of model components
Overall control of model analysis
Figure 13: Simple control panel
Class 4: Model testing theory
1. Ongoing modeller tests
2. ongoing financial statements tests
3. Tests prior to delivery
4. End user actual update and re-forecast testing
5. Three fundamental financial statements checks
6. End user acceptance testing
7. Auditor tests
Model auditor example of scope arrangement
Model auditor example approach arrangement
Model auditor example reports arrangement
Model auditor example error/query categories
Figure 14: Model review error categories
Model auditor example test – the forecast balance sheet test
Class 5: Model build theory
1. Specification (S)
2. Design (D)
Basic model structure
Business map of the model
3. Build (B) Shadow phase
Step 1 – Update:
Step 2 – Test:
Step 3 – Audit:
4. Test (T)
5. Deliver (V)
1. Shadow delivery (V1)
2. Update delivery (V2)
3. Final delivery (V3)
Non-project finance delivery events
Chapter 2. Finance theory. Characteristics of a project finance PFI deal. The public-private deal and the history of PFI
PFI finance debtor accounting treatment
Finance debtor treatment
Accounting effects of the finance debtor treatment
Modelling implications of the finance debtor treatment
Figure 15: Finance debtor interest rate
Figure 16: Finance debtor principal amortisation
Figure 17: Finance debtor interest revenue
Figure 18: Unitary charge control account (UCCA)
Figure 19: Opex total
Figure 20: Current Period Opex as % of Remaining Project Life Opex
Figure 21: Op Revenue Post-Finance Debtor, pre-Revenue Recognition
Figure 22: Total Operating Revenue over Remaining Project Life
Figure 23: Net Service Income
Figure 24: Op Revenue Post-Finance Debtor
Project finance: interested parties and their needs. The directors
The lenders
The government and the authority
The shareholder
The suppliers
Other parties
The business
The business reporting cycle
1. From invoice codes to management accounts
2. From management accounts to signed off financial statements
3. From compliant cover ratios to distributions
The macro-economy
RPI data from ONS site
Figure 25: RPI data table from ONS Table 20
Flows and balances
Figure 26: Balance corkscrew
Accrued and cash: the idea of working capital
The accrual concept
The cash concept
The working capital concept
Working capital balances created by the delay in payment
Informal definition of the financial statements (for modelling purposes)
The hidden financial statement: the tax calculation
Adjustments to the tax calculation
Effective tax rate
Equation 1: The effective tax rate
Deferred tax
The cash flow waterfall; power at your fingertips and easy navigation
The CFW as the only cash allocation engine
Figure 27: Cash flow waterfall central position in project finance model
The CFW as the central navigation and model entry point
The outline payment priority in the CFW
Banking cover ratios. Banking ratios as a fraction
Equation 2: Banking ratio
The three standard banking cover ratios: ADSCR, LLCR and PLCR
CFADS or cash available for debt service
ADSCR in detail
Equation 3: Annual Debt Service Cover Ratio (ADSCR)
LLCR in detail
Equation 4: Loan Life Cover Ratio (LLCR)
PLCR in detail
Equation 5: Project Life Cover Ratio (PLCR)
Project return
Project cash flows
Figure 28: Typical sign and size of investor cash flows
Internal rate of return
Project IRR
Project IRR values
Investor return calculations
Blended equity return
Reading the project model
How to read the cash flow waterfall
Figure 29: Cash flow totals
Project dimensions
Effective tax rate
Chapter 3. The language of modelling theory
1. The modelling language principles
Simple but no simpler
Transparency
Ordered bite-size pieces of logic
Classification
Row is the minimum transferrable item
Standard size and shape
Keyboard shortcuts
The 2-dimensional approach
Each situation deserves individual attention
Tracked changes
Model checks
Model flow
2. The modelling language rules
Line-item properties: name, units & values
Figure 30: Line-item classification and colour-coding
Line-item classification and colour-coding
Sheet classification and colour-coding
Figure 31: Excel sheet colour-coding
Structure and time are sacred
Live labelling
FAST column structure
Table 1: FAST column structure
Figure 32: FAST sheet and column structure
Column input sheet
Figure 33: Input sheet in column format
At-source referencing
Tracked changes
Figure 34: Track sheet
Model checks
Figure 35: Check sheet
Model flow
Bite-size pieces of logic
Figure 36: Progressive logic example
Calculation sign convention
Number formatting
Table 2: FAST number format definitions
Figure 37: Format Cells window in Excel
3. The modelling sheets
4. The modelling building blocks
1. Sums
Simple sum
Figure 38: Simple sum
Complex sum
Figure 39: Complex sum
Cumulative sum
2. Division & sign-flip. Division without errors
Figure 40: Division without errors
Sign flip
Figure 41: Sign flip
3. Multiplication
Percentage multiplication
Figure 42: Percentage multiplication shown with senior debt interest example
4. Balance corkscrew
Figure 43: The Balance Corkscrew
5. Flags
Single period event flag
Figure 44: Last closing balance flag
Multiple period event flag
Figure 45: Multiple period event flags
Intersection and exclusion event flag
OR event flag
Figure 46: OR event flag
AND event flag
Figure 47: AND event flag
NOT event flag
Figure 48: NOT event flag
6. Dateline
Model period end
Figure 49: Model period end and model period start
Model period start
7. Indexation
Actual + forecast indexation
Figure 50: Actual + forecast indexation
Figure 51: Indexation on time sheet
Rebased index
Figure 52: Rebasing the index to create a multiplicative index factor
Simple indexation
Figure 53: Simple indexation implemented
8. Uplift
% Uplift
Figure 54: % Uplift example
Linear factor
Figure 55: Linear factor example
Compound uplift factor
Figure 56: Compound uplift factor example
9. Choice & lookup. Index and match for scenario choice
Figure 57: MATCH example for scenario choice
Figure 58: INDEX example for scenario choice
VLOOKUP collector to flip vertical inputs
Figure 59: Insurance (Forecast) Inputs in column format
Figure 60: VLOOKUP and error-trapping for VLOOKUP Collector
Data validation (local)
Figure 61: Data validation drop-down list
Figure 62: Data validation on Excel menu
Figure 63: Data validation window and defined source
10. Conditional sums of flows & balances. SUMIF collector for flows to annual periodicity
Figure 64: Semi-annual financial statement line item
Figure 65: Using SUMIF to summarise semi-annual flows to an annual timeline
LOOKUP Collector for balances to annual periodicity
Figure 66: Semi-annual financial statements balance sheet item
Figure 67: Annual financial statements balance sheet item
Forward & backward collector
Figure 68: Forward-looking collector
11. Caps & floors
Simple floor
Figure 69: Simple floor
Floor and positive
Figure 70: Floor and positive
Figure 71: Positive value using MAX
Simple cap
Figure 72: Simple cap
Negative as a Positive
Figure 73: Negative as a positive value (POS)
Capped & floored
Figure 74: Capped & Floored
12. Allocators
Simple allocator
Figure 75: Simple allocator example
Factor allocator
Figure 76: Factored allocator example
Balance allocator
Figure 77: Balance allocator example
13. IRRs & cover ratios
IRR over semi-annual cash flows
Figure 78: Calculating the internal rate of return using the IRR function
Figure 79: Calculating the internal rate of return using the XIRR function
IRR over quarterly cash flows
Double-decker IRR
Figure 80: Internal Rate of Return for double-decker quarterly cash flows
Cover ratio
Figure 81: Cover ratio calculation including average
14. Track, checks & error-trapping. The Check building block
Figure 82: The Check building block
The Track & Check header building block
Figure 83: Track & Check header with no track changes or check errors
Figure 84: Track & Check with one check errors and track changes
Figure 85: Track & Check header
Error trapping building block
Figure 86: Error trapping example
15. Text manipulation & truncation. Three-way text allocation
Figure 87: Three-way text allocation
Standardised word truncation
Figure 88: Text truncation example
The FAST + 2 modelling adaptation
Table 3: FAST + 2 column structure
Figure 89: FAST + 2 sheet and column structure
The benefits of using FAST
Increased transparency
Increased clarity
Increased speed
Increased accuracy
Chapter 4. Excel theory
1. Setup. Basic Excel setup
Excel options
Figure 90: Excel options, accessed by pressing Alt + T + O
Freeze frame
Figure 91: Freeze frame optimal positioning
Font size, type and view settings
VBA and automation
Excel add-ins and dashboards
Gridlines
Negative number and zero format
Decimal places
Calculation mode and circular references
Circular references
Figure 92: Iterative calculations in Calculation options in Excel Options
Figure 93: Excel circular reference warning
Figure 94: Excel calculation options under the Formulas tab
Example circular reference problem
Figure 95: Circular reference example problem
Solution 1: Allow iterative calculations in Excel
Solution 2: Calculate off the previous balance
Figure 96: Solution 2 using the brought forward balance to avoid the circular reference
Solution 3: Rearrange the formula
Figure 97: Solution 3 rearranging the formula to avoid the circular reference
Solution 4: Use macros to break the circularity
Figure 98: Solution 4 using a macro to break the circularity
Figure 99: Solution 4 VBA macro code for copy and paste
Editing directly in cell
Figure 100: Excel advanced options ‘Allow editing directly in cells’
Excel 2003, Excel 2009 and Excel 2010
64-bit operating systems and Excel
Date format
Figure 101: Date format in Region and Language control panel
Figure 102: Date format in Excel
2. Formulae
List of simple everyday formulae
List of more complex formulae to use sparingly
List of formulae to avoid
3. Keyboard shortcuts
4. Navigation
5. Automation
Data validation
Figure 103: Data validation
Figure 104: Data validation setup
VBA macros and project finance
Goal seek
Conditional formatting
Figure 105: No error signal with conditional formatting
Figure 106: Error signal with conditional formatting
Figure 107: Conditional formatting Rules Manager
5. Known Excel issues
Charting tips
Inter-sheet links
File corruption and saving
Formatting (including conditional formatting)
Chapter 5. Computer theory
1. Body posture and eyes
2. PDFs. Reduce PDF file size in Acrobat Professional
3. WinZip
WinZip quick-fire setup
Figure 108: WinZip configuration
WinZip quick fire-usage
4. File naming convention and file control
File-naming convention
File control
5. Backing up and secure file distribution
6. Skype
What is Skype?
Why use Skype?
What do you need to use Skype?
What is internet chat?
How much does it cost?
What happens when I have Skype up and running?
How to avoid unwanted promotional messages that come through Skype
7. VBA Editor
Changing the colour scheme in the VBA Editor
Figure 109: VBA Editor Options
Figure 110: VBA Editor Options
8. Event Driven Analysis
Figure 111: Suggested model folder system for model build
PART 2: PRACTICE
Chapter 6. Building the model. How to use this chapter
Overview. How is a model built?
Figure 112: Rapid application development phases and process
Figure 113: breakdown of a model-building step
The language of the model-building process
Steps
Tasks
Statements & reports
Results
Inputs
Issues
Building blocks
Layout order
Build sequence
Checks
Events
Shorthand key for the model-building process
Steps. The steps to build a PFI model
1. Create the model folder system on your computer (CORE) Objective
Specification and build
2. Define the timeline and periodicity of the new model (CORE) Objective
Specification & build
Figure 114: Model timeframe specification grid
Figure 115: Model periodicity as defined in each sheet header
Figure 116: Time sheet header rows
3. Create the base Excel file for the new model (CORE) Objective
Specification & build
4. Build the Time sheet (CORE) Objective
Specification
Design
Build
Figure 117: Timeline header at the start of the concession
Figure 118: Timeline header at the end of the concession
5. Build the Inputs sheet (CORE) Objective
Specification
Figure 119: Inputs sheet
Design
Build
Results
6. Build the Track and Check sheets and the track/check header (CORE) Objective
Specification
Figure 120: Track and Check header
Design
Build
Results
7. Build the Template sheet (CORE) Objective
Specification & build
8. Create Operations, Accounting, Finance, Tax and Financial Statements sheets (CORE) Objective
Specification & build
9. Build the place-holding line-items into the financial statements (CORE) Objective
Specification & build
10. Define the profit & loss statement (CORE) Objective
Specification & build
Figure 121: Profit & loss statement example
11. Define the cash flow waterfall (CORE) Objective
Specification & build
Figure 122: Cash flow statement example
12. Define the balance sheet (CORE) Objective
Specification & build
Figure 123: Balance sheet statement example
13. Define the tax statement (CORE) Objective
Specification & build
Figure 124: Tax statement example
14. Build nine important flags as place-holders in the Time sheet (CORE) Objective
Specification & build
Table 4: Properties of an example concession flag
15. Build place-holder indexation in the Time sheet (CORE) Objective
Specification
Design
Build
Results
16. Build the Actuals sheet (CORE) Objective
Specification
Design
Build
Results
17. Add the financial statement sheet values from the FC model in the actual periods of the new Actuals sheet (CORE) Objective
Specification
Design
Build
Results
18. Build the Inputs_C sheet and populate it with the last closing balance sheet from the Actuals sheet (CORE) Objective
Specification
Design
Build
Figure 125: Inputs_C last closing balance sheet structure
Results
19. Build retained earnings balance and retained cash balance in the Finance sheet (CORE) Objective
Specification
Design
Build
Results
20. Create FS_Ref and FS_Diff sheets and populate the FS_Ref sheet with the values from the FC model (CORE) Objective
Specification
Design
Build
Results
21. Specify, design and build the operating revenues (CORE) Objective
Specification
Figure 126: Operating revenues formula syntax
Design
Build
Results
22. Specify, design and build the operating revenues received (CORE) Objective
Specification
Figure 127: Operating revenues received and trade debtors balance
Issues
Design
Build
Results
23. Paint the revenues into the statements (CORE) Objective
Specification
Design
Build
Results
24. Specify, design and build the operating costs (CORE) Objective
Specification
Design
Build
Results
25. Specify, design and build the operating costs paid (CORE) Objective
Specification
Issues
Design
Build
Results
26. Specify, design and build the capital expenditure (CORE)
27. Specify, design and build the life cycle expenditure (CORE) Objective
Specification
Design
Build
Results
Other matters. Sub-results, accounting and working capital effect
Accounting effect
Working capital effect
28. Specify, design and build the maintenance reserve account (CORE) Objective
Specification
Design
Build
Results
29. Specify, design and build insurance prepayments (CORE) Objective
Specification
Design
Build
Results
30. Specify, design and build VAT (CORE) Objective
Specification
Design
Build
Results
31. Specify, design and build capitalised interest and fees amortisation (CORE) Objective
Specification
Design
Build
Results
32. Specify, design and build development costs amortisation. Objective
Specification
Design
Build
Results
33. Specify, design and build issue costs amortisation. Objective
Specification
Design
Build
Results
34. Specify, design and build the term loan repayment (CORE) Objective
Specification
Design
Build
Results
35. Specify, design and build the term loan interest and balance (CORE) Objective
Specification
Design
Build
Results
36. Specify, design and build the debt service reserve account (DSRA) (CORE) Objective
Specification
Design
Build
Results
37. Specify, design and build the subordinated debt (part 1) Objective
Specification
Design
Build
Results
38. Specify, design and build the tax depreciation or writing down allowances (CORE) Objective
Specification
Design
Build
Sub-results
39. Specify, design and build corporation tax (CORE) Objective
Specification
Design
Build
Results
Sub-results
40. Specify, design and build the tax reserve account. Objective
Specification
Design
Build
Results
41. Specify, design and build interest on cash balances (CORE) Objective
Specification
Design
Build
Results
42. Specify, design and build deferred tax (CORE) Objective
Specification
Design
Build
Results
43. Specify, design and build share capital repayment. Objective
Specification
Design
Build
Results
44. Specify, design and build the subordinated debt (part 2) Objective
Specification
Design
Build
Results
45. Specify, design and build dividends (CORE) Objective
Specification
Design
Build
Results
46. Build the actuals into the financial statements (CORE) Objective
Figure 128: Actual + forecast financial statements
Specification
Figure 129: Forecast statements (with last actual closing balance sheet)
Design
Build
Results
47. Deliver the shadow model to the end user (CORE)
48. Update the Actuals sheet in the model (CORE) Objective
Specification
49. Create the Analysis sheet (CORE) Objective
Specification & build
50. Specify, design and build the cover ratios (CORE) Objective
Specification
Design
Build
Results
51. Specify, design and build the project return on the investment. Objective
Specification
Design
Build
Results
52. Specify, design and build the investor return on the investment (CORE) Objective
Specification
Design
Build
Results
53. Complete the build of all the flags in the Time sheet (CORE) Objective
Specification, design & build
Results
54. Complete indexation build (CORE) Objective
Specification
Table 5: Properties of an example index
Design
Build
Results
55. Specify, design and build further reports (CORE)
56. Specify, design and build the Quick Start sheet (CORE)
57. Deliver the updated model to end user (CORE)
58. Add further final updates to the model (CORE)
59. Deliver the final model to the end user (CORE)
Chapter 7. Practical modelling techniques
Techniques for the Shadow phase. 1. Tracking the outputs
2. Define the last closing balance sheet from the FC model as at the end of construction
3. Driving out the inputs
4. Create place holder inputs & clean up the inputs
5. Collect the ingredients
6. Reusing chunks of logic
7. Reusing single line-items
8. Format as you go
9. The one-off balance sheet imbalance
10. Other balance sheet solutions
11. Build the skeleton financial statements
12. Add the links between the financial statements
13. Add the financial statement checks
14. Add the last closing balance sheet
15. Add the FS_Ref and FS_Diff sheets
16. Create an input sheet for the actuals
17. Event flags
18. Build and use a balance corkscrew
19. Paint the exports into the financial statements
20. Add analysis sheet and calculations
21. Add control sheet
22. Add any charts
23. Rebase the FS_Ref and FS_Diff sheets
Techniques for the Update phase. 1. Collecting vertical inputs into horizontal calculation sheets
2. Summarising financial statements into annual time-buckets
3. Solutions to common modelling problems. Allow for errors and use error-trapping
Avoid circularities with an IF statement
Avoid DIV#/0 errors by first checking the value of the denominator with an IF statement
4. Update with new actuals
Chapter 8. Maintaining the investment. Design, application and maintenance
Responding to the quarterly update and ad-hoc analysis
Responding to the need to properly specify new logic/functionality
Model control & model naming convention
Running sensitivities
Links and model interface issues
Chapter 9. Lessons from PFI. Lessons from PFI and structured finance
Successful adaptations to other industries
Topics for further study
APPENDIX 1. Links and references. The book’s website
Lazuli Solutions, innovative modelling solutions for business decision makers
FAST modelling standard
Other useful links
APPENDIX 2. Dictionary of terminology, units & abbreviations. Modelling terminology. Modelling units
Glossary
APPENDIX 3. Keyboard shortcuts
APPENDIX 4. Excel functions. List of simple everyday formulae
List of more complex formulae to consider before using
List of formulae to avoid
APPENDIX 5. Business map or Quick Start sheet
APPENDIX 6. The tree analogy
Lazuli Solutions
Approach
Experience. Lazuli Solutions
PFI
Get in touch with Lazuli Solutions
