Skip to main content
XLSTAT is joining the Lumivero family. Learn more.

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. Decision Tree in XLSTAT - 1 Decision Tree in XLSTAT - 2

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). Decision Tree in XLSTAT - 3

Here is the resulting tree: Decision Tree in XLSTAT - 4 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.

Decision Tree in XLSTAT - 5

Here is the resulting tree: Decision Tree in XLSTAT - 6 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.

Decision Tree in XLSTAT - 7 Decision Tree in XLSTAT - 8

Here is the resulting tree: Decision Tree in XLSTAT - 9 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.

Was this article useful?

  • Yes
  • No