STA510 Business Statistics Semester 2 2019
Written Assignment: Computer Application Project
(worth 30% of the overall assessment)
DUE DATE: 11:59PM (Darwin time), 26th May 2020
Instructions:
• You will need to download Assignment Question file and data file (Assignment Dataset.XLS) to complete your assignment.
• The data file consists of two sheets: TRAVEL and TVDEBT
• All numerical calculations and graphs/plots should be done using EXCEL as much as possible.
• Only typed assessments in a Word document will be marked. Equations, symbols could be handwritten. You have to copy and paste Excel outputs (eg, plots, tables etc) and images of handwritten Equations, symbols into your main assignment, which is the Word document. Any answer in the Excel document, but not in the main document will NOT be marked.
• The completed assignment (word document) must be submitted electronically via “Written Assignment Submission Point: Word Document ONLY” under Written Assignment folder.
• The Excel file used to estimations must be submitted electronically via “Excel Workbook Submission Point” under Written Assignment folder.
• You are required to keep a hard copy of the submitted assignment to re-submit, in case the original submission is lost for some reason.
Important Notice:
As this is an individual assessment item, students should submit their individual assignment. All assignments submitted will go through a matching process. If found to have cheated/ plagiarised, all submissions involved would receive a mark of zero for this assessment item.
This Assignment consists of 2 Questions
QUESTION 1 [8 Marks]
With increasing congestion and the level of pollution in major cities, governments are trying to reduce the regular use of cars and encourage the use of public transport to travel to work. The following table presents data on the methods of travel used by Australian workers by age groups collected during on the census night for CENSUS 2016. The data are stored in file Assignment Dataset.XLS, TRAVEL sheet available in the Written Assignment folder. Using this data and EXCEL, answer the questions below.
Method of Travel to work by Age
(Employed persons counted at home on census night)
Method of travel 15 to 24 years 25 to 34 years 35 to 44 years 45 to 54 years 55 years or more Total
Train 59,160 66,088 50,508 30,899 14,877 221,532
Bus 63,153 53,371 44,668 30,711 16,251 208,154
Ferry/tram 6,856 10,138 5,519 3,481 2,108 28,102
Taxi 4,203 5,257 4,759 3,705 2,139 20,063
Car as driver 561,563 1,003,159 1,102,709 729,600 324,821 3,721,852
Car as passenger 156,174 127,822 106,694 74,262 32,727 497,679
Motorbike/motor scooter 11,995 23,067 13,802 5,694 2,687 57,245
Bicycle 24,941 26,345 18,503 8,252 3,954 81,995
Walked only 78,735 80,955 78,467 59,249 38,601 336,007
Other 8,188 21,250 23,116 17,275 9,462 79,291
Total 974,968 1,417,452 1,448,745 963,128 447,627 5,251,920

(a) Analyse the method of travel for the total number of employees travelling to work, using appropriate graphical descriptive method(s). Comment on your findings.
(b) Analyse the share of the different age groups of the total employees travelling to work, using appropriate graphical descriptive method(s). Comment on your findings.
(c) Analyse the method of travel by each age group using appropriate graphical descriptive method(s). Comment on your findings.
(d) Suggest ways (200 words maximum) the government could use your results in parts (a)(c) to reduce the regular use of cars and encourage the use of public transport to travel to work by different age groups.
Cont.
QUESTION 2 [22 Marks]
A sociologist theorised that people who watch television frequently are exposed to many commercials, which in turn lead them to buy, resulting in increasing debt. These effects are believed to be vary by demographic and economic characteristics, such as age, gender and income of individuals. To test this belief, a researcher plans to survey a sample of families across the country.
Part 1 [3 marks]
Briefly explain
(a) What type of survey method could the researcher use and why?
(b) What sampling method could the researcher use to select his/her sample and why?
(c) What kind of issues the researcher may face in this data collection?
Suppose the researcher collected data from 400 randomly selected families. For each family, the total debt (in $) and the number of hours the television is turned-on per week (TV hours) were recorded. The data are stored in file Assignment Dataset.XLS TVDEBT sheet available in the Written Assignment folder. Using this data and EXCEL, answer the questions below.
Part 2 [8 Marks]
Firstly, the researcher wishes to use the graphical descriptive methods and numerical descriptive measures to present the data for the two variables.
(a) He/she decides to use 10 class intervals, such as 0 X=6, 6 X=12, 12 X=18, .... for the TV Hours variable and class intervals 0 X=30000, 30000 X=60000, 60000 X=90000, .... for the Total Debt variable. Explain how he/she could have decided on the number of classes as 10.
(b) Draw two histograms for the two variables using appropriate BIN values.
(c) Prepare a brief numerical summary report for the two variables; the total debt and the TV hours by including the summary measures, such as mean, median, variance, standard deviation, smallest and largest values, and the three quartiles, for each variable.
Notes: Use QUARTILE.EXC command to generate the three quartiles
(d) Based on your histograms in part (b) and descriptive summary measures in part (c), comment on the shape of the distribution of the two variables.
Cont.
Part 3 [6 Marks]
Secondly, the researcher wishes to investigate the association between the two variables.
(a) Explain what could be the independent variable (X) and the dependent variable (Y).
(b) Using Excel, compute a numerical measure that measures the direction and strength of the linear relationship between the total debt and TV hours. Interpret this value.
(c) Using an appropriate plot, investigate the relationship between total debt and TV hours.
(d) On the same plot in part (c), fit a linear trend line including the equation and the coefficient of determination (R2). Interpret the trend line equation coefficients and the coefficient of determination.
Part 4 [5 marks]
(a) Based on your findings above, write a brief research report on the relationship between total debt and TV hours (not more than 250 words).
END OF WRITTEN ASSIGNMENT