Description of this paper

WACC, Forecasting Financial Statements, FCF, Cor...




WACC, Forecasting Financial Statements, FCF, Corporate Valuation Model It is 2010 and you are working in the finance department of BioTrend Industries. The CFO of the company knows that you just earned an MBA from Radford University, which is known for its rigorous education in Finance; hence, he asks you to determine the intrinsic value of the company alongside an assessment if the stock is currently undervalued or overvalued. Assume that the company is still in the non-growth stage, but that cash flows are expected to grow at a constant rate of 7% beginning in 2011. You start immediately with the collection of all relevant data, which are listed below: Financial statements for 2010 (see spreadsheet) The company's historical sales: Year Sales 2005 95,215 2006 143,901 2007 207,252 2008 298,065 2009 390,692 2010 455,150 2011 Assume interest rate on short-term securities: 3% Assume interest rate for short-term debt: 5% Assume interest rate on long-term debt: 8% No interest is earned on cash. Tax rate: 35% Dividends growth rate: 20% The industry average quick ratio: 2.0 The industry average debt ratio: 35% debt Target Capital structure: 40% debt and 60% common equity Bond Information: If the company issues new bonds it would do so by issuing bonds with 20 years to maturity. The company has right now semiannual coupon bonds with a 9% coupon payment outstanding. The bonds were issued 10 years ago with 30 years to maturity and sell currently for 1048. The flotation cost is assumed to be 3% of par value (remember par value is always $1,000). Stock Information: The risk free rate (Rf) is right now 3% and the return on the market 13%. The company?s beta is 0.9. The company has 5 million shares outstanding (5,000 in thousands). The current market value of the stock is $60. Use the provided spreadsheet to calculate the following (note, numbers are in thousands): A) The sales for 2011 (based on a regression equation) B) The pro-format statements for 2011 - The company needs to increase fixed assets to support sales - (Use the beginning of year debt balances to calculate net interest expense) C) The FCF for 2011 D) The WACC E) Intrinsic Value per share F) Market value added (MVA) a. Based on the actual market price b. Based on a. Assuming the historical trend continues, what will sales be in 2010? Base your forecast on a spreadsheet regression analysis of the 2004-2009 sales data above, and include the summary output of the regression in your answer. By what percentage are sales predicted to increase in 2010 over 2009? Is the sales growth rate increasing or decreasing? Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2010. Year Sales Growth Rate 2004 129,215,000 2005 180,901,000 40.0% 2006 235,252,000 30.0% 2007 294,065,000 25.0% 2008 396,692,000 34.9% 2009 455,150,000 14.7% 2010 515,465,267 % Increase in Predicted Sales for 2010 over 2009: 2009 Sales 455,150,000 2010 Sales 515,465,267 % increase 13.25% Note: This growth rate has been declining over time. b. Cumberland?s management believes that the firm will actually experience a 20 percent increase in sales during 2010. Construct 2010 pro forma financial statements. Cumberland will not issue any new stock or long-term bonds. Assume Cumberland will carry forward its current amounts of short-term investments and notes payable, prior to calculating AFN. Assume that any Additional Funds Needed (AFN) will be raised as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments). Use an interest rate of 9 percent for short-term debt (and for the interest income on short-term investments) and a rate of 11 percent for long-term debt. No interest is earned on cash. Use the beginning of year debt balances to calculate net interest expense. Assume that dividends grow at an 8 percent rate. Key Input Data: Used in the forecast Tax rate 40% Dividend growth rate 8% S-T rd 9% L-T rd 11% December 31 Income Statements: (in thousands of dollars) Forecasting 2009 2010 2010 2009 basis Ratios Inputs Forecast Sales $455,150 Growth Expenses (excluding depr. & amort.) $386,878 % of sales EBITDA $68,273 Depreciation and Amortization $7,388 % of fixed assets EBIT $60,885 Net Interest Expense $8,575 Interest rate x beginning of year debt EBT $52,310 Taxes (40%) $20,924 Net Income $31,386 Common dividends $12,554 Growth Addition to retained earnings (DRE) $18,832 Cumberland Industries December 31 Balance Sheets (in thousands of dollars) Forecasting 2009 2010 2010 2009 basis Ratios Inputs Without AFN AFN With AFN Assets: Cash and cash equivalents $91,450 % of sales = Short-term investments $11,400 Previous Accounts Receivable $103,365 % of sales Inventories $38,444 % of sales Total current assets $244,659 Fixed assets $67,165 % of sales Total assets $311,824 Liabilities and equity Accounts payable $30,761 % of sales Accruals $30,477 % of sales Notes payable $16,717 Previous Total current liabilities $77,955 Long-term debt $76,264 Previous Total liabilities $154,219 Common stock $100,000 Previous Retained Earnings $57,605 Previous + DRE Total common equity $157,605 Total liabilities and equity $311,824 Required assets = Specified sources of financing = Additional funds needed (AFN) = Required additional notes payable = Additional short-term investments = c. Now create a graph depicting the sensitivity of AFN for the coming year to the sales growth rate. To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%, 20%, 25%, and 30%. We can use a data table to answer this question: Sales 2010 AFN Growth rate $0 5% 10% 15% 20% 25% 30% d. Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2009 and 2010. Also, calculate the FCF for 2010. Net Operating Working Capital NOWC09 = Operating CA - Operating CL = - = NOWC10 = Operating CA - Operating CL = - = Total Operating Capital TOC09 = NOWC + Fixed assets = + = TOC10 = NOWC + Fixed assets = + = Net Operating Profit After Taxes NOPAT09 = EBIT x ( 1 - T ) = x = NOPAT10 = EBIT x ( 1 - T ) = x = Free Cash Flow FCF10 = NOPAT - Increase in TOC = - = e. Suppose Cumberland can reduce its inventory to sales ratio to 5 percent and its cost to sales ratio to 83 percent. What happens to AFN and FCF? Input Base Case New Scenario Inv. / Sales 0.0% 5.0% Note: we used the Scenario Manager. Costs / Sales 0.0% 83.0% FCF AFN,Hello, how is it going? I have this that I have added if it helps you complete it tonight. (although some of it might not be correct) Also, I understand if this is kinda rushing it for a big assignment. I will give additional money if it is completed tonight. Thank You,Hello? Are you there? Have not received an update for some time.,Thank you Rachel!,Hey,I have completed a large portion of the homework, if you could look just look over it and make sure it is correct. Also, I am not sure if I sent you my copy that I worked on so I will try again.,Did you receive the part I had worked on? Will you be able to complete this assignment tonight?,I don't think this is correct


Paper#2613 | Written in 18-Jul-2015

Price : $25