Question

Dry Goods Sales The data is for weekly sales in the dry goods department at a Wal*Mart store in the Northeast.  Peak values, I.e. spikes, usually occur at holiday periods.  Week 1 is the first week of February 2002.  To show continuity, week 1 of 2003 is represented as week 54 since week 53 represents the end of fiscal 2002 and start of the 2003 fiscal year. Dollar values are adjusted in order to disguise true sales figures, but trends in the data are retained for analysis puposes.

Week			Sales in $
26			15200
27			15600
28			16400
29			15600
30			14200
31			14400
32			16400
33			15200
34			14400
35			13800
36			15000
37			14100
38			14400
39			14000
40			15600
41			15000
42			14400
43			17800
44			15000
45			15200
46			15800
47			18600
48			15400
49			15500
50			16800
51			18700
52			21400
53			20900
54			18800
55			22400
56			19400
57			20000
58			18100
59			18000
60			19600
61			19000
62			19200
63			18000
64			17600
65			17200
66			19800
67			19600
68			19600
69			20000
70			20800
71			22800
72			23000
73			20800
74			25000
75			30600
76			24000
77			21200

1.) Can you identify at least 6 holiday periods or special events that cause the spikes in the data?

a.) In each case give the week number, date, and what holiday or special event it represents

b.) Which holiday results in the maximum sales for this department and how much are the sales?

2.) Generate three linear models for this data. Each linear model should be generated from a pair of data points.

a.) For each linear model, find the equation of the line. Show your work. Write the equation in slope intercept form.

b.) For each linear model discuss the meaning of the slope and y-intercept. Also provide an analysis as to why you like or dislike that particular model

c.) Discuss the rationale behind the model that you believe best predicts future results.

3.) Predict and analyze sales for the next four weeks

a.) Using your most preferred linear model, predict sales for the next four weeks and show calculations

b.) Based on your preferred linear model, compute the percent rate of increase (y2-y1)/y1 for the next four weeks

4.) If you were a manager of this department store, what recommendation would you make to the person in charge of inventory?

Answer 1

Linear Model,

The regression equation is defined as,

Now, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'y' column, Input X Range: 'x' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The regression equation is,

Quadratic model,

The regression equation is defined as,

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK.

Step 3: Select Input Y Range: 'y' column, Input X Range: 'X' column and X² column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The regression equation is,

b)

the marginal sales using the regression models are obtained by puuting the value of 'X' in the regreesion equation,

			Linear model	Quadratic model
Sales, y	Week, x	X^2	Y=8741.975+180.9997X	Y=16889.19-164.762X+3.3569X^2
15200	26	676	13447.97	14874.65
15600	27	729	13628.97	14887.81
16400	28	784	13809.97	14907.68
15600	29	841	13990.97	14934.26
14200	30	900	14171.97	14967.55
14400	31	961	14352.97	15007.56
16400	32	1024	14533.97	15054.29
15200	33	1089	14714.97	15107.72
14400	34	1156	14895.97	15167.88
13800	35	1225	15076.97	15234.74
15000	36	1296	15257.97	15308.32
14100	37	1369	15438.97	15388.61
14400	38	1444	15619.96	15475.62
14000	39	1521	15800.96	15569.34
15600	40	1600	15981.96	15669.77
15000	41	1681	16162.96	15776.92
14400	42	1764	16343.96	15890.78
17800	43	1849	16524.96	16011.36
15000	44	1936	16705.96	16138.65
15200	45	2025	16886.96	16272.65
15800	46	2116	17067.96	16413.37
18600	47	2209	17248.96	16560.80
15400	48	2304	17429.96	16714.94
15500	49	2401	17610.96	16875.80
16800	50	2500	17791.96	17043.37
18700	51	2601	17972.96	17217.66
21400	52	2704	18153.96	17398.66
20900	53	2809	18334.96	17586.37
18800	54	2916	18515.96	17780.80
22400	55	3025	18696.96	17981.94
19400	56	3136	18877.96	18189.79
20000	57	3249	19058.96	18404.36
18100	58	3364	19239.96	18625.65
18000	59	3481	19420.96	18853.64
19600	60	3600	19601.96	19088.35
19000	61	3721	19782.96	19329.78
19200	62	3844	19963.96	19577.91
18000	63	3969	20144.96	19832.77
17600	64	4096	20325.96	20094.33
17200	65	4225	20506.96	20362.61
19800	66	4356	20687.96	20637.60
19600	67	4489	20868.96	20919.31
19600	68	4624	21049.96	21207.73
20000	69	4761	21230.96	21502.87
20800	70	4900	21411.96	21804.71
22800	71	5041	21592.96	22113.28
23000	72	5184	21773.96	22428.55
20800	73	5329	21954.96	22750.54
25000	74	5476	22135.96	23079.25
30600	75	5625	22316.96	23414.66
24000	76	5776	22497.96	23756.80
21200	77	5929	22678.96	24105.64

c)

R square for linear model = 0.670943

R square for quadratic model = 0.650563

The R square value for linear model is greater than linear model hence linear model is better fit compare to quadratic model.

The scatter plot between sales and week doesnot shows any seasonality (repeated pattern)