Edward Bodmer

Corporate and Project Finance Modeling (eBook, PDF)

Theory and Practice

• Format: PDF

Produktbeschreibung

A clear and comprehensive guide to financial modeling and valuation with extensive case studies and practice exercises Corporate and Project Finance Modeling takes a clear, coherent approach to a complex and technical topic. Written by a globally-recognized financial and economic consultant, this book provides a thorough explanation of financial modeling and analysis while describing the practical application of newly-developed techniques. Theoretical discussion, case studies and step-by-step guides allow readers to master many difficult modeling problems and also explain how to build highly structured models from the ground up. The companion website includes downloadable examples, templates, and hundreds of exercises that allow readers to immediately apply the complex ideas discussed. Financial valuation is an in-depth process, involving both objective and subjective parameters. Precise modeling is critical, and thorough, accurate analysis is what bridges the gap from model to value. This book allows readers to gain a true mastery of the principles underlying financial modeling and valuation by helping them to: * Develop flexible and accurate valuation analysis incorporating cash flow waterfalls, depreciation and retirements, updates for new historic periods, and dynamic presentation of scenario and sensitivity analysis; * Build customized spreadsheet functions that solve circular logic arising in project and corporate valuation without cumbersome copy and paste macros; * Derive accurate measures of normalized cash flow and implied valuation multiples that account for asset life, changing growth, taxes, varying returns and cost of capital; * Incorporate stochastic analysis with alternative time series equations and Monte Carlo simulation without add-ins; * Understand valuation effects of debt sizing, sculpting, project funding, re-financing, holding periods and credit enhancements. Corporate and Project Finance Modeling provides comprehensive guidance and extensive explanation, making it essential reading for anyone in the field.

---

Dieser Download kann aus rechtlichen Gründen nur mit Rechnungsadresse in A, B, BG, CY, CZ, D, DK, EW, E, FIN, F, GR, HR, H, IRL, I, LT, L, LR, M, NL, PL, P, R, S, SLO, SK ausgeliefert werden.

Produktdetails

• Verlag: John Wiley & Sons
• Seitenzahl: 624
• Erscheinungstermin: 9. Oktober 2014
• Englisch
• ISBN-13: 9781118854464
• Artikelnr.: 41740090

Autorenporträt

EDWARD BODMER is an experienced financial and economic consultant, trainer, and lecturer. He has conducted many training programs around the world to both large corporations and public programs that have covered project finance, corporate finance, energy analysis, and mergers and acquisitions. Formerly, Bodmer was the Vice President at the First National Bank of Chicago, where he directed analysis of energy loans and also created financial modeling techniques used in advisory projects.

Inhaltsangabe

Preface xvii
Acknowledgments xxiii
Part I Financial Modeling Structure and Design: Structure and Mechanics of
Developing Financial Models For Corporate Finance and Project Finance
Analysis
Chapter 1 Financial Modeling and Valuation Nightmares: Problems That
Financial Models Cannot Solve 3
Chapter 2 Becoming a Black Belt Modeler 9
Chapter 3 General Model Objectives of Structuring Transactions, Risk
Analysis, and Valuation 13
Chapter 4 The Structure of Alternative Financial Models 17
Structure of a Corporate Model: Incorporating History and Deriving
Forecasts from Historical Analysis 21
Use of the INDEX Function in Corporate Models 26
Easing the Pain of Acquiring PDF Data 28
Structure of a Project Finance Model That Accounts for Different Risks in
Different Phases over the Life of a Project 30
Reconciliation of Internal Rate of Return in Project Finance with Return on
Investment in Corporate Finance 33
Structure of an Acquisition Model: Alternative Transaction Prices and
Financing Terms 35
Structure of an Integrated Merger Model: Forecasting Earnings per Share 37
Chapter 5 Avoiding Bad Programming Practices and Creating Effective
Auditing Processes 41
How to Make Financial Models More Efficient and Accurate 44
Chapter 6 Developing and Efficiently Organizing Assumptions 55
Assumptions in Demand-Driven Models versus Supply-Driven Models: The Danger
of Overcapacity in an Industry 55
Creating a Flexible Input Structure for Model Assumptions 60
Alternative Input Structures for Project Finance and Corporate Finance
Models 62
Setting Up Inputs with Code Numbers and the INDEX Function 62
Chapter 7 Structuring Time Lines 67
Timing in Corporate Finance Models: Distinguishing the Historical Period,
Explicit Period, and Terminal Period 67
Development to Decommissioning: Phases in the Life of a Project Finance
Model 69
Timing in Acquisition Models: Separating the Transaction Period, the
Holding Period, and the Exit Period 70
Structuring a Time Line to Measure History, Explicit Periods, and Terminal
Periods in Corporate Models and Risk Phases in Project Finance Models 72
Computing Start of Period and End of Period Dates 73
TRUE and FALSE Switches in Modeling Time Periods 75
Computing the Age of a Project in Years on a Monthly, Quarterly, or
Semiannual Basis 77
The Magic of a HISTORIC Switch in a Corporate Model 78
Transferring Data from a Corporate Model to an Acquisition Model Using
MATCH and INDEX Functions 82
Chapter 8 Projecting Revenues, Expenses, and Capital Expenditures to Derive
Pretax Cash Flow 85
Transparent Calculations of Pretax Cash Flow 85
Inflation and Growth Rates in Calculations of Pretax Cash Flow 88
Valuation Analysis from Prefinancing, Pretax Cash Flow 90
Chapter 9 Moving from Pretax Cash Flow to After-Tax Free Cash Flow 91
Working Capital Analysis 91
Problems in Computing Depreciation Expense in Corporate Models Involving
Asset Retirements 92
Portfolios of Assets with a Vintage Process 94
Accounting for Asset Retirements in Corporate Models 99
Alternative Methods for Deriving Retirements Associated with Existing
Assets in Corporate Models 103
Depreciation Issues in Project Finance Models 109
Modeling the Change in Deferred Taxes in Corporate Models 110
Adjusting the Tax Basis in an Acquisition 111
Chapter 10 Adding Debt to a Corporate or Project Finance Model by
Programming Cash Flow Waterfalls 113
Adding the Debt Schedule to a Financial Model 114
Modeling Scheduled Debt Repayments 116
Connecting Debt to Cash Flow in Corporate Models 117
With a Structured Process, You Can Model Any Cash Flow Waterfall 119
Defaults on Debt and Measuring the Debt Internal Rate of Return 124
Assessing Risk and Return Characteristics of Subordinated Debt 127
Chapter 11 Alternative Calculations of Equity Distributions 131
Modeling Dividend Distributions 132
Computing a Target Capital Structure through Simulating New Equity Issues
and Buybacks 136
Chapter 12 Putting Together Financial Statements and Calculating Income
Taxes 139
Computation of Taxes Paid and Taxes Deferred 140
Cash Flow Statement and Balance Sheet 144
Part II Analyzing Risks With Financial Models: Sensitivity Analysis,
Scenario Analysis, Break-Even Analysis, Time Series, and Monte Carlo
Simulation
Chapter 13 Risk Assessment: The Centerpiece of All Valuation, Contracting,
and Credit Issues in Finance 149
Six Alternative Ways to Assess the Risk of a Company, a Project, or a
Contract 151
Using Direct Risk Assessment to Measure Cash Flow and Financial Ratios 154
Chapter 14 Defining, Describing, and Assessing Risk in a Risk Allocation
Matrix 159
Chapter 15 Presentation of Risk Analysis through Adding Sensitivity
Analysis to Financial Models 165
Setting Up Data for Making Graphs by Converting Periodic Data into Annual,
Semiannual, or Quarterly Data 167
Using the INDIRECT Function to Automate Conversion to Time Period Data 172
Making Flexible Graphs for Sensitivity Analysis 173
Chapter 16 Using Financial Models to Establish Break-Even Points for Key
Input Variables with Data Tables 185
Establishing Break-Even Criteria When Analyzing Financial Models 188
Mechanics of Using Data Tables to Compute Break-Even Points Automatically
193
Creating Data Tables Using VBA Instead of the Data Table Tool 201
Summary of Break-Even Analysis 205
Chapter 17 Constructing Flexible Scenario Analysis for Risk Assessment 207
Mechanics of Scenario Analysis 210
Using VBA Code to Create a Scenario Analysis 221
Getting the Best of Both Worlds: Creating a Special Custom Scenario That
Allows Use of Spinner Buttons and Drop-Down Boxes 223
Chapter 18 Generating Tornado Diagrams, Spider Charts, and Waterfall Graphs
231
Tornado Diagrams That Display Which Variables Have the Largest Effect on
Value and Which Variables Have the Least Effect on an Output Variable 232
Creating a Tornado Diagram by Extending Scenario Analysis 234
Creating a Tornado Diagram Using a Two-Way Data Table 242
Spider Diagrams That Illustrate How Each Range in Input Variables Affects
an Output Variable 246
How to Create a Spider Diagram Using a Two-Way Data Table 247
Presenting Sensitivity Analysis with a Waterfall Chart 250
Chapter 19 Adding Probabilistic Risk Analysis and Time Series Equations to
Financial Models 253
Definition of Some Terms for Adding Stochastic Analysis to Your Financial
Models 256
Using Probability Distributions with Spreadsheet Functions Rather Than
Equations with Greek Letters 258
Chapter 20 Taking the Mystery out of Applying Time Series Analysis and
Monte Carlo Simulation in Financial Models 263
Step-by-Step Procedure to Incorporate a Monte Carlo Simulation into Your
Models 266
Chapter 21 Constructing Probability Distributions with Trends, Mean
Reversion, Price Boundaries, and Correlations among Variables 277
Starting Point for Developing Time Series Equations-Brownian Motion and
Normal Distributions 279
Testing the Assumption That Input Variables Are Normally Distributed 281
Price Boundaries and Short-Run Marginal Cost 285
Mean Reversion and Long-Run Equilibrium Analysis 286
Modeling Correlations among Variables in Time Series Equations 289
Chapter 22 The Difficult Problem of Estimating Volatility, Mean Reversion,
Time Trends, Correlations, and Price Boundaries from Historical Data or
Market Data 295
Calculation of Volatility from a Random Walk Process 296
Attempting to Measure the Presence of Mean Reversion in Historical Data 297
Attempting to Measure the Presence of Mean Reversion by Evaluating Changes
in Periodic Volatility 300
Risk Analysis Summary 303
Part III Advanced Corporate Modeling: Modeling Terminal Value With Stable
Ratios In the Discounted Cash Flow Model, Deriving Implied Multiples, and
Computing the Ridge Between Equity Value and Enterprise Value
Chapter 23 Overview of Issues When Computing Normalized Cash Flow and
Terminal Value 307
Chapter 24 Computing the Return on Invested Capital for Historical and
Projected Periods in Corporate Models 313
Working with a Free Cash Flow Perspective, an Equity Cash Flow Perspective,
or Both in Computing Financial Ratios 314
Presenting Return on Invested Capital in Financial Models 316
Chapter 25 Calculation of Invested Capital 321
Dissecting the Financial Structure of a Corporation to Understand the
Bridge from Enterprise Value to Equity Value 323
Drawing an Imaginary Line underneath EBIT to Understand the Financial
Structure of a Corporation 326
Constructing a Long-Term Model to Create Proof of Corporate Finance
Concepts 328
Chapter 26 Complex Items in Balance Sheet Analysis: Deferred Taxes,
Operating Cash, and Derivative Assets 337
Treatment of Accumulated Deferred Taxes Arising from Depreciation 337
Classification of Operating Cash That Produces Interest Income below the
EBITDA Line 341
Treatment of Derivative Assets and Liabilities Depending on How Derivatives
Affect EBITDA 344
Chapter 27 Four General Terminal Value Methods 347
Method 1: Stable Growth Using the (1 + g)/(WACC - g) Formula 349
Method 2: Value Driver Method-Incorporating the Return Relative to Cost of
Capital in Terminal Value 351
Method 3: Use of Multiples from Comparative Analysis 352
Method 4: Derived Multiple Formula 353
Chapter 28 Terminal Value and Philosophy: Company Growth Rates and Overall
Economic Growth 357
Computing Transition Periods Using Compound Growth Rates and Switch
Variables 359
Computing Explicit Period Cash Flow and Terminal Value with Different
Starting and Ending Points 362
Computing Value with Changing Weighted Average Cost of Capital and a
Midyear Convention 365
Chapter 29 Normalizing Terminal Year Cash Flows for Stable Working Capital
Investment 369
Effect of Changes in Growth on Working Capital Investment, Capital
Expenditures, Depreciation, and Deferred Taxes 370
Developing a Simple Equation for Normalizing Working Capital 371
Incorporating Terminal Period Normalized Cash Flow in a Corporate Model 375
Chapter 30 Relationship of Growth, Capital Expenditures, Depreciation, and
Return on Investment 377
The Long-Term Stable Ratio of Capital Expenditures to Depreciation and the
Ratio of Depreciation Expense to Net Plant 378
Computing the Ratio of Capital Expenditures to Depreciation When Historical
Growth Differs from Prospective Growth 385
Computing the Ratio of Capital Expenditures to Depreciation 390
Implementing the Stable Ratio of Capital Expenditures to Depreciation in
Valuation Analysis 393
Chapter 31 Computing Normalized Deferred Tax Changes 399
Stable Ratio of Deferred Tax to Capital Expenditure without Change in
Growth Rate 400
Normalized Deferred Tax with Change in Growth Rate 404
Chapter 32 Terminal Value and the Ability of a Company to Earn Returns
above the Cost of Capital 407
The Myth of Convergence of Return on Capital to Cost of Capital 408
Chapter 33 Errors and Distortions in Applying the Value Driver Formula 415
Deriving the Value Driver Formula for the Price/Earnings Ratio and Equity
Value 416
Deriving Implicit Assumptions about the Progression of the Incremental
Return on Equity in the
Equity-Based Value Driver Formula 418
Deriving the Value Driver Formula Using the Return on Invested Capital and
the Weighted Average Cost of Capital 425
Biases in the Value Driver Formula in a Case with Only Working Capital 427
Problems of the Value Driver Formula When Invested Capital Includes Net
Plant 432
Chapter 34 Computing Implied Price/Earnings Ratios for Use in Terminal
Value Calculations 435
Model for Deriving the P/E Ratio from Value Drivers 438
Chapter 35 Computing an Implied EV/EBITDA Ratio in Terminal Value
Calculations 445
Simulation Model to Derive Implied EV/EBITDA Ratio from Invested Capital
with Constant Growth 446
Function to Derive Implied EV/EBITDA Ratio 448
Comprehensive Analysis to Derive Implied EV/EBITDA Ratio with Changing
Growth, Deferred Taxes, and Working Capital 449
Chapter 36 Developing Value Drivers for P/E and EV/EBITDA Ratios with
Benchmarking and Regression 453
Benchmarking Multiples to Derive Cost of Capital 454
Downloading Data for a Sample of Companies from the Internet into a
Spreadsheet 455
Running Regression Analysis on Financial Data 458
Advanced Corporate Modeling Summary 460
Part IV Complex Issues: Circular References and Other Complex Issues From
Financial Structuring In Project Finance and Corporate Finance Models
Chapter 37 Resolving Circular References in Acquisition Models: Computing
Interest Expense on the Average Balance of Debt 465
Circular References and Use of Opening Balances in Annual Models 466
Alternative Techniques for Solving Circular Reference Logic Problems in
Financial Models 468
Resolution of Circular References from a Cash Flow Sweep Using the
Iteration Button 470
Solving Circular References from Cash Sweeps with Goal Seek and Solver 472
Solving Basic Circular References from Cash Sweeps with a Horrible Copy and
Paste Macro 474
Solving Circular References Related to a Cash Sweep Using Algebra 475
Solving Circular References with Functions That Iterate around Equations
That Cause the Problem 479
Chapter 38 Creating a Structured Cash Flow Process in a Corporate Model to
Resolve Circular References 483
Structuring a Corporate Model with a Cash Flow Waterfall 483
Resolving Circular References in a Corporate Model Using an Iterative
User-Defined Function 487
Chapter 39 Overview of Complex Project Finance Modeling Structuring Issues
491
Difficult Project Finance Problems: Structuring versus Risk Analysis
Elements of a Model 493
Items in Project Finance Models That Cause Circularity 495
Chapter 40 Funding Techniques in Project Finance and the Associated
Circular Reference Problems 497
Case 1: No Circular Reference-Pro-Rata Funding, Interest Paid during
Construction, and Debt Size from Cash Flow 499
Case 2: Circular Reference from Pro-Rata Funding with Capitalized Interest
or Debt Ratio Input 501
Case 3: Pro-Rata Funding with Capitalized Fees 506
Case 4: Cascade with Equity Funded before Debt That Can Be Solved with
Backward Induction 508
Case 5: Bond Financing in a Single Period 513
Chapter 41 Debt Sculpting in a Project Finance Model 515
Sculpting Method 1: Use of Solver 517
Sculpting Method 2: Goal Seek and Algebra 519
Sculpting Method 3: Net Present Value of Target Debt Service 521
Sculpting Method 4: Backward Induction 524
Sculpting Approaches in Complex Cases with Taxes, Debt Service Reserve
Accounts, and Interest Income 526
Solving Difficult Sculpting Problems with User-Defined Functions 532
Chapter 42 Automating the Goal Seek Process for Annuity and Equal
Installment Repayments 539
Debt Sizing with Level Repayments or Annuity Repayments Using a Goal Seek
Macro 541
Computing Debt Size for Equal Installment Structuring with a User-Defined
Function 542
Computing Debt Size for Annuity Structure with User-Defined Function 545
Chapter 43 Modeling Debt Service Reserve Accounts 547
Structuring the Debt Service Reserve Account in a Project Finance Model 548
Avoiding Circular References in Funding Debt Service Reserve Accounts
through Separating Construction Debt from Permanent Debt 550
Avoiding Circular References Due to Cash Flow Sweeps and the Debt Service
Reserve Account 552
Chapter 44 Modeling Maintenance Reserve Accounts 555
MRA Case 1: Constant Maintenance Time Period Increments and Level
Expenditures 556
MRA Case 2: Constant Time Period Increments and Changing Expenditures 557
MRA Case 3: Varying Time Period Increments and Changing Expenditures Using
the MATCH Function 559
Chapter 45 Refinancing and Valuing a Project Given Risk Changes over the
Life of a Project 563
Computed Internal Rate of Return with Changes in Discount Rate over Project
Life 563
Effects of Refinancing on the Value of a Project 565
Mechanics of Implementing Refinancing into a Project Finance Model 568
Chapter 46 Covenants and Cash Flow Sweeps in Project Finance Models 571
Mechanics of Modeling Covenants and Cash Flow Sweeps 572
Chapter 47 Asset Portfolios, Progress Payments, and Lease Rolls in Real
Estate Models 577
Modeling a Single Real Estate Project 579
Modeling Multiple Projects That Are Part of a Combined Portfolio with
Percent of Time Function 580
Modeling a Portfolio with the INDEX Function and Data Table Tools 584
About the Author 589
About the Website 591
Index 593