XLSTAT: Choice Based Conjoint Analysis with Hierarchical Bayes (CBC HB) in Excel

Choice-Based Conjoint HB in Excel: Use XLSTAT to design CBC surveys, collect responses, estimate utilities via Hierarchical Bayes, and simulate market shares for product decisions.

Sep 1, 2026
Knowledge

Title

XLSTAT: Choice Based Conjoint Analysis with Hierarchical Bayes (CBC HB) in Excel

Answer

XLSTAT: Choice Based Conjoint Analysis with Hierarchical Bayes (CBC HB) in Excel

Background

Conjoint analysis is a widely used marketing method for understanding consumer preferences and modeling their product choices. It simulates competitive markets through a single analysis and is essential for product development and marketing strategy.

There are two main types of conjoint analysis:

Full Profile Conjoint Analysis
Choice-Based Conjoint Analysis (CBC)

Using tools like XLSTAT, the process involves five key steps:

Selecting product attributes and their levels.
Creating an experimental design (e.g., full/fractional factorial, D-optimal).
Collecting responses via Excel-based surveys.
Analyzing data with methods like MONANOVA, multinomial logit, conditional logit, or hierarchical Bayes.
Simulating market scenarios using models such as first choice, logit, Bradley-Terry-Luce, or randomized first choice.

Both conjoint methods follow this analytical framework.

Dataset for Example

The (attached) Excel spreadsheet contains the results of this example. The results are divided into different sheets:

Factors: The characteristics of the selected factors.
CBC Design: The profiles generated, and the choices given by the 10 individuals.
CBC: The results of conjoint analysis by hierarchical Bayes approach (CBC).
Simulated Market: The complete market to simulate.
Market Simulation: The results of the market simulation.

Setting Up

This example illustrates how conjoint analysis supports product development in a competitive beverage market.

A soft drink brand wants to launch a new tea and uses conjoint analysis to identify:

The most appealing product characteristics.
The best combination to gain market share.

Step 1: Define Product Attributes

Experts select four key factors:

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 (3 × 3 × 2 × 3). Since it's impractical to test all, a choice-based conjoint (CBC) approach is used.

Step 2: Experimental Design with XLSTAT

Using XLSTAT’s conjoint module:

Profiles to classify: 12
Number of comparisons: 20 (must exceed profiles)
Profiles per comparison: 3

The software generates choice sets, allowing respondents to pick preferred products from realistic options. This setup enables analysis of consumer preferences and market simulations.

In XLSTAT’s Options tab, select the D-Optimal design for an efficient and informative subset of profiles. Alternatively, choose Orthogonal designs to access fractional factorial options.

Output:

First table: Summary of the generated model (design type, number of factors, levels, profiles, etc.)
Second table: List of generated profiles, each representing a unique product configuration to be evaluated by respondents.

This setup ensures meaningful comparisons while reducing respondent fatigue.

The choice table, located in the "CBC" sheet, is completed after respondents are interviewed.

Each respondent selects 1 option out of 3 per comparison, with choices recorded as 1, 2, or 3.

The left-side numbers link to the corresponding profiles in the profile table, enabling analysis of preferences based on actual product configurations.

Step 3: Fill the Conjoint Analysis Tables

In CBC, responses can be entered in two ways:

Manually, after external interviews.
Automatically, using individual sheets with results linked via formulas.

The latter is recommended for CBC due to the complexity of the overall table, ensuring accurate and efficient data entry.

Step 4: Run the Conjoint Analysis

Before collecting all responses, validate the design by:

Answering a test set of questions.
Running the model to ensure utilities are properly displayed.

Then proceed with full data collection.

To analyze:

Click the CJT icon → select Conjoint Analysis based on Choice.
Input:
- Response table: 10 columns of individual answers.
- Choice table: 3 columns with selection numbers only.
- Profile table: Full list of product profiles.
Set Method to Hierarchical Bayes for robust estimation of individual-level preferences.

In the Options tab, set up the Bayesian options and Stop conditions .

Key Results: Utilities and Importances

In Hierarchical Bayes analysis, the utilities (part-worth values) and importances of each factor are shown per individual, unlike the classical conditional logit model, which provides aggregate results.

These tables—found at the top of the output—reveal:

How much each respondent values specific attribute levels.
The relative importance of each factor in decision-making.

This shows that most of the individual consider that the temperature factor is an important factor in their process choice. About the utilities, we note that having one sugar has a negative effect. If needed, the detail for each individual can also displayed.

Step 5: Market Simulation

Conjoint analysis allows you to simulate market behavior without needing to test real products.

In this case, you're evaluating the impact and potential market share of a new tea product:

Medium, iced, with lemon, 1 sugar

The simulation includes this product along with 4 existing market alternatives, each with different attribute combinations. The simulation table lists all 5 product profiles.

Using the simulation tools in XLSTAT (e.g., first choice, logit, or randomized first choice methods), you can estimate:

Preference shares
Predicted market shares
Competitive impact of the new product

Running the Market Simulation

Click the CJT icon → select Conjoint Analysis Simulation.
Input:
- Utilities: from the CBC sheet.
- Variable info table: also from the CBC sheet.
- Simulated market: select product profiles (exclude product names).
- Product names: optional, added via the Product ID button.
Choose the CBC model.
Set the simulation method to Logit.

This will output predicted market shares for each product.

Interpreting the Results

The table shows the new product captures over 40% market share, indicating strong consumer preference.

This result suggests a high potential for success, supporting a decision to launch the product.

URL Name

6575-choice-based-conjoint-analysis-hierarchical-bayes-cbc