AutoSave Project 5 McCormick Workbook_2201 - Excel Search A Adam Ziemnicki AZ Share Comments File Home Insert Page Layout Formulas Data Review View Help Shape Format Cut Calibri (Body) 11 AA Wrap Text General Normal Bad Good Neutral Copy Paste BIU A Merge & Center $% 9000 Format Painter Conditional Format as Calculation Formatting Table Check Cell Explanatory...Input Insert Delete Format AutoSum Fill Clear T Sort & Find & Ideas Filter Select Clipboard Font F Alignment Number Styles Cells Editing TextBox 2 fx A B D E F G H L M N 1 Table 1 P Q R S 2 Details of McCormick Plant Proposal MACRS 3 4 5 6 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. Depreciation $350 Year 7 Year class Depreciation 14.29% $50.02 2 24.49% $85.72 7 3 17.49% $61.22 8 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. 4 12.49% $43.72 9 5 8.93% $31.26 10 11 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. 6 8.92% $31.22 7 8.93% $31.26 12 4.46% $15.61 13 14 15 16 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. 17 A B Cash Table 2 D E 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. 18 Year Cash from Revenue in $Millions outflow, Taxable expenses in Depreciation in Income in $ $Millions $Millions Millions 27.5% rate Tax in $Millions After tax Cash Flow In $Millions 19 20 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. 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 21 3 $2,000 $1,920 $61.22 $18.79 $5.17 $74.83 Use the WACC that you calculated in the Cost of Capital @b. 22 4 $2,100 $2,016 $43.72 $40.29 $11.08 $72.92 23 5 $2,200 $2,112 $31.26 $56.75 $15.60 $72.40 24 6 $2,300 $2,208 $31.22 $60.78 $16.71 $75.29 25 Questions: 7 $2,400 $2,304 $31.26 $64.75 $17.80 $78.20 26 8 $2,500 $2,400 $15.61 $84.39 $23.21 $76.79 27 28 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. 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 29 11 $2,600 $2,496 $0.00 $104.00 $28.60 $75.40 2. Create an after-tax cash flow timeline similar to the one you did in Project 4. 30 12 $2,500 $2,400 $0.00 $100.00 $27.50 $72.50 31 13 $2,400 $2,304 B #VALUE! #VALUE! 32 33 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. 14 $2,200 $2,112 Cash outflow, ex #VALUE! #VALUE! #VALUE! #VALUE! 15 $2,000 $1,920 $1,728.00 -$1,648.00 -$453.20 $533.20 Instructions Cost of Capital Capital Budgeting + Ready Ideas ^ U V W X Y A Count: 20 B 100% 7:35 PM 3/3/2020 21