How To Use Dummy Variable Regression In Excel To Do Conjoint Analysis

How To Use Dummy Variable Regression in Excel To Do Conjoint Analysis

Learn to use Dummy Variable Regression in Excel to do Conjoint Analysis in this easy-to-follow, step-by-step instructional article. This is a great technique that marketers use to find out what attribute of their products customers like the most. Conjoint Analysis quantifies a customer's degree of liking for each product attribute. Here's how to do it.

Introduction To Conjoint Analysis Done In Excel Using Dummy Variable Regression

Conjoint Analysis is a great tool for marketers. Conjoint analysis quantifies how desirable each product attribute choice is relative to the other available choices for a single product. In other words, the marketer learns which product choices a consumer values most and by how much. In this article and the linked video, you will learn exactly how to perform Conjoint Analysis in Excel using Dummy Variable Regression. That may sound like advanced stuff but it's really quite a bit simpler than you might imagine.

This video will make the entire procedure of doing Conjoint Analysis in Excel much easier to understand:

Video - How To Do Conjoint Analysis in Excel Using Dummy Variable Regression

( Is Your Sound On? )

Step-By-Step Video Showing How To Perform Conjoint Analysis Using Dummy Variable Regression in Excel In Order To Find Out Which Product Attributes Your Customers Value The Most

How You Can Use Dummy Variable Regression in Excel to Perform Conjoint Analysis: by excelmasterseries | video info

16 ratings | 19,782 views

curated content from YouTube

The 6 Steps of Performing Conjoint Analysis

The ultimate objective of Conjoint Analysis is quantify the consumer's degree of liking for each of the choices for one product. The "Utility" of an attribute is the value associated with the consumer's degree of liking for that choice.

A brief explanation of how Conjoint Analysis and Dummy Variable Regression are used together to arrive at the Utility for each product attribute is as follows below and also in the linked video above:

Step 1) List All Product Attributes For 1 Product

The marketer lists all of the available choices that a consumer has for one product. The marketer starts by listing all of the overall attribute categories such as color and add-ons. The marketer then lists all of the available choices within each attribute category. For example, here the marketer would be listing all available colors and add-ons.

Step 2) Make a List of All Possible Combinations of Those Attributes

The marketer creates the above list of all possible combinations of choices available to the consumer for that one product.

Step 3) Have Consumer Rate Each Attribute Combination

Example of a Completed Survey

This list of all possible combinations is handed to the consumer. The consumer rates each combination on a scale of 1 (least desirable) to 10 (most desirable).

Step 4) Prepare Completed Survey for Regression

Dummy Variables to Be Removed From Input Data To Prevent Collinearity

The survey results are arranged so that Dummy Variable Regression can be run on them. Each product choice is assigned its own Dummy Variable and one Dummy Variable from each overall attribute category is removed. This will be explained below and also in more detail in the linked video.

Dummy Variables in a regression are variables that can only assume two values. One Dummy Variable must be created for each product choice.

Step 5) Run Regression in Excel

Dummy Variable Regression is then run on the survey results data.

Step 6) Derive Attribute Utilities From Regression Output

Excel Regression Output

The Utility for each product attribute is derived directly from the coefficients of the resulting regression equation.

How To Derive Utilities From the Output

An Example of Using a Dummy Variable

For example, if the product comes only in the colors red and white, There will be a Dummy Variable for red and one for white. The Dummy Variable for the color red can take values of only 1 or 0 because the product will either be red or not. The same applies for the white Dummy Variable, and all other dummy variables.

When the survey is returned, the survey data is converted into the proper layout for the Regression function in Excel. Each Dummy Variable assigned to a specific attribute will be assigned the value of 0 or 1, depending on whether that attribute was an element of the combination that is currently being rated. Watching this done in the linked video is probably the easiest way to understand how to do it.

The Problem of Collinearity - and How To Solve It

One problem can occur when Dummy Variables are inputs to a regression. The problem of Collinearity or Multicollinearity occurs when any independent variable can be used to predict the value of any other independent variable. For example, if the product comes in only red or white, you can predict whether the product is red if you know whether or not the product is white. This is Collinearity.

Collinearity and Multicollinearity are corrected by removing one Dummy Variable from each choice category. For example, if color choices are red or white, the Dummy Variable for one of those colors would be removed. Collinearity is then solved. You cannot predict whether of not the product is red if you do not know whether the product is white (because the Dummy Variable for white has been removed).

The data can now be run as a regular regression using Excel's regression tool. The linked video shows how to do this in detail.

The regression is run and a regression equation is obtained.

The Product Utilities - The Measure of Customer Liking

The "Utilities" of each of the product choices are set to equal the value of the coefficients of the regression equation. The "Utility" is the degree of liking that the consumer attached to that product choice.

For example, the marketer will find out how important the color red was compared to each of the other product choices during the purchase decision. Utilities of product choices that were associated with the Dummy Variables that were removed to prevent collinearity will be assigned the value of 0.

We now have Utilities for each attribute. Now, the overall attractiveness of a particular combination of choices can be calculated by adding up the individual Utilities associated with the each of the choices. The sum of the Utilities for each combination is the regression's prediction of consumer's degree of liking for that combination of product choices.

The removal of the individual Dummy Variables does not affect the accuracy or completeness of the answer. Adding up the Utilities for each combination will produce a figure that will be very close to the consumer's actual rating for that combination. An example of this is shown in the video.

Comparing the Predicted Score With The Actual Score - With Dummy Variable Removed

Showing the Regression Equation Predicts Nearly the Same Score as the Customer's Ranking of Card 13, Even Though Dummy Variables

By Far, The Clearest Graduate-Level Statistics Lessons - All In Excel! ---> Click On Image To See Reviews !

The Excel Statistical Master eBook - Over 400 Pages and Videos of Easy-To-Understand, Step-By-Step Business Statistics in Excel - Download It Now !

Excel Statistical Master - Over 400 Pages and Videos of Easy-To-Understand Statistics

1) It's LOADED With Completed Problems ALL in Excel.

2) All Solved Problems Are REAL-WORLD, BUSINESS Problems.

3) Nothing But SIMPLE Explanations.

4) More Than Half of the Lessons Are Supplemented With VIDEOS To Help You Understand Quicker.

5) Lessons Are All in BITE-SIZE Chunks.

6) The Statistics Are MBA-LEVEL - Over 400 Pages and Videos.

7) All Lessons Are In CLEAR, EASY-TO-FOLLOW FRAMEWORKS.

8) NO USELESS THEORY Is Taught.

9) NO PRIOR STATISTICAL KNOWLEDGE Is Needed.

10) Every Lesson Is Entirely In Excel. NO LOOKING UP ON CHARTS.

11) You Already Know Excel. NO NEW SOFTWARE NECESSARY.

Wanna Be a Master of Business Statistics FAST ? Check Out My Other Lenses !

My Other Lens That Will Make You a Master of Business Statistics - FAST !

The 22 Most Common Mistakes Made During Statistical Analysis: If you make any of these mistakes, you'll either get a totally wrong answer or you will spend way more time than necessary getting to the right answer.
Excel's Easiest and Most Robust Normality Test - The Chi-Square Goodness-of-Fit Test: This Normality test is very easy to implement in Excel. This lens explains in easy-to-follow instructions exactly how to perform the test and also how the Chi-Square Goodness-Of-Fit Test actually works.
The 7 Biggest Reasons That Your Data Is Not Normally Distributed: Here is a list of the main (but correctable) reasons that your data does not appear to be normally distributed. If your data is normally distributed, you can perofrm most statistical tests on that data. This lens tells you why your data doesn't appear to be normally distributed and what you can do to correct that.
How To Use Dummy Variable Regression in Excel To Do Conjoint Analysis: Conjoint Analysis is a great marketing technique that will tell which products attributes your customers like the best and also quantifies the customers' degree of liking of each attribute. This lense shows exactly how to do it in Excel. This lens has a video to make it easier to understand.
Taguchi Testing - Is It a Good Tool For Landing Page Optimization?: This lens explains in simple terms exactly what Taguchi testing is and why it is or is not a good tool for landing page optimzation.
How To Create a User-Interactive Graph Of the Normal Distribution in Excel: You will see in step-by-step, easy-to-follow lessons, which includes a video, exactly to create a user-interactive graph of the normal distribution in Excel. The user will be able to vary the mean and standard deviation and watch those changes reflected in the Excel graph that you will be able to make after viewing this lens.
How To Use the t Test in Excel To Tell If Your New Marketing Is Working: The t Test is the easiest tool in the world to tell if your new marketing or advertising campaign really worked. Here's how to do it in Excel.
How To Find Out What Makes Your Customers Buy WIth the Chi-Square Independence Test In Excel: The Chi-Square Indepedence Test is a versatile test for determining if one thing is related to the other.l Here's how to do it in Excel, This lens has a video in it to make it easy to understand.
How To Quickly Read the Output of Regression in Excel: The output of regression in Excel in complicated. Here is how to quickly look at this output and identify the 4 main, important parts and what they mean. This lens has a video in it for easier understanding.
Download the 400+ Page eManual About How To Do Business Statistics in Excel: The 400+ page eManual, The Excel Statistical Master, simplfies business statistics by presenting everything in frameworks that are easy-to-follow and easy-to-duplicate. Loaded with worked-out problems, this eManual focuses on solving real-world business statistics problems. Everything is conveniently solved with Microsoft Excel.

Links To Learn Business Statistics At a More Basic Level - These Lessons Are Easy-To-Follow !

Business Statistics Made SIMPLE - For Those Who Don't Speak Math !

If you are interested in learning business statistics quickly and being able to solve real-world statistics problems, these links are for you.

Confidence Intervals For the Business Manager: Confidence Intervals made easy-to-understand for the business manager. Lots of solved problems. Each problems solved with a step-by-step framework that you will be able to duplicate and use quickly. No wasting time with academic theory that is not useful for solving problems.
The Normal Distribution For the Business Manager: How to use the Normal Distribution to solve real-world business problems. Lots of worked-out problems solved with and easy-to-follow- step-by-step framework. The emphisis is entirely on solving problems with no time wasting on complicated acedemic theory that is not useful for problem solving. Loaded with solved problems !
Prediction Using Regression For the Business Manager: Regression analysis has a lot of important uses in business. This lesson show you in detail how to perform regression without confusing you. Loaded with solved problems, this lesson teaches regression in an easy-to-follow, step-by-step method that you will pick up right away.
How To Solve ALL Hypothesis Tests in Only 4 Steps in Excel: This lesson presents a framework that shows you show to solve ALL of the many kinds of hypothesis tests in only 4 steps in Excel. Extremely useful !
The t Distribution For the Business Manager: The t Distribution is often used for analyzing small samples. It is very close to the normal distribution but very useful when sample size is less than 30.
Combinations and Permutations for the Business Manager - With Lots of Worked-Out Problems !: Combinations and Permutations are extremely useful statistical tools. Anyone who does statistical analysis should know them.
Correlation and Covariance For the Business Manager - With Lots of Worked-Out Problems !: Correlation and Covariance describe linear relationships between different variables. It allows you to quickly determine to what degree the movement of one object is related to the movement over another object.
The Binomial Distribution For the Business Manager - With Lots of Worked-Out Problems !: The Binomial Distribution is one of the most valuable and commonly used statistical tools. The Binomial Distribution is used whenever a process has only two possible outcomes. This is a very common occurrence.
ANOVA for the Business Manager - With Lots of Worked-Out Problems !: ANOVA, Analysis of Variance, is a test to determine whether the means of several groups are equal. As such, ANOVA is often used to determine if three or more different methods or treatments have the same effect on a population. For example, ANOVA testing might be used to determine if three different training methods produce the same sales results with a group of salespeople.
Other Useful Distributions For the Business Manager - With Lots of Worked-Out Problems !: Learn how many other distributions, such as the Weibull and the Poisson Distributions can be used in business.
Download the 400+ Page eManual About How To Do Business Statistics in Excel: The 400+ page eManual, The Excel Statistical Master, simplfies business statistics by presenting everything in frameworks that are easy-to-follow and easy-to-duplicate. Loaded with worked-out problems, this eManual focuses on solving real-world business statistics problems. Everything is conveniently solved with Microsoft Excel.

Squidoo

How To Use Dummy Variable Regression In Excel To Do Conjoint Analysis