Calculation methods and optimal path of a decision tree

This tutorial explains the different calculation methods and the notion of optimal path for a decision tree in Excel using XLSTAT.

Data set for working on calculations in a decision tree

Click on the link above to download an Excel workbook with the decision tree used for the calculations.

Goal of this tutorial

The objective is to start from an existing decision tree, based on a concrete example, and to learn about the different calculation methods. A company ready to launch a new product on the market is wondering which advertising channel it should use. We will help the company to make a choice according to its objective: maximizing its gain or minimizing it, taking into account its degree of risk aversion. The notion of optimal path is also presented. If you want to learn more about the construction of the tree, check out this tutorial.

Calculation methods for a decision tree and optimal path with XLSTAT

In XLSTAT, there are two calculation methods to help you make decisions. The gain, the sum of the costs and profits, can be maximized if you want to optimize your profit or minimized if you want to optimize your cost. These two methods of calculation are enhanced by the possibility of having a gain calculated from an exponential utility function where the utility R can be set. A positive utility reflects a more or less pronounced aversion to risk, whereas a negative utility reflects a preference for risk. Zero utility means that the gain is the sum of the costs and profits.

We start with an existing tree, provided in the data set. Let's start by displaying the tree with the objective of maximizing the gain. To open the tree settings dialog box, right click on the tree block (first block on top) and select XLDTREE/Open selected tree settings dialog box.

In the General tab, select the options Maximize Gain and Optimal Path for: Expected value.

In the Useful Information/Calculation Data tab, select the information to be displayed with the desired label and position. Then click on the OK button.

To highlight the optimal path (in green), right-click on the tree block (first block on top) and select XLDTREE/Highlight optimal path for the selected tree. The optimal path is recalculated each time a value is changed (cells on colored background).

Here is the resulting tree: The optimal path leads us to the ends of the tree that best meet the initial objective: maximizing the gain. It depends on the expected gain of each node and its behavior is different depending on the type of node. For a decision node, it passes through the branch whose gain best suits the chosen calculation method. For a chance node, it passes through all the branches of the node. Indeed, it is not possible to know in advance which branch will be completed, regardless of its probability of completion. The expected gain takes this behavior into account since it is the weighted sum (by the probability of completion) of the expected gain of the child node of each branch.

Let's take the example of an investment in internet advertising. We can observe the following figures:

• In the case where internal resources are used:

Expected sales hypothesis	Probability	Expected profit by hypothesis	Exp. profit weighted by hypothesis	Expected profit from the option	Cost of the option	Expected gain
Haute	60%	30 000	18 000 (60% x 30 000)	26 000 (18 000 + 8 000)	-8 000	18 000 (26 000 - 8 000)
Basse	40%	20 000	8 000 (40% x 20 000)

• In the case where external resources are used:

Expected sales hypothesis	Probability	Expected profit by hypothesis	Expected profit weighted by hypothesis	Expected profit from the option	Cost of the option	Expected Gain
Haute	70%	40 000	28 000 (70% x 40 000)	35 500 (28 000 + 7 500)	-15 000	20 500 (35 500 - 15 000)
Basse	30%	25 000	7 500 (30% x 25 000)

It therefore seems better, if we want to maximize the gain, to opt for having an external company develop the internet advertising. If we compare the expected gain from each advertising channel then this option is still the best choice. Such a decision must take into account other factors but these can easily be added to the tree to refine its analysis.

We are still starting from the same tree. We will now display the tree with the objective of minimizing the gain. In the General tab of the tree settings dialog box, select the Minimize gain option and click the OK button.

Here is the resulting tree: The optimal path leads us to the ends of the tree that best meet the initial objective: minimizing the gain.

Let's take the case of an investment in social networks. We can observe the following figures:

• In the case where internal resources are used:

Expected sales hypothesis	Probability	Expected profit by hypothesis	Expected profit weighted by hypothesis	Expected profit from the option	Cost of the option	Expected Gain
Haute	50%	20 000	10 000 (50% x 20 000)	17 500 (10 000 + 7 500)	-5 000	12 500 (17 500 - 15 000)
Basse	50%	15 000	7 500 (50% x 15 000)

• In the case where external resources are used:

Expected sales hypothesis	Probability	Expected profit by hypothesis	Expected profit weighted by hypothesis	Expected profit from the option	Cost of the option	Expected Gain
Haute	60%	25 000	15 000 (60% x 25 000)	22 200 (15 000 + 7 200)	-10 000	12 200 (22 200 - 10 000)
Basse	40%	18 000	7 200 (40% x 18 000)

It therefore seems better, if we want to minimize the gain, to opt for an external company to develop advertising on social networks. If we compare the expected gain of each advertising channel then this option is still the best choice.

We are still starting from the same tree. We will now display the tree with the objective of maximizing the gain but using the utility function. Open the tree settings dialog box.

In the General tab, select the options Maximize gain and Optimal path for: Expected utility, check the R if utility function box and fill in your utility. It is possible to enter the utility directly but it could be useful to read the utility in a cell of the sheet (as in our case) in order to vary this parameter without having to go through the dialog box.

In the Useful Information/Calculation Data tab, select the information to be displayed with the desired label and position. Then click on the OK button.

Here is the resulting tree: The optimal path leads us to the ends of the tree that best meet the initial objective: maximizing the gain with utility. As in the case of the gain without utility, opting for an external company to develop the internet advertising seems to be the best choice here.

Go further

You can highlight the optimal path for a particular node. To do this, simply right-click on the block of the starting node and select XLDTREE/Highlight optimal path from selected node. The same operation allows you to remove the optimal path.

As long as the optimal path option is activated, it is automatically recalculated and its display updated, each time the tree is modified. You can also force a branch to be part of the optimal path. This can be useful when you come back to an existing tree later, after certain choices have already been made or carried out.

Only one branch, among all those of the tree, can be forced. If another branch had already been forced then it would not be forced anymore. The forced branch has its name displayed in a different color than the other branch names.