Conjoint Analysis with Prohibited Pairs Using XLSTAT in Excel

 

Background

Conjoint analysis helps identify consumer preferences and simulate market choices. XLSTAT’s Conjoint module enables a streamlined process from experiment design to market simulation, supporting both full profile and choice-based conjoint analyses.

 

Data for Example

1. Select Factors & Levels
   Experts identify product characteristics. In this ice cream case (see attached):

   • Flavor: Raspberry, Mango, Strawberry, Vanilla, Chocolate

   • Packaging: Cone, Homemade waffle, Pint

   • Low Fat: Yes, No

   • Organic: Yes, No
     These yield 60 total combinations.

2. Design the Experiment
   Due to the large number of combinations, XLSTAT generates a reduced set of 10 profiles using a suitable experimental design (e.g., fractional factorial or D-optimal).

3. Collect Rankings
   15 respondents rank the selected profiles, recorded in Excel.

4. Analyze Data
   Apply regression methods like MONANOVA, multinomial logit, or conditional logit to estimate utilities.

5. Simulate Market Scenarios
   Use methods such as first choice, logit, or randomized first choice to simulate market shares.

Data Sheets Overview

• Factors: Defines product attributes.

• CJT Design: Shows generated profiles and respondent rankings.

• CJT Analysis: Contains utility estimation results.

• Market Generator: Defines potential market configurations.

• Market Simulation: Displays simulated market shares.

 

Setting Up

1. Open XLSTAT.

2. Choose Designs for Conjoint Analysis.

3. Input the factor table and set number of profiles (e.g., 10).

4. Proceed with data collection and analysis.

• To exclude certain combinations in the conjoint design:

  1. Go to the Options tab and check Prohibited combinations.

  2. Click OK—a dialog box will appear.

  3. Select Chocolate (Flavor) and Low fat (Fat content) on the left.

  4. Click Add to define the restriction.

  5. Click OK to finalize and apply the prohibited combination.

  This prevents respondents from evaluating unrealistic or undesirable product combinations.

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

  1. A model summary table of the generated profiles.

  2. A summary table of prohibited combinations, confirming the excluded pairings.

 

Running the Full Profile Conjoint Analysis in XLSTAT

1. Fill the CJT Design sheet with the rankings from the 15 respondents.

2. Alternatively, use individual sheets with automatic referencing.

3. Once completed, click Run the analysis to launch the interface with pre-loaded data and begin the analysis.

• After clicking OK, XLSTAT performs the computations and displays the analysis results.

Interpreting The Results

• The key results are the partial utilities and individual importances, calculated per respondent and shown in the resulting tables.

• The average partial utilities and factor importances are calculated and displayed as charts, providing insights into which attributes most influence consumer preferences.

• Conclusion and Segmentation

  Flavor and packaging are the most influential factors, with chocolate or raspberry flavors and homemade waffle packaging showing strong positive utility.
  XLSTAT-Conjoint also offers clustering to segment respondents and identify homogeneous preference groups.

Market Simulation with XLSTAT-Conjoint

A key advantage of conjoint analysis is the ability to simulate markets with untested products.

In this case, we assess the impact of Product 4—an organic, raspberry, not low fat ice cream in a homemade waffle—within a market of 3 existing products with varying attributes. The simulation estimates potential market shares for each product based on consumer preferences.

• To create the market table for simulation:

  1. Open the Market Generator tool.

  2. Select the Variable Information table from the CJT Analysis sheet.

  3. Enter the number of products to simulate.

  4. Click OK to generate the market table.

• A window appears to define each product’s characteristics. Define all products—ending with the new one (Product 4).

  After generating the market table:

  1. Go to CJT / Conjoint analysis simulation tool.

  2. Select the market data to begin the simulation.

• To simplify data selection:

  1. Click the magic stick icon to auto-load the four required datasets (Utilities, Variable Info, Simulated Market, Product ID).

  2. Ensure the CJT Analysis and Market Generator sheets are unmodified.

  3. Select any cell from each sheet (e.g., H13 in both).

  4. Click OK to proceed with the simulation.

• To manually select data for simulation:

  1. Utilities and Variable Info: from the CJT Analysis sheet.

  2. Simulated Market: from the Market Generator sheet (exclude product names).

  3. Product Names: select separately via the Product ID button.

  4. Choose the Full profile model and Logit method for simulation.

• After clicking OK, calculations run and results are displayed.

• The table shows that the market share for the new product (product 4) is 33%. This result seems satisfactory in order to launch the product on the market.

• To test variations of the new product, simply edit the last row (blue) in the Simulated Market table by clicking on the categories you wish to change (e.g., switch flavor to mango). This updates the simulation inputs.

• After modifying the categories, click Run again (below the Simulated Market table). Market shares and charts update automatically.

• In this example, the mango flavor led to a slight drop in market share, suggesting raspberry is the better choice.

  XLSTAT also supports advanced analyses like segmentation variables, weights, and clustering methods for deeper insights.