please help with questions 1 - 6. I am attaching all required docs. I know there is a lot of info attached but I would greatly appreciate your help. Thank you.

Here are project 5 tables

This is project 4 info.

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. The company will need to finance some of the cash to fund $17 million in starting at vear zero. The company 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 47 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. 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) 93% 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. 26 Type here to search Table 1 MACRS Depreciation 7 Year class 14.29% $350 Depreciation Year $50.02 $85.72 $61.22 $43.72 24.49% 17.49% 12.49% 7 4 $31.26 8.93% 9. 8.92% 8.93% $31.22 10 $31.26 $15.61 11 4.46% 8. 12 Table 2 13 14 Cash outflow, Cash from After tax Cash Flow In Taxable Income Tax in $Millions 27.5% rate Depreciation in expenses in $Millions $1,728 Revenue in $Millions in $ Millions $Millions $Millions $1,800 $65.95 15 Year $6.05 $21.99 $50.02 16 Capital Budgeting Cost of Capital Instructions 26 O Type here to search 1. 2. 3. 5. 6. $400 $384 20 $16.00 $4.40 $27.60 Table 3 A Tax in $Millions 27.5% rate in Taxable Income years 1, 2, 3 and After tax Cash Flow In 50% there after Cash from Cash outflow, Revenue in expenses in $Millions Depreciation in $Millions B Year $Millions in $ Millions $Millions $1,800 $1,900 $2,000 $2,100 $1,762.56 $1,860.48 $1,958.40 $50.02 $40.90 $52.22 $47.00 $43.70 -$12.57 -$3.46 -$12.70 -$5.40 -$46.20 $85.72 $61.22 $43.72 $31.26 -$19.62 $2,056.32 $2,154.24 $2,252.16 -$0.04 -$0.02 $2,200 $2,300 $2,400 $2,500 $14.50 $7.25 $38.51 $31.22 $16.62 $8.31 $39.53 $31.26 $15.61 $0.00 $18.67 $9.33 $2,350.08 $40.59 $36.39 $18.20 $33.80 $2,448.00 8 $54.08 $27.04 $27.04 $28.08 $27.04 $2,545.92 $2,643.84 $2,545.92 $2,600 $28.08 $0.00 $56.16 $2,700 10 $0.00 $54.08 $27.04 $2,600 11 61 Capital Budgeting Cost of Capital Instructions 92% Type here to search 26 R. 00 670 as P. $27.04 $26.00 $24.96 $22.88 $20.80 $2,600 $2,500 $2,400 $2,200 $2,545.92 $0.00 $54.08 $27.04 11 $2,448.00 $52.00 $26.00 $24.96 12 $2,350.08 $2,154.24 $1,958.40 $49.92 13 $22.88 $20.80 $45.76 $41.60 $37.44 14 $2,000 15 $18.72 $15.60 $18.72 $1,800 $1,762.56 16 $15.60 $1,468.80 $31.20 $24.96 $1,500 17 $12.48 $12.48 $1,175.04 $1,200 18 $8.32 $8.32 $16.64 $783.36 $800 57 58 59 50 61 19 $4.16 $4.16 $8.32 $391.68 $400 20 62 63 64 65 66 Capital Budgeting Cost of Capital Instructions 26 O Type here to search R. H. McCormick & Company is considering building a new factory in Largo, Maryland. James Francis, a landowner, is selling a 4.35-acre parcel of industrial zoned land with a listed sale price of $3,000,000.00 for the land, McCormick & Company is interested in the land and so is another manufacturing company. The competing manufacturing company has made a full offer of $3,000,000.00 for the land. McCormick & Company knows it can make an offer to outbid the competitor to obtain the land, So, McCormick & Company decided to offer $4,424,000.00. 4. 6. 7. Now, the landowner must make a decision between the two competing offers. To make this decision, James should first identify the Future Value (FV) of each offer. James's bank is offering a 12 percent interest rate when invested through the bank-managed growth 9. stock portfolios. Let's help James make his decision by answering the following questions using the template to the right. 10 10 11 1. What is the Future Value (FV) of each offer? FV=13,740,272 company makes an for $3,000,000.00 and McCormick & Company makes an offer $4,424,000.00. What is the two competing offers? Unknown manufacturing 12 13 2. Based on your Future Value calculations, which offer should James accept? James should accept the offer from McCormick and 15 Company. 14 10 Principal 16 McCormick & Company has decided in order for the company to have a minimal impact on current cash flows, the company will need to ### 17 borrow 70 percent Loan to Value (LTV) of the $4,424,000.00 offer in the form of a commercial acquisition and development loan to 18 purchase the land. This means McCormick & Company will need to make a 30 percent down payment to secure the commercial 4. Loan 19 acquisition and development loan. McCormick & Company is considering three different loan options: Loan A 20 Loan B 21 Loan A: 20-year loan with a fixed annual interest rate of 6 percent Loan C 22. Loan B: 10-year loan with a fixed annual interest rate of 4.5 percent 23 Loan C: 15-year loan with a fixed annual interest rate of 5 percent Loan 24 Loan A 3. How much of the total $4,424,000.00 offer will be financed? 3,096,800 25 Loan B Loan C 26 Instructions Corporate Valuation Financing and Investing Annuities 26 4. Which loan will have the lowest monthly payment? Loan A 27 Loan C 28 5. Which loan will have the lowest total payback amount? Loan B $3,913,699.35 29 6 Loan B 30 6. Would you recommend McCormick & Company select the loan with lowest monthly payment or lowest total payment and why? 31 32 Irecommend McCormick select the loan with the lowest total payback amount should be accepted as overall, it will have the lowest 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Instructions Financing and Investing Corporate Valuation Annuities Type here to search P. R. IY 8. 1. PV PMT FV ($13,740,272.47). 10 12% $4,424,000 10 11 IY 12% $ 4,424,000.00 PV PMT FV 12 ($13,740,272.47) 10 13 14 Amount Financed 30% $ 3,096,800.00 Percent Down Principal 15 16 17 PMT IY 6% $ 3,096,800.00 4.5% $ 3,096,800.00 5% $ 3,096,800.00 PV Loan 18 4 ($269,993.14) (S391,369.94) (S298,352.80) 20 19 Loan A 10 Loan B 20 15 21 Loan C 22 Total Paid $5,400,000.00 $3,913,699.35 $4,475,291.94 PMT $269,993.14 PV Loan 23 $3,096,800 6% 20 Loan A 24 $391,369.94 $298,352.80 $3,096,800 4.5% 10 Loan B 25 $3,096,800 5% 15 Loan C 26 27 6 Loan B 28 29 30 31 Annuities Corporate Valuation Financing and Investing Instructions 26 Type here to search 20 McCormick & Company's expected dividend per share next year is $2.28 21 McCormick & Company's expected dividend per share constant growth rate is 8.70% (as of May 2019) 22 McCormick & Company's stock price per share was $155.70 on 7/1/2019 23 2. Using the Dividend Discount Model (DDM), what is the cost of equity? 24 25 To find the cost of equity using DDM, we take the original equation Div P 3= 26 and rearrange the equation: R. Div 27 R5 28 29 30 31 32 34 35 36 37 38 39 40 Annuities Corporate Valuation Financing and Investing Instructions T. Bx (RM RE RE) 8. 1. %3D 9. 10 11 12 13 risk free rate%D 2.03 14 Expected return = 5.63 15 Beta 0.6 expected market return risk free rate 16 D 8.03 2.03 17 18 19 20 21 22 23 24 expected dividend= current stock price= 2.28 25 cost of equity=D 0.101644 155.7 26 or 10.16% 0.087 constant growth rate= 27 Annuities Financing and Investing Corporate Valuation Instructions 26 Type here to search P. 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? Yes I recommend McCormick should change its discount rate from 7% to 9% as market situation changed too much and low discount rate shows higher enterprise value of company which is good for the company but not for investors and stakeholders. Cost of Capital Capital Budgeting Instructions 26 O Type here to search 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. The company will need to finance some of the cash to fund $17 million in starting at vear zero. The company 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 47 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. 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) 93% 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. 26 Type here to search Table 1 MACRS Depreciation 7 Year class 14.29% $350 Depreciation Year $50.02 $85.72 $61.22 $43.72 24.49% 17.49% 12.49% 7 4 $31.26 8.93% 9. 8.92% 8.93% $31.22 10 $31.26 $15.61 11 4.46% 8. 12 Table 2 13 14 Cash outflow, Cash from After tax Cash Flow In Taxable Income Tax in $Millions 27.5% rate Depreciation in expenses in $Millions $1,728 Revenue in $Millions in $ Millions $Millions $Millions $1,800 $65.95 15 Year $6.05 $21.99 $50.02 16 Capital Budgeting Cost of Capital Instructions 26 O Type here to search 1. 2. 3. 5. 6. $400 $384 20 $16.00 $4.40 $27.60 Table 3 A Tax in $Millions 27.5% rate in Taxable Income years 1, 2, 3 and After tax Cash Flow In 50% there after Cash from Cash outflow, Revenue in expenses in $Millions Depreciation in $Millions B Year $Millions in $ Millions $Millions $1,800 $1,900 $2,000 $2,100 $1,762.56 $1,860.48 $1,958.40 $50.02 $40.90 $52.22 $47.00 $43.70 -$12.57 -$3.46 -$12.70 -$5.40 -$46.20 $85.72 $61.22 $43.72 $31.26 -$19.62 $2,056.32 $2,154.24 $2,252.16 -$0.04 -$0.02 $2,200 $2,300 $2,400 $2,500 $14.50 $7.25 $38.51 $31.22 $16.62 $8.31 $39.53 $31.26 $15.61 $0.00 $18.67 $9.33 $2,350.08 $40.59 $36.39 $18.20 $33.80 $2,448.00 8 $54.08 $27.04 $27.04 $28.08 $27.04 $2,545.92 $2,643.84 $2,545.92 $2,600 $28.08 $0.00 $56.16 $2,700 10 $0.00 $54.08 $27.04 $2,600 11 61 Capital Budgeting Cost of Capital Instructions 92% Type here to search 26 R. 00 670 as P. $27.04 $26.00 $24.96 $22.88 $20.80 $2,600 $2,500 $2,400 $2,200 $2,545.92 $0.00 $54.08 $27.04 11 $2,448.00 $52.00 $26.00 $24.96 12 $2,350.08 $2,154.24 $1,958.40 $49.92 13 $22.88 $20.80 $45.76 $41.60 $37.44 14 $2,000 15 $18.72 $15.60 $18.72 $1,800 $1,762.56 16 $15.60 $1,468.80 $31.20 $24.96 $1,500 17 $12.48 $12.48 $1,175.04 $1,200 18 $8.32 $8.32 $16.64 $783.36 $800 57 58 59 50 61 19 $4.16 $4.16 $8.32 $391.68 $400 20 62 63 64 65 66 Capital Budgeting Cost of Capital Instructions 26 O Type here to search R. H. McCormick & Company is considering building a new factory in Largo, Maryland. James Francis, a landowner, is selling a 4.35-acre parcel of industrial zoned land with a listed sale price of $3,000,000.00 for the land, McCormick & Company is interested in the land and so is another manufacturing company. The competing manufacturing company has made a full offer of $3,000,000.00 for the land. McCormick & Company knows it can make an offer to outbid the competitor to obtain the land, So, McCormick & Company decided to offer $4,424,000.00. 4. 6. 7. Now, the landowner must make a decision between the two competing offers. To make this decision, James should first identify the Future Value (FV) of each offer. James's bank is offering a 12 percent interest rate when invested through the bank-managed growth 9. stock portfolios. Let's help James make his decision by answering the following questions using the template to the right. 10 10 11 1. What is the Future Value (FV) of each offer? FV=13,740,272 company makes an for $3,000,000.00 and McCormick & Company makes an offer $4,424,000.00. What is the two competing offers? Unknown manufacturing 12 13 2. Based on your Future Value calculations, which offer should James accept? James should accept the offer from McCormick and 15 Company. 14 10 Principal 16 McCormick & Company has decided in order for the company to have a minimal impact on current cash flows, the company will need to ### 17 borrow 70 percent Loan to Value (LTV) of the $4,424,000.00 offer in the form of a commercial acquisition and development loan to 18 purchase the land. This means McCormick & Company will need to make a 30 percent down payment to secure the commercial 4. Loan 19 acquisition and development loan. McCormick & Company is considering three different loan options: Loan A 20 Loan B 21 Loan A: 20-year loan with a fixed annual interest rate of 6 percent Loan C 22. Loan B: 10-year loan with a fixed annual interest rate of 4.5 percent 23 Loan C: 15-year loan with a fixed annual interest rate of 5 percent Loan 24 Loan A 3. How much of the total $4,424,000.00 offer will be financed? 3,096,800 25 Loan B Loan C 26 Instructions Corporate Valuation Financing and Investing Annuities 26 4. Which loan will have the lowest monthly payment? Loan A 27 Loan C 28 5. Which loan will have the lowest total payback amount? Loan B $3,913,699.35 29 6 Loan B 30 6. Would you recommend McCormick & Company select the loan with lowest monthly payment or lowest total payment and why? 31 32 Irecommend McCormick select the loan with the lowest total payback amount should be accepted as overall, it will have the lowest 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Instructions Financing and Investing Corporate Valuation Annuities Type here to search P. R. IY 8. 1. PV PMT FV ($13,740,272.47). 10 12% $4,424,000 10 11 IY 12% $ 4,424,000.00 PV PMT FV 12 ($13,740,272.47) 10 13 14 Amount Financed 30% $ 3,096,800.00 Percent Down Principal 15 16 17 PMT IY 6% $ 3,096,800.00 4.5% $ 3,096,800.00 5% $ 3,096,800.00 PV Loan 18 4 ($269,993.14) (S391,369.94) (S298,352.80) 20 19 Loan A 10 Loan B 20 15 21 Loan C 22 Total Paid $5,400,000.00 $3,913,699.35 $4,475,291.94 PMT $269,993.14 PV Loan 23 $3,096,800 6% 20 Loan A 24 $391,369.94 $298,352.80 $3,096,800 4.5% 10 Loan B 25 $3,096,800 5% 15 Loan C 26 27 6 Loan B 28 29 30 31 Annuities Corporate Valuation Financing and Investing Instructions 26 Type here to search 20 McCormick & Company's expected dividend per share next year is $2.28 21 McCormick & Company's expected dividend per share constant growth rate is 8.70% (as of May 2019) 22 McCormick & Company's stock price per share was $155.70 on 7/1/2019 23 2. Using the Dividend Discount Model (DDM), what is the cost of equity? 24 25 To find the cost of equity using DDM, we take the original equation Div P 3= 26 and rearrange the equation: R. Div 27 R5 28 29 30 31 32 34 35 36 37 38 39 40 Annuities Corporate Valuation Financing and Investing Instructions T. Bx (RM RE RE) 8. 1. %3D 9. 10 11 12 13 risk free rate%D 2.03 14 Expected return = 5.63 15 Beta 0.6 expected market return risk free rate 16 D 8.03 2.03 17 18 19 20 21 22 23 24 expected dividend= current stock price= 2.28 25 cost of equity=D 0.101644 155.7 26 or 10.16% 0.087 constant growth rate= 27 Annuities Financing and Investing Corporate Valuation Instructions 26 Type here to search P. 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? Yes I recommend McCormick should change its discount rate from 7% to 9% as market situation changed too much and low discount rate shows higher enterprise value of company which is good for the company but not for investors and stakeholders. Cost of Capital Capital Budgeting Instructions 26 O Type here to search