Blue Bottling, Inc. (BBI), is a bottling company and is considering expanding in

Question

Blue Bottling, Inc. (BBI), is a bottling company and is considering expanding into filling 16-ounce bottles. In order to do so, BBI must purchase a new bottling machine. The machine would not replace the machine used to fill 32-ounce bottles. The risk of this project is similar to the current risk of the company. BBI uses Net Present Value as its investment decision method. Therefore, BBI executives have decided that their weighted average cost of capital would be an appropriate discount rate to use when analyzing the project. About the Machine BBI managers estimate the cost of the machine to be $260,000. In order for the machine to be usable for BBI, it must be modified, costing $40,000. The machine will be depreciated using straight-line depreciation, assuming a 7-year life and $30,500 salvage value. BBI managers believe that the machine will be replaced at the end of 7 years at which time it will be sold for its salvage value. During the 7 years of its use, managers estimate annual earnings before interest, depreciation and taxes (EBITDA) to be $60,000. There will be an increase in net working capital of $5000 with the new press, due to increased accounts receivable and inventory costs. Weighted Average Cost of Capital Current investigation shows that the firm has a 40% marginal tax rate. (Round all numbers to one-hundredth of one percent). Debt The firm has 7000 of its $1000 par, 18-year, 5% semi-annual bonds that are currently outstanding and being quoted at 104:8. Common Stock BBI common stock is currently trading for $50 per share and there are 100,000 shares issued and outstanding. There are two methods of computing the cost of capital, and you should average the two values for your final cost of common stock. The firm will pay annual dividends of $3.00 per share in the coming year. The firm’s dividends and earnings have been growing at an annual rate of 3.5%, and this is expected to continue in the future. Lang’s beta is currently .8 and the current 90-day t-bill rate is 2.5%, while the historic market average is 10%. Requirements of the Assignment Using Excel, create a spreadsheet that calculates all relevant cash flows (i.e., initial investment, periodic cash flows, and terminal value). Note: The spreadsheet should have data at the top and the cash flow calculations completely in equations, or picking up numbers from a previous cell. The instructor should be able to change inputs. The spreadsheet automatically adjusts to those changes. Round all numbers to the nearest dollar. Compute the weighted-average cost of capital (WACC) for the chosen firm on your spreadsheet. Take this number out to the nearest hundredth of a percent (e.g. 33.33%). There is no preferred stock in the company. Determine the weight of debt and common equity by the current market value. Using Excel equations, compute the NPV and IRR. BBI management expressed concern over their estimate of EBITDA of $60,000, especially if the economy deteriorated suddenly. Because of this concern, you’ve been asked to complete a sensitivity analysis, computing the NPV if EBITDA were $54,000 instead of $60,000. (Note that if your spreadsheet is set up correctly, you should be able to change the $60,000 data input to $54,000 and all calculations will change automatically, including the calculation of a new NPV and IRR.) In fact, managers would like an estimate of the break-even level of EBITDA, if you could provide that information. BBI management has also expressed concern over the cost of the new machine. There has been increased demand for the larger bottles, so prices have risen recently on the machine. It is possible that the machine will cost 10% more (i.e., $286,000), with installation not changing. If this occurs, managers believe that salvage value will also rise by 10%. Managers want to be sure the project is still a good one to accept if the machine costs more than originally anticipated. Use EBITDA of $70,000

Explanation / Answer

Cost of the machine 260000 Modification Cost 40000 Life of machine 7 years Depreciation method straight line Depreciation amount 42857 Salvage Value 30500 Annual EBIDTA 60000 Increase in networking capital 5000 Tax Rate 40% Par Value of Bond 100 Coupon rate 5% semi-annual Semi-annual Coupon amount 2.5 Period to maturity 18 years or 36 semi-annual periods Current Price of the bond 104.8 Let r be the YTM, then Current Price = semi-annual coupon * (1-(1/(1+r)^36)/r)+Face Value /(1+r)^36 This can be re-arranged as Current Price - semi-annual coupon * (1-(1/(1+r)^36)/r)-Face Value /(1+r)^36 = 0 Let r = 4% or 0.02 semi-annual rate LHS will be   = -7.9444212 Let r = 5.00% or 0.025 semi-annual rate LHS will be = 4.8 YTM = r + (net value 1 *(int rate 1 -int rate 2))/(net value 2-net value 1) ytm = 0.0462336 or 4.62% post tax cost of debt   = ytm * (1-tax rate) Post Tax cost of debt 0.0277402 or 2.77% Annual Dividend next year 3 Growth rate of dividend and earnings 3.50% expected to continue in future Current share price 50 Stock required rate of return   = (Dividend next year /current price) + Dividend growth rate Stock required rate of return 9.50% Stock Beta 0.8 T-Bill rate (risk-free rate) 2.50% Historic Market average return 10% Expected return   = risk-free rate + beta * (market return - risk-free rate) Expected return 8.50% Average cost of equity 9.00% = (value derived by dividend approach + value derived by CAPM)/2 Total Number of Bonds outstanding 7000 Current Market Price 104.8 Market Value of Debt 733600 Total Number shares outstanding 100000 Current share price 50 Market Value of Equity 5000000 Market Value of the firm 5733600 = market value of equity + market value of debt Weight of Debt 0.13 = market value of debt / market value of firm Weight of Equity 0.87 = market value of equity / market value of firm Weighed average cost of capital (WACC) = weight of debt*after-tax cost of debt + weight of equity * average cost of equity WACC   = 0.082034 or 8.20% Calculation of operating cash flows EBITDA 60000 Depreciation 42857 EBIT 17143 Tax at 40% 6857 Net Income 10286 Annual Operating cash flow 53143 =net income + depreciation Calculation of Total Cash Flows Year 0 1 2 3 4 5 6 7 cost of machine -260000 Modification Cost -40000 Annual operating Cash flow 53143 53143 53143 53143 53143 53143 53143 Salvage Value 30500 Total Cash Flows -300000 53142.86 53142.86 53142.86 53142.86 53142.86 53142.86 83642.86 IRR 7.49% NPV -7,088 Sensitivity Analysis Calculation of operating cash flows EBITDA 54000 Depreciation 42857 EBIT 11143 Tax at 40% 4457 Net Income 6686 Annual Operating cash flow 49543 =net income + depreciation Calculation of Total Cash Flows Year 0 1 2 3 4 5 6 7 cost of machine -260000 Modification Cost -40000 Annual operating Cash flow 49543 49543 49543 49543 49543 49543 49543 Salvage Value 30500 Total Cash Flows -300000 49542.86 49542.86 49542.86 49542.86 49542.86 49542.86 80042.86 IRR 5.72% NPV -24,290 Break-even EBITDA Break-even EBITDA is where NPV is equal to zero Let X be the operating cash flow which makes NPV equals zero -260,000-40,00 + x * ((1-(1/(1+wacc)^7)/wacc) = 0 x * ((1-(1/(1+wacc)^7)/wacc) = 300,000 x = 300000 * 1/((1-(1/(1+wacc)^7)/wacc) The second portion of the above equation is the present value factor of annuity which can be calculated as follows Present Value Annuity Factor 5.1703478 Operating Cash Flow 58023 = (cost of machine+modification cost)/Present Value Annuity Factor Less : Depreciation 42857 Net Income 15166 Tax 10111 EBT 25277 Depreciatiion 42857 EBITDA 68134 Break-even EBITDA 68134 Calculation with change in cost of machine Increase in price 10% Cost of new machine 286000 Modification cost 40000 Changed Salvage Value 33550 Calculation of operating cash flows EBITDA 70000 Depreciation 49543 EBIT 20457 Tax at 40% 8183 Net Income 12274 Annual Operating cash flow 61817 =net income + depreciation Calculation of Total Cash Flows Year 0 1 2 3 4 5 6 7 cost of machine -286000 Modification Cost -40000 Annual operating Cash flow 61817 61817 61817 61817 61817 61817 61817 Salvage Value 33550 Total Cash Flows -326000 61817.14 61817.14 61817.14 61817.14 61817.14 61817.14 95367.14 IRR 9.30% NPV 11,955.34

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.