(upbeat jazzy music)
- Hello, I am Professor Rick Jerz and I welcome you
to this session titled, Optimization and Simulation.
In this session we're going to cover these two topics,
which we have seen throughout the course.
I think you are going to really enjoy these topics.
Let's get started.
Models can be classified into two general categories:
deterministic models and probabilistic models.
A deterministic model will generally ignore
any uncertainty in its relationships and variables.
Even in problems where uncertainty exists,
if we reduce uncertain events to some determined value,
for example an average of various outcomes,
then we refer to these models as deterministic.
Unlike deterministic models,
probabilistic models consider uncertainty.
They incorporate a technical description of how variables
can change and the uncertainty is embedded
in the model's structure.
In general, probabilistic models are more difficult
to construct because uncertainty must be accommodated.
Optimization techniques can be applied to deterministic
models and simulation techniques get applied
to probabilistic models.
Let's first look at optimization techniques.
Often we might already have a deterministic model built,
and we are trying to find out how our decision might change
if some of the input variables change.
This is sometimes refereed to as sensitivity analysis.
With sensitivity analysis sometimes we are seeking
a value of interest and sometimes we are trying to find
the best, or optimal solution.
There are a variety of ways that we might use
Excel for optimization.
These include: changing values, inserting Sliders,
using what-if data tables, using Goal Seek,
and using Solver.
Some of these techniques we have already seen being used
within this course, but let's take a look at all of these
techniques knowing that some of this might be review.
To demonstrate these techniques, I will use some of our
cookie data that we generated in class.
One of the strengths of using spreadsheets to do business
modeling has always been the ability to change certain
input values and see how the result changes.
For example, in this T distribution model
I might want to explore how changing the null hypothesis
claim point will affect the p-value and our decision
to reject or not reject the null hypothesis.
If I wanted to seek out a certain p-value,
for example 5%, I could continue to change this claim point
until the p-value is observed to be this 5%.
This could take quite a few attempts at changing
this claim point.
A more effective way to manipulate this value
and observe the result would be to use Scroll Bars,
or Sliders in Microsoft Excel.
To use Scroll Bars you take on a developer or a programmer
perspective and therefore you have to remember to turn on
the Developer Ribbon in Excel.
I begin this process by first inserting a Scroll Bar.
Click on the Scroll Bar icon and then left-click
and drag in your Excel file to define the position
and shape of this Scroll Bar.
Then right-click on the Scroll Bar and choose
Format Control, this opens up its dialogue box.
You can define its current value, its minimum
and maximum value, and the incremental change.
The key to using this dialogue box
is this Cell link text box.
Click on the cell that you want to manipulate, that's it.
One problem with this control is that it only allows
for whole values, so sometimes you have to set up
an intermediate cell to change with the Scroll Bar,
and then link your real cell that you want to change
to this intermediate value.
Now click off the Scroll Bar feature to disable
as editing mode, and then use it to manipulate the value.
There may be situations where you want to change the data
and record all of the changes
by making this input value change.
The solution to this program
is to use a what-if data table.
These data tables provide the ability to compare
the results of formula varying one or two values.
You can create a one-input data table,
or a two-input data table.
Very few people know that this feature exists in Excel.
Let me demonstrate a one-input data table.
I am going to insert this data table way off to the right.
So in order to show you what I'm doing in the data table,
and the data itself, I am going to split my screen.
On the left I will look at my data, and on the right
I will insert my data table.
What I want to do is manipulate the claim point
and see the resultant p-value.
I'll set up some headings to keep track of this.
Okay, so let's say I want to vary the claim point
from say, 0.05 up to 2.0.
I'll go ahead and create these values.
Now some of you might be thinking about copying
the equation from where I calculate the p-value,
pasting it in this cell, and then duplicating it down.
But if I were to do this, I would be creating a lot of work
for myself, 'cause I would have to change all
of the relative cell references to be absolute.
This would take some effort and some time.
By using a data table I don't have to worry about this.
To begin I do have to copy the equation for the p-value
into this cell.
I probably could have avoided this step had I built
the original model with an intention of inserting
a data table.
But I didn't, so I have to go through this little bit
of extra effort.
You might notice that I left myself a blank row
in order to copy this equation right here.
Now I can't just copy and paste it,
because once again I'd have problems with cell references,
but what I can do is to click on the p-value cell,
go up to the edit area, drag through this entire equation
to select it, hit Control + C to copy this equation,
and then go over to the cell where I'm setting up my data
table, and paste this equation into the edit line.
This keeps that equation identical.
I do notice that this value matches the exact calculated
p-value on the left.
The next thing that I do is to make this into a data table.
Preselect this entire new data table area,
including the blank cell in the upper left.
Go to the data ribbon, click on the icon
for What-if Analysis and choose Data Table.
This dialogue box allows us to manipulate two variables,
but all we want to do is manipulate one variable.
And the variable that we want to manipulate
is in this column.
So what I need to do here is click on the column
input variable, and this is the odd thing,
I actually pointed to where
I am manipulating the original claim point.
Once I do this I can click on OK, and I am finished.
I end up with a table showing the p-values calculated
based upon the claim points to the left.
I can look at this data table to see if the value
that I was seeking is showing in the table,
or if there appears to be an optimal result.
If not, I can easily expand this data table
and probe a little bit deeper.
If you click on a cell where we have calculated
the p-value as a data table, you will notice that its
equation is a little bit different
than what you might expect.
In fact, you might notice that this data table technique
produces an Excel array.
Granted, this data table is not intuitive,
which is why most people don't know that it exists.
But think about what I have just showed you,
give it a try on your own, and I think you might
get the hang of it.
Goal Seek is another tool that we can use for optimization.
I have shown you how to use Goal Seek at different points
within this course, but I'll show it to you here again.
Goal Seek does simple optimization.
It allows you to seek one value by changing one other value.
It uses an iterative technique to guess at solutions
and keep guessing until the error in its guess
is some minimum value.
I have never found nay documentation about what this
minimum error is, but it seems to do a pretty good job.
Its limitations are that it may not find an exact answer,
and it only allows you to manipulate one cell
and see the result in another cell.
Let's give it a try with our model.
Let's assume that we want to find the claim point
that will produce a p-value of exactly 2%.
I go to the data ribbon, click on the What-if Analysis icon,
and select Goal Seek.
Goal Seek's dialogue box is pretty simple.
It says to set some cell
to a value by changing another cell.
What I want to do is to set the cell for the p-value
to be 2% by changing the claim point cell.
Once I set this up I hit OK, and depending upon
the speed of your computer, you might be able to watch
Excel do the iterations until it finds a claim point
that will make the p-value be exactly 2%.
Goal Seek is a simple tool that works well
in the right situations.
For a more advanced solving technique lets turn
our attention over to a tool called Solver.
I can show you how Solver works by doing what I just did
using Goal Seek.
I will try to find a claim point that makes the p-value
be exactly 2%.
Solver has been around for many years.
Let me show you the Excel 4.0 running
in my Windows 3.1 virtual PC.
Solver is found within the Formula menu.
Wow, let's return to modern times.
In most copies of Excel, Solver is found on the data ribbon
off to the right.
I'm going to click on it to open up its dialogue box.
Immediately this dialogue box will appear much more complex
than what we saw with Goal Seek.
To use it to solve our simple problem at hand,
I'm going to say set the objective cell to be D18.
I want this to be a value of 2% by changing cell D17.
When I click the Solve button, Solver will also do
some iterations and try to find a solution.
When it finds a solution it will display it to us.
I'm going to give you another lecture video
showing a very interesting application of Solver being used
for a transportation problem.
Now, let's turn our attention over to Simulation.
Simulation is a tool used to describe uncertainty
in complex situations.
Throughout this course we have been learning
how to calculate probabilities of different kinds of events.
Our models have been deterministic
and they have involved simple situations.
But how do we analyze operations that might involve
many probabilistic events?
This is where Simulation can be used.
Simulation is used for the representation of an operation,
a process, or a system that might have many interconnected
events, including events with uncertainty.
We use Simulation to understand the consequences of various
management decisions before such decisions must be made.
For example, what is the length of an amusement park line
given an arrival rate and a service rate?
What kinds of investment alternatives should be part
of a person's portfolio?
How might different pricing models affect the profits
of an organization?
Imagine a situation having uncertain inputs
with known probability distributions.
For example, the tossing of a dice.
When we toss a dice, we do not know what the result
might be, but we do know if we continue to toss a dice
over a long period of time, that the distribution
of the results, the numbers one through six,
will be uniformly distributed, meaning that these
numbers one through six are all equally likely to occur.
With some experiments, like the tossing of a dice,
we have learned that we might be able to use
the classical approach for probability and calculate
the probability distributions instead of repeating
this experiment many, many times.
But in some situations, especially when many probabilistic
experiments are combined, it's not easy to calculate
the resultant probability distributions.
This is a situation where Simulation can
be the right tool of choice.
Simulation simulates the uncertain inputs,
and their consequences, via repeated random sampling.
It shows the effect of uncertain inputs
in a statistical setting.
With Simulation, just like statistics, if you find yourself
using some advanced analytical techniques,
it might be worthwhile to invest in very specific
software products for this purpose.
And yes, you can buy Simulation tools that are designed
exclusively for doing Simulation.
But in some situations we can actually use Microsoft Excel
to do Simulations.
The Excel technique involves setting
up the problem scenario, using probability functions
and generating a series of random values.
The probability functions might include
a uniform distribution, or a normal distribution,
or a discrete distribution.
After we set up the model, we observe the model results.
I will demonstrate this technique using a scenario
of tossing two dice.
Here is my excel model for this experiment of tossing
two dice and totaling their values.
I am using the RANDBETWEEN function to simulate
the tossing of each dice.
For the result I just add up these two values.
To the right, I have frequency tables for each of the dice
and for the total.
And to the right of this, I provide frequency diagrams.
Now remember, we do know something about this experiment,
but let's pretend that we do not.
If I want to rerun this simple experiment,
I can do so by hitting the F9 key.
F9 will regenerate the random values.
If we were to run this experiment only one time,
our conclusion would be that the first dice
always ends up with the number one,
the second dice, the number five, and the result
will always be the value six.
However, remember that the tossing of a dice
contains uncertainty, and maybe these results
occurred by chance.
Maybe we should rerun this experiment,
tally the results and see how our conclusions might change.
Here, let me add some more rows.
As I add more rows I can observe the frequency diagrams
and see that our conclusions about the result is changing.
We might rerun this experiment many, many times,
and this is the concept behind Simulation.
You rerun your experiment many times to see what
the results of your experiment will be.
Let me show you what I get after 10,000 runs
of this experiment.
At this point I can conclude that the most likely result
is the number seven, and of course from classical
probability we can calculate this exact result.
By using the RANDBETWEEN function, we are simulating
a uniform probability distribution.
In Simulation modeling you always have to ask yourself,
what is the probability distribution
of our uncertain events?
For example, let's assume that our dice are loaded,
meaning that all values will not occur
the same number of times.
Let's assume that the result will follow a normal
probability distribution with the mean of three
and a standard deviation of one.
Let me substitute the normal probability distribution
function for this RANDBETWEEN function.
I will use Excel's Integer function to force the results
to be whole numbers.
After doing this for 10,000 tries, I see that the most
likely result is the value five.
But this puzzles me.
I would think that if each die had a mean of three,
that the total would be a mean of six.
Hmm, why did this happen?
Ah, it's this Integer function.
Maybe I should have used the ROUND function,
which will round these values up or down.
The Integer function always takes a value
to its lower integer value.
After making this adjustment, I see that the most likely
occurrence will be the value six.
Well we could repeat this another million times
if we wanted to, but I think that you see that the concept
behind Simulation is to rerun your experiment many times,
tally the results, and then you are in much better shape
to make your business decisions.
(upbeat jazzy music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions, or need my help,
please let me know.
(upbeat jazzy music)