02model 11/30/2020 7:21 12/2/2002
Chapter 2. Model for Financial Statements, Cash Flows, and Taxes
THE ANNUAL REPORT
The annual report contains a verbal section plus four key statements: the balance sheet, income statement,
statement of retained earnings, and statement of cash flows. These statements contain a wealth of information
that is used by bankers, stock and bond analysts, and managers. Hence, they are quite important. Spreadsheets
are used both to create and to analyze these statements, as we demonstrate in this model.
Regarding statement creation, firms' accounting information is maintained in computer files, which are
adjusted as sales occur, materials are purchased, and so forth. The firm's accountants periodically--monthly,
quarterly, and annually--extract information from the general files and compile the four statements. These
statements-- in both printed and spreadsheet format --are made available to security analysts, and if the firm
wants to borrow money, the same information is provided to potential lenders.
The firm's financial staff also uses the data to analyze the firm's condition--its strengths and weaknesses--and
this information is used in the quest to improve operations and increase the firm's value.
In this model, we start with the same balance sheet and income statement that was used in the chapter, but in
an Excel format, and then we show how Excel (and other spreadsheet packages) can be used to analyze the data.
The analysis will be extended in Chapter 3, which go on to examine various ratios and other metrics used to
appraise firms and to compare them with other companies.
Notice that a spreadsheet model dealing with taxes is shown on the tab labeled "TAXES" found in the lower
left section of the screen. Click on this tab to see the taxes model. Then, to return to the financial statement
model, click on the tab for that model. The "Taxes" model is meant to accompany the Web Appendix 2A.
In addition, note that in cells which summarize data in other cells, such as sums or differences, the
spreadsheet uses formulas rather than fixed numbers. For example, the cell for Total Assets in 2002 (F65)
contains the SUM formula rather than just $2,000. (The cell itself shows $2,000, but if you put the pointer on
F65, then the formula line will show that the cell actually contains a formula.) That way, if the data for any
input (cash, for instance) changes, the spreadsheet will automatically recalculate and provide the correct net
value for Total Assets. As you will see as you go through our models, this automatic recalculation feature is
one of the most useful and powerful aspects of Excel and other spreadsheets.
Finally, note that we show just below this introduction, before the financial statements, some information
that is used to develop aspects of the financial statements, including the tax rate, number of shares
outstanding, and price per share. Generally, in financial models, data that are used to construct statements
are shown in an Input Data Section toward the top of the model.
INPUT DATA SECTION: Historical Data Used in the Analysis
2002 2001
Year-end common stock price $23.00 $26.00
Year-end shares outstanding (in millions) 50 50
Tax rate 40% 40%
After-tax cost of capital 11.0% 10.3%
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
