Question Details

(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

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now