This Excel based project will focus on applications of linear equations and linear least squares regression. Project must be posted as a single PDF file in the provided Assignment DropBox in Blackboard. All content must be typed - no handwritten mathematics, graphs, labels, etc. Students can work with a partner and each group can submit one report, make sure both names are on the report. The data used is from the Methuen Wal*Mart store in fiscal 2002-2003. 1. Generate supporting Excel spreadsheet(s) and graphs (use scatter plots) to answer the following questions for the Dry Goods 2002-2003 data. 2. Identify at least 5 holiday periods or special events that cause spikes in the data. a. In each case, give the week number, date and what holiday or special event it represents. b. Which holiday results in the maximum sales for this department and how much are the sales? 3. Generate two linear models for this data. Each linear model should be generated from a pair of data points. a. To generate each linear model, pick a pair of points from the data and use the formulas, = 21 21 , and 1 = ( 1 ) to find the slope and the equation of the line for each pair of the points. Show all your work for finding the lines. Write the equations in slope-intercept form. b. For each linear model, discuss the meaning of the slope and y-intercept. Also provide an analysis as to why you like or dislike that particular model. c. Discuss the rationale behind your model that you believe best predicts future results. 4. Generate a linear least squares regression model for this data. a. Use Trend Line in Excel to generate the linear least squares regression model for the data. b. Display the equation and the R2 on your graph. c. What are the vales of the slope and the y-intercept for this model? What do the slope and y-intercept values mean for this model? d. Do you feel that this model predicts future trends? Explain your rationale. 5. Predict and analyze sales for the next four weeks. a. Use the linear least squares regression model obtained in question 4 to predict sales for the next four weeks (week 78, 79, 80, and 81). Show your calculations. b. Using these predicted values, compute the percent rate of increase 21 1 for the next four weeks. 6. If you were the manager of this department, what recommendations would you make to the person in charge of ordering inventory?

Wal*Mart Dry Goods Sales 2002-2003 The data is for weekly sales in the dry goods department at the Methuen Wal*Mart store in fiscal 2002-2003. Peak values, i.e. spikes, usually occur at holiday periods. Week 1 is the first week of February 2002. To show continuity, week 1 of 2003 is represented as week 54 since week 53 represents the end of fiscal 2002 and start of the 2003 fiscal year. Dollar values are adjusted in order to disguise true sales figures, but trends in the data are retained for analysis purposes. Week Sales in $ 26 15200 27 15600 28 16400 29 15600 30 14200 31 14400 32 16400 33 15200 34 14400 35 13800 36 15000 37 14100 38 14400 39 14000 40 15600 41 15000 42 14400 43 17800 44 15000 45 15200 46 15800 47 18600 48 15400 49 15500 50 16800 51 18700 52 21400 53 20900 54 18800 55 22400 56 19400 57 20000 58 18100 59 18000 60 19600 61 19000 62 19200 63 18000 64 17600 65 17200 66 19800 67 19600 68 19600 69 20000 70 20800 71 22800 72 23000 73 20800 74 25000 75 30600 76 24000 77 21200