Principles of Finance with Excel (Основы финансов c Excel)

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

PFE, Chapter 8: Financial planning models page 27

use cash as a plug). The fact that row 77 is the same as row 27 shows that our model correctly

accounts for all the accounting relations. To see this, look at cells C75, C76, and C77:

•

C76 shows that at the end of year 0 the firm’s cash balances were 80.

•

C75 shows that everything the firm did during the year—sales, costs of sales, interest

paid, new financing through debt and equity, ….

everything—produced a net decrease in

cash of 16.

•

C77 is the sum of the previous two cells: If the firm started off the year with 80 in cash

and if its total activities produced -16 in cash, then the ending cash balances should be 64.

And so they are! Our model accounts for all the firm’s activities.

What’s more useful—the consolidated statement of cash flows or the free cash flow?

What’s more useful—the cash increment in row 75 of the consolidated statement of cash

flows or the free cash flow we derived in section 8.4? Although they both have their purposes,

there’s no doubt that for finance purposes, the FCF is a more useful and more widely-used

number. The FCF measures the cash produced by the firm’s business activities. It is the relevant

finance measure for the effectiveness of the firm at doing what it was founded to do—make

something and sell it. The cash increment in row 75 is also important, however: First of all, it

allows us to check that we’ve done our calculations correctly by giving a check and balance on

the cash line in the balance sheet. Second, it shows us why the cash line in the balance changed.

PFE, Chapter 8: Financial planning models page 28

Conclusion

In this chapter we’ve used Excel to construct financial planning models. These models,

also called pro forma models or financial planning models, have a variety of uses in finance.

Financial planning models are at the heart of most business plans, the financial projections which

firms use to persuade banks to loan them money and to persuade investors to buy their shares.

Financial planning models are used to value firms (see next chapter) and to build scenarios

showing how the firm will perform under various operating and financial assumptions.

Building a financial planning model is a powerful intellectual exercise: It forces you to

combine accounting statements, a firm’s operational parameters, and the firm’s financing into

one integrated model of the firm.

This chapter has concentrated on the “nuts and bolts” of building a financial planning

model. In Chapter 9 we show you how to use these models to value the firm.

PFE, Chapter 8: Financial planning models page 29

Exercises

Note: The CD-ROM which comes with Principles of Finance with Excel contains a spreadsheet

entitled

Chapter08 template.xls. although you may have to make some changes in the

template, this can be used as the basis for answering many of the exercises below.

1. Here’s a basic exercise that will help you understand what’s going on in the modeling of

financial statements. Replicate the model in section 8.3. That is, enter the correct formulas for

the cells and see that you get the same results as the book. (This turns out to be more of an

exercise in accounting than in finance. If you’re like many financial modelers, you’ll see that

there are some aspects of accounting that you’ve forgotten!)

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

administrative (SG&A) expenses. Suppose that the firm has $200 of these expenses each year,

irrespective of the level of sales. Change the model to accommodate this new assumption. Show

the resulting profit and loss statements, balance sheets, the free cash flows, and the 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 $500 per year.

3. Suppose that in the model of Section 8.3 the fixed assets

at cost for years 1 – 5 are 100% of

sales (in the current model, it is

net fixed assets which are a function of sales). Change the

model accordingly. Show the resulting Profit and Loss Statements, Balance Sheets, and Free

Cash Flows for years 1 – 5. (Assume that in year 0, the fixed assets accounts are as shown in

section 2. Note that since year 0 is given—it is the current situation of the firm, whereas years

PFE, Chapter 8: Financial planning models page 30

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. Back to the model of section 8.3 as given in the book. Suppose that the fixed assets at cost

follow the following step function:

()

Fixed Assets at Cost

Sales if Sales

Sales Sales

≤

+−<≤

+− >

⎧

⎨

⎪

⎩

⎪

100% 1200

1200 90% 1200 1200 1400

1380 80% 1400 1400

Incorporate this function into the model.

5. a. Consider the model in section 8.3. Make two changes in the model: i) Let debt be the

plug and keep cash constant at its year-0 level. ii) Suppose that the firm has 1000 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% 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%, in steps of 2%.

Additional exercises:

• Debt capacity

• CL as percent of COGS

• small case

PFE, Chapter 9: DCF valuation with financial models page 1

CHAPTER 9: DISCOUNTED CASH FLOW (DCF) VALUATION

WITH FINANCIAL PLANNING MODELS

this version: July 27, 2003

Chapter contents

Overview......................................................................................................................................... 1

9.1. What does “value of the firm” mean?..................................................................................... 3

9.2. Using the DCF valuation—summary...................................................................................... 9

9.3. Projecting the FCFs and doing the DCF valuation with a financial planning model........... 15

9.4. Advanced section: What’s the theory behind the model?.................................................... 20

Summary....................................................................................................................................... 23

Exercises....................................................................................................................................... 24

Overview

In Chapter 8 we learned how to use accounting concepts to build a financial planning

model of a company. In this chapter we use financial planning models to value a company. This

is something that almost every finance specialist has to do occasionally. The valuation technique

we employ—called discounted cash flow (DCF) valuation—is the valuation technique

Notice: This is a preliminary draft of a chapter of Principles of Finance by Simon Benninga

(benninga@wharton.upenn.edu

). Check with the author before distributing this draft (though

you will probably get permission). Make sure the material is updated before distributing it. All

the material is copyright and the rights belong to the author and MIT Press.

PFE, Chapter 9: DCF valuation with financial models page 2

universally favored by the finance profession. DCF valuations are often based on the financial

planning models discussed in Chapter 8. When these models are used to do a DCF valuation,

they are also used to do much of the sensitivity analysis which helps determine if the valuation is

reasonable.

Valuation is not intrinsically difficulty, but because there are several competing

definitions of what constitutes the “value of a firm,” people often get confused. To shed some

light on this issue, Section 9.1 discusses the different concepts of firm value. As you will see in

Section 9.1, finance specialists often identify the value of the firm with the present value of its

future cash flows. We will use the financial planning models of Chapter 8 to determine these

cash flows.

After discussing the concept of firm value in Section 9.1, we summarize the steps

involved in a DCF valuation in Section 9.2. We then go on to show you how to value a company

by building a full-blown DCF valuation model (Section 9.3).

Finance concepts used

• Present value

• Free cash flow

• Gordon model (Chapter 6)

• Terminal value

• Mid-year valuation

Excel functions used

• NPV

PFE, Chapter 9: DCF valuation with financial models page 3

• Data tables

9.1. What does “value of the firm” mean?

The terms “value of a company” or “value of a firm” are often used interchangeably by

finance professionals. Even finance professionals, however, can use a confusing variety of

meanings for these terms. Here are a few of the meanings which are often intended:

• In finance the definition most often used for “firm value” is the following: The value

of a firm is the market value of the firm’s equity plus the market value of the firm’s

financial debt. This section illustrates two methods of computing the firm value.

o The simplest method is to value the firm’s equity (its shares) using the firm’s

share price in the market, and to add to this the value of the firm’s debt.

o A second method, the DCF method, is based on discounted cash flows. In a

DCF valuation firm value equals the present value of the firm’s futures FCFs

plus the value of its currently available liquid assets.

• Often when individuals discuss the firm value, they really mean the value of its

shares. It is better to use the term equity value for the value of a company’s shares

and to use the term firm value (or company value) to denote the market value of the

firm’s equity plus its debt. In our calculations we also show you how to compute the

value of a firm’s shares.

• Sometimes the term firm value is used to denote the accounting value of the firm.

Also known as the book value, this value is based on the firm’s balance sheets.

Because accounting statements are based on historical values, people in finance

PFE, Chapter 9: DCF valuation with financial models page 4

generally prefer not to use this definition. At the end of this section we illustrate why

we do not like this valuation method.

Motherboard Shoes: What’s it worth?

To illustrate the different concepts of firm value, we’ll tell the story of Motherboard

Shoes. Motherboard is listed on the Chicago Stock Exchange, but the majority of the stock is

owned by the Motherboard family, which founded the company and still runs it. The current

date is 1 January 2005, and the Motherboards have received an offer for their shares from

Century Shoe International. They would like to know if the offer is a fair one.

Their investment advisor, John Mba has advised them that there are two plausible ways

to value the company (John just finished business school and liked it so much that he changed

his last name to reflect his new status). Each of the two methods has advantages and

disadvantages.

The share price valuation: Valuing a Motherboard by using current share price

The simplest way to value Motherboard is look at the value of its share. Motherboard

Shoes has one million shares, which were trading on 31 December 2004 at $50 per share. Thus

the market value of the firm’s equity is $50 million. In addition the company’s balance sheet

shows that it has short-term debt of $2.5 million and long-term debt of $7.5 million; John Mba

PFE, Chapter 9: DCF valuation with financial models page 5

uses these balance sheet values (also called book values) of the debt as an approximation to the

debt’s market value.

Using the current share price, the firm value of Motherboard Shoes is $60 million:

ABC

Number of shares 1,000,000

Current share price 50.00

Market value of equity 50,000,000 <-- =B2*B3

Short-term debt 2,500,000

Long-term debt 7,500,000

Firm value 60,000,000 <-- =B4+B6+B7

MOTHERBOARD SHOES

The discounted cash flow (DCF) valuation: Valuing Motherboard by discounting its

future free cash flows

The advantage of the share-price valuation method illustrated above is that it is very

simple: The firm value equals the market value of the firm’s shares plus the book value of its

debt. Valuing the company at its current price of $50 per share is perfectly acceptable for

someone considering buying a few shares of the company, but it makes less sense if

Motherboard Shoes is selling a controlling block of shares. In this case the purchaser would

probably expect to pay more for the following reasons:

• If the purchaser tried to buy a big block of shares of Motherboard shares on the open

market, he would probably have to offer more than the current market price per share.

As he bought more and more shares, the price would go up; in addition, the

This is common practice. Most company debt is not traded on financial markets, and therefore there is no easily-

available market value for the debt. As a first approximation, most finance professionals use the book value of a

firm’s debt as a proxy for the debt’s market value.

PFE, Chapter 9: DCF valuation with financial models page 6

announcement that someone was trying to take over Motherboard Shoes would—in

many cases—force the share price up.

• There are benefits to controlling a company that are not priced in the market price per

share. The market price of a share reflects the value of a company’s future dividends

to a passive shareholder who has no control over the company. In general the value

of a controlling block of shares is larger than the market value, since the controlling

shareholder can actually decide what the company will do. He can also derive

considerable private benefits from running the company.

To deal with these problems, John Mba proposes to use the discounted cash flow (DCF)

valuation method to value the shares. DCF valuations are a standard finance methodology,

which defines the value of the firm as the present value of the firm’s future free cash flows

(FCF), discounted at the weighted average cost of capital (WACC), plus the firm’s initial cash

and marketable securities. Section 9.4 discusses the theory behind this method of valuation, but

for the moment we skip all the theory and simply present the formula:

Often called the "enterprise value"

of the firm

Market value Market value

DCF firm value

of firm s debt of firm s equity

all future FCFs Current cash and

discounted at WACC marketable securities

↑

⎛⎞

⎜⎟

⎝⎠



Economists use the term private benefits to discuss all kinds of financial and non-financial benefits associated with

firm ownership. The big car with a driver that the company gives its president is a private benefit of ownership, and

so is the feeling of ownership—a psychological benefit, perhaps, but nonetheless valuable.