XLSTAT: Choice Based Conjoint Analysis (CBC) in Excel

 

Background

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 example

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

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

• Open XLSTAT

• Click on the Marketing/Conjoint Analysis icon and choose the function Design for Choice-Based Conjoint analysis.

In the General tab:

• Enter the Factors/Categories table.

• Set the Number of profiles to classify to 12.

• Set the Number of comparisons to 20, ensuring that it is greater than the number of profiles.

• Set the Number of profiles per comparison to 3.

• In the Options tab, select Prohibited combinations to specify combinations of categories that should not occur in the generated profiles. 

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

 

Choice Based Conjoint analysis

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

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 factor is 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:

• Open the Market Generator tool to create the market table required for the market share simulation.

• Select the Variable Information table from the results sheet of the conjoint analysis.

• Specify the Number of products to generate.

• Generate the market table containing the different product profiles.

• For each attribute of each product, select the corresponding category.

• Once a product is fully defined, continue with the next product or stop generating the table.

• Define the new product to be introduced to the market as the last product. In this example, this is Product 4.

• Once the market table is generated, click the CJT icon.

• Select Conjoint analysis simulation tool to start the market share simulation.

• After clicking on the button, the dialog box appears.

In the General tab: 

• Make sure the conjoint analysis and simulated market were generated with XLSTAT and that their worksheets have not been manually modified.

• Click the magic wand to automatically load the four required datasets: Utilities, Variable Information, Simulated Market, and Product ID.

• Select any cell in the worksheet containing the Choice-Based Conjoint analysis results.

• Select any cell in the worksheet containing the market generated with XLSTAT.

• Click OK to confirm.

Note: you can also select the data manually if needed.

• Select the Utilities table from the CBC Analysis sheet.

• Select the Variable Information table from the CBC Analysis sheet.

• Select the Simulated Market from the Market Generator sheet, excluding the product names.

• Select the Product ID containing the product names.

• Select the Full Profile model.

• Select the Logit method for the simulation.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

The table shows that the market share for the new product (Product 4) is only 20%. This result seems not 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 increase a bit without sugar. We can try different modification to see if a new product can be interesting to launch on the market.