. 1
( 62 .)



>>

TLFeBOOK
BUILDING
FINANCIAL
MODELS
A Guide to Creating
and Interpreting
Financial Statements




JOHN S. TJIA




McGraw-Hill
New York Chicago San Francisco Lisbon
London Madrid Mexico City Milan
New Delhi San Juan Seoul
Singapore Sydney Toronto




TLFeBOOK
Copyright © 2004 by John S. Tjia.. All rights reserved. Manufactured in
the United States of America. 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 publisher.

0-07-144282-0

The material in this eBook also appears in the print version of this title: 0-07-140210-1.

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. For more information, please contact
George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904-4069.

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
GUARANTEES 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 guarantee 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 resulting 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
limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause
arises in contract, tort or otherwise.

DOI: 10.0136/0071442820




TLFeBOOK
For more information about this title, click here.




CONTENTS




INTRODUCTION v

CHAPTER 1
A Financial Projection Model 1
CHAPTER 2
Design Principles for Good Model Building 13
CHAPTER 3
Starting Out 23
CHAPTER 4
Your Model-Building Toolbox: F Keys and Ranges 47
CHAPTER 5
Your Model-Building Toolbox: Functions 63
CHAPTER 6
Guerilla Accounting for Modeling 109
CHAPTER 7
Balancing the Balance Sheet 119
CHAPTER 8
Income Statement and Balance Sheet Accounts 145
CHAPTER 9
Putting Everything Together 155
CHAPTER 10
The IS and BS Output Sheets 193
CHAPTER 11
The CF Sheet 199
CHAPTER 12
Ratios: Key Performance Indicators 209
CHAPTER 13
Forecasting Guidelines 227

iii




TLFeBOOK
Contents
iv




CHAPTER 14
The Cash Sweep 237
CHAPTER 15
The Cash Flow Variation for Cash Sweep 257
CHAPTER 16
Recording Macros 271
CHAPTER 17
On-Screen Controls 287
CHAPTER 18
Bells and Whistles 297
CHAPTER 19
Writing a Macro in Visual Basic for Applications 315

INDEX 329




TLFeBOOK
INTRODUCTION




This book will teach you how to bring together what you know
of finance, accounting, and the spreadsheet to give you a new
skill”building financial models. The ability to create and under-
stand models is one of the most valued skills in business and
finance today. It™s an expertise that will stand you in good stead
in any arena”Wall Street or Main Street”where numbers are
important. 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
financial model with Generally Accepted Accounting Principles
(GAAP) that you can use to make projections for industrial/man-
ufacturing companies. (Banks and insurance companies have dif-
ferent flows in their businesses and are not covered in this book.)
Along the way, I will take you through a tour of the essen-
tials in Excel and modeling (Chapters 1 to 5), then ˜˜guerilla
accounting™™ to give you some familiarity with this subject
(Chapter 6) before plunging into actual model building (Chapters
7 to 11). I cover the performance indicators that a model should
have (Chapter 12) and guidelines for making useful forecasts
(Chapter 13). In the rest of the book (Chapters 14 to 19), I take
you back to building additional ˜˜bells and whistles™™ to add to
the basic model that you have built.
v




TLFeBOOK
Introduction
vi




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
specific relationships among the numbers that organize them into
informational output.
The model becomes a financial model when it uses relation-
ships of operating, investing, and/or financing variables based
on GAAP principles.
And it can be called a financial projection model when it uses
assumptions about future performance in order to give a view of
what a company™s future financial condition might be like. By
changing the input variables, such a projection model can be
very 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
layout that are robust, yet easy and, yes, delightful to use. Arthur
C. Clarke, the renowned science writer, once said: ˜˜Any suffi-
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 specific
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,




TLFeBOOK
Introduction vii




you will have the satisfaction of having built your own model, to
which you can then add you own changes and modifications.



BUILD MODELS WITH YOUR OWN STYLE
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 pro-
perly, 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 basics of language, you will then be able to
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 find as the starting point in many forms of analysis. The
model will have these key features:
It will have historical and forecast numbers for modeling
u

an industrial type of company or business. Forecast
numbers can be entered as ˜˜hard-coded™™ numbers (e.g.,
sales will be 1053 this year and 1106 next year, etc.) or
as assumptions (e.g., sales growth next year will be
5 percent, etc.).

. 1
( 62 .)



>>