Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 7: Accounting principles page 31
• The business will last another 6 years, until 31 December 2010. At this date it will
quietly expire.
5
Brother-Sister assume that at the end of 2010 they’ll be able to sell the
building for $150,000. At that point the building will have been on ATS’s books for 6 ½
years. Since its monthly depreciation is $666.67, its accumulated depreciation will be
$52,000 (= 78 months *$666.67). Because the building was purchased for $80,000, its
book value at the end of 2010 will be $28,000 (book value = initial cost minus
accumulated depreciation). The net, after-tax cash flow from selling the building is given
below:
1
2
3
4
5
6
7
8
9
10
11
12
13
ABC
Book value of building
Initial cost 80,000
Accumulated depreciation, end 2010 52,000 <-- =78*666.67
Book value 28,000 <-- =B3-B4
Market value 150,000
Taxable gain 122,000 <-- =B7-B5
State tax on marketable gain (5%) 6,100 <-- =5%*B8
Gain for Federal tax purposes 115,900 <-- =B8-B9
Federal tax (36%) 41,724 <-- =36%*B10
Total taxes 47,824 <-- =B11+B9
Net after-tax cash flow from sale of building 102,176 <-- =B7-B12
ANYTOWN TRAVEL SERVICES (ATS), INC.
Building residual value at end-2010
5
A lot of the value of the business derives from the fact that Anytown didn’t have a taxi service. Brother-sister
figure that in another few years, more taxi operators will enter the business and make the business less profitable.
They’ve already started lobbying the Anytown city council to introduce strict taxi licensing regulations (with
existing operators grandfathered in ... ).
PFE, Chapter 7: Accounting principles page 32
Valuing the business
To value the business, ATS need to compute the cash flows and discount them by an
appropriate cost of capital. They assume that the appropriate discount rate is 30%. Here’s their
calculation:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ABCDE
Date FCF
A
dditional capex Residual value Total
31-Dec-04 206,658 206,658
31-Dec-05 206,658 -40,000 166,658
31-Dec-06 206,658 206,658
31-Dec-07 206,658 206,658
31-Dec-08 206,658 -40,000 166,658
31-Dec-09 206,658 206,658
31-Dec-10 206,658 102,176 308,834
Discount rate 30%
Enterprise value =
PV of future free cash flows
560,922 <-- =NPV(B11,E3:E9)
Add back cash balances
on 31 December 2003
179,436
Asset value of firm,
31 December 2003
740,358
Debt on 31 December 2003 57,000 <-- Outstanding mortgage
Value of equity 683,358 <-- =B15-B16
ANYTOWN TRAVEL SERVICES (ATS), INC.
Quick and dirty valuation
Here are the details of the valuation:
•
Assuming a discount rate of 30% for the cash flows, the enterprise value on 31 December
2003 at $560,922. We use the term
enterprise value to denote the present value of the
firm’s future free cash flows and terminal value.
6
•
In addition to this enterprise value, the firm has $179,436 cash on hand on 31 December
2003. Adding this cash to the enterprise value gives the asset value of the firm on this
date as $740,358.
6
We have an extended discussion of the enterprise value in the next chapter.
PFE, Chapter 7: Accounting principles page 33
• Since there is a mortgage outstanding on this date of $57,000, brother-sister’s equity
stake in the business is worth $683,358 = $740,358 - $57,000.
Not bad for a year’s work!
Technical comment: mid-year discounting
In Chapter 4, section000, we discussed the concept of mid-year discounting to account
for the fact that cash flows occur throughout the year and not at year’s end. Mid-year
discounting assumes that the annual cash flows occur in mid-year, and not at year’s end.
Had Brother and Sister valued their firm by using mid-year discounting, they would have
concluded that the equity value of the Anytown Travel Services was $761,985:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ABCDE
Date FCF
A
dditional capex Residual value Total
31-Dec-04 206,658 206,658
31-Dec-05 206,658 -40,000 166,658
31-Dec-06 206,658 206,658
31-Dec-07 206,658 206,658
31-Dec-08 206,658 -40,000 166,658
31-Dec-09 206,658 206,658
31-Dec-10 206,658 102,176 308,834
Discount rate 30%
Enterprise value =
PV of future free cash flows
639,549 <-- =NPV(B11,E3:E9)*(1+B11)^0.5
Add back cash balances
on 31 December 2003
179,436
Asset value of firm,
31 December 2003
818,985
Debt on 31 December 2003 57,000 <-- Outstanding mortgage
Value of equity 761,985 <-- =B15-B16
ANYTOWN TRAVEL SERVICES (ATS), INC.
Valuation assuming mid-year discounting
PFE, Chapter 7: Accounting principles page 34
Conclusion
In this chapter we’ve reviewed the basic methodology of financial accounting. We’ve
reviewed the construction of the balance sheet, the profit and loss statement, and the statement of
cash flows of a business. In addition we showed how to construct a free cash flow statement and
how to use this statement to value a business.
PFE, Chapter 8: Financial planning models page 1
CHAPTER 8: FINANCIAL PLANNING MODELS
*
this version: 24 July 2003
Chapter contents
Overview......................................................................................................................................... 1
8.1. Initial accounting statements for a financial planning model................................................. 3
8.2. Building a financial planning model.......................................................................................8
8.3. Extending the model to years 2 and beyond......................................................................... 18
8.4. Free cash flow (FCF): measuring the cash produced by the firm’s operations................... 21
8.5. Reconciling the cash balances—the consolidated statement of cash flows.......................... 25
Conclusion .................................................................................................................................... 28
Exercises....................................................................................................................................... 29
Overview
This chapter explains how to build spreadsheet models that allow you to predict the
future performance of a firm. These models are called financial planning models or pro forma
models. In accounting jargon a “pro forma” statement is something that looks like an accounting
statement but that is forward looking. Financial planning models look like accounting
statements; however, whereas accounting statements report what happened to the firm in the
*
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 8: Financial planning models page 2
past, a financial planning model predicts what the firm’s accounting statements will look like in
the future.
Financial planning models have a variety of uses:
• Projecting future financing needs of the firm: Building a financial planning model helps
you predict whether the firm will need financing in the future. It also helps you tie the
firm’s financing needs to its future performance. For example—does an increase in the
growth rate of sales create cash or use cash? The answer is not always clear: More sales
produce more profits (and hence produce more cash). However, an increase in the
growth rate of sales may also require more capital investment (machines, land, etc.) and
may require greater net working capital. A financial planning model can help us sort out
these two opposing trends.
• Business plans: When you make a business plan (which you then take to investors to get
financing or to a bank to explain why you need a loan and can pay it back), you’ll often
need to build a pro forma model of your firm. The model you build illustrates your
assumptions about the financial and business environment in which your firm will
operate in the future. Valuation: They can be used to predict the future free cash flows,
dividends, and profits of a firm. Chapter 9 shows how to use the pro forma prediction of
future cash flows to value a firm.
In this chapter we develop a simple model that illustrates the methodology of financial
planning models. In the next chapter we will use a financial planning model to value the firm.
PFE, Chapter 8: Financial planning models page 3
Finance and accounting concepts used in this chapter
• This chapter assumes familiarity with the accounting concepts reviewed in Chapter 7.
• Net present value, present value
• Cash flows and free cash flows (FCF)
Excel concepts and functions used in this chapter
• Excel formulas and model building
• Relative versus absolute copying
• Circular references
• Data tables
8.1. Initial accounting statements for a financial planning model
Financial planning models are predictions of what a firm’s future financial statements
will look like. To build such a model we start with the present—the firm’s current financial
statements. To illustrate the process by which financial planning models are constructed, in the
next section we will project five years of financial statements for Whimsical Toenails, a
company which runs a chain of toenail-painting parlors. Whimsical’s management and bankers
want to project the firm’s future performance, and we will help them by constructing a financial
planning model.
Our starting point is Whimsical Toenail’s current income statement and balance sheet:
PFE, Chapter 8: Financial planning models page 4
WHIMSICAL TOENAILS
INITIAL INCOME STATEMENT
Sales 1,000
Cost of goods sold -500
Depreciation -100
Interest payments on debt -32
Interest earned on cash 6
Profit before tax 375
Taxes -150
Profit after tax 225
Dividends -90
Retained earnings 135
WHIMSICAL TOENAILS
INITIAL BALANCE SHEET
Assets Liabilities and equity
Cash 80 Current liabilities 80
Current assets 150 Debt 320
Fixed assets
Fixed assets at cost 1,070 Equity
Accumulated depreciation -300 Stock (paid-in capital) 450
Net fixed assets 770 Accumulated retained earnings 150
Total assets 1,000 Total liabilities and equity 1,000
Accounting terminology versus financial planning model terminology
Before proceeding further, some comments on our use of terminology. While most of the
terminology in this chapter follows the standard accounting nomenclature, some changes are
necessary to accommodate the structure of financial planning models. For example, while
accountants use “current assets” to denote both operating and financial short-term assets,
financial planning models use “current assets” to mean only operating short-term assets (to
emphasize this point, the terminology “operating current assets” is sometimes used). Similarly,
in the accounting framework “current liabilities” includes both operational items (like accounts
payable—bills which are as yet unpaid by the firm) and financial items (like short-term debt and
current portion of long term debt). Financial planning models use “current liabilities” to denote
PFE, Chapter 8: Financial planning models page 5
operational items. To emphasize this point, we sometimes use the terminology “operating
current liabilities.”
Current assets—what’s included in the financial planning model and what’s not?
In financial planning models the “current assets” category contains only items that are
related to the operations of the firm. Here are several typical items that would be included in the
financial planning model definition of current assets.
• Accounts receivable: These are payments due from customers and are generated by
the operations of the firm. Since accounts receivable are generated by the firm’s
sales, they are included in the operating current assets of the financial planning
model.
• Inventories: Inventories include both raw materials to be used for production and
unsold finished products. Inventories are part of the operating current assets of the
financial planning model.
• Prepaid expenses: Prepaid expenses are costs which the firm pays before it actually
receives the associated services. An example might be rent paid by the firm for future
periods: If the firm pays this rent in advance (for example, not month-by-month, but
6 months in advance), then this prepayment of the rent is recorded by the accountant
as a prepaid expense and is recorded as a current asset. For our financial planning
model, we usually assume that these are part of operating current assets.
Which accounting current assets are not included in the financial planning model
definition of current assets? There are two important examples:
PFE, Chapter 8: Financial planning models page 6
• Cash: The “cash” item on the balance sheet refers to money kept in the firm’s bank
accounts. Sometimes the accounting line item is called “cash and equivalents,” with the
second term denoting assets like certificates of deposit and money market accounts which
can be easily converted into cash. “Cash” is an operating current asset to the extent that it
is needed by the firm for its daily operations. In most cases, however, the cash accounts
on the balance sheets simply refer to non-operating assets which are kept in liquid form
by the firm.
• Marketable securities: This item on the balance sheet refers to other financial assets—
such as stocks and bonds—bought by the firm. Marketable securities are not needed for
the firm’s operations, and are thus not an operating current asset.
The distinction between cash as an operating asset and cash as a store of value is usually
obvious once you understand the business of the firm. A taxi driver needs to keep some cash on
hand in order to make change for his customers, and a supermarket needs to keep some cash in
the till for the same reason; in these cases at least some of the cash is an operating current asset
(although even for a taxi or supermarket, most of the cash is likely to be a financial, non-
operating current asset). On the other hand, in March 2003, Microsoft reported having $4.3
billion in cash and another $41.9 billion in marketable securities. It is unlikely that almost any of
this $46.2 billion is needed for daily operations. It is not an operating current asset, but rather a
financial current asset.