use excel to solve all the questions and provide necessary graphic outputs

Data Overview

This is a sample of 116 weekly sales of Tropicana oranges juice, prices of Tropicana, Minute Made, Domick's orange juice, and dummy variables for feature and display. We want to predict sales of Tropicana give its price, its competitor's prices and whether it is on feature or display.

The data has the variables:

VariableDescription

WeekThe number of week in which data was collected

SalesTropSales of Tropicana for that week

PriceTropPrice of Tropicana for that week

PriceMMPrice of Minute Maid for that week

PriceDomPrice of Domick for that week

FeatureDummy to indicate if Tropicana was on feature in that week

DisplayDummy to indicate if Tropicana was on display in that week

Objective: Learn how

estimate regression models transform variables

Step 1: Open data in Excel and Run a Linear Regression

Estimate the following model (full model):

SalesTrop = a + b₁PriceTrop + b₂PriceMM + b₃PriceDom + b₄Feature + b₅Display

Data Analysis Regression

Select the appropriate data for Input Y Range and Input X Range. Check residuals and normality plots.

1.0) Make two scatter plots: 1) Fitted Values vs. Actual Y-Values and 2) Residuals vs. Fitted Value. Copy and paste here.

1.1) Does the linearity assumption hold? Why or why not?

1.2) Does the homoscedasticity assumption hold? Why or why not?

1.3) Does the normality assumption hold? Why or why not?

1.4) What is the R-squared value of this regression? What is the adjusted R-squared value? Is adjusted R-squared smaller than R-squared? Why?

1.5) What are the confidence intervals for the coefficients of PriceDom and Feature? Do these confidence intervals include 0? How should we interpret these two coefficients if the confidence intervals include 0?

1.6) What is the coefficient of PriceTrop? Does the confidence interval for the coefficient of PriceTrop include 0? Is this coefficient negative? What is the meaning of this coefficient being negative?

Step 2: Create Ln Variable and Re-run Regression

Now we need to estimate the following model, where we use the ln(SalesTrop) instead of SalesTrop.

ln(SalesTrop) = a + b₁PriceTrop + b₂PriceMM + b₃PriceDom + b₄Feature + b₅Display

We need to create the transformed variable. Create new column (new variable) in the data called LnSales using the Excel function =ln(SalesTrop)

Estimate the linear regression model above. Check residuals.

2.0) Make two scatter plots: 1) Fitted Values vs. Actual Y-Values and 2) Residuals vs. Fitted Value. Copy and paste here.

2.1) What is the R-squared value of this regression? What is the adjusted R-squared value? Is adjusted R-squared smaller than R-squared?

2.2) What are the confidence intervals for the coefficients of PriceDom abd Feature? Do these confidence intervals still include 0?

2.3) What is the coefficient of PriceTrop? Does the confidence interval for the coefficient of PriceTrop include 0? Is this coefficient still negative?

Step 3: Create More Ln Variables and Re-run Regression

Now we need to estimate the following model,

ln(SalesTrop) = a + b₁ln(PriceTrop) + b₂ln(PriceMM) + b₃Display + error

Create the variable ln(PriceTrop) and ln(PriceMM).

Estimate the linear regression model above. Check residuals and normality plot.

3.0) Make two scatter plots: 1) Fitted Values vs. Actual Y-Values and 2) Residuals vs. Fitted Value. Copy and paste here.

3.1) Does the linearity assumption hold? Why or why not?

3.2) Does the homoscedasticity assumption hold? Why or why not?

3.3) Does the normality assumption hold? Why or why not?

3.4) What is the value of the coefficient of ln(PriceTrop)? What is the own price elasticity of demand (you may have to google this)? Is the coefficient of ln(PriceTrop) negative or positive? Why?

3.5) What is the value of the coefficient of ln(PriceMM)? What is the cross price elasticity of demand (you may have to google this)? Is the coefficient of ln(PriceMM) is negative or positive? Why?

3.6) What is value of the coefficient of Display? Is this coefficient positive or negative? How would you interpret the sign (positive or negative) of this coefficient?

below is all the data

week	SalesTrop	PriceTrop	PriceMM	PriceDom	Feature	Display
40	6528	3.660	1.890	2.990	1	0
43	6016	3.660	2.840	2.990	0	0
44	6272	3.660	2.840	2.590	0	0
45	6848	3.660	2.390	2.990	0	0
46	7424	3.660	2.840	2.990	0	0
47	6848	3.660	2.840	2.390	0	0
48	7488	3.660	2.390	2.990	0	0
49	6336	3.660	2.390	1.990	0	0
50	6208	3.660	1.990	2.990	0	0
51	6400	3.660	2.840	2.990	0	0
52	13056	3.290	2.840	2.990	1	0
53	8704	3.290	2.840	2.190	1	0
54	8832	3.290	2.390	2.990	1	0
55	5696	3.660	2.390	2.990	0	0
56	14208	3.290	2.840	2.990	1	1
57	7616	3.290	2.840	1.990	1	0
58	5632	3.510	2.840	2.867	1	0
59	6592	3.510	1.990	2.990	1	0
60	7680	3.510	2.840	1.990	0	0
61	6720	3.510	1.990	2.990	1	0
62	10432	3.660	2.840	1.990	0	0
63	13824	2.990	2.290	1.990	1	0
65	7872	3.390	2.616	1.990	0	0
66	8064	3.390	2.460	1.990	0	0
67	23872	2.390	1.990	1.990	0	0
68	13760	2.390	1.690	1.990	1	0
71	10368	2.590	1.690	2.460	1	0
72	4608	3.390	1.690	1.990	0	0
73	7104	3.390	2.460	2.460	0	0
74	34432	2.490	2.460	2.410	1	0
75	6592	3.390	1.490	2.410	0	0
76	7296	3.390	1.490	2.410	0	0
77	7552	3.390	2.460	1.990	0	0
78	6976	3.390	2.460	2.460	1	0
79	6720	3.390	1.790	2.460	0	0
80	5824	3.390	2.460	1.690	1	0
81	96064	1.690	2.460	1.690	1	1
82	11072	3.390	2.350	2.460	0	0
83	55808	1.990	2.350	2.090	1	1
84	12928	3.390	2.350	2.090	0	0
85	68032	1.990	2.350	2.090	1	1
86	26112	1.990	2.350	2.090	1	0
87	11776	3.390	2.460	1.390	0	0
88	34048	2.290	2.460	2.260	1	1
89	14848	3.390	2.350	2.260	0	0
90	15424	3.390	2.350	2.260	1	0
91	64000	1.990	2.350	2.260	1	0
92	14528	1.990	1.760	1.690	1	0
93	7040	3.390	2.350	2.260	1	0
94	15168	3.390	2.350	1.690	1	0
95	53056	1.990	2.350	1.690	1	0
96	9536	1.990	2.350	2.260	1	0
97	11904	1.990	2.350	2.260	1	0
98	11136	3.390	1.990	2.260	0	1
99	37568	2.190	2.350	2.260	1	0
100	13376	2.190	2.350	2.260	1	0
101	6144	3.390	2.350	2.260	0	0
102	7104	3.390	2.350	1.290	1	0
103	5696	3.390	1.990	2.260	0	0
104	63168	1.990	2.350	2.260	1	1
105	7680	3.390	1.990	1.990	0	0
106	11136	2.732	1.690	1.952	0	1
107	8384	3.206	1.753	1.851	0	0
108	7616	2.970	2.286	1.790	0	0
109	9216	2.940	2.250	1.900	1	0
110	8512	2.940	2.250	1.690	1	0
111	8128	2.940	2.250	2.260	1	0
112	8960	2.940	2.250	1.490	1	0
113	13248	2.960	1.970	2.224	1	0
114	10624	2.990	1.970	2.074	0	0
115	57408	1.990	2.127	1.990	1	1
116	11200	2.725	1.990	1.990	1	0
117	8576	2.990	1.990	2.080	0	0
118	8704	2.990	1.857	2.260	0	0
119	12032	2.744	1.690	2.124	0	0
120	19008	2.390	1.730	1.990	1	0
121	18432	2.347	1.546	1.990	1	0
122	16576	2.290	1.490	2.147	1	0
123	10304	2.490	1.549	2.490	1	0
124	6272	2.990	2.350	2.490	0	0
125	9536	2.990	1.628	2.552	0	0
126	8448	2.990	1.490	2.366	0	0
127	34240	1.990	1.744	1.990	1	1
128	7040	2.990	2.350	1.990	0	0
129	74752	1.990	2.350	2.082	1	1
130	41792	1.790	2.350	2.304	1	0
131	83072	1.790	2.577	1.990	1	0
132	35712	1.914	2.242	2.150	1	0
133	21760	2.745	1.990	2.341	0	0
134	39296	2.500	2.040	1.990	1	1
135	11904	2.636	2.890	2.218	1	0
136	11392	2.990	2.890	2.660	0	0
137	12800	2.605	2.506	1.990	0	0
138	50624	2.240	2.160	2.660	1	1
139	11840	2.990	2.890	2.660	0	0
140	12096	2.990	2.890	2.660	0	0
141	10368	2.990	2.890	1.990	0	0
142	12224	2.990	2.790	2.666	0	0
143	98624	2.490	2.830	2.690	1	1
144	27840	2.214	2.890	2.690	1	0
145	81088	1.990	2.890	2.690	1	1
146	10688	2.990	1.990	2.421	0	1
147	24512	2.690	2.054	2.006	1	0
148	8640	2.990	1.890	2.342	0	0
149	11456	2.990	2.890	2.690	0	0
150	6080	2.990	2.890	2.290	0	0
151	9664	2.956	2.890	2.680	0	0
152	13120	2.890	2.590	2.650	1	1
153	8448	2.990	2.376	1.990	0	0
154	27072	2.790	1.990	1.958	1	1
155	14784	2.748	1.990	1.690	0	0
156	45632	2.490	2.033	1.804	1	1
157	16000	2.634	2.825	1.990	1	0
158	10048	3.050	2.037	2.578	0	0
159	22976	2.790	2.733	2.533	1	1
160	10496	2.989	2.770	2.190	1	0