Please assist. I am having trouble.

Drawing Tools Project 5 Workbook - Excel X File Home Insert Page Layout Formulas Data Review View Help Acrobat Format Tell me what you want to do Share Cut Copy Calibri (Body) - 11 - A A ab Wrap Text Accounting AutoSum - Paste Fill - AY O Format Painter BIU - -A- Merge & Center * % " 68 08 Conditional Format as Cell Insert Delete Format Sort & Find & Sensitivity Formatting . Table . Styles ~ Clear - Filter - Select ~ Clipboard Font Alignment Number Styles Cells Editing Sensitivity TextBox 1 X fx A B C D E F G H K M N O P Q - Details of Mccormick Plant Proposal As you know from Project 4, Mccormick & Company is considering building a new factory in Largo, Maryland. Mccormick & Company decided to offer $4,424,000 to obtain the land for this project. The new factory will require an initial investment of $350 million to build the new plant and purchase equipment. You have been asked to continue your work from project 4 with a full analysis of the proposed factory, including the start- up costs, the projected net cash flows from operations, the tax impact of depreciation, and the cash flow impacts of changes in working capital. 10 11 The investment will be depreciated as a modified accelerated cost recovery system (MACRS) seven-year class asset. The 12 correct depreciation table is included below. 13 O 14 The company will need to finance some of the cash to fund $17 million in accounts receivable and $14 million in Inventory 15 starting at year zero. The company expects vendors to give free credit on purchases of $15 million (accounts payable). The CFO wants you to consider the net cash outflows for working capital as well as the cash outflows for the plant, 16 equipment, and land in year zero. 17 Note: The $17 million for accounts receivable and the $14 million for Inventory are cash outflows. The $15 million for 18 accounts payable is a cash inflow. 19 20 The CFO has indicated that this net working capital will be recovered as a cash inflow in year 21. She also estimates that 21 the company will be able to sell the factory, equipment, and land in year 21 for $40 million. 22 23 The company estimates that the cash flows from operations will be as shown in Table 2. Note: Only the cash flows related 24 to operations (years 1-20) will generate accounting profits and thus taxable income (or losses). Instructions | Cost of Capital Capital Budgeting + - + 120% Type here to search O 9 0 W 98% } * ~ 6 6 21 9 4:26 PM 9/13/2020Drawing Tools Project 5 Workbook - Excel X File Home Insert Page Layout Formulas Data Review View Help Acrobat Format Tell me what you want to do Share Cut Calibri (Body) - 11 - A A ab Wrap Text Accounting AutoSum - Paste Copy Fill - AY O Format Painter BIU - -A- Merge & Center * % " 68 08 Conditional Format as Cell Insert Delete Format Sort & Find & Sensitivity Formatting . Table . Styles Clear Filter - Select ~ Clipboard Font Alignment Number Styles Cells Editing Sensitivity TextBox 1 X A B C D E F G H K M N O P Q 25 26 Questions: 27 1. What will be the depreciation for tax purposes each year? Complete Table 1 below to answer this question. Note: the 28 total deprecation for tax purposes will be $350 million. 29 2. Create an after-tax cash flow timeline for the proposed factory in Table 2 below. Note: The CFO estimates that 30 operations for the company will be profitable on an on-going basis. As a result, any accounting loss on this specific project 31 will provide a tax benefit for the company overall in the year of the loss. 32 33 3. Calculate the NPV and IRR using the data from Table 2. Should the project be accepted? 34 35 The following questions will be used to assist in evaluating the proposed project's risk. 36 37 4. The CFO is particularly concerned about the potential impact of future tax increases and that the expenses may have 38 been systematically understated. In order to undertake an objective evaluation of the project's risk, she asks you to 39 prepare a second analysis with a less favorable set of assumptions. She asks, "What would happen to the NPV and IRR 40 calculations if the cash outflow for expenses comes in 2% higher than estimated for the entire life of the project and if the 41 tax rate increases to 50% (combined Federal and Maryland state rates) starting in year 4?" Create an after-tax cash flow timeline for the proposed factory with these new assumptions in Table 3 below. 42 43 5. Calculate the NPV and IRR using the data from Table 3. 44 45 6. Considering all of your work in questions 1 through 5, should the project be accepted? Discuss the risk and the 46 47 Answer Questions Below: 48 Instructions | Cost of Capital Capital Budgeting + - + 120% Type here to search O W X 98% * ~ 6 6 21 9 4:26 PM 9/13/2020Drawing Tools Project 5 Workbook - Excel X File Home Insert Page Layout Formulas Data Review View Help Acrobat Format Tell me what you want to do Share Cut Calibri (Body) - 11 - A A ab Wrap Text Accounting AutoSum - Paste Copy Fill - AY O Format Painter BIU- -SA- Merge & Center * % " Conditional Format as Cell Insert Delete Format Sort & Find & Sensitivity Formatting . Table . Styles Clear - Filter - Select ~ Clipboard Font Alignment Number Styles Cells Editing Sensitivity TextBox 1 X fix A B C D E F G H K L M N O P Q 87 88 3 NPV 89 IRR 90 91 92 4 Table 3 93 A B C D E F Tax in $Millions 27.5% rate in years 1 , Taxable 2, 3 and After tax Cash from Cash outflow, Depreciat Income 50% Cash Revenue in expenses in ion in in $ there Flow In 94 Year $Millions $Millions $Millions Millions after $Millions 95 O S 96 1 $ 1,800.00 $ 1,762.56 $ 50.02 $ (12.57) $ (3.46) $ 40.90 97 2 $ 1,900.00 $ 1,860.48 $ 85.72 $ (46.20) $ (12.70) $ 52.22 98 3 $ 2,000.00 $ 1,958.40 $ 61.22 $ (19.62) $ (5.39) $ 46.99 99 4 $ 2,100.00 $ 2,056.32 $ 43.72 $ (0.04) $ (0.02) $ 43.70 100 5 $ 2,200.00 $ 2,154.24 $ 31.26 $ 14.50 $ 7.25 $ 38.51 101 6 $ 2,300.00 $ 2,252.16 $ 31.22 $ 16.62 $ 8.31 $ 39.53 102 7 $ 2,400.00 $ 2,350.08 $ 31.26 $ 18.67 $ 9.33 $ 40.59 Instructions | Cost of Capital Capital Budgeting + -+ 120% Type here to search O W X 98% " ~ 6 6 21) d 4:27 PM 9/13/2020 ESDrawing Tools Project 5 Workbook - Excel X File Home Insert Page Layout Formulas Data Review View Help Acrobat Format Tell me what you want to do Share Cut Calibri (Body) - 11 - A A ab Wrap Text Accounting AutoSum - Paste Copy Fill - AY O Format Painter BIU - -MA- BE Merge & Center % " Conditional Format as Cell Insert Delete Format Sort & Find & Formatting ~ Table . Styles Clear - Sensitivity Filter - Select ~ Clipboard Font Alignment Number Styles Cells Editing Sensitivity TextBox 1 X fx A B C D E F G H J K L M N O P Q R S 62 2 Table 2 63 A B C D E Tax in After tax Cash from Cash outflow, Depreciat Taxable $Millions Cash Revenue in expenses in ion in Income in 27.5% low In 64 Year $Millions $Millions $Millions $ Millions rate $Millions 65 o $ 66 1 $ 1,800.00 $ 1,728.00 $ 50.02 $ 21.99 $ 6.05 $ 65.95 67 2 $ 1,900.00 $ 1,824.00 $ 85.72 $ (9.72) $ (2.67) $ 78.67 68 3 $ 2,000.00 $ 1,920.00 $ 61.22 $ 18.79 $ 5.17 $ 74.83 69 4 $ 2,100.00 $ 2,016.00 $ 43.72 $ 40.29 $ 11.08 $ 72.92 70 5 $ 2,200.00 $ 2,112.00 $ 31.26 $ 56.75 $ 15.60 $ 72.40 71 6 $ 2,300.00 $ 2,208.00 $ 31.22 $ 60.78 $ $ 16.71 $ 75.29 72 7 $ 2,400.00 $ 2,304.00 $ 31.26 $ 64.75 $ 17.80 $ 78.20 73 8 $ 2,500.00 $ 2,400.00 $ 15.61 $ 84.39 $ 23.21 $ 76.79 74 9 $ 2,600.00 $ 2,496.00 $ 104.00 $ 28.60 $ 75.40 75 10 $ 2,700.00 $ 2,592.00 $ 108.00 $ 29.70 $ 78.30 76 11 $ 2,600.00 $ 2,496.00 $ 104.00 $ 28.60 $ 75.40 77 12 $ 2,500.00 $ 2,400.00 $ 100.00 $ 27.50 $ 72.50 78 13 $ 2,400.00 $ 2,304.00 $ 96.00 $ 26.40 $ 69.60 79 14 $ 2,200.00 $ 2,112.00 $ 88.00 $ 24.20 $ 63.80 80 15 $ 2,000.00 $ 1,920.00 $ 80.00 $ 22.00 $ 58.00 81 16 $ 1,800.00 $ 1,728.00 $ 72.00 $ 19.80 $ 52.20 82 17 $ 1,500.00 $ 1,440.00 $ 60.00 $ 16.50 $ 43.50 83 18 $ 1,200.00 $ 1,152.00 $ 48.00 $ 13.20 $ 34.80 84 19 5 800.00 $ 768.00 $ 32.00 $ 8.80 $ 23.20 85 20 $ 400.00 $ 384.00 $ 16.00 $ 4.40 $ 11.60 86 21 $ 401.00 $ 384.96 $ 16.04 $ 4.41 $ 11.63 87 Instructions | Cost of Capital Capital Budgeting + - + 100% Type here to search O W x 98% * ~ 6 6 21) d 4:25 PM 9/13/2020 5Drawing Tools Project 5 Workbook - Excel .... X File Home Insert Page Layout Formulas Data Review View Help Acrobat Format Tell me what you want to do Share Cut ab Wrap Text Accounting AutoSum - Paste Copy Calibri (Body) - 11 - A" A AY O Format Painter BIU - -A- BE Merge & Center * % " Conditional Format as Cell Insert Delete Format Fill - Formatting ~ Table . Styles Clear - Sort & Find & Sensitivity Filter - Select ~ Clipboard Font Alignment Number Styles Cells Editing Sensitivity TextBox 1 X V A B C D E F G H K L M N O P Q 102 7 $ 2,400.00 $ 2,350.08 $ 31.26 $ 18.67 $ 9.33 $ 40.59 103 8 $ 2,500.00 $ 2,448.00 $ 15.61 $ 36.39 $ 18.20 $ 33.81 104 9 $ 2,600.00 $ 2,545.92 $ $ 54.08 $ 27.04 $ 27.04 105 10 $ 2,700.00 $ 2,643.84 $ $ 56.16 $ 28.08 $ 28.08 106 11 $ 2,600.00 $ 2,545.92 $ $ 54.08 $ 27.04 $ 27.04 107 12 $ 2,500.00 $ 2,448.00 $ 52.00 $ 26.00 $ 26.00 108 13 $ 2,400.00 $ 2,350.08 $ 49.92 $ 24.96 $ 24.96 109 14 $ 2,200.00 $ 2,154.24 $ 45.76 $ 22.88 $ 22.88 110 15 $ 2,000.00 $ 1,958.40 $ 41.60 $ 20.80 $ 20.80 111 16 $ 1,800.00 $ 1,762.56 $ 37.44 $ 18.72 $ 18.72 112 17 $ 1,500.00 $ 1,468.80 $ 31.20 $ 15.60 $ 15.60 113 18 $ 1,200.00 $ 1,175.04 $ 24.96 $ 12.48 $ 12.48 114 19 $ 800.00 $ 783.36 $ 16.64 $ 8.32 $ 8.32 115 20 $ 400.00 $ 391.68 8.32 $ 4.16 $ 4.16 116 21 117 118 5 NPV 119 IRR 120 121 122 6 123 124 125 Instructions | Cost of Capital Capital Budgeting + - + 120% Type here to search O X 98% * ~ 6 6 21) d 4:27 PM 9/13/2020 ES