assume all my current answers in yellow cells are incorrect. thanks guys

11 2 Excel Assignment 4 3. A beverage company that currently sells grape juice is considering adding apple juice to its product line. They 4 have determined they can sell the new product, apple juice, for $100 per bottle. Their manufacturing plant 5 has excess Capacity in a fully depreciated building to process the apple juice drink. The apple juice drink will 6 be discontinued after three year. The new equipment will be depreciated straight line to zero over three years 7. The new apple juce project will require an additional $40.000 of working capital. The projected sales of the 8 apple juice are 300.000 bottles the first year with a 10% sales growth rate for both the following years 9 Variable costs are 35% of sales and fixed costs are $30,000 each year. The new equipment costs $480,000 10 and will have a salvage value of $50,000 12 The corporate marginal tax rate is 30% and the company currently has 1,000,000 shares of stock outstanding 13 at a current price of $26 per share. The company also has 20.000 bonds outstanding with a current price of 14 $970. The bonds pay interest semi-annually and the coupon rate is 5%. The bonds have a par value of $1.000 15 and will mature in 16 years. 16 17 Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public 18 financial information available. However management believes that given the industry they are in the most 19 reasonable comparable publicly traded company is the PepsiCo Inc (ticker symbol PEP) 20 In addition, management believes the S&P 500 is a reasonable prosy for the market portfolio 21 Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the 22 S&P 500 annual expected rate of return (We calculated a monthly expected return for the market 23 in the return exercise. You can simply multiply that rate by 12 for the expected annual rate on the market 24 Use the most recent 3 month Treasury Bill rate for the Risk Free Rate, 25 The WACC is then calculated using this information and the other information provided above 26 Clearly show all your calculations and sources for all parameter estimates used in the WACO, 27 28 Required 29 In the Blank Template worksheet tab: 301 Fill in all the yellow shaded cells with the given information. 31 32 2 Insert formulas in the blue shaded cells. II. Calculations, referencing cells in Section 1. You can calculate the 33 cost of debt using the Rate formula, and the given bond information. (Be sure to multiply by 2 to get annual rate) 34 35 3. Enter formulas in the blue cells in Sections III, IV, V and I creating a partial income statement, incremental 36 Cash Flows including terminal cash flows and the calulation of NPV. 37 38 A. The EXCEL worksheet is now set up so that you are able to change the parameters in E4 to Ell By changing 39 the value in cell H4 run three possible cases, best 20% growth, most likely 10% growth and worst case 0% 40 growth rate and enter the NPV's in Section VIII 42/5. State whether the company should accept or reject the project for each case scenario. 44 6. Create a NPV profile for the most likely case scenario. In the NPV Calculation tab 41 43 45 ABRIL OD Beta 3 1. Given the following data on proposed capital budgeting project. IL Calculations Economic life of project in years Annual Growth Rate 10N MVE Price of New Equipment SA80,000 # Bonds Outstanding 20.000 MVD Salvage value of New Equipment 550.000 Shares Outstanding 1.000.000 Total MV Effect on NWC S40,000 Single Bond Mkt Value 597000 Weight of Equity First Year Revenues 5900,000 Single Stock Mkt Value $26.00 Weight of Debt Fixed Costs $90.000 RI Cost of Equity 10 Variable costs 35 ON Rm Cost of Debt (TM) 1 Marginal Tax Rate 30.0% WACC 2 14 Spreadsheet for determining Cash Flows 15 Timeline Year 0 3 16 II. Net Investment Outlay Initial CF 17 New Equipment Notes: Cells C17 and 18 include the initial cash flows today 18 Increase in NWC 19 IV. Cash Flows from Operations 20 Total Revenues Columns D, E & F are the operating cash flows. 21 Fixed Costs 22 Variable Costs 23 Depreciation 24 EBIT 25 Tases 26 Net Income 27 Depreciation 28 Net operating CFS 29 V. Terminal Cash Flows Cells F30, F31, and F32 include terminal cash flows 30 Salvare Value SP 31 loss Tax on Salvage Value (SP-BV) 32 Return of NWC 33 Cash Flows 34 Present Value of CFS 35 36 VI. Calculate: NPV 37 38 VIL NPV Sceneries 39 State whether the company should accept or reject the project for each case scenario, 40 best case NPV 20% growth Accept or Reject 41 most likely case NPV 10% growth Accept or Reject 42 worst case NPV 0% growth Accept or Reject 43 44 H Create a NPV by creating a line traph of rows 9 and 10 121 PVCD PVCD C D - Creating a NPV Profile De 04 CE MOD PY 4 1 5 7 Cells BH to 37 in this work the link to cells to P3) in the Bank Template worksheet Find the present value of add flows by referencing row 2 for the discount rate PWCF)-FRYN You can do columns the same way as you did CH4 to F34 in the Bank Template worksheet Rows & 10 meie vas used to create the NPV profile Draph. 04 916 NPY 10 Disco 11 13 13 NPV Profile 15 10 17 30 20 100 0.90 080 0.70 0.60 050 0.40 0.30 020 NPV 0 10 . Discount Rate 11 2 Excel Assignment 4 3. A beverage company that currently sells grape juice is considering adding apple juice to its product line. They 4 have determined they can sell the new product, apple juice, for $100 per bottle. Their manufacturing plant 5 has excess Capacity in a fully depreciated building to process the apple juice drink. The apple juice drink will 6 be discontinued after three year. The new equipment will be depreciated straight line to zero over three years 7. The new apple juce project will require an additional $40.000 of working capital. The projected sales of the 8 apple juice are 300.000 bottles the first year with a 10% sales growth rate for both the following years 9 Variable costs are 35% of sales and fixed costs are $30,000 each year. The new equipment costs $480,000 10 and will have a salvage value of $50,000 12 The corporate marginal tax rate is 30% and the company currently has 1,000,000 shares of stock outstanding 13 at a current price of $26 per share. The company also has 20.000 bonds outstanding with a current price of 14 $970. The bonds pay interest semi-annually and the coupon rate is 5%. The bonds have a par value of $1.000 15 and will mature in 16 years. 16 17 Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public 18 financial information available. However management believes that given the industry they are in the most 19 reasonable comparable publicly traded company is the PepsiCo Inc (ticker symbol PEP) 20 In addition, management believes the S&P 500 is a reasonable prosy for the market portfolio 21 Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the 22 S&P 500 annual expected rate of return (We calculated a monthly expected return for the market 23 in the return exercise. You can simply multiply that rate by 12 for the expected annual rate on the market 24 Use the most recent 3 month Treasury Bill rate for the Risk Free Rate, 25 The WACC is then calculated using this information and the other information provided above 26 Clearly show all your calculations and sources for all parameter estimates used in the WACO, 27 28 Required 29 In the Blank Template worksheet tab: 301 Fill in all the yellow shaded cells with the given information. 31 32 2 Insert formulas in the blue shaded cells. II. Calculations, referencing cells in Section 1. You can calculate the 33 cost of debt using the Rate formula, and the given bond information. (Be sure to multiply by 2 to get annual rate) 34 35 3. Enter formulas in the blue cells in Sections III, IV, V and I creating a partial income statement, incremental 36 Cash Flows including terminal cash flows and the calulation of NPV. 37 38 A. The EXCEL worksheet is now set up so that you are able to change the parameters in E4 to Ell By changing 39 the value in cell H4 run three possible cases, best 20% growth, most likely 10% growth and worst case 0% 40 growth rate and enter the NPV's in Section VIII 42/5. State whether the company should accept or reject the project for each case scenario. 44 6. Create a NPV profile for the most likely case scenario. In the NPV Calculation tab 41 43 45 ABRIL OD Beta 3 1. Given the following data on proposed capital budgeting project. IL Calculations Economic life of project in years Annual Growth Rate 10N MVE Price of New Equipment SA80,000 # Bonds Outstanding 20.000 MVD Salvage value of New Equipment 550.000 Shares Outstanding 1.000.000 Total MV Effect on NWC S40,000 Single Bond Mkt Value 597000 Weight of Equity First Year Revenues 5900,000 Single Stock Mkt Value $26.00 Weight of Debt Fixed Costs $90.000 RI Cost of Equity 10 Variable costs 35 ON Rm Cost of Debt (TM) 1 Marginal Tax Rate 30.0% WACC 2 14 Spreadsheet for determining Cash Flows 15 Timeline Year 0 3 16 II. Net Investment Outlay Initial CF 17 New Equipment Notes: Cells C17 and 18 include the initial cash flows today 18 Increase in NWC 19 IV. Cash Flows from Operations 20 Total Revenues Columns D, E & F are the operating cash flows. 21 Fixed Costs 22 Variable Costs 23 Depreciation 24 EBIT 25 Tases 26 Net Income 27 Depreciation 28 Net operating CFS 29 V. Terminal Cash Flows Cells F30, F31, and F32 include terminal cash flows 30 Salvare Value SP 31 loss Tax on Salvage Value (SP-BV) 32 Return of NWC 33 Cash Flows 34 Present Value of CFS 35 36 VI. Calculate: NPV 37 38 VIL NPV Sceneries 39 State whether the company should accept or reject the project for each case scenario, 40 best case NPV 20% growth Accept or Reject 41 most likely case NPV 10% growth Accept or Reject 42 worst case NPV 0% growth Accept or Reject 43 44 H Create a NPV by creating a line traph of rows 9 and 10 121 PVCD PVCD C D - Creating a NPV Profile De 04 CE MOD PY 4 1 5 7 Cells BH to 37 in this work the link to cells to P3) in the Bank Template worksheet Find the present value of add flows by referencing row 2 for the discount rate PWCF)-FRYN You can do columns the same way as you did CH4 to F34 in the Bank Template worksheet Rows & 10 meie vas used to create the NPV profile Draph. 04 916 NPY 10 Disco 11 13 13 NPV Profile 15 10 17 30 20 100 0.90 080 0.70 0.60 050 0.40 0.30 020 NPV 0 10 . Discount Rate