Simulation

1
Simulation
2
Overview

• Monte Carlo Simulation
• Basic concepts and history
• _at_Risk
• Probability Distributions
• Normal, Gamma, Uniform, Triangular
• Assumption and Forecast cells
• Run Preferences
• Output Analysis
• Examples
• Coin Toss, TSB Account

3
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

4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
Origins of Monte Carlo
12
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?
13
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

14
(No Transcript)
15
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 P(Xlt0.35) 0.35
16
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
17
Some Random Results
Sample means from 15 trials
18
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?
19
Simulation with _at_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

20
(No Transcript)
21

• Running an _at_Risk simulation
• Define input distribution(s)
• Define output(s)
• Simulation settings
• Start simulation

22
(No Transcript)
23
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 _at_Risk
  Define Distributions button. Choose the uniform
  distribution from the drop-down list of
  distributions.

24

• 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 Apply.

25
Note the special _at_Risk function now in cell A4.
You could have entered this function by hand, or
by using the _at_Risk Model Define Distribution
menu.
26
2. Define Output Cell

• Select cell C4. Then click on the _at_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.

27
Note the special _at_Risk function now in cell C4.
You could have entered this function by hand, or
by using the _at_Risk Model Add Output menu.
28
3. Simulation Settings

• Click on the _at_Risk Simulation Settings button.
  On the Iterations tab, specify the number of
  iterations.

29
4. Run the Simulation

• Click on the _at_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.
• When the simulation is complete, Crystal Ball
  will display the message Maximum number of
  trials reached. Click on OK.

30
Analyzing the Results

• The _at_Risk Interactive Results Window

The Insert Window toolbar
The Graph button
31
Analyzing the Results
The Histogram button
Yeah, it looks a lot like the Graph button
32
Analyzing the Results
33
Simulation Results

• The 1000-trial _at_Risk simulation gives sample mean
  profit of 0.404. The number 0.404 is only an
  estimate of the true mean profit from the
  coin-flipping game.
• The standard error of the mean is 0.0604.

34
Simulation Results

• A 95 confidence interval for the true mean
  profit is approximately
• 0.404 ? 1.96(0.0604)
• We are 95 confident that the true mean lies
  somewhere between 0.286 and 0.522.
• To get a better estimate using simulation, we
  could increase the number of simulation trials,
  and continue the simulation run.

35
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.
36
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.
37
Assume that your medical expenses in a year are
normally distributed with mean 2000 and standard
deviation 500. Build an _at_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.
38
First, we set up a spreadsheet to organize all of
the information. In particular, we want to make
sure weve 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).
39
(No Transcript)
40
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.
41
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).
42
Select cell B9 and click on the Define
Distribution button. Note that we have used cell
references for the mean and standard deviation.
43
(No Transcript)
44
Now we need to tell _at_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.
45
(No Transcript)
46
Now click on the Simulation Settings button, and
set the number of iterations.
47
(No Transcript)
48
Unfortunately, we cant 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
49
The _at_Risk Output Statistics Report (a new
worksheet created automatically
50
(No Transcript)
51
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 ?).
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
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).

56
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

57
Random Number Generator
Needs a seed to get started Each random
number becomes the seed for its successor
58
Summary

• Monte Carlo Simulation
• Basic concepts and history
• _at_Risk
• Probability Distributions
• Normal, Gamma, Uniform, Triangular
• Assumption and Forecast cells
• Run Preferences
• Output Analysis
• Examples
• Coin Toss, TSB Account