Instructions
| Final Case Project |
| Suppose a beverage company is considering adding a new product line. |
| Currently the company sells apple juice and they are considering selling a fruit drink. |
| The fruit drink will have a selling price of $2.00 per jar. The plant has excess capacity in a |
| fully depreciated building to process the fruit drink. The fruit drink will be discontinued in four years. |
| The new equipment is depreciated to zero using straight line depreciation. The new fruit drink requires |
| an increase in working capital of $25,000 and $5,000 of this increase is offset with accounts payable. |
| Projected sales are 200,000 jars of fruit drink the first year, with a 15 percent growth for the following years. |
| Variable costs are 55% of total revenues and fixed costs are $10,000 each year. The new equipment costs |
| $195,000 and has a salvage value of $25,000. |
| The corporate tax rate is 40 percent and the company currently has 1,000,000 shares of stock outstanding |
| at a current price of $15. The company also has 50,000 bonds outstanding, with a current price of $985. The |
| bonds pay interest semi-annually at the coupon rate is 5%. The bonds have a par value of $1,000 and will |
| mature in ten 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 publicly traded company is Cott Corporation (ticker symble |
| is COT). 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 COT 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.) 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 |
| 1. Calculate the WACC for the company. |
| 2. Create a partial income statement incremental cash flows from this project in the |
| Blank Template worksheet using the tab below. |
| 3. 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.) |
| 4. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12. |
| Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, |
| respectfully. |
| 5. Create a NPV profile for the most likely case scenario. (See NPV Calculation tab below.) |
| 6. State whether the company should accept or reject the project for each case scenario. |
| 7. Turn in your project in the drop box. |
| ***Show all work IN EXCEL (formulas in cells). It will not only speed up your project, it will also allow me to know you have done the work. |
Blank Template
| Final Case Project | ||||||
| I. Given the following data on proposed capital budgeting project. | Note Cells C17 and C18 include the initial cash flows today. | |||||
| Economic life of project in years. | 4 | Collumn D through G are the operating cash flows. | ||||
| Price of New Equipment | $195,000 | Cells D30, D31, and D32 include terminal cash flows. | ||||
| Fixed Costs | $10,000 | |||||
| Salvage value of New Equipment | $25,000 | |||||
| Effect on NWC: | $20,000 | |||||
| First Year Revenues | $400,000 | |||||
| Variable Costs | 55.0% | |||||
| Marginal Tax Rate | 40.0% | |||||
| Growth Rate | 15.0% | |||||
| WACC | ??? | |||||
| Spreadsheet for determining Cash Flows (in Thousands) | ||||||
| Timeline: | Year | 0 | 1 | 2 | 3 | 4 |
| II. Net Investment Outlay = Initial CFs | ||||||
| Price | (195,000) | |||||
| Increase in NWC | ||||||
| III. Cash Flows from Operations | ||||||
| Total Revenues | 400,000 | |||||
| Variable Costs | (220,000) | |||||
| Fixed Costs | ||||||
| Depreciation | ||||||
| Earnings Before Taxes | ||||||
| Taxes | ||||||
| Net Income | ||||||
| Depreciation | ||||||
| Net operating CFs | ||||||
| IV. Terminal Cash Flows | ||||||
| Salvage Value | ||||||
| Tax on Salvage Value | ||||||
| Return of NWC | ||||||
| Cash Flows | ||||||
| Present Value of CFs | ||||||
| Calculate: | NPV |
NPV Calculations
| Creating a NPV Profile | Create a NPV by creating a line graph of rows 9 and 10. | ||||||||
| Discount Rate: | 0% | 2% | 4% | 6% | 8% | You may want to use different discount rates in your NPV profile | |||
| Year | CF | PV(CF) | PV(CF) | PV(CF) | PV(CF) | PV(CF) | |||
| 0 | Cells B4 to B8 in this worksheet can link to cells C33 to G33 in the Blank Template worksheet. | ||||||||
| 1 | |||||||||
| 2 | Find the present value of cash flows by referencing row 2 for the discount rate. | ||||||||
| 3 | You can do column C the same way as you did C33 to G33 in the Blank Template worksheet. | ||||||||
| 4 | |||||||||
| NPV | Rows 9 & 10 are the table that are used to crate the NPV profile graph. | ||||||||
| Discount Rate: | 0% | 2% | 4% | 6% | 8% |
&A
Page &P
WACC Calculation
