(upbeat music)
- Hello.
I'm Professor Rick Jerz,
and I welcome you back to another session
of business analytics.
In our previous session,
we learned about how we
can model business decision alternatives,
how probabilities and risk might be involved,
and how to analyze decision alternatives
using the decision tree modeling technique.
In this session, I'm going to show you how we can use
Microsoft Excel to create decision trees
to analyze these business alternatives
and to calculate expected payoffs.
Decision tree analysis is not a feature
that is inherently built into Microsoft Excel.
In the first part of this session,
I will show you how one might try to use Excel
to build a decision tree from scratch.
One could do this, but it would be difficult
when many alternatives with many outcomes are involved.
So I am going to show you a special utility called TreePlan
that can be used to streamline decision tree creation
and analysis within Microsoft Excel.
I think you are going to really enjoy this session.
Let's get started.
In my previous session, we learned to use the decision
tree modeling technique to help make better business decisions.
I provided you the steps to constructing decisions trees,
and I provided you the methodology of how to solve decision trees, shown here.
We had to do some calculations to solve this decision
tree for Bill Sampras's problem.
I didn't do these calculations in my previous session, but I did promise you
that we would see if we could use Microsoft Excel to do these calculations.
The supplemental reading shows how one can use Excel
to create a table and do some calculations.
Quite honestly, I don't like the author's approach
because it doesn't follow the logic of constructing
and solving a decision tree. So I am going to show you my technique
of how I would model a decision tree using Excel.
You should watch me
demonstrating my techique, but don't worry about doing it yourself.
Just watch.
You are not responsible for using my method,
but do take note of some Excel functions that I use.
However, after I show you my technique, I will show you a special Excel add-in
technique using a product called TreePlan, which I want you to do yourself.
So follow along with me in the second half of this session.
To construct a decision tree, we need to create decision nodes and event nodes.
These nodes will have various branches coming off of them.
Let's begin by representing a decision node.
Remember that a decision node has various alternatives emanating from it.
Begin representing a decision node by simply listing the alternatives.
For Bill's decision, his first decision node had two alternatives,
to "Accept John's Offer," or to "Reject John's Offer."
Recall that the branch for accepting John's offer had a known value.
It was $12,000.
I can format this cell as green, meaning that we know it.
The reject John's offer was unknown.
We cannot put any value into this cell until we calculate it.
I will color this cell orange, meaning we don't know it.
We also know that the rule is to pick
the branch with the highest expected value for decision nodes.
This particular value goes to the beginning of this node.
This type of decision is relatively straightforward to represent in Excel.
In words, we might say something like this.
If we accept John's offer, it is worth $12000.
If this value is greater than rejecting John's offer,
bring it forward as the highest expected value.
If not, bring forward the expected value for rejecting John's offer.
This kind of English-like wording, using the word "if" can be modeled in Excel.
Excel has a function called "IF()," and the IF() function
is one of the most powerful functions in Excel.
It helps Excel models "make decision!"
We will see this IF() function in action a lot.
I can type "equal if D8 is greater than D9, then
D8, D9." The result is 12,000.
If D9 happens to be 30,000, this value is moved forward.
But for this particular kind of decision, another function is even more useful.
It is called the MAX() function, which represents the word "maximum."
We want to pick the branch with the maximum value.
In Excel, I make this cell equal to the MAX() of,
and I like to use the pointing technique in Excel.
Using the cursor, I point to the value and accept John's offer, add a comma,
and then point to the value in reject John's offer.
Right now, the maximum value is this $12,000.
But that's only right now, because we do not know
what the reject John's offer value is.
We have to wait until we evaluate the downstream nodes.
This technique that I have just shown
you is a good way of modeling any decision node.
Realize that some decision nodes might have, oh,
three, four, 10 different alternatives, so the MAX() function becomes
the more efficient way of modeling this kind of decision node.
The IF() function would get awkward to use.
Event nodes are a little more challenging to model in Excel.
This is because event nodes branches also have probability associated with them.
We must calculate the event node's expected
value as a weighted average for all branches.
The modeling technique that I use for event
nodes is very similar to decision nodes.
First, list all the various branches.
For example, looking at the event node for the offer from Vanessa,
there are two branches.
One is "Offer from Vanessa," and the other is "No Offer from Vanessa."
We can type these into two cells,
and also type a value and a probability next to each.
I will model an event node with the more
challenging scenario, that of Bill waiting until the recruiting process,
and perhaps getting an excellent, very good, good, fair, or bad job offer.
I type these words into various cells, and then to the right,
I type the probabilities associated with each of these outcomes.
And next to those, I type the expected value for each of these outcomes.
I now must calculate the expected value of this particular node.
Remember that we desire the weighted average.
The expected value for the excellent job
branch is 21600 times the 5% probability.
Likewise, the expected value for a very good job
offer is 16800 times the probability.
Instead of going through each outcome individually, I know that
I can do these calculations faster by duplicating the calculation
in the first cell throughout all remaining cells.
I can click the first cell and left-drag down to highlight
the remaining cells, and then press Control-D on the PC; Command-D on the Mac.
To get the expected value for the node,
I can use the SUM() function at the beginning of the node.
I format these decision and event nodes so that they look more visually appealing.
Now that we see how to model the more challenging event node,
let's get back to modeling the event node for whether Bill gets Vanessa's offer.
This node has two branches, an "offer from Vanessa" and "no offer from Vanessa."
Each branch has an expected value
that we may not know yet, and each branch has a probability.
I can set up the equations just like I did for the recruiting event node,
even though I don't have any values yet.
I can now model the decision node for whether Bill accepts Vanessa's offer.
This node also has two branches.
We "accept the offer," or we "do not accept the offer." Remember that since
this is a decision node, we do not worry about probabilities, only values.
We know that if Bill accepts Vanessa's offer, the salary will be $14,000.
We don't know the salary if Bill rejects
Vanessa's offer because it depends on the recruiting process.
Okay, I think we're just about there.
We have the two decision nodes, accept or reject John's offer,
and the other decision node to accept or reject Vanessa's offer.
We also have two event nodes.
One represents the possibility that Vanessa provides an offer.
The other represents the chance of getting a job through the recruiting process.
Notice that this particular node gets repeated if Bill rejects
Vanessa's offer, or if Bill gets no offer from Vanessa.
In Excel, it's pretty easy
to duplicate this one chance event of the recruiting process.
I left-drag through all these cells, press Control-C, or Command-C on the Mac, to
copy it into the clipboard, then Control-V or Command-V to paste it elsewhere.
Okay, we now have to connect the nodes.
The result of one node feeds a value in another node.
We have no value shown under "reject John's offer,"
but we will create this using an equation.
This cell equals the expected value from the event node
cell, "accept Vanessa's offer.
Likewise, for the "no offer from Vanessa" cell, I create this
using an equation as the expected value from the recruiting process.
You can see that this process of constructing a decision tree
in Excel is a combination of creating the nodes
and then linking the expected value from one cell to another.
Here is my completed decision model.
If I have set up all the calculations correctly,
this first cell shows the expected value for this entire decision.
Wow, I have modeled Bill's problem with a decision tree
technique in Microsoft Excel!
Yes, it took some work, and it's a little awkward.
Let me know if you have any ideas to improve my methods.
We can also do sensitivity analysis.
We can change any of these values and see how our decision is affected.
Bill's problem is relatively small.
For bigger problems, you might be thinking, "Hmmm, this can get complex.
Is there any easier way to do this?" Well, I can't think of any better
way to do this, but whenever you are challenged with an Excel modeling problem
like this, you should consider exploring Excel "accessories" that might help.
Excel provides the ability to create
unique accessories by writing programs using Microsoft's
Visual Basic for Applications software, referred to as VBA.
These accessories often can be added into Excel,
so they are referred to as "add-ins." Even Microsoft
creates some add-in accessories, which I will show you later.
For creating Decision Trees, we are fortunate
to have a product called "TreePlan." Companies that create these accessories
often want to be paid for their efforts, so TreePlan is a pay-for accessory.
You can purchase it or use the version I provide for
free via our school's TreePlan site educational-use-only license.
The current TreePlan version works on both PCs and Macs.
I want you to experience an Excel add-in,
so I require you to use this TreePlan
add-in and experience modeling decision trees.
It may be a bit challenging, but in the end,
I think you will enjoy your learning.
So in this second part, follow along with me.
Some add-ins can be permanently installed in Excel.
I will demonstrate a temporary method.
Refer to the TreePlan documentation if you decide to install TreePlan permanently.
After downloading TreePlan, you will notice that it is a .zip file.
You can unzip this file on either the PC or Mac, typically
by double-clicking the zip file, and it unzips into its own folder.
There are several files in this new folder.
The key file that we will use is TreePlan-212-Student-Addin.xlam.
The "212" designates TreePlan's version, which may change with future versions.
The file extension, .xlam, may not show
if you have extensions turned off in Windows or MacOS.
The TreePlan installation varies between Macs and PCs.
The Mac installation is simpler, so I will demonstrate it first.
PC users should still watch this Mac installation to appreciate better
what you might face on the PC; there are some overlapping concepts.
The Treeplan file is a "macro" file.
Excel is sensitive to opening macro files because macro files can contain viruses.
By default, Excel disables macro files.
If you try to open the TreePlan-212-Student-Addin.xlam file,
Excel typically asks if you want to enable macros.
In this case, since we trust TreePlan, we choose to enable macros.
On my Mac, since I am careful about opening macro files,
so I turn on "Enable all Macros" in Excel's Security settings.
If you prefer, you can use the "Disable with notification" setting.
PC users will face this macro security issue, too.
Well, that should be about it.
If TreePlan loaded correctly, you should see another ribbon item
titled "TreePlan." To prove that TreePlan is loaded, click the TreePlan
ribbon, then the TreePlan icon, pick "new tree," and everything looks good.
You are ready to go!
Whenever you want to use TreePlan, begin by first opening this file.
Okay, I will show PC users how to install TreePlan,
which can be a little more complex depending upon your computer's
setup, Windows, and Excel.
I encourage Mac users to watch to appreciate running Excel
in other environments.
After downloading the TreePlan zip file, extract its contents
by right-clicking it, and picking Extract All.
This unzips the file into its own folder.
Double-click on the TreePlan-212-Student-Addin.xlam file.
You will probably get this "Excel Security Notice," click "Enable Macros."
If you are lucky, Excel opens
and has a new Treeplan ribbon.
Click the TreePlan ribbon, then the TreePlan icon,
click "New Tree," and you will see one node created.
You are in good shape.
However, for many students, this will not work.
Even I am in this category.
When I try opening the TreePlan file,
I get this dialog box saying, "Activate an unprotected worksheet in an unprotected
workbook before using TreePlan." This means that you must first open
Excel with a new workbook before opening this TreePlan file.
Watch. I open Excel365 in Windows 11, pick
Blank workbook, open the TreePlan file, click the TreePlan
ribbon, the TreePlan icon, "New Tree," and it works.
I see my first decision tree node.
This is how TreePlan on the PC should behave.
However, unfortunately, this won't work for some students.
You might encounter another problem.
If when you click on "New Tree," you get this "Excel
4.0 (XLM) macros have been disabled due to your security settings" dialog box,
there is another security setting that you must enable.
Do this by clicking on File, Options, Trust Center,
Trust Center Setting, Macro Settings, and then "Enable Excel 4.0 macros
when VBA macros are enabled." Also, notice
right here that you can "Enable VBA macros"
if you tend to trust macro files that you get from others.
Whoa, a lot of clicks!
For these setting to work, you must Quit Excel and re-open it.
Choose a blank workbook, open the TreePlan file, click the TreePlan ribbon,
then the TreePlan file, and it appears that TreePlan is now working.
This is good news, and more students will be happy.
Some students might still have problems.
The TreePlan company, in its PC Installation file,
describes yet another problem common to PC users.
This has to do with a setting in the TreePlan-212-Student-Addin.xlam
file, as described in the IMPORTANT-Windows-Excel-Info.txt file.
It says: "A security feature of the Windows
operating system may block running your Excel add-in.
To unblock your add-in, run File Explorer, navigate to your add-in
XLAM file, right-click the file icon, and choose Properties > General.
If you see an Unblock checkbox in the lower-right corner, check the box,
and click OK." Here is a picture showing the Unblock setting.
I have never encountered this problem, but a share of former students have.
I'll show you what the directions mean.
In Windows, I right-click the file icon, and choose Properties > General.
Notice that I do not see the Unblock option.
I am not sure when this Unblock option shows itself, it might have something
to do with having a company version of Excel, or something like that.
Yes, former students have reported seeing it,
and when they follow the directions to unblock, TreePlan works.
Well, as you can see, PC users might encounter
a few more problems than Mac users.
You might encounter these types of problems
when installing other add-ins in Excel.
Okay, let's now work with TreePlan and create Bill's job scenario in Excel.
Click the TreePlan ribbon, then the TreePlan icon.
We now have our first decision node.
Already you can see that creating your first decision
node is much easier than what I had done, manually, earlier in this session.
This TreePlan utility is very nice!
This particular decision node has two alternatives.
I can erase the utility-provided heading and type
in my label, such as Bill's Job Decision.
I will save this file typing control-S
on the PC, Command-S on the Mac.
I highly recommend getting into the habit
of regularly using this quick save keystroke.
I can replace the words Alternative 1 and Alternative 2
with the wording from our particular problem, "Accept John's Offer,"
"Reject John's Offer." If a branch of a decision node
ends with a value, you can enter that value right here.
We enter $12,000 for "Accept John's Offer." Notice
that TreePlan uses a triangle to denote the end of a branch.
The general technique of adding or removing nodes or branches is to click
one of these symbols and then click the "TreePlan" icon on the ribbon,
which will open a dialog box with various options.
I want to add two events for Reject John's Offer.
I click on this triangle, then the "TreePlan" icon,
and then "Change to event node," "Two."
I change the labels to "Offer from Vanessa" and "No offer from Vanessa,"
and change the default probabilities from 50-50 to 60-40.
Notice that probabilities are entered as decimals and not as percentages.
When you enter .6, you might notice that Excel shows an "#N/A."
This is because your probabilities must sum to 1.
After entering .4, this error goes away.
Pretty cool - TreePlan has some built-in error checking.
Now let's tackle that interview event.
I will put this on the "No Offer from Vanessa" branch.
I click on the ending triangle, then the TreePlan icon,
change this to Event with five branches, and then OK.
Wow, that was easy.
I can enter the ending values and the probabilities.
When we are done typing, you should note that TreePlan immediately
calculated the expected value for this node as $11,580,
which is what we expected from the reading.
Let's add the decision node for the "Offer from Vanessa" branch.
I click on the triangle, then TreePlan, then Decision Node, Two.
I label these "Accept Vanessa's Offer" and "Reject Vanessa's Offer."
The problem tells us that Vanessa's offer is $14,000, so I enter now.
We have one remaining event to add.
We need to add the Interview event to the "Reject Vanessa's Offer" branch.
TreePlan allows us to copy and paste branches, a pretty powerful feature.
I click on the event node for the Interview event, then Treeplan,
Copy subtree, and then click on the triangle
for "Reject Vanessa's Offer," TreePlan, and Paste subtree.
Wow, this is really nice!
Our decision tree is now complete, and we can see
that our decision tree exactly duplicates the results from our author.
Notice that TreePlan uses shapes to denote
decisions, events, and ending points and uses color.
We can easily see the decision and event nodes in Bill's problem.
Oops, I notice that the student version of TreePlan wants to keep its own title.
I can insert a row and type "Bill's Job Decision." Building a decision tree
with Treeplan, without a doubt, is easier than doing it manually.
If you find need to create decision trees at work,
you should consider buying a non-student license.
With a completed decision tree model in Excel, sensitivity
analysis is straightforward.
For example, let's assume that Vanessa's probability of getting an offer is 70-30.
Does this change our decision?
No, it doesn't.
The expected value is higher, but the path to get there remains the same.
How about if John offers $15,000?
Yes, this does change Bill's decision.
Take John's offer!
Excel and TreePlan can make decision modeling fun.
Give it a try.
Spend some time learning
TreePlan and read its documentation when you have some time.
I showed you how to use it, in general,
but TreePlan has even more features?
(upbeat 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.