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 popular 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. 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, randomized first choice.

These steps can be carried out for a full profile conjoint analysis or a choice based conjoint analysis (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 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?

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 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, 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 to select certain combinations of modalities that are not possible.

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 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. In case we do not select prohibited combinations, another 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. Here, with prohibited combinations, the optimize option is automatic.

Once you click the OK 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. [CBC08]

Setting up a choice-based analysis with XLSTAT

The first step is to fill the conjoint design table. This can either be done 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.

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. 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 and if the sheet of results has not been manually modified (add rows or columns…). A new window will pop up. Select any cell of the sheet containing the design for choice-based conjoint analysis. In this example, we selected cell H13 in 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.

Interpreting the results of a choice-based 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 sugar and the intensity factors are the most important factors in the analysis with an importance of more than 33%. In the aggregated utilities table, we see that the lack of sugar has a negative effect on choice.

Running a Market simulation with XLSTAT

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 medium very warm tea without 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. 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 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…). 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. 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 almost 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 no sugar in 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 without sugar. So, it would be better to launch a new product with 1 sugar.

Many more advanced analyses are possible with XLSTAT (use of segmentation variables, weights, use of statistical clustering methods...).