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.

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...