please help

Final Case Project 2 3 Suppose a beverage company is considering adding a new product line. 4 Currently the company sells apple juice and they are considering selling a fruit drink. 5 The fruit drink will have a selling price of $1.00 per jar. The plant has excess capacity in 6 fully depreciated building to process the fruit drink. The fruit drink will be discontinued in four yea 7 The new equipment is depreciated to zero using straight line depreciation. The new fruit drink requires 8 an increase in working capital of $75,000 and S5,000 of this increase is offset with accounts payable. 9 Projected sales are 150,000 jars of fruit drink the first year, with a 10 percent growth for the following years. 10 Variable costs are 55% of total revenues and fixed costs are $20,000 each year. The new equipment costs 11 S159,000 and has a salvage value of $25,000 12 13The corporate tax rate is 35 percent and the company currently has 1,000,000 shares of stock outstanding 14 at a current price of $5. The company also has 5,000 bonds outstanding, with a current price of $985. The 15 bonds pay interest semi-annually at the coupon rate is 6%. The bonds have a par value of $1,000 and will 16 mature in thirty years. 17 18 Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly 19 available financial information. However, management believes that given the industry they 20 are in the most reasonable comparable publicly traded company is Cott Corporation (ticker symble 21 is COT). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio 22 Therefore, the cost of equity is calculated using the beta from COT and the market risk premium based on the 23 S&P 500 annual expected rate of return. (We calculated a monthly expected return for the market 24 in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the 25 market.) The WACC is then calculated using this information and the other information provided 26 above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. 27 28 Required 291. Calculate the WACC for the company. 30 2. Create a partial income statement incremental cash flows from this project in the 31 Blank Template worksheet using the tab below. 32 3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. 33(You can either use the EXCEL formula PVO or use mathmatical formula for PV of a lump sum.) 34 4. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12. 35 Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, 36 respectfully 37 5. Create a NPV profile for the most likely case scenario. (See NPV Calculation tab below.) 38 6. State whether the company should accept or reject the project for each case scenario 39 7. Turn in your project in the drop box. 40 41 PLEASE MAKE SURE YOU CHECK THE RUBRIC SO YOU KNOW WHAT YOU WILL BE EVALUATED ON! 42 E3 Final Case Project 2 I. Given the following data on proposed capital budgeting proje Note Cells C17 and C18 include the initial cash flows today Economic life of project in years. Price of New Equipment Fixed Costs e operating cash flows. Collumn D through G are th Cells D30, D31, and D32 include terminal cash flows. $10,000 $5,000 S5,000 $5,000 S5.000 50.0% 50.0% 50.0% 2.0% Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Marginal Tax Rate Growth Rate WACC PLEASE NOTE: You may need to change inputs provided in Collumn E at left. 10 12 1 2 14 Spreadsheet for determining Cash Flows (in Thousands) 15 Timeline: Year 16 II. Net Investment Outlay Initial CFs 17 18 Price Increase in NWC 10,000 19 III. Cash Flows from Operations 20 21 Total Revenues Variable Costs Fixed Costs Depreciation 5,000 23 24 25 26 27 28 29 IV. Terminal Cash Flows 30 31 32 Earnings Before Taxes Taxes Net Income ciation Net operating CFs Salvage Value Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs 34 35 36 Calculate: 37 38 39 40 41 42 NPV 114 H IK L MN OP QR S T U Create a NPV by creating a line graph of rows 9 and 1 1Creating a NPV Profile 2 Discount Rate: 0% You may want to use different discount rates in your NPV profi Cells B4 to B8 in this worksheet can link to cells C32 to G32 in the Blank Template works Find the present value of cash flows by referencing row 2 for the discour 8% 4% 8% iear 4 5 You can do column C the same way as you did C33 to G33 in the Blank Template workshe 4 8 9 NPV 10 Discount Rate: Rows 9 & 10 are the table that are used to crate the NPV profil 4% 12 13 14 15 16 17 Final Case Project 2 3 Suppose a beverage company is considering adding a new product line. 4 Currently the company sells apple juice and they are considering selling a fruit drink. 5 The fruit drink will have a selling price of $1.00 per jar. The plant has excess capacity in 6 fully depreciated building to process the fruit drink. The fruit drink will be discontinued in four yea 7 The new equipment is depreciated to zero using straight line depreciation. The new fruit drink requires 8 an increase in working capital of $75,000 and S5,000 of this increase is offset with accounts payable. 9 Projected sales are 150,000 jars of fruit drink the first year, with a 10 percent growth for the following years. 10 Variable costs are 55% of total revenues and fixed costs are $20,000 each year. The new equipment costs 11 S159,000 and has a salvage value of $25,000 12 13The corporate tax rate is 35 percent and the company currently has 1,000,000 shares of stock outstanding 14 at a current price of $5. The company also has 5,000 bonds outstanding, with a current price of $985. The 15 bonds pay interest semi-annually at the coupon rate is 6%. The bonds have a par value of $1,000 and will 16 mature in thirty years. 17 18 Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly 19 available financial information. However, management believes that given the industry they 20 are in the most reasonable comparable publicly traded company is Cott Corporation (ticker symble 21 is COT). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio 22 Therefore, the cost of equity is calculated using the beta from COT and the market risk premium based on the 23 S&P 500 annual expected rate of return. (We calculated a monthly expected return for the market 24 in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the 25 market.) The WACC is then calculated using this information and the other information provided 26 above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. 27 28 Required 291. Calculate the WACC for the company. 30 2. Create a partial income statement incremental cash flows from this project in the 31 Blank Template worksheet using the tab below. 32 3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. 33(You can either use the EXCEL formula PVO or use mathmatical formula for PV of a lump sum.) 34 4. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12. 35 Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, 36 respectfully 37 5. Create a NPV profile for the most likely case scenario. (See NPV Calculation tab below.) 38 6. State whether the company should accept or reject the project for each case scenario 39 7. Turn in your project in the drop box. 40 41 PLEASE MAKE SURE YOU CHECK THE RUBRIC SO YOU KNOW WHAT YOU WILL BE EVALUATED ON! 42 E3 Final Case Project 2 I. Given the following data on proposed capital budgeting proje Note Cells C17 and C18 include the initial cash flows today Economic life of project in years. Price of New Equipment Fixed Costs e operating cash flows. Collumn D through G are th Cells D30, D31, and D32 include terminal cash flows. $10,000 $5,000 S5,000 $5,000 S5.000 50.0% 50.0% 50.0% 2.0% Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Marginal Tax Rate Growth Rate WACC PLEASE NOTE: You may need to change inputs provided in Collumn E at left. 10 12 1 2 14 Spreadsheet for determining Cash Flows (in Thousands) 15 Timeline: Year 16 II. Net Investment Outlay Initial CFs 17 18 Price Increase in NWC 10,000 19 III. Cash Flows from Operations 20 21 Total Revenues Variable Costs Fixed Costs Depreciation 5,000 23 24 25 26 27 28 29 IV. Terminal Cash Flows 30 31 32 Earnings Before Taxes Taxes Net Income ciation Net operating CFs Salvage Value Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs 34 35 36 Calculate: 37 38 39 40 41 42 NPV 114 H IK L MN OP QR S T U Create a NPV by creating a line graph of rows 9 and 1 1Creating a NPV Profile 2 Discount Rate: 0% You may want to use different discount rates in your NPV profi Cells B4 to B8 in this worksheet can link to cells C32 to G32 in the Blank Template works Find the present value of cash flows by referencing row 2 for the discour 8% 4% 8% iear 4 5 You can do column C the same way as you did C33 to G33 in the Blank Template workshe 4 8 9 NPV 10 Discount Rate: Rows 9 & 10 are the table that are used to crate the NPV profil 4% 12 13 14 15 16 17