Market Simulator: Conjoint Analysis with Excel
Last week I’ve decided to practice more on applying multiple regression, but I’m already quite bored of the topic of sales forecasting. This is when conjoint analysis comes to mind. The basic ideas behind conjoint analysis are simply an application of multiple regression, so this will be a perfect practice.
Conjoint analysis enables analyst to determine the product characteristics that drive a consumer’s preference for products. Through conjoint analysis, analyst will be able to learn the relative importance of the product attributes and within each attribute the ranking of the levels.
Today I chose to practice with this data set that shows a consumer ranking on carpet cleaning products. The product attributes compared are package design (3), brand (3), price (3), house keeping magazine (2) approved, and product guarantee (2). In this situation, there are a total of 108 possible product combinations, and 18 product profiles were chosen to be ranked by consumers.
Although this time I’m using an existing conjoint case, it is important to remember that when designing the conjoint model from scratch, we have to adopt an orthogonal design. The product profiles shown to the consumers can’t be chosen randomly. For example, if every profile that is magazine approved also has product guarantee, we won’t be able to tell consumer’s real preference. The attributes have to be uncorrelated to avoid multicollinearity problems.
Now we figure out the relative importance of product attributes using regression with dummy variables. But first we have to rescale the consumer ranking so that the highest ranked product receives a score of 18. If we don’t rescale, the larger regression coefficients would correspond to less preferred attribute, and we don’t want that.Create a new column and subtract 19 from the product’s actual ranking, and there we get the inverse ranking.
To run a multiple linear regression using dummy variables we will have to leave out some arbitrarily chosen level of each attribute (package C, brand 3…etc). Run the forecast =TREND(J21:J38,A21:H38,A21:H38) and later on we will see how well multiple linear regression explains consumer product ranking . Without certain level of attributes, we get to see whether the given level of attribute makes the product less or more preferred than the omitted level of the attribute. Now it’s time to run the regression.
After we run the regression, check our data. All of our independent variables are significant at 0.5 level. R squared value indicates that the attributes explain 98% of the variation in this ranking. Thus this multiple linear regression model works for our conjoint analysis.
Now we look at the other part of the report. Design C leads to a rank 4.5 higher than Design A and 3.5 lower than Design B. Brand 3, leads to a 1.5 higher than Brand 1 and 2 higher than Brand 2. How do we further use this information? To rank the importance of each attribute, we calculate the attribute spread (best level -> worst level).
Tah-dah! We get our final result of the ranking of each attribute. Apparently package design is the most important and that magazine approval is the least important. We can use the result of our conjoint analysis in segmenting the market or perform value-based pricing, but I’ll leave that for another day I guess.
