Simon Benninga. Financial Modelling 3-rd edition

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

108 Chapter 3

Income Statement Equations

•

Sales = Initial sales

(1+ Sales growth)

year

•

Costs of goods sold = sales

Costs of goods sold/Sales

The assumption is that the only expenses related to sales are costs of

goods sold. Most companies also book an expense item called selling,

general, and administrative expenses (SG&A). The change you would

have to make to accommodate this item is obvious (see an exercise at

the end of this chapter).

•

Interest payments on debt = Interest rate on debt

Average debt over

the year

This formula allows us to accommodate changes in the model for

repayment of debt, as well as rollover of debt at different interest rates.

Note that in the current version of the model debt stays constant, but in

other versions of the model to be discussed later debt will vary over

time.

•

Interest earned on cash and marketable securities = Interest rate on

cash

Average cash and marketable securities over the year

•

Depreciation = Depreciation rate

Average ﬁ xed assets at cost over

the year

This calculation assumes that all new ﬁ xed assets are purchased during

the year. We also assume that there is no disposal of ﬁ xed assets.

•

Proﬁ t before taxes = Sales − Costs of goods sold − Interest payments

on debt + Interest earned on cash and marketable securities −

Depreciation

•

Taxes = Tax rate

Proﬁ t before taxes

•

Proﬁ t after taxes = Proﬁ t before taxes − Taxes

•

Dividends = Dividend Payout ratio

Proﬁ t after taxes

The ﬁ rm is assumed to pay out a ﬁ xed percentage of its proﬁ ts

as dividends. An alternative would be to assume that the ﬁ rm has a target

for its dividends per share.

•

Retained earnings = Proﬁ t after taxes − Dividends

Balance Sheet Equations

•

Cash and marketable securities = Total liabilities and equity − Current

assets − Net ﬁ xed assets

109 Financial Statement Modeling

As explained earlier, this formula means that cash and marketable

securities are the balance-sheet plug.

•

Current assets = Current assets/Sales

Sales

•

Net ﬁ xed assets = Net ﬁ xed assets/Sales

Sales

•

Accumulated depreciation = Previous year’s accumulated depreciation

+ Depreciation rate

Average ﬁ xed assets at cost over the year

•

Fixed assets at cost = Net ﬁ xed assets + Accumulated depreciation

Note that this model does not distinguish between plant property and

equipment (PP&E) and other ﬁ xed assets such as land.

•

Current liabilities = Current liabilities/Sales

Sales

•

Debt is assumed to be unchanged. An alternative model, which we will

explore later, assumes that debt is the balance-sheet plug.

•

Stock doesn’t change. (Shareholders provide no additional direct

ﬁ nancing: the company is assumed to issue no new stock or repurchase

any stock.)

•

Accumulated retained earnings = Previous year’s accumulated retained

earnings + Current year’s additions to retained earnings

4. This is not the only way to model ﬁ xed assets. An alternative method assumes that net

ﬁ xed assets is constant; see section 3.9 for an implementation.

Circular References in Excel

Financial statement models in Excel always involve cells that are mutually

dependent. As a result the solution of the model depends on the ability of

Excel to solve circular references. To make sure your spreadsheet recalculates,

you have to go to the Tools|Options|Calculation box and click Iteration. If

you open a spreadsheet that involves iteration and if this box is not clicked,

you will see the following Excel error message:

110 Chapter 3

Depending on where you are in Excel when you open the ﬁ le with the cir-

cular references, you may get a slightly different version of this message.

Whatever message you see, get out of it by clicking on Cancel and go to Tools|

Options|Calculation|Iteration. In this dialog box click the box labeled

Iteration:

A somewhat irritating feature of Excel is the inability of some versions of

the program to attach the Iteration feature to individual spreadsheets: In

some versions of Excel the Tools|Options|Calculation|Iteration feature is uni-

versal to all the spreadsheets—it is turned either off or on; in other versions

(including the latest version), Iteration can be attached to individual spread-

sheets. You’ll have to check your version of Excel to see how it works.

3.2.3 Extending the Model to Years 2 and Beyond

Now that you have the model set up, you can extend it by copying the

columns.

111 Financial Statement Modeling

Note that the most common mistake to make in the transition between

the two-columned ﬁ nancial model and this one is the failure to mark the

model parameters with dollar signs. If you commit this error, you will get

zeros in places where there should be numbers.

3.3 Free Cash Flow: Measuring the Cash Produced by the Business

Now that we have the model, we can use it to make ﬁ nancial predictions.

The most important calculation for valuation purposes is the free cash

ﬂ ow (FCF). FCF—the cash produced by a business without taking into

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 and marketable securities 8.00%

Tax rate 40%

Dividend payout ratio 40%

Yea

r 012345

Income statement

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

Costs of goods sold (500) )055()506( (666) )237( (805)

Interest payments on debt (32) )23( (32) )23()23( (32)

Interest earned on cash and marketable securities 6 9 14 20 3362

Depreciation (100) )711()731((161))981( (220)

Profit before tax 374 054

014785835294

Taxes (150) )461()081( (197) )512( (235)

Profit after tax 225 072642253323592

Dividends (90) (98) (108) (118) )921( (141)

Retained earnings 135 261841112491771

Balance sheet

Cash and marketable securities 80 312441954173982

Current assets 150 281561242022002

Fixed assets

At cost 1,070 1,264 047,1684,1 2,031 2,364

Depreciation (300) )714()455( (715) (904) (1,124)

Net fixed assets 770

239748 1,025 1,127 1,240

Total assets 1,000 1,156 315,1623,1 1,718 1,941

Current liabilities 80 88 97 106 921711

Debt 320 023023023023023

Stock 450 054054054054054

Accumulated retained earnings 150 064892038736 1,042

Total liabilities and equity 1,000 1,156 315,1623,1 1,718 1,941

FIRST FINANCIAL MODEL

112 Chapter 3

account the way the business is ﬁ nanced—is the best measure of the cash

produced by a business.

The easiest way to deﬁ ne the free cash ﬂ ow is

as follows:

Deﬁ ning the Free Cash Flow

Proﬁ t after

taxes

This is the basic measure of the proﬁ tability of the business, but it is

an accounting measure that includes ﬁ nancing ﬂ ows (such as

interest), as well as noncash expenses such as depreciation. Proﬁ t

after taxes does not account for either changes in the ﬁ rm’s working

capital or purchases of new ﬁ xed assets, both of which can be

important cash drains on the ﬁ rm.

+Depreciation

This noncash expense is added back to the proﬁ t after tax.

+After-tax interest

payments (net)

FCF is an attempt to measure the cash produced by the business

activity of the ﬁ rm. To neutralize the effect of interest payments on

the ﬁ rm’s proﬁ ts, we

• Add back the after-tax cost of interest on debt (after-tax since

interest payments are tax deductible).

• Subtract out the after-tax interest payments on cash and

marketable securities.

−Increase in

current assets

When the ﬁ rm’s sales increase, more investment is needed in

inventories, accounts receivable, etc. This increase in current assets is

not an expense for tax purposes (and is therefore ignored in the

proﬁ t after taxes), but it is a cash drain on the company.

+Increase in

current liabilities

An increase in the sales often causes an increase in ﬁ nancing related

to sales (such as accounts payable or taxes payable). This increase in

current liabilities—when related to sales—provides cash to the ﬁ rm.

Since it is directly related to sales, we include this cash in the free

cash ﬂ ow calculations.

−Increase in ﬁ xed

assets at cost

An increase in ﬁ xed assets (the long-term productive assets of the

company) is a use of cash, which reduces the ﬁ rm’s free cash ﬂ ow.

Here is the calculation for our ﬁ rm:

5. Extensive discussions of free cash ﬂ ow and its uses in a valuation context can be found

in books by Benninga and Sarig (1997) and by McKinsey & Company, Inc., et al.

(2005).

BACDEF

Year 012345

Free cash flow calculation

Profit after tax 246 270 295 323 352

Add back depreciation 117 137 161 189 220

Subtract increase in current assets (15) (17) (18) (20) (22)

Add back increase in current liabilities 8 9 10 11 12

Subtract increase in fixed assets at cost (194) (222) (254) (291) (333)

Add back after-tax interest on debt 19 19 19 19 19

Subtract after-tax interest on cash and mkt. securities (5) (9) (12) (16) (20)

Free cash flow 176 188 201 214 228

113 Financial Statement Modeling

BACDEFGH

Cash flow from operating activities

Profit after tax 246 270 323592 352 <-- =G22

Add back depreciation 117 137 981161 220 <-- =-G19

Adjust for changes in net working capital:

Subtract increase in current assets (15) )71((18)(20)(22) <-- =-(G28-F28)

Add back increase in current liabilities 8 9 1101 12 <-- =G35-F35

Net cash from operating activities 356 400 205844 562 <-- =SUM(G55:G59)

Cash flow from investing activities

Aquisitions of fixed assets--capital expenditures (194) (222) (254) (291) (333) <-- =-(G30-F30)

Purchases of investment securities 0 0 0 0 0 <-- Not in our model

Proceeds from sales of investment securities 0 0 0 0 0 <-- Not in our model

Net cash used in investing activities (194) (222) (254) (291) (333) <-- =SUM(G63:G65)

Cash flow from financing activities

Net proceeds from borrowing activities 0 0 0 0 0 <-- =G36-F36

Net proceeds from stock issues, repurchases 0 0 0 0 0 <-- =G37-F37

Dividends paid (98) )801( (118) (129) (141) <-- =G23

Net cash from financing activities (98) (108) (118) (129) (141) <-- =SUM(G69:G71)

Net increase in cash and cash equivalents 64 70 76 82 88 <-- =G72+G66+G60

Check: changes in cash and mkt. securities 64 286707 88 <-- =G27-F27

CONSOLIDATED STATEMENT OF CASH FLOWS: RECONCILING THE CASH BALANCES

3.3.1 Reconciling the Cash Balances

The free cash ﬂ ow calculation is different from the “consolidated state-

ment of cash ﬂ ows” that is a part of every accounting statement. The

purpose of the consolidated statement of cash ﬂ ows is to explain the

increase in the cash accounts in the balance sheet as a function of

the cash ﬂ ows from the ﬁ rm’s operating, investing, and ﬁ nancing activi-

ties. In the pro forma example of this section we treat the cash and mar-

ketable securities as the balance-sheet plug; however, it can also be

derived from a standard accounting statement of cash ﬂ ows.

Line 75 checks that the changes in the cash accounts derived through

the consolidated statement of cash ﬂ ows match those derived in the

ﬁ nancial model (which uses cash as a plug). As you can see, the model

works, in the sense that changes in cash balances from the consolidated

statement of cash ﬂ ows in fact match those in the projected balance

sheets of the pro forma model.

3.4 Using the Free Cash Flow to Value the Firm and Its Equity

The enterprise value of the ﬁ rm is the present value of the ﬁ rm’s future

anticipated free cash ﬂ ows. We can use the pro forma FCF projections

and a cost of capital to determine the enterprise value of the ﬁ rm.

Suppose we have determined that the ﬁ rm’s weighted average cost of

capital (WACC) is 20 percent (recall that the calculation of the WACC

114 Chapter 3

was discussed in Chapter 2). Then the enterprise value of the ﬁ rm is the

discounted value of the ﬁ rm’s projected FCFs plus its terminal value:

Enterprise value =

∞

∑

FCF

WACC

1()

Most ﬁ nancial analysts consider it presumptuous to project an inﬁ nite

number of free cash ﬂ ows; therefore, the projected cash-ﬂ ow stream is

often cut off at some arbitrary date, and a terminal value is substituted

for the cash ﬂ ows beyond this date:

Enterprise value =

FCF

WACC

FCF

WACC

FCF

WAC

11 1()()

...

( CC

WACC

)

()

Year-5 terminal value

In this formula, the year-5 terminal value is a proxy for the present value

of all FCFs from year 6 onward. Instead of projecting the FCFs from

year 6 onward, we use the most common terminal-value model:

Terminal value at end of year 5

Lon

∗+

∞

∑

FCF

WACC

FCF

()

(

gg-term FCF growth)

Long-term FCF growt

WACC

FCF

()

(

∗+

∞

∑

hh)

Long-term FCF growthWACC −

This model (based on the formula for the present value of a growing

annuity, see Chapter 1, p. 9) assumes that the year-5 cash ﬂ ow will con-

tinue to grow at a constant long-term growth rate.

Here’s an example that uses our projections:

BACDEFGHI

Valuing the firm

Weighted average cost of capital 20%

Long-term free cash flow growth rate 5% <-- real growth 2% + inflation 3%?

Year 0 1 2 3 4 5

FCF 176 188 201 214 228

Terminal value 1,598 <-- =G58*(1+B55)/(B54-B55)

Total 176 188 201 214 1,826

Enterprise value, present value of row 60 1,231 <-- =NPV(B54,C60:G60)

Add in initial (year 0) cash and mkt. securities 80 <-- =B27

Asset value in year 0 1,311 <-- =B63+B62

Subtract out value of firm's debt today (320) <-- =-B36

Equity value 991 <-- =B64+B65

6. The same method underlies the Gordon model in Chapter 2.

115 Financial Statement Modeling

Note that the long-term FCF growth rate in cell B55 is different from

the sales growth in cell B2. The sales growth is the anticipated growth

over the years 1–5; the long-term growth rate is probably better esti-

mated by making a more realistic estimate of the growth of the ﬁ rm’s

market sector. For ﬁ rms operating in a mature market, we often estimate

the long-term FCF growth as the sum of real growth plus anticipated

inﬂ ation.

3.5 Some Notes on the Valuation Procedure

In this section we cover some issues related to the valuation procedure

outlined in section 3.4.

3.5.1 Terminal Value

In determining the terminal value we used a version of the growing

annuity model described in Chapter 1. We have assumed that—after the

year-5 projection horizon—the cash ﬂ ows will grow at a long-term rate

of growth of 5 percent. This assumption gives the terminal value as

Terminal value at end of year 5

( Long-term FCF growth)

∗+

−

FCF

WACC

LLong-term FCF growth

There are other ways of calculating the terminal value. All of the

following are common variations that can be implemented in the

framework of our model (see end-of-chapter exercises):

•

Terminal value = Year-5 book value of debt + Equity

This calculation assumes that the book value correctly predicts the

market value.

•

Terminal value = (Enterprise market/Book multiple)

(Year-5 book

value of debt + Equity)

•

Terminal value = P/E ratio

Year-5 proﬁ ts + Year-5 book value of

debt

•

Terminal value = EBITDA ratio

Year-5 anticipated EBITDA

(EBITDA = Earnings before interest, taxes, depreciation, and

amortization.)

116 Chapter 3

3.5.2 The Treatment of Cash and Marketable Securities in the Valuation

We have added the initial cash balances back to the present value of the

projected FCFs to get the enterprise value. This procedure assumes

the following:

•

Year-0 balances of cash and marketable securities are not needed to

produce the FCFs in subsequent years.

•

Year-0 balances of cash and marketable securities are “surpluses” that

could be drawn down or paid out by shareholders without affecting the

future economic performance of the ﬁ rm.

A wholly equivalent assumption sometimes made by investment

bankers and equity analysts is to assume that initial cash balances are

negative debt. If you made this assumption, you would value the equity

in the following way:

BAC

Cash and marketable securities as negative debt

NPV of row 60 = enterprise value 1,295 <-- =B62

Net year 0 debt: debt minus cash (240) <-- =-B36+B27

Equity value 1,055 <-- =B69+B70

3.5.3 Midyear Discounting

While the NPV formula assumes that all cash ﬂ ows occur at the end of

the year, it is more logical to assume that they occur smoothly throughout

the year. For discounting purposes, we should therefore discount cash

ﬂ ows as if, on average, they occur in the middle of the year. Thus the

enterprise value is more logically calculated as follows:

Enterprise value =

FCF

WACC

FCF

WACC

FCF

11 1()()

...

(

WACC

FCF

WACC

)

()

Year-5 terminal value

WACC

FCF

WACC

()

...

()

(

⎡

⎣

⎢

Year-5 terminal value

CCC)

⎤

⎦

⎥

↑

This can be calculated using Excel’s NPV function.

 

∗+()

WACC

117 Financial Statement Modeling

Incorporating this midyear discounting into our value calculations

gives us the following:

BACDEFGHI

Valuing the firm--using midyear discounting

Weighted average cost of capital 20%

Long-term free cash flow growth rate 5%

Year 0 1 2 3 4 5

FCF 176 188 201 214 228

Terminal value 1,598 <-- =G79*(1+B76)/(B75-B76)

Total 176 188 201 214 1,826

Enterprise value, NPV of row 81 1,348 <-- =NPV(B75,C81:G81)*(1+B75)^0.5

Add in initial (year 0) cash and mkt. securities 80 <-- =B27

Asset value in year 0 1,428 <-- =B84+B83

Subtract out value of firm's debt today (320) <-- =B65

Equity value 1,108 <-- =B85+B86

3.6 Sensitivity Analysis

As in any Excel model, we can perform extensive sensitivity analysis on

our valuation. Taking the last case as our base case, we can ask, for

example, What is the effect of the sales growth rate on the equity value

of the ﬁ rm?

Cells B91:C100 contain a data table (see Chapter 31 if you are unsure

of how to construct these tables). While initial increases in sales growth

increase the value of the ﬁ rm, very high sales growth actually decreases

ﬁ rm value. We leave it to you to check that this result is due to the high

ﬁ xed-assets-to-sales ratio.

100

101

102

103

104

105

106

BACDEFGHI

Data table: The effect of sales growth (cell B2) on equity valuation

Growth 1,108 <-- =B87 , data table header

0% 1,093

2% 1,105

4% 1,113

6% 1,117

8% 1,115

10% 1,108

12% 1,095

14% 1,076

16% 1,049

Sales Growth and Equity Value

1,040

1,050

1,060

1,070

1,080

1,090

1,100

1,110

1,120

0% 2% 4% 6% 8% 10% 12% 14% 16%

Sales growth

Equity value