Conjoint analysis in Excel tutorial

This tutorial will help you design and interpret a full profile conjoint analysis 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.
The XLSTAT Conjoint analysis 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 levels to describe the products.
2. Generation of a design of experiments based on full factorial, fractional factorial, or D-optimal.
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 full profile conjoint analysis with prohibited pairs.

Dataset to run a full profile conjoint analysis

In this example, we will study a classic case of conjoint analysis: how to introduce a new product in a competitive market. More specifically, an ice cream shop wants to introduce a new ice cream. The conjoint analysis will be used to answer the following questions: what are the characteristics that the ice cream should have in order to, first, please the greatest number of people, and, second, 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. CJT Design: this sheet contains the profiles generated and the rankings given by the 15 individuals.
3. CJT Analysis: this sheet contains the results of conjoint analysis (CJT).
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 a full profile conjoint design with XLSTAT

Choosing the factors

The first step is the choice of important characteristics that define an ice cream. This is done in collaboration with experts in the ice cream market. The selected factors are :
1. Flavor (Raspberry, Mango, Strawberry, Vanilla, Chocolate)
2. Packaging (Cone, Homemade waffle, Pint)
3. Is the ice cream Low fat (Low fat, Not low fat)?
4. Is the ice cream organic (Not organic, Organic)?
By permuting the different levels of these factors, one can get 60 different combinations corresponding to 60 products. Judges (respondents) will not be able to evaluate them all. Therefore, we will use experimental designs to reduce the number of products presented to the respondents. The obtained profiles will be ranked by 15 interviewed people.

Profile selection

XLSTAT-Conjoint analysis uses experimental designs to select a number of profiles and allow interviewed people to make their rankings.
Launch XLSTAT and select the Designs for conjoint analysis function:
A dialog box will then appear. You can now select the Factors/Categories table and the number of profiles to be generated (10).

In the Options tab, we check the option prohibited combinations in order to exclude some combinations of modalities from the survey.
Once you click on OK, a first dialog box appears asking for 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 run and the results are displayed. The first table summarizes the generated model.
The next table shows the conjoint design starting with the profiles on the left part. The right part of the table has to be filled with the rankings of each respondent.

Setting up a full profile conjoint analysis with XLSTAT

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 / Conjoint analysis.

Once you click on the OK button, the computations are performed and the results are displayed.

Interpreting the results of a full-profile conjoint analysis

The most important results are the partial utilities as well as the individual importances. These are calculated at an individual level. Their averages are calculated and displayed on charts. These give an idea of the importance of each factor.

We can conclude that flavor and packaging are the most important factors both at the individual level and the average level. More specifically, raspberry flavour and homemade waffle packaging have a largely positive effect (average utilities graph).
XLSTAT-Conjoint allows to segment respondents by using statistical clustering methods. This option allows us to see if homogeneous groups of individuals emerge.

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 an ice cream is analyzed and we would like to know the impact and market shares associated with a new product (named Product 4). This product is an organic strawberry ice-cream which is not low fat and served in a cone. We know that in today's market there are 3 ice-cream 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.
To generate the market table containing the different products, you need to select the Variable information table displayed on the CJT Analysis sheet and indicate the number of products you want to generate. Then click OK.

A new window pops up asking to choose the characteristics of each product. When a product has been defined, you can either continue to the next one or stop building the table. Usually, the new product we want introduce to the market is the last one. In our case this is Product 4.
After the market generation, you can start the simulation. To do that, click on CJT / 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 and if the two worksheets have not been manually modified (addition of rows or columns…). Then select any cell of the sheet containing results of conjoint analysis and any cell of the worksheet containing the market generated with XLSTAT. In this example, we select the cells H13 of the CJT Analysis and Market Generator sheets. Then click OK.

You can also select your data manually: Utilities can be found in the CJT Analysis sheet; the table of information about variables is obtained in the CJT 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 on 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 only 9%. This result seems not enough 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 the flavor of the new product is mango instead of raspberry), 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 again button (just below the simulated market table). Market shares and associated charts will be automatically updated.

We can see in this example that market share increases a little, but it's not enough, you should try it with a strawberry flavor and a homemade waffle packaging.
More advanced analyses are possible with XLSTAT (use of segmentation variables, weights, use of statistical clustering methods...).

Was this article useful?