Table of Contents

 

Using Dummy Variable Regression in Excel To Perform Conjoint Analysis

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



The 6 Steps of Using Dummy Variable Regression to Perform Conjoint Analysis


   Step 1) List All Product Attributes For 1 Product

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

   Step 3) Have Consumer Rate Each Attribute Combination

   Step 4) Prepare Completed Survey for Regression

      Dummy Variables to Be Removed From Input Data To Prevent Collinearity

   Step 5) Run Regression in Excel

   Step 6) Derive Attribute Utilities From Regression Output


An Example of Using a Dummy Variable

The Problem of Collinearity - and How To Solve It

The Product Utilities - The Measure of Customer Liking





How To Quickly Read the Output of Regression in Excel

Step-By-Step Video About How To Quickly Read and Understand the Output of Excel Regression

The 4 Most Important Parts of Regression Output


1) Overall Regression’s Accuracy

R Square

Adjusted R Square



2) Probability That This Output Was Not By Chance

Significance of F


3) Individual Regression Coefficient Accuracy

P-value of each coefficient and the Y-intercept


4) Visual Analysis of Residuals

Charting the Residuals

The Residual Chart





Logistic Regression Analysis in Excel

Customer Quality Scores Are Created With Logistic Regression

Step-By-Step Video Showing How To Predict if a Prospect Will Buy Using Logistic Regression in Excel:

What is Logistic Regression?

An Example of Logistic Regression In Action

Create the Predictive Equation

The Logit

Calculating the Logit Variables - A, B, and Constant

Optimizing the Logit Variables in the Excel Solver

The Final, Most Accurate Predictive Equation

You'll Have To Tweek the Constraints in the Excel Solver


The Four Steps of Regression in Excel (Including 2 Crucial Ones Always Skipped)

Step-By-Step Video Showing How To Do All 4 Steps of Regression in Excel, Including the 2 Crucial Initial Steps That No One Does

   Crucial Step 1) Graphing the Data

   Crucial Step 2) Running Correlation Analysis on All Variables Simultaneously

      Remove Input Variables That Have Low Correlation With Output Variable

      Remove Inputs Variables Highly Correlated With Other Input Variables

      Adding New Input Variables To The Regression Analysi


   Step 3) – Run the Regression in Excel


   Step 4) Analysis of Excel Output





How To Do Nonlinear Regression Using the Excel Solver

The Solver dialogue box has the following 4 parameters that need to be set:

   Objective:

   Decision Variables:

   Constraints:

   Selection of Solving Method: GRG Nonlinear


Solver Tips


Initial Solver Settings:

   Show Iteration Results:

   Use Automatic Scaling:

   Assume Non-Negative:

   Bypass Solver Reports:


 

Meet Mark the Author



Check Out the Latest Manuals in the Excel Master Series