Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

138 Chapter 4

4.3 Analyzing the Financial Statements

We’re going to build a set of ﬁ nancial statements with roughly the same

format as the historical statements.

In order to do so, we have to analyze

the statements and make some decisions about plausible predictions and

projections for the future.

4.3.1 Sales Projections

The sales projection is one of the most critical elements in our model of

PPG; it is central to determining the value of the company in the ﬁ nancial

model we build. Before we start, here are some details of PPG’s sales

between 1990 and 2000:

BA K

2000

Sales 8,629

Cost of goods sold 5,334

Selling, general, and administrative

expense 1,646

Operating income nefore depreciation 1,649

Depreciation and amortization 447

Interest expense 193

Nonoperating income (expense) and

special items 8

Pretax income 1,017

Income taxes - total 369

Minority interest 28

Income before extraordinary items 620

Extraordinary items and discontinued

operations 75

Net income (loss) 620

Cash dividends 276

Retained 344

PPG CORPORATION INCOME STATEMENTS, 1991-2000 (Million $)

CDEFGH I J

1999 1998 1997 1996 1995 1994 1993 1992 1991

7,757 7,510 7,379 7,218 7,058 6,331 5,754 5,814 5,673

4,696 4,476 4,397 4,340 4,212 3,866 3,633 3,695 3,676

1,514 1,404 1,318 1,243 1,214 1,137 1,073 1,083 1,079

1,547 1,630 1,664 1,634 1,632 1,329 1,048 1,036 918

415 354 348 340 332 318 331 352 351

142 119 115 108 93 91 109 148 169

-17 137 -26 . . -64 -63 . -44

973 1,294 1,175 1,240 1,262 856 544 542 354

377 466 435 471 480 325 236 218 147

28 27 26 25 15 16 13 4 5

568 801 714 744 768 515 295 319 201

0000000-2730

568 801 714 744 767.6 514.6 22.2 319.4 276.2

264 252 239 237 239 238 221 200 183

304 549 475 507 529 277 -199 120 94

ABCDEFGHIJK

Year Sales

Year-on-

year growth

1990 5,889

1991 5,673 -3.67% <-- =B4/B3-1

1992 5,814 2.49%

1993 5,754 -1.03%

1994 6,331 10.03%

1995 7,058 11.47%

1996 7,218 2.27%

1997 7,379 2.23%

1998 7,510 1.78%

1999 7,757 3.29%

2000 8,629 11.24%

Compound growth

rate

3.89% <-- =(B13/B3)^(1/10)-1

PPG CORPORATION ANALYSIS OF SALES 1990-2000

PPG, Sales and Sales Growth, 1990–2000

1,000

2,000

3,000

4,000

5,000

6,000

7,000

8,000

9,000

10,000

1990

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

Sales ($ million)

-6%

-4%

-2%

10%

12%

14%

Sales growth

Sales Year-on-

year growth

3. We already made lots of judgment calls in the historical ﬁ nancial statements. For

example, we combined all the debt items into one.

139 Building a Financial Model: The Case of PPG Corporation

There are two things to notice about PPG’s historic sales:

•

Average sales growth is quite low (around 4 percent), as beﬁ ts a

company in a mature industry. In such an industry we would expect sales

growth to be approximately the average growth of the economy as a

whole.

•

The growth pattern is very cyclical. This is typical of the chemicals

industry. PPG’s growth seems to ﬂ uctuate between low (and even nega-

tive) growth and high rates of growth.

Projection In our base-case projection (see section 4.6), we will assume

that PPG’s future sales growth is 4 percent. In the exercise at the end of

the chapter we will let you use a variation where the growth is high for

2001 and 2002 and much lower afterward.

4.3.2 Proﬁ t and Loss Analysis

We analyze the ratios for the proﬁ t and loss statement. Our general

approach is that the primary ratios are percentages of sales:

BACDEFGHI

Profit and loss historical ratios 2000 1999 1998 1997 1996 1995 1994 1993 1992

COGS/Sales

61.81% 60.54% 59.60% 59.59% 60.13% 59.68% 61.05% 63.14% 63.55%

SG&A/Sales 19.08% 19.52% 18.70% 17.86% 17.23% 17.19% 17.96% 18.65% 18.62%

Total costs/Sales (sum of above items) 80.89% 80.06% 78.30% 77.45% 77.36% 76.87% 79.02% 81.79% 82.18%

Depreciation/Gross fixed assets 6.31% 6.05% 5.25% 5.15% 5.09% 5.13% 5.15% 5.48% 5.71%

Interest expense/Average debt 6.70% 6.30% 6.96% 7.23% 7.96% 7.88% 8.04% 8.96% 10.30%

Incomes taxes/Pretax income 36.28% 38.75% 36.01% 37.02% 38.00% 38.00% 38.00% 43.41% 40.31%

Minority Interest/(Pretax - Taxes) 4.32% 4.70% 3.26% 3.51% 3.20% 1.92% 3.00% 4.19% 1.24%

Dividends/Net income 44.52% 46.48% 31.46% 33.47% 31.80% 31.12% 46.21% 994.59% 62.49%

Profit and loss--model parameters

COGS/Sales

61.39% <-- =AVERAGE(B29:K29)

SG&A/Sales 18.38% <-- =AVERAGE(B30:K30)

Depreciation/Gross fixed assets 6.31% <-- =B33

Interest rate 6.70% <-- =B34

Tax rate 36.28% <-- =B35

Minority Interest/(Pretax - Taxes) 4.32% <-- =B36

Dividend growth 6.00% <-- See explanation later on

Model Parameters Our choice of model parameters (rows 40–46) may

appear to be idiosyncratic. In come cases—for examples in COGS/Sales

and SG&A/Sales—we have decided to use the historical averages of the

parameters. In other cases—for example, the depreciation rate, the ﬁ rm’s

interest rate on future borrowings, and the percentage of earnings paid

to minority interests—we have chosen to ignore historical information

and to use only the last year’s values. In the case of modeling the ﬁ rm’s

140 Chapter 4

Model Parameters We use the historical ratios to project future antici-

pated ratios. As in the case of our parameter choice for the income

statement, we make no claims for consistency, preferring instead to use

our judgment (or lack of judgment). Following are our choices.

BACDEFGHIJ

Balance sheet--historical ratios

ASSETS 2000 1999 1998 1997 1996 1995 1994 1993 1992 1991

Cash and short-term investments

Receivables/Sales 18.11% 20.55% 18.19% 18.34% 16.98% 17.64% 19.41% 17.32% 17.60% 18.65%

Inventories/Sales 12.99% 13.10% 12.21% 11.70% 11.03% 10.45% 10.84% 11.88% 12.77% 15.43%

Other current assets/Sales 3.45% 3.79% 3.32% 3.24% 2.84% 2.65% 3.01% 4.07% 2.13% 3.57%

Operating CA/Sales 35.84% 39.47% 35.42% 35.02% 31.81% 32.24% 34.25% 35.21% 33.55% 38.31%

Property, plant, equipment, gross/Sales 82.15% 88.42% 89.73% 91.58% 92.66% 91.59% 97.34% 105.01% 105.91% 109.52%

Annual depreciation/PPE 6.31% 6.05% 5.25% 5.15% 5.09% 5.13% 5.15% 5.48% 5.71% 5.65%

Property, Plant, equipment, net/Sales 34.08% 37.81% 38.68% 38.69% 40.36% 40.17% 43.31% 48.44% 51.11% 56.12%

Other assets/Sales

16.12% 15.91% 15.85% 14.35% 16.74% 15.36% 15.53% 14.57% 12.72% 12.33%

Other assets, growth rate since 1991

7.93% <-- =(B14/K14)^(1/9)-1

LIABILITIES

Accounts payable/Sales

8.85% 9.73% 8.39% 8.75% 8.34% 8.27% 8.18% 7.58% 7.69% 9.17%

Accrued expense/Sales

7.02% 8.37% 8.44% 7.64% 6.98% 7.37% 7.60% 7.34% 5.56% 6.13%

Other Current liabilities/Sales

0.14% 0.34% 0.15% 0.11% 0.21% 0.58% 0.87% 1.17% 0.83% 1.09%

Operating CL/Sales

16.02% 18.43% 16.98% 16.51% 15.52% 16.21% 16.64% 16.09% 14.08% 16.40%

Debt

Debt/Equit

95.93% 89.83% 59.65% 67.80% 59.70% 47.52% 44.74% 45.66% 48.42% 59.28%

Debt/Assets

32.56% 31.30% 23.26% 24.77% 23.01% 19.71% 19.41% 19.98% 23.08% 25.98%

Other liabilities/Sales

11.64% 12.50% 13.14% 12.90% 11.92% 11.86% 12.10% 13.95% 5.26% 6.12%

Other liabilities, growth rate since 1991

12.53% <-- =(B26/K26)^(1/9)-1

4. And just to make sure, we’ll do sensitivity analysis on the parameters to test whether

a different choice would have an impact.

future dividends, we have chosen another approach entirely—we will

assume that the ﬁ rm’s total dividends will grow by 4.13 percent per year;

an explanation for this assumption is given in section 4.9.

In every case we tried to make our parameter choice an intelligent,

informed reﬂ ection of our knowledge of the ﬁ rm.

4.3.3 Balance Sheet Analysis

We analyze the balance sheet ratios as follows:

141 Building a Financial Model: The Case of PPG Corporation

Net Fixed Assets/Sales PPG made great efforts over the 1991–2000

decade to become more efﬁ cient. These are reﬂ ected in the steady decline

of the ratio of net plant, property, and equipment to sales.

BACDEF

Balance sheet--model parameters

ASSETS

Cash and short-term investments

constant

Receivables/Sales

18.28% <-- =AVERAGE(B42:K42)

Inventories/Sales

12.24% <-- =AVERAGE(B43:K43)

Other current assets/Sales

3.21% <-- =AVERAGE(B44:K44)

Operating CA/Sales

35.11% <-- =AVERAGE(B45:K45)

Property, plant, equipment, gross

=accrued depreciation + net plant, property, equipment

Accrued depreciation

=previous year's accrued + this year's depreciation from income

Property, plant, equipment, net/Sales

34.08% <-- =B49

Other assets, annual growth rate

7.93% <-- =B52

LIABILITIES

Accounts payable/Sales

8.50%

Accrued expense/Sales

7.25%

Other current liabilities/Sales

12 <-- constant at current level

Debt/Assets

32% <-- approximately current level

Deferred taxes

grows at sales growth

Minority Interest

128 <-- constant at current level

Other liabilities, annual growth rate

12.53% <-- =B65

EQUITY

Common stock

484

Capital surplus

102

Retained earnings

=previous year's retained + this year's retentions from income statement

Treasury stock - total dollar amount

PLUG

142 Chapter 4

PPG became much more efﬁ cient during the 1990s: In 1991 it took

about $560 of net ﬁ xed assets to produce $1,000 of sales; by 2000 it took

only $340 of ﬁ xed assets to produce $1,000 of sales. The modeling ques-

tion is whether this trend will continue. In our model we have assumed

that it will not, and that PPG will maintain (but not improve) its current

level of efﬁ ciency.

4.4 A Model for PPG

We are now ready to model PPG’s ﬁ nancial statements. We use the follow-

ing model to project PPG’s ﬁ nancial statements for 2001. The model uses

the naming feature of Excel to name most of the cells. To name a cell, click

on the Name Box and write in the name you choose for the cell:

PPG: Net Plant, Property, and Equipment to Sales

30%

35%

40%

45%

50%

55%

60%

20001999

1998

1997199619951994199319921991

5. This is an important parameter. In one of the exercises we ask you to perform some

sensitivity analysis on PPE/Sales and judge its effect on the company’s earnings and

valuation.

143 Building a Financial Model: The Case of PPG Corporation

4.4.1 What Is the Plug in the Model?

Our model assumes that PPG will have a debt/assets ratio of 32 percent

and that treasury stock will be the plug. The basic assumption is that

additional cash will be used to repurchase equity. We will discuss this

issue further later on. For the moment, examine the following model and

note that treasury stock is indeed the plug:

Treasury stock C62 SUM(C66:C78)

= All current liabilities Debt

=− +

++++

Deferred taxes

Minority interest Other liabilities

Common stoock Capital surplus

Retained earnings Total assets

−

If you turn this equation around (putting Total assets on the left-hand

side and Treasury stock on the right-hand side), then you see why we’ve

correctly described the plug:

Total assets All current liabilities Debt Deferred taxes

Minor

=++

+ iity interest Other liabilities

Common stock Capital surplus

+ eetained earnings Treasury stock

Total liabilities and equity

−

144 Chapter 4

Here’s the model:

BA DC

Profit and loss assumptions

Cell names

Sales growth

3.89% <-- growth

COGS/Sales

61.39% <-- cogs_sales

SG&A/Sales 18.38% <-- sga_sales

Depreciation/Gross fixed assets 6.31% <-- depr_perc

Interest rate 6.70% <-- interest

Minority interest as % of PAT 4.32% <-- minority

Growth rate of total dividends 4.13% <-- divgrowth

Tax rate 36.28% <-- tax

Balance sheet assumptions

Assets

Cash and short-term investments constant

Receivables/Sales 18.28% <-- ar_sales

Inventories/Sales 12.24% <-- inventory_sales

Other current assets/Sales 3.21% <-- otherca_sales

Property, plant, equipment, net/Sales 34.08% <-- ppe_sales

Investments and advances constant

Other assets, annual growth rate

7.93% <-- otherassets_growth

Liabilities

Accounts payable/Sales 8.50% <-- ap_sales

Accrued expense/Sales 7.25% <-- accruedexp_sales

Other current liabilities/Sales constant

Debt/Assets 32.00% <-- debt_assets

Deferred taxes growth at sales growth

Minority interest constant

Other liabilities, annual growth 12.53% <-- otherliab_growth

Treasury stock plug

PROFIT AND LOSS

2000 2001

Sales 8,629

8,965 <-- =B34*(1+growth)

Cost of goods sold 5,334

5,503 <-- =sales2001*cogs_sales

Selling, general, and administrative

expense 1,646

1,648 <-- =sales2001*sga_sales

Operating income before depreciation 1,649

1,813 <-- =sales2001-C35-C36

Depreciation and amortization 447

465 <-- =depr_perc*AVERAGE(B55:C55)

Interest expense 193

200 <-- =interest*AVERAGE(B69:C69)

Nonoperating Income (expense) and

special Items 8

8<-- =B40

Pretax income 1,017

1,140 <-- =C37-C38-C39-C40

Income taxes - total 369

414 <-- =tax*C41

Minority interest 28

18 <-- =minority*C42

Net income

620 709 <-- =C41-C42-C43

Dividend

276 287 <-- =B45*(1+divgrowth)

Retained earnings

344 421 <-- =C44-C45

PPG, FINANCIAL MODEL

First year only--2001

145 Building a Financial Model: The Case of PPG Corporation

Here’s the calculation of the free cash ﬂ ow:

BA DC

ASSETS

2000 2001

Cash and short-term investments 111

111 <-- =B49

Receivables 1,563

1,639 <-- =ar_sales*sales2001

Inventories - total 1,121

1,097 <-- =inventory_sales*sales2001

Other current assets 298

287 <-- =otherca_sales*sales2001

Total current assets

3,093 3,134 <-- =SUM(C49:C52)

Property, plant, and equipment, gross 7,089

7,669 <-- =C56+C57

Depreciation, depletion, and amortization

(accumulated) 4,148

4,613 <-- =B56+C38

Property, plant, and equipment, net 2,941

3,055 <-- =ppe_sales*sales2001

Investments and advances 320

320 <-- =B59

Intangibles 1,700

1,700 <-- =B60

Other assets 1,071

1,156 <-- =B61*(1+otherassets_growth)

TOTAL ASSETS

9,125

9,366 <-- =SUM(C57:C61)+C53

LIABILITIES

2000 2001

Accounts payable 764 762

<-- =ap_sales*sales2001

Accrued expense 606 650

<-- =accruedexp_sales*sales2001

Other current liabilities 12 12

<-- =B67

Debt 2,971 2,997

<-- =debt_assets*C62

Deferred taxes 543 564

<-- =B70*(1+growth)

Minority interest 128 128

<-- =B71

Other liabilities 1,004 1,130

<-- =B72*(1+otherliab_growth)

EQUITY

Common stock 484 484

<-- =B75

Capital surplus 102 102

<-- =B76

Retained earnings 6,019 6,440

<-- =B77+C46

Less: treasury stock 3,508 3,903

<-- =-C62+SUM(C65:C77)

Total stockholders equity 3,097 3,124

<-- =SUM(C75:C77)-C78

TOTAL LIABILITIES AND EQUITY

9,125 9,366

<-- =SUM(C65:C72)+C79

BA DC

FREE CASH FLOW

2001

Profit after tax 709 <-- =C44

Add back depreciation 465 <-- =C38

Change in net working capital

Increase in operating current assets -41 <-- =+SUM(B50:B52)-SUM(C50:C52)

Add increase in operating current liabilities 41 <-- =SUM(C65:C67)-SUM(B65:B67)

Subtract capital expenditures -580 <-- =-C55+B55

Subtract increase in other assets -85 <-- =-C61+B61

Add back after-tax interest 127 <-- =(1-tax)*C39

FCF 636 <-- =SUM(C84:C91)

146 Chapter 4

4.5 Back to Treasury Stock and the Dividend

Our model explains (1) how much money the ﬁ rm pays to its share-

holders and (2) how this amount was ﬁ nanced. In 2001 we predict

that PPG will pay its shareholders $682 million—$287 million through

dividends and a further $395 million through share repurchases:

100

101

102

103

104

105

106

107

108

109

BA DC

What's paid out to shareholders?

2001

Dividend 287 <-- =C45

Calculating the share repurchases

New stock raised (

negative

equity payout)

0 <-- =SUM(C75:C76)-SUM(B75:B76)

Additional treasury stock (positive equity payout) 395 <-- =C78-B78

Paid out to shareholders =

Dividend - New stock

+ Additional treasury

682 <-- =C96-C98+C99

How was this financed?

Free cash flow 636 <-- =C92

Additional debt financing 26 <-- =C69-B69

Increase in deferred taxes 21 <-- =C70-B70

Increase in minority interest 0 <-- =C71-B71

Increase in other liabilities 126 <-- =C72-B72

Interest after tax 127 <-- =(1-tax)*C39

Paid out to shareholders 682 <-- =SUM(C103:C107)-C108

147 Building a Financial Model: The Case of PPG Corporation

4.6 The Whole Model

Here’s the whole model:

BACDEF

Profit and loss assumptions

Sales growth

3.89%

COGS/Sales

61.39%

SG&A/Sales

18.38%

Depreciation/Gross fixed assets 6.31%

Interest rate 6.70%

Minority interest as % of PAT 4.32%

Growth rate of total dividends 4.13%

Tax rate 36.28%

Balance sheet assumptions

Assets

Cash and short-term investments constant

Receivables/Sales 18.28%

Inventories/Sales 12.24%

Other current assets/Sales 3.21%

Property, plant, equipment, net/Sales 34.08%

Investments and advances constant

Other assets/Sales 7.93%

Liabilities

Accounts payable/Sales 8.50%

Accrued expense/Sales 7.25%

Other current liabilities/Sales constant

Debt/Assets 32.00%

Deferred taxes growth at sales growth

Minority interest assumed constant

Other liabilities, annual growth 12.53%

Treasury stock plug

PROFIT AND LOSS

2000 2001 2002 2003 2004 2005

Sales 8,629

8,965 9,313 9,676 10,052 10,443

Cost of goods sold 5,334

5,503 5,718 5,940 6,171 6,411

Selling, general, and administrative

expense 1,646

1,648 1,712 1,779 1,848 1,920

Operating income before depreciation 1,649

1,813 1,884 1,957 2,033 2,112

Depreciation and amortization 447

465 503 544 587 634

Interest expense 193

200 204 212 219 227

Nonoperating income (expense) and

special items 8

88888

Pretax income 1,017

1,140 1,168 1,194 1,219 1,243

Income taxes - total 369

414 424 433 442 451

Minority interest 28

18 18 19 19 19

Net income

620 709 726 742 757 773

Dividend

276 287 299 312 324 338

Accrued

344 421 427 430 433 435

PPG, FINANCIAL MODEL, 2001-2005