WalMart Sales of Boxed Foods within the Dry Goods Dept 2002-2003
MATH.1220
Management Calculus
Project #1
WalMart Sales of Boxed Foods within the Dry Goods Dept 2002-2003
The following items are a guide for responses to be addressed in project one. 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.5×11 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:
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.
Modeling the data linearly –
Generate a linear model for this data by choosing two points.
Generate a least squares linear regression model for this data.
How good is this regression model? Output and discuss the R2 value.
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?
Compare the two models. Which do you feel is better?
Remove appropriate outliers as you deem necessary and rerun the linear regression
model. What is the marginal sales and discuss improvements.
Modeling the data quadratically –
Generate a quadratic model for this data. Also output and discuss the R2 value.
What are the marginal sales for this department using this model?
Calculate the model generated relative max/min value. Show backup analytical work.
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
Based on all models run, which model do you feel best predicts future trends? Explain
your rationale.
Based on the model selected, what type of seasonal adjustments, if any, would be
required to meet customer needs?
INCLUDEPICTURE “/var/folders/sj/7_tfwmrj25j4t9l38_4mgww00000gn/T/com.microsoft.Word/WebArchiveCopyPasteTempFiles/page1image685553920” * MERGEFORMATINET
to a single pdf file if you choose. Copy and paste your Excel files to your Word
INCLUDEPICTURE “/var/folders/sj/7_tfwmrj25j4t9l38_4mgww00000gn/T/com.microsoft.Word/WebArchiveCopyPasteTempFiles/page1image685471472” * MERGEFORMATINET
document. Send me a pdf or MS Word version of your final project.
INCLUDEPICTURE “/var/folders/sj/7_tfwmrj25j4t9l38_4mgww00000gn/T/com.microsoft.Word/WebArchiveCopyPasteTempFiles/page1image684757264” * MERGEFORMATINET
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 R^2 values. Discuss
what outliers were removed and why.
The file project 1 online requirements lists details for what is to be done. Write your
report as if you are a consultant to WalMart in an expository format. The Excel cheat
sheet file provides contains brief steps for Excel 2016 to generate your Excel charts,
add trendlines, set up headings and axes titles, reset min and max values for axes, and
move the chart to a separate page. You should find it useful. The sample scattergram
project 1 for online pdf file exhibits what your scattergram should look like. The Boxed
Foods goods data Excel file provides the data to work with.
Excel 2016 cheat sheet
The following should guide you through creating charts for Excel 2016. Start by referencing your text (p.635-636).
Enter data with headingsHighlight 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->closeUnder 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 “Boxed Foods”, 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 desiredRight 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 the formula->font->change the size to a larger size as needed->ok.
it is necessary to increase the text size of your Excel trendline generated formula, right click on
[A.] Project – Analysis & Requirements
1. Analyze the given Walmart Boxed Foods 2002-2003 data set that is part of the Dry Goods department. Correlate the major spikes of the data with that of the major holiday periods cited in the 2002-2003 calendar year.Identify holiday periods or special events that cause the spikes in the data. Present your analysis result in a table format showing:
Wks-No, Spike-Sales-Value, and Calendar period
2. Generate linear and quadratic models using regression for the data set. Present your plots with appropriate titles, the axes labeled and showing the model equation and R2 value. Refer to the project requirements file for detailed instructions to be followed.
