I need help solving the spreadsheet for Case 16. Its about theNEW ENGLAND SEAFOOD COMPANY.

CASE16M Student Model NEW ENGLAND SEAFOOD COMPANY Capital Budgeting with Staged Entry This case is designed to give further insight into the capital budgeting process. It focuses on the timing and relevancy of cash flows and the use of decision trees. The model calculates NPV, IRR, MIRR, payback, and discounted payback on the basis of input data for the two production stages. It also constructs a decision tree for the final decision. The spreadsheet contains two graphs: [1] a graph depicting the effect of changes in the variable cost percentage on IRR and MIRR of Stage 1, and [2] a graph depicting the effect of the chance of high demand in Stage 2 on the NPV of the whole project, using the pessimistic figures of Question 10. If you are using the student version of the model, the following cells have been blanked out: C81:C83, B84:D85, B96:G97, E131:F131, E134:F134, E136:F136, E138:F141, E143:F144, E146:F147, E149:F150, F152:F157, E159:F159, and D235:F236. 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. ====== ========= ========= ========= ========= ========= ========= All Dollar Amounts in 000's. INPUT DATA: KEY OUTPUT: Stage 1: Stage 1: Plant Costs: k 10.00% Land book value $500 NPV ($11,711) Land current val $1,000 IRR Err:523 Land salvage val $2,000 MIRR 10.98% Building cost $4,000 Payback 0.00 Building SV $3,000 Disc payback 99.99 Equipment cost $6,000 Equipment SV $500 Production Costs: Variable cost % Fixed costs NWC costs (% of sales) Sales Data: 1997 unit sales Unit sales g Sales price (ton) Other Data: Tax rate WACC Risk adjustor Inflation rate Unexpensed R&D Stage 2: 60.00% $6,000 k NPV @ t=6 25.00% IRR MIRR Payback 10,000 Disc payback 10.0% NPV @ t=0 $2,000 Expected NPV: High 10.00% $18,530 29.93% 26.70% 4.59 5.12 $10,460 Low 10.00% $4,961 17.06% 15.75% 5.11 5.53 $2,801 @t=6 $14,460 @t=0 $8,162 40.0% 10.0% Total Project: 0.0% 4.00% NPV ($2,342) $2,500 SD $4,190 CV 1.79 Stage 2: Year 1999 2000 2001 2002 2003 2004 2005 Probabilities: Enter Stage 2 Stage 2 demand: High Low NCF High Low ($12,500) ($12,500) (1,150) (1,150) (400) (400) 5,000 4,250 5,500 4,000 6,000 3,750 37,500 18,750 0.8 0.7 0.3 ====== ========= ========= ========= ========= ========= ========= MODELGENERATED DATA: Stage 1. Depreciation Cash Flows: Year Building Equip Total 1997 $128 $128 1998 128 128 1999 128 128 2000 128 2001 128 $640 $0 $384 ====== ======= ======= Stage 1. Revenues and Operating Expenses: Unit Sales Selling Sales Working Variable Fixed Year (tons) Price Revenue Capital Cost Cost 1997 10,000 $2,000 $20,000 $5,000 $12,000 $6,000 1998 11,000 $2,000 22,000 500 13,200 6,240 1999 12,100 $2,000 24,200 550 14,520 6,490 2000 2001 Stage 1. Cash Flow Worksheet: Note: Data for 19982001 are in Rows 129159. End of Year Land R & D expense Building Equipment cost Working capital Total capital 1993 ($1,000) (1,000) 1994 1995 1996 1997 $200 ($4,000) ($6,000) ($5,000) (500) ($2,000) ($4,000) ($6,000) ($5,000) ($300) ======== ======== ======== ======== ======== $20,000 12,000 6,000 128 Operating income $0 $0 $0 $0 $1,872 Tax 0 0 0 0 749 Net income $0 $0 $0 $0 $1,123 Depreciation 0 0 0 0 128 Op cash flow $0 $0 $0 $0 $1,251 Cap cash flow (2,000) (4,000) (6,000) (5,000) (300) Net cash flow ($2,000) ($4,000) ($6,000) ($5,000) $951 ======== ======== ======== ======== ======== Cash Flows, Continued: End of Year 1998 1999 2000 2001 Land R & D expense $200 $200 Building Equipment cost Working capital (550) 0 Total capital ($350) $200 ======== ======== ======== ======== Sales $22,000 $24,200 Variable cost 13,200 14,520 Fixed cost 6,240 6,490 Depreciation 128 128 Operating income $2,432 $3,062 Tax 973 1,225 Net income $1,459 $1,837 Depreciation 128 128 Op cash flow $1,587 $1,965 Cap cash flow (350) 200 Land SV Land SV tax Bldg SV Bldg SV tax Equip SV Equip SV tax Net cash flow $1,237 $2,165 ======== ======== ======== ======== Sales Variable cost Fixed cost Depreciation Stage 1. NPV, IRR, MIRR, and Payback: Risk adj WACC = 10.00% Cum CF Cum PV CF Terminal Cash PV of (for (for disc Value Year Flow Cash Flow payback) payback) (for MIRR) 1993 ($2,000) ($2,000) ($2,000) ($2,000) 1994 (4,000) (3,636) (6,000) (5,636) 1995 (6,000) (4,959) (12,000) (10,595) 1996 (5,000) (3,757) (17,000) (14,352) 1997 951 650 (16,049) (13,702) $1,393 1998 1,237 768 (14,812) (12,934) 1,647 1999 2,165 1,222 (12,646) (11,711) 2,620 2000 0 0 (12,646) (11,711) 0 2001 0 0 (12,646) (11,711) 0 NPV ($11,711) IRR Err:523 MIRR 10.98% Payback 0.00 years Disc payback 99.99 years PV of COF ($14,352) TV of CIF $5,660 Stage 2: High Demand Year 1999 2000 2001 2002 2003 2004 2005 Cum CF Cash PV of (for Flow Cash Flow payback) ($12,500) ($12,500) ($12,500) (1,150) (1,045) (13,650) (400) (331) (14,050) 5,000 3,757 (9,050) 5,500 3,757 (3,550) 6,000 3,726 2,450 37,500 21,168 39,950 Risk adj WACC = NPV @ t=6 IRR MIRR Payback Disc payback NPV @ t=0 Cum PV CF Terminal (for disc Value payback) (for MIRR) ($12,500) (13,545) (13,876) (10,119) 6,655 (6,363) 6,655 (2,637) 6,600 18,530 37,500 10.00% $18,530 29.93% 26.70% 4.59 5.12 $10,460 PV of COF ($13,876) TV of CIF $57,410 Stage 2: Low Demand: Year 1999 2000 2001 2002 2003 2004 2005 Cum CF Cash PV of (for Flow Cash Flow payback) ($12,500) ($12,500) ($12,500) (1,150) (1,045) (13,650) (400) (331) (14,050) 4,250 3,193 (9,800) 4,000 2,732 (5,800) 3,750 2,328 (2,050) 18,750 10,584 16,700 Risk adj WACC = NPV @ t=6 IRR Cum PV CF Terminal (for disc Value payback) (for MIRR) ($12,500) (13,545) (13,876) (10,683) 5,657 (7,951) 4,840 (5,622) 4,125 4,961 18,750 10.00% $4,961 17.06% PV of COF ($13,876) TV of CIF $33,372 MIRR Payback Disc payback NPV @ t=0 15.75% 5.11 5.53 $2,801 Combined Stage 2: NPV @ t=6 NPV @ t=0 $14,460 $8,162 Decision Tree: Stage 1 Yes Yes Yes Stage 2 Yes Yes No Expected NPV SD of NPV CV of NPV Joint Demand NPV Prob High Low n.a. (11,711) 0.20 0.20 ======= ProbxNPV (2,342) ($2,342) ======= ($2,342) $4,190 1.79 Sensitivity Analysis: Variable Cost Percentage (Stage 1): NPV IRR MIRR ($11,711) Err:523 10.98% 55.00% ($570) 9.15% 9.44% 57.50% (1,595) 7.59% 8.39% 60.00% (2,619) 5.97% 7.26% 62.50% (3,644) 4.31% 6.05% 65.00% (4,668) 2.59% 4.72% 67.50% (5,693) 0.81% 3.27% 70.00% (6,718) 1.03% 1.65% 72.50% (7,742) 2.95% 0.16% 75.00% (8,767) 4.93% 2.24% 77.50% (9,791) 6.98% 4.69% 80.00% (10,816) 9.12% 7.67% 82.50% (11,840) 11.34% 11.53% 85.00% (12,865) 13.66% 17.15% 87.50% (13,889) 16.06% 28.40% 90.00% (14,914) 18.56% #VALUE! 92.50% (15,938) 21.17% #VALUE! 95.00% (16,963) 23.88% #VALUE! ====== ========= ========= ========= ========= Effect of Chance of High Demand: (Question 10 CF's) ($2,342) 0.1 ($2,689) 0.2 (2,268) 0.3 (1,846) 0.4 (1,425) 0.5 (1,003) 0.6 (582) 0.7 (160) 0.8 262 0.9 683 17.15% #VALUE! ========= ========= END NEW ENGLAND SEAFOOD COMPANY Effect of Variable Cost Percentage 15.00% 10.00% IRR and MIRR 5.00% 0.00% -5.00% -10.00% -15.00% -20.00% -25.00% -30.00% -35.00% 55.00% 57.50% 60.00% 62.50% 65.00% 67.50% 70.00% 72.50% 75.00% 77.50% 80.00% 82.50% 85.00% 87.50% 90.00% 92.50% 95.00% Variable Costs as Percentage of Sales IRR MIRR NEW ENGLAND SEAFOOD COMPANY Effect of Chance of High Demand $1,000 Net Present Value $500 $0 ($500) ($1,000) ($1,500) ($2,000) ($2,500) ($3,000) 0.1 0.2 0.3 0.4 0.5 Probability of High Demand NPV Total Project 0.6 0.7 0.8 0.9