Corporate and Project Finance Modeling: Theory and Practice

The comprehensive, step-by-step guide to successful financial modeling Corporate and Project Finance Modeling offers anyone interested in creating, interpreting, and understanding financial models a comprehensive guidebook with real-world applications. Based on Edward Bodmer's years of experience creating, reviewing, and teaching corporate finance modeling, project finance modeling, acquisition modeling, and real estate modeling, this important text explains how to master difficult problems and build highly structured models from the ground up. Step by step, this comprehensive guide shows how to create flexible, efficient, and stable model structures, and contains unique solutions that address complex issues. Filled with essential information and detailed techniques, Corporate and Project Finance Modeling offers those new to the subject as well as experienced professionals a guide for taking their skills to a higher level. Included with the text is a website that offers a wealth of exercises, model examples, video explanations, and case studies. It contains hundreds of customized exercises, many corporate finance and acquisition model templates, and a number of featured example models of business enterprises from a wide variety of industries. Using the examples and information presented in this text will help develop the skills for solving modeling problems and creating effective financial models.

Part 1. Financial Modelling Structure and Design: Structure and Mechanics of Developing Financial Models for Corporate Finance and Project Finance Analysis Chapter 1. Financial Modelling and Valuation Nightmares: Problems that Financial Models Cannot Solve Chapter 2. Becoming a Black Belt Modeller Chapter 3. General Model Objectives of Structuring Transactions, Risk Analysis and Valuation Chapter 4. The Structure of Alternative Financial Models Structure of a Corporate Model to Recount the History and Make Forecasts Use of the INDEX Function in Corporate Models Easing the Pain of Acquiring PDF Data Structure of a Project Finance Model that Accounts for Different Financing Risks in Different Phases over the Life of a Project Reconciliation of IRR in Project Finance with ROI in Corporate Finance Structure of an Acquisition Model: Alternative Transaction Prices and Debt Terms Structure of an Integrated Merger Model: Forecasting Earnings Per Share Chapter 5. Avoiding Bad Programming Practices and Creating Effective Auditing Processes How to Make Financial Modelling More Efficient and Accurate Chapter 6. Developing and Displaying Assumptions: The Most Important Part of a Model Assumptions in Demand Driven Models vs. Supply Driven Models: The Danger of Overcapacity in an Industry Creating a Flexible Structure of Inputs Alternative Input Structures for Project Finance and Corporate Finance Models Setting–up Inputs with Code Numbers and the INDEX Function Chapter 7. Structuring Time Lines and Understanding Changing Risk in Model Phases Timing in Corporate Finance Models: Distinguishing the Historic Period, Explicit Period and Terminal Period Development to Decommissioning: Phases in the Life of a Project Finance Model Timing in Acquisition Models: Separating the Transaction Period, the Holding Period and the Exit Period Structuring a Time Line to Measure History, Explicit Periods, and Terminal Periods in Corporate Models and Risk Phases in a Project Finance Model Computing Start and End Dates for Financial Models TRUE and FALSE Switches in Modelling Time Periods Computing the Age of a Project in Years on a Monthly, Quarterly, or Semi–Annual Basis The Magic of a Historic Switch in a Corporate Model Transferring Data from a Corporate Model to an Acquisition Model Using MATCH and INDEX Functions Chapter 8. Projecting Inflation, Revenues, Expenses and Capital Expenditures in Working Analysis Valuation Analysis from Pre–Financing, Pre–tax Cash Flow Chapter 9. Moving from EBITDA and Capital Expenditures to Free Cash Flow Working Capital Analysis Problems in Computing Depreciation Expense in Corporate Models Involving Retirement Portfolios of Assets with a Vintage Process Unbiased Accounting for Asset Retirements in Corporate Models Alternative Methods for Deriving Retirements Associated with Existing Assets Depreciation Issues in Project Finance Models Modelling the Change in Deferred Taxes in Corporate Models Adjusting the Tax Basis in an Acquisition Chapter 10. Adding Debt to a Corporate or Project Finance Model: Programming Cash Flow Waterfalls Basic Debt Schedules in Any Model Modelling Scheduled Debt Repayments Connecting Debt to Cash Flow in Corporate Model With A Few Steps You Can Model Any Cash Flow Waterfall Defaults on Debt and Measuring the Potential Decline in Debt IRR below the Stated Interest Rate Assessing Risk and Return Characteristics of Subordinated Debt Chapter 11. Alternative Calculations of Equity Distributions Chapter 12. The Last Step in Creating a Financial Model—Putting Together Financial Statements and Calculating Income Taxes Computation of Taxes Paid and Taxes Deferred Cash Flow Statement and Balance Sheet Part 2. Analysing Risks with Financial Models: Sensitivity Analysis, Scenario Analysis, Break–even Analysis, Time Series and Monte Carlo Simulation Chapter 13. Risk Assessment: the Centrepiece of all Valuation, Contacting and Credit Issues in Finance Six Alternative Ways to Assess the Risk of a Company, Project or Contract Using Direct Risk Assessment to Measure Cash Flow and Financial Ratios Chapter 14. Defining, Describing, and Assessing Risk in a Risk Allocation Matrix Chapter 15. Adding Sensitivity Analysis to Financial Models Converting Periodic Data into Annual, Semi–Annual or Quarterly Data Using the INDIRECT Function to Automate Conversion to Time Period Data Making  Flexible Graphs for Sensitivity Analysis Chapter 16. Using Financial Models to Establish Break–Even Points for Key Input Variables with Data Tables Establishing Break–even Criteria when Analyzing Financial Models Mechanics of Using Data Tables to Automatically Compute Break–Even Points Creating Data Tables using VBA Instead of the Data Table Tool Chapter 17. Constructing Flexible Scenario Analysis to Evaluate Transaction Structures Mechanics of Scenario Analysis Using VBA to Create a Scenario Analysis Getting the Best of Both Worlds: Creating a Special Custom Scenario to Test a Model Chapter 18. Generating Tornado and Spider Diagrams Tornado Diagrams that Display which Variables have the Largest Effect on Value and Which Variables have the Least Effect on an Output Variable The Easy Way to Create a Tornado Diagram by Extending Scenario Analysis How you can Create a Tornado Diagram Using a Two Way Data Table Spider Diagrams that Illustrate how Each Input Variable Affects and Output Variable and Presents Results that Look Something like a Spider Web How to Create a Spider Diagrams using a Two–way Data Table Chapter 19. Advantages and Disadvantages of Adding Probabilistic Risk Analysis and Time Series Equations to Financial Models Definition of Some Terms for Adding Stochastic Analysis to Your Financial Models Using Probability Distributions in Practice with Excel Rather than with Confusing Equations with Greek Letters Chapter 20. Taking the Mystery out of Applying Time Series Analysis and Monte Carlo Simulation to Financial Models Chapter 21. Constructing Probability Distributions with Trends, Mean Reversion, Price Boundaries, and Correlations among Variables The Starting Point for Developing Time Series Equations—Brownian Motion and Normal Distributions Testing the Assumption that Input Variables are Normally Distributed Price Boundaries and Short–run Marginal Cost Mean Reversion and Long–Run Equilibrium Analysis Modelling Correlations Among Variables in Time Series Equations Chapter 22. The Difficult Problem of Estimating Volatility, Mean Reversion, Time Trends, Correlations and Price Boundaries from Historic Data and/or Market Data Calculation of Volatility from a Random Walk Processes Attempting to Measure the Presence of Mean Reversion in Historic Data with Statistics Attempting to Measure the Presence of Mean Reversion by Evaluating Changes in Periodic Volatility Risk Analysis Summary Part 3. Advanced Corporate Modelling: Modelling Terminal Value with Stable Ratios in the Discounted Cash Flow Model, Deriving Implied Multiples, and Computing the Bridge between Equity Value and Enterprise Value Chapter 23. Overview of Practical Issues when Computing Terminal Value Chapter 24. Benefits of Carefully Computing the Return on Invested Capital for Historic and Projected Periods in Corporate Models Working with a Free Cash Flow Perspective, an Equity Cash Flow Perspective or Both in Computing Financial Ratios Presenting Return on Invested Capital in Financial Models Chapter 25. Detailed Calculation of Invested Capital Dissecting the Financial Structure of a Corporation to Understand the Bridge from Enterprise Value to Equity Value Drawing an Imaginary Line Underneath EBIT to Understand the Financial Structure of a Corporation Constructing a Long–term Model to Create Proofs of Corporate Finance Concepts Chapter 26. Complex Items in Invested Capital Segregation: Deferred Taxes, Operating Cash and Derivative Assets Treatment of Accumulated Deferred Taxes Arising from Depreciation The Tricky Issue of Operating Cash that Produces Interest Income Below the EBITDA Line Treatment of Derivative Assets and Liabilities Depending on How Derivatives Affect EBITDA Chapter 27. Four General Terminal Value Methods and Debates – Making Independent Valuation Versus Relative Valuations Method 1: Stable Growth Using the (1+g)/(WACC–g) Formula Method 2: Value Driver Method – Incorporating the Return Relative to Cost of Capital in Terminal Value Method 3: Use of Multiples from Comparative Analysis Method 4: Derived Multiple Formula Chapter 28. Terminal Value and Philosophy: Company Growth Rates and Overall Economic Growth Computing Transition Periods using Compound Growth Rates and Switch Variables Computing Explicit Period Cash Flow and Terminal Value with Different Starting and Ending Points Computing Value with Changing WACC and a Midyear Convention Chapter 29. Normalising Terminal Year Cash Flows for Stable Working Capital The Effect of Changes in Growth on Working Capital Investment, Capital Expenditures, Depreciation and Deferred Taxes Developing a Simple Equation for Normalizing  Working Capital Incorporating Terminal Period Normalized Cash Flow in a Corporate Model Chapter 30. Relationship between Growth, Capital Expenditures, Depreciation and Return on Investment The Long–term Stable Ratio of Capital Expenditures to Depreciation and the Ratio of Depreciation Expense to Net Plant Computing the Ratio of Capital Expenditures to Depreciation when Historic Growth Differs from Prospective Growth Computing the Ratio of Capital Expenditures to Depreciation that can be used in Corporate Models Implementing the Stable Ratio of Capital Expenditures to Depreciation in Valuation Analysis Chapter 31. Computing Normalised  Deferred Tax Changes in the Terminal Period Chapter 32. Terminal Value and the Ability of a Company to Earn Returns above the Cost of Capital The Myth of Convergence of Return on Capital to Cost of Capital Chapter 33. The Value Driver Formula: A Solution or a Black Box of Assumptions that are Impossible to Verify Deriving the Value Driver Formula in the Case of the P/E Ratio and Equity Earnings Deriving Implicit Assumptions about the Progression of the Incremental ROE in the Equity Based Value Driver Formula Deriving the Value Driver Formula Using the Return on Invested Capital and the Weighted Average Cost of Capital Biases in the Value Driver Formula from Working Capital and Changes in Working Capital Problems of the Value Driver Formula when Invested Capital includes Net Plant Chapter 34. Computing Implied P/E Ratios for Use in Terminal Value Calculations Differences between the EV/EBITDA Ratio and the P/E Ratio: Depreciation, Taxes and Debt Chapter 35. Computing an Implied EV/EBITDA Ratio in Terminal Value Calculations Chapter 36. Regression Analysis of P/E, EV/EBITDA, and Market to Book Ratio to Incorporate Market Data on Cost of Capital Summary of Part 3 Part 4. 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 Step One in Resolving Circular References: Understanding Why Circular References Arise Circular References from a Cash Flow Sweep using the Iteration Button, Goal Seek, Solver and Copy and Paste Macros Solving Basic Circular References from Cash Sweeps with Goal Seek and Solver Solving Basic Circular References from Cash Sweeps with a Horrible Copy and Paste Macro Solving Basic Circular References in  an Acquisition Model with a Cash Sweep using Algebra Solving Circular References with Functions that Iterate Around Equations that Cause the Problem Chapter 38. Creating a Structured Cash Flow Process in a Corporate Model: Resolving Circular References Associated Debt and Cash Balances Resolving Circular References in a Corporate Model Using the Iterative Function Approach Chapter 39. Overview of Project Finance Modelling Issues: Understanding the Difference in Contract and Debt Structuring versus Risk Analysis Objectives in a Model Difficult Project Finance Problems: Structuring versus Risk Analysis Elements of a Model Items that Cause Circularity Chapter 40. Understanding Funding Techniques in Project Finance and Solving the Circular Reference Problem Case 1: No Circular Reference—Pro–Rata Funding, Interest Paid During Construction and Debt Size from Cash Flow Case 2: Circular Reference from Pro–Rata Funding with Capitalised Interest or Debt Ratio Input Case 3: Pro–Rata Funding with Capitalised Fees Case 4: Cascade with Equity Funded before Debt and Backwards Induction Case 5: Bond Financing in Single Period Chapter 41. Debt Sculpting in a Project Finance Model Sculpting Method 1: Use of Solver Sculpting Method 2: Goal Seek and Algebra Sculpting Method 3: NPV of Debt Service from Target Debt Service Sculpting Method 4: Backwards Induction Sculpting Approaches in Complex Cases with Taxes, DSRA and Interest Income Solving the Difficult Sculpting Problems with User Defined Functions Chapter 42. Functions to Automate the Goal Seek Process for Annuity and Equal Instalment Repayments Debt Sizing with Level Repayments or Annuity Repayments with a Goal Seek Macro Computing Debt Size for Equal Instalment Structuring with a User Defined Function Computing Debt Size for Annuity Structure with User Defined Function Chapter 43. Misery Caused by Debt Service Reserve Accounts: A Few Tricks to Make the Process Easier Avoiding Circular References in Funding DSRA through Separating Construction Debt from Permanent Debt Avoiding Circular References due to Cash Flow Sweeps and the DSRA Chapter 44. Modelling Approaches for Maintenance Reserve Accounts MRA Case 1: Constant Maintenance Time Period Increments and Level Expenditures MRA Case 2: Constant Time Period Increments and Changing Expenditures MRA Case 3: Varying Time Period Increments and Changing Expenditures Using the MATCH Function Chapter 45. Including Re–Financing in a Model and Valuing a Project at Different Assumed Sale Dates Mechanics Implementing of Re–financing into a Project Finance Model Chapter 46. Incorporating Covenants and Cash Flow Sweeps in Project Finance Models Chapter 47. Issues in Real Estate Modelling: Asset Portfolios, Progress Payments and Lease Rolls Modelling a Single Real Estate Project Modelling Multiple Projects that are Part of a Combined Portfolio

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.