XLSTAT: Calculation Methods and Optimal Path of a Decision Tree in Excel

 

Background

In XLSTAT, decision trees offer two calculation methods: maximize gain (profit optimization) or minimize gain (cost optimization). You can also apply an exponential utility function, where utility R reflects risk preference—positive for risk aversion, negative for risk-seeking, and zero for neutral.

 

Dataset for Example

In this example, a company planning a product launch must choose the best advertising channel to maximize or minimize gains, considering its risk aversion. The concept of an optimal path is also explored.

The objective is to start from an existing decision tree, based on a concrete example, and to learn about the different calculation methods.

 

Setting Up

• To begin, load the provided decision tree.

• Right-click the top tree block and select XLDTREE > Open selected tree settings dialog box.

• In the General tab, choose Maximize Gain and set Optimal Path for: Expected value.

• In the Useful Information/Calculation Data tab, select your display preferences and click OK.

 

• To highlight the optimal path in green, right-click the top tree block and select XLDTREE > Highlight optimal path for the selected tree.

• The path updates automatically when any colored cell value changes.

  

Here is the resulting tree: 

The optimal path leads to tree endpoints that best fulfill the goal of maximizing gain. It is based on the expected gain at each node and varies by node type:

• Decision node: the path follows the branch with the highest expected gain per the chosen method.

• Chance node: the path includes all branches, as outcomes are uncertain and weighted by their probabilities.

For example, in an internet advertising investment using internal resources, we observe the following data:

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)

To maximize gain, outsourcing the internet advertising is the better option based on expected gain, even when comparing individual channels. Other factors may influence the decision but can be added to the tree for further analysis.

• To switch to minimizing gain, reopen the tree settings dialog, select Minimize Gain in the General tab, and click OK.

Here is the resulting tree: 

The optimal path now leads to the endpoints that best achieve the objective of minimizing gain.

For example, in a social network investment using internal resources, the following figures are observed:

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)

To minimize gain, outsourcing social network advertising remains the better option based on expected gain across channels.

To now maximize gain using a utility function:

• Open the tree settings dialog.

• In the General tab, select Maximize Gain and Optimal path for: Expected utility.

• Check R if utility function and input the utility value or reference a cell for flexibility.

• In the Useful Information/Calculation Data tab, choose your display settings, then click OK.

  

Here is the resulting tree: 

The optimal path, now based on maximizing gain with utility, again points to outsourcing internet advertising as the best choice—just as in the case without utility.

 

Additional Info

To highlight the optimal path from a specific node, right-click its block and select XLDTREE > Highlight optimal path from selected node. Repeat to remove it.

The optimal path updates automatically with any tree changes. You can also force a single branch into the optimal path—useful when revisiting a tree after decisions are made. Only one branch can be forced at a time; it appears with a distinct name color.