Choice Based Conjoint Analysis (CBC) in Excel tutorial

This tutorial will help you design and interpret a Choice-Based Conjoint analysis (CBC) in Excel using the XLSTAT statistical software.

What is Conjoint Analysis?

Conjoint analysis is a popular marketing method that allows you to know the expectations of consumers about a product and to model their choices - both crucial steps of a marketing analysis. Conjoint analysis in XLSTAT can be used to simulate competitive markets using a single analysis; it is its biggest advantage.
Two methods of conjoint analysis are available: full profile conjoint analysis and Choice-Based Conjoint analysis (CBC). XLSTAT analysis is a complete statistical program which allows you to run through all the analytical steps of conjoint analysis which can be divided into five steps:
1. Choice of the relevant factors and their modalities to describe the products.
2. Generation of design of experiments based on full factorial, fractional factorial, D-optimal and incomplete block designs.
3. Collection of the results in Microsoft Excel sheets.
4. Data analysis with specific regression methods - MONANOVA (monotone regression), multinomial logit, conditional logit, etc.
5. Simulation of new markets with various methods: first choice, logit, Bradley-Terry-Luce and randomized first choice.
These steps can be carried out for a full profile conjoint analysis or a CBC.
In this tutorial, we will develop the steps for the implementation and interpretation of a CBC analysis with prohibited pairs.

Dataset for running a choice-based analysis

In this tutorial, we will look at a classic case of conjoint analysis on the introduction of a new product in a competitive market. A brand of soft drink wants to introduce a new product. A conjoint analysis is applied in order to answer two 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?
The results are divided into different sheets:
1. Factors: this sheet contains the characteristics of the selected factors.
2. CBC Design: this sheet contains the profiles generated and the choices given by the 10 individuals.
3. CBC Analysis: this sheet contains the results of conjoint analysis (CBC).
4. Market generator: this sheet contains the complete market to simulate.
5. Market Simulation: this sheet contains the results of the market simulation.

Setting up the design for a choice-based analysis with XLSTAT

Choosing the factors

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 :
1. Temperature (very hot, hot, iced)
2. Sugar (no sugar, 1 sugar, 2 sugar)
3. Lemon (yes, no)
4. 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 CBC, selections of products are presented to the respondents who choose the one they would buy in each case.

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.
You can then enter the factors/categories table, the number of profiles to classify (12), the number of comparisons (20, this number must 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 us to select certain combinations of modalities that are not possible.
Once you click on OK, a dialog box appears which allows the selection of prohibited combinations. Here we choose to prohibit a drink without lemon and with a strong intensity. For that, we select "no" and "strong" in the left part of the interface, then click on Add, and finally on OK.

The first table summarizes the generated model. The second table is the table of profiles.

The following table is the table of choice, it is in the sheet CBC Design 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.

Setting up a Choice-Based Conjoint analysis with XLSTAT

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 questions and running the model. If utilities are well displayed, then, you can make your respondents fill the rest of the table.
As part of this analysis, 10 individuals have been questioned about their preferences in terms of tea. The results are 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 launches the interface with loaded data.
Alternatively, you can click on CJT / Choice-Based Conjoint analysis. In this case you need to manually load the different datasets needed.

Once you click the OK button, computations are made and results are displayed. A multinomial logit model derived from a specific conditional logit model is used for estimation.

Interpreting the results of a Choice-Based Conjoint analysis

The most important results are the utilities and importances. 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 temperature and the intensity factors are the most important factors in the analysis with an importance of more than 30%.

Running a Market simulation with XLSTAT

The main advantage of conjoint analysis is that it allows us to simulate a market even if the products in the market have not been tested by the individuals.
In our case, the market for a tea-based beverage is analyzed and we would like to investigate the impact and market shares the introduction of a new product would have.
This product (Product 4) is a medium ice tea with lemon and 1 sugar. We know that in today's market there are 3 tea-based beverages that have different characteristics.
The following table shows the simulated market:

This table will be needed for the market share simulation. To easily create this table, you can use the Market generator tool as follow:
To generate the market table containing the different products, you have to select the Variable Information table which is on the results sheet of your conjoint analysis. You also have to indicate the number of products you want to generate.

Then, for each attribute of each product, you will be asked to choose the category to add. When an entire product has been defined, you can either continue with the next product or stop building the table. Usually, the new product we want to introduce on the market is the last product. In our case, this is Product 4.
Once the market is generated, you can start the simulation. To do that, click the CJT icon and choose the function Conjoint analysis simulation tool.
You can then select the data. To facilitate data selection and avoid manually selecting the four required datasets (Utilities, Variable information, Simulated market, Product ID), click on the magic stick. This will automatically load the four datasets if the conjoint analysis and the simulated market have been generated with XLSTAT and if the two worksheets have not been manually modified (add rows or columns, etc…). Then select any cell of the sheet containing the results of choice based conjoint analysis and any cell of the worksheet containing the market generated with XLSTAT. In this example, we selected the cells H13 of the sheets CBC Analysis and Market Generator.

You can also select your data manually: Utilities can be found in the CBC Analysis sheet; the table of information about variables is obtained in the CBC analysis sheet. The simulated market is in the Market generator sheet (do not select the names of products). You can also select the name of the product just behind the Product ID button. Select the Full profile model and the logit method for simulation.

Once you click the OK button, the calculations are performed and the results are displayed.
The table shows that the market share for the new product (Product 4) is more than 50%. This result seems more than satisfactory in order to launch the product on the market.

To quickly evaluate the impact of other change on the new product (you may want to know what would be the market share if there is no sugar on the product), you can modify the categories of the last product (blue row) in the Simulated market table by clicking on the categories you want to change.
Once the categories are modified, you can click on the Run the analysis again button (just below the simulated market table). Market shares and associated charts are automatically updated.

We can see in this example that market share slightly decreased without sugar. So, it would be better to launch a product with 1 sugar.
Many more advanced analyses are possible with XLSTAT (e.g., use of segmentation variables, weights, use of statistical clustering methods...).

Was this article useful?