Choice Based Conjoint with multiple question sets in Excel

This tutorial will help you design and interpret a Choice-Based conjoint analysis (CBC) with multiple questionnaires using the XLSTAT statistical software.

Do you need the basics of Conjoint Analysis? Click here to read What is conjoint analysis.

Goal of this tutorial

In this tutorial, we will look at a classic case of conjoint analysis on the introduction of a new product in a competitive market. This product is a drink based on tea.
A brand of soft drink wants to introduce a new product. A conjoint analysis is applied in order to answer 2 questions: What characteristics should the drink have in order to, 1) please the greatest number of people, and 2) gain market shares in an already competitive market?

Dataset

The data are divided into different sheets:

Factors: this sheet contains the characteristics of the selected factors.
CBC Design: this sheet contains the profiles generated and the choices given by the 10 individuals.
CBC Analysis: this sheet contains the results of the conjoint analysis (CBC).

Step 1: Choosing the factors

In this tutorial, we will look at a classic case of conjoint analysis on the introduction of a new product in a competitive market. This product is a drink based on tea.
A brand of soft drink want to introduce a new product and in order to answer two questions, a conjoint analysis is applied. What are the characteristics that should bring the drink in order to, first, please the greatest number of people, and, secondly, to gain market shares in an already competitive market?
The first step in the conjoint analysis is done in collaboration with experts in the beverage market: choosing the main characteristics that define a drink. The selected factors are :

Temperature (very hot, hot, iced)
Sugar (no sugar, 1 sugar, 2 sugar)
Lemon (yes, no)
Intensity (strong, medium, light)

By combining the different levels of these factors, 54 different products can be generated. Judges (respondents) will not be able to evaluate all these products. So, we will use experimental designs to reduce the number of products presented to the respondents. In choice-based conjoint analysis (CBC), selections of products are presented to the respondents who choose the one they would buy in each case.

Step 2: Profile selection

XLSTAT-Conjoint analysis designs let you select a number of profiles (possible products) that are then automatically grouped into different sets. These sets are shown to respondents who are asked to choose the product they prefer in every set.
Once XLSTAT is started, click on the CJT icon and choose the function Design for choice-based conjoint analysis.
Enter the factors/categories table, the number of profiles to classify (12), the number of comparisons (20), this number has to be greater than the number of profiles) and the number of profiles per comparison (3).

In the Options tab, we check the option prohibited combinations which allows selecting some combinations of modalities that are not possible.
We also choose to generate two different designs containing every 10 combinations and for 5 different individuals.
Here, we take into account prohibited combinations, so we can only search for a D-optimal design.

Once you click on OK, a first dialog box appears, it allows the selection of prohibited combinations.
We choose here to prohibit the chocolate flavor with the type of lightened ice.
For that we select chocolate and low fat in the left part of the interface, then click on Add, and finally on OK.

Once you click the OK button, the calculations are made and the results are displayed. The first table summarizes the generated model. The second and third tables are the profiles tables for each design.

The following table is the table of choice and must be completed after respondents (individuals) have been interviewed. The choices are between 1 and 3 for each individual. The numbers on the left of the table are associated with profiles of the profile table.

Step 3: Filling the conjoint analysis tables

The conjoint design table can either be filled directly in the CJT design sheet after interviewing individuals or using the individual sheets with automatic referencing of results. The latter possibility is interesting in the context of CBC analysis because completing the overall table can be complex.

Important: When using the optimized version of the design algorithm, please check the validity of the design by responding to a set of question and running the model. If utilities are well displayed, then, you can make your respondents fill the rest of the table.

Step 4: Interpreting the results of a CBC analysis with multiple question sets

As part of this analysis, 10 individuals have been questioned about their preferences in terms of tea. The results are displayed in the CBC Analysis sheet.
Once the conjoint design is filled with the responses, you are ready to run the analysis. One option is to click on the button Run the analysis which automatically loads the data into the interface.

A multinomial logit model derived from a specific conditional logit model is used for estimation.
The most important results are the utilities and importance. They can be found in the first tables.
We see that the utilities are not individual but related to all individuals.

This shows that the Intensity factor is the most important factor in the analysis with an importance of 52%. In the aggregated utility table, we see that the lack of sugar has a positive effect on choices.

Was this article useful?