Simple Regression of Sales Revenue 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.

You must submit your assignment with an assignment coversheet. The assignment coversheet is also available in the ‘Computing Assignment’ link on the unit webpage.

1. Use the built regression tool in Excel and your personalized data set (see above) to estimate the simple regression of sales revenue in dollars (Y) generated by a telemarketer in his/her first year of completed service on the number of hours of preliminary training provided to a new telemarketer (X).    

2. When answering the following questions, assume all the assumptions of the neoclassical (stochastic regressor) simple regression (NSR) model with multivariate normally distributed random disturbances are satisfied.

3. Clearly state your estimated conditional expectation function (sample regression line).

4. With reference  to  your  estimated  equation,  calculate  (using  a  calculator)  a  90% confidence interval for the coefficient of X (i.e.?2 ) in the model.  In performing this calculation, use the relevant estimated standard error of the estimator of the coefficient of X given in your summary Excel regression output. Give an interpretation of the confidence interval you calculate.                 

5. Again with reference to your estimated equation, perform a test of the null hypothesis that the coefficient of number of hours of preliminary training provided to a telemarketer ( ?2 ) equals zero against the alternative that it is greater than zero, using the ? = 0.05 (i.e.  5%) level of significance. In presenting your answer to this question you are required to use the 6-step hypothesis testing procedure given in the unit summary lecture notes. Again in answering this question, you should use the relevant estimated standard error of the estimator of the coefficient of X given in your summary Excel regression output.                                            

6. Give an interpretation of the realized coefficient of determination value ( r 2 ) given in your summary Excel regression output.