How To Use Excel For Conjoint Analysis

Assuming you know what Conjoint Analysis is, this post focuses on how you can use Excel to estimate part worth utility based on a simple example.


We will start off with another assumption that you've already known:

-          the attributes of your product,

-          the possible combinations and

-          the preference from your customers on those combinations

The example that I am going to illustrate is a product that has the following attributes:

·         Brand:Toshiba, Coby and Sony

·         Color: Red and Blue

·         Price:  $50, $100, and $150

With those attributes, there are 18 possible combinations.

            3 Brands x 2 Colors x 3 Prices = 18 Combinations

Customers' preference is recorded on a scale from 0 to 10, with 10 being the highest degree of preference, and 0, the  lowest (please see below)

Our task is the estimate the part-worth utilities

Now it's time for Excel to come to action:

Step 1: Enter the information, including all the combinations and the rating for each of the combination into Excel

Exhibit 01: Raw data

Step 2: Create dummy variable coding. we will be setting one attribute of  each variable to zero. In this example,

-          for Brand, you set Coby to zero

-          for Color, you set Red to zero

-          for price, set $50 to zero

In Excel we will create additional columns representing the remaining the attributes:

-          for Brand, you will have columns for Toshiba and Sony

-          for  Color, you will have Blue

-          for Price, you will have $100 and $150

Here are the headers of the new columns:

Exhibit 02: Additional columns representing variables

Now we will code 1 and 0. In column Toshiba, you will reference column Brand. Whenever the value in  Brand is "Toshiba", the value in Toshiba will be 1, otherwise it is zero. You can use an IF statement to express that rule:

Exhibit 03: IF statement for Toshiba

Copy that IF statement down your table, you should see this:

Exhibit 04: Dummy codes for brand Toshiba

Notes: 

• Do NOT think of numbers 1 and zero here as numerical values. Zero doesn't mean that it is smaller than 1. Zero only means this combination, the brand  is not Toshiba and 1 means the brand is Toshiba 
• Further note: you can, of course, use absolute cell reference in this task if you don't want to type the word "Toshiba". Your IF statement will look like this: 

Exhibit 05: Another expression of IF statement

Now you do the same with columns Sony, Blue, $100 and $150. The IF statements will look like this:

Exhibit 06: An illustration of IF statements for all the variables

 

and the whole table should look like this:

Exhibit 06: Dummy codes for all the attribute combinations

 

Step 3: Run Multiple Linear Regression:

- Switch to Data tab

- Click on Data Analysis (this is an add-in)

- Choose Regression from the pop-up window

Exhibit 07: Where to find Regression tool

 

- Click OK, we should see this Regression dialog box:

Exhibit 08: Regression wizard

The Input Y Range and Input X Range might not be as empty as above. Whatever you see there, if any, disregard them because you will have to define them using the table you have just set up. In this case your Y Range will be the column that has the header Preference, and your X Range will be all the columns Toshiba, Sony, Blue, $100, and $150. Such selection will look like this with my table:

Exhibit 09: How to fill in Regression wizard

Note: if you selected the column headers like I do, you have to check "Label" as seen above, if you didn't, uncheck it. I prefer to pick the columns with headers. For simplicity, leave the rest unchecked as seen above.

Click OK, and voila, you've got this regression output table:

Exhibit 10: Regression output table

 I assume that you can read what the table means. If not, wait until my next post.