The CAPBUD2 worksheet handles only cash inflows that are even in amount each year. Many capital projects generate uneven cash inflows. Suppose that the new store had expected cash earnings of $80,000 per year for the first two years, $140,000 for the next four years, and $220,000 for the last four years. The new stores will generate the same total cash return ($1,600,000) as in the original problem, but the timing of the cash flows is different. Alter the CAPBUD2 worksheet so that the NPV and IRR calculations can be made whether there are even or uneven cash flows.

Hint: One suggestion is to label column F in the scratch pad as Uneven cash flows. Enter the uneven cash flows for each year. Modify FORMULA3 (cell E17) to include these cash flows. Modify the formulas in the range E30 to E39 to include the new data. Then set cell E10 (estimated Annual Net Cash Inflow) to zero. When you have even cash flows, use cell E10 and set column F in the scratch pad to zeros. If you have uneven cash flows, set cell E10 to zero and fill in column F in the scratch pad.

Note that this solution causes garbage to come out in cells E15 and E16 because those formulas were not altered. Check figures for uneven cash flows: NPV (cell E17), $68,674.

A B D E F G 1 2 3 CAPBUD Capital Budgeting $500,000 10 years $25,000 $110,000 20.00% 4.55 years 12.50% ($34,790) 17.95% 5 Data Section 6 7 Cost of investment (initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of return 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Scratch Pad Cash flow table needed for NPV & IRR calculations NPV IRR Annual Salvage Combined Year Cash Flow Value Flows 0 -500000 1 110000 0 110000 2 110000 0 110000 3 110000 0 110000 4 110000 0 110000 5 110000 0 110000 6 110000 0 110000 7 110000 0 110000 8 110000 0 110000 9 110000 0 110000 10 110000 25000 135000 B D E F years 500000 10 25000 110000 0.2 years =E7/E10 =(E10-(E7-E97/E8)E7 =NPV(E11,E30:E39)+E29 =IRR(E29:E39,E11) Scratch Pad 1 2 3 4 5 Data Section 6 7 Cost of investment initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of retum 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 10 NPV & IRR calculations NPV Annual Salvage Cash Flow Value Year 0 1 =(B30+1) =(B31+1) =(B32+1) =(B33+1) =(B34+1) =(B35+1) =(B36+1) =(B37+1) =(B38+1) =IF((E8>=1),E10,0) =IF((E8>=2),E10,0) =IF((E8>=3).E10,0) =IF((E8>=4),E10,0) =IF((E8>=5),E10,0) =IF((E8>=6),E10,0) =IF((E8>=7),E10,0) =IF((E8>=8),E10,0) =IF((E8>=9),E10,0) =IF((E8>=10),E 10,0) =IF((E8=1),E9,0) =IF((E8=2),E9,0) =IF((E8=3).E9.0) =IF((E8=4),E9,0) =IF((E855).E9,0) =IF((E8=6),E9,0) =IF((E8=7),E9,0) =IF((E8=8),E9.0) =IF((E8=9),E9,0) =IF((E8=10),E9,0) IRR Combined Flows --E7 =C30+D30 =C31+D31 =C32+D32 =C33+D33 =C34+D34 =C35+D35 =C36+D36 =C37+D37 =C38+D38 =C39+D39