Finance Spreadsheet Problems (13-11) -The Henley Corporation and Axel Telecommunications
Question # 00028417
Posted By:
Updated on: 10/16/2014 01:03 AM Due on: 10/16/2014
SPREADSHEET PROBLEM
(13-11)
Build a Model:
Corporate Valuation
Start with the partial model in the file Ch13 P11 Build a Model.xls on the textbook’s Web site. The Henley Corporation is a privately held company specializing in lawn care products and services. The most recent financial statements are shown below. Income Statement for the Year Ending December 31 (Millions of Dollars Except for Per Share Data)
2010
Net sales $ 800.0
Costs (except depreciation) 576.0
Depreciation 60.0
Total operating costs $ 636.0
Earnings before interest and taxes $ 164.0
Less interest 32.0
Earnings before taxes $ 132.0
Taxes (40%) 52.8
Net income before preferred dividends $ 79.2
Preferred dividends 1.4
Net income available for common dividends $ 77.9
Common dividends $ 31.1
Addition to retained earnings $ 46.7
Number of shares (in millions) 10
Dividends per share $ 3.11
Balance Sheet for December 31 (Mi llions of Dol lars)
2010 2010
Assets Liabilities and Equity
Cash $ 8.0 Accounts payable $ 16.0
Marketable securities 20.0 Notes payable 40.0
Accounts receivable 80.0 Accruals 40.0
Inventories 160.0 Total current liabilities $ 96.0
Total current assets $268.0 Long-term bonds 300.0
Net plant and equipment 600.0 Preferred stock 15.0
Common stock (par plus PIC) 257.0
Retained earnings 200.0
Common equity $457.0
Total assets $868.0 Total liabilities and equity $868.0
Projected ratios and selected information for the current and projected years are shown below.
Actual Projected
2010 2011 2012 2013 2014
Sales growth rate 15% 10% 6% 6%
Costs/Sales 72% 72 72 72 72
Depreciation/Net PPE 10 10 10 10 10
Cash/Sales 1 1 1 1 1
resource
Actual Projected
2010 2011 2012 2013 2014
Accounts receivable/Sales 10% 10% 10% 10% 10%
Inventories/Sales 20 20 20 20 20
Net PPE/Sales 75 75 75 75 75
Accounts payable/Sales 2 2 2 2 2
Accruals/Sales 5 5 5 5 5
Tax rate 40 40 40 40 40
Weighted average cost of
capital (WACC) 10.5 10.5 10.5 10.5 10.5
a. Forecast the parts of the income statement and balance sheet that are necessary for calculating free cash flow.
b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow each year to ensure that there is constant growth (that is, the same as the constant growth rate in sales) by the end of the forecast period.
c. Calculate operating profitability (OP = NOPAT/Sales), capital requirements (CR = Operating capital/Sales), and expected return on invested capital (EROIC = Expected NOPAT/Operating capital at beginning of year). Based on the spread between EROIC and WACC, do you think that the company will have a positive Market Value Added (MVA = Market value of company ? Book value of company = Value of operations ? Operating capital)?
d. Calculate the value of operations and MVA. (Hint: First calculate the horizon value at the end of the forecast period, which is equal to the value of operations at the end of the forecast period.) Assume that the annual growth rate beyond the horizon is 6%.
(13-11)
Build a Model:
Corporate Valuation
Start with the partial model in the file Ch13 P11 Build a Model.xls on the textbook’s Web site. The Henley Corporation is a privately held company specializing in lawn care products and services. The most recent financial statements are shown below. Income Statement for the Year Ending December 31 (Millions of Dollars Except for Per Share Data)
2010
Net sales $ 800.0
Costs (except depreciation) 576.0
Depreciation 60.0
Total operating costs $ 636.0
Earnings before interest and taxes $ 164.0
Less interest 32.0
Earnings before taxes $ 132.0
Taxes (40%) 52.8
Net income before preferred dividends $ 79.2
Preferred dividends 1.4
Net income available for common dividends $ 77.9
Common dividends $ 31.1
Addition to retained earnings $ 46.7
Number of shares (in millions) 10
Dividends per share $ 3.11
Balance Sheet for December 31 (Mi llions of Dol lars)
2010 2010
Assets Liabilities and Equity
Cash $ 8.0 Accounts payable $ 16.0
Marketable securities 20.0 Notes payable 40.0
Accounts receivable 80.0 Accruals 40.0
Inventories 160.0 Total current liabilities $ 96.0
Total current assets $268.0 Long-term bonds 300.0
Net plant and equipment 600.0 Preferred stock 15.0
Common stock (par plus PIC) 257.0
Retained earnings 200.0
Common equity $457.0
Total assets $868.0 Total liabilities and equity $868.0
Projected ratios and selected information for the current and projected years are shown below.
Actual Projected
2010 2011 2012 2013 2014
Sales growth rate 15% 10% 6% 6%
Costs/Sales 72% 72 72 72 72
Depreciation/Net PPE 10 10 10 10 10
Cash/Sales 1 1 1 1 1
resource
Actual Projected
2010 2011 2012 2013 2014
Accounts receivable/Sales 10% 10% 10% 10% 10%
Inventories/Sales 20 20 20 20 20
Net PPE/Sales 75 75 75 75 75
Accounts payable/Sales 2 2 2 2 2
Accruals/Sales 5 5 5 5 5
Tax rate 40 40 40 40 40
Weighted average cost of
capital (WACC) 10.5 10.5 10.5 10.5 10.5
a. Forecast the parts of the income statement and balance sheet that are necessary for calculating free cash flow.
b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow each year to ensure that there is constant growth (that is, the same as the constant growth rate in sales) by the end of the forecast period.
c. Calculate operating profitability (OP = NOPAT/Sales), capital requirements (CR = Operating capital/Sales), and expected return on invested capital (EROIC = Expected NOPAT/Operating capital at beginning of year). Based on the spread between EROIC and WACC, do you think that the company will have a positive Market Value Added (MVA = Market value of company ? Book value of company = Value of operations ? Operating capital)?
d. Calculate the value of operations and MVA. (Hint: First calculate the horizon value at the end of the forecast period, which is equal to the value of operations at the end of the forecast period.) Assume that the annual growth rate beyond the horizon is 6%.
e. Calculate the price per share of common equity as of 12/31/2010.
PROBLEMS 1–5
(14–1)
Residual Distribution Model
Axel Telecommunications has a target capital structure that consists of 70% debt and 30% equity. The company anticipates that its capital budget for the upcoming year will be $3 million. If Axel reports net income of $2 million and follows a residual distribution model with all distributions as dividends, what will be its dividend payout ratio?
(14–2)
Residual Distribution Policy
Petersen Company has a capital budget of $1.2 million. The company wants to maintain a target capital structure which is 60% debt and 40% equity. The company forecasts that its net income this year will be $600,000. If the company follows a residual distribution model and pays all distributions as dividends, what will be its payout ratio?
(14–3)
Dividend Payout
The Wei Corporation expects next year’s net income to be $15 million. The firm’s debt ratio is currently 40%. Wei has $12 million of profitable investment opportunities, and it wishes to maintain its existing debt ratio. According to the residual distribution model (assuming all payments are in the form of dividends), how large should Wei’s dividend payout ratio be next year?
(14–4)
Stock Repurchase
A firm has 10 million shares outstanding with a market price of $20 per share. The firm has $25 million in extra cash (short-term investments) that it plans to use in a stock repurchase; the firm has no other financial investments or any debt. What is the firm’s value of operations, and how many shares will remain after the repurchase?
(14–5)
Stock Split
Gamma Medical’s stock trades at $90 a share. The company is contemplating a 3-for-2 stock split. Assuming the stock split will have no effect on the total market value of its equity, what will be the company’s stock price following the stock split?
PROBLEMS 1–5
(14–1)
Residual Distribution Model
Axel Telecommunications has a target capital structure that consists of 70% debt and 30% equity. The company anticipates that its capital budget for the upcoming year will be $3 million. If Axel reports net income of $2 million and follows a residual distribution model with all distributions as dividends, what will be its dividend payout ratio?
(14–2)
Residual Distribution Policy
Petersen Company has a capital budget of $1.2 million. The company wants to maintain a target capital structure which is 60% debt and 40% equity. The company forecasts that its net income this year will be $600,000. If the company follows a residual distribution model and pays all distributions as dividends, what will be its payout ratio?
(14–3)
Dividend Payout
The Wei Corporation expects next year’s net income to be $15 million. The firm’s debt ratio is currently 40%. Wei has $12 million of profitable investment opportunities, and it wishes to maintain its existing debt ratio. According to the residual distribution model (assuming all payments are in the form of dividends), how large should Wei’s dividend payout ratio be next year?
(14–4)
Stock Repurchase
A firm has 10 million shares outstanding with a market price of $20 per share. The firm has $25 million in extra cash (short-term investments) that it plans to use in a stock repurchase; the firm has no other financial investments or any debt. What is the firm’s value of operations, and how many shares will remain after the repurchase?
(14–5)
Stock Split
Gamma Medical’s stock trades at $90 a share. The company is contemplating a 3-for-2 stock split. Assuming the stock split will have no effect on the total market value of its equity, what will be the company’s stock price following the stock split?
-
Rating:
/5
Solution: Finance Spreadsheet Problems (13-11) -The Henley Corporation and Axel Telecommunications