Wal*Mart Dry Goods Sales 2003-2004 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 4th holiday when people are buying barbecue related items. 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 R2 values. Discuss what outliers were selected, their calendar dates, and why the values were removed. Note that some items were removed and some added to the Dry Goods department in the Walmart Methuen store during the transition from 2002-2003 to 2003-2004. This accounts for the apparent variation in the graphs when comparing the data for project one and project two. Generate supporting Excel graphs (use scatter plots) to answer the following questions for the Dry Goods 2003-2004 data: Methuen WalMart 2003-2004 Dry Goods Week 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 62 63 65 66 67 68 69 70 Sales in s 18000 16800 15200 15000 13600 16000 12600 14800 16800 14800 15200 16000 15600 15600 15000 1700 15800 13800 12800 1440 15800 160Q0 12400 16200 17000 18600 16000 18000 19820 | 18600 1850 18000 18200 18600 1600 15200 168O0 15800 17800 15800 | 15600 14200 15800 1j1Q0 141o0 1440 14O0 169O0 17000 160Q0 1780 72 73 7 75 76 17 78 79 80 81 82 83 84 85 86 87 88 89 90 91 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, quadratic, cubic, 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 R2 value and discuss these results. c. What are the marginal sales (derivative, i.e. rate of change) for this department using each model. Discuss with detail what the marginal sales for each model indicates. d. Compare your models. Which do you feel is best? Remove appropriate outliers as you deem necessary for your favorite models and rerun the appropriate least squares model. What is the marginal sales and discuss improvements. e. 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? 4. For the model selected as your preferred predictor, compute the percent rate of increase Yz - Y for the next four weeks and provide appropriate backup computation. Note that week yi 91 sales is the last sales data available, so use your model to predict sales for week 92 and then compute the percent rate of increase. Repeat this process for Y; Y2, etc. Y2 Wal*Mart Dry Goods Sales 2003-2004 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 4th holiday when people are buying barbecue related items. 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 R2 values. Discuss what outliers were selected, their calendar dates, and why the values were removed. Note that some items were removed and some added to the Dry Goods department in the Walmart Methuen store during the transition from 2002-2003 to 2003-2004. This accounts for the apparent variation in the graphs when comparing the data for project one and project two. Generate supporting Excel graphs (use scatter plots) to answer the following questions for the Dry Goods 2003-2004 data: Methuen WalMart 2003-2004 Dry Goods Week 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 62 63 65 66 67 68 69 70 Sales in s 18000 16800 15200 15000 13600 16000 12600 14800 16800 14800 15200 16000 15600 15600 15000 1700 15800 13800 12800 1440 15800 160Q0 12400 16200 17000 18600 16000 18000 19820 | 18600 1850 18000 18200 18600 1600 15200 168O0 15800 17800 15800 | 15600 14200 15800 1j1Q0 141o0 1440 14O0 169O0 17000 160Q0 1780 72 73 7 75 76 17 78 79 80 81 82 83 84 85 86 87 88 89 90 91 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, quadratic, cubic, 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 R2 value and discuss these results. c. What are the marginal sales (derivative, i.e. rate of change) for this department using each model. Discuss with detail what the marginal sales for each model indicates. d. Compare your models. Which do you feel is best? Remove appropriate outliers as you deem necessary for your favorite models and rerun the appropriate least squares model. What is the marginal sales and discuss improvements. e. 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? 4. For the model selected as your preferred predictor, compute the percent rate of increase Yz - Y for the next four weeks and provide appropriate backup computation. Note that week yi 91 sales is the last sales data available, so use your model to predict sales for week 92 and then compute the percent rate of increase. Repeat this process for Y; Y2, etc. Y2