Industrial Engineering Chapter 11 Excel Problem Ecfj Pwcfj Vcj Varpwj Epw Varpw Sdpw Prpwgt Przgtepwsdpw Prpwgt Prpwgt

Unlock access to all the studying documents.

View Full Document

PROBLEM 11.44

j E(CFj) (P|F 10%,,j) PW(CFj) V(Cj) (P|F 10%,,j)2 Var(PWj)

0 -$22,500 1.00000 -$22,500 6,250,000 1.00000 6250000

1 $4,000 0.90909 $3,636 160,000 0.82645 132231.405

2 $5,000 0.82645 $4,132 250,000 0.68301 170753.364

3 $6,000 0.75131 $4,508 360,000 0.56447 203210.615

4 $7,000 0.68301 $4,781 490,000 0.46651 228588.616

5 $8,000 0.62092 $4,967 640,000 0.38554 246747.705

6 $9,000 0.56447 $5,080 810,000 0.31863 258090.962

E(PW) = $4,605 Var(PW) = 7489622.67

SD(PW) = 2736.7175

Pr(PW>0) = Pr[Z>-E(PW)/SD(PW)] Pr(PW>0) = 0.95378837

Pr(PW>0) = Pr[Z>-4,605/2736.717499]

Pr(PW>0) = Pr[Z>-1.682678]

PROBLEM 11.45

a) MARR = 0%

j E(CFj) (P|F 0%,,j) PW(CFj) V(Cj) (P|F 0%,,j)2 Var(PWj)

0 -$22,500 1.00000 -$22,500 6,250,000 1.00000 6250000

1 $4,000 1.00000 $4,000 160,000 1.00000 160000

2 $5,000 1.00000 $5,000 250,000 1.00000 250000

3 $6,000 1.00000 $6,000 360,000 1.00000 360000

4 $7,000 1.00000 $7,000 490,000 1.00000 490000

5 $8,000 1.00000 $8,000 640,000 1.00000 640000

6 $9,000 1.00000 $9,000 810,000 1.00000 810000

E(PW) = $16,500 Var(PW) = 8960000

SD(PW) = 2993.325909

Pr(PW>0) = Pr[Z>-E(PW)/SD(PW)] Pr(PW>0) = 0.999999982

Pr(PW>0) = Pr[Z>-16,500/2993.325909]

Pr(PW>0) = Pr[Z>-5.5122631]

b) MARR = 15%

j E(CFj) (P|F 15%,,j) PW(CFj) V(Cj) (P|F 15%,,j)2 Var(PWj)

0 -$22,500 1.00000 -$22,500 6,250,000 1.00000 6250000

1 $4,000 0.86957 $3,478 160,000 0.75614 120982.9868

2 $5,000 0.75614 $3,781 250,000 0.57175 142938.3114

3 $6,000 0.65752 $3,945 360,000 0.43233 155637.9345

4 $7,000 0.57175 $4,002 490,000 0.32690 160181.8692

5 $8,000 0.49718 $3,977 640,000 0.24718 158198.2119

6 $9,000 0.43233 $3,891 810,000 0.18691 151394.7917

E(PW) = $575 Var(PW) = 7139334.105

SD(PW) = 2671.953238

Pr(PW>0) = Pr[Z>-E(PW)/SD(PW)]

Pr(PW>0) = Pr[Z>-575/2671.953238]

Pr(PW>0) = Pr[Z>-0.2151984]

Pr(PW>0) = 0.58515156

PROBLEM 11.46

Determine Pr[PWA(15%) > PWB(15%)], or Pr[PWA-B(15%) > 0]

j E[CF(A)] Var[CF(A)] E[CF(B)] Var(CF(B)]

0 -$15,000 0

1 -$5,000 250000 -$8,000 562500

2 -$5,000 250000 -$8,000 562500

3 -$5,000 250000 -$8,000 562500

4 -$5,000 250000 -$8,000 562500

5 -$5,000 250000 -$8,000 562500

6 -$5,000 250000 -$8,000 562500

7 -$5,000 250000 -$8,000 562500

8 -$3,000 890000 -$8,000 562500

j E[CF(A-B)] Var[CF(A-B)] (P|F 15%,2j) PW(Var)

0 -$15,000 0 1.00000 0

1$3,000 812500 0.75614 614366.73

2$3,000 812500 0.57175 464549.512

3$3,000 812500 0.43233 351266.172

4$3,000 812500 0.32690 265607.691

5$3,000 812500 0.24718 200837.574

6$3,000 812500 0.18691 151862.06

7$3,000 812500 0.14133 114829.535

8$5,000 1452500 0.10686 155221.078

PW = -$884.23 Sum = 2318540.35

SD(PW) = 1522.67539

Pr[PWA-B(15%) > 0] = 0.28071811

The probability of the investment of $15,000 being profitable is negligible.

PROBLEM 11.47

MARR = 10%

j E[CF(A)] Var[CF(A)] E[CF(B)] Var(CF(B)]

0 -$15,000 0

1 -$5,000 250000 -$8,000 562500

2 -$5,000 250000 -$8,000 562500

3 -$5,000 250000 -$8,000 562500

4 -$5,000 250000 -$8,000 562500

8 -$3,000 890000 -$8,000 562500

j E[CF(A-B)] Var[CF(A-B)] (P|F 10%,2j) PW(Var)

2$3,000 812500 0.68301 554948.432

3$3,000 812500 0.56447 458635.068

4$3,000 812500 0.46651 379037.246

7$3,000 812500 0.26333 213956.644

8$5,000 1452500 0.21763 316106.32

PW = $1,937.79 Sum = 3166312.78

Pr[PWA-B(10%) > 0] = 0.8619245

1 -$5,000 250000 -$8,000 562500

2 -$5,000 250000 -$8,000 562500

3 -$5,000 250000 -$8,000 562500

4 -$5,000 250000 -$8,000 562500

5 -$5,000 250000 -$8,000 562500

8 -$3,000 890000 -$8,000 562500

j E[CF(A-B)] Var[CF(A-B)] (P|F 12%,2j) PW(Var)

2$3,000 812500 0.63552 516358.439

3$3,000 812500 0.50663 411637.786

4$3,000 812500 0.40388 328155.123

7$3,000 812500 0.20462 166253.598

8$5,000 1452500 0.16312 236934.214

Pr[PWA-B(12%) > 0] = 0.66511179

0.61821655

PROBLEM 11.48

N L p Cost

6$5,000 0.04 $4,100.66

6$3,000 0.08 $4,359.88

6$1,000 0.08 $4,619.09

8$5,000 0.08 $3,950.67

8$3,000 0.16 $4,125.56

8$1,000 0.16 $4,300.45

10 $5,000 0.08 $3,939.85

10 $3,000 0.16 $4,065.34

10 $1,000 0.16 $4,190.83

E(X)= $4,182.73

PROBLEM 11.49

a EXPECTED PW IS THE DISCOUNTED SUM OF EXPECTED CASH FLOWS.

VARIANCE OF PW IS THE SUM OF THE VARIANCES OF CASH FLOWS TIMES

15.00%

EOY CF (P|F i%,n) PW OF CF PW OF LIFE PROB OF LIFE

0 -$22,500 1.00000 -$22,500.00

1$6,250 0.86957 $5,434.78

4$6,250 0.57175 $3,573.46 -$4,656.39 0.10

5$6,250 0.49718 $3,107.35 -$1,549.03 0.25

6$6,250 0.43233 $2,702.05 $1,153.02 0.45

8$6,250 0.32690 $2,043.14 $5,545.76 0.05

a PROBABILITY THAT PW > 0 IS THE SUM OF THE PROBABILITIES FOR LIVES WITH PW > 0

= 0.45 + 0.15 + 0.05 = 0.65 OR 65%

THE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY

TO RUN THE @RISK SIMULATION

SIMULATION PARAMETERS: 10,000 ITERATIONS, LATIN HYPERCUBE, SEED=987654321

EOY CF CF|LIFE

0 -22500 =C32

16250 =C33

46250 =C36

56250 =IF(B37<=C$29,C37,0)

66250 =IF(B38<=C$29,C38,0)

PROBABILITY OF PW > 0 = 0.65 OR 65%

PROBLEM 11.50

a EXPECTED PW IS THE DISCOUNTED SUM OF EXPECTED CASH FLOWS.

VARIANCE OF PW IS THE SUM OF THE VARIANCES OF CASH FLOWS TIMES

THE DISCOUNT FACTORS SQUARED.

15.00% PW OF VAR OF PW

EOY E[CF] SD[CF] VAR[CF] (P|F i%,n) SAVINGS OF SAVINGS

0 -$19,700,000 – – 1.00000 -$19,700,000 –

1 $1,200,000 120,000 14,400,000,000 0.86957 $1,043,478 10,888,468,809

2 $3,600,000 240,000 57,600,000,000 0.75614 $2,722,117 32,932,986,946

SUMS -> -$1,031,532 698,678,354,037

SQ. ROOT -> 835,870

b EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF

add’l info THE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY

TO RUN THE @RISK SIMULATION

SIMULATION PARAMETERS: 10,000 ITERATIONS, LATIN HYPERCUBE, SEED=987654321

3 =RiskNormal(6000000,650000)

4 =RiskNormal(9600000,750000)

5 =RiskNormal(11000000,1080000)

=RiskOutput(“NPV(15%)”)+NPV(0.15,EOY1:EOY5)+EOY0

ESTIMATE OF MEAN = -$1,031,539

ESTIMATE OF STANDARD DEVIATION = $828,138

PROBABILITY OF PW > 0 = 0.107 0R 10.7%

c THE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY

TO RUN THE @RISK SIMULATION

SIMULATION PARAMETERS: 10,000 ITERATIONS, MONTE CARLO, SEED=987654321

EOY CF

0 -19700000

1 =RiskNormal(1200000,120000)

2 =RiskNormal(3600000,240000)

3 =RiskNormal(6000000,650000)

4 =RiskNormal(9600000,750000)

5 =RiskNormal(11000000,1080000)

=RiskOutput(“NPV(15%)”)+NPV(0.15,EOY1:EOY5)+EOY0

ESTIMATE OF MEAN = -$1,032,209

ESTIMATE OF STANDARD DEVIATION = $827,269

PROBABILITY OF PW > 0 = 0.107 OR 10.7%

PROBLEM 11.51

a EXPECTED PW IS THE DISCOUNTED SUM OF EXPECTED CASH FLOWS.

VARIANCE OF PW IS THE SUM OF THE VARIANCES OF CASH FLOWS TIMES

THE DISCOUNT FACTORS SQUARED.

EXPECTED VALUE OF A UNIFORMLY DISTRIBUTED RANDOM VARIABLE = (MIN + MAX)/2

EXPECTED VALUE = ($60,000 + $70,000)/2 = $65,000

VARIANCE OF A UNIFORMLY DISTRIBUTED RANDOM VARIABLE = ((MAX – MIN)^2)/12

VARIANCE = (($70,000 – $60,000)^2)/12 = 8,333,333

12.00%

EOY E[CF] VAR[CF] (P|F i%,n) E[PW OF CF] VAR[PW OF CF]

0 -$263,000 0 1.00000 -$263,000 0

1 $65,000 8,333,333 0.89286 $58,036 6,643,282

2 $65,000 8,333,333 0.79719 $51,818 5,295,984

3 $65,000 8,333,333 0.71178 $46,266 4,221,926

4 $65,000 8,333,333 0.63552 $41,309 3,365,693

5 $65,000 8,333,333 0.56743 $36,883 2,683,110

6 $65,000 8,333,333 0.50663 $32,931 2,138,959

SUMS -> $4,241 24,348,954

SQ. ROOT -> 4,934

EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF

THE PW EXCEED A VALUE OF INTEREST.

PROB(PW>VALUE) = NORMSDIST((E(PW) – VALUE) / SD(PW))

PROB(PW>0) = NORMSDIST((4241 – 0) / 4934)

PROB(PW>0) = 0.805

THE PROBABILITY THAT PW IS GREATER THAN ZERO IS 0.805 OR 80.5%

add’l info THE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY

TO RUN THE @RISK SIMULATION

SIMULATION PARAMETERS: 10,000 ITERATIONS, LATIN HYPERCUBE, SEED=987654321

EOY CF

0-263000

1 =RiskUniform(60000,70000)

2 =RiskUniform(60000,70000)

3 =RiskUniform(60000,70000)

4 =RiskUniform(60000,70000)

5 =RiskUniform(60000,70000)

6 =RiskUniform(60000,70000)

=RiskOutput(“NPV(12)”)+NPV(0.12,EOY1:EOY6)+EOY0

THE PROBABILITY THAT PW > 0 IS 0.796 OR 79.6%

b THE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY

TO RUN THE @RISK SIMULATION

SIMULATION PARAMETERS: 10,000 ITERATIONS, MONTE CARLO, SEED=987654321

EOY CF

0-263000

1 =RiskUniform(60000,70000)

2 =RiskUniform(60000,70000)

3 =RiskUniform(60000,70000)

4 =RiskUniform(60000,70000)

5 =RiskUniform(60000,70000)

6 =RiskUniform(60000,70000)

=RiskOutput(“NPV(12)”)+NPV(0.12,EOY1:EOY6)+EOY0

THE PROBABILITY THAT PW > 0 IS 0.797 OR 79.7%