Question

Assignment 7: Conjoint Analysis Please finish the following assignments in Excel and submit the Excel file...

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
Choice Price d_coke d_regular Coke or Pepsi Diet or Regular Respondent 1 Respondent 2 Profiles Respondent 2
1 $3.50 $1.00 $0.00 C D 18 20 Choice Price d_coke d_regular Ratings d_regular d_coke Price constant
2 $3.75 $1.00 $0.00 C D 16 19 1 3.5 1 0 20
3 $4.00 $1.00 $0.00 C D 13 17 2 3.75 1 0 19
4 $4.25 $1.00 $0.00 C D 10 14 3 4 1 0 17
5 $4.50 $1.00 $0.00 C D 7 11 4 4.25 1 0 14
6 $3.50 $1.00 $1.00 C R 20 15 5 4.5 1 0 11
7 $3.75 $1.00 $1.00 C R 19 12 6 3.5 1 1 15
8 $4.00 $1.00 $1.00 C R 17 8 7 3.75 1 1 12
9 $4.25 $1.00 $1.00 C R 15 5 8 4 1 1 8
10 $4.50 $1.00 $1.00 C R 12 3 9 4.25 1 1 5 d_regular d_coke Price constant
11 $3.50 $0.00 $0.00 P D 9 18 10 4.5 1 1 3 Customer 0 0 0 0
12 $3.75 $0.00 $0.00 P D 6 16 11 3.5 0 0 18 1
13 $4.00 $0.00 $0.00 P D 4 13 12 3.75 0 0 16 2
14 $4.25 $0.00 $0.00 P D 2 9 13 4 0 0 13
15 $4.50 $0.00 $0.00 P D 1 6 14 4.25 0 0 9
16 $3.50 $0.00 $1.00 P R 14 10 15 4.5 0 0 6
17 $3.75 $0.00 $1.00 P R 11 7 16 3.5 0 1 10
18 $4.00 $0.00 $1.00 P R 8 4 17 3.75 0 1 7
19 $4.25 $0.00 $1.00 P R 5 2 18 4 0 1 4
20 $4.50 $0.00 $1.00 P R 3 1 19 4.25 0 1 2
20 4.5 0 1 1

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)

More Information:

. 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.
0 0
Add a comment Improve this question Transcribed image text
Answer #1

Reference Tables:

Results of Regression Analysis for Both respondents are given Below :

Answer - A. Respondent 2 is more price sensitive than respondent 1.

Add a comment
Know the answer?
Add Answer to:
Assignment 7: Conjoint Analysis Please finish the following assignments in Excel and submit the Excel file...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • 7. Producer surplus for an individual and a market Suppose the market for cheesecake is a...

    7. Producer surplus for an individual and a market Suppose the market for cheesecake is a perfectly competitive market-that is, sellers take the market price as given. Manuel owns a restaurant where he sells cheesecake. The following graph shows Manuel's weekly supply curve, represented by the orange line. Point A represents a point along his supply curve. The price of cheesecake is $3.00 per slice, as shown by the horizontal black line. Manuel's Weekly Supply 5.00 4.00 3.50 3.50 3.00...

  • I'm confused on this assignment, I'm not certain on what formulas on excel I need to...

    I'm confused on this assignment, I'm not certain on what formulas on excel I need to use for this? Part 1: Loan Affordability Analysis Create a monthly payment table with changing interest rate and loan amount and allow users to change the term and affordable payment as below and use the conditional formatting to highlight the area of rate/loan that is affordable (less than or equal to the affordable payment) with green color and area that is not affordable with...

  • Please use R program to solve with explanation. Enterprise Industries produces Fresh, a liquid landry detergent....

    Please use R program to solve with explanation. Enterprise Industries produces Fresh, a liquid landry detergent. The company wishes to study the relationship between price and demand for the large size bottle of Fresh in its sales regions. The company has gathered data (see Table) concerning demand for Fresh in 30 sales regions of equal sales potential. i = 1,2,.,30 ythe demand for the large size bottle of Fresh (in hundreds of thousand) in sales region i Xithe price (in...

  • Re-save the file to either your desktop or other storage device using the name“firstName_LastName_L4_Titan_Property”. (Note firstName...

    Re-save the file to either your desktop or other storage device using the name“firstName_LastName_L4_Titan_Property”. (Note firstName and LastName are your own first and last names). The upper left area of the worksheet is a Payment Calculator. Use a function to calculate the monthly payment (D7) using the data provided. In the Payment Calculator, use “Goal Seek” to keep the payment per month at $3,000 by increasing thedown payment. Copy the info from B4:B8 and paste to cells B10:B14. Copy D4:D7...

  • just the graph please 10. Problems and Applications Q10 A friend of yours is considering two...

    just the graph please 10. Problems and Applications Q10 A friend of yours is considering two cell phone service providers. Provider A charges $110 per month for the service regardless of the number of phone calls made. Provider B does not have a fixed service fee but instead charges $1 per minute for calls. Your friend's monthly demand for minutes of calling is given by the equation Qd = 100 – 20P, where P is the price of a minute....

  • Please provide excel formulas Stat 3309 - Statistical Analysis for Business Applications I Consider the following...

    Please provide excel formulas Stat 3309 - Statistical Analysis for Business Applications I Consider the following data representing the total time in hours) a student spent on reviewing for the Stat 3309 final exam and the actual score on the final. The sample of 10 students was taken from a class and the following answers were reported. time score 0 23 4 30 5 32 7 50 8 45 10 55 12 60 15 70 18 80 20 100 Part...

  • please do 3-7 thank you 2. 3. 4. Repeat steps 0.a.-f. for TITRA2 and TITRA3. Write...

    please do 3-7 thank you 2. 3. 4. Repeat steps 0.a.-f. for TITRA2 and TITRA3. Write the balanced chemical equation for the reaction between your unknown acid and sodium hydroxide. The generalized chemical formula of your acid (HxA) is found on the unknown sample bottle. “X” denotes the number of hydrogen's that react with the hydroxide on the sodium hydroxide. Go back to the TITRA1 D2 EXPANDED graph and determine the equivalence point volume of sodium hydroxide used to titrate...

  • Real analysis 10 11 12 13 please (r 2 4.1 Limit of Function 129 se f: E → R, p is a limit point of E, and li...

    Real analysis 10 11 12 13 please (r 2 4.1 Limit of Function 129 se f: E → R, p is a limit point of E, and limf(x)-L. Prove that lim)ILI. h If, in addition, )o for all x E E, prove that lim b. Prove that lim (f(x))"-L" for each n E N. ethe limit theorems, examples, and previous exercises to find each of the following limits. State which theo- rems, examples, or exercises are used in each case....

  • please answer question #1 7 1. You are given the following equilibrium expected returns and risks...

    please answer question #1 7 1. You are given the following equilibrium expected returns and risks E(R) - 12.2%; E(Re) - 15.5% BA -0.7; Be-1.25. c( 0.460.0615 a. What is the equation of the Security Market Line? b. A portfolio, made up of A (above) and another security, has a beta of 1.10 and expected return of 13%. Which one would you rather buy - A alone or the portfolio? Why? Ee19. 6 - OVAL BYA c. Given the SML...

  • can you help me solve the highlighted areas with excel formulas Module 5 Student Version 4/4/96...

    can you help me solve the highlighted areas with excel formulas Module 5 Student Version 4/4/96 Financial Statements for the Year Ended December 31, 1995 (Millions) ACE REPAIR, INC. Cost of Capital (Easy ERSION Cash & Sec. A/R Inventory $5.0 46.3 74.1 A/P Accruals N/P $39.0 14.7 35.5 This case illustrates the cost of capital estimation process. It demonstrates (1) the mechanics of determining the component costs of capital--specifically debt, preferred stock and common equity, (2) the effects of changes...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT