MATH.1220 Management Calculus Project #1 Wal*Mart Dry Goods Sales 2002-2003 The following items are a guide for responses to be addressed in project one. Note question four about holidays as they might address spikes in the data. Spikes are outliers and should normally be removed from the data to ensure a better model. All content must be printed - no handwritten mathematics, graphs, labels, etc. Create a pdf version of your final work and send it to me. Remember to copy and paste your Excel generated work into your Word doc for this project before generating the pdf. Students may work either separately or in teams of two. Team projects will result in more intense grading. The project will be graded on a 0-5 scale. Together with project 2, the projects will be worth 5% of your grade. Generate supporting Excel spreadsheet(s) and graphs (use scatter plots) to answer the following questions for the Dry Goods 2002-2003 data: 1. 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? d. What is the marginal revenue for this department using the linear model with two data points and the regression model? Note that marginal revenue is the same as the first derivative of the revenue (sales) function. e. Compare the two models. Which do you feel is better? 2. Modeling the data quadratically a. Generate a quadratic model for this data. b. What is the marginal revenue 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. 3. Comparing models a. 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. Identify holiday periods or special events that cause spikes in the original data. WalMart weeks start the beginning of February. So, for example, Walmart week 30 in the 2002 is actually week 34 (30 + 4) in the calendar year 2002 which equates to the end of August 2002. To make the weeks continuous, week 53 is actually WalMart week 1 in 2003 and this equates to week 5 (53 - 52 +4) or the first week in February 2003. Week 72 is week 24 (72 - 52 + 4) in the year 2003 or mid June 2003. Project 1 The files to use as reference are Project #1 with detail Projects MATH.1220 for students Excel notes for data charts These files contain information for project 1. The \"Excel notes\" file provides information on generating scattergrams and least squares models with Excel 2003, 2007 and 2013. The last 2 pages (p.599-600) are reprints from your text. The \"Projects MATH.1220 for students\" file provides an overview of the data used. The first file \"Project 1 with detail\" provides specific information to be addressed in your project. Project 1 assignment Project 1 must be a file in Word format. Copy and paste your Excel files to your Word document. Give me a PDF version of your final project. If you choose, you may work in pairs on Project 1. If you decide to go that route, you must inform me ahead of time, grading will be more intense and include both student names on the cover sheet. 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. Excel 2010 cheat sheet The above mentioned Excel notes file contains detailed information for various versions of Excel except Excel 2010. The following should guide you through creating charts for Excel 2010. Start by referencing your text p.599-600. Many of the steps as mentioned there for Excel 2013 will be the same for Excel 2010. 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\Projects Using the link http://faculty.uml.edu/mstick/92.122/material/MAST_template_form_Feb_1_2005.pdf specified in the syllabus, there will be two projects this term. The first project is on page 14, item 3 that deals with Dry Goods sales data for 2002-2003 from the Methuen WalMart store. Students can work in pairs. The projects are scored on a 0-5 scale. The due date for the first project will be two weeks after the assignment. More detail about the projects follows. Project one is page 14 item 3 in the WalMart document (http://faculty.uml.edu/mstick/92.122/material/MAST_template_form_Feb_1_2005.pdf). The two links to be used are: http://faculty.uml.edu/mstick/92.122/material/Walmart dry goods weekly sales.htm http://faculty.uml.edu/mstick/92.122/material/Walmart_dry_goods.pdf Note that all links may open in the background and when opening the files, OK the message requesting whether they are from trustworthy sources. CTRL + click on the first link http://faculty.uml.edu/mstick/92.122/material/Walmart dry goods weekly sales.htm to open the WalMart Dry Goods sales data for 2002-2003. It may open in the background. Highlight everything, copy and paste it to a Word file, then save and print your file. There may be other options to print the data, but this worked on a system with Word 2010. WalMart's fiscal year begins with the first week in February. Then week 12 as listed in the WalMart spreadsheet is approximately week 16 (add 4 weeks for January) in the calendar year, i.e. the 3rd week of April 2002. To account for data over two years, I labeled week 53 as the first week in fiscal year 2003. CTRL + click on the second link http://faculty.uml.edu/mstick/92.122/material/Walmart_dry_goods.pdf to open the WalMart Dry Goods pdf file for the 2002-2003 sales data. It may open in the background. To save the file, click on the page icon on the right (Word 2010) and save as a pdf. Use CTRL + P to print the file. Another option is to right click on the pdf file and select print. When generating linear and quadratic models, consider various options. Options for linear models are to select different pairs of points, perform a linear least squares on all the data, perform a linear least squares after removing some outliers. Options for quadratic models are to consider a quadratic least squares on all the data, perform several quadratic least squares after removing different groups of outliers. There should be a discussion of the results of marginal sales (rates of change of the sales models) and their meanings for the various models generated. The project document should be prepared in a word document with Excel generated spreadsheets, graphs, analyses and least squares to support findings. Project two that will be assigned later will be page 14 item 7 that follows up on the above assignment and refers to Dry Goods sales data for 2003-2004.

This page uses frames, but your browser doesn't support them.