پارسی   English   العربیه

Corporate Valuation Modeling: A Step-by-Step Guide



"Valuation is regarded as more art than science. Keith has deciphered the complex and technical skills developed over years of investment banking and corporate finance experience into a logical, usable book that I have not seen after years of M&A work. More than a list of formulae, Keith builds the foundations of very robust knowledge of model logic, construction, and customization, while subtly building a library of extremely useful formula combinations and putting the tools in the workshop for you. This is the book I needed ten years ago."—Adam Quinn, Senior Relationship Manager, Professional Services, St. George Institutional andCorporate Bank, and Chief Investment Officer, TAC Capital Investment and Advisory

"This guide enables the user to set out data and assumptions, understand logic, and deliver results. Once you're in control of your model you can apply different scenarios, stress testing, and become the 'go to person' on a deal."—Anthony Ditchburn, Associate Director, Financial Institutions Credit, Wholesale Banking, a division of National Australia Bank Limited

Corporate Valuation Modeling takes you step by step through the process of creating a powerful corporate valuation model. Each chapter skillfully discusses the theory of the concept, followed by Model Builder instructions that inform you of every step necessary to create the template model. Many chapters also include a validation section that shows techniques and implementations that you can employ to make sure the model is working properly. Engaging and informative, this reliable resource:

  • Provides a comprehensive, integrated approach to modeling a corporate entity with the primary goal of determining a firm value

  • Contains a Tool Box section at the end of each chapter that assists those who may be less skilled in Excel techniques and functions

  • Walks you through the full process of constructing a fully dynamic corporate valuation model

KEITH A. ALLMAN is the manager of analytics and modeling at Pearl Street Capital Group. He is also the founder of Enstruct, a quantitative finance training and consulting company. Prior to this, he was a vice president in the Global Special Situations Group at Citigroup. Allman has also worked in Citigroup's Global Securitized Markets division modeling conduit transactions and in MBIA Corporation's Quantitative Analytics group. He is the author of the Wiley titles Modeling Structured Finance Cash Flows with Microsoft Excel and Reverse Engineering Deals on Wall Street with Microsoft Excel. Allman received a master's degree in international affairs with a concentration in finance and banking from Columbia University and dual bachelor degrees from UCLA.



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.


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


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


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.


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.


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.



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.


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.


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.


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.


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.


Model Builder 10.4: Creating Dynamic Charts.


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.


About the CD-ROM.