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) for the Dry Goods 2002-2003 data.

2. Identify at least 5 holiday periods or special events that cause spikes in the data.

In each case, give the week number, date and what holiday or special event

it represents.

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 , and 1 = ( 1) to find the slope and the

2 1

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, y = mx + b. 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 values 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) 100% for the next four weeks. 1

6. If you were the manager of this department, what recommendations would you make to the person in charge of ordering inventory?