Corporate and Project Finance Modeling

Corporate and Project Finance Modeling

by Edward Bodmer
Released November 2014
Publisher(s): Wiley
ISBN: 9781118854365

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

A clear and comprehensive guide to financial modeling and valuation with extensive case studies and practice exercises

Corporate and Project Finance Modelingtakes 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.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Preface
  5. Acknowledgments
  6. Part I: Financial Modeling Structure and Design
    1. Chapter 1: Financial Modeling and Valuation Nightmares
    2. Chapter 2: Becoming a Black Belt Modeler
    3. Chapter 3: General Model Objectives of Structuring Transactions, Risk Analysis, and Valuation
    4. Chapter 4: The Structure of Alternative Financial Models
      1. Structure of a Corporate Model: Incorporating Historyand Deriving Forecasts from Historical Analysis
      2. Use of the INDEX Function in Corporate Models
      3. Easing the Pain of Acquiring PDF Data
      4. Structure of a Project Finance Model That Accounts for Different Risks in Different Phases over the Life of a Project
      5. Reconciliation of Internal Rate of Return in Project Finance with Return on Investment in Corporate Finance
      6. Structure of an Acquisition Model: Alternative Transaction Prices and Financing Terms
      7. Structure of an Integrated Merger Model: Forecasting Earnings per Share
    5. Chapter 5: Avoiding Bad Programming Practices and Creating Effective Auditing Processes
      1. How to Make Financial Models More Efficient and Accurate
    6. Chapter 6: Developing and Efficiently Organizing Assumptions
      1. Assumptions in Demand-Driven Models versus Supply-Driven Models: The Danger of Overcapacity in an Industry
      2. Creating a Flexible Input Structure for Model Assumptions
      3. Alternative Input Structures for Project Finance and Corporate Finance Models
      4. Setting Up Inputs with Code Numbers and the INDEX Function
    7. Chapter 7: Structuring Time Lines
      1. Timing in Corporate Finance Models: Distinguishing the Historical Period, Explicit Period, and Terminal Period
      2. Development to Decommissioning: Phases in the Life of a Project Finance Model
      3. Timing in Acquisition Models: Separating the Transaction Period, the Holding Period, and the Exit Period
      4. Structuring a Time Line to Measure History, Explicit Periods, and Terminal Periods in Corporate Models and Risk Phases in Project Finance Models
      5. Computing Start of Period and End of Period Dates
      6. TRUE and FALSE Switches in Modeling Time Periods
      7. Computing the Age of a Project in Years on a Monthly, Quarterly, or Semiannual Basis
      8. The Magic of a HISTORIC Switch in a Corporate Model
      9. Transferring Data from a Corporate Model to an Acquisition Model Using MATCH and INDEX Functions
    8. Chapter 8: Projecting Revenues, Expenses, and Capital Expenditures to Derive Pretax Cash Flow
      1. Transparent Calculations of Pretax Cash Flow
      2. Inflation and Growth Rates in Calculations of Pretax Cash Flow
      3. Valuation Analysis from Prefinancing, Pretax Cash Flow
    9. Chapter 9: Moving from Pretax Cash Flow to After-Tax Free Cash Flow
      1. Working Capital Analysis
      2. Problems in Computing Depreciation Expense in Corporate Models Involving Asset Retirements
      3. Portfolios of Assets with a Vintage Process
      4. Accounting for Asset Retirements in Corporate Models
      5. Alternative Methods for Deriving Retirements Associated with Existing Assets in Corporate Models
      6. Depreciation Issues in Project Finance Models
      7. Modeling the Change in Deferred Taxes in Corporate Models
      8. Adjusting the Tax Basis in an Acquisition
    10. Chapter 10: Adding Debt to a Corporate or Project Finance Model by Programming Cash Flow Waterfalls
      1. Adding the Debt Schedule to a Financial Model
      2. Modeling Scheduled Debt Repayments
      3. Connecting Debt to Cash Flow in Corporate Models
      4. With a Structured Process, You Can Model Any Cash Flow Waterfall
      5. Defaults on Debt and Measuring the Debt Internal Rate of Return
      6. Assessing Risk and Return Characteristics of Subordinated Debt
    11. Chapter 11: Alternative Calculations of Equity Distributions
      1. Modeling Dividend Distributions
      2. Computing a Target Capital Structure through Simulating New Equity Issues and Buybacks
    12. Chapter 12: Putting Together Financial Statements and Calculating Income Taxes
      1. Computation of Taxes Paid and Taxes Deferred
      2. Cash Flow Statement and Balance Sheet
  7. Part II: Analyzing Risks with Financial Models
    1. Chapter 13: Risk Assessment
      1. Six Alternative Ways to Assess the Risk of a Company, a Project, or a Contract
      2. Using Direct Risk Assessment to Measure Cash Flow and Financial Ratios
    2. Chapter 14: Defining, Describing, and Assessing Risk in a Risk Allocation Matrix
    3. Chapter 15: Presentation of Risk Analysis through Adding Sensitivity Analysis to Financial Models
      1. Setting Up Data for Making Graphs by Converting Periodic Data into Annual, Semiannual, or Quarterly Data
      2. Using the INDIRECT Function to Automate Conversion to Time Period Data
      3. Making Flexible Graphs for Sensitivity Analysis
    4. Chapter 16: Using Financial Models to Establish Break-Even Points for Key Input Variables with Data Tables
      1. Establishing Break-Even Criteria When Analyzing Financial Models
      2. Mechanics of Using Data Tables to Compute Break-Even Points Automatically
      3. Creating Data Tables Using VBA Instead of the Data Table Tool
      4. Summary of Break-Even Analysis
    5. Chapter 17: Constructing Flexible Scenario Analysis for Risk Assessment
      1. Mechanics of Scenario Analysis
      2. Using VBA Code to Create a Scenario Analysis
      3. Getting the Best of Both Worlds: Creating a Special Custom Scenario That Allows Use of Spinner Buttons and Drop-Down Boxes
    6. Chapter 18: Generating Tornado Diagrams, Spider Charts, and Waterfall Graphs
      1. Tornado Diagrams That Display Which Variables Have the Largest Effect on Value and Which Variables Have the Least Effect on an Output Variable
      2. Creating a Tornado Diagram by Extending Scenario Analysis
      3. Creating a Tornado Diagram Using a Two-Way Data Table
      4. Spider Diagrams That Illustrate How Each Range in Input Variables Affects an Output Variable
      5. How to Create a Spider Diagram Using a Two-Way Data Table
      6. Presenting Sensitivity Analysis with a Waterfall Chart
    7. Chapter 19: Adding Probabilistic Risk Analysis and Time Series Equations to Financial Models
      1. Definition of Some Terms for Adding Stochastic Analysis to Your Financial Models
      2. Using Probability Distributions with Spreadsheet Functions Rather Than Equations with Greek Letters
    8. Chapter 20: Taking the Mystery out of Applying Time Series Analysis and Monte Carlo Simulation in Financial Models
      1. Step-by-Step Procedure to Incorporate a Monte Carlo Simulation into Your Models
    9. Chapter 21: Constructing Probability Distributions with Trends, Mean Reversion, Price Boundaries, and Correlations among Variables
      1. Starting Point for Developing Time Series Equations—Brownian Motion and Normal Distributions
      2. Testing the Assumption That Input Variables Are Normally Distributed
      3. Price Boundaries and Short-Run Marginal Cost
      4. Mean Reversion and Long-Run Equilibrium Analysis
      5. Modeling Correlations among Variables in Time Series Equations
    10. Chapter 22: The Difficult Problem of Estimating Volatility, Mean Reversion, Time Trends, Correlations, and Price Boundaries from Historical Data or Market Data
      1. Calculation of Volatility from a Random Walk Process
      2. Attempting to Measure the Presence of Mean Reversion in Historical Data
      3. Attempting to Measure the Presence of Mean Reversion by Evaluating Changes in Periodic Volatility
      4. Risk Analysis Summary
  8. Part III: Advanced Corporate Modeling: Modeling Terminal Value with Stable Ratios in the Discounted Cash Flow Model, Deriving Implied Multiples, and Computing the Bridge between Equity Value and Enterprise Value
    1. Chapter 23: Overview of Issues When Computing Normalized Cash Flow and Terminal Value
    2. Chapter 24: Computing the Return on Invested Capital for Historical and Projected Periods in Corporate Models
      1. Working with a Free Cash Flow Perspective, an Equity Cash Flow Perspective, or Both in Computing Financial Ratios
      2. Presenting Return on Invested Capital in Financial Models
    3. Chapter 25: Calculation of Invested Capital
      1. Dissecting the Financial Structure of a Corporation to Understand the Bridge from Enterprise Value to Equity Value
      2. Drawing an Imaginary Line underneath EBIT to Understand the Financial Structure of a Corporation
      3. Constructing a Long-Term Model to Create Proof of Corporate Finance Concepts
    4. Chapter 26: Complex Items in Balance Sheet Analysis
      1. Treatment of Accumulated Deferred Taxes Arising from Depreciation
      2. Classification of Operating Cash That Produces Interest Income below the EBITDA Line
      3. Treatment of Derivative Assets and Liabilities Depending on How Derivatives Affect EBITDA
    5. Chapter 27: Four General Terminal Value Methods
      1. Method 1: Stable Growth Using the (1 + g)/(WACC – g) Formula
      2. Method 2: Value Driver Method—Incorporating the Return Relative to Cost of Capital in Terminal Value
      3. Method 3: Use of Multiples from Comparative Analysis
      4. Method 4: Derived Multiple Formula
    6. Chapter 28: Terminal Value and Philosophy
      1. Computing Transition Periods Using Compound Growth Rates and Switch Variables
      2. Computing Explicit Period Cash Flow and Terminal Value with Different Starting and Ending Points
      3. Computing Value with Changing Weighted Average Cost of Capital and a Midyear Convention
    7. Chapter 29: Normalizing Terminal Year Cash Flows for Stable Working Capital Investment
      1. Effect of Changes in Growth on Working Capital Investment, Capital Expenditures, Depreciation, and Deferred Taxes
      2. Developing a Simple Equation for Normalizing Working Capital
      3. Incorporating Terminal Period Normalized Cash Flow in a Corporate Model
    8. Chapter 30: Relationship of Growth, Capital Expenditures, Depreciation, and Return on Investment
      1. The Long-Term Stable Ratio of Capital Expenditures to Depreciation and the Ratio of Depreciation Expense to Net Plant
      2. Computing the Ratio of Capital Expenditures to Depreciation When Historical Growth Differs from Prospective Growth
      3. Computing the Ratio of Capital Expenditures to Depreciation
      4. Implementing the Stable Ratio of Capital Expenditures to Depreciation in Valuation Analysis
    9. Chapter 31: Computing Normalized Deferred Tax Changes
      1. Stable Ratio of Deferred Tax to Capital Expenditure without Change in Growth Rate
      2. Normalized Deferred Tax with Change in Growth Rate
    10. Chapter 32: Terminal Value and the Ability of a Company to Earn Returns above the Cost of Capital
      1. The Myth of Convergence of Return on Capital to Cost of Capital
    11. Chapter 33: Errors and Distortions in Applying the Value Driver Formula
      1. Deriving the Value Driver Formula for the Price/EarningsRatio and Equity Value
      2. Deriving Implicit Assumptions about the Progression of the Incremental Return on Equity in the Equity-Based Value Driver Formula
      3. Deriving the Value Driver Formula Using the Return on Invested Capital and the Weighted Average Cost of Capital
      4. Biases in the Value Driver Formula in a Case with Only Working Capital
      5. Problems of the Value Driver Formula When Invested Capital Includes Net Plant
    12. Chapter 34: Computing Implied Price/Earnings Ratios for Use in Terminal Value Calculations
      1. Model for Deriving the P/E Ratio from Value Drivers
    13. Chapter 35: Computing an Implied EV/EBITDA Ratio in Terminal Value Calculations
      1. Simulation Model to Derive Implied EV/EBITDA Ratio from Invested Capital with Constant Growth
      2. Function to Derive Implied EV/EBITDA Ratio
      3. Comprehensive Analysis to Derive Implied EV/EBITDARatio with Changing Growth, Deferred Taxes, and Working Capital
    14. Chapter 36: Developing Value Drivers for P/E and EV/EBITDA Ratios with Benchmarking and Regression
      1. Benchmarking Multiples to Derive Cost of Capital
      2. Downloading Data for a Sample of Companies from the Internet into a Spreadsheet
      3. Running Regression Analysis on Financial Data
      4. Advanced Corporate Modeling Summary
  9. Part IV: Complex Issues: Circular References and Other Complex Issues from Financial Structuring in Project Finance and Corporate Finance Models
    1. Chapter 37: Resolving Circular References in Acquisition Models
      1. Circular References and Use of Opening Balances in Annual Models
      2. Alternative Techniques for Solving Circular Reference Logic Problems in Financial Models
      3. Resolution of Circular References from a Cash Flow Sweep Using the Iteration Button
      4. Solving Circular References from Cash Sweeps with Goal Seek and Solver
      5. Solving Basic Circular References from Cash Sweeps with a Horrible Copy and Paste Macro
      6. Solving Circular References Related to a Cash Sweep Using Algebra
      7. Solving Circular References with Functions That Iterate around Equations That Cause the Problem
    2. Chapter 38: Creating a Structured Cash Flow Process in a Corporate Model to Resolve Circular References
      1. Structuring a Corporate Model with a Cash Flow Waterfall
      2. Resolving Circular References in a Corporate Model Using an Iterative User-Defined Function
    3. Chapter 39: Overview of Complex Project Finance Modeling Structuring Issues
      1. Difficult Project Finance Problems: Structuring versus Risk Analysis Elements of a Model
      2. Items in Project Finance Models That Cause Circularity
    4. Chapter 40: Funding Techniques in Project Finance and the Associated Circular Reference Problems
      1. Case 1: No Circular Reference—Pro-Rata Funding, Interest Paid during Construction, and Debt Size from Cash Flow
      2. Case 2: Circular Reference from Pro-Rata Funding with Capitalized Interest or Debt Ratio Input
      3. Case 3: Pro-Rata Funding with Capitalized Fees
      4. Case 4: Cascade with Equity Funded before Debt That Can Be Solved with Backward Induction
      5. Case 5: Bond Financing in a Single Period
    5. Chapter 41: Debt Sculpting in a Project Finance Model
      1. Sculpting Method 1: Use of Solver
      2. Sculpting Method 2: Goal Seek and Algebra
      3. Sculpting Method 3: Net Present Value of Target Debt Service
      4. Sculpting Method 4: Backward Induction
      5. Sculpting Approaches in Complex Cases with Taxes, Debt Service Reserve Accounts, and Interest Income
      6. Solving Difficult Sculpting Problems with User-Defined Functions
    6. Chapter 42: Automating the Goal Seek Process for Annuity and Equal Installment Repayments
      1. Debt Sizing with Level Repayments or Annuity Repayments Using a Goal Seek Macro
      2. Computing Debt Size for Equal Installment Structuring with a User-Defined Function
      3. Computing Debt Size for Annuity Structure with User-Defined Function
    7. Chapter 43: Modeling Debt Service Reserve Accounts
      1. Structuring the Debt Service Reserve Account in a Project Finance Model
      2. Avoiding Circular References in Funding Debt Service Reserve Accounts through Separating Construction Debt from Permanent Debt
      3. Avoiding Circular References Due to Cash Flow Sweeps and the Debt Service Reserve Account
    8. Chapter 44: Modeling Maintenance Reserve Accounts
      1. MRA Case 1: Constant Maintenance Time Period Increments and Level Expenditures
      2. MRA Case 2: Constant Time Period Increments and Changing Expenditures
      3. MRA Case 3: Varying Time Period Increments and Changing Expenditures Using the MATCH Function
    9. Chapter 45: Refinancing and Valuing a Project Given Risk Changes over the Life of a Project
      1. Computed Internal Rate of Return with Changes in Discount Rate over Project Life
      2. Effects of Refinancing on the Value of a Project
      3. Mechanics of Implementing Refinancing into a Project Finance Model
    10. Chapter 46: Covenants and Cash Flow Sweeps in Project Finance Models
      1. Mechanics of Modeling Covenants and Cash Flow Sweeps
    11. Chapter 47: Asset Portfolios, Progress Payments, and Lease Rolls in Real Estate Models
      1. Modeling a Single Real Estate Project
      2. Modeling Multiple Projects That Are Part of a Combined Portfolio with Percent of Time Function
      3. Modeling a Portfolio with the INDEX Function and Data Table Tools
  10. About the Author
  11. About the Website
  12. Index
  13. End User License Agreement

Product information

  • Title: Corporate and Project Finance Modeling
  • Author(s): Edward Bodmer
  • Release date: November 2014
  • Publisher(s): Wiley
  • ISBN: 9781118854365