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
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. |
Reference Tables:
Results of Regression Analysis for Both respondents are given Below :
Answer - A. Respondent 2 is more price sensitive than respondent 1.
Assignment 7: Conjoint Analysis Please finish the following assignments in Excel and submit the Excel file...
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 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. 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 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 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 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 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 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 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 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...