Building Financial Models (McGraw-Hill Finance & Investing)

465 Pages·2009·7.376 MB·English

by  John Tjia

BUILDING
FINANCIAL
MODELS
S E C O N D E D I T I O N
This page intentionally left blank
BUILDING
FINANCIAL
MODELS
SECOND EDITION
THE COMPLETE GUIDE TO DESIGNING, BUILDING,
AND APPLYING PROJECTION MODELS
JOHN S. TJIA
New York Chicago San Francisco Lisbon London
Madrid Mexico City Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
To my wife, Charlotte, with love and kisses
Copyright © 2009 by John S. Tjia. All rights reserved. Except as permitted under the United States
Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by
any means, or stored in a database or retrieval system, without the prior written permission of the pub-
lisher.
ISBN: 978-0-07-160890-9
MHID: 0-07-160890-7
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-160889-3,
MHID: 0-07-160889-3.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after
every occurrence of a trademarked name, we use names in an editorial fashion only, and to the
benefit of the trademark owner, with no intention of infringement of the trademark. Where such
designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales
promotions, or for use in corporate training programs. To contact a representative please e-mail us at
[email protected].
This publication is designed to provide accurate and authoritative information in regard to the subject
matter covered. It is sold with the understanding that neither the author nor the publisher is engaged
in rendering legal, accounting, futures/securities trading, or other professional service. If legal advice
or other expert assistance is required, the services of a competent professional person should be
sought. —From a Declaration of Principles jointly adopted by a Committee of the American Bar
Association and a Committee of Publishers
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors
reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted
under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not
decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon,
transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without
McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use;
any other use of the work is strictly prohibited. Your right to use the work may be terminated if you
fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUAR-
ANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF
OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY
INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR
OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR
FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guar-
antee that the functions contained in the work will meet your requirements or that its operation will
be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone
else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages result-
ing therefrom. McGraw-Hill has no responsibility for the content of any information accessed through
the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect,
incidental, special, punitive, consequential or similar damages that result from the use of or inability
to use the work, even if any of them has been advised of the possibility of such damages. This limita-
tion of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in
contract, tort or otherwise.
C O N T E N T S
INTRODUCTION vii
CHAPTER 1
A Financial Projection Model 1
CHAPTER 2
Best Practices 15
CHAPTER 3
Setting the Stage 29
CHAPTER 4
Accounting for Modeling 65
CHAPTER 5
The Model Building Toolbox: F Keys and Ranges 83
CHAPTER 6
The Model Building Toolbox: Functions 107
CHAPTER 7
Building a Pilot Model 159
CHAPTER 8
Circular References and Iterative Calculations 171
CHAPTER 9
Variations on Balancing Plugs 199
CHAPTER 10
Preparing to Build a Full Model 209
CHAPTER 11
Building an Integrated Financial Model: Part 1 225
CHAPTER 12
The Cash Flow Statement 255
CHAPTER 13
The Cash Sweep 269
vi Contents
CHAPTER 14
Ratios 313
CHAPTER 15
Forecasting Guidelines 335
CHAPTER 16
Discounted Cash Flow Valuation 345
CHAPTER 17
Introduction to Visual Basic for Applications 365
CHAPTER 18
A VBA Primer 379
CHAPTER 19
Tips and Tricks 409
APPENDIX: ABBREVIATIONS 437
INDEX 439
ABOUT THE AUTHOR 450
I N T R O D U C T I O N
T
his book will teach you how to bring together what you know
of i nance, accounting, and the spreadsheet to give you a new
skill—building i nancial models. The ability to create and under-
stand models is one of the most valued skills in business and i -
nance today. It is an expertise that will stand you in good stead in
any arena—Wall Street or Main Street—where numbers are impor-
tant. Whether you are a veteran, just starting out on your career,
or still in school, having this expertise can give you a competitive
advantage in what you want to do.
By the time you have completed the steps laid out in this
book, you will have created a working, dynamic spreadsheet i -
nancial model that you can use to make projections for indus-
trial/manufacturing companies. (Banks and insurance companies
have different l ows in their businesses and are not covered in
this book.)
This second edition is an extensive rewrite of the i rst edi-
tion and includes an additional chapter on discounted cash l ow
valuation modeling. This edition uses screen illustrations from
Excel 2007, the latest version from Microsoft Ofi ce available as
of this writing. Although the instructions still apply to earlier
versions, the illustrations are oriented more toward this latest in-
carnation of the world’s most popular spreadsheet application.
vii
viii Introduction
FIRST, SOME DEFINITIONS
A spreadsheet can be used to tabulate and organize numbers, but
it does not become a model until it contains data, equations, and
specii c relationships among the numbers that organize them into
informational output.
The model becomes a i nancial model when it incorporates the
relationships of operating, investing, and/or i nancing variables
based on general accounting principles.
It can be called a i nancial projection model when it uses as-
sumptions about future performance to give a view of what a
company’s future i nancial condition might be like. By changing
the input variables, such a projection model becomes useful for
showing the impact of different assumptions and/or strategies
for the future.
TWO REQUIREMENTS FOR MAGIC
The task of developing a good spreadsheet model is a combina-
tion of many things, but primarily it is about good thinking and
a sound knowledge of the tools at hand. These two attributes will
put you on the right track for producing a model structure and lay-
out that are robust yet easy and, yes, delightful to use. Arthur C.
Clarke, the late renowned science writer, once said, “Any sufi -
ciently advanced technology is indistinguishable from magic.” I
hope that after using the approaches and techniques for building
models in this book, you too can look at your work and feel the
magic you have created. And I certainly hope that your colleagues,
managers, and clients will have the same reaction.
THIS IS A HANDS-ON BOOK
This book will lead you through the development process for a
projection model. It is laid out in a step-by-step format in which
each chapter describes a step. Each chapter covers a specii c phase
of building a model. This is a hands-on book. You will get the most
out of this book if you perform the steps outlined in each chapter
on your computer screen. By the end of the book, you will have
Introduction ix
the satisfaction of having built your own model. To this model you
can then add you own changes and modii cations.
BUILD MODELS WITH YOUR OWN STYLE
Building models is a l uid, creative activity, and there are as many
ways to build a model as, say, to write a book. Most of them will
result in working models, but not necessarily very good ones;
there are, after all, bad books. But there are also excellent books
with very different styles. The intent of this book is to show you
the tools—the vocabulary and the syntax of model building, if you
will—for developing a model that works properly, and so provide
you with the foundation for developing other models. Just as you
develop your own style of writing once you have learned the ba-
sics of language, you can then develop your own style of model
building.
THE MODEL WE WILL BE BUILDING
The projection model we will be developing is one that you might
i nd as the starting point in many forms of analysis. The model
will have these key features:
(cid:2) It will have historical and forecast numbers for
modeling an industrial type of company or business.
Forecast numbers can be entered as “hard-coded”
numbers (e.g., sales will be 1,053 this year and 1,106
next year, etc.) or as assumptions (e.g., sales growth next
year will be 5%, etc.).
(cid:2) The income statement, balance sheet, and a cash l ow
statement are interlinked following general accounting
principles.
(cid:2) The balance sheet balances: the total assets must equal
the total liabilities and net worth.
(cid:2) Two mechanisms are introduced for balancing the
balance sheet: the balance sheet method and the cash
l ow method.