Assignment 7: Conjoint Analysis

Please finish the following assignments in Excel and submit the Excel file onto Pilot.

There are two respondents on Coke and Pepsi in data conjoint_practice.xsls. The two products are characterized by the following attributes

• Price (4 levels): $3.50, $3.75, $4.00, $4.25
• Brand: Coke vs Pepsi
• Type: Diet vs Regular

Please run conjoint analysis for the two respondents, and answer the following questions on worksheet Question. (Hints: you do not have to code the price. Instead, you can use it directly)

1. About the data: different from the conjoint analysis (3) video (the coke pepsi analysis), here we have only two respondents. So your conjoint analysis will be done on these two respondents (column E and F in the data)

2. Another difference from the coke-pepsi analysis is the organization of the data. Here we are putting the responses and the product profiles in one worksheet. So you need to make some changes in finishing the assignment:

a. Code the product profiles. There will be 20 profiles, with price, and two dummies for coke vs. pepsi and regular vs. diet. Please refer my coded profiles in the "partial solution" workbook.

b. Ratings: there the ratings are organized vertically while they are organized horizontally in the video example. So we need to make some changes to the INDEC function used for picking up the ratings. Basically the second and third parameters for the INDEX function. Please refer to the formula used in cell Q3 in the "partial solution" workbook.

c. Customer ID: the customer IDS are not given explicitly. Here we have two solutions: (1) Using the What-IF function: you can assume respondent 1 has customer ID 1, and respondent 2 has customer ID 2. Then you can use them in the respondent index (cell Q1), the INDEX function (cell Q3), and the what if function (cell S3 and S4). (2) Without WHTT_IF function: the purpose of what-if function is to repeat the linear regression automatically when there are large amount of respondents. Since we have only two respondents, you do not have to use What-IF function. Instead, you can run the LINEST function twice, one for each respondent (please change the dependent variable in the first parameter accordingly). In this way, you can get two sets of parameters.

The question "Describe how these two individuals are different in their preference." is all about the differences of three coefficients from the linear regression. For example, if the price coefficient of the first respondent is more negative than the second respondent, you can say "the first respondent is more price sensitive than the second one". Similarly, you can compare their preferences for coke vs. pepsi, and regular vs. diet.

Question: Describe how these two individuals are different in their preference.

Show the steps please

Price	Coke or Pepsi	Diet or Regular	Respondent 1	Respondent 2
$3.50	C	D	18	20
$3.75	C	D	16	19
$4.00	C	D	13	17
$4.25	C	D	10	14
$4.50	C	D	7	11
$3.50	C	R	20	15
$3.75	C	R	19	12
$4.00	C	R	17	8
$4.25	C	R	15	5
$4.50	C	R	12	3
$3.50	P	D	9	18
$3.75	P	D	6	16
$4.00	P	D	4	13
$4.25	P	D	2	9
$4.50	P	D	1	6
$3.50	P	R	14	10
$3.75	P	R	11	7
$4.00	P	R	8	4
$4.25	P	R	5	2
$4.50	P	R	3	1