I have this problem in class that is due Friday and I need help with running regressions using Excel's Data Analysis tool.

Attached are the files for the problem.

Carseat-or-bust Inc.

Memo

From the VP of product shelve stocking

To: the Analytics team.

Subject: Competition for Chief Analytics position.

In order to choose which of you to promote to the chief in the team I want you to analyze the data I provide you and make a choice for what shelving decision to make in order to aggressively maximize the profits of the company.

Data file: Carseats.xlsx.

You will choose a best model from the dataset in the Train data section of the Excel file attached. Perform the following steps in Excel with the Regression or Solver utilities, or anything you can use, as long as the final report has proper plots.

Using the Train dataset to create as many intermediate models as you need using different sets of variables. Use either heuristics or your gut feeling testing it, or your general life experience to start with, or the correlation matrix technic you learned in class. Conclude to a best model.

In a report present to me the best 3 models you can create. Describe the method you used to create each model you have chosen. I want you to justify your decisions by being able to answer the following questions:

What are you predicting?

What are the dimensions (rows by columns) of the dataset?

How many observations are in the Train

How many observations are in the Test

What is the equation of the best model?

Interpret the regression output for the best models you found. Create a matrix table of Model vs parameters that includes the following parameters a-thru-g:

R²

adjusted R²

Number of the degrees-of-freedom of each of the models.

Level of the coefficients (in the report tell me which are the bigger drivers and how you decide that?)?

P-values of the coefficients

MAPE from the Train dataset.

MAPE from the Test dataset.

The above metrics are the best to compare models for any analysis. How do the MAPE values compare to the model(s) you chose? Also briefly describe how you implemented the model on the test data. Walk me through the process and analysis.Many can run a regression, but many fail to interpret the results and paint the entire picture. The one of you I will want to promote must be able to tell me a good story and give me a decision that I will believe in.

Data Directory and Description

A simulated data set containing sales of child car seats at different stores.

Variables

Store ID An index of the stores. Notice that they are not in sequence as the test data are sampled from the full set randomly. This is how you do Train & Test method.

Sales Unit sales (in thousands) at each location

CompPrice Price charged by competitor at each location in $

Income Community income level (in thousands of dollars)

Advertising Local advertising budget for company at each location (in thousands of dollars)

Population Population size in region (in thousands)

Price Price company charges for car seats at each site

ShelveLocation A factor with levels Bad, Good and Medium indicating the quality of the shelving location for the car seats at each site

Age Average age of the local population Education Education level at each location Urban A factor with levels No and Yes to indicate whether the store is in an urban or rural location

US A factor with levels No and Yes to indicate whether the store is in the US or not

The link to download the dataset: https://1drv.ms/x/s!AiwBzW1gxqwR2m5GBESwaREZdlGx

Please solve this as soon as possible.