Operations -- Prof. Juran

Simulation

Overview Monte Carlo Simulation

– Basic concepts and history

@Risk

– Probability Distributions • Uniform, Normal, Gamma – Distribution and Output cells – Simulation Settings – Output Analysis

Examples

– Coin Toss, TSB Account Operations -- Prof. Juran 2

Monte Carlo Simulation

Using theoretical probability distributions to model real-world situations in which randomness is an important factor.

Differences from other spreadsheet models •No optimal solution •Explicit modeling of random variables in special cells •Many trials, all with different results •Objective function studied using statistical inference Operations -- Prof. Juran 3

Operations -- Prof. Juran 4

Operations -- Prof. Juran 5

Operations -- Prof. Juran 6

Operations -- Prof. Juran 7

Operations -- Prof. Juran 8

Operations -- Prof. Juran 9

Operations -- Prof. Juran 10

Origins of Monte Carlo

Stanislaw M. Ulam (1909 - 1984) Operations -- Prof. Juran Nicholas Metropolis (1915-1999) 11

Example: Coin Toss

Imagine a game where you flip a coin once. If you get “heads”, you win $3.00 If you get “tails”, you lose $1.00

The coin is not fair; it lands on “heads” 35% of the time What is the expected value of this game?

Operations -- Prof. Juran 12

Simulation “By Hand”

Set up a spreadsheet model Add an element of randomness • Excel built-in random number generator • Use F9 key to create repetitive iterations of the random system (“realizations”) Keep track of the results Operations -- Prof. Juran 13

A B 1 2 3 4 Random # 0.2002

Outcome Head Profit $3.00

C D E

=RAND() =IF(B2<0.35,"Head","Tail") =IF(B2<0.35,3,-1)

Operations -- Prof. Juran 14

What Does =RAND() Do?

Uniform random number between 0 and 1 Never below 0; never above 1 All values between 0 and 1 are equally likely Operations -- Prof. Juran 0.35

0.65

P(X<0.35) = 0.35

15

What Does =IF Do?

Evaluates a logical expression (true or false) Gives one result for true and a different result for false In our “coin” model, RAND and IF work together to generate heads and tails (and profits and losses) from a specific probability distribution A B 1 2 3 4 Random # 0.7438

Outcome Tail Profit -$1.00

C D E

=RAND() =IF(B2<0.35,"Head","Tail") =IF(B2<0.35,3,-1)

Operations -- Prof. Juran 16

Some Random Results

Sample means from 15 trials: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C Random # Outcome Profit 0.4619

0.4118

Tail -$1.00

Tail -$1.00

0.5815

0.9792

0.2852

0.9064

Tail -$1.00

Tail -$1.00

Head $3.00

Tail -$1.00

0.9855

0.9988

0.2206

0.0986

0.9696

0.8026

0.8189

0.7137

0.9258

Tail -$1.00

Tail -$1.00

Head Head $3.00

$3.00

Tail -$1.00

Tail -$1.00

Tail -$1.00

Tail -$1.00

Tail -$1.00

-$0.20

D E

=AVERAGE(C2:C16)

F 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B C Random # Outcome Profit 0.1979

0.9185

Head $3.00

Tail -$1.00

0.4688

0.6670

0.0902

0.3757

Tail -$1.00

Tail -$1.00

Head $3.00

Tail -$1.00

0.1492

0.4518

0.8503

0.1392

0.1924

0.0179

0.4799

0.5064

0.3051

Head Head Head Head Head $3.00

Tail -$1.00

Tail -$1.00

$3.00

$3.00

$3.00

Tail -$1.00

Tail -$1.00

$3.00

$0.87

D E

=AVERAGE(C2:C16)

F Operations -- Prof. Juran 17

Problems with this Model

Hitting F9 thousands of times is tedious Keeping track of the results (and summary statistics) is even more tedious What if we want to simulate something other than a uniform distribution between 0 and 1?

Operations -- Prof. Juran 18

Simulation with @Risk

Special cells for random variables (Distributions) Special cells for objective functions (Outputs) Simulation Settings •Number of trials •Random number seed •Sampling method Output Analysis •Studying outputs •Extracting data Operations -- Prof. Juran 19

1 2 3 4 5 6 7 A COIN.XLS

Random # 0.702

B Outcome Tail

=RAND()

C D E Profit -1

=IF(A4<0.35,3,-1) =IF(A4<0.35,"Head","Tail")

Operations -- Prof. Juran 20

Running an @Risk simulation: 1. Define input distribution(s) 2. Define output(s) 3. Simulation settings 4. Start simulation

Operations -- Prof. Juran 21

1. Define Input Distribution

First make sure there is a number in cell A4 (it cannot be blank or contain a formula). Then move the cursor to cell A4 and click on the @Risk “Define Distributions” button. Choose the uniform distribution from the list of distributions.

Operations -- Prof. Juran 22

After you select “Uniform”, a graph of the uniform distribution will appear. Set the “Min” of the uniform to 0 and the “Max” to 1. Then press “OK”.

Operations -- Prof. Juran 23

1 2 3 4 5 6 7 A COIN.XLS

Random # 0.500

B Outcome Tail C Profit -1

=RiskUniform(0,1)

D Note the special @Risk function now in cell A4. You could have entered this function by hand, or by using the @Risk – Model – Define Distribution menu.

Operations -- Prof. Juran 24

2. Define Output Cell

Select cell C4. Then click on the @Risk “Add Output” button. Give the output variable a name, such as “Profit.” The window should now look as shown below. Press “OK” to return to the spreadsheet.

Operations -- Prof. Juran 25

1 2 3 4 5 6 A COIN.XLS

Random # 0.500

B Outcome Tail C Profit -1

=RiskUniform(0,1)

D E F

=RiskOutput()+IF(A4<0.35,3,-1)

G Note the special @Risk function now in cell C4. You could have entered this function by hand, or by using the @Risk – Model – Add Output menu.

Operations -- Prof. Juran 26

3. Simulation Settings

Click on the “Settings” button. Specify the number of iterations. Operations -- Prof. Juran 27

4. Run the Simulation

Click on the @Risk “Start Simulation” icon. The “Forecast: profit” window will appear, and the number of trials simulated will show in the bottom left corner of the Excel window. Operations -- Prof. Juran 28

4. Run the Simulation

@Risk displays a graph for each output cell. Operations -- Prof. Juran 29

Analyzing the Results

Excel Reports: download and save results in Excel Browse Results: interactive graphs Summary: detailed output for each “special” cell Operations -- Prof. Juran 30

Analyzing the Results

Operations -- Prof. Juran 31

Simulation Results

The 10,000-trial @Risk simulation gives sample mean profit of $0.400. The number $0.400 is only an estimate of the true mean profit from the coin-flipping game. The standard error of the mean is 0.01908. 𝑠 𝑋 = 𝑠 𝑥 𝑛 = 1.908

10,000 = 0.01908

Operations -- Prof. Juran 32

Simulation Results

A 95% confidence interval for the true mean profit is approximately: 0.400  1.96(0.01908) We are 95% confident that the true mean lies somewhere between $0.3626 and $0.4374.

To get a better estimate using simulation, we could increase the number of simulation trials, and continue the simulation run. Operations -- Prof. Juran 33

Example 2: Tax-Saver Benefit

A TSB (Tax Saver Benefit) plan allows you to put money into an account at the beginning of the calendar year that can be used for medical expenses. This amount is not subject to federal tax — hence the phrase TSB. Operations -- Prof. Juran 34

As you pay medical expenses during the year, you are reimbursed by the administrator of the TSB until the TSB account is exhausted. From that point on, you must pay your medical expenses out of your own pocket. On the other hand, if you put more money into your TSB than the medical expenses you incur, this extra money is lost to you. Your annual salary is $50,000 and your federal income tax rate is 30%.

Operations -- Prof. Juran 35

Assume that your medical expenses in a year are normally distributed with mean $2000 and standard deviation $500. Build an @Risk model in which the output is the amount of money left to you after paying taxes, putting money in a TSB, and paying any extra medical expenses. Experiment with the amount of money put in the TSB, and identify an amount that is approximately optimal.

Operations -- Prof. Juran 36

First, we set up a spreadsheet to organize all of the information. In particular, we want to make sure we’ve identified the decision variable (how much to have taken out of our salary and put into the TSB account — here in cell B1), the output (net income — after tax, and after extra medical expenses not covered by the TSB — which we have here in cell B14), and the random variable (in this case the amount of medical expenses — here in cell B9).

Operations -- Prof. Juran 37

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 A TSB Amount (Decision Variable) Annual Salary Tax Rate After TSB Income Taxes Owed Net Income Before Medical Expenses Total Medical Expenses Amount in TSB Expenses Not Covered (Must Be Paid Out-Of-Pocket) Money Left Over in TSB (Lost) Net Income After Medical Expenses (Objective) B $ 3,000.00

C D

=B3-B1

$ 50,000.00

30% $ 47,000.00

$ 14,100.00

$ 32,900.00

$ $ $ 2,000.00

3,000.00

$ 1,000.00

=B5*B4 =B5-B6 This will be a random variable.

=B1 =MAX(B9-B10,0) =MAX(B10-B9,0)

$ 32,900.00

=B7-B11

Operations -- Prof. Juran 38

Note (this is important): We will never get a simulation model to tell us directly what is the optimal value of the decision variable (how much to have deducted from our pre-tax pay). We will try different values (here we have arbitrarily started with $3000 in cell B1) and see how the objective changes. Through educated trial-and-error, we will eventually come to some conclusion about what is the best amount of money to put into the TSB account.

Operations -- Prof. Juran 39

Now we add the element of randomness by making B9 into a distribution cell. First, enter the mean and standard deviation for the medical expenses random variable (we put them in cells B16 and B17, respectively).

16 17 Mean Standard Deviation A B $ 2,000.00

Operations -- Prof. Juran 40

Select cell B9 and click on the Define Distribution button. Note that we have used cell references for the mean and standard deviation.

Operations -- Prof. Juran 41

13 14 15 16 17 1 2 3 4 5 6 7 8 9 10 11 12 A TSB Amount (Decision Variable) Annual Salary Tax Rate After TSB Income Taxes Owed Net Income Before Medical Expenses Total Medical Expenses Amount in TSB Expenses Not Covered (Must Be Paid Out-Of-Pocket) Money Left Over in TSB (Lost) Net Income After Medical Expenses (Objective) Mean Standard Deviation B $ 3,000.00

$ 50,000.00

30% $ 47,000.00

$ 14,100.00

$ 32,900.00

$ 2,000.00

$ 3,000.00

$ $ 1,000.00

$ 32,900.00

$ 2,000.00

$ 500.00

C D

=RiskNormal(B16,B17,RiskStatic(2000))

Operations -- Prof. Juran 42

Now we need to tell @Risk to keep track of our output cell during all of our simulation runs, so we can see its mean and standard deviation over many trials. Select the net income cell B14 and click on the Add Output button.

Operations -- Prof. Juran 43

11 12 13 14 15 16 17 1 2 3 4 5 6 7 8 9 10 A TSB Amount (Decision Variable) Annual Salary Tax Rate After TSB Income Taxes Owed Net Income Before Medical Expenses Total Medical Expenses Amount in TSB Expenses Not Covered (Must Be Paid Out-Of-Pocket) Money Left Over in TSB (Lost) Net Income After Medical Expenses (Objective) Mean Standard Deviation B $ 3,000.00

$ 50,000.00

30% $ 47,000.00

$ 14,100.00

$ 32,900.00

$ 2,000.00

$ 3,000.00

$ $ 1,000.00

C D

=RiskNormal(B16,B17,RiskStatic(2000)) =RiskOutput()+B7-B11

$ 32,900.00

$ 2,000.00

$ 500.00

Operations -- Prof. Juran 44

Now click on the Simulation Settings button, and set the number of iterations.

Operations -- Prof. Juran 45

Operations -- Prof. Juran 46

Unfortunately, we can’t tell whether $3000 is the optimal amount without trying many other possible amounts. This could entail a long and tedious series of simulation runs, but fortunately it is possible to test many values at once. We set up numerous columns in the worksheet, so that we can perform simulation experiments on many possible TSB amounts simultaneously: B $ 1,000 C $ 1,250 D $ 1,500 E $ 1,750 F $ 2,000 G $ 2,250 H $ 2,500 I $ 2,750 J $ 3,000 K $ 3,250 12 13 14 15 16 17 18 1 2 3 4 5 6 7 8 9 10 11 A TSB Amount (Decision Variable) Annual Salary Tax Rate After TSB Income Taxes Owed Net Income Before Medical Expenses Total Medical Expenses Amount in TSB Expenses Not Covered (Must Be Paid Out-Of-Pocket) Money Left Over in TSB (Lost) Net Income After Medical Expenses (Objective) Mean Standard Deviation $ 50,000 30% $ 49,000 $ 14,700 $ 34,300 $ 50,000 30% $ 48,750 $ 14,625 $ 34,125 $ 50,000 30% $ 48,500 $ 14,550 $ 33,950 $ 50,000 30% $ 48,250 $ 14,475 $ 33,775 $ 50,000 30% $ 48,000 $ 14,400 $ 33,600 $ 50,000 30% $ 47,750 $ 14,325 $ 33,425 $ 50,000 30% $ 47,500 $ 14,250 $ 33,250 $ 50,000 30% $ 47,250 $ 14,175 $ 33,075 $ 50,000 30% $ 47,000 $ 14,100 $ 32,900 $ 50,000 30% $ 46,750 $ 14,025 $ 32,725 $ 2,000.00

$ 1,000.00

$ 1,000.00

$ $ 33,300.00

$ 2,000.00

$ 500.00

$ 1,250.00

$ 750.00

$ $ 33,375.00

$ 1,500.00

$ 500.00

$ $ 33,450.00

$ 1,750.00

$ 250.00

$ $ 33,525.00

$ 2,000.00

$ $ $ 33,600.00

$ 2,250.00

$ $ 250.00

$ 33,425.00

=RiskOutput("1500",A14,3)+D7-D11

$ 2,500.00

$ $ 500.00

$ 33,250.00

$ 2,750.00

$ $ 750.00

$ 33,075.00

=RiskOutput("1750",A14,4)+E7-E11

$ 3,000.00

$ $ 1,000.00

$ 32,900.00

$ 3,250.00

$ $ 1,250.00

$ 32,725.00

Operations -- Prof. Juran 47

The @Risk Output Results report (a new worksheet created automatically):

@RISK Output Results

Performed By: admin Date: Saturday, January 25, 2014 2:54:48 PM Name Cell Graph Range: Net Income After Medical Expenses (Objective) Min Mean Max 5% 95% Errors 2250 2500 2750 3000 3250 1000 1250 1500 1750 2000 G14 H14 I14 J14 K14 B14 C14 D14 E14 F14 $ 31,332.38 $ 33,295.75 $ 34,300.00 $ 32,477.41 $ 34,122.42 0 $ 31,407.38 $ 33,360.35 $ 34,125.00 $ 32,552.41 $ 34,125.00 0 $ 31,482.38 $ 33,408.34 $ 33,950.00 $ 32,627.41 $ 33,950.00 0 $ 31,557.38 $ 33,426.10 $ 33,775.00 $ 32,702.41 $ 33,775.00 0 $ 31,632.38 $ 33,400.53 $ 33,600.00 $ 32,777.41 $ 33,600.00 0 $ 31,707.38 $ 33,326.10 $ 33,425.00 $ 32,852.41 $ 33,425.00 0 $ 31,782.38 $ 33,208.34 $ 33,250.00 $ 32,927.41 $ 33,250.00 0 $ 31,857.38 $ 33,060.35 $ 33,075.00 $ 33,002.41 $ 33,075.00 0 $ 31,932.38 $ 32,895.75 $ 32,900.00 $ 32,900.00 $ 32,900.00 0 $ 32,007.38 $ 32,724.00 $ 32,725.00 $ 32,725.00 $ 32,725.00 0 Operations -- Prof. Juran 48

TSB Simulation Analysis Results

$33,500 $33,400 $33,300 $33,200 $33,100 $33,000 $32,900 $32,800 $32,700 $32,600 $32,500 $1000 $1250 $1500 $1750 $2000 $2250 $2500

Amount Put Into TSB Account

$2750 $3000 $3250 Operations -- Prof. Juran 49

Rework part a, but this time assume a gamma distribution for your annual medical expenses. Use $0 for the location parameter, $125 for the scale parameter (sometimes symbolized with β), and 16 for the shape parameter (sometimes symbolized with  ). Operations -- Prof. Juran 50

Operations -- Prof. Juran 51

Gamma Distribution

Gamma vs. Normal

Normal Distribution $0 $1,000 Operations -- Prof. Juran $2,000

Medical Expense

$3,000 $4,000 $5,000 52

TSB Simulation Analysis Results

$33,500 $33,400 $33,300 $33,200 $33,100 $33,000 $32,900 $32,800 $32,700 $32,600 $32,500 $1000 $1250 $1500 $1750 $2000 $2250 $2500

Amount Put Into TSB Account

$2750 $3000 $3250 Operations -- Prof. Juran 53

Conclusions • The best amount to put into the TSB is apparently about $1,750 per year.

• This result is robust over different distributions of medical costs.

• This result is based on sample statistics, not known population parameters.

• We have confidence in these sample statistics because of the large sample size (1,000).

Operations -- Prof. Juran 54

Random Number Generator

Built into Excel • RAND() function • Tools – Data Analysis – Random Number Generation Built into all simulation software Not really random; correctly called pseudo-random Operations -- Prof. Juran 55

Random Number Generator

Needs a “seed” to get started Each random number becomes the seed for its successor Operations -- Prof. Juran 56

Summary Monte Carlo Simulation

– Basic concepts and history

@Risk

– Probability Distributions • Uniform, Normal, Gamma – Distribution and Output cells – Simulation Settings – Output Analysis

Examples

– Coin Toss, TSB Account Operations -- Prof. Juran 57