Download John Tjia - Building Financial Modeling.pdf PDF

TitleJohn Tjia - Building Financial Modeling.pdf
TagsForex Price Action
LanguageEnglish
File Size4.4 MB
Total Pages353
Table of Contents
                            Building.Financial.Models.A.Guide.to.Creating.and.Interpreting.Financial.Statements
	Cover
CONTENTS
INTRODUCTION
C H A P T E R 1
A Financial Projection
Model
C H A P T E R 2
Design Principles for
Good Model Building
C H A P T E R 3
Starting Out
C H A P T E R 4
Your Model-Building
Toolbox: F Keys and
Ranges
C H A P T E R 5
Your Model-Building
Toolbox: Functions
C H A P T E R 6
Guerilla Accounting for
Modeling
C H A P T E R 7
Balancing the
Balance Sheet
C H A P T E R 8
Income Statement and
Balance Sheet Accounts
C H A P T E R 9
Putting Everything
Together
C H A P T E R 10
The IS and BS
Output Sheets
C H A P T E R 11
The CF Sheet
C H A P T E R 12
Ratios: Key Performance
Indicators
C H A P T E R 13
Forecasting Guidelines
C H A P T E R 14
The Cash Sweep
C H A P T E R 15
The Cash Flow Variation
for Cash Sweep
C H A P T E R 16
Recording Macros
C H A P T E R 17
On-Screen Controls
C H A P T E R 18
Bells and Whistles
C H A P T E R 19
Writing a Macro in Visual
Basic for Applications
I N D E X
                        
Document Text Contents
Page 1

TLFeBOOK

Page 2

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

http://dx.doi.org/10.1036/0071442820

Page 176

The Balance Sheet, Shareholders’ Equity Input
and Balancing Rows

A B C D E F G

161 SHAREHOLDERS’ EQUITY
162 Common stock 460.0 500.0 580.0 650.0 650.0 650.0
163 Common stock 460.0 500.0 580.0 650.0 650.0 650.0
164
165 Retained earnings 200 261.0 337.0
166 Net to retained earnings 92.6 106.2 116.6
167 Retained earnings 200.0 261.0 337.0 429.6 535.7 652.3
168
169 Other equity acct 10.0 11.0 12.0
170 % of revenues 1.2% 1.2% 1.2% 1.1% 1.0% 0.9%
171 Other equity account 10.0 11.0 12.0 12.1 12.3 12.4
172 Total SH equity 670.0 772.0 929.0 1,091.7 1,198.0 1,314.8

173 Total liabs & SH equity 1,300.0 1,447.0 1,565.0 1,747.9 1,869.9 2,023.3

174
175

Input

You may be wondering what the final printout will be.
Remember that this is the input sheet. So what we need to do
is to create several other sheets whose job is basically nothing
more than to read the results of the input sheet (not the yellow
input lines themselves). These sheets can be named, for example,
‘‘Income Statement,’’ ‘‘Balance Sheet,’’ and ‘‘Cash Flow.’’ You will
see how they look at the end of the chapter.

First, let’s look at the elements of the ‘‘Input’’ sheet, and I
will explain them as we go along.

HOW TO READ THE ILLUSTRATIONS

In order to illustrate the formula-writing as fully as possible, I
have included small sections of our model showing the formulas
that need to be written into the various rows. The notation ‘‘>>>’’
means ‘‘copy this across the columns to the right.’’ For space
reasons, the illustrations only go to column F. As you set up
your own model, you can copy the columns out beyond this,
to as many columns as you want.

Putting Everything Together 165

TLFeBOOK

Page 177

Formulas in the Titles

For all the titles, there should be a way of allowing the labels to
be changed and having those changes read by the output. A
good way to do this, rather than having to retype the labels, is
simply to have the ‘‘output’’ row read the label for that block. For
example, for revenues, the formula in A8 should read as shown:

A8 ¼ A6

You should feel free to add in these formulas yourself in the
model shown below. Use Excel’s Copy and Paste commands to
enter the same data in the cells.

Dates and Revenues

A B C D E F

1 First Corporation
2
3 Proj Proj

4 INCOME STATEMENT 2000 =B4+1 >>> >>> >>>

5
6 Revenues 825.0 900.0 1,000.0
7 Percent growth % na =IF(B6,C6/B6_1,“na) >>> 10.0% 10.0%
8 =A6 =B6 >>> >>> =IF(E6,E6,

IF(ISNUMBER(E7),
D8*(1+E7),0))

>>>

Title
A1 ‘‘First Corporation’’
Date
B4 2000
C4 ¼B4þ1. Copy this cell across to column G.

Each looks to the previous cell and adds
the value 1 to that number. You can use the
DATE function if you want to use a more
sophisticated dating system that uses months
and years as described in Chapter 5 in the
Section ‘‘Functions for Dates.’’

Revenues
B7 This is ‘‘na’’ because there is no prior year for

measuring growth.
C7 ¼IF(B6,C6/B6� 1, ‘‘na’’)

166 Chapter 9

TLFeBOOK

Page 352

A B O U T T H E A U T H O R

John S. Tjia (pronounced ‘‘Chee-ah’’) has 15 years of experience
in developing financial models, including 7 years as head of the
Models Group J.P. Morgan (now known as JPMorgan Chase) in
New York. He was responsible for designing, developing, and
providing the training for the firm’s standard analysis and exec-
ution financial models for the Mergers & Acquisitions and
Investment Banking divisions worldwide. Prior to that, he was
an investment banker in the firm’s Hong Kong office and had
assignments in credit analysis and financial advisory in New
York. Now a founding partner of the TMG & Associates, LLC
modeling consulting company (www.tmga.com), he lives in
Pleasantville, New York, with his wife and two children. He
can be contacted at [email protected]

TLFeBOOK

Page 353

This page intentionally left blank.

TLFeBOOK

Similer Documents