## (solution) I need help solving the spreadsheet for Case 16. Its about the

I need help solving the spreadsheet for Case 16. Its about the NEW 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

====== ========= ========= ========= ========= ========= ========= 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

Inflation rate

Unexpensed R&amp;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 ====== ========= ========= ========= ========= ========= =========

MODEL­GENERATED 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 1998­2001 are in Rows 129­159. End of Year

Land

R &amp; 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 &amp; 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:

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

Solution details:

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor