Data Analysis in Business ASSIGNMENT 2

ETF2121/ETF5912 Data Analysis in Business
 
 1
ASSIGNMENT 2
 Due by Monday 5pm of Week 12
 This assignment comprises 15% of the assessment for ETF2121 and 15% of the
assessment for ETF5912, and it consists of questions on Statistical Inference and
Regression Analysis. The data files for this assignment must be downloaded from
the “Assignments” tab of the ETF2121/ETF5912 Moodle site.
 
 You must submit a printed “hard copy” of your written work (with an Assignment
Cover Sheet - from the “Assignments” tab on Moodle) by 5pm on the due date.
Submit it to your tutor in your tutorial (or to your tutor’s mailbox, 5th floor H Block).
ENSURE that you
* submit a printed “hard copy” of your assignment to your tutor.
* obtain a receipt for your submitted assignment by uploading it to Moodle.
 This receipt will prove that you submitted your work, and that it was on
 time. (Go to the “Assignments” tab and click on the “Assignment 2” link to
 upload.)
 There is a penalty of 10% of the marks for each day the assignment is overdue.
Extensions beyond the due date will only be allowed in special circumstances.
Contact your lecturer.
 
 Do not submit your assignment in a folder – stapled pages are easier for the
marker. Save trees! Double-sided printing is encouraged.
 
 Keep your marked assignment work until after publication of final results for the unit.
 
 The assignment should be no more than 15 pages. PAGES EXCEEDING THE
PAGE LIMIT WILL NOT BE MARKED.
 
 Assignments without signed cover sheets WILL NOT BE ACCEPTED.
 
 For each question
o Use the relevant Excel functions and procedures.
o You may insert text boxes at relevant points in the spreadsheet for any
comments and explanations.
o In point form, give details on how the procedure was implemented. You
may use a text box within the spreadsheet for this or type your details in a
WORD document and insert them at the relevant point among your print
outs. Alternatively, you may cut and paste some of the data and your
output and insert it into the WORD document.
o Do not print out whole spreadsheets, but just sections showing relevant
data and output.
 
 Some Excel help is available via the “EXCEL” tab on Moodle. ETF2121/ETF5912 Data Analysis in Business
 
 2
 
Question 1
Use the relevant Excel functions and procedures to generate your output.
 
Consider a random sample of 100 households from a middle-class neighbourhood that
was the recent focus of an economic development study conducted by the local
government. Specifically, for each of the 100 households in the sample that is in one of
four local government locations, information was gathered on the gross annual income of
the household (in $) and on each of several other variables. The data are given in the
EXCEL file A2_1. Economic researchers would like to test for existence of a significance
difference between the mean annual income levels of householders in every pair locations
(that is, first and second; first and third; first and fourth; second and third; second and
fourth; third and fourth).
 
a) Before conducting any hypothesis test on the difference between the various pairs
of mean income levels, perform a test for equal population variances in income
for each pair of locations. Comment on your findings.
b) Given your conclusions in Part (a), perform a test for the existence of a difference
in mean annual income levels in each pair of locations. Comment on your
findings.
 
 
Question 2
Use the relevant Excel functions and procedures to generate your output.
 
Continuing on from Question 1, economic researchers want to understand the relationship
between the size of monthly mortgage or rent payment for household in this particular
neighbourhood and the following set of variables: family size, location of the household
with the neighbourhood, an indication whether those surveyed own or rent their home;
gross annual household income (in $); average monthly expenditure on utilities (in $) and
the total indebtedness (in $ and excluding the value of monthly mortgage or rent
payment) of the household. The data are in the EXCEL file A2_1.
 
a) Fit a regression model to predict the size of the monthly mortgage or rent
payments using all the explanatory variables. Explain whether multicollinearity is
a problem.
b) Fit the best regression model to predict the size of the monthly mortgage or rent
payments. Explain why this is the best regression model.
c) Comment on the goodness of fit of the model from Part (b).
d) Interpret each of the regression coefficients for the fitted model in Part (b).
e) For the fitted model in Part (b), are there any possible outliers? If there are, take
the necessary action.
f) For the fitted model in Part (b), after taking Part (e) into account, check for
violations of the regression assumptions.
 
 
 ETF2121/ETF5912 Data Analysis in Business
 
 3
Question 3
Use the relevant Excel functions and procedures to generate your output.
 
The EXCEL file A2_3 contains the following information for the years 1993 to 2010:
 Domestic Car sales (in thousands)
 Real price index for new car prices (where 1987 = 100 is the base index)
 Real disposable income (in 1992 dollars)
 Interest rate
 
a) Fit a multiple linear regression model to predict domestic car sales. Analyse the
fitted model.
b) Fit a multiplicative model to predict domestic car sales. Interpret the coefficients.
c) Compare the two estimated models from Parts (a) and (b). Explain which is the
better model.
d) During a year in which the real price index is 250, the interest rate is 12% and the
real disposable income per person is $3,500, and using the multiplicative model,
there is a 5% chance that car sales will be less than what particular value. (Hint:
Use the standard error of prediction and the fact that the errors are approximately
normally distributed)