Question

The WalMart’s fiscal year starts the first week of February. This means that when analyzing the data, week 26 is actually week 30 (26+4 weeks for January) in 2002 or the end of July 2002. Also, week 52 is actually week 4 (52+4 weeks for January 2002 minus 52 weeks for 2002) in 2003 or the end of January 2003. As an example, the spike in sales (revenue) at week 75 occurs in week 27 (75+4 weeks for January 2002 minus 52 weeks for 2002) in 2003 or the first week in July 2003. This corresponds to sales for the July 4th holiday when people are buying barbecue related items. Please use excel.

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

Identify spikes (outliers) in the data where extreme sales values occur and correlate these spikes with actual calendar dates in 2002 or 2003 and with holidays or special events that may occur during these periods.

1. Modeling the data linearly - a. Generate a linear model for this data by choosing two points.

b. Generate a least squares linear regression model for this data.

c. How good is this regression model? Output and discuss the R² value.

d. What are the marginal sales (derivative, i.e. rate of change) for this department using the linear model with two data points and the regression model?

e. Compare the two models. Which do you feel is better?

f. Remove appropriate outliers as you deem necessary and rerun the linear regression model. What is the marginal sales and discuss improvements.

2. Modeling the data quadratically - a. Generate a quadratic model for this data. Also output and discuss the R² value.

b. What are the marginal sales for this department using this model?

c. Calculate the model generated relative max/min value. Show backup analytical work.

d. Compare actual and model generated relative max/min value.

e. Remove outliers and rerun the quadratic least squares model. What is the marginal sales and discuss improvements.

3. Comparing models - a. Based on all models run, which model do you feel best predicts future trends? Explain your rationale.

b. Based on the model selected, what type of seasonal adjustments, if any, would be required to meet customer needs?

Answer 1

Scatterplot of Sales in $ vs Week 32500 30000 27500 ea 25000 22500 20000 17500 15000 20 30 40 50 Week 60 70 80

1) a) Two point line equation

considering two points as (50, 16800), (60, 19600)

line equation will be

(Y-16800)(60 - 50) = (X - 50)(19600 - 16800)

Y = 280X - 2800

b) Linear regression

Y = 8741.975 + 181X

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.806575
R Square	0.650563
Adjusted R Square	0.643574
Standard Error	2030.33
Observations	52
ANOVA
	df	SS	MS	F	Significance F
Regression	1	3.84E+08	3.84E+08	93.08734	5.29E-13
Residual	50	2.06E+08	4122241
Total	51	5.9E+08
	Coefficients	Standard Error	t Stat	P-value
Intercept	8741.975	1006.33	8.686988	1.47E-11
Week	180.9997	18.75999	9.648178	5.29E-13

2)

Polynomial Regression Analysis: Sales in $ versus Week

The regression equation is
Sales in $ = 16889 - 164.8 Week + 3.357 Week^2

Model Summary

S	R-sq	R-sq(adj)
1929.12	69.08%	67.82%

Analysis of Variance

Source	DF	SS	MS	F	P
Regression	2	407487012	203743506	54.75	0.000
Error	49	182353565	3721501
Total	51	589840577

Sequential Analysis of Variance

Source	DF	SS	F	P
Linear	1	383728507	93.09	0.000
Quadratic	1	23758505	6.38	0.015

Fitted Line: Sales in $ versus Week

Sales in $ 16889 - 164.8 Week 3.357 Week2 32500 R-Sq R-Sq(adj) 1929.12 69.1% 67.8% 30000 27500 25000 22500 20000 17500 50 Week 20 30 40 70 80