Description of this paper

Finance Spreadsheet Problems (13-11) -The Henley Corporation and Axel Telecommunications




Question;SPREADSHEET PROBLEM(13-11)Build a Model:Corporate ValuationStart 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)2010Net sales $ 800.0Costs (except depreciation) 576.0Depreciation 60.0Total operating costs $ 636.0Earnings before interest and taxes $ 164.0Less interest 32.0Earnings before taxes $ 132.0Taxes (40%) 52.8Net income before preferred dividends $ 79.2Preferred dividends 1.4Net income available for common dividends $ 77.9Common dividends $ 31.1Addition to retained earnings $ 46.7Number of shares (in millions) 10Dividends per share $ 3.11Balance Sheet for December 31 (Mi llions of Dol lars)2010 2010Assets Liabilities and EquityCash $ 8.0 Accounts payable $ 16.0Marketable securities 20.0 Notes payable 40.0Accounts receivable 80.0 Accruals 40.0Inventories 160.0 Total current liabilities $ 96.0Total current assets $268.0 Long-term bonds 300.0Net plant and equipment 600.0 Preferred stock 15.0Common stock (par plus PIC) 257.0Retained earnings 200.0Common equity $457.0Total assets $868.0 Total liabilities and equity $868.0Projected ratios and selected information for the current and projected years are shown below.Actual Projected2010 2011 2012 2013 2014Sales growth rate 15% 10% 6% 6%Costs/Sales 72% 72 72 72 72Depreciation/Net PPE 10 10 10 10 10Cash/Sales 1 1 1 1 1resource Actual Projected2010 2011 2012 2013 2014Accounts receivable/Sales 10% 10% 10% 10% 10%Inventories/Sales 20 20 20 20 20Net PPE/Sales 75 75 75 75 75Accounts payable/Sales 2 2 2 2 2Accruals/Sales 5 5 5 5 5Tax rate 40 40 40 40 40Weighted average cost ofcapital (WACC) 10.5 10.5 10.5 10.5 10.5a. 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 ModelAxel 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 PolicyPetersen 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 PayoutThe 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 RepurchaseA 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 SplitGamma 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?


Paper#48574 | Written in 18-Jul-2015

Price : $27