Instructions for Regression Assignment

This is an individual assignment.  You may discuss the assignment with other students, but you must do the work and answers the questions yourself.  You cannot copy your spreadsheet or answers from another student.

There are 5 different versions of the assignment.  Each version involves the same regression model and the same fictional characters, but the data provided are different, so the answers will be different. 

You must use the correct data set (as listed below) or you will receive a 0 for the assignment:

Students who have a Student ID Number that ends with 0 or 1: Data Set A

Students who have a Student ID Number that ends with 2 or 3: Data Set B

Students who have a Student ID Number that ends with 4 or 5: Data Set C

Students who have a Student ID Number that ends with 6 or 7: Data Set D

Students who have a Student ID Number that ends with 8 or 9: Data Set E

Miles, Santiago, and Yuna all sell robot parts for Cyberdyne Systems.  Cyberdyne carefully monitors their employees' performance.  Each year, for each employee, statistics are kept on the sales of equipment in dollars, the number of presentations they gave to potential customers, and the number of trade shows each salesperson attended to attract customers.  Cyberdyne also kept track of whether each salesperson spent most of their time working in the Eastern or the Western half of the United States.  In addition, each year, each salesperson is assigned a key product to push that year, but they are free to sell any of Cyberdyne's products.

Follow these steps to do the assignment:

1. Make sure you use the right data set (as listed above).  There are 30 observations in all.  In addition, to the right of the data set on the worksheet is a list of product names and their prices.  This will be needed.

2. On the same page the data comes on, add a scatter plot chart that has SALES on the Y-axis and PRESENTATIONS on the X-axis.

3. On this same page, make 1 pivot table that shows the average SALES for Miles, Santiago, and Yuna (each one separately) when they are concentrating on the East, and the average SALES for Miles, Santiago, and Yuna(each one separately) when they are concentrating on the West.  All of this should be shown on only 1 pivot table, in a manner that makes it easy for the reader to compare all the values.  (It is okay if it also shows the overall averages for the salespeople and for the two regions.)

4. On a new page named "clean data," prepare the data so that you have the variables defined below.  Specifically, in order to get full credit,use formula in excel to create the variables WEST, MILES and SANTIAGO to go with the other numerical data given.  Also, use VLOOKUP along with the price table to the right of the raw data to create the PRICE variable defined below.

SALES = the value of sales of equipment sold by Miles, Santiago, or Yuna in a year.

PRESENTATIONS = the number of presentations given to potential clients byMiles, Santiago, or Yuna in a year. 

SHOWS= the number of trade shows attended by Miles, Santiago, or Yuna in a year.

WEST = 1 if salesperson concentrated on the West; 0 otherwise.

MILES = 1 if the observation is for Miles; 0 otherwise.

SANTIAGO = 1 if the observation is for Santiago; 0 otherwise.

PRICE=price of key product that salesperson is supposed to push that year.

5. Use the data from your "clean data page" to estimate the regression model shown below.  The results of this regression should appear on a different spreadsheet page named "results."

SALES = B₀ + B₁PRESENTATIONS + B₂SHOWS + B₃WEST+B₄MILES + B₅SANTIAGO + B₅PRICE+e

NOTE:  If your regression results contain something that looks like, say 5.83E-5, that means 5.83x10-5 or 0.0000583.  If it was 5.83E5, that would mean 5.83x10+5 or 583,000.

6. Answer the following questions, typing the answers in a Microsoft Word document.

a. Interpret the slope estimate from your results for SHOWS. Comment on the p-value and how that affects your interpretation of the slope estimate (be precise.)

b. Given the information in your pivot table, who would you pick to concentrate on clients in Los Angeles?  Why?

c. Interpret the B estimate and p-value for MILES (be precise.)From Cyberdyne's viewpoint, what practical information is this telling us about the situation? 

d. Precisely, how well does the regression fit the data?  How do you know?

Attachment:- Assignment Files.rar