MKT Tool 3

profilelance5600
ToolSUmmary3DataCereal.xlsx
Home>Business & Finance homework help>Marketing homework help>MKT Tool 3

cereal ranking

PROFILE Rank X1 X2 X3 X4 X5 X6 X7 X8
1 1 0 0 0 0 0 0 0 0 Original Data from Surveys put in format to run Regression Analysis in Excel - go to next sheet
2 5 0 0 0 1 0 1 0 1
3 6 0 0 1 0 1 0 1 0
4 3 0 1 0 0 0 1 1 0
5 7 0 1 0 1 1 0 0 0
6 4 0 1 1 0 0 0 0 1
7 2 1 0 0 0 1 0 0 1
8 8 1 0 0 1 0 0 1 0
9 9 1 0 1 0 0 1 0 0
1 1 0 0 0 0 0 0 0 0
2 6 0 0 0 1 0 1 0 1
3 9 0 0 1 0 1 0 1 0
4 4 0 1 0 0 0 1 1 0
5 8 0 1 0 1 1 0 0 0
6 5 0 1 1 0 0 0 0 1
7 3 1 0 0 0 1 0 0 1
8 2 1 0 0 1 0 0 1 0
9 7 1 0 1 0 0 1 0 0
1 1 0 0 0 0 0 0 0 0
2 4 0 0 0 1 0 1 0 1
3 9 0 0 1 0 1 0 1 0
4 2 0 1 0 0 0 1 1 0
5 8 0 1 0 1 1 0 0 0
6 7 0 1 1 0 0 0 0 1
7 5 1 0 0 0 1 0 0 1
8 3 1 0 0 1 0 0 1 0
9 6 1 0 1 0 0 1 0 0
1 4 0 0 0 0 0 0 0 0
2 8 0 0 0 1 0 1 0 1
3 9 0 0 1 0 1 0 1 0
4 5 0 1 0 0 0 1 1 0
5 6 0 1 0 1 1 0 0 0
6 7 0 1 1 0 0 0 0 1
7 1 1 0 0 0 1 0 0 1
8 2 1 0 0 1 0 0 1 0
9 3 1 0 1 0 0 1 0 0
1 1 0 0 0 0 0 0 0 0
2 8 0 0 0 1 0 1 0 1
3 3 0 0 1 0 1 0 1 0
4 2 0 1 0 0 0 1 1 0
5 5 0 1 0 1 1 0 0 0
6 9 0 1 1 0 0 0 0 1
7 4 1 0 0 0 1 0 0 1
8 6 1 0 0 1 0 0 1 0
9 7 1 0 1 0 0 1 0 0

&"Helvetica Neue,Regular"&12&K000000&P

Regression Output

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.7402702209
R Square 0.548 This sheet is the output you get after running a regression analysis for Conjoint Analysis data.
Adjusted R Square 0.4475555556
Standard Error 1.9407902171 AdjR2 shows how much data you captured we want it to be .30 or above
Observations 45
Significance F tell me my results are not due to luck if the number is below .05
ANOVA
df SS MS F Significance F Coefficients are the UTILITY numbers that will be rescaled to fall between 0-1 -
Regression 8 164.4 20.55 5.4557522124 0.0001549253 the utility numbers represent how happy a person is with each attribute level.
Residual 36 135.6 3.7666666667 The Intercept is included with UTILITY numbers,
Total 44 300 Take note that the chart does not include the levels that were dropped.
I will add the dropped attribute levels back as 0 and then rescale them with the rest of the numbers - next sheet
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 1.6 0.8679477711 1.8434288943 0.0735111575 -0.1602796677 3.3602796677 -0.1602796677 3.3602796677
X1 -0.4666666667 0.7086763875 -0.6585046079 0.5144036435 -1.9039289968 0.9705956635 -1.9039289968 0.9705956635
X2 0.4666666667 0.7086763875 0.6585046079 0.5144036435 -0.9705956635 1.9039289968 -0.9705956635 1.9039289968
X3 4.0666666667 0.7086763875 5.7383972971 0.0000015546 2.6294043365 5.5039289968 2.6294043365 5.5039289968
X4 3.1333333333 0.7086763875 4.4213880814 0.0000866603 1.6960710032 4.5705956635 1.6960710032 4.5705956635
X5 1.6 0.7086763875 2.2577300841 0.0301228241 0.1627376698 3.0372623302 0.1627376698 3.0372623302
X6 1.2 0.7086763875 1.6932975631 0.0990363362 -0.2372623302 2.6372623302 -0.2372623302 2.6372623302
X7 -0.0666666667 0.7086763875 -0.0940720868 0.9255735344 -1.5039289968 1.3705956635 -1.5039289968 1.3705956635
X8 0.2666666667 0.7086763875 0.3762883474 0.7089111697 -1.1705956635 1.7039289968 -1.1705956635 1.7039289968

Rescale

STEP 1: Find the biggest number under coefficients including Intercept-sometimes the biggest number is the Intercept
Step 2: Find the smallest number
Step 3: Calculate the Range by subtracting the smallest number from the largest number.
Step 4: Rescale using the following formula - =(Coeffient Number - Lowest Number)/Range
Step 5: Round off to 2 digits and then transfer to the next page
Coefficients RESCALED Rounded Off
Intercept 1.6 0.4569536424 0.46
X1 -0.4666666667 0.0007358352 0
X2 0.4666666667 0.2067696836 0.21
X3 4.0666666667 1.0014716703 1
X4 3.1333333333 0.7954378219 0.79
X5 1.6 0.4569536424 0.46
X6 1.2 0.3686534216 0.37
X7 -0.0666666667 0.0890360559 0.09
X8 0.2666666667 0.1626195732 0.16
soggy 0 0.1037527594 0.1
Bad 0 0.1037527594 0.1
Salty 0 0.1037527594 0.1
Grandpa 0 0.1037527594 0.1
Biggest Number 4.06
Smallest Number -0.47
Range 4.53

Final Graph Data

X1 Crunchy
X2 Chewy Texture
Dropped Soggy
This shows the attributes I dropped from each category-soggy , Bad, Salty, Grandpa
X3 Good
X4 Edible Taste
Dropped Bad
X5 Healthy
X6 Sugary Nutrition
Dropped Salty
X7 Children
X8 College Age
Dropped Grandpa
use yellow box to create a column graph - only left X1-X8 so you could see where to put data from now on simply call by the attribute level
FROM PREVIOUS SHEET X1 Crunchy 0
Coeffiecients Rounded Off X2 Chewy 0.21
Intercept 0.46 Soggy 0.1
X1 0
X2 0.21 X3 Good 1
X3 1 X4 Edible 0.79
X4 0.79 Bad 0.1
X5 0.46
X6 0.37 X5 Healthy 0.46
X7 0.09 X6 Sugary 0.37
X8 0.16 Salty 0.1
soggy 0.1
Bad 0.1 X7 Children 0.09
Salty 0.1 X8 College 0.16
Grandpa 0.1 Grandpa 0.1
Intercept 0.46