It's a spreadsheet
Project 5 Workbook Instructions Instructions To complete this workbook, answer the questions on each worksheet in the space provided. Cost of Capital 1rRF =3% RPm =6.0% B = 0.01 rs=3.0600% 2D1 =$2.38 P0 =$135 G = 7% rs=8.7629629629629600% 3Estimate 1 =3% Estimate 2 =4% Estimate 3 =5% Current borrowing =4% 1rs=7% 2rs=8% 3rs=9% 4I choose 9% for McCormick & Company's Cost of Equity because the Cost of Equity represents the rate of return. Therefore, 9% would guarentee McCormick & Company's highest return rate. 5Value of Euqity =1.75E+10 Cost of Debt =4% Tax Rate =0.275 WACC=15.69% 6 Information from McCormick Your task is to make an estimate of McCormick & Company's Weighted Average Cost of Capital (WACC) to use as the discount rate for evaluating capital projects. Interest rates have risen and the CFO plans to borrow $350 million using the 20 year bond that you recommended in Project 4. For most of the past 10 years the company has used 7% as the discount rate. This was based on study of the company's WACC in early 2010. Interest rates have risen since the last study when the McCormick & Co rate average interest rate was 3%. We will use 4% today. Also, there has been a change in the weights between debt and equity. The most significant change was the acquisition of RB Foods. It is the main reason for this study. Here is what our MD&A reported about that acquisition. On August 17, 2017, we completed the acquisition of Reckitt Benckiser's Food Division ("RB Foods") from Reckitt Benckiser Group plc. The purchase price was approximately $4.2 billion, net of acquired cash. The acquisition was funded through our issuance of approximately 6.35 million shares of common stock non-voting (see note 13 of the financial statements) and through new borrowings comprised of senior unsecured notes and pre-payable term loans (see note 6 of the financial statements). The acquired market-leading brands of RB Foods include French’s®, Frank’s RedHot® and Cattlemen’s®, which are a natural strategic fit with our robust global branded flavor portfolio. We believe that these additions move us to a leading position in the attractive U.S. condiments category and provide significant international growth opportunities for our consumer and industrial segments. The RB Foods acquisition resulted in acquisitions contributing more than one-third of our sales growth in 2017 and is expected to result in acquisitions contributing more than one-third of our sales growth in 2018. As part of your work, you will be asked for a recommendation for the cost of equity. There are three widely accepted methods used to calculate the cost of equity. They are the Capital Asset Pricing Model (CAPM), the Discounted Cash Flow approach (DCF) and the debt rate plus a risk premium of 3% to 5% recommended by one board member. This last is often used by very wealthy investors as a check on the other two methods. 1. Even though the CFO expects that it ultimately will not be useful, you have been asked to calculate the cost of equity using the Capital Asset Pricing Model (CAPM). Yahoo Finance reports Beta as 0.01. Management wants to use the 30 year bond rate as the risk free rate, arguing that Investors should make long term investments. That rate is 3% today. The expected return on the stock market as a whole has been estimated to be 7%, 10% and 12% by various studies. The CFO asks that you use an expected return of 9% for the average stock. The market risk Premium (RPM) will be 6%. 9% minus 3% = 6%. Calculate the McCormick & Co cost of equity using the CAPM. The formula is rs = rRF + (RPM ) x β. rs is the required return on equity or the Cost of Equity, rRF is the risk free rate, RP M is the required stock market return in excess of the risk free rate, and β , (Beta) is the stocks relative risk. β is also described as the estimate of the amount of risk that an individual stock contributes to a well balance portfolio. 2. The Discounted Cash Flow model (referred to as the Direct Dividend Model in the MBA 620 course materials) is preferred by wealthy investors. The formula reduces to rs = (D1 / P0 ) + g where rs is the required return on equity or the Cost of Equity, D1 is the expected future dividend, P0 is the price of the stock today and g is the expected growth in dividends. The CFO notes that the expected future dividend is $2.38 and the expected growth rate is 7%. Please use $135 per share as the stock price. Calculate the cost of equity rs using the DCF approach. 3. Rick Malcolm is an advisor to a board member who works at a private equity firm. He has told the CFO that sophisticated investors use a quick estimate of the cost of equity. He says that the cost of equity must be above the company's debt rate. The estimate is that 3% to 5% should be added to the company's long term interest rates. McCormick & Company estimates its current borrowing cost at 4%. Make your own estimate of the relative risk of McCormick & Company. Then calculate the Cost of equity, rs using this own debt plus a 3% to 5% formula. 4. Pick your own best estimate of McCormick & Company cost of equity and tell why you made the choice. 5. Calculate the Weighted Average Cost of Capital (WACC) for McCormick and Company using the formula WACC = WD RD (1-T) + WS rS and WD = Value of debt / Value of debt plus value of equity; WS = Value of Stock Equity / Value of Debt Plus Value of Equity. For ease, the CFO says to use book value of Debt and the market Value of Equity. On February 26, 2019 the market Value of Equity (or Market Cap) in Yahoo was $17.5 billion. Use the 2018 10-K Financial Statements filed January 25, 2019 and look on the Balance sheet to see the total of Short term borrowings, Current portion of long term debt and Long term debt. Use 4% for the cost of debt. Use 27.5% as the tax rate - a combination of federal and state income tax. 6. Recognize that Finance Theory tells us to use the WACC for the discount rate for capital budgeting. The past discount rate was 7%. Do you recommend that McCormick change its discount rate. If so what rate do you recommend? If not, why not? Capital Budgeting Table 1 MACRS Depreciation$350 Year7 Year classDepreciation 114.29%$50.02 224.49%$85.72 317.49%$61.22 412.49%$43.72 58.93%$31.26 68.92%$31.22 78.93%$31.26 84.46%$15.61 Table 2 ABCDEF YearCash from Revenue in $MillionsCash outflow, expenses in $MillionsDepreciation in $MillionsTaxable Income in $ MillionsTax in $Millions 27.5% rateAfter tax Cash Flow In $Millions 1$1,800$1,728$50.02$21.99$6.05$65.95 2$1,900$1,824$85.72-$9.72-$2.67$78.67 3$2,000$1,920$61.22$18.79$5.17$74.83 4$2,100$2,016$43.72$40.29$11.08$72.92 5$2,200$2,112$31.26$56.75$15.60$72.40 6$2,300$2,208$31.22$60.78$16.71$75.29 7$2,400$2,304$31.26$64.75$17.80$78.20 8$2,500$2,400$15.61$84.39$23.21$76.79 9$2,600$2,496$0.00$104.00$28.60$75.40 10$2,700$2,592$0.00$108.00$29.70$78.30 11$2,600$2,496$0.00$104.00$28.60$75.40 12$2,500$2,400$0.00$100.00$27.50$72.50 13$2,400$2,304$0.00$96.00$26.40$69.60 14$2,200$2,112$0.00$88.00$24.20$63.80 15$2,000$1,920$0.00$80.00$22.00$58.00 16$1,800$1,728$0.00$72.00$19.80$52.20 17$1,500$1,440$0.00$60.00$16.50$43.50 18$1,200$1,152$0.00$48.00$13.20$34.80 19$800$768$0.00$32.00$8.80$23.20 20$400$384$0.00$16.00$4.40$11.60 Table 3 ABCDEF YearCash from Revenue in $MillionsCash outflow, expenses in $MillionsDepreciation in $MillionsTaxable Income in $ MillionsTax in $Millions 27.5% rate in years 1 , 2, 3 and 50% there afterAfter tax Cash Flow In $Millions 1$1,800$1,762.56$50.02-$12.57-$3.46$40.90 2$1,900$1,860.48$85.72-$46.20-$12.70$52.22 3$2,000 4$2,100$2,056.32$43.72-$0.04-$0.02$43.70 5$2,200 6$2,300 7$2,400 38$2,500 9$2,600$0.00 10$2,700$0.00 11$2,600$2,545.92$0.00$54.08$27.04$27.04 12$2,500$0.00 13$2,400$0.00 14$2,200$0.00 15$2,000$0.00 16$1,800$0.00 17$1,500$0.00 18$1,200$0.00 19$800$0.00 20$400$0.00 Details of McCormick Plant Proposal As you know from Project 4, McCormick & Company is considering a project that requires an initial investment of $350 million to build a new plant and purchase equipment. The investment will be depreciated as a modified accelerated cost recovery system (MACRS) seven-year class asset. The new plant will be built on some of the company's land, which has a current, after-tax market value of $14 million. You have been asked to refine your work to include the correct tax impact of depreciation, and the cash flow impact of working capital on the capital budget evaluation. The investment will be depreciated as a modified accelerated cost recovery system (MACRS) seven-year class asset. The correct depreciation table is included at the right. The company will need to finance some of the cash to fund $17 million in receivables and $14 million in Inventory starting at year zero. The company expects vendors to give free credit on purchases of $15 million (accounts Payable). Add the net cash outflow for working capital to the cash outflow for the plant, equipment and land in year zero. The $17 million for receivables and the $14 million for Inventory are cash outflows. The $15 million for receivables is a cash inflow. Assume that this net working capital is recovered as a cash inflow in year 21. The company still estimates revenues and expenses the same as it did in Project 4. See Table 2 at the right. The company now estimates that it can sell the land in year 21 for $40 million. It will also recover the cash spent on working capital in year 21. Use the WACC that you calculated in the Cost of Capital tab. Questions: 1. What will be the tax depreciation each year? Note: the total deprecation of tax purposes will still be $350 million if your calculations are correct. 2. Create an after-tax cash flow timeline similar to the one you did in Project 4. 3. Calculate the new NPV and IRR. Should the Project be accepted? The CFO thinks that the likely NPV and IRR will be close to the numbers that you calculated in Project 4. The following questions