| Final Case Project |
| Suppose that the Cott Corporation (Symbol COT) is considering adding a new product line. Currently, Cott |
| sells apple juice and they are considering selling a new fruit drink. The fruit drink will have a selling |
| price of $1.64 per container. The plant has excess capacity in a fully depreciated building to |
| process the fruit drink product line. The new fruit drink will be discontinued in four years. The |
| new equipment falls under the 3-year MACRS depreciation schedule. The new fruit drink requires |
| an increase in inventory of $275,000, an increase in accounts receivable of $135,000, and an increase |
| in accounts payable of $110,000. Projected sales are 4,000,000 containers in the first |
| year, with a 9% growth rate for each subsequent year. Variable costs are 60% of total revenues |
| and fixed costs are $1,250,000 each year. The new equipment costs $2,500,000 and has a salvage |
| value of $500,000. The corporate tax rate for Cott is 35%. |
| Cott currently has 1,100,000 bonds outstanding with a 5.38% coupon rate (paid semi-annually) |
| and 8 years left until maturity. They are selling at 98% of par. |
| You will gather current market data (price, beta, & shares outstanding) on Cott's common stock from Yahoo! Finance. |
| *(Use the closing price from Friday, April 24) |
| Remember from project #3, beta and shares outstanding are under 'Key Statistics' in Yahoo! Finance. |
| The monthly expected return on the S&P 500 (SPY) that we calculated from project #3 was 1.19%. |
| Multiplying this by 12 will give you a reliable estimate of the expected return on the market. |
| The risk-free rate is 1.2%. |
| Required |
| *Unless the value is given above (or pulled straight from Yahoo! Finance), all cells highlighted in yellow require a calculation done using formulas. |
| *Do not forget the correct sign convention |
| *Double-check your calculations using your calculator |
| 1. Calculate the WACC for the company using the 'WACC' tab below. |
| (I have done the cost of debt calculations to get you started) |
| 2. Create pro-forma income statements for the incremental cash flows from this project using the |
| 'Pro Forma' tab below. |
| Enter formulas to calculate the PV of each year's cash flows in row 36. |
| (You can either use the EXCEL formula PV() or use mathmatical formula for PV of a lump sum.) |
| The NPV is simply the sum of the PVs of all years' cash flows |
| 3. Create a NPV profile using the 'NPV Profile' tab below. |
| 4. E-mail your completed spreadsheet to me at [email protected] by Sunday, May 3 at 11:59p. |
| The subject line of your email will read 'class section_last name_Excel Project #4' |
| For example: '002_Coy_Excel Project #4' |
Feras_bk
final_excel_project_wacc_fruit_drink.xls
