06 Aug 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% |
WACC Calculation
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.