Wal*Mart Dry Goods Sales 2002-2003 The following items are a guide for responses to be addressed in the extra credit project. 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. 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. Even though the acquired data is from 2002-2003, the data analyses are relevant for data acquired at other time periods. All projects are to be prepared in a word document format with imbedded Excel graphs. Then a single pdf file is to be created for submission to the Blackboard dropbox. Students are to submit individual efforts for the project but may work with others in the development of Excel related concepts. When doing your least squares modeling of the data, don't forget to generate the model (linear, logarithmic, exponential) 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 holidays or special events 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 R2 value and discuss these results. c. What are the marginal sales (derivatives, l.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 for the next week 78 and week d. Analytically prepare predictions of sales for each model for the next week 78 and week 82 which is five weeks after the data set ends. Also compute rates of change (marginals) for each model at weeks 78 and 82 . e. Remove appropriate outliers as you deem necessary and rerun the least squares models. What are the marginal sales for each new model and discuss R2 changes. f. For your new models with outliers removed, analytically prepare predictions of sales and marginals at weeks 78 and 82 . 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? \begin{tabular}{|l|l|} \hline 61 & 3475 \\ \hline 62 & 4000 \\ \hline 63 & 3900 \\ \hline 64 & 3250 \\ \hline 65 & 3600 \\ \hline 66 & 4500 \\ \hline 67 & 3600 \\ \hline 68 & 4100 \\ \hline 69 & 4300 \\ \hline 70 & 4600 \\ \hline 71 & 3950 \\ \hline 72 & 4300 \\ \hline 73 & 4300 \\ \hline 74 & 4225 \\ \hline 75 & 3975 \\ \hline 76 & 4600 \\ \hline 77 & 4300 \\ \hline \end{tabular} The following should guide you through creating charts for Excel 2016. Start by referencing your text (p.635-636). - Enter data with headings - Highlight the headings and data, insert tab-select scattergram (use entry for points in the top left or use the entry for straight lines and markers in the 2nd row far right) - Right click any point in the graph->select add trendline, select: linear, display equation on chart, display R-squared->close - Under chart tools in the upper right ( click anywhere on the chart in empty space if chart tools doesn't appear) click on layout->chart title->enter "Dry Goods", then axes titles->horizontal>enter "weeks", then axes titles->vertical->enter "sales' - Right click any value on the horizontal axis->select format axis->reset the min and max values as desired - Right click on the chart in empty space select "move chart" click on new sheet to get a separate page for the chart. Be sure to save your file! Note: If it is necessary to increase the text size of your Excel trendline generated formula, right click on the formula->font->change the size to a larger size as needed->ok