Excel Assignment 4 A beverage company that currently sells apple juice is considering adding grape juice to its product line. They have determined they can sell the new product, grape juice, for $1.55 per bottle. Their manufacturing plant has excess capacity in a fully depreciated building to process the grape juice drink. The grape juice drink will be discontinued after three years. The new equipment will be depreciated straight line to zero over its six year life. The new grape juice project will require an additional $100,000 of working capital. The projected sales of the grape juice are 275,000 bottles the first year, with a 15% sales growth rate for both the following years. Variable costs are 30% of sales and fixed costs are $120,000 each year. The new equipment costs $600,000 and will have a salvage value of $60,000. The corporate marginal tax rate is 35% and the company currently has 1,000,000 shares of stock outstanding at a current price of $16 per share. The company also has 25,000 bonds outstanding, with a current price of $980. The bonds pay interest semi-annually and the coupon rate is 5%. The bonds have a par value of $1,000 and will mature in 17 years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public financial information available. However management believes that given the industry they are in, the most reasonable comparable publicly traded company is PepsiCo, Inc (ticker symbol PEP). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the S&P 500 annual expected rate of return. (We calculated a monthly expected return for the market in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market.) Use the most recent 3 month Treasury Bill rate for the Risk Free Rate. The WACC is then calculated using this information and the other information provided above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. Required In the Blank Template worksheet tab: 1. Fill in all the yellow shaded cells with the given information. 2. Insert formulas in the blue shaded cells, II. Calculations, referencing cells in Section 1. You can calculate the cost of debt using the Rate formula, and the given bond information. 3. Enter formulas in the blue cells in Sections III, IV, V and VI creating a partial income statement, incremental cash flows including terminal cash flows and the calulation of NPV. 4.The EXCEL worksheet is now set up so that you are able to change the parameters in Section 1. By changing the value in cell 18 (annual growth rate) run three possible cases, best 30 % growth, most likely 15 % growth, and worst case 0% growth rate and enter the NPV's in Section VII. 5. State whether the company should accept or reject the project for each case scenario. 6. Create a NPV profile for the most likely case scenario. (In the NPV Calculation tab) L. Given the following data on proposed capital budgeting project. First Year Revenues Fixed Costs Variable Costs% Marginal Tax Rate Price of New Equipment Economic life of New Equipment in years Salvage value of New Equipment Effect on NWC: Spreadsheet for determining Cash Flows Timeline: Year 0 III. Net Investment Outlay-Initial CFS New Equipment Increase in NWC IV. Cash Flows from Operations Total Revenues Fixed Costs Variable Costs Depreciation EBIT Taxes Net Income Depreciation Net operating CFs V. Terminal Cash Flows Salvage Value less Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs VI. Calculate: NPV VII. NPV Scenerios best case NPV 30% growth S most likely case NPV 15% growth S worst case NPV 0% growth S P # Shares Outstanding #Bonds Outstanding Single Stock Mkt Value Single Bond Mkt Value Annual Growth Rate Betal RF 3 II. Calculations Cost of equity Cost of debt (YTM) MVE MVD Total MV Weight of equity Weight of debt WACC RM Notes: Cells E17 and E18 include the initial cash flows today. Collumns F, G and H are the operating cash flows. Cells H30, H31, and H32 include terminal cash flows. Accept or Reject Accept or Reject Accept or Reject same should sorento relect the neelert for each case scenarin SP (SP-BV)*T] Creating a NPV Profile Discount Rate 0% Year CE PVICE 0 1 1 3 NIV Discent Rate NPV 3% PVICE) 1.00 0.90 0.80 0.70 0.60 0.50 0.40 0.30 0.20 0.10 10% PV(CD 15% PVICE) 10% NPV Profile 15% Discount Rate 20% PVICE) NPV Calculations 20% #551 Page 3 Create a NPV by creating a line graph of rows 10 and 11. You may want to use different discount rates in your NPV profile Cells CS to C8 in this worksheet can link to cells E33 to H33 in the Blank Template worksheet Find the present value of cash flows by referencing row 3 for the discount rate You can do column C the same way as you did E33 to H33 in the Blank Template worksheet. Rows 10 & 11 are the table that are used to create the NPV profile graph