Calculating custom measures using Excel

To gain a bit more insight as to how the model performs in practical terms, choose Organize Model Measures from the Tools menu.
Figure 1. Organizing model measures

The Organize Model Measures dialog box allows you to choose the measures (or columns) to show in the Interactive List viewer. You can also specify whether measures are computed against all records or a selected subset, and you can choose to display a pie chart rather than a number, where applicable.

Figure 2. Organize Model Measures dialog box

In addition, if you have Microsoft Excel installed, you can link to an Excel template that will calculate custom measures and add them to the interactive display.
In the Organize Model Measures dialog box, set Calculate custom measures in Excel (TM) to Yes.
Click Connect to Excel (TM)
Select the template_profit.xlt workbook, located under streams in the Demos folder of your IBM® SPSS® Modeler installation, and click Open to launch the spreadsheet.
Figure 3. Excel Model Measures worksheet

The Excel template contains three worksheets:
- Model Measures displays model measures imported from the model and calculates custom measures for export back to the model.
- Settings contains parameters to be used in calculating custom measures.
- Configuration defines the measures to be imported from and exported to the model.
The metrics exported back to the model are:
- Profit Margin. Net revenue from the segment
- Cumulative Profit. Total profit from campaign
As defined by the following formulas:
```
Profit Margin = Frequency * Revenue per respondent - Cover * Variable cost

Cumulative Profit = Total Profit Margin - Fixed cost
```
Note that Frequency and Cover are imported from the model.

The cost and revenue parameters are specified by the user on the Settings worksheet.

Figure 4. Excel Settings worksheet

Fixed cost is the setup cost for the campaign, such as design and planning.

Variable cost is the cost of extending the offer to each customer, such as envelopes and stamps.

Revenue per respondent is the net revenue from a customer who responds to the offer.
To complete the link back to the model, use the Windows taskbar (or press Alt+Tab) to navigate back to the Interactive List viewer.
Figure 5. Choosing inputs for custom measures

The Choose Inputs for Custom Measures dialog box is displayed, allowing you to map inputs from the model to specific parameters defined in the template. The left column lists the available measures, and the right column maps these to spreadsheet parameters as defined in the Configuration worksheet.
In the Model Measures column, select Frequency and Cover (n) against the respective inputs and click OK.
In this case, the parameter names in the template—Frequency and Cover (n)—happen to match the inputs, but different names could also be used.
Click OK in the Organize Model Measures dialog box to update the Interactive List viewer.

Figure 6. Organize Model Measures dialog box showing custom measures from Excel

The new measures are now added as new columns in the window and will be recalculated each time the model is updated.

Figure 7. Custom measures from Excel displayed in the Interactive List viewer

By editing the Excel template, any number of custom measures can be created.