Sales Revenue in Dollars - Telemarketing Company Assignment

In this assignment you are required to use the relevant built-in function in Excel to estimate a simple linear regression model. As essential preparation for this assignment you should have worked through the computing exercise sets.

Suppose the managers of a telemarketing company are interested in the relationship between the following two variables:

• The sales revenue in dollars (say Y) generated by a telemarketer in his/her first year of completed service.

• The number of hours of preliminary training provided to a new telemarketer (say X).

Using a random sample of data for 42 telemarketers that recently completed their first year of service with the company, you are required to estimate a conditional expectation function of the following form by least squares regression using the Microsoft Excel regression tool.

E(Y | X ) = ?1 + ?2 X

To generate your own personalised data set you will need to follow the steps below.

1. Open the ‘Assignment Data Generation File’ attachment in the ‘Computing Assignment’ link on the left hand menu of the vUWS site for this unit. If the Excel file opens in ‘Protected View’, just click on ‘Enable Editing’ before undertaking the following steps.

2. Once you have opened the Data Generation File in Excel, you should enter the last four digits of your student number in the range of cells E2 to H2, i.e. with the fifth digit in E2, the sixth digit in F2, the seventh digit in G2, and the eighth digit in H2. This will generate your personalised data set in the range A1:C43 (with the headings ‘Marketer’, ‘Hours of Training’ and ‘Sales Revenue’ in A1, B1 and C1, respectively). Note that the marker of your assignment will be able to check that you use the correct data set corresponding to your student number.

3. Open a new new Excel workbook and copy the range A1:C43 containing your data set in the Data Generation File to the new workbook. Close the Data Generation File, then give your new Excel workbook a name and save it on your computer or storage medium (memory stick, etc.). Apart from entering the last four digits of your student number and copying your generated data, you will not be able to edit the Data Generation File.

Required Assignment Tasks

Each student is required to submit in MS Word document format (at the end of Week 13 to his/her tutor):

• Main Report: Answers to parts (b) to (e).

• Annexure: A softcopy of his/her part (a) (see below) tabulated regression results in including a copy of the data set used and the summary results table from using the built-in regression tool in Excel. Students are not required to submit any list of residuals, residual plots or line fit plots.