This is the entire question I need have this done by this ev

This is the entire question. I need have this done by this evenning. Please and Thank you. The link below provides the data on two different spread sheets. It has to be done on excel. http://faculty.uml.edu/mstick/92.122/material/Walmart dry goods weekly sales.htm Project 1: The data used is from the Methuen Walmart store infiscal 2002 - 2003 and fiscal 2003 - 2004. Walmart\'s fiscal year starts with the first week of February, so week 1 in 2002 is the first week in February and week 52 (or week 53 based on roundoff) occurs during the last week of January in 2003. Questions: 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?

Solution

It is clear that no line can be found to pass through all points of the plot. Thus no functional relation exists between the two variables week(w) and Sales(s). However, the scatter plot does give an indication that a straight line may exist such that some of the points on the plot are scattered randomly around this line. The regression model here is called a simple linear regression model because there is just one independent variable.

The marginal revenue function is the first derivative of the total revenue function. so

S=8741.97+180.99w

TR=(8741.97+180.99w)w

MR=8741.97+361.999w