Q 1.
A university lecturer at a large private university took a random sample of 55 students from her first-year Business Data Analysis course. She wishes to investigate the relation between the number of hours students spend on casual employment per week and marks attained in the most recent assessment item. For each student, the number of hours worked per week and marks attained were recorded. The data are stored in ‘HoursMark55’ worksheet in the xls. data file attached.
In addition, students were also asked to respond to survey Qs concerning other variables (e.g. major of study chosen, suburb where student lives, country of origin, etc). The lecturer categorised the data collected on the variable suburb where student lives into ten suburbs, and calculated the frequencies as presented in the table below.
|
Suburb
|
|
Category
|
Frequency
|
|
Suburb A
|
3
|
|
Suburb B
|
9
|
|
Suburb C
|
7
|
|
Suburb D
|
6
|
|
Suburb E
|
8
|
|
Suburb F
|
7
|
|
Suburb G
|
5
|
|
Suburb H
|
5
|
|
Suburb I
|
2
|
|
Suburb J
|
3
|
Using the information presented in the table above, answer the following Qs.
- Which graphical technique would you use for making comparison in the number of students in each suburb? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.
- Which graphical technique would you use for making comparison of the proportionof the number of students in each suburb? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.
Q 2
The lecturer wishes to draw histograms in presenting graphical summaries of the data on the two main variables concerned - weekly working hours and marks attained, as stored in ‘HoursMark55’ worksheet.
- She decided to use 7 class intervals to construct a histogram for each variable. Explain how the lecturer could have decided in using 7 as the number of class intervals to be used.
- The lecturer specified class intervals 0 < X ≤ 5, 5 < X ≤ 10, …, 30 < X ≤ 35 for the weekly working hours variable. She then specified class intervals 50 < X ≤ 55, 55 < X ≤ 60, ..., 80 < X ≤ 85 for the marks attained variable. Explain how the lecturer could have decided on the width of each class for these class intervals on each of the two variables.
- Using Excel, draw and display the histogram for the working hours variable using the class intervals specified in part (b). Comment on the shape of the distribution of the data set as displayed in the histogram.
- Using Excel, draw and display the histogram for the marks attained variable using the class intervals specified in part (b). Comment on the shape of the distribution of the data set as displayed in the histogram.
Q 3.
The lecturer would like to present numerical descriptive measures to summarize the data on the two variables: weekly working hours and marks attained.
- Using the relevant Excel function, prepare and display a numerical summary report for each of the two variables including measures such as mean, median, mode, range, variance, standard deviation, smallest and largest values, and the three quartiles. Also, calculate and display the coefficient of variation value for each of the two variables.
- Using the relevant Excel function, compute the correlation value of the relation between the working hours variable and the marks attained variable. Display the correlation value. Then, provide an interpretation of this correlation value in terms of the relationship between the two variables.
Q 4
The lecturer uses a regression analysis to further evaluate the linear relationship between the two variables – working hours per week and marks attained.
- Specify the dependent variable and the independent variable for this regression analysis. Briefly explain the reasoning for your selection.
- Use an appropriate graphical technique to draw a diagram that evaluates the relationship between the two variables. Display the diagram. On the same diagram, fit a linear trend line including the equation and the coefficient of determination R.
- Use Excel to estimate a simple linear regression model. Display the Excel regression summary table and write the estimated linear equation. Then, provide interpretation of the intercept and slope coefficient estimates of the linear model.
- Display the value of the coefficient of determination. Then, provide an interpretation of the coefficient of determination value in terms of the relationship between the two variables.
Q 5
A recording label/entertainment promoter wishes to do a linear regression analysis to predict the concert attendance of an established Korean-pop band in their upcoming tour in 2021. He believes that the expected number of concert attendees is related and can be explained by the number of times that the band’s songs have been downloaded in the social media. Yearly data concerning the two variables are collected and stored in ‘ConcertDownloads15’ worksheet in the xls. data file attached.
- Based on the information provided above, specify the dependent variable and the independent variable for doing the regression analysis. Briefly explain the reasoning for your selection.
- Using Excel, draw a chart to evaluate the number of concert attendance over time. Display your chart and provide a brief comment on what you can observe from the chart
- Using Excel, calculate the correlation value of the relationship between the two concerned variables. Then, provide an interpretation of the correlation calculatedin terms of the relationship between the two variables.
- Use Excel to estimate a simple linear regression model. Display the Excel regression summary table and write the estimated linear equation. Then, provide an interpretation of the slope coefficient estimate of the linear model.
- Display the value of the coefficient of determination. Then, provide an interpretation of the coefficient of determination value in terms of the relationship between the two variables.
Q 6.
Refer to the data stored in ‘ExportImport’ worksheet in the .xls data file attached. The data show Australia’s exports and imports of services by selected regions in 2017 and 2018.
- Use Excel to draw an appropriate graphical technique to compare the value of Australia’s exports of services (in $m) in 2017 and 2018, broken down by region of export destination. Display your chart output. Provide comments on the information you observe from the chart output.
Use Excel to draw an appropriate graphical technique to display the percentage value of Australia’s imports of services (in %) in 2017, broken down by region of import origin. Display your chart output. Provide comments on the information you observe from the chart output.
