SOLUTION TO SPREADSHEET PROBLEM
6-14 The detailed solution for the spreadsheet problem, Ch06 P14 Build a Model Solution.xls
is available at the textbook’s Web site.
Answers and Solutions: 6 – 13
MINI CASE
Jenny Cochran, a recent graduate of the University of Tennessee with four years of
banking experience, was recently brought in as assistant to the chairman of the board of
Computron Industries, a manufacturer of computer components.
The company doubled its plant capacity, opened new sales offices outside its home
territory, and launched an expensive advertising campaign. Computron’s results were not
satisfactory, to put it mildly. Its board of directors, which consisted of its president and
Balance Sheets
Assets
2014
2015
Cash
$ 9,000
$ 7,282
Short-term investments.
48,600
20,000
Liabilities and equity
2014
2015
Accounts payable
$ 145,600
$ 324,000
Notes payable
200,000
720,000
Accruals
136,000
284,960
total current liabilities
$ 481,600
$ 1,328,960
323,432
Common stock (100,000 shares)
460,000
460,000
203,768
total equity
$ 663,768
$ 557,632
$ 1,468,800
$ 2,886,592
Mini Case: 6 – 15
Accounts receivable
351,200
632,160
Inventories
715,200
total current assets
$ 1,124,000
$ 1,946,802
491,000
146,200
263,160
net fixed assets
$ 344,800
$ 939,790
$ 1,468,800
$ 2,886,592
Income Statements
2015
Sales
$ 5,834,400
Cost of goods sold
4,980,000
Other expenses
720,000
Other data
2015
Stock price
$ 6.00
Shares outstanding
100,000
DPS
$ 0.110
Mini Case: 6 – 16
Depreciation
116,960
total operating costs
$ 3,222,900
$ 5,816,960
EBIT
$ 209,100
$ 17,440
Interest expense
176,000
Pretax earnings
$ 146,600
Taxes (40%)
$ 87,960
Statement of Cash Flows
Operating activities
Net income
$ (95,136)
Adjustments:
noncash adjustments:
Investing activities
Cash used to acquire fixed assets
$ (711,950)
Cash due to change in short term investments
$ 28,600
Net cash provided by operating activities
$ (683,350)
Financing activities
change in notes payable
$ 520,000
change in long-term debt
$ 676,568
change in common stock
payment of cash dividends
$ (11,000)
$ 1,185,568
Summary
Net change in cash
$ (1,718)
Cash at beginning of year
Cash at end of year
$ 7,282
a. What effect did the expansion have on sales and net income? What effect did the
expansion have on the asset side of the balance sheet? What effect did it have on
liabilities and equity?
Answer: Sales increased by over by over $2.4 million, but net income fell by over $190,000.
Mini Case: 6 – 17
depreciation
changes in working capital:
change in accounts receivable
change in inventories
change in accounts payable
change in accruals
Net cash provided by operating activities
$ (503,936)
b. What do you conclude from the statement of cash flows?
Answer: Net CF from operations = –$503,936, because of negative net income and increases in
c. What is free cash flow? Why is it important? What are the five uses of FCF?
Answer: FCF is the amount of cash available from operations for distribution to all investors
(including stockholders and debtholders) after making the necessary investments to
support operations. A company’s value depends upon the amount of FCF it can
generate.
1. Pay interest on debt.
Mini Case: 6 – 18
d. What is Computron’s net operating profit after taxes (NOPAT)? What are
operating current assets? What are operating current liabilities? How much net
operating working capital and total net operating capital does Computron have?
Answer: NOPAT = EBIT(1 – TAX RATE)
Operating current assets are the CA needed to support operations. OP CA include:
cash, inventory, receivables. OP CA exclude: short-term investments, because these
are not a part of operations. Operating current liabilities are the CL resulting as a
normal part of operations. OP CL include: accounts payable and accruals. OP CA
exclude: notes payable, because this is a source of financing, not a part of operations.
Mini Case: 6 – 19
e. What is Computron’s free cash flow (FCF)? What are Computron’s “net uses”
of its FCF?
Answer: FCF = NOPAT – Net investment in capital
= $10,464 – ($2,257,632 – $1,138,600)
f. Calculate Computron’s return on invested capital. Computron has a 10% cost
of capital (WACC). Do you think Computron’s growth added value?
ANSWER: ROIC = NOPAT / TOTAL NET OPERATING CAPITAL.
Current year:
Mini Case: 6 – 20
g. Cochran also has asked you to estimate Computron’s EVA. She estimates that
the after-tax cost of capital was 10 percent in both years.
ANSWER: EVA = NOPAT– (WACC)(CAPITAL).
h. What happened to Computron’s market value added (MVA)?
Answer: MVA = market value of the firm – book value of the firm.
Mini Case: 6 – 21
i. Assume that a corporation has $100,000 of taxable income from operations plus
$5,000 of interest income and $10,000 of dividend income. What is the
company’s tax liability?
Answer: Calculation of the company’s tax liability:
Taxable operating income $100,000
Mini Case: 6 – 22
j. Assume that you are in the 25 percent marginal tax bracket and that you have
$5,000 to invest. You have narrowed your investment choices down to
California bonds with a yield of 7 percent or equally risky ExxonMobil bonds
with a yield of 10 percent. Which one should you choose and why? At what
marginal tax rate would you be indifferent to the choice between California and
ExxonMobil bonds?
Answer: After–tax return income at t = 25%:
Mini Case: 6 – 23