book

Corporate and Project Finance Modeling

Name: Corporate and Project Finance Modeling
Author: Edward Bodmer
ISBN: 9781118854365

by Edward Bodmer

November 2014

Intermediate to advanced

624 pages

17h 11m

English

Wiley

Read now

Unlock full access

Cover
Title Page
Copyright
Preface
Acknowledgments
Part I: Financial Modeling Structure and Design
Chapter 1: Financial Modeling and Valuation Nightmares
Chapter 2: Becoming a Black Belt Modeler
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: Incorporating Historyand Deriving Forecasts from Historical AnalysisUse of the INDEX Function in Corporate ModelsEasing the Pain of Acquiring PDF DataStructure of a Project Finance Model That Accounts for Different Risks in Different Phases over the Life of a ProjectReconciliation of Internal Rate of Return in Project Finance with Return on Investment in Corporate FinanceStructure of an Acquisition Model: Alternative Transaction Prices and Financing TermsStructure of an Integrated Merger Model: Forecasting Earnings per Share

Chapter 5: Avoiding Bad Programming Practices and Creating Effective Auditing Processes
How to Make Financial Models More Efficient and Accurate
Chapter 6: Developing and Efficiently Organizing Assumptions
Assumptions in Demand-Driven Models versus Supply-Driven Models: The Danger of Overcapacity in an IndustryCreating a Flexible Input Structure for Model AssumptionsAlternative Input Structures for Project Finance and Corporate Finance ModelsSetting Up Inputs with Code Numbers and the INDEX Function
Chapter 7: Structuring Time Lines
Timing in Corporate Finance Models: Distinguishing the Historical Period, Explicit Period, and Terminal PeriodDevelopment to Decommissioning: Phases in the Life of a Project Finance ModelTiming in Acquisition Models: Separating the Transaction Period, the Holding Period, and the Exit PeriodStructuring a Time Line to Measure History, Explicit Periods, and Terminal Periods in Corporate Models and Risk Phases in Project Finance ModelsComputing Start of Period and End of Period DatesTRUE and FALSE Switches in Modeling Time PeriodsComputing the Age of a Project in Years on a Monthly, Quarterly, or Semiannual BasisThe Magic of a HISTORIC Switch in a Corporate ModelTransferring Data from a Corporate Model to an Acquisition Model Using MATCH and INDEX Functions
Chapter 8: Projecting Revenues, Expenses, and Capital Expenditures to Derive Pretax Cash Flow
Transparent Calculations of Pretax Cash FlowInflation and Growth Rates in Calculations of Pretax Cash FlowValuation Analysis from Prefinancing, Pretax Cash Flow
Chapter 9: Moving from Pretax Cash Flow to After-Tax Free Cash Flow
Working Capital AnalysisProblems in Computing Depreciation Expense in Corporate Models Involving Asset RetirementsPortfolios of Assets with a Vintage ProcessAccounting for Asset Retirements in Corporate ModelsAlternative Methods for Deriving Retirements Associated with Existing Assets in Corporate ModelsDepreciation Issues in Project Finance ModelsModeling the Change in Deferred Taxes in Corporate ModelsAdjusting the Tax Basis in an Acquisition
Chapter 10: Adding Debt to a Corporate or Project Finance Model by Programming Cash Flow Waterfalls
Adding the Debt Schedule to a Financial ModelModeling Scheduled Debt RepaymentsConnecting Debt to Cash Flow in Corporate ModelsWith a Structured Process, You Can Model Any Cash Flow WaterfallDefaults on Debt and Measuring the Debt Internal Rate of ReturnAssessing Risk and Return Characteristics of Subordinated Debt
Chapter 11: Alternative Calculations of Equity Distributions
Modeling Dividend DistributionsComputing a Target Capital Structure through Simulating New Equity Issues and Buybacks
Chapter 12: Putting Together Financial Statements and Calculating Income Taxes
Computation of Taxes Paid and Taxes DeferredCash Flow Statement and Balance Sheet
Part II: Analyzing Risks with Financial Models
Chapter 13: Risk Assessment
Six Alternative Ways to Assess the Risk of a Company, a Project, or a ContractUsing Direct Risk Assessment to Measure Cash Flow and Financial Ratios
Chapter 14: Defining, Describing, and Assessing Risk in a Risk Allocation Matrix
Chapter 15: Presentation of Risk Analysis through Adding Sensitivity Analysis to Financial Models
Setting Up Data for Making Graphs by Converting Periodic Data into Annual, Semiannual, or Quarterly DataUsing the INDIRECT Function to Automate Conversion to Time Period DataMaking 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 ModelsMechanics of Using Data Tables to Compute Break-Even Points AutomaticallyCreating Data Tables Using VBA Instead of the Data Table ToolSummary of Break-Even Analysis
Chapter 17: Constructing Flexible Scenario Analysis for Risk Assessment
Mechanics of Scenario AnalysisUsing VBA Code to Create a Scenario AnalysisGetting the Best of Both Worlds: Creating a Special Custom Scenario That Allows Use of Spinner Buttons and Drop-Down Boxes
Chapter 18: Generating Tornado Diagrams, Spider Charts, and Waterfall Graphs
Tornado Diagrams That Display Which Variables Have the Largest Effect on Value and Which Variables Have the Least Effect on an Output VariableCreating a Tornado Diagram by Extending Scenario AnalysisCreating a Tornado Diagram Using a Two-Way Data TableSpider Diagrams That Illustrate How Each Range in Input Variables Affects an Output VariableHow to Create a Spider Diagram Using a Two-Way Data TablePresenting Sensitivity Analysis with a Waterfall Chart
Chapter 19: Adding Probabilistic Risk Analysis and Time Series Equations to Financial Models
Definition of Some Terms for Adding Stochastic Analysis to Your Financial ModelsUsing Probability Distributions with Spreadsheet Functions Rather Than Equations with Greek Letters
Chapter 20: Taking the Mystery out of Applying Time Series Analysis and Monte Carlo Simulation in Financial Models
Step-by-Step Procedure to Incorporate a Monte Carlo Simulation into Your Models
Chapter 21: Constructing Probability Distributions with Trends, Mean Reversion, Price Boundaries, and Correlations among Variables
Starting Point for Developing Time Series Equations—Brownian Motion and Normal DistributionsTesting the Assumption That Input Variables Are Normally DistributedPrice Boundaries and Short-Run Marginal CostMean Reversion and Long-Run Equilibrium AnalysisModeling Correlations among Variables in Time Series Equations
Chapter 22: The Difficult Problem of Estimating Volatility, Mean Reversion, Time Trends, Correlations, and Price Boundaries from Historical Data or Market Data
Calculation of Volatility from a Random Walk ProcessAttempting to Measure the Presence of Mean Reversion in Historical DataAttempting to Measure the Presence of Mean Reversion by Evaluating Changes in Periodic VolatilityRisk Analysis Summary
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
Chapter 24: Computing the Return on Invested Capital for Historical and Projected Periods in Corporate Models
Working with a Free Cash Flow Perspective, an Equity Cash Flow Perspective, or Both in Computing Financial RatiosPresenting Return on Invested Capital in Financial Models
Chapter 25: Calculation of Invested Capital
Dissecting the Financial Structure of a Corporation to Understand the Bridge from Enterprise Value to Equity ValueDrawing an Imaginary Line underneath EBIT to Understand the Financial Structure of a CorporationConstructing a Long-Term Model to Create Proof of Corporate Finance Concepts
Chapter 26: Complex Items in Balance Sheet Analysis
Treatment of Accumulated Deferred Taxes Arising from DepreciationClassification of Operating Cash That Produces Interest Income below the EBITDA LineTreatment of Derivative Assets and Liabilities Depending on How Derivatives Affect EBITDA
Chapter 27: Four General Terminal Value Methods
Method 1: Stable Growth Using the (1 + g)/(WACC – g) FormulaMethod 2: Value Driver Method—Incorporating the Return Relative to Cost of Capital in Terminal ValueMethod 3: Use of Multiples from Comparative AnalysisMethod 4: Derived Multiple Formula
Chapter 28: Terminal Value and Philosophy
Computing Transition Periods Using Compound Growth Rates and Switch VariablesComputing Explicit Period Cash Flow and Terminal Value with Different Starting and Ending PointsComputing Value with Changing Weighted Average Cost of Capital and a Midyear Convention
Chapter 29: Normalizing Terminal Year Cash Flows for Stable Working Capital Investment
Effect of Changes in Growth on Working Capital Investment, Capital Expenditures, Depreciation, and Deferred TaxesDeveloping a Simple Equation for Normalizing Working CapitalIncorporating Terminal Period Normalized Cash Flow in a Corporate Model
Chapter 30: Relationship of 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 PlantComputing the Ratio of Capital Expenditures to Depreciation When Historical Growth Differs from Prospective GrowthComputing the Ratio of Capital Expenditures to DepreciationImplementing the Stable Ratio of Capital Expenditures to Depreciation in Valuation Analysis
Chapter 31: Computing Normalized Deferred Tax Changes
Stable Ratio of Deferred Tax to Capital Expenditure without Change in Growth RateNormalized Deferred Tax with Change in Growth Rate
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: Errors and Distortions in Applying the Value Driver Formula
Deriving the Value Driver Formula for the Price/EarningsRatio and Equity ValueDeriving Implicit Assumptions about the Progression of the Incremental Return on Equity in the Equity-Based Value Driver FormulaDeriving the Value Driver Formula Using the Return on Invested Capital and the Weighted Average Cost of CapitalBiases in the Value Driver Formula in a Case with Only Working CapitalProblems of the Value Driver Formula When Invested Capital Includes Net Plant
Chapter 34: Computing Implied Price/Earnings Ratios for Use in Terminal Value Calculations
Model for Deriving the P/E Ratio from Value Drivers
Chapter 35: Computing an Implied EV/EBITDA Ratio in Terminal Value Calculations
Simulation Model to Derive Implied EV/EBITDA Ratio from Invested Capital with Constant GrowthFunction to Derive Implied EV/EBITDA RatioComprehensive Analysis to Derive Implied EV/EBITDARatio with Changing Growth, Deferred Taxes, and Working Capital
Chapter 36: Developing Value Drivers for P/E and EV/EBITDA Ratios with Benchmarking and Regression
Benchmarking Multiples to Derive Cost of CapitalDownloading Data for a Sample of Companies from the Internet into a SpreadsheetRunning Regression Analysis on Financial DataAdvanced Corporate Modeling Summary
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
Circular References and Use of Opening Balances in Annual ModelsAlternative Techniques for Solving Circular Reference Logic Problems in Financial ModelsResolution of Circular References from a Cash Flow Sweep Using the Iteration ButtonSolving Circular References from Cash Sweeps with Goal Seek and SolverSolving Basic Circular References from Cash Sweeps with a Horrible Copy and Paste MacroSolving Circular References Related to a Cash Sweep Using AlgebraSolving Circular References with Functions That Iterate around Equations That Cause the Problem
Chapter 38: Creating a Structured Cash Flow Process in a Corporate Model to Resolve Circular References
Structuring a Corporate Model with a Cash Flow WaterfallResolving Circular References in a Corporate Model Using an Iterative User-Defined Function
Chapter 39: Overview of Complex Project Finance Modeling Structuring Issues
Difficult Project Finance Problems: Structuring versus Risk Analysis Elements of a ModelItems in Project Finance Models That Cause Circularity
Chapter 40: Funding Techniques in Project Finance and the Associated Circular Reference Problems
Case 1: No Circular Reference—Pro-Rata Funding, Interest Paid during Construction, and Debt Size from Cash FlowCase 2: Circular Reference from Pro-Rata Funding with Capitalized Interest or Debt Ratio InputCase 3: Pro-Rata Funding with Capitalized FeesCase 4: Cascade with Equity Funded before Debt That Can Be Solved with Backward InductionCase 5: Bond Financing in a Single Period
Chapter 41: Debt Sculpting in a Project Finance Model
Sculpting Method 1: Use of SolverSculpting Method 2: Goal Seek and AlgebraSculpting Method 3: Net Present Value of Target Debt ServiceSculpting Method 4: Backward InductionSculpting Approaches in Complex Cases with Taxes, Debt Service Reserve Accounts, and Interest IncomeSolving Difficult Sculpting Problems with User-Defined Functions
Chapter 42: Automating the Goal Seek Process for Annuity and Equal Installment Repayments
Debt Sizing with Level Repayments or Annuity Repayments Using a Goal Seek MacroComputing Debt Size for Equal Installment Structuring with a User-Defined FunctionComputing Debt Size for Annuity Structure with User-Defined Function
Chapter 43: Modeling Debt Service Reserve Accounts
Structuring the Debt Service Reserve Account in a Project Finance ModelAvoiding Circular References in Funding Debt Service Reserve Accounts through Separating Construction Debt from Permanent DebtAvoiding Circular References Due to Cash Flow Sweeps and the Debt Service Reserve Account
Chapter 44: Modeling Maintenance Reserve Accounts
MRA Case 1: Constant Maintenance Time Period Increments and Level ExpendituresMRA Case 2: Constant Time Period Increments and Changing ExpendituresMRA Case 3: Varying Time Period Increments and Changing Expenditures Using the MATCH Function
Chapter 45: Refinancing and Valuing a Project Given Risk Changes over the Life of a Project
Computed Internal Rate of Return with Changes in Discount Rate over Project LifeEffects of Refinancing on the Value of a ProjectMechanics of Implementing Refinancing into a Project Finance Model
Chapter 46: Covenants and Cash Flow Sweeps in Project Finance Models
Mechanics of Modeling Covenants and Cash Flow Sweeps
Chapter 47: Asset Portfolios, Progress Payments, and Lease Rolls in Real Estate Models
Modeling a Single Real Estate ProjectModeling Multiple Projects That Are Part of a Combined Portfolio with Percent of Time FunctionModeling a Portfolio with the INDEX Function and Data Table Tools
About the Author
About the Website
Index
End User License Agreement

Content preview from Corporate and Project Finance Modeling

Chapter 47Asset Portfolios, Progress Payments, and Lease Rolls in Real Estate Models

Real estate analysis and modeling encompass a combination of aspects from both corporate finance and project finance. The financial model of a hotel project or a commercial building has many similar characteristics to project finance analysis. These investments move through different stages, revenue contracts are important in risk analysis, and the financial analysis is driven by cash flows and internal rates of return (IRRs) rather than reported profits on the income statement. However, hotels and commercial buildings do not have a precise defined life, prospective cash flow analysis relies on rental market history, and the projects are often valued using a flexible terminal date driven by market multiples—characteristics that resemble corporate analysis. Other real estate investments such as residential and commercial mixed development projects have more elements in common with corporate analysis. These mixed developments consist of portfolios that contain several individual projects with different cash flow patterns and different risks. Financing is driven by loan-to-value leverage ratios rather than cash flow coverage ratios such as the debt service coverage ratio and the residual values at some terminal date are central to the valuation. Yet the individual developments that comprise these mixed-use investments do have similarities to project finance analysis. Their value is defined by stages, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781118854365Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Corporate and Project Finance Modeling

by Edward Bodmer

Chapter 47Asset Portfolios, Progress Payments, and Lease Rolls in Real Estate Models

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.