simulation ii. operations -- prof. juran2 overview advanced simulation applications retirement...

Simulation II

2Operations -- Prof. Juran

Overview

Advanced Simulation Applications • Retirement Planning• Securities Pricing• Project Management

3

Retirement PlanningAmanda has 30 years to save for her retirement. At the beginning of each year, she puts $5000 into her retirement account. At any point in time, all of Amanda's retirement funds are tied up in the stock market. Suppose the annual return on stocks follows a normal distribution with mean 12% and standard deviation 25%. What is the probability that at the end of 30 years, Amanda will have reached her goal of having $1,000,000 for retirement? Assume that if Amanda reaches her goal before 30 years, she will stop investing.

Operations -- Prof. Juran

4

1 2 3 4 5 6 7 8 9

A B C D E F G H I J Ann. Inv. $5,000 Mean 12% Reached goal? 0 Goal $1,000,000 Stdev 25%

Year Beginning Return Ending $500,957 Max Assets 0 0 $478,876 Final Assets 1 $5,000 -18.09% $4,095 2 $9,095 10.03% $10,008 3 $15,008 -29.95% $10,513 4 $15,513 3.44% $16,047

=IF(F4>B2,1,0)

=MAX(D6:D35) =D35

=B1 =B6*(1+C6) =D6+5000 =B7*(1+C7)


5

The annual investment activities (columns A-D, beginning in row 5) actually extend down to row 35, to include 30 years of simulated returns.

The range C6:C35 will be random numbers, generated by @Risk.

We could track Amanda’s simulated investment performance either with cell F5 (simply =D35, the final amount in Amanda’s retirement account), or with F4 (the maximum amount over 30 years). Using F4 allows us to assume that she would stop investing if she ever reached $1,000,000 at any time during the 30 years, which is the assumption given in the problem statement.

Cell H1 is either 1 (she made it to $1 million) or 0 (she didn’t). Over many trials, the average of this cell will be out estimate of the probability that Amanda does accumulate $1 million. This will be an @Risk output cell.


6

We create a graph showing the amount of money in Amanda’s retirement account during the simulation. This adds little to our understanding, but it’s fun to watch.


1

234567891011121314151617181920212223242526272829303132333435

A B C D E F G H I J K L M NAnn. Inv. $5,000 Mean 12% Reached goal? 1

Goal $1,000,000 Stdev 25%

Year Beginning Return Ending $9,135,794 Max Assets0 0 $5,601,871 Final Assets1 $5,000 19.34% $5,9672 $10,967 11.26% $12,2013 $17,201 -19.29% $13,8834 $18,883 15.23% $21,7575 $26,757 -13.77% $23,0746 $28,074 -3.78% $27,0147 $32,014 13.98% $36,4898 $41,489 31.97% $54,7539 $59,753 43.37% $85,669

10 $90,669 32.90% $120,49811 $125,498 38.53% $173,85512 $178,855 -2.37% $174,61313 $179,613 17.65% $211,30714 $216,307 18.66% $256,68015 $261,680 -20.44% $208,19316 $213,193 68.38% $358,97917 $363,979 18.37% $430,84518 $435,845 24.43% $542,30119 $547,301 -6.26% $513,02420 $518,024 33.20% $690,00921 $695,009 37.91% $958,45522 $963,455 17.26% $1,129,75723 $1,134,757 21.96% $1,383,99524 $1,388,995 51.94% $2,110,37925 $2,115,379 31.55% $2,782,85226 $2,787,852 34.98% $3,763,02427 $3,768,024 13.70% $4,284,39628 $4,289,396 35.21% $5,799,71629 $5,804,716 57.39% $9,135,79430 $9,140,794 -38.72% $5,601,871

$0

$1,000,000

$2,000,000

$3,000,000

$4,000,000

$5,000,000

$6,000,000

$7,000,000

$8,000,000

$9,000,000

$10,000,000


@RISK Output Results

Name Cell Graph Min Mean Max 5% 95% Errors

Max Assets F4 $58,562 $1,482,458 $31,585,400 $250,837 $4,422,332 0

Ending Assets F5 $28,242 $1,335,891 $31,585,400 $176,964 $4,172,809 0

Reached goal? H1 0 0.4782 1 0 1 0

11

It looks like Amanda has about a 48% chance of meeting her goal of $1 million in 30 years.


12

Example: Asian Option

George Brickfield’s business is highly exposed to volatility in the cost of electricity.

He has asked his investment banker, Lisa Siegel, to propose an option whereby he can hedge himself against changes in the cost of a kilowatt hour of electricity over the next twelve months.


13

Lisa thinks that an Asian option would work nicely for George’s situation.

An Asian option is based on the average price of a kilowatt hour (or other underlying commodity) over a specified time period.


14

In this case, Lisa wants to offer George a one year Asian option with a target price of $0.059.

• If the average price per kilowatt hour over the next twelve months is greater than this target price, then Lisa will pay George the difference.

• If the average price per kilowatt hour over the next twelve months is less than this target price, then George loses the price he paid for the option (but he is happy, because he ends up buying relatively cheap electricity).


15

What is a fair price for Lisa to charge for 1 million kwh worth of these options?

Use the historical data provided and Monte Carlo simulation to arrive at a fair price.


16

Month $/kwh Month $/kwh Month $/kwh Jan-90 0.0510 May-93 0.0630 Sep-96 0.0580 Feb-90 0.0560 Jun-93 0.0710 Oct-96 0.0570 Mar-90 0.0540 Jul-93 0.0840 Nov-96 0.0550 Apr-90 0.0520 Aug-93 0.0770 Dec-96 0.0550

May-90 0.0520 Sep-93 0.0790 Jan-97 0.0520 Jun-90 0.0570 Oct-93 0.0660 Feb-97 0.0530 Jul-90 0.0670 Nov-93 0.0560 Mar-97 0.0500

Aug-90 0.0640 Dec-93 0.0690 Apr-97 0.0500 Sep-90 0.0640 Jan-94 0.0560 May-97 0.0530 Oct-90 0.0580 Feb-94 0.0540 Jun-97 0.0540 Nov-90 0.0540 Mar-94 0.0530 Jul-97 0.0540 Dec-90 0.0570 Apr-94 0.0560 Aug-97 0.0520 Jan-91 0.0590 May-94 0.0560 Sep-97 0.0500 Feb-91 0.0590 Jun-94 0.0580 Oct-97 0.0550 Mar-91 0.0560 Jul-94 0.0590 Nov-97 0.0520 Apr-91 0.0550 Aug-94 0.0530 Dec-97 0.0480






17

A n important initial step is to study the historical behavior of electricity prices. Our model will be based not on the actual prices, but on monthly percent changes in price, so we add a column to our data set that calculates the percent change in price (or return):

1234567

A B C D EMonth $/kwh Return

Jan-90 0.0510Feb-90 0.0560 0.09804Mar-90 0.0540 -0.03571Apr-90 0.0520 -0.03704

May-90 0.0520 0.00000Jun-90 0.0570 0.09615

=(B3-B2)/B2


18

N ow , w e need to think about w hat sort of theoretical probability distribution w ould do a good job of approximating the empirical distribution in these data. A useful tool for studying distributions is the histogram:

H istogram of E lectric ity R eturns

0

5

10

15

20

25

30

-0 .200 -0.175 -0.150 -0.125 -0.100 -0.075 -0.050 -0.025 0.000 0.025 0.050 0.075 0.100 0.125 0.150 0.175 0.200 0.225 0.250 0.275 0.300

M onthly Price C hange

Freq

uenc

y


19

It would appear that the percent price changes are approximately normally distributed, so we’ll use a normal distribution. We’ll use the sample mean and sample standard deviation from these data (0.001768 and 0.073462, respectively) as the mean and standard deviation of the input random variable for our model.



12345678910111213141516171819

A B C D E F G HInitial Electricity Price $0.05684Target Price $0.05900Mean monthly return 0.18%Std dev monthly return 7.35%# kwh per option 1,000,000

Month Return Price Average PriceJan -2.81% 0.05524 0.063$ Feb 6.86% 0.05903Mar 4.02% 0.06141 PayoutApr 7.25% 0.06586 4,121$

May 5.94% 0.06977Jun -1.94% 0.06842Jul -9.45% 0.06195

Aug -1.67% 0.06092Sep 6.86% 0.06510Oct -0.32% 0.06489Nov -7.72% 0.05988Dec 8.55% 0.06500

=AVERAGE(C8:C19)

=C5*MAX(E8-C2,0)

=AVERAGE(Data!C3:C121)=STDEV(Data!C3:C121)

=C12*(1+B13)


In B8:B19 we have 12 @Risk distribution cells, normally distributed with the mean and standard deviation from our sample data (C3 and C4).

In C8:C19 we use the random percent returns to calculate monthly prices, which are averaged in E8 for the whole year.

E11 calculates the payout on the option (an @Risk output cell).

The average value of E11 over many trials will be a reasonable estimate of the fair price for this option.


123456789

1011121314151617181920212223

A B C D E F G H I J K LInitial Electricity Price $0.05684Target Price $0.05900Mean monthly return 0.18%Std dev monthly return 7.35%# kwh per option 1,000,000

Month Return Price Average PriceJan -2.81% 0.05524 0.063$ Feb 6.86% 0.05903Mar 4.02% 0.06141 PayoutApr 7.25% 0.06586 4,121$

May 5.94% 0.06977Jun -1.94% 0.06842Jul -9.45% 0.06195

Aug -1.67% 0.06092Sep 6.86% 0.06510Oct -0.32% 0.06489Nov -7.72% 0.05988Dec 8.55% 0.06500

0.04

0.05

0.06

0.07

0.08

0.09

0.10

0.11

0.12

Jan

Feb

Mar Apr

May Jun Jul

Aug

Sep Oct

Nov

Dec

Pri

ce ($

/kw

h)

Month

Price


123456789

1011121314151617181920212223

A B C D E F G H I J K LInitial Electricity Price $0.05684Target Price $0.05900Mean monthly return 0.18%Std dev monthly return 7.35%# kwh per option 1,000,000

Month Return Price Average PriceJan -0.89% 0.05633 0.070$ Feb 18.89% 0.06697Mar -1.57% 0.06592 PayoutApr 5.61% 0.06962 11,472$

May -6.39% 0.06517Jun 5.01% 0.06844Jul 10.20% 0.07542

Aug -1.25% 0.07448Sep -0.95% 0.07377Oct -4.53% 0.07043Nov 19.08% 0.08387Dec -10.31% 0.07522

0.04

0.05

0.06

0.07

0.08

0.09

0.10

0.11

0.12

Jan

Feb

Mar Ap

r

May Jun Jul

Aug

Sep

Oct

Nov

Dec

Pric

e ($

/kw

h)Month

Price

=RiskOutput("Payout")+C5*MAX(E8-C2,0)

=RiskNormal($C$3,$C$4,RiskStatic($C$3))


The frequency chart indicates that the option is frequently worthless (as evidenced by the tall bar at zero), but that the payout is occasionally $20,000 or more.

To estimate a fair price, the most useful piece of the simulation output is the sample mean of approximately $2,934.46 per million kwh.

27

Beta Distribution

Parameter Description Characteristics Min Minimum Value Any number -∞ to ∞ Max Maximum Value Any number -∞ to ∞ Alpha (α) Shape Factor Must be > 0 Beta (β) Shape Factor Must be > 0

The Beta distribution is a continuous probability distribution defined by four parameters:


28

Here are sixteen different Beta distributions, all with a minimum of 0 and a maximum of 100.

β

0.5 1.0 2.0 4.0

0.5

0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75

1.0

0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0 25 50 75

2.0

0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75

α

4.0

0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75 0.0000

0.0050

0.0100

0.0150

0.0200

0.0250

0.0300

0.0350

0.0400

0.0450

0.0500

0 25 50 75


29

The Beta distribution is popular among simulation modelers because it can take on a wide variety of shapes, as shown in the graphs above.

The Beta can look similar to almost any of the important continuous distributions, including Triangular, Uniform, Exponential, Normal, Lognormal, and Gamma.

For this reason, the Beta distribution is used extensively in PERT, CPM and other project planning/control systems to describe the time to completion of a task.


30

Mean: min - max min

(i)

Standard Deviation:

2

2 min - max1

(ii)


31

The project management community has evolved approximations for the Beta distribution which allow it to be handled with three parameters, rather than four.

The three parameters are the minimum, mode, and maximum activity times (usually referred to as the optimistic, most-likely, and pessimistic activity times).

This doesn’t give exactly the same results as the mathematically-correct version, but has important practical advantages.

Most real-life managers are not comfortable talking about things like probability functions and Greek-letter parameters, but they are comfortable talking in terms of optimistic, most-likely, and pessimistic.

PERT Approximations


32

3-step Procedure

1. Get estimates for the optimistic (minimum), most-likely (mode), and pessimistic (maximum) completion times for the activity.

2. Estimate the mean and standard deviation using equations (iii) and (iv):

6

max mode 4min (iii)

6

min - max (iv)

3. Use equations (v) and (vi) to calculate shape factors that are consistent with the mean and standard deviation:

1

mean - maxmin -mean min - maxmin -mean

2 (v)

min -mean mean - max

(vi)


33

Operations Example: Project Management (PERT)

Sharon Katz is project manager in charge of laying the foundation for the new Brook Museum of Art in New Haven, Connecticut.

Liya Brook, the benefactor and namesake of the museum, wants to have the work done within 41 weeks, but Sharon wants to quote a completion time that she is 90% confident of achieving.

The contract specifies a penalty of $10,000 per week for each week the completion of the project extends beyond week 43.


34

Activity Description Optimistic Pessimistic Most-likely Predecessors A Survey Site 2 4 3 None B Excavation 9 15 12 A C Prepare Drawings 4 18 9 None D Soil Study 1 1 1 B E Prelim. Report 1 3 2 C, D F Approve Plans 1 1 1 E G Concrete Forms 5 9 6 F H Procure Steel 2 10 5 F I Order Cement 1 1 1 F J Deliver Gravel 2 5 3 G K Pour Concrete 8 14 10 H, I, J L Cure Concrete 2 2 2 K M Strength Test 2 2 2 L



1. What completion time should Sharon use, if she wants to be 90% confident?

2. What is the probability of completion by week 43?

3. Give an estimated probability distribution for the amount of penalties Sharon will have to pay.

4. What is the expected value of the penalty?

5. Which activities are most likely to be on the critical path?

36

An activity-on-arc diagram of the problem:



Model Overview123456789

1011121314151617181920212223242526272829303132333435

A B C D E F G H I J K L M N O PActivity Description Predecessors Start Node End Node Min Mode Max Simulated Time Start Time End Time Critical?

A Survey Site None 0 1 2 3 4 3 0.00 3.00 1B Excavation A 1 2 9 12 15 12 3.00 15.00 1C Prepare Drawings None 0 3 4 9 18 9 0.00 9.00 0D Soil Study B 2 3 1 1 1 1 15.00 16.00 1E Prelim. Report C, D 3 4 1 2 3 2 16.00 18.00 1F Approve Plans E 4 5 1 1 1 1 18.00 19.00 1G Concrete Forms F 5 7 5 6 9 6 19.00 25.00 1H Procure Steel F 5 6 2 5 10 5 19.00 24.00 0I Order Cement F 5 8 1 1 1 1 19.00 20.00 0

Dummy H 6 8 0 0 0 0 24.00 24.00 0J Deliver Gravel G 7 8 2 3 5 3 25.00 28.00 1K Pour Concrete H, I, J 8 9 8 10 14 10 28.00 38.00 1L Cure Concrete K 9 10 2 2 2 2 38.00 40.00 1M Strength Test L 10 11 2 2 2 2 40.00 42.00 1

Node Time Path Total Critical?0 0 A-B-D-E-F-H-K-L-M 38.00 01 3.00 C-E-F-H-K-L-M 31.00 02 15.00 A-B-D-E-F-I-K-L-M 34.00 03 16.00 C-E-F-I-K-L-M 27.00 04 18.00 A-B-D-E-F-G-J-K-L-M 42.00 15 19.00 C-E-F-G-J-K-L-M 35.00 06 24.00 Max Path 42.007 25.008 28.009 38.00

10 40.00 <= 43? Penalty11 42.00 1 -$

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

38

Here’s the section keeping track of the activity times. The numbers in column I will be @Risk distribution cells.


123456789101112131415

A B C D E F G H I J K LActivity Description Predecessors Start Node End Node Min Mode Max Simulated Time Start Time End Time Critical?



39

@RiskFor each of the random activities, we create a distribution cell, as shown here for Activity A:


40

Now we set up an area in the spreadsheet to keep track of the nodes and their times:

18192021222324252627282930

A BNode Time

0 0123456789

1011

We need to link the node times to the starting and ending times for the activities. The start time for any activity is the time at which its beginning node occurs. The end time for any activity is the start time plus the activity time.


41

Example: Activity E


123456789

101112131415161718192021222324252627282930313233

A B C D E F G H I J K L M N OActivity Description Predecessors Start Node End Node Min Mode Max Simulated Time Start Time End Time Critical?



Node Time Path Total Critical?0 0 A-B-D-E-F-H-K-L-M 38.00 01 3.00 C-E-F-H-K-L-M 31.00 02 15.00 A-B-D-E-F-I-K-L-M 34.00 03 16.00 C-E-F-I-K-L-M 27.00 04 18.00 A-B-D-E-F-G-J-K-L-M 42.00 15 19.00 C-E-F-G-J-K-L-M 35.00 06 24.00 Max Path 42.007 25.008 28.009 38.00

10 40.00 <= 43? Penalty11 42.00 1 -$

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M=MAX(K4,K5)

=VLOOKUP(D6,$A$19:$B$30,2,0) =J6+I6

42

Here’s the model after doing this for every random activity time (Activities D, F, I, L, M, and the Dummy activity have no variability):


123456789

101112131415161718192021222324252627282930313233

A B C D E F G H I J K L M N OActivity Description Predecessors Start Node End Node Min Mode Max Simulated Time Start Time End Time Critical?

A Survey Site None 0 1 2 3 4 2.4 0.00 2.40 1B Excavation A 1 2 9 12 15 11.5 2.40 13.89 1C Prepare Drawings None 0 3 4 9 18 10.6 0.00 10.58 0D Soil Study B 2 3 1 1 1 1.0 13.89 14.89 1E Prelim. Report C, D 3 4 1 2 3 1.9 14.89 16.75 1F Approve Plans E 4 5 1 1 1 1.0 16.75 17.75 1G Concrete Forms F 5 7 5 6 9 5.5 17.75 23.26 1H Procure Steel F 5 6 2 5 10 6.1 17.75 23.85 0I Order Cement F 5 8 1 1 1 1.0 17.75 18.75 0

Dummy H 6 8 0 0 0 0.0 23.85 23.85 0J Deliver Gravel G 7 8 2 3 5 2.7 23.26 26.00 1K Pour Concrete H, I, J 8 9 8 10 14 9.4 26.00 35.40 1L Cure Concrete K 9 10 2 2 2 2.0 35.40 37.40 1M Strength Test L 10 11 2 2 2 2.0 37.40 39.40 1

Node Time Path Total Critical?0 0 Path 1 A-B-D-E-F-H-K-L-M 37.24 01 2.40 Path 2 C-E-F-H-K-L-M 32.94 02 13.89 Path 3 A-B-D-E-F-I-K-L-M 32.15 03 14.89 Path 4 C-E-F-I-K-L-M 27.85 04 16.75 Path 5 A-B-D-E-F-G-J-K-L-M 39.40 15 17.75 Path 6 C-E-F-G-J-K-L-M 35.10 06 23.85 Max Path 39.407 23.268 26.009 35.40

10 37.40 <= 43? Penalty11 39.40 1 -$

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

43

Now we set up an area in the spreadsheet to track each of the paths through the network, to see which one is critical. This network happens to have six paths, so we set up a cell to add up all of the activity times for each of these paths:


12345678910111213141516171819202122232425

D E F G H I JStart Node End Node Min Mode Max Simulated Time Start Time

0 1 2 3 4 3 0.001 2 9 12 15 12 3.000 3 4 9 18 9 0.002 3 1 1 1 1 15.003 4 1 2 3 2 16.004 5 1 1 1 1 18.005 7 5 6 9 6 19.005 6 2 5 10 5 19.005 8 1 1 1 1 19.006 8 0 0 0 0 24.007 8 2 3 5 3 25.008 9 8 10 14 10 28.009 10 2 2 2 2 38.0010 11 2 2 2 2 40.00

Path Total Critical?A-B-D-E-F-H-K-L-M 38.00 0C-E-F-H-K-L-M 31.00 0A-B-D-E-F-I-K-L-M 34.00 0C-E-F-I-K-L-M 27.00 0A-B-D-E-F-G-J-K-L-M 42.00 1C-E-F-G-J-K-L-M 35.00 0Max Path 42.00

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

=SUM(I4,I6,I7,I9,I13,I14,I15)=IF(G21=$G$25,1,0)

=MAX(G19:G24)

44

Now, for each activity, we can set up a cell to indicate whether the activity was critical for any particular realization of the model.

Note that Activity H (Procure Steel, in row 9) is part of two paths (A-B-D-E-F-H-K-L-M, in row 19, and A-C-E-F-H-K-L-M, in row 20). In this example, neither of those was the critical path, so Activity H is non-critical.


12345678910111213141516171819202122232425

D E F G H I J K L M NStart Node End Node Min Mode Max Simulated Time Start Time End Time Critical?

0 1 2 3 4 3 0.00 3.00 11 2 9 12 15 12 3.00 15.00 10 3 4 9 18 9 0.00 9.00 02 3 1 1 1 1 15.00 16.00 13 4 1 2 3 2 16.00 18.00 14 5 1 1 1 1 18.00 19.00 15 7 5 6 9 6 19.00 25.00 15 6 2 5 10 5 19.00 24.00 05 8 1 1 1 1 19.00 20.00 06 8 0 0 0 0 24.00 24.00 07 8 2 3 5 3 25.00 28.00 18 9 8 10 14 10 28.00 38.00 19 10 2 2 2 2 38.00 40.00 110 11 2 2 2 2 40.00 42.00 1

Path Total Critical?A-B-D-E-F-H-K-L-M 38.00 0C-E-F-H-K-L-M 31.00 0A-B-D-E-F-I-K-L-M 34.00 0C-E-F-I-K-L-M 27.00 0A-B-D-E-F-G-J-K-L-M 42.00 1C-E-F-G-J-K-L-M 35.00 0Max Path 42.00

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

=SUM(H19,H20)

45

27282930

A B C D E8 47.509 65.83

10 67.83 <= 43?11 69.83 0

=IF(B30<43,1,0)

282930

A B C D E F G H I9 65.83

10 67.83 <= 43? Penalty11 69.83 0 268,333$

=IF(B30>43,10000*(B30-43),0)

Here’s a cell to tell whether the project was completed by week 43:

Here’s a cell to keep track of the penalty (if any) Sharon will have to pay. Note that we have assumed that the penalty applies continuously to any part of a week.


46

Now we create output cells to track the completion time of the whole project (B30) as well as the criticalities of the various paths (H19:H24) and activities (L2:L15).

We also make output cells to track whether the project took longer than 43 weeks, and what the penalty was.


123456789

101112131415161718192021222324252627282930

A B C D E F G H I J K LActivity Description Predecessors Start Node End Node Min Mode Max Simulated Time Start Time End Time Critical?

A Survey Site None 0 1 2 3 4 2.4 0.00 2.40 1B Excavation A 1 2 9 12 15 11.5 2.40 13.89 1C Prepare Drawings None 0 3 4 9 18 10.6 0.00 10.58 0D Soil Study B 2 3 1 1 1 1.0 13.89 14.89 1E Prelim. Report C, D 3 4 1 2 3 1.9 14.89 16.75 1F Approve Plans E 4 5 1 1 1 1.0 16.75 17.75 1G Concrete Forms F 5 7 5 6 9 5.5 17.75 23.26 1H Procure Steel F 5 6 2 5 10 6.1 17.75 23.85 0I Order Cement F 5 8 1 1 1 1.0 17.75 18.75 0

Dummy H 6 8 0 0 0 0.0 23.85 23.85 0J Deliver Gravel G 7 8 2 3 5 2.7 23.26 26.00 1K Pour Concrete H, I, J 8 9 8 10 14 9.4 26.00 35.40 1L Cure Concrete K 9 10 2 2 2 2.0 35.40 37.40 1M Strength Test L 10 11 2 2 2 2.0 37.40 39.40 1

Node Time Path Total Critical?0 0 Path 1 A-B-D-E-F-H-K-L-M 37.24 01 2.40 Path 2 C-E-F-H-K-L-M 32.94 02 13.89 Path 3 A-B-D-E-F-I-K-L-M 32.15 03 14.89 Path 4 C-E-F-I-K-L-M 27.85 04 16.75 Path 5 A-B-D-E-F-G-J-K-L-M 39.40 15 17.75 Path 6 C-E-F-G-J-K-L-M 35.10 06 23.85 Max Path 39.407 23.268 26.009 35.40

10 37.40 <= 43? Penalty11 39.40 1 -$

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M


1. What completion time should Sharon use, if she wants to be 90% confident?


3. Give an estimated probability distribution for the amount of penalties Sharon will have to pay.

4. What is the expected value of the penalty?


5. Which activities are most likely to be on the critical path?

Name Cell Mean A L2 0.9859 B L3 0.9859 C L4 0.0141 D L5 0.9859 E L6 1.0000 F L7 1.0000 G L8 0.9932 H L9 0.0068 I L10 0.0000 Dummy L11 0.0068 J L12 0.9932 K L13 1.0000 L L14 1.0000 M L15 1.0000

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

0

1

2

3

4

5

7

8

9

10

11

A

C

B

D

E

F

H

G J K

I

6

Dummy

L

M

Name Cell Mean Path 1 A-B-D-E-F-H-K-L-M H19 0.0066 Path 2 C-E-F-H-K-L-M H20 0.0002 Path 3 A-B-D-E-F-I-K-L-M H21 0.0000 Path 4 C-E-F-I-K-L-M H22 0.0000 Path 5 A-B-D-E-F-G-J-K-L-M H23 0.9793 Path 6 C-E-F-G-J-K-L-M H24 0.0139

52

SummaryAdvanced Simulation Applications • Retirement Planning• Securities Pricing• Project Management

Tonight?• Distribution fitting• Correlated distributions• Value-at-Risk (VaR)


simulation ii. operations -- prof. juran2 overview advanced simulation applications retirement...

Documents