In this video, I’m going to show how to use Excel OM/QM software to construct decision trees.
After installing the software, you should find an Excel QM shortcut on the desktop.
Double-click on the Excel QM shortcut to open it. It will launch Excel and ask if you want
to enable macros. Click Enable Macros.
The software is created by Howard Weiss and copyright by Pearson.
I’m going to close this dialog box. And click on the Excel QM tab up here.
I’m going to click on the Alphabetical listing of the contents here.
Choose Decision Analysis Then Decision Trees
I don’t want a title so I’m just going to click OK.
We’ll be using this dialog box to create the tree.
Let’s start by constructing a decision tree for this payoff table.
The decision tree starts at this selected decision node 1.
We will be entering the number of branches in this second box.
Since there are 3 decision alternatives here
we set this box to 3 and click the “Add 3 DECISIONS” button.
Now clicking on any of these terminal nodes selects that node
and the node number is shown in the first box here.
Since we have two states of nature or events here we change the number of branches to 2
and click “Add 2 EVENTS”
I’m going to change the probability at the top here to 0.6
and that automatically changes the other probability to 0.4
I’m going to click on Event 1 cell and type High and for Event 2, Low
Typically, we enter payoffs at the end of the terminal node here
but not in this program In this program,
we will only be entering payoffs in the shaded cells beneath the branch descriptions
The probabilities go on top of the descriptions So, 500 for High
and 600 for Low You can see here that the
expected value is automatically calculated. Now, instead of creating the event branches
for Decision 2 from scratch, I can just copy from Decision 1 by clicking on node 2
then clicking “Copy subtree at node 2” Next, I’ll click on the terminal node 3 here
and click “Paste subtree from 2”. I’m going to change the payoffs…
and the expected value is calculated to be 590 here.
Since the payoff is 500 for Decision 3 irrespective of the state of nature,
I only need to enter 500 once here. This decision node then compares the
expected values for the 3 decision and selects the best, which is 590 here.
Now, note that there is no undo button but you can be saving your work as you go.
So, if something goes wrong you can just close and reopen the file.
Another way is to make copies of your sheet after each stage.
So if you make a mistake, you can go back and continue from an earlier version.
I will not make a copy of this sheet by right-clicking on Sheet 2 here
Select Move or Copy Check the “Create a copy” box
and click OK. So I have Sheet 2 and a copy of it here.
I’m going to continue working on the copy. Suppose if we go with Decision 3,
we are again faced with choosing between two options: 3A and 3B.
That will require clicking on the terminal node 4 here
and adding 2 decision branches there. I’m going to delete the 500 payoff here.
And enter the new payoffs 550 and 450 respectively.
You can see that the better of the two payoffs is automatically selected here.
I will also click on the Decision label cells to change them to 3A
and 3B
Now, suppose option 3A is also subject to High and Low events with payoffs as shown.
I can click on node 2, copy the subtree
then click on node 9 and paste the subtree.
Delete the 550 payoff here
and replace the High and Low Payoffs with the new values.
The expected value there is calculated to be 600 And is chosen here because it is larger than 450
It’s also chosen here as the best expected value of the 3.
Now, suppose we want an additional branch at node 3.
Click on node 3. And change the number of branches to add to 1.
Click “Add 1 event” And if I think I don’t really need that branch,
I can click on node 9 here and click “Remove node 9”
That will remove the branch that goes from node 3 to node 9.
You can see here that it’s now gone. Now if these payoffs were costs
we just need to click Minimize here and wait a few seconds.
And you can see here that it chose the minimum of the expected values.
And that an overview of the Excel OM-QM Decision Tree module.
Thanks for watching.