Can anyone help me with these cells

B70-B74, E70-E74, B90:F100, B105:F110, E115-E121?

Module 7 Hard Model 10/08/15 CHINO MATERIAL SYSTEMS Capital Budgeting Decision Methods This case is designed as an introduction to capital budgeting methods. NPV, IRR, MIRR, PI, and Payback are included in the analysis. The model develops the project's cash flows on the basis of input cost and savings data, then calculates the above decision criteria. Note that this model was constructed specifically for this case, and hence project life and depreciation allowances are fixed. The spreadsheet sheet NPV-PROF contains a graph of the NPV profiles and the sheet MULTIPLE contains a graph of the multiple IRR project. If you are using the student version of the model, the model generating cells have been blanked out. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. ========== =============== ========== ========== ========== ============ =============== INPUT DATA: KEY OUTPUT: New system: $285,000 NPV Delivered cost $20,086 $18,000 IRR Installation 0.00% $15,000 MIRR Salvage value 0.00% $76,000 Payback Annual savings 0.00 PI 0.00 Old system: $0 Current book value $4,000 Current market value $2,500 Removal cost Other data: 12.0% Cost of capital 12.0% Cost of borrowing 14.0% Return for typical investments 36.0% Tax rate ========== =============== ========== ========== ========== ============ =============== MODELGENERATED DATA: Cash flows at t=0: Endofproject cash flows: Cost of new system $285,000 Salvage value ----> $15,000 Installation 18,000 SV tax 5,400 -----------------Total cost $303,000 Total $9,600 ------- Depreciation information: Proceeds from old equip $4,000 Tax on proceeds 1,440 Removal cost (AT) 1,600 Depreciable basis: $303,000 -------Net cash flow, old $960 -------Net invest. outlay $302,040 ======== Annual cash flows: Deprn A.T. Cost Year Tax saving Saving Net CF Cum CF -------------------------------0 1 $21,816 $48,640 70,456 70,456 2 34,906 $48,640 83,546 154,002 3 20,725 $48,640 69,365 223,367 4 13,090 $48,640 61,730 285,096 5 11,999 $48,640 60,639 345,735 6 6,545 $48,640 55,185 400,920 7 $48,640 48,640 449,560 8 $48,640 48,640 498,200 NPV: IRR: TV: MIRR: Payback: PI: $20,085.81 Analysis of ShortTerm Project: Cost Year 1 CF NPV IRR MIRR Data Table for effect of cost savings Savings NPV 20,086 IRR 0.00% 35,000 50,000 60,000 70,000 80,000 MIRR 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Payback 0.00 0.00 0.00 0.00 0.00 0.00 Data Table for effect of cost of capital: Cap Cost NPV IRR MIRR Payback PI 0.00 0.00 0.00 0.00 0.00 0.00 NPV of > short-term PI project 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% 15.0% 16.0% 17.0% 18.0% Data Table for effect of tax rates (numbers from senstivity analysis): Tax rate NPV IRR MIRR Payback PI 20.0% 30.0% 40.0% 50.0% 60.0% Analysis of the Multiple IRR Project: Cap cost NPV CF 0 1 2 0.0 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 110% 120% 130% 140% 150% 160% 170% 180% 190% 200% 210% 220% 230% 240% 250% 260% 270% 280% 290% 300% 310% 320% 330% NPV = IRR(1) = IRR(2) = Err:523 Err:523 the two IRRs. ========== =============== ========== ========== ========== ============ =============== END FIGURE 1 NPV-PROFILES 12 10 8 NPV 6 4 2 0 1 2 3 4 5 6 7 8 COST OF CAPITAL eight-year project one-year project 9 10 11 FIGURE 2 Multiple IRR Illustration 12.0 10.0 8.0 NPV 6.0 4.0 2.0 0.0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Cost of Capital NPV Module 7 Hard Model 10/09/15 CHINO MATERIAL SYSTEMS Capital Budgeting Decision Methods This case is designed as an introduction to capital budgeting methods. NPV, IRR, MIRR, PI, and Payback are included in the analysis. The model develops the project's cash flows on the basis of input cost and savings data, then calculates the above decision criteria. Note that this model was constructed specifically for this case, and hence project life and depreciation allowances are fixed. The spreadsheet sheet NPV-PROF contains a graph of the NPV profiles and the sheet MULTIPLE contains a graph of the multiple IRR project. If you are using the student version of the model, the model generating cells have been blanked out. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. ========== =============== ========== ========== ========== =========== ============== INPUT DATA: KEY OUTPUT: New system: $285,000 NPV Delivered cost $20,086 $18,000 IRR Installation 14.06% $15,000 MIRR Salvage value 12.90% $76,000 Payback Annual savings 4.28 PI 1.07 Old system: $0 Current book value $4,000 Current market value $2,500 Removal cost Other data: 12.0% Cost of capital 12.0% Cost of borrowing 14.0% Return for typical investments 36.0% Tax rate ========== =============== ========== ========== ========== =========== ============== MODELGENERATED DATA: Cash flows at t=0: Endofproject cash flows: Cost of new system $285,000 Salvage value ----> $15,000 Installation 18,000 SV tax 5,400 -----------------Total cost $303,000 Total $9,600 ------- Depreciation information: Proceeds from old equip $4,000 Tax on proceeds 1,440 Removal cost (AT) 1,600 Depreciable basis: $303,000 -------Net cash flow, old $960 -------Net invest. outlay $302,040 ======== Annual cash flows: Deprn A.T. Cost Year Tax saving Saving Net CF Cum CF -------------------------------Year 0 ($302,040) ($302,040) 0 1 $21,816 $48,640 70,456 (231,584) 1 2 34,906 $48,640 83,546 (148,038) 2 3 20,725 $48,640 69,365 (78,673) 3 4 13,090 $48,640 61,730 (16,944) 4 5 11,999 $48,640 60,639 43,695 5 6 6,545 $48,640 55,185 98,880 6 7 $48,640 48,640 147,520 7 8 $48,640 48,640 196,160 8 NPV: IRR: TV: MIRR: Payback: PI: Analysis of ShortTerm Project: Cost Year 1 CF NPV IRR MIRR $20,085.81 14.1% $70,456 12.90% 4.28 1.07 Data Table for effect of cost savings Savings 35,000 50,000 60,000 70,000 80,000 NPV 20,086 (110,265) (62,576) (30,783) 1,010 32,803 IRR 14.06% -1.19% 5.02% 8.69% 12.11% 15.33% MIRR 12.90% 5.82% 8.80% 10.51% 12.05% 13.45% Payback 4.28 7.54 5.90 5.15 4.57 4.11 Data Table for effect of cost of capital: Cap Cost 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% NPV IRR MIRR Payback 65,967 53,545 41,787 30,648 20,086 10,062 542 14.06% 14.06% 14.06% 14.06% 14.06% 14.06% 14.06% 10.70% 11.25% 11.80% 12.35% 12.90% 13.46% 14.03% 4.28 4.28 4.28 4.28 4.28 4.28 4.28 PI 1.07 0.63 0.79 0.90 1.00 1.11 NPV of > short-term PI project 1.22 1.18 1.14 1.10 1.07 1.03 1.00 0 1 2 3 4 5 6 7 8 20% 30% 40% 50% 60% -301800 72920 80192 72314 68072 67466 64436 60800 60800 -301950 71380 82288 70471 64108 63199 58654 53200 53200 -302100 69840 84384 68628 60144 58932 52872 45600 45600 -302250 68300 86480 66785 56180 54665 47090 38000 38000 -302400 66760 88576 64942 52216 50398 41308 30400 30400 -301800 -228880 -148688 -76374 -8302 59164 123600 184400 245200 -301950 -230570 -148282 -77811 -13703 49496 108150 161350 214550 -302100 -232260 -147876 -79248 -19104 39828 92700 138300 183900 -302250 -233950 -147470 -80685 -24505 30160 77250 115250 153250 -302400 -235640 -147064 -82122 -29906 20492 61800 92200 122600 15.0% 16.0% 17.0% 18.0% (8,506) (17,114) (25,309) (33,116) 14.06% 14.06% 14.06% 14.06% 14.59% 15.16% 15.73% 16.30% 4.28 4.28 4.28 4.28 0.97 0.94 0.92 0.89 Data Table for effect of tax rates (numbers from senstivity analysis): Tax rate NPV MIRR Payback PI 44,955 29,412 13,869 (1,674) (17,217) 20.0% 30.0% 40.0% 50.0% 60.0% IRR 16.34% 14.95% 13.44% 11.82% 10.04% 13.96% 13.31% 12.63% 11.92% 11.18% 4.12 4.22 4.32 4.45 4.59 1.15 1.10 1.05 0.99 0.94 Analysis of the Multiple IRR Project: Cap cost 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 110% 120% 130% 140% 150% 160% 170% 180% 190% 200% 210% 220% 230% 240% 250% 260% 270% 280% 290% 300% 310% 320% 330% NPV CF -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 -103742.8 0 1 2 NPV = IRR(1) = IRR(2) = Err:523 Err:523 the two IRRs. -103742.8 -103742.8 ========== =============== ========== ========== ========== =========== ============== END FIGURE 1 NPV-PROFILES 80000 NPV 60000 40000 20000 0 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 -20000 -40000 COST OF CAPITAL eight-year project one-year project 0.16 0.17 0.18 FIGURE 2 Multiple IRR Illustration 0.0 -20000.0 NPV -40000.0 -60000.0 -80000.0 -100000.0 -120000.0 Cost of Capital NP V