Question

Note that 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. Outliers
(extreme values) are present in the data and can distort modeling results. As an example, spikes in sales
(revenue) at weeks 28-30 occurs in weeks 32-34 (28+4 and 30+4) which represent mid to late August
2002. Another spike at week 58 week is actually week 10 in 2003 (58+4 weeks for January 2002 minus
52 weeks for 2002). This corresponds to sales for early March 2003. The question is whether these
spikes are due to special events or holiday periods, or are perhaps due to restocking and stock
availability.
All projects must be printed on 8.5x11 paper in a word document with imbedded Excel graphs. No
electronic copies or handwritten ones will be accepted. All content must be printed – no handwritten
mathematics, graphs, labels, etc. All projects must be stapled.
The final project report is to be an individual effort. Collaboration during the report development is
acceptable as part of the learning process.
When doing your least squares modeling of the data, don’t forget to generate the model (linear or
quadratic) and then remove outliers (extreme values causing spikes in the data) and rerun the model.
The results should improve with better R2 values. Discuss what outliers were removed and why.
Generate supporting Excel graphs (use scatter plots) to answer the following questions for the given
data:
1. 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 events that may occur during these periods.
2. 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 R2 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.
3. Modeling the data quadratically -
a. Generate a quadratic model for this data. Also output and discuss the R2 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.
4. 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?

Boxed Foods 2002-2003 Data
Week	Sales in $
26	2400
27	2000
28	1800
29	1750
30	1700
31	2500
32	3100
33	2400
34	2350
35	3100
36	3150
37	2300
38	2600
39	2025
40	2225
41	2200
42	1975
43	2025
44	2025
45	2400
46	2200
47	2600
48	1975
49	2700
50	2800
51	3600
52	3200
53	3025
54	3000
55	3400
56	3400
57	4050
58	4500
59	3850
60	3500
61	3475
62	4000
63	3900
64	3250
65	3600
66	4500
67	3600
68	4100
69	4300
70	4600
71	3950
72	4300
73	4300
74	4225
75	3975
76	4600
77	4300