A B C D E F G H | J K Coffee Shop Case Project Suppose a beverage company is considering adding a new product line. Currently the company sells packaged drinks and they are considering selling a new coffee drink. The coffee drink will have a selling price of $4.89 per can. The plant has excess capacity in a fully depreciated building to process the coffee drink. The coffee drink will be discontinued in four years. The new equipment is depreciated to zero using straight line depreciation. The new coffee drink requires an increase in working capital of $30,000 and $5,000 of this increase is offset with accounts payable. Projected sales are 80,000 cans of coffee drink the first year, with a 7 percent growth for the following years. Variable costs are 46% of total revenues and fixed costs are $129,000 each year. The new equipment costs $385,000 and has a salvage value of $20,000. The corporate tax rate is 21 percent and the company currently has 100,000 shares of stock outstanding at a current price of $11.50. The company also has 1200 bonds outstanding, with a current price of $995. The bonds pay interest annually at the coupon rate is 3%. The bonds have a par value of $1,000 and will mature in twenty years. Even though the company has stock outstanding, it is not publicly traded. Therefore, there is no publicly available financial information. However, management believes that given the industry they are in, the most reasonable comparable Beta would be approximately 1.25. The current risk free rate in the market has been volatile with interest rate changes, but it now sits at 3.86% Management also believes the S&P 500 is a reasonable proxy, so the expected market return is 9.62%. Therefore, the cost of equity is calculated using CAPM, and the market risk premium based on the S&P 500 annual expected rate of return, along with the risk free rate and the given beta. The WACC is then calculated using this information and the other information provided above. Clearly show all your calculations for these calculations on the Blank Template tab. Required 1. Calculate the WACC for the company. 2. Calculate the rate of debt for the company. 3. Calculate the cost of equity for the company. 4. Using the information from the problem (above), create a partial income statement incremental cash flows from this project in the Blank Template worksheet using the tab below. 5. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. (You can either use the EXCEL formula PV() or use mathmatical formula for PV of a lump sum.) 6. Be sure to set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12. Also, link as much as you can in the partial income statement so that the numbers will automatically change. THIS WILL HELP YOU LATER! 7. Create a NPV profile for the most likely case scenario. (See NPV Calculation tab below.) 8. State whether the company should accept or reject the project for each case scenario. 9. You are now ready to "take the quiz" and fill in your answers for this assignment! A B C D E F G H Note Cells C17 and C18 include the initial cash flows today. Column D through G are the operating cash flows. Cells D30, D31, and D32 include terminal cash flows. PLEASE NOTE: You may need to change inputs provided in Column E at left. WACC Total Equity Total Debt Total Value $ Re 0 2 3 4 Rd 1 2 3. Final Case Project I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Marginal Tax Rate Growth Rate WACC Beta 4 Spreadsheet for determining Cash Flows (in Thousands) Timeline: 6 II. Net Investment Outlay = Initial CFs 1 2 1 , 3. Price Increase in NWC III. Cash Flows from Operations Total Revenues Variable Costs Fixed Costs Depreciation Earnings Before Taxes Taxes Net Income Depreciation Net operating CFs 9 IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC 2 B Cash Flows 1 Calculate: Present Value of CFs Year 3 9 0 1 2 B 5. 5 7 3. 9 1 2 3. 1 , NPV NPV Decision: Accept? Reject? Tax Rate WACC: Cost of Debt N Periods I/Y PMT PV FV Cost of Equity Risk Free Rate Market Risk Premium Beta CAPM: J K L $ % #DIV/O! #DIV/0! 2 B 1 5 7 B 9 1 2 B 1 5 7 A B D |Creating a NPV Profile (Most Likely Scenario) Discount Rate: 0% 2% Year CF 0 $ 1 $ 2 $ 3 $ - NPV 4 $ O Discount Rate: - PV(CF) PV(CF) E F G H 4% PV(CF) 6% PV(CF) 8% PV(CF) 0% 2% 4% 6% 8% - J K L M N Create a NPV by creating a line graph of rows 9 and 10. P Q R S Cells B4 to B8 in this worksheet can link to cells C32 to G32 in the Blank Template worksheet. Find the present value of cash flows by referencing row 2 for the discount rate. You can do column C the same way as you did C33 to G33 in the Blank Template worksheet. Rows 9 & 10 are the table that are used to create the NPV profile graph.