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.

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 Dry Goods 2002-2003 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 (Dry goods Dept) Sales 2002-2003

Week Sales$

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