Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 8: Financial planning models page 7
Current liabilities
For purposes of the financial planning model, “current liabilities” contains only items that
are related to the operations of the firm. Here are two typical items that would be included in the
current liabilities of our financial planning model:
• Accounts payable: These are unpaid bills the firm owes to its suppliers. Since this
item is related to the operations of the firm, we include it in the financial planning
model definition of current liabilities.
• Taxes payable: When a firm’s payment of taxes does not coincide with the
accounting period, the taxes owed are entered into the balance sheet as a current
liability. For example, for the accounting year ending 31 December 2005, XYZ
Corp. owes the $2,000 in taxes, but it won’t pay this tax bill until 15 January 2006.
The financial statements of XYZ Corp. for 2005 will report taxes of $2,000 in the
profit and loss statement; the firm’s balance sheet will report taxes payable of $2,000
in the current liabilities. Taxes payable relate to the firm’s operations and are
included in the financial planning model definition of current liability.
Accounting current liability items that are not included in the financial planning model
definition of current liabilities are typically financial items. Here are two examples:
• Short-term debt: These are borrowings by the firm that are due within one year. A
bank overdraft (a credit line on a business’s checking account) is a good example of a
short-term debt. Accountants include this item in current liabilities, but financial
planning models include them as debt.
• Current portion of long-term debt: This is the portion of the firm’s debt that is due
for payment within the current financial year. Accountants include this item in
PFE, Chapter 8: Financial planning models page 8
current liabilities; financial planning models include the current portion of long-term
debt in the debt category.
8.2. Building a financial planning model
Now that we have our terminology straight, we can build our financial planning model
for Whimsical Toenails. A typical financial planning model has three major components:
• The model parameters. Also called the value drivers, a financial planning model’s
parameters include the major assumptions of the model. For example, we might assume
that the sales growth parameter is 10% per year. Or we might assume that the current
assets to sales parameter is 15%—meaning that an increase of $1,000 in sales requires an
additional $150 of current assets. Typically, financial-statement models are sales-driven;
this term means that many of the most important financial statement value drivers are
assumed to be functions of the firm’s sales.
• The financial policy assumptions. We will make assumptions about how the firm
finances itself in the future. What is the mix between debt and new equity issued? Does
excess cash produced by the firm go towards repaying debt or does it end up in the firm’s
cash balances? These assumptions are important determinants of the firm’s future
financial statements.
• The pro forma financial statements. Once we decide on the financial model’s parameters,
we will build the pro forma financial statements for the firm we are modeling—the
income statement, balance sheets, and free cash flows.
PFE, Chapter 8: Financial planning models page 9
When we’ve used the model’s parameters and the financing assumptions to project the
future financial statements of the firm, we can then use the model. By varying the model’s
assumptions, we can use the financial planning model to build different scenarios of how the
firm will perform in the future. In Chapter 9 we will use the financial planning model to project
the future free cash flows of the firm in order to value the firm. We might also want to use the
model to evaluate the ability of the firm to repay its debts (there’s an end-of-chapter exercise
which illustrates this use).
The model’s parameters—the value drivers
The sales growth parameter is usually the most important parameter of the financial
planning model. In our example Whimsical Toenails current (year 0) level of sales is 1,000.
Over the five-year horizon of the financial planning model, the firm expects its sales to grow at a
rate of 10 percent per year.
Other model parameters are derived from the following financial statement relations.
1
• Current assets: We assume that Whimsical’s end-year current assets on the balance sheet
will be 15 percent of the annual firm sales.
• Current liabilities: We assume that Whimsical’s end-year current liabilities on the
balance sheet will be 8 percent of the annual firm sales
• Net fixed assets: End-year net fixed assets are assumed to be 77 percent of annual sales.
• Depreciation: The annual depreciation charge is 10 percent of the average value of the
fixed assets on the books during the year.
1
In practice the model’s parameters are often derived from an analysis of the company’s historic financial
statements.
PFE, Chapter 8: Financial planning models page 10
• Cost of goods sold: Assumed to be 50 percent of sales.
• Interest rate on debt: 10 percent.
• Interest earned on cash: Whimsical Toenails earns 8 percent on the average balances of
cash.
• Tax rate: 40 percent of the firm’s profit before taxes.
• Dividends paid: We assume that Whimsical Toenails pays out 40 percent of its profits
after taxes as dividends to shareholders.
The model’s financial policy assumptions
The second component of a financial planning model is the model’s financial policy
assumptions. In this initial financial planning model we make the following assumptions:
• Debt: Whimsical currently has debt of 320 on its balance sheet. The company’s
agreement with the bank specifies that it will repay 80 of this debt in each of the next four
years. Once the debt is fully repaid, the company intends to stay debt-free.
• Stock: Company management does not intend to either issue new stock nor repurchases
stock over the five-year model horizon. The stock item in the firm’s balance sheets thus
remains at its year-0 level of 450.
• Cash. In our model this item is the plug: The cash item is defined so that the left-hand
side of the balance sheet always equals the right-hand side of the balance sheet:
Cash =Total liabilities and equity - Current assets - Net fixed assets
The “plug” is the balance sheet item which guarantees the equality of the future projected
total assets and the future projected total liabilities and equity. Every financial planning model
has a plug, and the plug is almost always either cash (as in this case) or debt or stock.
PFE, Chapter 8: Financial planning models page 11
To see how the plug fits into our model, consider the projected future balance sheets:
WHIMSICAL TOENAILS
balance sheet model assumptions
Assets Liabilities and equity
Cash [PLUG] Current liabilities [8% of sales]
Current assets [15% of sales] Debt [repaid by 80/year until zero]
Fixed assets
Fixed assets at cost
- Accumulated depreciation
[10% of average assets]
Net fixed assets [77% of sales]
Equity
Stock (paid in capital) [constant]
Accumulated retained earnings
[previous year’s accumulated retained
+ this year’s retained from income
statement]
Total assets Total liabilities and equity
The “plug” assumption has two meanings:
1. The mechanical meaning of the plug: The plug guarantees the equality of total assets and
total liabilities and equity. For the Whimsical Toenails financial model, cash is the plug.
By defining cash to equal the total liabilities and equity minus current assets and minus
net fixed assets, we guarantee that future projected assets and liabilities will always be
equal. This is important, since the two sides of the balance sheet must always be equal.
2. The financial meaning of the plug: Whimsical Toenails sells no additional stock and is
locked into a debt repayment schedule. By defining the plug to be cash, we are also
making a statement about how the firm finances itself. For the case of Whimsical
Toenails, this means that all incremental financing (if needed) for the firm will come
from the cash; it also means that if the firm has additional cash, it will go into this
account.
PFE, Chapter 8: Financial planning models page 12
Projecting the year 1 balance sheet and income statement
Given our assumptions we can now develop the pro forma model and project the
financial statements for year 1:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
ABCD
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 earned on cash balances 8.00%
Tax rate 40%
Dividend payout ratio 40%
Year 0 1
Income statement
Sales 1,000 1,100 <-- =B15*(1+$B$2)
Costs of goods sold (500) (550) <-- =-C15*$B$6
Depreciation (100) (117) <-- =-$B$7*(C30+B30)/2
Interest payments on debt (32) (28) <-- =-$B$8*(B36+C36)/2
Interest earned on cash and marketable securities 6 6 <-- =$B$9*(B27+C27)/2
Profit before tax 374 411 <-- =SUM(C15:C19)
Taxes (150) (164) <-- =-C20*$B$10
Profit after tax 225 247 <-- =C21+C20
Dividends (90) (99) <-- =-$B$11*C22
Retained earnings 135 148 <-- =C23+C22
Balance sheet
Cash 80 64 <-- =C39-C28-C32
Current assets 150 165 <-- =C15*$B$3
Fixed assets
At cost 1,070 1,264 <-- =C32-C31
Depreciation (300) (417) <-- =B31-$B$7*(C30+B30)/2
Net fixed assets 770 847 <-- =C15*$B$5
Total assets 1,000 1,076 <-- =C32+C28+C27
Current liabilities 80 88 <-- =C15*$B$4
Debt 320 240
<-- =B36-80
Stock 450 450 <-- =B37
Accumulated retained earnings 150 298 <-- =B38+C24
Total liabilities and equity 1,000 1,076 <-- =SUM(C35:C38)
WHIMSICAL TOENAILS
SETTING UP THE FINANCIAL STATEMENT MODEL
for year 1
PFE, Chapter 8: Financial planning models page 13
Income statement equations
Here are the relations for our financial planning model, with model parameters in bold
face. These relations will end up as the formulas in the cells of our Excel model.
• Sales = Initial sales * (1+ Sales growth)
year
.
Alternatively: Sales(t) = Sales(t-1)*(1+Sales growth)
• Costs of goods sold = Sales * Costs of goods sold/Sales
We assume that Whimsical’s 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). Exercise 2 at the end of this chapter illustrates how you would
introduce SG&A into the model.
• Interest payments on debt = Interest rate on debt * Average debt over the year. We
use this formula to estimate Whimsical’s interest payments on the debt. For example:
If the company’s debt at the end of year 0 is 320 and its debt at the end of year 1 is
240, the financial planning model estimates its year-1 interest payments as:
Excel note: Relative versus absolute copying
The dollar signs within a formula indicate that when the formulas are copied the cell
references to the model parameters should not change. The technical jargon for this in Excel is
absolute copying as opposed to the relative copying when variables are indicated without dollar
signs. The distinction between absolute and relative copying is critical for financial planning
models—if you fail to put the dollar signs correctly in the model, it will not copy correctly when
you project years 2 and beyond.
The use of relative versus absolute copying is explained in Chapter 27.
PFE, Chapter 8: Financial planning models page 14
N
Whimsical's
average debt
in year 1
320 240
10%* 10%* 280 28
2
↑
+
== .
• Interest earned on cash = Interest rate on cash * Average cash over the year.
• Depreciation = Depreciation rate * Average fixed assets at cost over the year. This
assumes that all new fixed assets are purchased during the year. We also assume that
there is no disposal of fixed assets. Looking at the financial model may help you
understand the calculation of the depreciation: Whimsical’s year-0 fixed assets at
cost are $1,070 and its projected year-1 fixed assets at cost are $1,264. Since the
company’s depreciation rate is 10%, its year-1 depreciation in the income statement
is:
N
Average fixed
assets at cost
in year 1
1,070 1,264
10%* 10%* 1,167 117
2
↑
+
==
•
Profit before taxes = Sales - Costs of goods sold - Interest payments on debt + Interest
earned on cash & marketable securities – Depreciation.
•
Taxes = Tax rate * Profit before taxes
•
Profit after taxes = Profit before taxes - Taxes
•
Dividends = Dividend payout ratio * Profit after taxes.
Whimsical Toenails has a policy of paying out a fixed percentage of its profits as
dividends. In the exercises for this chapter we explore some alternative dividend
policies.
•
Retained earnings = Profit after taxes - Dividends
PFE, Chapter 8: Financial planning models page 15
Balance sheet equations
• Cash = Total liabilities – Current assets – net fixed assets.
As explained above, this definition means that cash is the balance sheet plug.
•
Current assets = Current assets/sales * Sales
•
Net fixed assets = Net fixed assets/sales * Sales .
•
Accumulated depreciation = Previous year’s accumulated depreciation +
Depreciation rate * average fixed assets at cost over the year.
•
Fixed assets at cost = Net fixed assets + accumulated depreciation.
Note that this model does not distinguish between plant, property, and equipment
(PP&E) and other fixed assets such as land.
•
Current liabilities = Current liabilities/Sales * Sales .
2
•
Debt is assumed to be unchanged. This means that during the model’s 5 year
horizon, we assume that the firm neither increases its borrowing nor pays back any of
its initial debt principal. An alternative model which assumes that debt is the balance
sheet plug; this model is the subject of one of our end-of-chapter exercises.
•
Stock is assumed to be unchanged. The company is assumed to issue no new stock.
2
Some modellers prefer to model current liabilities as a percentage of the firm’s costs of goods sold (COGS). The
thinking here is that—because current liabilities include the firm’s accounts payable (which in turn include the
firm’s unpaid bills for inventories and the like)—current liabilities are largely dependent on the level of the firm’s
costs of goods sold. While it is easy to incorporate this assumption in our model (see example ??? at the end of the
chapter), it doesn’t make much difference: If COGS are a percentage of sales and current liabilities are a percentage
of sales, then the current liabilities are also a percentage of the COGS.
PFE, Chapter 8: Financial planning models page 16
• Accumulated retained earnings = Previous year’s accumulated retained earnings +
current year’s additions to retained earnings