Corporate Valuation Modeling: A Step-by-Step Guide

Acknowledgments.

About the Author.

Chapter 1: Introduction.

Overview of the Corporate Valuation Process.

A Conceptual Road Map.

A Technical Road Map.

A Few Best Practices Regarding Financial Modeling.

How This Book Works.

Model Builder 1.1: Initial Settings and Assumptions Sheet Set Up.

Toolbox: Naming Cells.

Chapter 2: Dates and Timing.

The Need for a Flexible System

The Forecast Period.

The Terminal Period.

Historical Time Periods

Event Timing.

Model Builder 2.1: Dates and Timing on the Assumptions Sheet.

Model Builder 2.2: Introducing the Vectors Sheet.

Summary of Dates and Timing.

Toolbox.

Chapter 3: Revenue, Costs, & the Income Statement.

Revenue.

Model Builder 3.1: Three Methods for Estimating Revenue Based on Historical Data.

Costs.

Organizing Revenue and Cost Assumptions for Scenario Analysis.

Model Builder 3.2: Installing an Excel Based Scenario Selector System.

Bringing Revenues and Costs Together: The Income Statement.

Model Builder 3.3: Integrating the Income Statement.

A Work In Progress.

Toolbox.

Chapter 4: Capital Structure and Balance Sheet.

What the Company Owns.

Model Builder 4.1: Starting the Balance Sheet with Assets.

What the Company Owes.

Model Builder 4.2: Continuing the Balance Sheet with Liabilities.

What the Company Has Already Paid For.

Model Builder 4.3: Continuing the Balance Sheet with Equity.

Toolbox: Be Careful with Growth.

Chapter 5: Capital Expenditures, Depreciation, Intangibles, and Amortization.

Capital Expenditures.

Depreciation.

A Balance Sheet or Income Statement Item?

Concept Status.

Model Builder 5.1: Capital Expenditure Schedules Set Up.

Model Builder 5.2: Depreciation Schedules Set Up.

Intangibles.

Amortization.

Model Builder 5.3: Intangibles and Amortization Schedules.

Income Statement and Balance Sheet Effects.

Model Builder 5.4: Integrating Capital Expenditures, Depreciation, Intangibles, and Amortization.

Toolbox.

Chapter 6: Long Term Debt.

What is Long Term Debt?

Using Debt for a Reason.

Modeling Debt: Debt Components in Detail.

Model Builder 6.1: Setting Up Debt and Calculating What is Due.

Paying Liabilities.

Model Builder 6.2: Paying the Correct Liability Amount.

Model Builder 6.3: Integrating Long Term Debt into the Income Statement and Balance Sheet.

Toolbox.

Chapter 7: Balancing the Model.

Model Builder 7.1: Calculating Cash and Short Term Debt Interest.

Working with the Model.

The Model as an Analysis Tool.

Toolbox: Excel's Calculation Modes.

Chapter 8: Reconciling Cash Flow.

The Cash Flow Statement.

Working Capital.

Model Builder 8.1: Calculating Working Capital.

Model Builder 8.2: Building the Cash Flow Statement.

Preventing Error through Internal Validation.

Model Builder 8.3: Implementing Internal Validations.

Other Validations.

Toolbox.

Chapter 9: Free Cash Flow, Terminal Value, and Discount Rates and Methods.

Free Cash Flow: A Matter of Perspective.

Model Builder 9.1: Implementing Free Cash Flow.

Terminal Value: Beyond the Forecast Period.

Model Builder 9.2: Calculating and Integrating a Stable Growth Terminal Value.

Discount Rates and Methods.

Model Builder 9.3: Calculating and Implementing the Weighted Average Cost of Capital.

Model Builder 9.4: Discounting with Multiple Rates to Determine the Corporate Value.

After the Corporate Valuation.

Toolbox.

Weighted Averages Using SUMPRODUCT and SUM.

Present Value Functions: PV, NPV, XNPV.

Discounting Process with Multiple Rates.

COVAR, VAR, and VARP for Beta Calculation.

Chapter 10: Output Reporting.

Output Summary.

Model Builder 10.1: Preparing for the Output Summary Sheet.

Web Downloads.

Model Builder 10.2: Connecting the Example Model to the Web.

Model Builder 10.3: Creating the Output Summary Sheet.

Charts.

Model Builder 10.4: Creating Dynamic Charts.

Toolbox.

Chapter 11: Automation Using Visual Basic Applications (VBA).

The Object Oriented Programming Language (OOP).

Follow the Rules.

The Visual Basic Editor.

Writing Code: Subroutines and Functions.

Understanding VBA Code and Practicing Coding Techniques.

Model Builder 11.1: Moving Data Using VBA.

Model Builder 11.2: A First Look at Loops and Variables in VBA.

Common Errors for First Time VBA Programmers.

VBA within a Financial Modeling Context.

Model Builder 11.3: Eliminating Circular References.

Model Builder 11.4:  Creating a Scenario Generator.

Model Builder 11.5: Automatic Sheet Printing.

Continuing with VBA.

Conclusion.

About the CD-ROM.

Index.