ECON Excel Assignment
muteb11
instructions_for_regression_assignment_f2014.docxInstructions for Regression Assignment
This assignment is due at 11:55 PM on Monday, October 6. Late assignments will not be accepted. It is worth 10% of your grade. You should submit an Excel file and a Word document through your Moodle account by this time. The Excel file should contain the data work, chart, regression, and pivot table mentioned in the instructions and the Word document should contain your written answers to the questions.
Name your files after your last name. For example, if I were in the class, my files would be named halcoussis.xlsx and halcoussis.docx.
IMPORTANT: 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
Lem, Ted and Veronica all sell robot parts for Veridian Dynamics. Veridian 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. Veridian 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 Veridian’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 Lem, Ted, and Veronica (each one separately) when they are concentrating on the East, and the average SALES for Lem, Ted, and Veronica (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, LEM and TED 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 Lem, Ted, or Veronica in a year.
PRESENTATIONS = the number of presentations given to potential clients by Lem, Ted, or Veronica in a year.
SHOWS = the number of trade shows attended by Lem, Ted, or Veronica in a year.
WEST = 1 if salesperson concentrated on the West; 0 otherwise.
