Cornell Every Australian Employee Is Entitled to Minimum Wage as Set by GOVT Assessment

BUSINESS STATISTICSAssessment 3: Individual Assignment
Instructions:
This is an individual assignment with a total of 40 marks. The allocation of marks is as follows:
Statistical Analysis with Excel File:
32
Professional Report:
8
Total:
40
Report Structure
The response must be provided in the form of a professional report with no more than 10
pages (excluding the cover page).
The structure of your professional report must include:
• A Title,
• An Executive Summary,
• An Introduction,
• Analysis, and
• Conclusions.
Submission
•
You must submit an electronic copy of your assignment on Canvas. See the attached
Template of your submission for more details.
Excel Work
This assignment requires the use of Microsoft Excel. Using Data Analysis Tool-Pack will
assist tremendously in getting through the assignment requirements.
You need to submit the Excel file along with your report. The excel file needs to be clear and
carefully organized and must show all workings underlying the Professional report and
associated statistical analysis. It will be treated as an appendix to your report, i.e., not included
in the page count.
DO NOT leave references to the excel workbook within the Professional report as
responses to the questions. You will need to take relevant results from your Excel workbook
and incorporate them into your report. The report needs to be standalone.
1
Presentation Instructions:
Your written professional report should comply with the following presentation standards:
1) Typed using a standard professional font type (e.g. Times Roman), 12-point font size.
2) 1.5-line spacing, numbered pages, and clear use of titles and section headings.
3) Delivered as a Word (.doc or .docx) or PDF (.pdf) file.
4) Checked for spelling, typographical and grammatical errors. Where relevant, round to 3
decimal places.
5) With all relevant tables and charts, the report should be no more than 10 pages long.
Problem Description:
This is a further analysis of the public-private pay gap for individuals with similar productive
characteristics in the Australian population. Mahuteau et al. (2017) report that (1) on average
public sector workers earn about 5.1% more hourly wages than those in the private sector and
(2) that this wage premium (comparatively higher wages in public sector) is slightly higher for
females than males. Systematic remuneration differences for employees with similar
productive capabilities potentially has both efficiency and equity consequences.
In order to estimate the extent of discrimination in the job market where public servants with
the identical labour market characteristics as their private counterparts receive different wages,
you will estimate a set of linear regression models.
Since this is an additional analysis on the public-private pay gap, the content in the Introduction
section of your report may overlap with the one in the Group Assignment submitted earlier.
However, you are encouraged to develop/source new background materials.
You will use the same dataset as in Assignment 2. The data are drawn from the 2019
Household, Income and Labour Dynamics in Australia (HILDA) survey. The sample used for
analysis comprises 219 full-time Australian workers in the age group 21-65.
The dataset values can be interpreted and be used to create appropriate variables as follows:
1. Worker’s Wages: the variable wage records hourly earnings in AU dollars of full-time
workers [note the unit of measurement]
2. Sector: Public and private sector identification data can be converted into a dummy
variable named as “public”, with 1 representing public employee else 0 for private
employee.
3. Gender: using the gender identification data, create a dummy variable male that
identifies male employee as 1 and female as 0.
4. Educational attainment: the dummy variable degree = Yes (1) if the individual has a
bachelor’s degree or higher qualification, and = No (0) for lower than degree
qualifications.
5. Age: is the numerical data type reflecting the age of an employee.
6. Marital Status: the dummy variable married = Yes (1) if the individual is married
otherwise No (0).
2
Locate the data file (IndividualBusStats.xls) on CANVAS.
REQUIREMENT:
1. Before estimating the regression equation, conduct an overall preliminary analysis of the
relationship between workers’ wages and
a.
b.
c.
d.
e.
sector,
gender,
educational attainment,
age and
marital status.
Use tables and/or appropriate graphs for the categorical variables (male, public, degree,
married) and the numerical variable (age).
Interpret your findings by comparing and contrasting the earnings of the counterparts based
on each of these dummy variables and also explain the kind of relationship you observe
between workers’ earnings and age?
(5 marks)
2. Use a simple linear regression to estimate the relationship between workers’ earnings and
the variable public (Model A). You may use the Data Analysis Tool Pack. Based on the
Excel regression output:
a) Write down the estimated regression equation,
b) Interpret the slope coefficient,
c) Carry out any relevant two-tailed hypothesis test of the slope coefficient using the
critical value approach, at the 5% significance level, showing the step by step
workings/diagram in your report.
d) Interpret your hypothesis test results.
(4 marks)
3. Use a multiple regression model to explore the relationship of workers’ earnings with
variables related to sector, gender, educational attainment, age and marital status (Model
B). You may use Data Analysis Tool Pack for this. Based on the Excel regression output:
a) Write down the estimated regression equation,
b) Interpret the slope coefficients,
c) Carry out any relevant two-tailed hypothesis tests for each individual slope
coefficient using the p-value approach, at the 5% significance level.
d) Carry out an overall significance test using the p-value approach.
e) Carefully interpret your hypothesis test results.
f) Are your regression findings with regards to public-private wage gap broadly
consistent with those reported in the study of Mahuteau et al. (2017)?
(8 marks)
4. Interpret the R-squared in Model A and adjusted R-squared in Model B. Which one is a
better model? Explain why, relating your answer to the interpretations.
(2 marks)
3
5. Compare the coefficients of public variable in Model A and Model B. Explain carefully
why the results are different, relating your discussion to sector wage discrimination.
(4 marks)
6. Predict the earnings of a 40-year-old male, university qualified and married public worker.
Next, predict the earnings of a female worker with the same characteristics.
(2 marks)
7. Another conclusion from Mahuteau et al. (2017) is that the wage premium (comparatively
higher wages) for the workers in the public sector is slightly higher for females than males.
Conduct appropriate regression analyses to examine whether your findings based on 2019
data are broadly consistent with those reported in the study.
(4 marks)
8. If you could request additional data to study the factors that influence workers’ earnings,
what extra variables would you request? Discuss two such variables, explaining why you
choose them and how each of your proposed variables could be measured in the regression
model. [You could draw evidence from journal articles, newspapers, etc]
(3 marks)
(5 + 4 + 8 + 2 + 4 + 2 + 4 + 3 = 32 marks)
(Professional report = 8 marks)
Reference:
Mahuteau, S, Mavromaras, K, Richardson, S & Zhu, R 2017, ‘Public–private sector wage
differentials in Australia’, Economic Record, vol. 93, pp. 105-121.
4
Data overview:
The data set is a random sample of 219 employees from the public and private sectors in Australia in 2019.
The tab “public” includes the employees from the public sector.
The tab “private” includes the employees from the private sector.
Sources: the 2019 HILDA (Household, Income and Labour Dynamics in Australia) survey
Variable Definition:
gender
female or male
degree
Yes = with a university degree; No = without a university degree
married Yes = married; No = unmarried
wage
hourly wages measured in 2019 AU$
age
ustralia in 2019.
gender
Female
Female
Male
Female
Male
Female
Female
Male
Female
Female
Female
Female
Female
Female
Female
Male
Female
Male
Male
Male
Female
Female
Female
Male
Female
Male
Male
Female
Female
Female
Female
Male
Female
Female
Male
Female
Female
Male
Male
Male
Female
Female
Male
Female
Male
Female
age
45
23
44
27
28
24
31
28
39
22
27
64
44
25
22
28
27
54
45
28
46
43
61
38
26
30
28
28
34
25
32
48
24
29
41
37
57
32
31
31
51
32
33
45
53
32
wage
degree
20.5938 No
21.2813 No
22.5 No
22.9167 Yes
23.1053 No
23.2955 No
23.34 Yes
24.2368 Yes
25 Yes
26.3158 Yes
27.7778 Yes
28.5714 No
29.2222 Yes
29.25 Yes
30.6667 No
30.6667 No
30.85 No
30.9091 No
31.25 Yes
31.875 No
32.4324 No
33.575 Yes
33.625 No
33.75 No
34 Yes
35.5405 Yes
35.7143 No
35.7692 Yes
35.9737 No
36.4333 No
36.6444 Yes
37.2105 No
37.2533 Yes
37.3333 No
37.4889 Yes
37.5 Yes
37.5 No
39.4737 No
39.8 Yes
40 No
40.5405 No
41.0952 No
41.225 Yes
41.3667 No
41.6 No
42.1316 Yes
married
No
No
Yes
No
No
No
No
No
Yes
No
No
No
No
No
No
No
No
Yes
Yes
No
No
Yes
Yes
No
Yes
No
No
No
Yes
No
Yes
No
No
No
Yes
Yes
Yes
No
No
No
Yes
No
Yes
Yes
No
Yes
Male
Female
Male
Male
Female
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Female
Female
Female
Female
Female
Female
Female
Male
Male
Female
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Female
Male
63
46
58
30
29
46
40
52
39
65
59
40
57
26
37
31
44
44
31
31
55
41
60
31
51
39
40
30
33
38
36
53
56
48
54
49
55
60
43
56
42.4242 Yes
43.05 No
45.1304 Yes
45.94 Yes
46.24 No
47.5 No
47.6744 Yes
47.7111 Yes
48.125 Yes
49.4737 No
50 No
50 Yes
50 No
50.35 Yes
50.875 Yes
51.3514 No
51.45 No
52.5 No
52.6316 Yes
52.75 Yes
54.6 Yes
55.5556 No
55.5946 Yes
55.6667 Yes
56.5263 Yes
57.1429 Yes
57.76 No
57.8684 No
58.0526 No
58.4524 Yes
61.3667 Yes
62.3 No
64.4737 No
64.48 No
64.7 Yes
64.725 Yes
67.6546 Yes
78.9143 Yes
82.8947 Yes
98.975 Yes
No
Yes
Yes
Yes
No
No
No
No
No
Yes
No
Yes
No
Yes
No
Yes
No
No
No
Yes
Yes
No
Yes
Yes
Yes
No
No
No
Yes
Yes
No
No
Yes
Yes
No
No
Yes
No
Yes
Yes
gender
Male
Male
Female
Female
Male
Female
Female
Male
Male
Female
Male
Female
Female
Female
Female
Female
Female
Male
Male
Female
Female
Female
Male
Female
Female
Female
Male
Female
Male
Male
Male
Male
Female
Male
Female
Female
Male
Male
Male
Male
Male
Female
Female
Male
Male
Female
age
36
61
25
22
23
27
27
21
21
31
27
41
26
31
23
28
63
24
52
27
27
49
47
43
31
27
27
60
52
22
22
60
41
39
39
48
30
22
26
22
32
46
47
31
60
53
wage
degree
3.00903 Yes
6.52174 No
9.2 No
10 No
10.56 No
13.5 Yes
14.5833 No
16.2667 No
16.4286 No
17.5 No
18 No
18.2 Yes
18.3421 No
19.18 No
19.1818 No
19.6744 No
19.9063 No
19.9833 No
20 No
20.1842 Yes
20.8333 No
21 No
21.4 No
21.4186 Yes
21.4889 Yes
21.5 Yes
21.8182 No
21.9048 No
22 No
22.275 No
22.5 No
22.6316 No
22.6415 No
23.08 No
24.56 No
24.5714 No
24.95 No
25 No
25.5667 Yes
25.6667 No
26 No
26.24 No
26.3158 No
26.9048 No
27.1667 No
27.1667 No
married
Yes
No
No
No
No
No
Yes
No
No
No
No
Yes
Yes
No
No
Yes
No
No
No
No
Yes
Yes
No
No
No
Yes
No
No
No
No
No
No
Yes
No
Yes
Yes
Yes
No
No
No
No
Yes
No
Yes
Yes
Yes
Female
Female
Male
Male
Female
Male
Female
Male
Male
Female
Female
Male
Male
Male
Female
Female
Male
Male
Female
Male
Female
Male
Female
Male
Male
Male
Female
Male
Female
Male
Male
Female
Male
Female
Male
Female
Female
Male
Male
Female
Female
Male
Female
Male
Female
Female
Male
25
35
63
28
42
44
28
24
27
26
59
23
40
22
50
65
28
30
37
32
25
32
51
35
27
38
64
26
34
27
37
33
23
30
37
41
27
26
35
29
55
42
22
30
61
41
29
27.3404 Yes
27.625 No
28 No
28 Yes
28.3333 Yes
28.3333 No
28.62 Yes
28.775 No
28.85 No
28.9211 Yes
29.0833 No
29.3091 No
29.6977 Yes
31 Yes
31.1111 No
31.125 Yes
31.2093 Yes
31.4286 Yes
31.6667 No
32.3438 No
32.4211 No
32.5 No
33.1861 No
33.3333 No
33.4 Yes
34.375 No
34.8727 No
34.92 Yes
37.093 No
37.4474 No
37.5 No
37.6 Yes
38.1579 No
38.35 No
38.35 No
38.4615 No
38.8 No
38.8519 No
39 Yes
39.5 No
39.85 Yes
39.95 No
40 No
40.0455 No
41.6667 Yes
41.8364 Yes
42.4419 No
No
No
Yes
No
No
No
No
No
No
No
Yes
No
Yes
No
Yes
No
No
No
Yes
No
No
Yes
Yes
Yes
Yes
No
No
No
Yes
Yes
No
Yes
No
No
No
Yes
No
No
Yes
No
No
Yes
No
No
No
No
No
Female
Male
Male
Male
Female
Male
Male
Male
Female
Male
Male
Male
Female
Female
Female
Female
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
59
37
29
46
32
50
30
33
33
40
44
26
58
31
36
46
32
55
46
37
43
64
36
36
49
31
60
42
35
31
38
38
43
38
63
56
64
31
53
44
42.5135 No
43.0222 No
43.15 No
43.16 Yes
43.3429 Yes
43.8597 No
44.6 No
44.7556 No
44.8667 Yes
46.02 No
46.02 Yes
46.4211 Yes
47.95 Yes
48.075 No
48.2 Yes
48.9 Yes
49.0222 Yes
50.9286 Yes
52.94 Yes
56.25 Yes
56.4 No
57.525 No
61.38 Yes
61.5 No
61.7209 No
68.9474 Yes
70 No
70.2128 Yes
70.8333 Yes
73.1 No
73.64 Yes
78.26 Yes
79.6857 No
79.8889 Yes
80 Yes
87.5 No
91.1765 No
100 No
100 Yes
100.581 No
Yes
No
No
No
No
Yes
Yes
No
No
No
Yes
No
Yes
No
No
No
No
No
No
No
Yes
Yes
Yes
No
Yes
Yes
Yes
No
No
Yes
Yes
No
Yes
No
No
Yes
Yes
No
Yes
No