I need help solving the word document to this case (part 12a). Can anyone please help?

CASE12AM Student Version 5/19/95 CRANFIELD INC. (PART I) Cash Flow Estimation and Capital Budgeting Analysis This case focuses on capital budgeting decision criteria and cash flow estimation. It begins with such basic concepts as incremental cash flows, sunk costs, opportunity costs, and externalities, and it then moves to inflation adjustments. The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, and Payback for the lite orange juice project. The model includes a data table which can be used for sensitivity analysis. We created a graph which shows how NPV varies with changes in inflation. You can click on the NPV tab to view the graph. A more extensive risk analysis is conducted in the model for Case 13A. If you are using the student version of the model, the following cells have been blanked out: G56, F58:F59, E72, F75, F77, E81, G85, F91, C95, and F97. 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: Initial Investment: Equipment cost $400,000 NPV $0 Freight $20,000 IRR 10.7% Installation $40,000 MIRR 9.8% Change in NWC $15,000 Payback 3.2 Operating Flows and Inflation Rates: Unit sales 400,000 t=0 sales price $2.00 Fixed oper costs $100,000 t=0 VC per unit $1.25 Price inflation 0.0% Cost inflation 0.0% SV, Taxes, and C of C: Salvage value $50,000 Tax rate 40% Cost of capital 10% Cannibalization of Other Projects: Revenue Loss Cost Reduction Net Cannibalization $40,000 $20,000 $20,000 ====== ========= ========= ========== ========== ========== ========== |:: MODELGENERATED DATA: CRANFIELD INC. (PART I) Net Investment Outlay: Equipment cost $400,000 Freight 20,000 Installation 40,000 Change in NWC 15,000 $475,000 ======== Cash Flows: Unit price Unit sales Year 0 $2.00 Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow Depreciation Schedule: Basis: $460,000 MACRS Dep. End of Year Year Factor Expense Book Value 1 33% $151,800 2 45% 207,000 (207,000) 3 15% (207,000) 4 7% (207,000) 100% $358,800 ====== ======== Year 1 $2.00 400,000 $800,000 100,000 500,000 600,000 151,800 20,000 $28,200 11,280 $16,920 151,800 $168,720 Year 2 $2.00 400,000 $800,000 100,000 100,000 207,000 20,000 $473,000 189,200 $283,800 $283,800 Year 3 $2.00 400,000 $800,000 100,000 500,000 600,000 0 0 $0 0 $0 Salvage value SV tax Recovery of NWC Termination CF Project NCF ### ======== Decision Measures: NPV IRR 10.7% TV 690,964 MIRR 9.8% Payback 3.2 $168,720 ======== $283,800 ======== ======== Cumulative Cash Flows: 0 ($475,000) 1 (306,280) 2 3 (22,480) 4 100,520 Year 4 $2.00 400,000 $800,000 100,000 500,000 600,000 0 20,000 $180,000 72,000 $108,000 0 $108,000 0 15,000 $15,000 $123,000 ======== Sensitivity Analysis: Changes in NPV Due to Changes in Price Inflation and Unit Sales (Uses Data Table 2 to perform the calculations) Price Unit Sales Inflation: 0 200,000 300,000 400,000 500,000 600,000 700,000 0.00% ### ($91,400) $51,244 $193,888 $336,532 $479,176 1.00% (215,755) (63,967) 87,821 239,609 391,397 543,185 2.00% (197,117) (36,010) 125,097 286,204 447,311 608,418 3.00% (178,125) (7,522) 163,082 333,685 504,288 674,892 4.00% (158,772) 21,507 201,786 382,066 562,345 742,625 5.00% (139,055) 51,083 241,221 431,359 621,497 811,636 6.00% (118,968) 81,214 281,396 481,578 681,760 881,942 7.00% (98,505) 111,908 322,322 532,735 743,148 953,561 8.00% (77,661) 143,174 364,009 584,844 805,679 1,026,514 ====== ========= ========= ========== ========== ========== ========== END NPV With Differing Inflation and Unit Sales $1,200,000 $1,000,000 $800,000 NPV $600,000 $400,000 $200,000 $0 ($200,000) ($400,000) 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% Sales Price Inflation (% ) 200,000 400,000 700,000 6.00% 7.00% 8.00% CASE12AM Student Version 5/19/95 CRANFIELD INC. (PART I) Cash Flow Estimation and Capital Budgeting Analysis This case focuses on capital budgeting decision criteria and cash flow estimation. It begins with such basic concepts as incremental cash flows, sunk costs, opportunity costs, and externalities, and it then moves to inflation adjustments. The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, and Payback for the lite orange juice project. The model includes a data table which can be used for sensitivity analysis. We created a graph which shows how NPV varies with changes in inflation. You can click on the NPV tab to view the graph. A more extensive risk analysis is conducted in the model for Case 13A. If you are using the student version of the model, the following cells have been blanked out: G56, F58:F59, E72, F75, F77, E81, G85, F91, C95, and F97. 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: Initial Investment: Equipment cost $400,000 Freight $20,000 Installation $40,000 Change in NWC $15,000 Operating Flows and Inflation Rates: Unit sales 400,000 t=0 sales price $2.00 Fixed oper costs $100,000 t=0 VC per unit $1.25 Price inflation 0.0% Cost inflation 0.0% ========= ========= ========= KEY OUTPUT: NPV IRR MIRR Payback $51,244 15.0% 12.9% 2.9 Cannibalization of Other Projects: SV, Taxes, and C of C: Salvage value Tax rate Cost of capital $50,000 40% 10% Revenue Loss Cost Reduction Net Cannibalization $40,000 $20,000 $20,000 ====== ========= ========= ========= ========= ========= ========= |:: MODEL-GENERATED DATA: CRANFIELD INC. (PART I) Net Investment Outlay: Equipment cost $400,000 Freight 20,000 Installation 40,000 Change in NWC 15,000 -------$475,000 ======== Cash Flows: Unit price Unit sales Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow Salvage value SV tax Recovery of NWC Termination CF Year 0 -------$2.00 Depreciation Schedule: Basis: MACRS Dep. Year Factor Expense -----------------1 33% $151,800 2 45% $207,000 3 15% $69,000 4 7% $32,200 ------------100% $460,000 ====== ======== Year 1 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 151,800 20,000 -------$28,200 11,280 -------$16,920 151,800 -------$168,720 -------- Year 2 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 207,000 20,000 -------($27,000) (10,800) -------($16,200) 207,000 -------$190,800 -------- Year 3 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 69,000 20,000 -------$111,000 44,400 -------$66,600 69,000 -------$135,600 -------- $460,000 End of Year Book Value ----------308,200 101,200 32,200 0 Year 4 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 32,200 20,000 -------$147,800 59,120 -------$88,680 32,200 -------$120,880 -------$50,000 20,000 15,000 -------$45,000 Project NCF -------($475,000) ======== Decision Measures: NPV $51,244.33 IRR 15.0% TV 770,474 MIRR 12.9% Payback 2.9 -------$168,720 ======== -------$190,800 ======== -------$135,600 ======== Cumulative Cash Flows: 0 ($475,000) 1 (306,280) 2 (115,480) 3 20,120 4 186,000 -------$165,880 ======== Sensitivity Analysis: Changes in NPV Due to Changes in Price Inflation and Unit Sales (Uses Data Table 2 to perform the calculations) Price Unit Sales Inflation: ---------------------- ---------------- ---------------- ---------------- ---------------51244.3 200,000 300,000 400,000 500,000 600,000 700,000 0.00% ($234,044) ($91,400) $51,244 $193,888 $336,532 $479,176 1.00% (215,755) (63,967) 87,821 239,609 391,397 543,185 2.00% (197,117) (36,010) 125,097 286,204 447,311 608,418 3.00% (178,125) (7,522) 163,082 333,685 504,288 674,892 4.00% (158,772) 21,507 201,786 382,066 562,345 742,625 5.00% (139,055) 51,083 241,221 431,359 621,497 811,636 6.00% (118,968) 81,214 281,396 481,578 681,760 881,942 7.00% (98,505) 111,908 322,322 532,735 743,148 953,561 8.00% (77,661) 143,174 364,009 584,844 805,679 1,026,514 ====== ========= ========= ========= ========= ========= ========= END NPV With Differing Inflation and Unit Sales $1,200,000 $1,000,000 $800,000 608,418 $600,000 NPV $400,000 163,082 $200,000 $0 (178,125) ($200,000) ($400,000) 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% Sales Price Inflation (%) 200,000 400,000 700,000 6.00% 7.00% 8.00% CASE12AM Student Version 5/19/95 CRANFIELD INC. (PART I) Cash Flow Estimation and Capital Budgeting Analysis This case focuses on capital budgeting decision criteria and cash flow estimation. It begins with such basic concepts as incremental cash flows, sunk costs, opportunity costs, and externalities, and it then moves to inflation adjustments. The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, and Payback for the lite orange juice project. The model includes a data table which can be used for sensitivity analysis. We created a graph which shows how NPV varies with changes in inflation. You can click on the NPV tab to view the graph. A more extensive risk analysis is conducted in the model for Case 13A. If you are using the student version of the model, the following cells have been blanked out: G56, F58:F59, E72, F75, F77, E81, G85, F91, C95, and F97. 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: Initial Investment: Equipment cost $400,000 NPV $51,244 Freight $20,000 IRR 15.0% Installation $40,000 MIRR 12.9% Change in NWC $15,000 Payback 2.9 Operating Flows and Inflation Rates: Unit sales 400,000 t=0 sales price $2.00 Fixed oper costs $100,000 t=0 VC per unit $1.25 Price inflation 0.0% Cost inflation 0.0% Cannibalization of Other Projects: SV, Taxes, and C of C: Salvage value Tax rate Cost of capital $50,000 40% 10% Revenue Loss Cost Reduction Net Cannibalization $40,000 $20,000 $20,000 ====== ========= =========== ========= ========= ========= ========= |:: MODEL-GENERATED DATA: CRANFIELD INC. (PART I) Net Investment Outlay: Equipment cost Freight Installation Change in NWC Cash Flows: Unit price Unit sales Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow Salvage value SV tax Recovery of NWC Termination CF $400,000 20,000 40,000 15,000 -------$475,000 ======== Year 0 -------$2.00 Depreciation Schedule: Basis: MACRS Dep. Year Factor Expense -----------------1 33% $151,800 2 45% $207,000 3 15% $69,000 4 7% $32,200 ------------100% $460,000 ====== ======== Year 1 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 151,800 20,000 -------$28,200 11,280 -------$16,920 151,800 -------$168,720 -------- Year 2 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 207,000 20,000 -------($27,000) (10,800) -------($16,200) 207,000 -------$190,800 -------- Year 3 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 69,000 20,000 -------$111,000 44,400 -------$66,600 69,000 -------$135,600 -------- $460,000 End of Year Book Value ----------308,200 101,200 32,200 0 Year 4 -------$2.00 400,000 -------$800,000 100,000 500,000 600,000 32,200 20,000 -------$147,800 59,120 -------$88,680 32,200 -------$120,880 -------$50,000 20,000 15,000 -------$45,000 Project NCF -------($475,000) ======== Decision Measures: NPV $51,244.33 IRR 15.0% TV 770,474 MIRR 12.9% Payback 2.9 -------$168,720 ======== -------$190,800 ======== -------$135,600 ======== Cumulative Cash Flows: 0 ($475,000) 1 (306,280) 2 (115,480) 3 20,120 4 186,000 -------$165,880 ======== Sensitivity Analysis: Changes in NPV Due to Changes in Price Inflation and Unit Sales (Uses Data Table 2 to perform the calculations) Price Unit Sales Inflation: -------------------------- ---------------- ---------------- ---------------- ---------------51244.3 200,000 300,000 400,000 500,000 600,000 700,000 0.00% ($234,044) ($91,400) $51,244 $193,888 $336,532 $479,176 1.00% (215,755) (63,967) 87,821 239,609 391,397 543,185 2.00% (197,117) (36,010) 125,097 286,204 447,311 608,418 3.00% (178,125) (7,522) 163,082 333,685 504,288 674,892 4.00% (158,772) 21,507 201,786 382,066 562,345 742,625 5.00% (139,055) 51,083 241,221 431,359 621,497 811,636 6.00% (118,968) 81,214 281,396 481,578 681,760 881,942 7.00% (98,505) 111,908 322,322 532,735 743,148 953,561 8.00% (77,661) 143,174 364,009 584,844 805,679 1,026,514 ====== ========= =========== ========= ========= ========= ========= Cash Flows: Unit price Unit sales Year 0 -------$2.00 Year 1 -------$2.10 400,000 -------$840,000 100,000 500,000 630,000 151,800 20,000 -------$38,200 15,280 -------$22,920 151,800 -------$174,720 -------- Year 2 -------$2.21 400,000 -------$882,000 100,000 500,000 661,500 207,000 20,000 -------($6,500) (2,600) -------($3,900) 207,000 -------$203,100 -------- Year 3 -------$2.32 400,000 -------$926,100 100,000 500,000 694,575 69,000 20,000 -------$142,525 57,010 -------$85,515 69,000 -------$154,515 -------- -------($475,000) ======== -------$174,720 ======== -------$203,100 ======== -------$154,515 ======== Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow Salvage value SV tax Recovery of NWC Termination CF Project NCF Decision Measures: NPV $61,820.83 IRR 16.7% TV 679,076 MIRR 9.3% Payback 2.6 Cash Flows: Year END Year 4 -------$2.43 400,000 -------$972,405 100,000 500,000 729,304 32,200 20,000 -------$190,901 76,361 -------$114,541 32,200 -------$146,741 -------$0 0 0 -------$0 -------$146,741 ======== Cumulative Cash Flows: 0 ($475,000) 1 (300,280) 2 (97,180) 3 57,335 4 204,076 Year Year Year Year 0 Unit price Unit sales -------$1.85 Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow 1 -------$1.94 400,000 -------$774,900 100,000 500,000 612,000 151,800 20,000 -------($8,900) 15,280 -------($24,180) 151,800 -------$127,620 -------- 2 -------$2.03 400,000 -------$813,645 100,000 500,000 624,240 207,000 20,000 -------($37,595) (2,600) -------($34,995) 207,000 -------$172,005 -------- 3 -------$2.14 400,000 -------$854,327 100,000 500,000 636,725 69,000 20,000 -------$128,602 57,010 -------$71,592 69,000 -------$140,592 -------- -------$127,620 ======== -------$172,005 ======== -------$140,592 ======== Salvage value SV tax Recovery of NWC Termination CF Project NCF -------($475,000) ======== Decision Measures: NPV ($7,192.58) IRR 9.2% TV 591,441 MIRR 5.6% Payback 3.2 Cumulative Cash Flows: 0 ($475,000) 1 (347,380) 2 (175,375) 3 (34,783) 4 116,441 4 -------$2.24 400,000 -------$897,044 100,000 500,000 649,459 32,200 20,000 -------$195,384 76,361 -------$119,024 32,200 -------$151,224 -------$0 0 0 -------$0 -------$151,224 ======== NPV With Differing Inflation and Unit Sales $1,200,000 $1,000,000 $800,000 608,418 $600,000 NPV $400,000 163,082 $200,000 $0 (178,125) ($200,000) ($400,000) 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% Sales Price Inflation (%) 200,000 400,000 700,000 6.00% 7.00% 8.00%