Simon Benninga. Financial Modelling 3-rd edition

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

128 Chapter 3

like many ﬁ nancial modelers, you’ll see that there are some aspects of accounting

that you’ve forgotten!)

2. The model of section 3.1 includes costs of goods sold but not selling, general, and

administrative (SG&A) expenses. Suppose that the ﬁ rm has $200 of these expenses

each year, irrespective of the level of sales.

a. Change the model to accommodate this new assumption. Show the resulting

proﬁ t and loss statements, balance sheets, the free cash ﬂ ows, and valuation.

b. Do a data table in which you show the sensitivity of the equity value to the level

of SG&A. Let SG&A vary from $0 per year to $600 per year.

3. Suppose that in the model of section 3.1 the ﬁ xed assets at cost for years 1–5 are

100 percent of sales (in the current model, it is net ﬁ xed assets which are a function

of sales). Change the model accordingly. Show the resulting proﬁ t and loss state-

ments, balance sheets, and free cash ﬂ ows for years 1–5. (Assume that in year 0, the

ﬁ xed assets accounts are as shown in section 3.2. Note that since year 0 is given—it

is the current situation of the ﬁ rm, whereas years 1–5 are the predictions for the

future—there is no need for the year-0 ratios to conform to the predicted ratios for

years 1–5.)

4. Referring again to the model of section 3.2, suppose that the ﬁ xed assets at cost

follow the following step function:

Fixed assets as cost

Sales

Sales=

∗

+∗ −

100

1 200 90 1 200

138

,%(,)

,00 80 1 400+∗ −

≤

%( , )Sales

if Sales 1,200

1,200 < Sales 1,400

Sales >>

⎧

⎨

⎪

⎩

⎪

1 400,

Incorporate this function into the model.

5. Consider the model in section 3.6 (where debt is the plug).

a. Suppose that the ﬁ rm has 1,000 shares and that it decides to pay, in year 1, a

dividend per share of 15 cents. In addition, suppose that it wants this dividend

per share to grow in subsequent years by 12 percent per year. Incorporate these

changes into the pro forma model.

b. Do a sensitivity analysis in which you show the effect on the debt/equity ratio of

the annual growth rate of dividends. Vary this rate from 0 to 18 percent, in steps

of 2 percent. For this exercise, deﬁ ne debt as net debt (i.e., debt minus cash and

marketable securities). (Note that since the WACC is equal to 20 percent, the

growth rate must be less than 20 percent.)

6. In the model of section 3.6, assume that the ﬁ rm needs to have minimum cash

balances of 25 at the end of each year. Introduce this constraint into the model.

7. In the valuation exercise of section 3.4, the terminal value is calculated using a

Gordon dividend model on the cash ﬂ ows. Replace this terminal value by the year-5

book value of debt plus equity. In making this change, you are essentially assuming

that the book value correctly predicts the market value.

8. Repeat exercise 7, but this time replace the terminal value by an EBITDA ratio

times year-5 anticipated EBITDA. Show a graph of the equity value of the ﬁ rm as

a function of the assumed year-5 EBITDA ratio, varying this ratio from 6–14.

9. In the project ﬁ nance pro forma of section 3.9 it is assumed that the ﬁ rm pays off

its initial debt of $1,000 in equal installments of principal over ﬁ ve years. Change

129 Financial Statement Modeling

this assumption and assume instead that the ﬁ rm pays off its debt in equal payments

of interest and principal over ﬁ ve years. Hint: You have to use the PMT function to

ﬁ nd the annual payments; then set up a loan table (as in Chapter 1) to split the

annual payments into an interest and repayment of principal. Alternatively you can

use the functions PPMT and IPMT discussed in Chapter 33.

10. This problem introduces the concept of “sustainable dividends”: The ﬁ rm whose

ﬁ nancials are illustrated in the following spreadsheet wishes to maintain cash bal-

ances of 80 over the next ﬁ ve years. It also desires neither to issue additional stock

nor to make any changes in its current level of debt. As a result, dividends are the

plug in the balance sheet. Model this situation (note that for some parameter levels

you may get “negative dividends,” indicating that there is no sustainable level of

dividends).

BACDEF

Sales growth 10%

Current assets/Sales 15%

Current liabilities/Sales 8%

Net fixed assets/Sales 77%

Costs of goods sold/Sales 50%

Depreciation rate 10%

Interest rate on debt 10.00%

Interest paid on cash & marketable securities 8.00%

Tax rate 40%

012345

Inc tement

Sal 1,000

Co s sold (500)

Interest payments on debt (32)

Interest earned on cash & marketable securities 6

Depreciation (100)

Profit before tax 374

Taxes (150)

Profit after tax 225

Dividends (90)

Retained earnings 135

Balance sheet

Cash and marketable securities 80 80 08 08 80 80

Current assets 150

Fixed assets

At cost 1,070

Depreciation (300)

Net fixed assets 770

Total assets 1,000

Current liabilities 80

Debt 320 023 023 320 320 320

Stock 450 054 054 450 450 450

Accumulated retained earnings 150

Total liabilities and equity 1,000

SUSTAINABLE DIVIDENDS--Template

ome sta

sts of good

130 Chapter 3

Recall that the free cash ﬂ ow (FCF) is the amount of cash generated

by the ﬁ rm under the assumption that it has no debt. Thus, to calculate

the FCFs of the ﬁ rm, we calculate the proﬁ t and loss statement that

it would have had (including tax carryforwards) if it had no interest

payments:

BAC

Year 1 2

Earnings before interest and taxes (EBIT) -100 400

Interest -50 -80

Profit before tax -150 320 <-- =SUM(C3:C4)

Loss carryforward 0 -150 <-- =IF(B9<0,B9,0)

Taxable income -150 170 <-- =SUM(C5:C6)

Taxes (30%) 0 -51 <-- =-MAX(0,0.3*C7)

Profit after tax -150 119 <-- =C8+C7

PROFIT AND LOSS WITH CARRYFORWARD

BAC

Year 1 2

Earnings before interest and taxes (EBIT) -100 400

Interest 0 0

Profit before tax -100 400

Loss carryforward 0 -100 <-- =IF(B20<0,B20,0)

Taxable income -100 300 <-- =SUM(C16:C17)

Taxes (30%) 0 -90 <-- =-MAX(0,0.3*C18)

Profit after tax -100 210 <-- =C19+C18

Add back tax-loss carryforward 0 100 <-- =-C17

Free cash flow (FCF) -100 310 <-- =C21+C20

Free Cash Flow (FCF) with No Debt

The loss carryforward is added back to the proﬁ t after tax because it

is a noncash charge against earnings (like depreciation). We can also do

this calculation directly from the proﬁ t and loss statement. However, we

have to recognize that the tax-loss carryforward includes an element of

the interest charges, which needs to be netted out:

Appendix 1: Calculating the Free Cash Flows When There Are Negative Proﬁ ts

We start off with a simple example. A ﬁ rm that has no depreciation, no

changes in net working capital, and no capital expenditures has the

following proﬁ ts in two successive years:

131 Financial Statement Modeling

Appendix 2: Accelerated Depreciation in Pro Forma Models

The models in the chapter all use straight-line depreciation. However,

they can be adjusted to accommodate accelerated depreciation (whether

this adjustment is worth the effort is another question, which is discussed

at the end of this appendix).

As an example, consider a company that depreciates all of its assets

using the ﬁ ve-year accelerated cost recovery system (ACRS) deprecia-

tion schedule that is part of the 1986 Tax Reduction Act in the United

States.

The depreciation rates for an asset under this act are as shown

in the following table:

BAC

Year 1 2

Earnings before interest and taxes (EBIT) -100 400

Interest -50 -80

Profit before tax -150 320 <-- =C5

Loss carryforward 0 -150 <-- =C6

Taxable income -150 170 <-- =C7

Taxes (30%) 0 -51 <-- =C8

Profit after tax -150 119 <-- =C9

Profit after tax -150 119 <-- =C32

Add back loss carryforward 0 150 <-- =-C29

Add back interest, net of tax 50 56 <-- =-(1-0.3)*C27

Subtract interest tax shield on carryforward 0 -15 <-- =0.3*B27

Free cash flow (FCF) -100 310 <-- =SUM(C35:C38)

FCF Calculation

CALCULATING FCF FROM PROFIT AND LOSS STATEMENT

Year

Depreciation

rate

Cumulative

depreciation

1 20.00% 20%

2 32.00% 52%

3 19.20% 71%

4 11.52% 83%

5 11.52% 94%

6 5.76% 100%

Depreciation Table--Five-Year Asset Life

11. This appendix contains an advanced topic that can be skipped on ﬁ rst reading.

12. For more information about the ACRS depreciation schedules, you are referred to

standard U.S. ﬁ nance texts (for example, Brealey, Myers, Allen (2005).

132 Chapter 3

(Note that the “ﬁ ve-year” depreciation schedule actually depreciates the

asset over six years.)

The following spreadsheet fragment shows the ﬁ xed-asset schedule

over the next ﬁ ve years:

BACDEFGH

DEPRECIATION CALCULATION--year 0 assets assumed 3 years old

Sales growth 10%

Net fixed assets/Sales 77%

Sales 1,000 1,100 1,210 464,1 133,1 1,611

Year 0 1 2 3 4 5

Fixed assets at cost, end of year 2,674 3,155 3,838 615,5 635,4 6,239 <-- =G62-G61

Accumulated depreciation (1,904) (2,308) (2,907) )115,3((4,389) (4,999) <-- =-G74

Net fixed assets required, end of year 770 520,1 239 748 1,127 1,240 <-- =$B$5*G57

New assets acquired during year 481 896 386 980 327

Accumulated depreciation calculation

Accumulated depreciation, end of previous year 1,904 2,308 2,907 3,511 4,389

Depreciation of year 0 assets 308 308 154 154 154

Depreciation of assets acquired year 1 96 154 92 55 55

Depreciation of assets acquired year 2 137 219 131 79

Depreciation of assets acquired year 3 140 223 134

Depreciation of assets acquired year 4 314 188

Depreciation of assets acquired year 5 231

Accumulated depreciation 1,904 2,308 2,907 3,511 4,389 4,999 <-- =SUM(G67:G72)

Year

Depr.

rate

Cum.

1 20.00% 20%

2 32.00% 52%

3 19.20% 71%

4 11.52% 83%

5 11.52% 94%

65.76%100%

Depreciation table--5-year asset life

=VLOOKUP(3+C$59,$A$80:$B$85,2)*$B$60

=VLOOKUP(C$59,$A$80:$B$85,2)*$C$64

=VLOOKUP(D$59-1,$A$80:$B$85,2)*$D$64

=VLOOKUP(E$59-2,$A$80:$B$85,2)*$E$64

Here’s an explanation of this schedule:

•

In each of the years, the ratio of net ﬁ xed assets to sales is assumed to

be 77 percent. With anticipated sales as in row 57, the required net ﬁ xed

assets are given in row 62.

•

At date 0, the ﬁ rm’s assets are assumed to be three years old. Therefore,

the cumulative depreciation on the assets is 71 percent of their initial

value (cell C82

2,674 = 1,904).

•

The cumulative depreciation in years 1–5 is calculated by summing the

annual depreciation on each set of assets using the VLookup function

(for a description of this function, see Chapter 34).

•

The results from the cumulative depreciation calculated in the previous

screen are then referenced in row 61. The ﬁ xed assets at cost are

calculated in row 60.

133 Financial Statement Modeling

Is the Modeling of Accelerated Depreciation Worth the Effort?

As you can see, writing pro forma models to account for accelerated

depreciation is a fairly big mess! Modeling accelerated depreciation

requires additional assumptions about the schedule in use for the assets

(i.e., are assets depreciated on a three-year, ﬁ ve-year, seven-year, . . .

schedule?). It also requires the modeler to make assumptions about the

average age of existing assets; in the present example we have assumed

that assets are all depreciated on a ﬁ ve-year schedule and that existing

assets in place at date 0 are three years old. Accelerated depreciation

also requires separate depreciation schedules for assets acquired in each

year.

As a ﬁ nancial analyst, you have to ask yourself whether this is worth

the effort. In our opinion the detailed analysis of accelerated deprecia-

tion is worthwhile only in cases where the ﬁ rm has large amounts of ﬁ xed

assets that generate signiﬁ cantly large accretions to the ﬁ xed-asset base

(meaning that the cash-ﬂ ow effect of doing an accelerated-ﬁ xed-asset

calculation versus a straight-line calculation is large). In Chapter 4 we

return to this topic when we model both straight-line depreciation and

deferred taxes instead of modeling the accelerated depreciation.

Building a Financial Model: The Case of PPG Corporation

4.1 Overview

In this chapter we implement the ideas of Chapter 3 and build a ﬁ nancial

model for PPG Corporation. PPG is a large diversiﬁ ed manufacturer of

chemicals, paints, and glass (the initials stand for Pittsburgh Paint and

Glass). The company is the world’s largest manufacturer of auto glass.

In 2000, PPG had sales of $8.6 billion and proﬁ ts of $620 million.

The company has more than 35,000 employees and has paid a dividend

continuously for 103 years.

In this chapter we

•

Analyze the ﬁ nancial statements of PPG for the years 1991–2000.

•

Use our analysis to build a pro forma model for the company for the

years 2001–2005.

•

Calculate the cost of capital for PPG and use this cost of capital to

value the company and its shares.

•

Do some appropriate sensitivity analysis on our calculations.

WARNING! This Chapter May Be Deleterious to Your Mental

Health!

The material in this chapter is irritating and complicated but not

difﬁ cult. Why irritating? Because ﬁ nancial models require a lot of

assumptions and analysis. Everything is related to almost every-

thing else. And because they require you to recall some basic

accounting concepts.

However, the case in this chapter illustrates one of the most

important corporate ﬁ nance applications: the valuation of a

company in the framework of its accounting and ﬁ nancial

parameters. Such valuations are the core of most business

plans, corporate ﬁ nancial planning models, and (intelligent)

analyst valuations.

1. It certainly affected the author’s mental stability.

136 Chapter 4

4.2 PPG Financial Statements, 1991–2000

The next pages contain historical information and analysis about the

balance sheets and income statements of PPG Corporation from 1991 to

2000. In section 4.3 we analyze these statements with a view to develop-

ing parameters to use in our ﬁ nancial model for PPG. In this section we

present the statements and make several comments about them.

2. This treatment of goodwill amortization is contained in FASB 142. Note also that in

any case the depreciation of this item was never an expense for tax purposes in the

United States.

Cash and short-term invest-

ments Some ﬁ rms use this item

to build up a “nest egg” of assets

which can be used as needed (for

acquisitions, to pay for a dividend,

etc.), but PPG keeps relatively low

balances of cash. In the balance

sheets of other ﬁ rms, this item is

often called “cash and cash equiva-

lents” or “cash and marketable

securities.”

Other assets and other liabili-

ties These items are primarily the

assets and liabilities of the pension

plans of PPG. The “other assets”

represent the market value of

PPG’s pension assets and the “other

liabilities” represent the present

value of PPG’s future pension lia-

bilities. Note that the gap between

the two has increased over the last

10 years—PPG’s promised beneﬁ ts

to its employees are growing faster

than the assets from which these

beneﬁ ts are to be paid.

Intangibles This item covers

goodwill (see appendix to this

chapter for a discussion) and a

variety of other methods. Starting

in 2001, this item is no longer

depreciated.

At the end of 2000,

PPG had intangibles of $1,071

million on its books.

Accounts payable and accrued

expenses Accounts payable are

usually due to costs of goods sold

that are unpaid, whereas accrued

expenses results from other unpaid

expenses of the company (wages,

SG&A, etc.).

Deferred taxes As discussed in

appendix A.1, this item is due to

the difference in reported depreci-

ation for shareholder purposes and

the (accelerated) depreciation with

which PPG pays its taxes.

Minority interest PPG’s balance

sheets include the full value of

certain companies and subsidiaries

that it does not fully own. The

minority interest is the book value

of the equity of these companies

not owned by PPG. This topic is

discussed in the appendix.

BACDEFGH IJK

ASSETS

2000 1999 1998 1997 1996 1995 1994 1993 1992 1991

Cash and short-term investments 111 158 128 129 70 106 62 112 61 38

Receivables 1,563 1,594 1,366 1,353 1,226 1,245 1,229 997 1,023 1,058

Inventories - total 1,121 1,016 917 863 797 738 686 683 742 875

Other current assets 298 294 249 239 205 187 191 234 124 203

Total current assets 3,093 3,062 2,660 2,584 2,296 2,276 2,168 2,026 1,951 2,173

Property, plant, and equipment, gross 7,089 6,859 6,739 6,758 6,688 6,464 6,163 6,042 6,158 6,212

Depreciation, depletion, and amortization

(accumulated) 4,148 3,926 3,834 3,903 3,775 3,629 3,420 3,255 3,186 3,029

Property, plant, and equipment, net 2,941 2,933 2,905 2,855 2,914 2,835 2,742 2,787 2,972 3,183

Intangibles 1,700 1,685 632 370 23 0 0 0 0 0

Other assets 1,391 1,234 1,190 1,059 1,208 1,084 983 838 739 700

TOTAL ASSETS

9,125 8,914 7,387 6,868 6,441 6,194 5,894 5,652 5,662 6,056

LIABILITIES

Accounts payable 764 755 630 646 602 583 518 436 447 520

Accrued expense 606 649 634 564 504 520 481 423 323 348

Other current liabilities 12 26 11 8 15 41 55 67 48 62

Debt 2,971 2,790 1,718 1,701 1,482 1,221 1,144 1,129 1,307 1,574

Deferred taxes 543 520 440 406 419 355 303 269 472 476

Minority interest 128 98 87 82 76 68 70 52 60 76

Other liabilities 1,004 970 987 952 861 837 766 803 306 347

EQUITY

Common stock 484 484 484 484 484 484 484 242 242 242

Capital surplus 102 104 105 99 97 81 68 298 233 230

Retained earnings 6,019 5,786 5,489 4,916 4,569 4,063 3,494 3,158 3,424 3,369

Less: treasury stock - total dollar amount 3,508 3,268 3,198

2,990 2,667 2,060 1,489 1,225 1,200 1,186

Total stockholders equit

y 3,097 3,106 2,880 2,509 2,483 2,569 2,557 2,473 2,699 2,655

TOTAL LIABILITIES AND EQUITY

9,125 8,914 7,387 6,868 6,441 6,194 5,894 5,652 5,662 6,056

PPG CORPORATION BALANCE SHEETS, 1991-2000 (Million $)

137