Right Smart Bowling is buying a new polishing machine to replace the old, worn-out one. Compute the payback period, accounting rate of return, and net present value. Hurdle rate 10.0% Useful (project) life 5 Residual value 5,000 Investment 45,000 Net cash inflows Cash Flow Year 1 15,000 Cash Flow Year 2 15,000 Cash Flow Year 3 15,000 Cash Flow Year 4 15,000 Cash Flow Year 5 15,000 USING EXCEL OR OTHER SPREADSHEET, EVERYTHING BELOW THIS LINE SHOULD BE FORMULAS 1 mpute the payback Amount Years Initial investment Cash flow year 1 use the formula =-MIN(D19, D10), which indicates to use the lesser of the unrecaptured investment or the cash flow Amount left to recapture Cash flow year 2 use the formula =-MIN(D21, D11), which indicates to use the lesser of the unrecaptured investment or the cash flow Amount left to recapture Cash flow year 3 use the formula =-MIN(D23, D12), which indicates to use the lesser of the unrecaptured investment or the cash flow Amount left to recapture Cash flow year 4 use the formula =-MIN(D25, D13), which indicates to use the lesser of the unrecaptured investment or the cash flow Amount left to recapture Cash flow year 5 use the formula =-MIN(D27, D14), which indicates to use the lesser of the unrecaptured investment or the cash flow Amount left to recapture Payback = years 2 mpute the Accounting Rate of Return (a) compute average annual accounting income Annual net operating cash flows does not include original investment or residual value DepreciationA B C D E F G H I J K L M N O P Q R S T U V 33 2 34 Compute the Accounting Rate of Return 35 (a) compute average annual accounting income 36 Annual net operating cash flows does not include original investment or residual value 37 Depreciation 38 Net accounting income 39 Divided by project life 40 Average annual accounting income 41 (b) compute average amount invested 42 Beginning investment book value (cost) 43 Ending investment book value (cost - total depreciation) 44 45 Divided by 46 Average amount invested 47 ( c) compute ARR 48 Average annual accounting Average amount Accounting Rate of 49 (round to nearest tenth of a percent) 50 51 52 3 53 Compute the net present value of the investment Present value of 19 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 54 N = 1 0.9520 0.9430 0.9350 0.9260 0.9170 0.9090 0.9010 0.8930 0.8850 0.8770 0.8700 55 Period Cash Flows Discount Factor Discounted cash flow N = 2 0.9070 0.8900 0.8730 0.8570 0.8420 0.8260 0.8120 0.7970 0.7830 0.7690 0.7560 56 1 N = 3 0.8640 0.8400 0.8160 0.7940 0.7720 0.7510 0.7310 0.7120 0.6930 0.6750 0.6580 57 N = 4 0.8230 0.7920 0.7630 0.7350 0.7080 0.6830 0.6590 0.6360 0.6130 0.5920 0.5720 58 AWN N = 5 0.7840 0.7470 0.7130 0.6810 0.6500 0.6210 |0.5930 0.5670 0.5430 0.5190 0.4970 59 N = 6 0.7460 0.7050 0.6660 0.6300 0.5960 0.5640 0.5350 0.5070 0.4800 0.4560 0.4320 60 5 N = 7 0.7110 0.6650 0.6230|0.5830 0.5470 0.5130 0.4820 0.4520 0.4250 0.4000 0.3760 61 Present value of net cash flows N = 8 0.6770 0.6270 0.5820 0.5400 0.5020 0.4670 0.4340 0.4040 0.3760 0.3510 0.3270 62 Cost of Asset N = 9 0.6450 0.5920 0.5440 0.5000 0.4600 0.4240 0.3910 0.3610 0.3330 0.3080 0.2840 63 Net Present Value @ N = 10 0.6140 0.5580 0.5080 0.4630 0.4220 0.3860 0.3520 |0.3220 0.2950 0.2700 |0.2470 64 Factors are rounded to the nearest thousandth 65 66 4 67 Compute the profitability index, rounded to two decimal places. 68 69 Present value of net cash flows 70 Divided by cost of assetA G H K M N O Q R 15 4 Compute the profitability index, rounded to two decimal places. Present value of net cash flows Divided by cost of asset Profitability index 5 Would you recommend this investment? Why or why not? Optional Compute the net present value of the investment using Excel's PV function. The PV function will return a negative number, so use a negative sign in front of the formula to convert it to a positive number. Period Cash Flows Discounted cash flow 1 =-PV(D$68,C61,0,D61) - WN 5 Present value of net cash flows Cost of Asset Net Present Value @ Optional 2 Compute the net present value of the investment using Excel's NPV function Period Cash Flows 2 UI AW CO includes proceeds from saleA B C D E F G H I J K Hurdle rate Present value of net cash flows Cost of asset Net Present Value Optional Compute the internal rate of return of the investment using Excel's IRR function. Display to two decimal places, but do not round. Cash Flow Year 0 (Cost) Note: IRR requires a negative amount for the investment. Cash Flow Year Cash Flow Year 2 Cash Flow Year 3 Cash Flow Year 4 Cash Flow Year 5 (included proceeds from sale) IRR Optional Proof of IRR Period Cash Flows Discounted cash flow 1 =-PV(D$68, C61,0,D61) 2 4 UT Present value of net cash flows Cost of Asset Net Present Value @ (link to IRR)