XLSTAT: Conditional logit model in Excel

This tutorial will help you set up and interpret a Conditional Logit analysis in Excel using the XLSTAT statistical software.

 

Background

The conditional logit model is a statistical method similar to logistic regression.

The conditional logit model is a method mostly used in its evolved form as part of the conjoint analysis. It is nevertheless useful when to analyze a certain type of data. McFadden introduced this model in 1973. Instead of having one line per individual, there are as many lines as alternatives. Thus, it is no longer the characteristics of the individuals that are modeled but the alternatives.

If one seeks to study travel modes, we will have four travel modes (car / train / air / bus), each travel mode having its own characteristics (price, speed), but an individual can only choose one of the four modes.

As part of a conditional logit model, we have for N individuals, N*4 rows with 4 rows associated with the four choices. The binary response variable will indicate the choice of the individual with the value 1, and 0 will correspond to the options that the individual did not choose.

A column associated with the name of the individuals (with 4 lines per individual for our example) has to be selected in XLSTAT. The explanatory variables will also have N * 4 rows.

 

Dataset for Example

The example discussed below is a classic case in which one seeks to compare the travel modes proposed to go on vacation. It comes from Greene, W.H. (2003). Econometric Analysis, 5th edition. Upper Saddle River, NJ: Prentice Hall.

An Excel file containing both the data and the results can be downloaded above.

The data correspond to a sample of 210 individuals, each one having 4 possibilities (air, car, bus and train). We asked each of them the travel mode they would choose to go on vacation.

The data set has 840 rows. The first column identifies the individual, the second is the binary variable modeling the travel mode. Then there are two quantitative variables, respectively, the overall cost and the waiting time during the trip associated with each travel mode for each individual. Finally, the categorical variable associated with the transportation is in the last column (air, train, bus or car).

 

Setting Up

• Open XLSTAT.

• Click on the Marketing/Conjoint Analysis menu and select the Conditional Logit feature.

• Once you have clicked the button, the dialog box appears.

In the General tab:

• Select Choice in the Response variable field.

• Select Subject in the Subject labels field.

• Select Travel Mode in the Qualitative Explanatory variable field.

• Select Global Cost and Waiting Time in the Quantitative Explanatory variable field.

• Select the variable labels  option, as we selected the labels of the variables.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The following table gives several indicators of the quality of the model (or goodness of fit). These results are similar to R² and to the analysis of variance table of linear regression and ANOVA. The most important value is the Chi-square associated with the log ratio (LR). This is equivalent to Fisher's F test in the linear model: an attempt to assess whether the variables provide a significant amount of information to explain the variability of the binary variable. In our case, as the probability is less than 0.0001, we can conclude that the variables provide a significant amount of information.

This goodness of fit statistics show that our model is significantly better than the model without any predictor. The following table confirms these initial impressions:

The p-values are all very small and the impact of the three variables is significant in the type III analysis table.

Finally, the coefficients of the model show that the air is preferred and that the waiting time has a significant negative effect on the choice of travel mode.

The analysis of residuals may also be useful and provide other information about individuals' choices.