Question

The following items are a guide for responses to be addressed in project two.  Note that WalMart’s fiscal year starts the first week of February.  This means that when analyzing the data, week 41 is actually week 45 (41+4 weeks for January) in 2003 or the beginning of November 2003.  Also, week 52 is actually week 4 (52+4 weeks for January 2003 minus 52 weeks for 2003) in 2004 or the end of January 2004.  As an example, the spikes in sales (revenue) during weeks 70-74 start in week 22 (70+4 weeks for January 2003 minus 52 weeks for 2003) in 2004 or the first week in June 2004, and extend through week 26 in 2004 or the end of June 2004.  This corresponds perhaps to sales for graduation celebrations during the beginning of June and preparation for the July 4^th holiday when people are buying barbecue related items.

All projects must be prepared in a word document format with imbedded Excel graphs.  Students must work separately.  Submit a single pdf file of your project to Blackboard.

When doing your least squares modeling of the data, don’t forget to generate the required models and then remove outliers (extreme values causing spikes in the data) and rerun the model.  The results should improve with better R² values.  Discuss what outliers were selected, their calendar dates, and why the values were removed.

Generate supporting Excel graphs (use scatter plots) to answer the following questions for the Dry Goods 2003-2004 data:

1.     Identify spikes (outliers) in the data where extreme (high or low) sales values occur and correlate these spikes with actual calendar dates in 2003 or 2004 and with any holidays or special events or abnormally slow periods that may occur during these periods.

 

2.     Modeling the data:

a.     Generate linear, logarithmic, and exponential models.  Output at most two models on any graph.

b.     When generating the least squares models for this data, output the model and the R² value and discuss these results.

c.      What are the marginal sales (derivative, i.e. rate of change) for various weeks throughout the data set for this department using each model.  Discuss with detail what the marginal sales for each model indicates.

d.     Analytically prepare predictions of sales for each model at weeks 95 and 100.  Also compute rates of change (marginals) for each model at weeks 95 and 100.

e.     Remove appropriate outliers as you deem necessary for your models and rerun the least squares models.  What are the marginal sales and discuss any improvements.

f.      For your models with outliers removed, analytically prepare predictions of sales at weeks 95 and 100.  Also compute rates of change (marginals) for each model at weeks 95 and 100.

 

 

 

3.     Comparing models

a.     Based on all models run, which model do you feel best predicts future trends?  Explain your rationale. Also, compare your models and explain which you feel is overall the best to use in representing the overall data pattern and explain why?

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

 

x (Weeks)	y (Sales)
69	19600
74	18600
70	18600
66	18600
71	18450
73	18200
72	18000
68	18000
41	18000
91	17800
79	17600
89	17000
65	17000
88	16900
77	16800
49	16800
42	16800
83	16600
64	16200
84	16100
90	16000
75	16000
67	16000
62	16000
52	16000
46	16000
80	15800
78	15800
61	15800
57	15800
56	15700
81	15600
54	15600
53	15600
76	15200
51	15200
43	15200
55	15000
44	15000
50	14800
48	14800
87	14500
86	14400
60	14400
82	14200
85	14100
58	13800
45	13600
59	12800
47	12600
63	12400