Your data analysis solution

Choice Based Conjoint (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 marketing method that allows to know the expectations of consumers about a product and to model their choices - both crucial steps of a marketing analysis. The conjoint analysis method is now extremely common in marketing. Modeling of consumer choice is a key area of marketing. Conjoint analysis is 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 in 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, randomized first choice.

These steps can be carried out both for a full profile conjoint analysis and for a choice based conjoint analysis (CBC).

In this tutorial, we will develop the steps necessary for the implementation and interpretation of a conjoint analysis with XLSTAT.

Dataset and goal of this choice-based conjoint analysis 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 softdrink 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?
An Excel spreadsheet containing the data and results of this example can be downloaded by clicking here 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.

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 softdrink 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:

  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 choice based conjoint analysis (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.

Once the button is clicked, the dialog box appears.

You can then enter the name of the analysis, the number of factors (four in our case), 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 Factors tab, activate the Select on a sheet option and select the data in the Factors sheet. Do not select column labels.

In the Outputs tab, do not activate the individual sheets in this example because the generation of these sheets is not necessary. In a comprehensive analysis, they can be very useful in order to fill the results directly by respondents.

Once you click on the OK button, a new dialog box appears. It allows you to select the fractional factorial design of experiments or to optimize the design (D-optimal). We use the optimize option.

Once you click the Optimize button, the calculations are made and the results are displayed.
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.

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 optimize 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.

Interpreting the results

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.

You can then select the data.

To facilitate data selection and avoid to manually select the three required datasets (Responses, profiles and choices), you can click on the magic stick. This will automatically load the two datasets if the conjoint design has been generated with XLSTAT. A new window will pop up. Select the first cell of the XLSTAT report of the conjoint design. This cell usually corresponds to cell B1 of the CBC design sheet. Then click OK.

You can also select your datasets manually. In the responses field, select the 10 columns of the table of responses completed by individuals. In the choice table field, select the three columns containing the comparisons profile numbers (without comparisons names). In the profiles field, select the profile table (without profile names).

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. 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 sugar factor is the most important factor in the analysis with an importance of more than 60%. It is followed by temperature which has an importance of 22%. In the aggregated utilities table, we see that the lack of sugar has a positive effect on choice.

Market simulation

The main advantage of conjoint analysis is that it allows 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 strong iced tea without lemon and no 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. In order to easily create this table, you can use the Market generator tool as follow:

In order 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 click on OK.

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 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.

In order to facilitate data selection and avoid to manually select the four datasets required (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. Then select the first cell of the conjoint analysis report and the first cell of the generated market report. These correspond to the cells B1 of the CBC Analysis and Market generator sheets. Then click OK.

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 30%. This result seems satisfactory in order to launch the product on the market.

In order to quickly evaluate the impact of other changing on the new product (you may want to know what would be the market share if there is 1 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 on this example that market share slightly decreased with the addition of 1 sugar. So it would be better to launch a new product without sugar.

Many more advanced analyses are possible with XLSTAT (use of segmentation variables, weights, use of statistical clustering methods...).
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found