below are the Completed tables from project 4

Please help with questions 1 -6 and tables 1- 3. Thanks

below are the completed tables from project 4

As you know from Project 4, (no you don't know from the new 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. (note, a $350 million investment for a company with $10 billion in assets is significant but not huge. The $4 million investment in Project 4 is trivial and likely not requiring any high level approvals) 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. (We jump right in to the most difficult tax depreciation. Your accountants can help in the real world) 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. illion in /2 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 (Project 4 eliminated the relatively easy cash flow time line so this will be your first) 3. 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. ( this will be your first calculation of NPV and IRR. These two concepts are VERY important in Finance. You must use the NPV and IRR functions in Excel for this question.) The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow and estimates that the tax rate The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow 4. The controller is worried about tax increases and estimates that the tax rate with be raised to 50% (federal and Maryland state) in year 4. Also there is a concern that expenses are understated. He asks, "What would happen to the NPV calculation if the cash tax expenses come in 2% higher than estimated and the tax rate increases to 50% in year 4?" This will allow a subjective evaluation of the project risk. Calculate a new cash flow time line with cash expenses 10% higher than those in Table 2 and with a 50% tax rate. 5. What would be the net present value in this "worst case" cash flow? What will be the IRR? (Use the NPV and IRR function) 6. Should the project be accepted? Discuss the risk and the reward to McCormick. (this is to see if you can handle decision making under uncertainty. We don't know what will happen in future. Will taxes be raised or not? Will expenses go up or not? Companies must deal with these questions. If you just look at the worst case, not much will happen. We would have no Apple, Microsoft, Tesla, Google, Facebook, or Amazon if these innovators had just abandoned their ideas because someone came up with a worst case scenario. e 9296 11/ U Table 1 MACRS Depreciation $350 Depreciation 7 Year class 14.29 % Year 1 $50.02 24.49 % $85.72 3 17.49% $43.72 4 12.49% 8.93 % 0 6 8.92% 7 8.93 % 4.46% Table 2 4 C Cash outflow, A D E F Cash from Revenue in $Millions $1,800 $1,900 $2,000 Depreciation in Taxable Income Tax in SMillions 27.5 % rate expenses in SMillions After tax Cash Flow In in $ Millions SMillions $Millions 5 Year $65.95 $21.99 $6.05 S1,728 $1,824 $50.02 6 $78.67 -$2.67 -$9.72 $85.72 21 7 $1,920 $2,016 $2,112 8 3 $2,100 4 $2,200 0 $2,208 $2,300 1 $2,304 $2.400 Cost of Capital $2,400 $2.500 Instructions 2 7 3 Capital Budgeting e OType here to search P R U $2,600 $2,700 $2,600 $2,500 $2,400 $2,200 $2,000 $1,800 $1,500 $1,200 W $2,496 $2,592 $2,496 $2,400 $2,304 4 10 11 $104.00 $28.60 $75.40 7 12 8 13 $2,112 14 $1,920 $1,728 $1,440 0 15 16 1 2 17 3 $1,152 18 $800 $768 4 19 5 $400 $384 20 Table 3 86 87 C F B E A Tax in SMillions 27.5% rate in Cash outflow Cash from Revenue in Taxable Income years 1,2,3 and After tax Cash Flow In 50% there after Depreciation in expenses in SMillions $1,762.56 $1,860.48 $Millions in $ Millions $12.57 SMillions 38 Year SMillions $40.90 $52.22 $3.46 $1,800 $50.02 39 -$12.70 $46.20 $85.72 $1,900 2 40 31 $2,000 $2,100 41 $0.04 $43.70 $0.02 $43.72 $2,056.32 42 4 $2,200 $2,300 S2.400 Instructions 43 5 44 6 45 Capital Budgeting Cost of Capital e OP Type here to search St A D G K N Now that McCormick & Company has secured the land for the new factory through a loan, it is time to construct the new factory. Instead of using operating cash flow to fund the construction of the new factory, McCormick & Company has decided to raise capital. To raise additional capital, the company is considering issuing additional shares of stock. For McCormick & Company to determine how much it will cost the company to issue stock, the company mnust determine the expected return on the stock in relation to the systematic risk. We can help McCormick & Company with this by answering the following questions P Q R U using the provided information below: McCormick & Company uses the 10-Year Treasury Constant Maturity Rate as the risk free rate. As of 7/1/2019, this was 2.03 according to the US Treasury. McCormick & Company has disclosed the company's levered Beta is 0.60 (MarketWatch, 7/1/2019). McCormick & Company has disclosed the company's expected return on the market is 8.03 % R. R + Bx (R 1 R) To answer the following questions, use the template to the right. risk free rate 2.03 Expected return- 5.63 1. What is McCormick & Company's expected return on the issuance of stock using CAPM? 0.6 Beta expected market return risk free rate 2 R 8.03 In the CAPM, we examined the expected return on the market as a whole. In an effort to estimate the expected return of McCormick & Company's stock, we will use the Dividend Discount Model (DDM). We can help McCormick & Company with by answering the following questions using the provided information below: 2.03 this P McCormick & Company's expected dividend per share next year is $2.28 McCormick & Company's expected dividend per share constant growth rate is 8.70 % ( as of May 2019) McCormick & Company's stock price per share was $155.70 on 7/1/2019 2. Using the Dividend Discount Model (DDM), what is the cost of equity? To find the cost of equity using DDM, we take the original equation Div + 9 expected dividend current stock price Div 2.28 P - R-g cost of equity- 155.7 0.10164 and rearrange the equation: constant growth rate- or 10.16 % R= P 0.087 Annuities Financing and Investing Corporate Valuation Instructions 4 e 95% 1 Type here to search PV PMT FV 10 12% $4,424,000 ($13,740,272.47) 2 IN PV PMT FV 12% $ 4,424,000.00 10 ($13,740,272.47) Principal Amount Financed 30% $ 3,096,800.00 Percent Down 4 Loan IN 6 % | S 3,096,800.00 4.5% S3,096,800.00 5% S 3,096,800.00 PV PMT Loan A (S269,993.14) ($391,369.94) ($298,352.80) 20 Loan B 10 Loan C 15. Loan Loan A Total Paid $5,400,000.00 $3,913,699.35 PV PMT 3,096,800 $269,993.14 20 6% 4.5 % $391,369.94 $298,352.80 Loan B $3,096,800 $3,096,800 10 $4,475,291.94 5% Loan C 15 6 Loan B Annuities Instructions Corporate Valuation Financing and Investing e Type here to search As you know from Project 4, (no you don't know from the new 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. ( note, a $350 million investment for a company with $10 billion in assets is significant but not huge. The $4 million investment in Project 4 is trivial and likely not requiring any high level approvals) 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. (We jump right in to the most difficult tax depreciation. Your accountants can help in the real world) 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. will need to finance some of the cash to fund $17 million in The company 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. (Q-6 of the Cost of Capital tab in Project 5) 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 (Project 4 eliminated the relatively easy cash flow time line so this will be your first) 3. 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. (this will be your first calculation of NPV and IRR. These two concepts are VERY important in Finance. You must use the NPV and IRR functions in Excel for this question.) The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow 4. The controller is worried about tax increases and estimates that the tax rate with be raised to 50% (federal and Maryland state) in year 4. Also there is a concern that expenses are understated. He asks, "What would happen to the NPV calculation if the cash tax expenses come in 2% higher than estimated and the tax rate increases to 50% in year 4?" This will allow a subjective evaluation of the project risk. Calculate a new cash flow time line with cash expenses 10% higher than those in Table 2 and with a 50% tax rate. 4P e 100% 5. What would be the net present value in this "worst case" cash flow? What will be the IRR? (Use the NPV and IRR function) 6. Should the project be accepted? Discuss the risk and the reward to McCormick. (this is to see if you can handle decision making under uncertainty. We don't know what will happen in future. Will taxes be raised or not? Will expenses go up or not? Companies must deal with these questions. If you just look at the worst case, not much will happen. We would have no Apple, Microsoft, Tesla, Google, Facebook, or Amazon if these innovators had just abandoned their ideas because someone came up with a worst case scenario. e e Type here to search 100% Dele Ansert Prisc F12 F10 F1 F9 FB F7 FB FS R U I/Y PV 1 N PMT FV ($13,740,272.47) $4,424,000 10 12% I/Y PV PMT FV 12 % $4,424,000.00 ($13,740,272.47) 10 Amount Financed 30% $3,096,800.00 Principal Percent Down 3 $4,424,000.00 PMT I/Y PV Loan 4 ($269,993.14) (S391,369.94) ($298,352.80) 6% $ 3,096,800.00 4.5% 3,096,800.00 5% $ 3,096,800.00 20 Loan A Loan B 10 15 Loan C Total Paid PMT PV /Y Loan $3,096,800 $3,096,800 $3,096,800 $5,400,000.00 $3,913,699.35 $4,475,291.94 $269,993.14 $391,369.94 $298,352.80 6 % 20 Loan A 4.5% 10 Loan B 5% 15 Loan C 6 Loan B Annuities Corporate Valuation Financing and Investing Instructions 100% Type here to search O Z 10 11 12 13 risk free rate 5.63 2.03 Expected return = 14 0.6 Beta 15 expected market return risk free rate 16 8.03 2.03 17 Div 2 Rs 18 P 19 20 21 22 23 24 expected dividend= current stock price 2.28 25 0.101644 cost of equity= 155.7 26 or 10.16% 0.087 constant growth rate= 27 28 Annuities Corporate Valuation Instructions Financing and Investing Type here to search As you know from Project 4, (no you don't know from the new 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. (note, a $350 million investment for a company with $10 billion in assets is significant but not huge. The $4 million investment in Project 4 is trivial and likely not requiring any high level approvals) 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. (We jump right in to the most difficult tax depreciation. Your accountants can help in the real world) 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. illion in /2 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 (Project 4 eliminated the relatively easy cash flow time line so this will be your first) 3. 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. ( this will be your first calculation of NPV and IRR. These two concepts are VERY important in Finance. You must use the NPV and IRR functions in Excel for this question.) The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow and estimates that the tax rate The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow 4. The controller is worried about tax increases and estimates that the tax rate with be raised to 50% (federal and Maryland state) in year 4. Also there is a concern that expenses are understated. He asks, "What would happen to the NPV calculation if the cash tax expenses come in 2% higher than estimated and the tax rate increases to 50% in year 4?" This will allow a subjective evaluation of the project risk. Calculate a new cash flow time line with cash expenses 10% higher than those in Table 2 and with a 50% tax rate. 5. What would be the net present value in this "worst case" cash flow? What will be the IRR? (Use the NPV and IRR function) 6. Should the project be accepted? Discuss the risk and the reward to McCormick. (this is to see if you can handle decision making under uncertainty. We don't know what will happen in future. Will taxes be raised or not? Will expenses go up or not? Companies must deal with these questions. If you just look at the worst case, not much will happen. We would have no Apple, Microsoft, Tesla, Google, Facebook, or Amazon if these innovators had just abandoned their ideas because someone came up with a worst case scenario. e 9296 11/ U Table 1 MACRS Depreciation $350 Depreciation 7 Year class 14.29 % Year 1 $50.02 24.49 % $85.72 3 17.49% $43.72 4 12.49% 8.93 % 0 6 8.92% 7 8.93 % 4.46% Table 2 4 C Cash outflow, A D E F Cash from Revenue in $Millions $1,800 $1,900 $2,000 Depreciation in Taxable Income Tax in SMillions 27.5 % rate expenses in SMillions After tax Cash Flow In in $ Millions SMillions $Millions 5 Year $65.95 $21.99 $6.05 S1,728 $1,824 $50.02 6 $78.67 -$2.67 -$9.72 $85.72 21 7 $1,920 $2,016 $2,112 8 3 $2,100 4 $2,200 0 $2,208 $2,300 1 $2,304 $2.400 Cost of Capital $2,400 $2.500 Instructions 2 7 3 Capital Budgeting e OType here to search P R U $2,600 $2,700 $2,600 $2,500 $2,400 $2,200 $2,000 $1,800 $1,500 $1,200 W $2,496 $2,592 $2,496 $2,400 $2,304 4 10 11 $104.00 $28.60 $75.40 7 12 8 13 $2,112 14 $1,920 $1,728 $1,440 0 15 16 1 2 17 3 $1,152 18 $800 $768 4 19 5 $400 $384 20 Table 3 86 87 C F B E A Tax in SMillions 27.5% rate in Cash outflow Cash from Revenue in Taxable Income years 1,2,3 and After tax Cash Flow In 50% there after Depreciation in expenses in SMillions $1,762.56 $1,860.48 $Millions in $ Millions $12.57 SMillions 38 Year SMillions $40.90 $52.22 $3.46 $1,800 $50.02 39 -$12.70 $46.20 $85.72 $1,900 2 40 31 $2,000 $2,100 41 $0.04 $43.70 $0.02 $43.72 $2,056.32 42 4 $2,200 $2,300 S2.400 Instructions 43 5 44 6 45 Capital Budgeting Cost of Capital e OP Type here to search St A D G K N Now that McCormick & Company has secured the land for the new factory through a loan, it is time to construct the new factory. Instead of using operating cash flow to fund the construction of the new factory, McCormick & Company has decided to raise capital. To raise additional capital, the company is considering issuing additional shares of stock. For McCormick & Company to determine how much it will cost the company to issue stock, the company mnust determine the expected return on the stock in relation to the systematic risk. We can help McCormick & Company with this by answering the following questions P Q R U using the provided information below: McCormick & Company uses the 10-Year Treasury Constant Maturity Rate as the risk free rate. As of 7/1/2019, this was 2.03 according to the US Treasury. McCormick & Company has disclosed the company's levered Beta is 0.60 (MarketWatch, 7/1/2019). McCormick & Company has disclosed the company's expected return on the market is 8.03 % R. R + Bx (R 1 R) To answer the following questions, use the template to the right. risk free rate 2.03 Expected return- 5.63 1. What is McCormick & Company's expected return on the issuance of stock using CAPM? 0.6 Beta expected market return risk free rate 2 R 8.03 In the CAPM, we examined the expected return on the market as a whole. In an effort to estimate the expected return of McCormick & Company's stock, we will use the Dividend Discount Model (DDM). We can help McCormick & Company with by answering the following questions using the provided information below: 2.03 this P McCormick & Company's expected dividend per share next year is $2.28 McCormick & Company's expected dividend per share constant growth rate is 8.70 % ( as of May 2019) McCormick & Company's stock price per share was $155.70 on 7/1/2019 2. Using the Dividend Discount Model (DDM), what is the cost of equity? To find the cost of equity using DDM, we take the original equation Div + 9 expected dividend current stock price Div 2.28 P - R-g cost of equity- 155.7 0.10164 and rearrange the equation: constant growth rate- or 10.16 % R= P 0.087 Annuities Financing and Investing Corporate Valuation Instructions 4 e 95% 1 Type here to search PV PMT FV 10 12% $4,424,000 ($13,740,272.47) 2 IN PV PMT FV 12% $ 4,424,000.00 10 ($13,740,272.47) Principal Amount Financed 30% $ 3,096,800.00 Percent Down 4 Loan IN 6 % | S 3,096,800.00 4.5% S3,096,800.00 5% S 3,096,800.00 PV PMT Loan A (S269,993.14) ($391,369.94) ($298,352.80) 20 Loan B 10 Loan C 15. Loan Loan A Total Paid $5,400,000.00 $3,913,699.35 PV PMT 3,096,800 $269,993.14 20 6% 4.5 % $391,369.94 $298,352.80 Loan B $3,096,800 $3,096,800 10 $4,475,291.94 5% Loan C 15 6 Loan B Annuities Instructions Corporate Valuation Financing and Investing e Type here to search As you know from Project 4, (no you don't know from the new 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. ( note, a $350 million investment for a company with $10 billion in assets is significant but not huge. The $4 million investment in Project 4 is trivial and likely not requiring any high level approvals) 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. (We jump right in to the most difficult tax depreciation. Your accountants can help in the real world) 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. will need to finance some of the cash to fund $17 million in The company 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. (Q-6 of the Cost of Capital tab in Project 5) 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 (Project 4 eliminated the relatively easy cash flow time line so this will be your first) 3. 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. (this will be your first calculation of NPV and IRR. These two concepts are VERY important in Finance. You must use the NPV and IRR functions in Excel for this question.) The following questions will be used to estimate risk. Please use Table 3 to calculate cash flow 4. The controller is worried about tax increases and estimates that the tax rate with be raised to 50% (federal and Maryland state) in year 4. Also there is a concern that expenses are understated. He asks, "What would happen to the NPV calculation if the cash tax expenses come in 2% higher than estimated and the tax rate increases to 50% in year 4?" This will allow a subjective evaluation of the project risk. Calculate a new cash flow time line with cash expenses 10% higher than those in Table 2 and with a 50% tax rate. 4P e 100% 5. What would be the net present value in this "worst case" cash flow? What will be the IRR? (Use the NPV and IRR function) 6. Should the project be accepted? Discuss the risk and the reward to McCormick. (this is to see if you can handle decision making under uncertainty. We don't know what will happen in future. Will taxes be raised or not? Will expenses go up or not? Companies must deal with these questions. If you just look at the worst case, not much will happen. We would have no Apple, Microsoft, Tesla, Google, Facebook, or Amazon if these innovators had just abandoned their ideas because someone came up with a worst case scenario. e e Type here to search 100% Dele Ansert Prisc F12 F10 F1 F9 FB F7 FB FS R U I/Y PV 1 N PMT FV ($13,740,272.47) $4,424,000 10 12% I/Y PV PMT FV 12 % $4,424,000.00 ($13,740,272.47) 10 Amount Financed 30% $3,096,800.00 Principal Percent Down 3 $4,424,000.00 PMT I/Y PV Loan 4 ($269,993.14) (S391,369.94) ($298,352.80) 6% $ 3,096,800.00 4.5% 3,096,800.00 5% $ 3,096,800.00 20 Loan A Loan B 10 15 Loan C Total Paid PMT PV /Y Loan $3,096,800 $3,096,800 $3,096,800 $5,400,000.00 $3,913,699.35 $4,475,291.94 $269,993.14 $391,369.94 $298,352.80 6 % 20 Loan A 4.5% 10 Loan B 5% 15 Loan C 6 Loan B Annuities Corporate Valuation Financing and Investing Instructions 100% Type here to search O Z 10 11 12 13 risk free rate 5.63 2.03 Expected return = 14 0.6 Beta 15 expected market return risk free rate 16 8.03 2.03 17 Div 2 Rs 18 P 19 20 21 22 23 24 expected dividend= current stock price 2.28 25 0.101644 cost of equity= 155.7 26 or 10.16% 0.087 constant growth rate= 27 28 Annuities Corporate Valuation Instructions Financing and Investing Type here to search