Title: Simulation
1Simulation
2Overview
- 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
3Monte 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)
11Origins of Monte Carlo
12Example 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?
13Simulation 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)
15What 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
16What 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
17Some Random Results
Sample means from 15 trials
18Problems 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?
19Simulation 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)
231. 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.
25Note 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.
262. 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.
27Note 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.
283. Simulation Settings
- Click on the _at_Risk Simulation Settings button.
On the Iterations tab, specify the number of
iterations.
294. 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.
30Analyzing the Results
- The _at_Risk Interactive Results Window
The Insert Window toolbar
The Graph button
31Analyzing the Results
The Histogram button
Yeah, it looks a lot like the Graph button
32Analyzing the Results
33Simulation 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.
34Simulation 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.
35Example 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.
36As 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.
37Assume 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.
38First, 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)
40Note (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.
41Now 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).
42Select 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)
44Now 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)
46Now click on the Simulation Settings button, and
set the number of iterations.
47(No Transcript)
48Unfortunately, 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
49The _at_Risk Output Statistics Report (a new
worksheet created automatically
50(No Transcript)
51Rework 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)
55Conclusions
- 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).
56Random 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
57Random Number Generator
Needs a seed to get started Each random
number becomes the seed for its successor
58Summary
- 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