- Hello, I am Professor Rick Jerz
and I welcome you back to my week 10 reinforcement session
of business analytics.
In my lecture videos, I have provided a foundation
for optimization and simulation.
In this session, I would like to review
a variety of optimization techniques
that can be used with Excel.
Then I would like to talk about simulation
by illustrating a few more examples
where simulation can be helpful.
Let's get started.
The goals for this session
are to review a few administrative items,
to review a few optimization concepts,
to review and demonstrate simulation
and to say a few words about our end of semester.
Relative to administrative items,
I just want to remind you
that exam 2 will soon be here upon us.
Remember that if you are unable to take it
on the designated day, to just let me know,
and I am willing to move it forwards or backwards
a little bit.
On Moodle you will see a study guide
explaining what is on exam 2,
and if you have any questions about it,
just post your questions on Moodle.
In my lecture video,
I shared some ideas with you
about how to use Microsoft Excel for optimization.
There were five techniques,
the first three I would call
number manipulation techniques,
and the last two I would categorize
as optimization.
The what if technique simply involves
changing numbers,
and observing the changes in the results.
One can keep changing the input values
until the desired results are reached.
Although this sounds like it could involve
a lot of work, it is actually the way
that many people do use Microsoft Excel.
This is the power of these spreadsheet programs,
even when they were first introduced in the early 1980s.
Sliders are among some tools that can be added
as a developer, to more easily manipulate input values.
What if data tables are yet another technique
that can be used when you want to create
a quick summary table that shows input values
and resultant output values.
I really believe that this is a powerful technique,
but that you will have to spend some time learning it
in order to put it to full and productive use.
Goal seek is a technique
that lets you seek a specific value
by manipulating one other value.
I have always viewed solver
as being like goal seek on steroids.
You are able to manipulate many input values,
constrain these, and do either
minimization, maximization, or seek out a certain value.
When you get a chance,
take a moment to review these techniques once again
by watching my lecture video.
Now, let's shift our discussion to simulation.
In my lecture video I show you and explain
how we could use Microsoft Excel
to simulate the tossing of either one dice or two dice.
Of course, we know that we do not need to simulate
this experiment.
We can calculate the likelihood of different outcomes,
based upon classical probability theory.
However, I did want to illustrate to you
that if we do want to use simulation, we could,
and the key behind this technique
is generating and using random values.
The Excel functions rand and randbetween
are the key functions to doing simulation in Excel.
We use these functions to model one experiment,
and then repeat this experiment many, many times
in Microsoft Excel.
In my dice model, I have run this experiment
10,000 times.
We tally the results,
create relative frequency diagrams,
which then display the probability of different outcomes.
For simple probabilistic experiments, sure,
we go ahead and calculate probabilities.
But for complex experiments,
the calculations can become challenging,
which is why we might turn to simulation
as another technique of calculating
resultant probabilities.
Let me show you a little bit more complex simulation.
Think back to the week that we covered probability,
and at the end, we explored a very interesting
probability scenario, the Monty Hall
"let's make a deal" game show.
A big prize is behind one of three doors.
A contestant picks a door.
Monty Hall exposes one of the other two doors
that does not contain the prize,
and the contestant has to decide whether or not
they stay with their original pick,
or pick the other door.
We learned that the contestant should always switch.
They have a 2/3 probability of getting the big prize
if they switch,
and a 1/3 probability if they stay
with their original pick.
The solution is bewildering to many people.
Many people believe that it doesn't matter.
I challenged you to try this on your own,
many, many times,
and prove to yourself that it is better to switch
than to stay with the same door.
What I was really challenging you to do
was to simulate this game show scenario.
I thought that it would now be fun to show you
how I would simulate this scenario using Microsoft Excel.
On my reinforcement web page,
I have provided you the beginnings of this Excel model
for this game show.
Let me explain this model to you.
To the left I have modeled the experiment,
and to the right I have a summary table.
When we open this model,
there are only two experiments that have been run.
Since this model is using the random functions,
it may look a little bit different to you
when you open it up.
Remember that the random function regenerates itself
every time there is a significant entry event in Excel.
The experiment uses a data table
so that I can easily add rows,
and the calculations in each row
will always be immediately calculated.
The first column contains the experiment count.
This looks like just the simple sequential number,
but you might want to take a moment
and explore the equation in this cell,
because this is how you can use Excel
to automatically create a row number.
You will notice that I am using the row function
in order to help me do this.
The next three columns contain door numbers,
and they use an equation to calculate these,
just so that they immediately appear
as I add more rows.
I will not have to worry about duplicating these values
downwards as I add more rows,
because this is a data table.
The fourth column is the interesting column.
It is picking a number between 1 and 3,
and this represents the door
where the prize is.
You might notice that I use the randbetween function
to do this.
The next column I have labeled
"contestant pick and stay."
This simulates that the contestant can pick
any of the three doors,
but once they do, they stay with it.
Once again, I'm using the randbetween function to do this.
The last column shows whether they win or lose
by staying with their pick.
If the door that they have picked matches the prize door,
they win.
I use Excel's if statement to show this,
and I use conditional formatting
whenever there is a win,
so that it draws our attention to it.
The summary table shows how many times
we have run this experiment,
how many times the contestant loses
by staying with their pick and not switching,
and their overall loss percent.
In other words the probability of losing
when they do not switch.
Right now, for these two experiments of mine,
the contestant who stays with the door that they pick
has won 50 percent of the time.
This is not very insightful.
The key to simulation is to run it many, many times,
and then see what happens over the long run.
To run this simulation another time,
position your cursor in the lower right hand cell,
and hit your Tab key.
You can press and hold your Tab key
to add more and more experiments,
and observe what the loss percent is.
After 10 runs of the simulation,
I see that the contestant loses 70 percent of the time
by not switching.
This means that they would have won 70 percent of the time
had they switched.
I can more quickly add rows
by highlighting some of these rows,
right clicking, and doing an insert.
Then I can easily repeat this insert
by clicking on Control Y many, many times.
After about 10,000 runs of this experiment,
which is many more times
than the show actually aired on TV,
we see that the loss percent by picking and staying
with the same door is 66 percent.
This means that the probability
of the contestant losing
when they pick a door and stay with it
is 66 percent, or 2/3.
Had they switched, they would have won 2/3 of the time.
This simulation provides the answer,
or our business strategy
for what the contestant should do.
If they switch doors,
they win 2/3 of the time.
If they do not switch, they win only 1/3 of the time.
Wow, this simulation is pretty cool,
and is a good technique to show the probability
of what happens when we do not know
how to directly calculate probabilities.
Let me move on to a more complex scenario.
The Conley Fisheries problem.
I have provided you the supplemental reading
on my resources web page for this topic,
a simple problem, operations at Conley Fisheries,
that describes the nature of this business scenario.
I am not going to read this to you,
but you should read, certainly,
the first two pages before watching this video.
The basic scenario is that Clint Conley
runs a fishing operation on the east coast.
Of course he'd like to make the greatest profit,
and to do so he has to decide where to sell his fish.
In this simple scenario,
he can sell his fish in one of two ports:
Gloucester or Rockport.
And let's say that he has hired us
to help him figure this out.
Since there are some probabilistic unknowns
in this scenario, let's model it as a simulation problem
using Microsoft Excel.
This is what I have done,
and I have provided you my model
in our group of Excel models.
There are many ways that one could approach this,
so I'm giving you my modeling approach
to solving this problem.
I have tried to structure this model
to be similar to our other statistics models,
and I have made it similar to both our dice
and our Monty Hall "let's make a deal"
simulation models.
In this case study,
we are asked some questions
that we need to address.
I show these questions at the very top of my model.
Below this, I have an input area.
I have highlighted some of the cells in green
that I am allowing you to change
just in case you want to do a what if analysis.
I think that this is always a good Excel modeling technique.
Below this I show you a results area.
To the right is where our simulation occurs.
When you open this model you will see that I have run
200 simulations of the analysis.
I really like this case study problem,
because it includes both deterministic
and probabilistic events,
and it causes me to apply some Excel modeling techniques
that we have seen in the past,
and some that we have not.
This might be our most sophisticated Excel model.
This has not come immediately,
in fact, it has taken me several years
to develop this particular model.
Well, let's get into it.
The deterministic part of this problem
occurs when Clint sells his fish in Gloucester.
Remember, deterministic means no probabilities.
We are told that Clint's boat can catch a maximum
of 3500 pounds of fish.
Gloucester is a large port, and will buy all of this.
I have designed my model to be a little bit flexible,
so sales is actually the minimum
of either what Clint catches,
or what Gloucester is willing to purchase.
We are told that the selling price for fish
is 3.25 a pound.
So it's easy to calculate gross profit.
This is just sales times selling price.
We are told that Clint's boat's expenses is 10,000 dollars.
We can therefore calculate net profit
as gross sales minus expenses.
So, if Clint sells his fish in Gloucester,
he makes 1,375 dollars a day.
To the right of the values,
I provide a reference to the paragraph from our reading
where you can find these numbers described.
And to the right of this column
I provide you some information as to whether or not
these numbers are fixed or variable.
For Gloucester, they are all provided as fixed numbers.
Again, deterministic.
Rockport is the interesting port.
Clint's daily catch remains at 3500 pounds of fish.
We are told that the demand for fish in Rockport can vary,
and it varies according to table 5.1.
The interesting thing about demand in Rockport
is that it does not follow a continuous
probability distribution.
Instead, it follows a discreet probability distribution
as shown in table 5.1.
I have graphed this distribution for the demand for fish
to the right.
What this all means to Clint,
is that when he pulls his boat into Rockport,
he does not know exactly
what he is going to be able to sell.
He might sell nothing, or he might sell his whole catch.
Clint essentially has to roll the dice
to see what he might sell.
The chart showing the probability of demand
shows that in general the demand for fish in Rockport
is 4,000 pounds or more, but not always.
How about selling price?
We are told that the selling price for fish in Rockport
follows a normal probability distribution
with a mean of 3.65 and a standard deviation of 20 cents.
What this means to Clint
is that he also doesn't know what the price
for fish will be.
However, it does look to be more favorable in Rockport
than in Gloucester.
In fact, if you do some normal probability distribution
calculations in your head,
you might notice that the 3.25 price in Gloucester
is, oh, about 2 standard deviations away
from the 3.65 price in Rockport.
What this means is that better than 95 percent of the time,
the price for fish will be higher in Rockport
than in Gloucester.
With both sales and selling price being somewhat unknown,
our challenge is, how do we calculate gross profit?
We will see this being done in our simulation.
Both expenses are the same,
and again, we will have to calculate net profit
in our simulation.
So let me move to the right of my model,
and show you how I do these calculations
in our simulation.
The general calculations are identical
to what we did for Gloucester.
We have quantity sold times price equals gross profit,
we subtract the expenses of the boat,
and we get net profit.
The difference is that some of these values
are probabilistic.
Let me explain this simulation model to you.
I do use a data table,
just as I did with the dice and the Monty Hall problem,
so that we could easily add rows to this simulation model,
meaning more simulation runs.
The first column is day number.
I use a form of the Excel's row function
to calculate this just as I did
in the "let's make a deal" simulation.
For price, we were told that the price is normally
distributed with a mean of 3.65 dollars,
and a standard deviation of 20 cents.
I use Excel's normal inverse function
to produce this value.
The normal inverse function takes three values.
The first one is a probability,
and I am using the rand function to generate this value.
The second is the mean,
and the third is the standard deviation.
I reference these last two values from our input table.
You can see the results of this equation
by looking at the results for each day number.
In the next column, I am generating a random number
that I will use to help me calculate demand.
Remember that demand is a discreet probability distribution.
I am going to use Excel's vlookup function,
which we have not really used for any purpose this semester,
to look up the random value from the left column
in the table, table 5.1,
and then return the demand from this table.
If you have never used vlookup,
you will have to dissect this technique that I am using
to really understand what it is doing.
The quantity sold is the minimum of either
what Clint's boat catches, or the demand.
This is exactly what we did for Gloucester.
Gross profit is quantity sold times the selling price,
and finally, net profit is gross profit
minus boat expenses.
As you scan down this net profit column,
you will see that there are days where
Clint's boating operation can make more money
by selling in Rockport than in Gloucester.
Remember that the profit of selling in Gloucester
is exactly 13.75.
The question here is, in the long run,
where should Clint sell his fish,
Rockport or Gloucester?
I have only run this simulation 200 times.
To run this more times,
I am going to insert some rows in this simulation table.
I stroke through part of this table,
right click, choose insert,
and insert rows by shifting these down.
Then I will quickly hit my Control Y key
to repeat this many, many times.
I have done this, and this simulation now contains
10,000 days.
This should be long enough.
Remember that you can always regenerate these values
by hitting your F9 key.
I think that I am now done explaining this model to you.
So let's return to the questions and answer these.
The first question is,
what is the shape of the probability distribution
of daily earnings from using Rockport?
To the right of the simulation I have created
a frequency table of the earnings
for these 10,000 simulations.
I then use this data to create a histogram,
which I show at the bottom of my results.
This distribution seems to have a couple peaks,
with a larger peak around 2,500 dollars.
It's not a normal distribution,
nor is it a binomial distribution,
it's just its own unique distribution.
Try hitting the F9 key several times,
and you'll see for 10,000 runs of the simulation,
the shape of this curve does not really change very much.
The next question is,
on any given day, what is the probability
of earning more using Rockport instead of Gloucester?
Down below in the results,
I calculate from the simulation
the percent of days that exceeded this 13.75 value.
I see that it is 82 percent.
In other words, 82 percent of the time,
Clint will make more money by selling his fish in Rockport
than in Gloucester.
I think that this is one of the main answers
that we are seeking.
The next question is,
on any given day, what is the probability
of losing money if they use Rockport?
Down in the results table, I do this calculation
by figuring the percent of days that we lost money
in our 10,000 run simulation.
The next question is,
what is the expected daily earnings from using Rockport?
I calculate this in the results,
as just simply the average of all 10,000 calculated
net profits from the simulation.
And the last question is,
what is the standard deviation
of the daily earnings from using Rockport?
Again, in the results area,
I do this calculation as a standard deviation
of our 10,000 net profits.
I think, based upon all of this information,
Clint should sell in Rockport, in spite of the risk.
I hope that this problem has given you an appreciation
of what simulation can do.
In our assignments for this week,
I'm challenging you to use this particular model,
and to do some what if analysis
to see how the results might change.
I am not expecting you to create these simulations
on your own,
but if you do want to give it a try,
I have given you some other examples in our reading.
Next week, I will be providing a
end of semester video for you.
On this week's reinforcement web page,
I show you that I will be posting
our final Excel model on my website,
and make it available to you for life,
under this particular name.
Also, you might see my own end of semester survey
on Moodle show up very soon.
Thanks for spending some of your time with me.
I hope you have enjoyed what I have shared with you.
If you ever have any questions, or need my help,
please let me know.