Hello, I am professor Rick Jerz and I welcome you back
to my week two reinforcement session of business analytics.
By now, you have listened to my previous two sessions
about decision theory and modeling
decision trees using Excel.
Sometimes students find it helpful
to see additional problem scenarios.
In this week's session, I first want to go back a moment
and review just a few things about week one.
Then I plan to reinforce some of this week's concepts
relative to decision trees to show you yet another
problem scenario from another author
and to see how we might solve this problem using TreePlan.
I also want to briefly introduce you
to next week's topic, probability.
Okay, we have our work cut out.
Let's get started.
My objectives for this session are to review
a few administrative items, to review a few issues
about levels of measurement, to review
one more descriptive statistics problem
using defined-name ranges, to review decision analysis
and one more decision tree model,
and to mention a few things
about our next chapter on probability.
I certainly think that you have noticed
that this week's assignments were less than
the previous week's assignments.
This was good news, a little bit of a break.
I cannot think of anything new that I need to say
about the administration of this course.
Whenever you have questions, just please let me know.
I think that by now you are getting a good feel
for the course and that you see that I take care
of students' needs and questions.
Sometimes we struggle with determining
whether a particular variable is a interval
or ratio level of measurement.
For example, what is temperature?
I found this one description on the internet
that I thought was worth sharing with you.
Take a moment and read it and it may help you
understand whether or not a particular measurement
has an absolute zero value and should be
a ratio level of measurement.
I also found this chart that I am sharing with you
that provides yet another perspective
of what you can do with nominal, ordinal,
interval, and ratio levels of measurement.
I'd like to do another problem in the area
of descriptive statistics with another big data set.
I'm going to do this to once again illustrate
the concept of using defined range names.
On the week two reinforcement web page,
I have provided you a link to a very large
race data Excel file.
This file is from one of the Des Moines Race for the Cure
road races that I administered a number of years ago.
It has almost 24,000 records in it.
You will notice that there is one column
for the participant's age.
Let's say we want to calculate some descriptive statistics
about the age of the participants.
How would we do this?
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solved this problem.
Well, this is a case where I'm not going to copy
and paste 24,000 rows into my descriptive statistics model,
but instead I can do this by changing the named range
in my descriptive statistics model for my data
and point it to this external road race file.
I changed the cell reference to M2 to M25,000.
I have just calculated all the descriptive
statistics for age.
I can quickly see that both the mean and the median age
is about 35 years old and the mode is 28.
There were more 28-year-olds in this race
than any other age.
Okay, let's turn our attention now
to doing another decision analysis problem.
We have learned this technique for quantifying decisions
and determining the best decision.
Using a decision tree modeling technique,
we are able to model the decision,
various outcomes, and probabilities.
This technique provides us the ability
to also do sensitivity analysis.
We have learned that there are different ways to use Excel,
but no way seems ideal.
If we model decision trees using Excel functions,
it is awkward to add more nodes and outcomes.
We saw that we could use TreePlan
to quickly model decision trees and outcomes,
but TreePlan does not utilize
some of the cooler features of Excel.
Let me show you what I mean.
On our week two reinforcement web page,
I have given you a link to both a Excel model
and a description of the problem that this model
is trying to address.
This problem and model are from another textbook author.
Here's the problem.
Silicon Incorporated, a semiconductor manufacturer,
is investigating the possibility of producing
and marketing a microprocessor.
Undertaking this project will require either purchasing
a sophisticated CAD system or hiring and training
several additional engineers.
The market for the product could be either
favorable or unfavorable.
Silicon Incorporated has the option of not developing
the new product at all.
In other words, do nothing.
With favorable acceptance by the market,
sales would be 25,000 processors selling for $100 each.
With unfavorable acceptance, sales would be only 8,000
processors selling for $100 each.
The cost of the CAD equipment is $500,000.
The cost of hiring and training three new engineers
is only $375,000.
However, manufacturing costs should drop from $50 each
when manufacturing without CAD to $40 each
when manufacturing with CAD.
The probability of favorable acceptance of the new
microprocessor is 40%.
The probability of unfavorable acceptance is 60%.
What should Silicon Incorporated do?
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
Here is the Excel model from this other author.
I think that we can see that this kind of problem
fits our decision tree modeling technique.
We have three alternatives.
Invest in CAD, hire engineers, or do nothing,
and each of these three alternatives
can be influenced by probabilistic events
with differing costs.
The first thing that I notice that this author
does use a technique that makes this problem
look like a decision tree, but it's a little bit
different than what we have seen in TreePlan.
There seems to be a little bit more information
being provided in this model.
We have these two additional boxes on the top
that provide information about sales estimates
and manufacturing costs per unit.
I think that these boxes were created
to allow the user to vary these costs
to do additional sensitivity analysis.
The data that we see in these boxes
drive these other cells for manufacturing cost and revenue.
You can see these relationships if you double click
on any of the red cells.
I kinda like seeing these two values,
manufacturing cost and revenue,
as we might expect the profit from any one of these branches
is revenue minus cost.
To calculate the expected value of having
either favorable or unfavorable sales,
we can take the gross revenue and multiply them
by their associated probabilities.
The probability of favorable sales is 40%
and this particular author realized
that the 40% only needs to be entered in one cell.
This one entry can drive the calculations
of all of the other probability cells.
This blue dollar value is the expected value
for this particular alternative.
To calculate net cost, we have to subtract
this expected cost from the investment cost
of the alternative.
So, for example, if we purchase CAD,
the expected profit is $888,000,
but the investment in the CAD system is $500,000
resulting in a net profit of $388,000.
We do this for the other nodes and pick the alternative
with the highest net profit.
With these initial values, the solution is to purchase CAD.
The value of this modeling technique
is that we can then do sensitivity analysis
with any of the values in the green cells
to see whether or not our decision changes.
This does not present itself as a normal Excel model.
This author made the decision tree
look like a decision tree by using Excel's Insert Shapes
feature and by not showing us the typical
gridlines and headings.
If we want to see these features, we can go
to the View ribbon and turn Gridlines and Headings back on.
Now it looks more like the Excel
that we are accustomed to seeing.
I am sharing this author's decision tree model with you
just to show you that there can be a variety
of ways of using Excel to do decision tree modeling.
Let me show you how I took this decision tree problem,
modeled it in TreePlan, and then tried to add
some of these intermediate calculations
that we see being done in this model.
You can download my TreePlan model
for this CAD problem on the week two reinforcement webpage.
I use TreePlan to create my decision tree,
but off to the right, I've included an area
where I do some additional calculations
for sales and costs.
I then modify the TreePlan branch cost
with an equation using sales and manufacturing cost.
I've also added an Excel feature, a scroll bar,
that allows me to easily change selling price.
Hey, I got a little bit fancy, right?
These sliders could be added for any variable
that I would want to change very quickly.
I just illustrated the concept for selling price.
Let me take a moment and show you how I did this.
In order to add these kinds of features,
you must turn the Developer ribbon on.
On the Mac, I go to Preferences, Toolbars,
and then turn on Developer.
On the PC, you would go to your Options toolbars
to do the same thing.
Then this Developer ribbon shows.
On this ribbon, I pick Scroll Bar
and then somewhere in my worksheet,
I drag out the shape of this scroll bar.
I right click on it, Format this Control,
set some of the values, and finally,
I point this cell link text box to the cell
that I want this scroll bar to control.
Finally, click somewhere off of this control
and then you can use it.
Okay, my point of all of this is that
this decision tree modeling technique in Excel
can be very challenging.
I hope that by now you are convinced
that this decision tree modeling technique
is a mathematically valid technique
for figuring out the best decision alternative.
However, the quality of your decision
is still based upon knowing something about
these input values and probabilities.
For example, what is the likelihood
of favorable market sales being exactly 25,000 units?
What is the likelihood of the probability
for favorable sales being .4?
Maybe it will be .5 or probably .6?
All of this probability, probably this, probably that,
leads us into our next chapter on probability,
and I have to tell you that this chapter
can be a little bit difficult for some students.
I encourage you to relax and take your time
working through this chapter on probability.
Thanks for taking some time to spend a few moments 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.