# DATA ANALYSIS in BUSINESS

DATA ANALYSIS in BUSINESS

This assignment comprises 10% of the assessment for ETX2121 and 10% for ETF9121.

You must submit a printed “hard copy” of your written work (with an Assignment Cover Sheet – from the “ASSIGNMENTS” section of 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” section & click the “Assignment 1” 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 tutor.

• 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 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 you may insert text boxes at relevant points in the spreadsheet for any comments and explanations.

o Do not print out whole spreadsheets, just sections showing relevant data and output.

• Some Excel help is available via the “EXCEL” section of Moodle.

Question 1

You must use the relevant Excel functions and procedures to generate your output.

Bank auditors investigate accounts by taking a sample, rather than performing a census. For example, auditors may wish to (inter alia) compare the reported value of

account balances with the balances obtained from their own investigations. The following exercise is a simulation of (part of) what the auditors would do when they

are investigating a population of accounts.

The file DAiB A1_Q1.xlsx contains account numbers and balances for all children’s accounts at the Bank of Caulfield on 30 June 2013.

(a) (i) Select a systematic sample of 150 accounts. Explain how you do this.

(ii) Provide summary statistics from Excel’s “Data Analysis”/Descriptive Statistics”.

Do not print out your sample.

(b) The auditors might treat the sample in (a) as if it were a simple random sample. However, if the population of data has a repeating pattern, a systematic

sample can unrepresentative. Would systematic sampling yield an unrepresentative sample here? Discuss.

(c) We wish to know what size of simple random sample is required in order to be 98% confident that the estimate of the mean balance of all children’s accounts is

within $120.

(i) Calculate this sample size.

(ii) Explain why the fpc is, or is not, important in this calculation.

(d) (i) From DAiB A1_Q1.xlsx, select a simple random sample of the size from (c)(i).

(ii) Do not print out your sample. Just provide summary statistics from Excel.

(e) Calculate the observed sampling error from your sample in (d). How does this observed sampling error compare with the maximum probable sampling error given in

(c)?

(f) If the Banking Ombudsman wanted to survey the banking behaviour of 2,000 adult customers of Australian retail banks (eg, CBA, NAB), what might be a suitable

sampling technique to use? Justify your answer.

Question 2

You must use the relevant Excel functions and procedures to generate your output.

Pollsters and psephologists are interested in people’s voting intentions. The following is a simulation of a pre-election poll.

Imagine that we know the “two-party preferred” voting intentions (Labor or Coalition) of a population of 635 voters in a particular electoral subdivision. (See the

file DAiB A1_Q2.xlsx.)

(a) For the population, calculate the proportion of voters who prefer Labor.

Let us now explore the nature of the sampling process by pretending to be a pollster who is sampling from this population.

(b) Select a sample of size 40, stratified by sex and with proportional sample sizes.

(c) For the 40 voters in the sample, calculate the proportion who prefer Labor. Compare this with the value obtained in (a). Explain any difference between these

two values.

(d) Stratified sampling was used here, with stratification according to sex, and with proportional allocation of sample sizes.

(i) Could optimal allocation have been used here? Explain your answer.

(ii) For the situation described in this question,

• why do you think the population was stratified by sex? Explain your answer.

• would any other bases for stratification be useful? Explain your answer.