= 1 Identifying Relevant Cash Flows 2 3 Suppose a beverage company is considering adding a new product line. 4 Currently the company sells apple juice and they are considering selling a fruit drink. 5 The fruit drink will have a selling price of $1.00 per jar. The plant has excess capacity in a 6 7 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 8 an increase in working capital of $25,000 and $5,000 of this increase is offset with accounts payable. 9 Projected sales are 150,000 jars of fruit drink the first year, with a 20 percent growth for the following years. 10 Variable costs are 55% of total revenues and fixed costs are $10,000 each year. The new equipment costs 11 $195,000 and has a salvage value of $25,000. 12 Bond Information 13 The corporate tax rate is 35 percent and the company currently has 1,000,000 shares of stock outstanding 14 at a current price of $15. The company also has 50,000 bonds outstanding, with a current price of $985. The 15 bonds pay interest semi-annually at the coupon rate is 6%. The bonds have a par value of $1,000 and will 16 mature in twenty years. 17 Equity Information 18 Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly 19 available financial information. However, management believes that given the industry they 20 are in the most reasonable comparable publicly traded company is Monster Beverage Corp (ticker symble 21 is MNST). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. 22 Therefore, the cost of equity is calculated using the beta from MNST and the market risk premium based on the 23 S&P 500 annual expected rate of return. (The best estimate for the expected return on the market is to look at 24 long run historical averages of the stock market. I provided you the historical long run average in Module 5 25 under page Historical Asset Averages. Next go to US Treasury Yield website to obtain current 3 month T-bill rate.) 26 WACC is then calculated using the CAPM and beta estimate as discussed for COT since it is in the same industry. 27 Clearly show all your calculations and sources for all parameter estimates used in the WACC. 28 A 29 Required 30 1. Calculate the WACC for the company. 49 50 51 52 53 54 55 D E F 31 2. Create a partial income statement incremental cash flows from this project in the 32 Blank Template worksheet using the tab below. 33 3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. 34 (You can either use the EXCEL formula PV() or use mathmatical formula for PV of a lump sum.) 35 4. Set up the EXCEL worksheet so that you are able to change the parameters in E3 to E12. 36 Run three cases best, most likely, and worst case where the growth rate is 30%, 20%, and 5%, respectfully. 37 38 5. Create a NPV profile for the most likely case scenario. (See NPV Calculation tab below.) 39 6. State whether the company should accept or reject the project for each case scenario. 40 7. Summarize your recommendation on a one-page pdf or doc file with the following: 41 a. NPV for each case 42 43 44 45 46 47 48 G H the b. NPV profile graph for most likely case c. Very brief (two or three sentence at most) recommendation of accepting or rejecting project. d. Your brief recommendation should include a note stating which parameter estimates you are most uncertain of. D Final Case Project 21. Given the following data on proposed capital budgeting project. 3 Economic life of project in years. Price of New Equipment Fixed Costs 4 5 6 7 8 9 10 11 12 CHREIBSON 388588385889 TU 14 Spreadsheet for determining Cash Flows (in Thousands) 15 Timeline: Year 16 11. Net Investment Outlay-Initial CFS Price 18 Increase in NWC 19 III. Cash Flows from Operations Total Revenues 20 Variable Costs Fixed Costs 17 21 22 23 24 25 26 27 28 30 31 32 Salvage value of New Equipment Effect on NWC: First Year Revenues 34 Variable Costs Marginal Tax Rate Growth Rate WACC 29 IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Retum of NWC Cash Flows Present Value of CFs 37 40 Depreciation Earings Before Taxes Taxes Net Income Depreciation Net operating CFs 36 Calculate: NPV Instructions Ready Calculate Blank Template 0 0 10,000 1 0 0 10,000 NPV Calculations E $0 $10,000 SO $0 4 $150,000 0.0% 0.0% 2 0.0% 0.0% 10,000 Sheet9 F 3 10,000 G H Note Cells C17 and C18 include the initial cash flows today. Collumn D through Gare the operating cash flows. Cells D30, D31, and D32 include terminal cash flows. Sheet10 4 KAINA 10,000 Sheet11 K Sheet12 Sheet13 Sheet14 L Sheet15 Creating a NPV Profile Discount Rate: Year 0 1 2 3 4 P NPV Discount Rate: C 256 85 CE PV(CE) PV(CE) Instructions D 3% 25 Blank Template F 4% 4% 6% 8% PV(CF) PV(CF) PV(CE) 6% 0 NPV Calculatinas 8% Shanes H 1 J 0 Create a NPV by creating a line graph of rows 9 and 10 You may want to use different discount rates in your NPV profile Cells B4 to 88 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 crate the NPV profile graph R S structure.com/courses/2189500/pages/historical-asset-performance?module_item_id=35562668 Historical Asset Performance Describe historical returns for common stock, corporate bonds, and treasury securities Researchers collect historical data to examine how different financial assets behave over time. The most widely cited set of financial data used by industry practitioners and academia is Stocks, Blonds, Bills, and Inflation Yearbook TM, Morningstar, Inc., Chicago (with annual updates by Roger G. Ibbotson and Rex Sinquefield). The data collected includes monthly and annual returns for large company stocks, small company stocks, corporate bonds, long-term government bonds, government treasury bills, and inflation Table 5.1 Annual average returns and standard deviations Table 5.1 reports the annual average returns and standard deviations for the five financial assets in the Morningstar data set. The average returns and standard deviations are calculated using annual data from 1926 to 2012. Large Stocks Small Stocks Corporate Bonds Long Term T-bonds T-bills Average Returns 11.70% 16.53% 6.41% 5.89% 3.68% Standard Deviation 20.17% 32.32% 8.32% d 8.26% Me Salet perc pof 3.15% Based on the historical results of the Table, the highest average returns and standard deviations are associated with small company stocks followed by large company stocks. Long- term corporate bonds have the next highest average returns and standard deviations, followed closely by long term U.S. treasury bonds. US, treasury bills have the smallest average return and standard deviation
