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 == 30 Wrap Text General Normal Bad Good Neutral Copy Paste BIU R A Merge & Center $ % 950 00 Format Painter Conditional Format as Calculation Formatting Table Check Cell Explanatory...Input Insert Delete Format AutoSum Fill Clear T Sort & Find & Ideas Clipboard Font F Alignment Number Styles Cells Filter Select Editing TextBox 2 fx Ideas ^ L M N P Q U V W Y 20 $400 $384 $2,208.00 -$2,192.00 -$602.80 $618.80 A B D E F G H J 38 39 40 viaryland 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. Use Table 3 41 42 5. What would be the net present value, NPV in this "worst case" cash flow? What will be the IRR? 43 44 6. Should the project be accepted? Discuss the risk and the reward to McCormick. 45 46 Answer Questions Below: 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 Instructions Cost of Capital Capital Budgeting Ready A Year Cash from Revenue in $Millions B Cash outflow, expenses in $Millions Table 3 C D E F Taxable Depreciation in Income in $ $Millions Tax in $Millions 27.5% rate in years 1, 2, 3 and After tax Cash Flow In Millions 50% there after $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 $1,958.40 $61.22 -$19.62 -$5.39 $46.99 4 $2,100 $2,056.32 $43.72 -$0.04 -$0.01 $43.69 5 $2,200 $2,154.24 $31.26 $14.50 $3.99 $41.77 6 $2,300 $2,252.16 $31.22 $16.62 $4.57 $43.27 1 $2,400 $2,350.08 $31.26 $18.67 $5.13 $44.79 8 $2,500 $2,448.00 $15.61 $36.39 $10.01 $41.99 9 $2,600 $2,545.92 $0.00 $54.08 $14.87 $39.21 10 $2,700 $2,643.84 $0.00 $56.16 $15.44 $40.72 11 $2,600 $2,545.92 $0.00 $54.08 $14.87 $39.21 12 $2,500 $2,448.00 $0.00 $52.00 $14.30 $37.70 13 $2,400 $2,350.08 B 14 $2,200 $2,154.24 Cash outflow, ex 15 $2,000 $1,958.40 $1,728.00 #VALUE! #VALUE! -$1,686.40 #VALUE! #VALUE! #VALUE! #VALUE! -$463.76 $505.36 16 $1,800 $1,762.56 $1,824.00 -$1,786.56 -$491.30 $528.74 17 $1,500 $1,468.80 $1,920.00 -$1,888.80 -$519.42 $550.62 18 $1,200 $1,175.04 $2,016.00 -$1,991.04 -$547.54 $572.50 19 $800 20 $400 $783.36 $391.68 $2,112.00 $2,208.00 -$2,095.36 -$576.22 $592.86 -$2,199.68 -$604.91 $613.23 B < A 100% 8:13 PM 3/3/2020 21