Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 7: Accounting principles page 21
Here’s the whole statement:
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
ABCD
Consolidated statement of cash flows
Cash flow from operating activities
Profit after taxes 69,116 <-- =H131
Add back depreciation 5,250 <-- =-H127
Subtract increase in current assets -16,000 <-- =-SUM(B136:B137)
Add increases in current liabilities 58,562 <-- =E122+E123+E124
Cash provided from operating activities
116,928
Cash flow from investing activities
Payments for fixed assets
Taxis -36,000
Computer -2,000
Building -80,000
Purchases of marketable securities 0
Proceeds from sales of marketable securities 0
Cash used in investing activities
-118,000
Cash flow from financing activities
Proceeds from new debt 68,000
Debt repayments -3,200
Cash dividends paid 0
New stock sold 0
Stock repurchased 0
Cash used in financing activities
64,800
Net change in cash over period
63,728 <-- =B159+B168+B176
Initial cash balances 25,000
Ending cash balance
88,728
During the period January – June, ATS had a net cash inflow of $63,728 (cell B178).
Added to the initial cash balance of the company over this period, $25,000, the ending cash
balances should be $88,728. And indeed they are—this is the cash balance listed on the
company’s end-June balance sheet.
7.4. Computing the free cash flow (FCF)
The consolidated statement of cash flows gives the amount of cash generated by ATS
during its first half year of existence. For finance purposes (recall, dear reader, that this is a
PFE, Chapter 7: Accounting principles page 22
finance and not an accounting book!), it is useful to know how much cash was generated by the
firm’s operations. Our measure of this is the free cash flow (FCF). The consolidated statement
of cash flows does not give this information, since it mixes operational and financial cash flows.
The FCF is defined as:
DEFINITION OF THE FREE CASH FLOW (FCF)
Explanation
Profit after taxes
Add back depreciation Depreciation is a non-cash expense and is
therefore added back.
Subtract increase in current assets
used for operations
For purposes of the FCF, this item does not
include cash or marketable securities
Add increase in current liabilities
from operations
Accounting current liabilities include items
like short-term debt and current portion of
long-term debt. These financial items are not
included in the FCF.
Subtract increase in fixed assets at
cost
This represents the amount spent on new
assets over the period. In the jargon of Wall
Street it is often called “capital expenditures”
(CAPEX).
Add back after-tax interest expenses The FCF is an operating concept: It relates
to cash generated by the firm’s operations.
Interest expenses are a financial (non-
operating) item and should therefore be
added back. On the other hand, the profit
after taxes includes only
after-tax interest;
this is the amount added back in the FCF
calculation.
Note that in our case the firm’s
effective tax
rate is
N
(
)
N
Federal State tax
Federal tax
tax rate rate
deductability
of state tax
36% 1 36% * 5% 39.2%+− =
FCF The free cash flow is the amount of cash
generated by the firm’s operations or
business activities. Another way of looking
at this is that the FCF is the amount of cash
generated by the firm if everything were
financed with equity.
PFE, Chapter 7: Accounting principles page 23
To compute the FCF it is handy to put the profit and loss and the balance sheet in two
side-by-side columns, one for the start of the period (1 January 2003) and one for the end of the
period (30 June 2003).
PFE, Chapter 7: Accounting principles page 24
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
ABC D
Profit and loss for period ending 1-Jan-03 30-Jun-03
Sales 214,000
Cost of sales -94,800
Interest -272
Depreciation -5,250
Profit 113,678
State income tax (5%) -5,684
Federal income tax (36%) -38,878
Profit after taxes 69,116
Dividends 0
Retained earnings 69,116
Balance sheet--Assets 1-Jan-03 30-Jun-03
Current assets
Cash 25,000 88,728
Accounts receivable 0 10,000
Prepaid expenses (insurance) 0 6,000
Total current assets 25,000 104,728
Fixed assets
At cost 0 118,000
Accumulated depreciation 0 -5,250
Net fixed assets 0 112,750
Total assets 25,000 217,478
Balance sheet--Liabilities and equity 1-Jan-03 30-Jun-03
Current liabilities
Short-term loan from bank (taxi loan) 4,800
Taxes payable 0 44,562
Accounts payable (gasoline) 0 14,000
Total current liabilities 0 63,362
Long-term liabilities
Mortgage to buy building 0 60,000
Equity
Stock 25,000 25,000
Retained earnings 0 69,116
Total equity 25,000 94,116
Total liabilities and equity 25,000 217,478
Free cash flow (FCF)
Profit after taxes 69,116
Add back depreciation 5,250
Change in net working capital
Subtract increase in operating current assets -16,000
<-- =-SUM(C17:C18)-SUM(B17:B18). Changes in
cash and marketable securities are not included
Add increase in operating current liabilities 58,562
<-- =C32-C29-B32. The taxi loan is financing and not
operational
Change in net working capital 42,562 <-- =C49+C48
Change in fixed assets at cost -118,000 <-- =-(C22-B22)
Add back after-tax net interest paid 165 <-- =-(1-39.2%)*C5
Free cash flow -906 <-- =C44+C45+C50+C52+C54
ANYTOWN TRAVEL SERVICES (ATS), INC.
Calculating the Free Cash Flow (FCF)
PFE, Chapter 7: Accounting principles page 25
So what does the FCF of -$906 mean? If ATS had paid all its operating expenses in cash
and collected all its operating payments in cash during the first 6 months of its existence, it
would be “in the hole” $906.
7.5. Stop and think!
So . . . Did Brother-Sister do well this half year? Or should we be worried by their
negative free cash flow?
•
The negative FCF tells us that the business did not produce enough cash to pay all of its
expenses and capital costs. But, of course, the “changes in fixed asset at cost” item of
$118,000 (cell C52) includes several long-term investments (two taxis, a computer, and a
building) which are expected to produce revenues over a long period of time.
•
The accounting profits of $69,611 deal with these long-term investments by including
only their
depreciation as a cost of operating the business. Conceptually, this
depreciation attributes the cost of having a long-term asset to the current period’s income.
7.6. The next half year
The months July – December 2003 were a period of stabilization for ATS. During this
period the company bought no more new assets. Here’s what happened:
•
Sales continued at $50,000 per month, for a total of $300,000.
PFE, Chapter 7: Accounting principles page 26
• The flour mill continued to rack up $10,000 of fares per month. It paid its bills faithfully
at the end of each month, so that at end-December 2003 it had only December’s $10,000
outstanding.
•
The two drivers continued to make $1,500 per month each. This made their total salaries
for the period $18,000.
•
The two owners continued to take $2,000 per month salary, for a total of $24,000 for
these six months.
•
Gasoline bills continued to be $7,000 per taxi per month. Total fuel bills for the period
were 7*$14,000 = $98,000. ATS was paying its gasoline bill at the end of the following
month, so that at year end it had $14,000 of gasoline bills outstanding. The remainder
($84,000) had been paid in full.
•
The “prepaid expense (insurance)” became an actual expense:
o The balance sheet line called “prepaid expense” became 0 instead of $6,000
o $6,000 of insurance expenses were charged against profits as an expense
•
The company continued to pay off its taxi loan. During the period July – December, it
paid off the loan in full and paid $168 in interest on the loan (1% per month on the
outstanding balance).
38
39
40
41
42
43
44
45
46
ABCD
Taxi loan
Principal
outstanding,
beg. Month
Principal
paid, end
month
Interest
31-Jul-03 4,800 800 48.00
31-Aug-03 4,000 800 40.00
30-Sep-03 3,200 800 32.00
31-Oct-03 2,400 800 24.00
30-Nov-03 1,600 800 16.00
31-Dec-03 800 800 8.00
1-Jan-04 0
Total 4,800 168
PFE, Chapter 7: Accounting principles page 27
• The company started to pay off its mortgage. The mortgage payments were $500 per
month, with ½% interest on the outstanding balance:
48
49
50
51
52
53
54
55
56
ABCD
Mortgage
Principal
outstanding,
beg. Month
Principal
paid, end
month
Interest
31-Jul-03 60,000 500 300.00
31-Aug-03 59,500 500 297.50
30-Sep-03 59,000 500 295.00
31-Oct-03 58,500 500 292.50
30-Nov-03 58,000 500 290.00
31-Dec-03 57,500 500 287.50
1-Jan-04 57,000
Total 3,000 1,762.50
•
At the end of the year, when the owners saw that the business was profitable, they
declared a $30,000 dividend. In order not to stress the business, they decided to pay out
$15,000 of this dividend immediately and the remainder at the end of March 2004. This
created:
o A charge of $15,000 against the cash accounts of the business
o An item called “dividends payable” in the current liabilities of the firm
Putting all these numbers together, here are the company’s financial statements for the
second half year:
PFE, Chapter 7: Accounting principles page 28
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
ABCDEFGH
Assets Liabilities and equity Profit and loss
Current assets Current liabilities
Cash Taxi loan from bank 0 Sales 300,000
Cash at beginning of period 88,728 Accounts payable (gasoline) 14,000 Driver salaries -18,000
Cash sales 290,000 Taxes payable Owner salaries -24,000
Driver salaries -18,000 For Jan - June Fuel -98,000
Owner salaries -24,000 For July - Dec 55,103 Interest -1,931
Receivable paid 10,000 Dividend payable 15,000 Insurance -6,000
Fuel -84,000 Total current liabilities 84,103 Depreciation
Interest Computer -500
Auto loan -168 Long-term liabilities Building -4,000
Mortgage -1,763 Mortgage to buy building 57,000 Taxis -7,000
Repayment of principal
Auto loan -4,800 Profit 140,570
Mortgage -3,000 State income tax (5%) -7,028
Taxes paid, 15 Jul 2003 -44,562 Federal income tax (36
%
-48,075
Payment of outstanding gas bill -14,000 Profit after taxes 85,466
Dividend paid -15,000 Dividends -30,000
Cash at end of period 179,436 Retained earnings 55,466
Accounts receivable 10,000
Prepaid expenses (insurance) 0
Current assets 189,436
Fixed assets Equity
Computer 2,000 Initial stock 25,000
Minus accumulated depreciation -750 Accumulated retained earnings
Taxis 36,000 Jan - June 69,116
Minus accumulated depreciation -12,000 July - December 55,466
Building 80,000 Total equity 149,582
Minus accumulated depreciation -4,000
Net fixed assets 101,250
Total assets 290,686 Total liabilities and equity 290,686
ANYTOWN TRAVEL SERVICES (ATS), INC., July - December 2003
financial statements including depreciation and taxes
7.7. Computing the free cash flow for the second half year
In rows 44-58 below we calculate the free cash flow for the company at the end of
December 2003:
PFE, Chapter 7: Accounting principles page 29
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
ABCDE
Profit and loss for period ending 1-Jan-03 30-Jun-03 31-Dec-03
Sales 214,000 300,000
Cost of sales -94,800 -146,000
Interest -272 -1,931
Depreciation -5,250 -11,500
Profit 113,678 140,570
State income tax (5%) -5,684 -7,028
Federal income tax (36%) -38,878 -48,075
Profit after taxes 69,116 85,466
Dividends 0 -30,000
Retained earnings 69,116 59,114
Balance sheet--Assets 1-Jan-03 30-Jun-03 31-Dec-03
Current assets
Cash 25,000 88,728 179,436
Accounts receivable 0 10,000 10,000
Prepaid expenses (insurance) 0 6,000 0
Total current assets 25,000 104,728 189,436
Fixed assets
At cost 0 118,000 118,000
Accumulated depreciation 0 -5,250 -16,750
Net fixed assets 0 112,750 101,250
Total assets 25,000 217,478 290,686
Balance sheet--Liabilities and equity 1-Jan-03 30-Jun-03 31-Dec-03
Current liabilities
Short-term loan from bank (taxi loan) 4,800 0
Taxes payable 0 44,562 55,103
Accounts payable (gasoline) 0 14,000 14,000
Dividend payable 15,000
Total current liabilities 0 63,362 84,103
Long-term liabilities
Mortgage to buy building 0 60,000 57,000
Equity
Stock 25,000 25,000 25,000
Retained earnings 0 69,116 124,582
Total equity 25,000 94,116 149,582
Total liabilities and equity 25,000 217,478 290,686
Free cash flow (FCF)
Profit after taxes 69,116 59,114
Add back depreciation 5,250 11,500 <-- =-D6
Change in net working capital
Subtract increase in operating current assets -16,000 6,000 <-- =-(SUM(D17:D18)-SUM(C17:C18))
Add increase in operating current liabilities 58,562 25,541 <-- =(D33-D29)-(C33-C29)
Change in net working capital 42,562 31,541 <-- =D50+D49
Change in fixed assets at cost -118,000 0
Add back after-tax net interest paid 165 1,174
Free cash flow -906 103,329 <-- =D45+D46+D51+D53+D55
Year total FCF 102,423 <-- =C56+D56
ANYTOWN TRAVEL SERVICES (ATS), INC.
Free Cash Flow for 2003
During the year, the company had a total FCF of $102,423. Not bad for a new company!
PFE, Chapter 7: Accounting principles page 30
Which depreciation do you add back in the cash flow?
When calculating the free cash flow (FCF) for ATS for the period July-December 2003,
the depreciation we add back in the free cash flow (cell D46) computation is the depreciation
which appears in the profit and loss statement for the period (cell D6), and not the accumulated
depreciation which appears on the balance sheet (cell D23) for the same period. The reason for
this is that D6 refers to the depreciation applied to the profits of the period, whereas D23 refers
to the accumulated depreciation for all of the fixed assets which appear on the balance sheet.
7.8. Using the FCF in a valuation exercise
Suppose we wanted to use the FCFs to value the Brother Sister Travel Services. In
financial theory, the value of a business is the present value of its free cash flows, discounted at
an appropriate risk-adjusted discount rate.
At the end of 2003, Brother and Sister perform such a valuation, in a quick-and-dirty
manner. They make the following assumptions:
•
The FCF of $103,329 which occurred in the second half of 2003 will recur in each half
year. This makes the annual anticipated FCF $206,658.
•
Except that every 3 years they will have to buy new taxis for $36,000 and some
additional equipment for another $4,000 (they’re a bit unspecific about this, but the
thought is that the computer will need replacing every few years and perhaps there’ll be
some other expenses).