PROBLEM 11.52
a CASH FLOWS ARE EXPRESSED AS COSTS (I.E., POSITIVE VALUES REPRESENT COST NOT REVENUE)
ALTERNATIVE A WILL BE PREFERRED IF PW(COSTS A) < PW(COSTS B) OR IF PW(COSTS A-B) < 0
VARIANCE = (STANDARD DEVIATION)^2
SALVAGE VALUE IS TREATED AS A NEGATIVE CASH FLOWS SINCE CASH FLOWS ARE EXPRESSED
AS COSTS.
EXPECTED VALUE OF A DIFFERENCE IS THE DIFFERENCE OF THE EXPECTED VALUES.
VARIANCE OF A DIFFERENCE IS THE SUM OF VARIANCES.
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%
EOY E[CF A] VAR[CF A] E[CF B] VAR[CF B] E[A-B] VAR[A-B] (P|F i%,n) E[PW A-B] VAR[PW A-B]
0 $13,000 0 $0 0 $13,000 0 1.00000 $13,000 0
1 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.86957 -$2,174 614,367
2 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.75614 -$1,890 464,550
3 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.65752 -$1,644 351,266
4 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.57175 -$1,429 265,608
5 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.49718 -$1,243 200,838
6 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.43233 -$1,081 151,862
7 $5,000 250,000 $7,500 562,500 -$2,500 812,500 0.37594 -$940 114,830
8 $3,000 890,000 $7,500 562,500 -$4,500 1,452,500 0.32690 -$1,471 155,221
SUMS -> $1,128 2,318,540
SQ. ROOT -> 1,523
EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF
THE PW EXCEED A VALUE OF INTEREST.
PROB(PW OF A-B<VALUE) = 1 – NORMSDIST((E(PW A-B) – VALUE) / SD(PW A-B))
PROB(PW OF A-B<0) = 1 – NORMSDIST((1,128 – 0) / 1522.68)
PROB(PW A-B<0) = 0.229
THE PROBABILITY THAT A IS THE PREFERRED ALTERNATIVE IS 0.229 OR 22.9%
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
0
1
2
3
4
5
6
7
8
THE PROBABILITY THAT A IS THE PREFERRED ALTERNATIVE IS 0.234 OR 23.4%
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
0
1
2
ALT A
=13000
=RiskNormal(5000,500)
=RiskNormal(5000,500)
=RiskNormal(5000,500)
=RiskNormal(5000,500)
B
=RiskNormal(7500,750)
=RiskNormal(7500,750)
=RiskNormal(7500,750)
=RiskNormal(7500,750)
=C51-D51
=C52-D52
=RiskNormal(5000,500)
=RiskNormal(5000,500)
=RiskNormal(5000,500)
=RiskNormal(5000,500)-RiskNormal(2000,800)
=RiskNormal(7500,750)
=RiskNormal(7500,750)
A-B
=C46-D46
=C47-D47
=C48-D48
=C49-D49
=C50-D50
=RiskOutput(“PW(COST A-B)”)+NPV(0.15,EOY1:EOY8)+EOY0
ALT A
B
A-B
=RiskNormal(7500,750)
=RiskNormal(7500,750)
=C53-D53
=C54-D54
=13000
=C46-D46
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C47-D47
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C48-D48
3
4
5
6
7
8
THE PROBABILITY THAT A IS THE PREFERRED ALTERNATIVE IS 0.232 OR 23.2%
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C49-D49
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C50-D50
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C51-D51
=RiskNormal(5000,500)-RiskNormal(2000,800)
=RiskNormal(7500,750)
=C54-D54
=RiskOutput(“PW(COST A-B)”)+NPV(0.15,EOY1:EOY8)+EOY0
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C52-D52
=RiskNormal(5000,500)
=RiskNormal(7500,750)
=C53-D53
PROBLEM 11.53
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.
THE EXPECTED VALUES AND VARIANCES FOR EACH YEAR’S RETURNS ARE SHOWN BELOW
EXPECTED CASH FLOW = SUM OF (OUTCOME * PROBABILITY)
VARIANCE OF CASH FLOW = E[CF^2] – E[CF]^2
EOY E[CF] VAR[CF]
1 $2,080 117,600
SUMS -> $803 322,650
SQ. ROOT -> 568
EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF
THE PW EXCEED A VALUE OF INTEREST.
THE PROBABILITY THAT PW < 0 IS 0.087 OR 8.7%
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-5000
1 =RiskDiscrete({2500,1800},{0.4,0.6})
2 =RiskDiscrete({3000,2000},{0.5,0.5})
3 =RiskDiscrete({3500,2500},{0.7,0.3})
=RiskOutput(“NPV(15%)”)+NPV(15%,EOY1:EOY3)+EOY0
PROBLEM 11.55
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.
18.00%
EOY E[CF] VAR[CF] (P|F i%,n) E[PW OF CF] VAR[PW OF CF]
0 -$32,000 1,000,000 1.00000 -$32,000 1,000,000
1 $4,000 4,000,000 0.84746 $3,390 2,872,738
2 $8,000 9,000,000 0.71818 $5,745 4,642,100
3 $12,000 25,000,000 0.60863 $7,304 9,260,788
4 $12,000 36,000,000 0.51579 $6,189 9,577,374
5 $12,000 49,000,000 0.43711 $5,245 9,362,159
SUMS -> -$4,126 36,715,159
SQ. ROOT -> 6,059
MEAN OF PW = -$4,126
STANDARD DEVIATION OF PW = $6,059
b 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) = 1 – NORMSDIST((-4126 – 0) / 6059)
PROB(PW<0) = 0.752
THE PROBABILITY THAT PW IS LESS THAN ZERO IS 0.752 OR 75.2%
THE PROBABILITY THAT PW IS GREATER THAN ZERO IS = 1 – 0.752 OR 24.8%
c PROB(PW>5,000) = NORMSDIST((-4126 – 5000) / 6059)
PROB(PW>1000000) = 0.066
THE PROBABILITY THAT PW IS GREATER THAN $5,000 IS 0.066 OR 6.6%
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 =RiskNormal(-32000,1000)
1 =RiskNormal(4000,2000)
2 =RiskNormal(8000,3000)
3 =RiskNormal(12000,5000)
4 =RiskNormal(12000,6000)
5 =RiskNormal(12000,7000)
=RiskOutput(“NPV(18)”)+NPV(0.18,EOY1:EOY5)+EOY0
THE PROBABILITY THAT PW > $5,000 IS 0.064 OR 6.4%
d 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 =RiskNormal(-32000,1000)
1 =RiskNormal(4000,2000)
2 =RiskNormal(8000,3000)
3 =RiskNormal(12000,5000)
4 =RiskNormal(12000,6000)
5 =RiskNormal(12000,7000)
=RiskOutput(“NPV(18)”)+NPV(0.18,EOY1:EOY5)+EOY0
THE PROBABILITY THAT PW > $5,000 IS 0.064 OR 6.4%
PROBLEM 11.56
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%
EOY E[CF] VAR[CF] (P|F i%,n) E[PW OF CF] VAR[PW OF CF]
0 -$800,000 62,500,000,000 1.00000 -$800,000 62,500,000,000
1 $1,000,000 202,500,000,000 0.86957 $869,565 153,119,092,628
2 $1,000,000 360,000,000,000 0.75614 $756,144 205,831,168,413
SUMS -> $825,709 421,450,261,041
SQ. ROOT -> 649,192
MEAN OF PW = $825,709
STANDARD DEVIATION OF PW = $649,192
b 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) = 1 – NORMSDIST((825709 – 0) / 649192)
PROB(PW<0) = 0.102
THE PROBABILITY THAT PW IS LESS THAN ZERO IS 0.102 OR 10.2%
c PROB(PW>1,000,000) = NORMSDIST((825709 – 1000000) / 649192)
PROB(PW>1000000) = 0.394
THE PROBABILITY THAT PW IS GREATER THAN $1,000,000 IS 0.394 OR 39.4%
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
0 =RiskNormal(-800000,250000)
1 =RiskNormal(1000000,450000)
2 =RiskNormal(1000000,600000)
=RiskOutput(“NPV(15)”)+NPV(0.15,EOY1:EOY2)+EOY0
THE PROBABILITY THAT PW < 0 IS 0.101 OR 10.1%
d 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
EOY
0 =RiskNormal(-800000,250000)
1 =RiskNormal(1000000,450000)
2 =RiskNormal(1000000,600000)
=RiskOutput(“NPV(15)”)+NPV(0.15,EOY1:EOY2)+EOY0
THE PROBABILITY THAT PW < 0 IS 0.104 OR 10.4%
PROBLEM 11.57
a EXPECTED VALUE OF A DIFFERENCE (REVENUE – EXPENSE) IS THE DIFFERENCE OF EXPECTED VALUES.
VARIANCE OF A DIFFERENCE (REVENUE – EXPENSE) IS THE SUM OF VARIANCES.
EXPECTED PW IS THE DISCOUNTED SUM OF EXPECTED CASH FLOWS.
VARIANCE OF PW IS THE SUM OF THE VARIANCE OF CASH FLOWS TIMES
THE DISCOUNT FACTORS SQUARED.
20.00%
EOY E[REV] VAR[REV] E[COST] VAR[COST] E[CF] VAR[CF] (P|F i%,n) E[PW CF] VAR[PW CF]
0$0 0$5,000 0-$5,000 01.00000 -$5,000 0
1$24,000 16,000,000 $20,000 9,000,000 $4,000 25,000,000 0.83333 $3,333 17,361,111
SQ. ROOT -> 6,147
EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF
THE PW EXCEED A VALUE OF INTEREST.
PROB(PW OF CF<VALUE) = 1 – NORMSDIST((E(PW CF) – VALUE) / SD(PW CF))
b EXCEL’S NORMSDIST FUNCTION IS USED TO DETERMINE THE PROBABILITY OF
THE PW EXCEED A VALUE OF INTEREST.
PROB(PW OF CF>VALUE) = NORMSDIST((E(PW CF) – VALUE) / SD(PW CF))
cTHE FOLLOWING TABLE SHOWS THE DATA AND FUNCTIONS NECESSARY
TO RUN THE @RISK SIMULATION
SIMULATION PARAMETERS: 10,000 ITERATIONS, LATIN HYPERCUBE, SEED=987654321
=RiskNormal(24000,4000)
2 =RiskNormal(20000,3000) =REV-EXP
3 =RiskNormal(20000,3000) =REV-EXP
=RiskOutput(“PW(CF)”)+NPV(0.20,EOY1:EOY3)+EOY0
=RiskNormal(24000,4000)
=RiskNormal(24000,4000)
06000 =REV-EXP
1 =RiskNormal(20000,3000) =REV-EXP
2 =RiskNormal(20000,3000) =REV-EXP
3 =RiskNormal(20000,3000) =REV-EXP
=RiskOutput(“PW(CF)”)+NPV(0.20,EOY1:EOY3)+EOY0
THE PROBABILITY THAT THE PW IS GREATER THAN $10,000 IS 0.141 OR 14.1%
=RiskNormal(24000,4000)
=RiskNormal(24000,4000)
=RiskNormal(24000,4000)