Conjoint-Analyse in Excel durchführen - Anleitung

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 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-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 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 full profile conjoint analysis with XLSTAT.

Dataset and goal of this conjoint analysis tutorial

In this tutorial 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 to 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?

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

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 180 different combinations corresponding to 180 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, click on the CJT icon and then click on Designs for conjoint analysis.

A dialog box will then appear. You can now enter the name of the analysis, the number of factors (four in our case) and the number of profiles to be generated (10).

In the Factors tab, activate the select on a sheet option and select the data in the Factors sheet. Do not select labels associated to each column.

In the Output tab, individual sheets are not activated since the use of these sheets is not necessary for the tutorial. In a comprehensive analysis though, they can be very useful in order to get the responses filled directly by respondents.

Once you click on OK, a new dialog box appears. This allows you to select a specific fractional factorial design or to optimize the design (D-optimal). Here, we used the optimize option.

Once you click the Optimize button, the calculations run and the results are displayed. The first table summarizes the generated model.

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

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.

Interpreting the results

For the aim of this study, 15 individuals have been questioned about their ice cream preferences. The survey answers can be found in the CJT design sheet and the results of the analysis in the CJT 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 / C****onjoint analysis.

To facilitate data selection and avoid to manually select the two required datasets (Responses and Profiles), 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 CJT Analysis sheet. Then click OK.

You can also select your data manually. In the Responses field, select the 15 columns of the conjoint design corresponding to the individuals rankings (right part of the table). In the Profiles field, select the columns associated to the different profiles (excluding the profile names). Choose the ranking option as response type.

Once you click on the OK button, the computations are performed and the results are displayed. The most important results are the partial utilities as well as the individual importances. These are caclulated at an individual level. They can be found in the below tables.

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, chocolate or 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 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 to a new product (named Product 4). This product is an organic raspberry ice-cream which is not low fat and served in a homemade waffle. 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 as follow:

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. 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 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 of 38%. 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 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 on this example that market share slightly decreased with the mango flavor. So it would be better to include a raspberry flavor in the new product. Many more advanced analyses are possible with XLSTAT (use of segmentation variables, weights, use of statistical clustering methods...).