work together particularly on R program

MAC 503/MBA 581 Homework 2 – Due February 15

Again you should write up individually but may work together particularly on R program, please submit as an html file generated by markdown.

1. For the data set pepsicokes posted on BlackBoard, do the following R operations:
2. Bring the file into R;
3. Create a dummy variable whitecollar ( 1 if white collar, 0 otherwise), representing

stores with white collar percent > 60 and calculate frequencies for this variable; the white collar percentages on the dataset are represented by the variable WhiteCollarPctPen.

1. Do a crosstab of Prizm cluster (the 16 social groups) with Chain; and another crosstab of Prizm cluster with whitecollar (use table() function); the social groups are represented on the dataset by the variable prizm_cluster.
2. Calculate descriptive statistics for any 5 continuous variables in the dataset. (you should already know from the previous assignment that what function you must use!)
3. Regress Corp_Pep_Volume_per__MM_ACV (million dollars All Commodity Volume = Total Store Sales) on a set of any 6 independent variables. (use lm() for linear regression)
4. Run a Hierarchical Cluster Analysis using the variables Pepsi Volume per MM ACV, Pepsi Price per MM ACV, Coke Volume per MM ACV, Coke Price per MM ACV. Generate a Scree plot and find the kink point to determine the optimal number of clusters.
5. Run a K-means Cluster Analysis using the number of clusters determined in d.

1. Consider the regression below (and on the next page) that was estimated on weekly data over a 2-year period on a sample of Kroger stores for Pepsi carbonated soft drinks. The dependent variable is the log of Pepsi volume per MM ACV. There are 53 stores in the dataset (data were missing for some stores in some weeks). Please answer the following questions about the regression output.
2. Comment on the goodness of fit and significance of the regression, and of individual variables. What does the ANOVA table reveal?
3. Write out the equation and interpret the meaning of each of the parameters.
4. What is the price elasticity? The cross-price elasticity with respect to Coke price? Are these results reasonable? Explain
5. What do the results tell you about the effectiveness of Pepsi and Coke display and advertising?
6. What are the 3 most important variables? Explain how you arrived at this conclusion.
7. What is collinearity? Is collinearity a problem for this regression? Explain. If it is a problem, what action would you take to deal with it?
8. What changes to this regression equation, if any, would you recommend? Explain.

Model Summary(b)

 Model R R Square Adjusted R Square Std. Error of the Estimate 1 .869(a) .754 .754 .4120

a  Predictors: (Constant), Mass stores in trade area, Labor Day dummy, Pepsi advertising days, Store traffic, Memorial Day dummy, Pepsi display days, Coke advertising days, Log of Pepsi price, Coke display days, Log of Coke price

b  Dependent Variable: Log of Pepsi volume/MM ACV

ANOVA(b)

 Model Sum of Squares df Mean Square F Sig. 1 Regression 2881.089 10 288.109 1697.262 .000(a) Residual 937.695 5524 .170 Total 3818.784 5534

a  Predictors: (Constant), Mass stores in trade area, Labor Day dummy, Pepsi advertising days, Store traffic, Memorial Day dummy, Pepsi display days, Coke advertising days, Log of Pepsi price, Coke display days, Log of Coke price

b  Dependent Variable: Log of Pepsi volume/MM ACV

Coefficients(a)

 Model Unstandardized Coefficients Standardized Coefficients t Sig. Collinearity Statistics B Std. Error Beta Tolerance VIF 1 (Constant) 7.79429 .06249 124.721 .000 Log of Pepsi price -3.34665 .03483 -.739 -96.091 .000 .751 1.332 Log of Coke price .65877 .03170 .181 20.784 .000 .587 1.703 Pepsi advertising days .00173 .00020 .065 8.644 .000 .784 1.275 Coke advertising days -.00009 .00018 -.004 -.502 .616 .689 1.450 Pepsi display days .00011 .00021 .004 .546 .585 .656 1.525 Coke display days -.00299 .00020 -.123 -14.766 .000 .646 1.549 Labor Day dummy .27190 .04167 .045 6.525 .000 .923 1.083 Memorial Day dummy .21295 .04269 .036 4.988 .000 .834 1.199 Store traffic .00000 .00000 .023 3.367 .001 .961 1.040 Mass stores in trade area -.00910 .00026 -.238 -35.161 .000 .968 1.033

a  Dependent Variable: Log of Pepsi volume/MM ACV

1. The tables on the following 4 pages summarize the output of K-Means clustering for the WNY soft drink file that I used to demonstrate regression.

Since clusters should reflect consumer and firm behavior for the focal product category, I used the following bases for clustering: Pepsi Volume per MM ACV, Pepsi Price per MM ACV, Coke Volume per MM ACV, Coke Price per MM ACV. These variables were all standardized to mean 0, standard deviation 1 (Zscores) before clustering.

A 4-cluster solution, which I think provides a good description of this market, is provided below. Output consists of averages of standard scores (Zscores) for each cluster, ANOVA tests of significant differences between these averages across clusters, and number of stores in each cluster.

Descriptor variables for each cluster were taken to be: Currentpop, MedianHHincome,

MedianHomeValue, WhitePopPctPen, BlackPopPctPen, Groc_Miles,

Mass_Miles. Descriptives and ANOVA tests are presented for the continuous variables.  Cross-tabs are presented for chain and prizm social group.

1. Interpret the results of the clustering. Name each cluster, and describe what it stands for. Explain your choices.
2. What can you say from the results about the market for soft drinks, and about the apparent strategies of Tops and Wegmans, and Pepsi and Coke?
3. Develop a strategy for targeting each of the four segments for Pepsi.

K-Means Clustering of WNY Data

Bases

Final Cluster Centers

 Corp_Pepsi_ Corp_Coke_ Volume_per__ Corp_Pepsi_ Volume_per__ Corp_Coke_ Cluster MM_ACV Price MM_ACV Price 1 -0.9092 1.1475 0.8399 -0.8676 2 0.2813 -0.5680 -1.2003 -0.6806 3 -0.1705 -0.0227 -0.1792 1.2173 4 1.5359 -1.1635 0.6072 0.1488 Means are for standardized variables – scale = mean 0, stdev = 1

ANOVA

 Cluster Error F Sig. Variable Mean Square df Mean Square df R-Square Corp_Pep_Volume_per__MM_ACV 29.3050 3 0.3567 132 87.92 0.0000 0.6512 Corp_Pep_Price 30.4930 3 0.3297 132 91.48 0.0000 0.6776 Corp_Coke_Volume_per__MM_ACV 27.7033 3 0.3931 132 83.11 0.0000 0.6156 Corp_Coke_Price 36.1290 3 0.2016 132 108.39 0.0000 0.8029

The F tests should be used only for descriptive purposes because the clusters have been chosen to maximize the differences among cases in different clusters. The observed significance levels are not corrected for this and thus cannot be interpreted as tests of the hypothesis that the cluster means are equal.

Cluster Summary and Number of Cases in each Cluster

 Cluster Frequency in Cluster RMS Std Deviation Maximum Distance from Seed to Observation Nearest Cluster Distance Between Cluster Centroids 1 39 0.4396 1.7172 3 2.7020 2 32 0.4881 2.6067 3 2.2685 3 43 0.5985 2.8373 2 2.2685 4 22 0.7716 2.2536 2 2.4256

Descriptors

Descriptives

 Variable and Cluster N Mean Std Dev Currentpop 1 39 44707.74 17134.62 2 32 39931.44 15379.06 3 43 54964.19 23737.98 4 22 14805.00 14126.24 Overall 136 41989.54 22814.25 MedianHHincome 1 39 48707.69 14214.63 2 32 44103.84 13361.84 3 43 43920.47 12139.50 4 22 36287.27 4185.07 Overall 136 44101.64 12748.94 MedianYrsSchool 1 39 13.48 0.82 2 32 13.15 0.63 3 43 13.23 0.74 4 22 12.74 0.23 Overall 136 13.20 0.72 WhiteCollarPctPen 1 39 61.64 9.29 2 32 57.73 9.61 3 43 57.25 9.15 4 22 48.23 5.09 Overall 136 57.16 9.72 Farm_Forest_FishPctPen 1 39 1.36 1.26 2 32 1.42 1.74 3 43 1.93 1.71 4 22 4.15 2.95 Overall 136 2.00 2.09 BlueCollarPctPen 1 39 24.25 6.78 2 32 26.32 6.31 3 43 26.59 6.53 4 22 29.42 5.01 Overall 136 26.31 6.49

 Variable and Cluster N Mean Std Dev MedianHomeValue 1 39 104816.67 28068.36 2 32 92660.03 26336.36 3 43 96370.60 29053.05 4 22 85279.36 15407.20 Overall 136 96125.39 26915.04 WhitePopPctPen 1 39 83.84 16.49 2 32 86.13 17.39 3 43 85.58 18.46 4 22 95.20 3.95 Overall 136 86.77 16.40 BlackPopPctPen 1 39 9.56 12.73 2 32 9.38 16.09 3 43 8.67 13.90 4 22 1.88 2.20 Overall 136 7.99 13.16 Groc_Miles 1 39 2.90 0.69 2 32 3.20 1.60 3 43 2.61 1.60 4 22 5.54 4.27 Overall 136 3.31 2.32 Mass_Miles 1 39 2.49 0.90 2 32 2.52 1.01 3 43 2.30 1.67 4 22 4.87 4.81 Overall 136 2.82 2.40

ANOVA Tests of Equality of Group Means

 Variable DF Cluster DF Error F PROB Currentpop 3 132 22.7096 0.00000 MedianHHincome 3 132 4.8345 0.00317 MedianYrsSchool 3 132 5.6417 0.00114 WhiteCollarPctPen 3 132 10.9795 0.00000 Farm_Forest_FishPctPen 3 132 12.2661 0.00000 BlueCollarPctPen 3 132 3.1651 0.02670 MedianHomeValue 3 132 2.8355 0.04066 WhitePopPctPen 3 132 2.5265 0.06025 BlackPopPctPen 3 132 1.9613 0.12291 Groc_Miles 3 132 10.2798 0.00000 Mass_Miles 3 132 7.3739 0.00013

Chain * Cluster Number of Case Crosstabulation

 Frequency Cluster Row Pct 1 2 3 4 Total TOPS 0 31 32 22 85 0 36.47 37.65 25.88 WEGMANS 39 1 11 0 51 76.47 1.96 21.57 0 Total 39 32 43 22 136

Chi-Square Tests for Chain * Cluster Number of Case Crosstabulation

Statistic                     DF       Value      Prob

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

Chi-Square                     3     96.9395    <.0001

Likelihood Ratio Chi-Square    3    122.1429    <.0001

Mantel-Haenszel Chi-Square     1     58.5519    <.0001

Prizm_Cluster Crosstabulation Cluster Number of Case *

 Cluster Number of Cases Cluster 1 2 3 4 C1 2 0 0 1 C2 3 4 6 0 C3 1 4 5 0 S2 7 1 6 0 S3 11 7 7 0 S4 2 4 2 0 T1 2 4 1 0 T2 4 2 3 1 T3 0 2 3 13 T4 4 0 5 7 U1 0 1 0 0 U2 1 0 0 0 U3 2 3 5 0