Question

LTNormal」1,0 Spac-. a·y.LI, r i, mt.x' ˊ·a-ia. Heading 1 Font 2) Page 18S Figure 92 MANAGERIAL STATISTICS: A CASE-BASED APPROACH 9.1 Soda Sales INTRODUCTION You 188 u have been asked by Cesca, Inc., to forecast future sales of Dada Soda lre in the soda.xls file It consists of quarterly Dada Soda sales figures fot the da fosur years (see Figure 9.1). Quarter I is the beginning of a year and is, the he efore, a and a hwo things are apparent from the graph: Sales are growing over time, and seatsonal tactor exists, Suppose we ignore the seasonality and regress sales quarter variable, i.e, draw a best fit line through the graph (see Figure 9.2).t Figure 9.1 Quarterly soles for Dado Sodo. winter quarter Quarterly Sales Data 200000 180000 160000 140000 120000 100000 0 2 46 810 12 14 16 18 quarter Figure 9.2 Quorterly sales for Dada Soda with regression line. Quarterly Sales Data 260000 240000 220000 200000 180000 160000 140000 120000 100000 0 2 46 810 12 14 16 quarter Regression Equation: y 66686x + 125384

Answer 1

Let's carry out regression in Excel. We regress Sales against the Year and the Quarter. The Year is 1 for the first 4 rows, 2 for the next 4 rows and so on. So, the data looks as follows:

quarter	sales	winter	spring	summer	Year
1	122520	1	0	0	1
2	149931	0	1	0	1
3	162481	0	0	1	1
4	122630	0	0	0	1
5	132818	1	0	0	2
6	178325	0	1	0	2
7	205781	0	0	1	2
8	158807	0	0	0	2
9	163083	1	0	0	3
10	221728	0	1	0	3
11	237244	0	0	1	3
12	186756	0	0	0	3
13	187123	1	0	0	4
14	238305	0	1	0	4
15	250138	0	0	1	4
16	195399	0	0	0	4

For regression in Excel, go to Data tab -> Data Analysis ->Regression, and choose Sales column as the Y-column and Summer/Winter/Spring/year columns as the X-column. The output from regression is as follows:

	Coefficients	Standard Error	t Stat	P-value
Intercept	98817.4375	6670.515316	14.81406351	1.29947E-08
winter	-14512	6289.022152	-2.307512941	0.041479899
spring	31174.25	6289.022152	4.956931181	0.000430875
summer	48013	6289.022152	7.634414197	1.01619E-05
Year	26832.225	1988.763425	13.49191395	3.45804E-08

Hence, looking at the coefficients for the predictor variables obtained above, we get the following regression equation for Sales in terms of Year and the Quarter of the Year:

Sales = 98817.4375 - 14512*winter + 31174.25*spring + 48013*summer + 26832.225*Year

where the variables winter/spring/summer, the possible values are 0 or 1 depending upon which quarter we are making a prediction about.

Pls note that the coefficient for the 4th quarter is implicitly 0 in the model in order to avoid collinearity (since the sales of the 4th quarter is implicitly known once we know the full Year sales and the sales for the other 3 quarters).

Now, for an example, the sales for the Spring Quarter 2 of Year 5 can be predicted as follows:

Sales = 98817.4375 - 14512*winter + 31174.25*spring + 48013*summer + 26832.225*Year

= 98817.4375 - 14512*0 + 31174.25*1 + 48013*0 + 26832.225*5 = 264152.8