XLSTAT: Choice Based Conjoint with Multiple Question Sets in Excel

 

Background

A conjoint analysis can be applied in order to answer 2 questions:

What characteristics should the product have in order to

1. please the greatest number of people

2. gain market shares in an already competitive market?

 

Dataset for Example

See the (attached) spreadsheet containing the example.

 

Setting-Up

Step 1: Choosing the Factors

A soft drink brand plans to launch a tea-based beverage and uses choice-based conjoint analysis (CBC) to determine:

• What characteristics will appeal to the most people?

• What combination will gain market share?

Selected factors (with levels):

• Temperature: very hot, hot, iced

• Sugar: no sugar, 1 sugar, 2 sugar

• Lemon: yes, no

• Intensity: strong, medium, light

This results in 54 possible product combinations, too many for respondents to evaluate directly.

 

Step 2: Profile Selection

To reduce the number of evaluations, use experimental design in XLSTAT:

• Open XLSTAT.

• Click CJT and select Design for choice-based conjoint analysis.

In the General tab:

• Define the factors and categories in the Factors/Categories table.

• Set the Number of profiles to classify to 12.

• Set the Number of comparisons to 20. Make sure this number exceeds the number of profiles.

• Set Profiles per comparison to 3.

This generates sets of product profiles for respondents to choose from, forming the basis for preference analysis.

In the Options tab:

• Check Prohibited combinations to exclude unrealistic or invalid attribute combinations.

• Set to generate 2 designs, each with 10 combinations for 5 individuals.

• Since prohibited combinations are applied, only a D-optimal design can be used for optimal efficiency.

• After clicking OK, a dialog box appears for setting prohibited combinations.

To exclude chocolate flavor with lightened ice:

1. Select chocolate and low fat from the left panel.

2. Click Add to define the prohibited combination.

3. Click OK to confirm.

The design will now exclude this invalid pairing.

• After clicking OK, XLSTAT runs the calculations and displays:

  1. First table: Summary of the generated model (design type, number of profiles, individuals, etc.).

  2. Second & third tables: Profile tables for each of the two designs, showing the product combinations assigned to respondents.

The choice table is filled after interviews, where each respondent selects 1 of 3 options per comparison.

• Choices are recorded as 1, 2, or 3.

• Left-side numbers correspond to profile IDs from the profile table.

This links respondent choices to specific product configurations for analysis.

 

Step 3: Filling the Conjoint Analysis Tables

You can fill the CJT design sheet:

• Manually, after interviews.

• Or using individual sheets with automatic referencing, which is recommended for CBC due to the table's complexity.

This ensures accurate and efficient data entry for analysis.

Important: When using the optimized 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

• Once responses from the 10 individuals are entered in the conjoint design sheet, go to the CBC Analysis sheet.

• Click Run the analysis to automatically load the data and start the conjoint analysis process.

• A multinomial logit model (from the conditional logit family) is used for estimation.

The key results are:

• Utilities: show preference strength for each attribute level.

• Importances: indicate the relative weight of each factor.

These results are aggregate, reflecting the preferences of all individuals combined, not individual-level outputs.

The results show:

• Intensity is the most influential factor, with 52% importance.

• In the aggregated utility table, no sugar has a positive utility, indicating a preference for unsweetened tea.