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

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