Can I please get some help with my case spreadsheet for chino materials case study 95?

Module 7 Hard Model 06/01/16 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 Annual savings Payback 4.28 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: Cost of new system $285,000 Installation 18,000 -------Total cost $303,000 -------Proceeds from old equip $4,000 Tax on proceeds 1,440 Removal cost (AT) 1,600 -------- ========== ============ Endofproject cash flows: Salvage value ----> SV tax Total Depreciation information: Depreciable basis: Net cash flow, old $960 -------$302,040 ======== Net invest. outlay Annual cash flows: Deprn A.T. Cost Year Tax saving Saving ---------------------0 1 $21,816 $48,640 2 34,906 $48,640 3 20,725 $48,640 4 13,090 $48,640 5 11,999 $48,640 6 6,545 $48,640 7 0 $48,640 8 0 $48,640 NPV: IRR: TV: MIRR: Payback: PI: $20,085.81 14.1% Net CF -----($302,040) 70,456 83,546 69,365 61,730 60,639 55,185 48,640 48,640 Cum CF -----(231,584) (148,038) (78,673) (16,944) 43,695 98,880 147,520 196,160 Analysis of ShortTerm Project: Cost -150000 Initial Inv: Year 1 CF 175,000 NPV $6,250.00 IRR MIRR 12.90% 4.28 Data Table for effect of cost savings Savings NPV 20,086 IRR 14.06% 35,000 50,000 60,000 70,000 80,000 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 PI 0.00 0.00 0.00 0.00 0.00 0.00 Payback > PI Data Table for effect of cost of capital: Cap Cost 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% 15.0% 16.0% 17.0% 18.0% NPV 6,250 12,037 10,550 9,091 7,658 6,250 4,867 3,509 2,174 862 (427) (1,695) IRR 0.00% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% MIRR 0.00% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 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 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% CF -$10,000.0 -$413.2 $5,555.6 $9,171.6 $11,224.5 $12,222.2 $12,500.0 $12,283.7 $11,728.4 $10,941.8 $10,000.0 $8,956.9 $7,851.2 $6,710.8 $5,555.6 $4,400.0 $3,254.4 $2,126.2 $1,020.4 -$59.5 -$1,111.1 -$2,133.2 -$3,125.0 -$4,086.3 -$5,017.3 -$5,918.4 -$6,790.1 -$7,633.3 -$8,448.8 -$9,237.3 -$10,000.0 -$10,737.7 -$11,451.2 -$12,141.7 0 1 2 NPV = IRR(1) = IRR(2) = the two IRRs. -$59.5 -$2,133.2 Initial Inv: (50,000) 200,000 (160,000) 189.4% ========== =============== ========== ========== ========== ============ =============== =============== ct cash flows: $15,000 5,400 ----------$9,600 information: $303,000 150000 NPV of short-term project 50000 =============== END FIGURE 1 NPV-PROFILES 14,000 12,000 10,000 8,000 NPV 6,000 4,000 2,000 0 0.08 (2,000) 0.09 0.1 0.11 0.12 0.13 0.14 0.15 (4,000) COST OF CAPITAL eight-year project one-year project 0.16 0.17 0.18 0.17 0.18 FIGURE 2 Multiple IRR Illustration $15,000.0 $10,000.0 $5,000.0 NPV $0.0 -$5,000.0 -$10,000.0 -$15,000.0 Cost of Capital NPV Module 7 Hard Model 06/02/16 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 Annual savings Payback 4.28 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: Cost of new system $285,000 Installation 18,000 -------Total cost $303,000 -------Proceeds from old equip $4,000 Tax on proceeds 1,440 Removal cost (AT) 1,600 -------- ========== ============ Endofproject cash flows: Salvage value ----> SV tax Total Depreciation information: Depreciable basis: Net cash flow, old $960 -------$302,040 ======== Net invest. outlay Annual cash flows: Deprn A.T. Cost Year Tax saving Saving ---------------------0 1 $21,816 $48,640 2 34,906 $48,640 3 20,725 $48,640 4 13,090 $48,640 5 11,999 $48,640 6 6,545 $48,640 7 0 $48,640 8 0 $48,640 NPV: IRR: TV: MIRR: Payback: PI: $20,085.81 14.1% Net CF -----($302,040) 70,456 83,546 69,365 61,730 60,639 55,185 48,640 48,640 Cum CF -----($302,040) (231,584) (148,038) (78,673) (16,944) 43,695 98,880 147,520 196,160 Analysis of ShortTerm Project: Cost -150000 Initial Inv: Year 1 CF 175,000 NPV $6,250.00 IRR MIRR 12.90% 4.28 Data Table for effect of cost savings Savings NPV 20,086 IRR 14.06% 35,000 50,000 60,000 70,000 80,000 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 PI 0.00 0.00 0.00 0.00 0.00 0.00 Payback > PI Data Table for effect of cost of capital: Cap Cost 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% 15.0% 16.0% 17.0% 18.0% NPV 6,250 IRR 0.00% MIRR 0.00% 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 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% CF -$10,000.0 -$413.2 $5,555.6 $9,171.6 $11,224.5 $12,222.2 $12,500.0 $12,283.7 $11,728.4 $10,941.8 $10,000.0 $8,956.9 $7,851.2 $6,710.8 $5,555.6 $4,400.0 $3,254.4 $2,126.2 $1,020.4 -$59.5 -$1,111.1 -$2,133.2 -$3,125.0 -$4,086.3 -$5,017.3 -$5,918.4 -$6,790.1 -$7,633.3 -$8,448.8 -$9,237.3 -$10,000.0 -$10,737.7 -$11,451.2 -$12,141.7 0 1 2 NPV = IRR(1) = IRR(2) = the two IRRs. -$59.5 -$2,133.2 Initial Inv: (50,000) 200,000 (160,000) $911 10.6% 189.4% ========== =============== ========== ========== ========== ============ =============== =============== ct cash flows: $15,000 5,400 ----------$9,600 information: $303,000 150000 NPV of short-term project 50000 =============== END FIGURE 1 NPV-PROFILES 12 10 8 NPV 6 4 2 0 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 COST OF CAPITAL eight-year project one-year project 0.16 0.17 0.18 0.17 0.18 FIGURE 2 Multiple IRR Illustration $15,000.0 $10,000.0 $5,000.0 NPV $0.0 -$5,000.0 -$10,000.0 -$15,000.0 Cost of Capital NPV Module 7 Hard Model 06/02/16 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 $0 $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: Cost of new system Installation -------Total cost -------Proceeds from old equip Tax on proceeds Removal cost (AT) -------Net cash flow, old -------Net invest. outlay ======== Annual cash flows: ========== ============ Endofproject cash flows: Salvage value ----> SV tax Total Depreciation information: Depreciable basis: Deprn Year Tax saving -------0 1 2 3 4 5 6 7 8 ---- A.T. Cost Saving ------------ Net CF ------ NPV: IRR: TV: MIRR: Payback: PI: Cum CF ------ Analysis of ShortTerm Project: Cost Year 1 CF NPV IRR MIRR Data Table for effect of cost savings Savings NPV IRR MIRR Payback PI Payback > PI 35,000 50,000 60,000 70,000 80,000 Data Table for effect of cost of capital: Cap Cost NPV IRR MIRR 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 20.0% 30.0% 40.0% 50.0% 60.0% IRR MIRR Payback PI Analysis of the Multiple IRR Project: Cap cost NPV 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% CF 0 1 2 NPV = IRR(1) = IRR(2) = the two IRRs. 190% 210% ========== =============== ========== ========== ========== ============ =============== =============== ct cash flows: ----------- information: NPV of short-term project =============== END FIGURE 1 NPV-PROFILES 12 10 8 NPV 6 4 2 0 0.08 0.09 0.1 0.11 0.12 0.13 0.14 0.15 COST OF CAPITAL eight-year project one-year project 0.16 0.17 0.18 0.17 0.18 FIGURE 2 Multiple IRR Illustration 12.0 10.0 8.0 NPV 6.0 4.0 2.0 0.0 Cost of Capital NPV