Corporate Valuation Modeling E-Book

Keith A. Allman

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 436

Veröffentlichungsjahr: 2010

Beschreibung

A critical guide to corporate valuation modeling Valuation is at the heart of everything that Wall Street does.Every day, millions of transactions to purchase or sell companiestake place based on prices created by the activities of all marketparticipants. In this book, author Keith Allman provides you with acore model to value companies. Corporate Valuation Modeling takes you step-by-stepthrough the process of creating a powerful corporate valuationmodel. Each chapter skillfully discusses the theory of the concept,followed by Model Builder instructions that inform you of everystep necessary to create the template model. Many chapters alsoinclude a validation section that shows techniques andimplementations that you can employ to make sure the model isworking properly. * Walks you through the full process of constructing a fullydynamic corporate valuation model * A Tool Box section at the end of each chapter assists readerswho may be less skilled in Excel techniques and functions Complete with a companion CD-ROM that contains constructedmodels, this book is an essential guide to understanding theintricacies of corporate valuation modeling. Note: CD-ROM/DVD and other supplementary materials arenot included as part of eBook file.

Leseprobe

Table of Contents

 

Title Page

Copyright Page

Preface

Acknowledgements

 

CHAPTER 1 - Introduction

 

OVERVIEW OF THE CORPORATE VALUATION PROCESS

CONCEPTUAL ROADMAP

TECHNICAL ROADMAP

A FEW BEST PRACTICES REGARDING FINANCIAL MODELING

HOW THIS BOOK WORKS

MODEL BUILDER 1.1: INITIAL SETTINGS AND ASSUMPTIONS SHEET SETUP

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, and 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 AN INCOME STATEMENT ITEM?

CONCEPT STATUS

MODEL BUILDER 5.1: CAPITAL EXPENDITURE SCHEDULES SETUP

MODEL BUILDER 5.2: DEPRECIATION SCHEDULES SETUP

INTANGIBLES

AMORTIZATION

MODEL BUILDER 5.3: INTANGIBLES AND AMORTIZATION SCHEDULES

INCOME STATEMENT AND BALANCE SHEET EFFECTS

MODEL BUILDER 5.4: INTEGRATING CAPITAL EXPENDITURES, DEPRECIATION, ...

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

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

 

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

About the Author

Index

Download CD/DVD content

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.

For a list of available titles, please visit our Web site at www.WileyFinance.com.

Copyright © 2010 by Keith A. Allman. All rights reserved.

 

Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada.

 

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

 

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

 

Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc., is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration.

 

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

 

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

 

Allman, Keith A., 1977-

Corporate valuation modeling : a step-by-step guide / Keith A. Allman. p. cm.

Includes index.

eISBN : 978-0-470-57937-4

1. Corporations—Valuation. 2. Business enterprises—Valuation. I. Title.

HG4028.V3.A474 2010

332.63’2042—dc22

2009027783

Preface

Another book about financial modeling? You might be rolling your eyes and muttering under your breath, “Why? Aren’t there plenty of books that cover this topic?” Yet, you still chose to look inside and see what this one is about. The motivation behind looking at financial modeling books is most likely related to a desire to learn financial modeling in an easy-to-understand, time-efficient, low-cost manner. However, after poring over a few books with the words Financial Modeling in the title, you might be left feeling like you know more about specific skills and topics, but not a working financial model. Perhaps these books have given you an understanding of how the model should work, but you are confused as to how to practically implement the information provided. Ultimately, an easy-to-understand, integrated analysis still eludes you.

There’s a vast sea of approaches authors take with financial modeling books. Some try to encompass every concept in finance and provide examples of how to implement each concept in Excel. Those are the cookbooks of finance. Introduce a topic, show an Excel example, and then move on to the next topic. Others take a similar approach, but vary the medium. Rather than use Excel, they offer books on financial modeling entirely in code with languages such as VBA or C++. Although many of these books can be highly informative, they often leave it up to the reader to figure out how to connect the individual concepts.

The answer, some say, is books that focus on specific concepts. Rather than covering all possible finance topics, these books hone in on specific areas such as fixed income or derivatives. The problem with many of these books is they often rely too much on delving into the details of the topic and demonstrating formula derivations, instead of dedicating time to showing how to implement the concept. Or, they discuss the implementation and show some screenshots, but fail to provide clear instructions, open functions, and code, much less a complete working model.

To me, the best type of financial modeling book is one that is dedicated to a specific topic within finance, offers multiple examples of implementation, is written in a clear and easy-to-understand manner, and provides a completely integrated example model. There are a few books that have been written in this fashion on topics such as credit risk, interest rates, options, and structured finance, but I find that few have addressed corporate valuation in this manner.

It seems to me that corporate valuation modeling too often gets lumped together in the general financial modeling book category. Since a company encompasses many topics in finance it may seem appropriate to cover all of those topics and then assume that the reader can value the company. Unfortunately, connecting the concepts theoretically and implementing those connections on a computer can be just as hard as understanding the individual concept or computer-based implementation in the first place.

Take depreciation as an example. Some books show how to use Excel’s prebuilt depreciation functions to create a depreciation schedule. Others discuss depreciation concepts. Yet, few show readers how to create the depreciation schedule in a way that is automated with the associated asset’s creation. Further, the prebuilt depreciation functions in Excel need to be turned off so the asset is not overdepreciated depending on the forecast period of the model. Then, once we get the schedule correct, we have to accumulate the depreciation on the balance sheet, remove it from different sections of certain financial statements, and perhaps add it back when dealing with valuation calculations.

This book attempts to address many of these shortfalls by providing a compre hensive, integrated approach to modeling a corporate entity with the primary goal of determining a firm’s value. Theory is introduced to guide the reader along the valua tion process and connect each concept with the prior and future concepts. Along the way, clear, step-by-step instructions are provided that cover every cell of the included example model. No sections are hidden, password protected, or incomplete.

Beyond concept and implementation issues, after teaching courses on corporate valuation modeling hundreds of times, I have also come to realize that an added layer of complexity is the preexisting skill level of readers. Some are very new to finance and Excel, others new to just finance, others new to just Excel, and some are seasoned in both, but wanting to learn more. While the text itself addresses the finance topics and shows an integrated implementation, the Excel skills can be a challenge for some and a bore for others who already know them. For this reason, there is a Toolbox at the end of each chapter that provides additional information on the Excel functions and techniques that are used in the chapter. This way, the text is not full of background knowledge that would bore the intermediate Excel users, but the content is still there for the beginning Excel user to learn more.

I hope that this book is a valuable resource for people new to finance, seasoned professionals engaged in analysis, and experienced executives trying to learn what their junior staff is doing all night long. I also continually strive to improve my books, find the best possible methods to teach, and ensure that every reader learns. If you are confused by any section or topic related to this book or my other books, if you think you may have found an error, or if you just want to discuss finance-related topics, please feel free to review the Books and Blog section of my company’s web site www.enstructcorp.com or personally e-mail me at [email protected].

KEITH A. ALLMAN

Acknowledgments

My father always suggested that I focus on math and quantitative subjects. Early on, I rebelled, thinking he couldn’t be further off topic from what I would do in my career. Given that this is my third book on financial modeling, I suppose I should state that he was right. My mother was less adamant about the subject, but to not acknowledge her would undermine the value of her support even to this day. While on the family track, I should note two more family members who have influenced this work. The first is my sister, who was my academic rival when we were children. That energy fomented the fervor with which I have approached all subjects of interest to this day. The second is my grandfather, who lives and breathes the stock market. I am convinced our conversations subconsciously caused my gravitation toward finance. As for more direct acknowledgments, Susan Jane Brett reviewed the book in detail and offered critical comments that led to revisions and clarifications. Her thoroughness is very much appreciated. Also, all of my corporate valuation class participants over the past three years have contributed to this book through the study of their learning methodologies, the development of the curriculum for their courses, and the critical thought caused by their questions. Finally, I would like to thank all of the staff at John Wiley & Sons who work on my books, especially Bill Falloon, Meg Freeborn, and Mary Daniello.

K.A.A.

CHAPTER 1

Introduction

Corporate valuation modeling consistently proves challenging because it requires a thorough understanding of two bodies of thought that demand disparate skill sets: finance and technology. On the finance side, we must understand fundamental topics such as time value of money, growth rates, debt calculations, and other subjects that blend accounting, economics, and mathematics. In particular, accounting is a subject that corporate valuation analysts must be well versed in because generally a subject that corporate valuation analysts must be well versed in because generally accepted accounting principles (GAAP) or international financial reporting standards (IFRS) need to be followed to make sure analyses are consistent. On the technology side, we must select a program or programming language to utilize and understand the technical functionality of that program well. In many cases, the program is Excel, which requires knowledge of a number of program-specific functions and techniques in order to transfer the financial concepts to an orderly, dynamic analysis. Prior to jumping right to the construction process, we will take a step back and examine the overall process.

OVERVIEW OF THE CORPORATE VALUATION PROCESS

The corporate valuation analysis process itself is quite complex with many moving parts that are intricate to stitch together. Taking a reverse approach, that is, starting with the firm value and tracing back its calculations and components, is a good method of gaining an overview of this process.

Projecting Cash Flow

Figure 1.1 provides a graphical overview of the discounted cash flow valuation process. First, we should establish that we will take a discounted cash flow approach to determining corporate value. Many other methods exist, such as relative valuation and adjusted present value, but the most popular detailed analysis is to discount expected future cash flows.

Discounting expected cash flows is a method used in many areas of finance. Bond pricing, securities analysis, and project valuation all use discounted cash flow techniques. Any discounted cash flow technique has two general components: future expected cash flows and a rate or rates to discount those cash flows to bring them to the present value. The sum of all present-valued cash flows is the . So the path we first go down is making sure we do the best possible job of estimating future cash flow and calculating discount rates.