XLSTAT: Building a Decision Tree in Excel

 

Data For Example

See the attached data where a ficticious company is deciding on the best advertising channel for launching a new product. The decision tree, with its nodes and branches, visualizes various solutions and outcomes. We will use this tool to assist the company in its decision-making process.

 

Setting Up

• Open XLSTAT.

• Click on the Marketing/ Decision aid menu and select the Decision tree feature.

In the General tab:

• Enter the tree's name in the Name field.

• To maximize the company's gain, enable Maximize Gain and set Optimal Path for: Expected value.

In the Options tab:

• Enable the following options:

In the Useful Information tab:

• Select the information to display for each node and assign labels and positions. 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

• To add the first node, click the green triangle or right-click the end node (right block) and select XLDTREE/Create New Node to open the Node Setup dialog.

  

• Select "Decision" to create the first decision node with branches.

1. • Enter the node name, then add a branch using the  button next to the branch list.
   • Update each branch in the Selection section and validate with the button.

  

• Click the OK button to validate the dialog box and display the node you created.

• For each advertising channel, choose between internal resources or an external company. Click the green triangle of the end node for the first branch or right-click and select XLDTREE/Create New Node to open the Node Setup dialog and fill in the details.

 

• Right-click a cell in the new node block and select XLDTREE/Copy subtree from selected node.

  

• Right-click a cell in the second end node block and select XLDTREE/Paste Subtree in place. Repeat for the last end node. 

• Fill in the correct values in the cells. Cells with a colored background can be edited directly in Excel, while formula cells will update automatically.

• Replace each end node with a chance node, describing the two turnover hypotheses (high or low). Follow these steps:

1. • Click the green triangle or right-click the node block and select XLDTREE/Create New Node.

   • Select "Chance" and fill in the required details.

   • Copy the node.

   • Paste it onto the other relevant end nodes.

   • Enter the correct values directly in the Excel sheet cells.

 

Interpreting the Results

For an investment in newsletter development, the following figures apply:

Using internal resources:

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	40%	18 000	7 200 (40% x 18 000)	16 800 (7 200 + 9 600)	-3 500	13 300 (16 800 - 3 500)
Basse	60%	16 000	9 600 (60% x 16 000)

 

Using external resources:

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%	20 000	12 000 (60% x 20 000)	18 400 (12 000 + 6 400)	-5 000	13 400 (18 400 - 5 000)
Basse	40%	16 000	6 400 (40% x 16 000)

It seems more profitable to hire an external company for the newsletter setup. When comparing potential revenue from each advertising channel, internet advertising stands out as the best option. Other factors should also be considered, but they can be easily incorporated into the analysis for more accuracy.

 

Go further

1. You can choose:
   1. the display mode for a decision tree
   2. the types of information to display.
   3. See Customize Decision Tree to play with these options.
2. Methods for calculation and optimal path in a decision tree
   1. Different calculation modes are available to exploit a decision tree. 
   2. You can also display the optimal path according to the objective (maximize or minimize the gain), for the whole tree or from a specific node.
   3. See Calculation methods and optimal paths to learn more.