Get documents about "Stock Valuation Spreadsheet
Description
Stock Valuation Spreadsheet document sample
Document Sample
59642498-9ab9-4663-b6ca-45ce3113f72a.xls Corporate Valuation
7/11/2007
Chapter 15. Mini Case
This spreadsheet has two major components, one for Corporate Valuation and one for Value Based Management. Click on the
tabs in the lower left of the screen to switch between sections.
The value of a company is the sum of: (1) the value of its assets-in-place, including their associated growth opportunities, which
is called the value of operations and (2) the value of its non-operating assets, such as marketable securities and investments in
non-controlled affiliates. The value of operations is the present value of the free cash flows produced by the assets-in-place and
their associated growth opportunities.
Situation
You have been hired as a consultant to Kulpa Fishing Supplies (KFS), a company that is seeking to increase its value. KFS has
asked you to estimate the value of two privately held companies that KFS is considering acquiring. But first, the senior
management of KFS would like for you to explain how to value companies that don't pay any dividends. You have structured
your presentation around the following questions.
a. List the two types of assets that companies own. Answer: See Chapter 15 Mini Case Show
b. What are assets-in-place? How can their value be estimated? Answer: See Chapter 15 Mini Case Show
c. What are non-operating assets? How can their value be estimated? Answer: See Chapter 15 Mini Case Show
d. What is the total value of a corporation? Who has claims on this value? Answer: See Chapter 15 Mini Case Show
e. The first acquisition target is a privately held company in a mature industry. The company currently has free cash flow of
$20 million. Its WACC is 10% and it is expected to grow at a constant rate of 5%. The company has marketable securities of
$100 million. It is financed with $200 million of debt, $50 million of preferred stock, and $210 million of book equity.
(1) What is its total corporate value? What is its value of equity?
(2) What is its value of operations?
(3) What is its MVA (MVA = Total corporate value – Total book value)?
Claims on Corporate Value
Debt holders have the first claim on corporate value. Preferred stockholders have the next claim and the remaining is left to
common stockholders.
INPUT DATA SECTION: Data used for valuation (in millions)
Free Cash Flow $20.0
WACC 10% Vop = FCF1 FCF1 = FCF0 * (1 + g)
Growth 5% (WACC-g)
Marketable Securities $100.0
Debt $200.0
Preferred Stock $50.0
Book Value of Equity $210.0 $21.0
Vop =
0.05
Vop = $420.0
Sources of Corporate Value
Value of Operation $420.0
Total Corporate Value = $520.0
Value of Non-operating Assets $100.0
Claims on Corporate Value
Value of Debt $200.0
Value of Preferred Stock $50.0
Value of Equity $270.0 Value of Equity =Total Corporate Value - Value of
Debt - Value of Preferred
(3.) What is its MVA (MVA = Total corporate value – total book value)?
Market Value Added (MVA)
Michael C. Ehrhardt Page 1 8/22/2011
59642498-9ab9-4663-b6ca-45ce3113f72a.xls Corporate Valuation
Total value of the firm $520.00
Minus: Book value of debt $200.00
Minus: Book value of preferred stock$50.00
Minus: Book value of equity $210.00
MVA $60.00 Market
Non Op. Value Book Value
Vop Assets Debt Pref. Stock Equity Equity MVA
Market Value Sources $420.0 $100.0
Market Value Claims $200.0 $50.0 $270.0
Book Value Claims $200.0 $50.0 $210.0 $60.00
$600.0
$500.0
MVA
$400.0 Book Value Equity
Market Value Equity
$300.0 Pref. Stock
Debt
$200.0 Non Op. Assets
Vop
$100.0
$0.0
Market Value Market Value Book Value
Sources Claims Claims
f. The second acquisition target is a privately held company in a growing industry. The target has recently borrowed $40
million to finance its expansion; it has no other debt or preferred stock. It pays no dividends and currently has no marketable
securities. KFS expects the company to produce free cash flows of -$5 million in one year, $10 million in two years, and $20
million in three years. After three years, free cash flow will grow at a rate of 6%. Its WACC is 10% and it currently has 10
million shares of stock.
(1.) What is its horizon value (i.e., its value of operations at year three)? What is its current value of operations (i.e., at time
zero)?
(2.) What is its value of equity on a price per share basis?
Calculate the value of operations for the following company.
Actual Projected
1 2 3 4
Long-term growth rate 6%
Weighted Avg. Cost of Cap. (WACC) 10.00%
Free Cash Flow ($5.0) $10.0 $20.0 $21.2
Find the horizon value.
The horizon value is the value of all the free cash flows in year 4 and beyond, discounted back to the
end of year 3. The formula is:
HVyear 3 = [FCFyear 3 * (1+g)] / [ WACC - g].
Horizon Value $530.00
Find the PV of the horizon value and of the free cash flows.
PV of Horizon Value @ WACC $398.20
PV of free cash flows @ WACC $18.75
Michael C. Ehrhardt Page 2 8/22/2011
59642498-9ab9-4663-b6ca-45ce3113f72a.xls Corporate Valuation
Value of Operations $416.94
PROBLEM
Find the value of equity and the price per share.
Step 1: Find the total value of the firm.
Value of operations $416.94
Minus: Value of Debt $0.00
Total value of firm $416.94
Step 2: Find the value of equity.
Total value of firm $416.94
Minus: Value of debt $40.00
Minus: Value of preferred stock $0.00
Value of equity $376.94
Step 3: Find the price per share.
Value of equity $376.94
Divided by number of shares 10
Price per share $37.69
See the other worksheet tab (Value Based Management) for the rest of the Mini Case questions and answers.
Michael C. Ehrhardt Page 3 8/22/2011
VALUE-BASED MANAGEMENT: 7/11/2007
g. KFS is also interested in applying value-based management to its own divisions. Explain what value-based
management is. Answer: Chapter 15 Mini Case Show
h. What are the four value drivers? How does each of them affect value? Answer: Chapter 15 Mini Case Show
i. What is expected return on invested capital (EROIC)? Why is the spread between EROIC and WACC so important?
Answer: Chapter 15 Mini Case Show
j. KFS has two divisions. Both have current sales of $1,000, current expected growth of 5%, and a WACC of 10%.
Division A has high profitability (OP=6%) but high capital requirements (CR=78%). Division B has low profitability
(OP=4%) but low capital requirements (CR=27%). What is the MVA of each division, based on the current growth of
5%? What is the MVA of each division if growth is 6%?
Impact on MVA
Expected growth goes to 6%
Division A Division B
Sales $1,000 $1,000 MVA t =
Growth 5% 5%
é Salest (1 + g ) ù é æ CR ö ù
ê WACC - g ú êOP - WACC ç (1 + g ) ÷ ú
WACC 10% 10%
ç ÷
OP
CR
6%
78%
4%
27%
ë ûë è øû
Division A Division B
Operating Profit 6% 6% 4% 4%
Capital Requirements 78% 78% 27% 27%
Growth 5% 5% 5% 5%
MVA -300.0 -300.0 300.0 300.0
Market Value Added
400.0
300.0
200.0
100.0
0.0 MVA
Division
-100.0
6%
-200.0
5% 6%
-300.0 Division B
-400.0
k. What is the EROIC of each division for 5% growth and for 6% growth? How is this related to MVA?
Division A Division B
NOPATt +1
Capital 780.00 780.00 270.00 270.00 EROIC t =
Growth 5% 6% 5% 6% Capital t
Sales $1,050.0 $1,050.0 $1,050.0 $1,060.0
Capital t [EROIC t - WACC]
NOPAT $63.0 $63.6 $42.0 $42.4
EROIC 8.1% 8.2% 15.6% 15.7%
MVA t =
MVA -300.0 -360.0 300.0 385.0
WACC - g
l. List six potential managerial behaviors that can harm a firm’s value. Answer: Chapter 15 Mini Case Show
m. The managers at KFS have heard that corporate governance can affect shareholder value. What is corporate
governance? List five corporate governance provisions that are internal to a firm and are under its control. Answer:
Chapter 15 Mini Case Show
n. What characteristics of the board of directors usually lead to effective corporate governance? Answer: Chapter 15 Mini Case Show
o. List three provisions in the corporate charter that affect takeovers. Answer: Chapter 15 Mini Case Show
p. Briefly describe the use of stock options in a compensation plan. What are some potential problems with stock options
as a form of compensation? Answer: Chapter 15 Mini Case Show
q. What is block ownership? How does it affect corporate governance? Answer: Chapter 15 Mini Case Show
r. Briefly explain how regulatory agencies and legal systems affect corporate governance. Answer: Chapter 15 Mini Case
Show
Related docs
Other docs by mml13993
Revision Date 6 2010 Contractor High Performance Bridge Concrete Mix Design
Views: 46 | Downloads: 0
