Generate supporting Excel graphs (use scatter plots) to answer the following questions for the Dry Goods 2002-2003 data:

1. Identify at least 5 spikes (outliers) in the data where extreme sales values occur and correlate these spikes with calendar dates in 2002 or 2003 and with holidays or special events that may occur during these periods. 2

2. Modeling the data linearly

a. Generate a linear model for this data by choosing two points. To generate the 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. Be sure not to select outliers, adjacent points, or first and last points together, which may result in a skewed model equation. Show all your work for finding the line. Write the equations in slope-intercept form, y = mx + b. Show calculations.

b. Use Trend Line in Excel to generate a least squares linear regression (best fit) model (equation) for this data. Include the model equation, trend-line, and r 2 -value within graph.

c. How good is this regression model? Discuss the r2 -value.

d. 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?

e. Compare the two models. Which do you feel is better?

f. Remove appropriate outliers as you deem necessary, explain why they were removed, and rerun the linear regression model. Include (within the graph) the model equation, trend-line, and r 2 -value. Discuss improvements in this new model vs the linear regression model that contained the outliers. Also, what are the marginal sales for this new model?

3. Modeling the data quadratically

a. Use Trend Line in Excel to generate a quadratic model for this data. Include model equation, trend-line, and r2 -value within graph. Discuss the r 2 -value.

b. What are the marginal sales 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.

e. Remove outliers and rerun the quadratic least squares model. Include model equation, trend-line, and r2 -value within graph. Discuss improvements in this new model vs the quadratic regression model that contained the outliers. Also, what are the marginal sales for this new model?

4. Comparing models

a. Based on all models run, 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?