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