I have no idea how to do anything on this assignment and I just need someone to do it correctly so i can pass the year.
RMIT Classification: Trusted BUSINESS STATISTICS Assessment 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. 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] 1. 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. 1. Gender: using the gender identification data, create a dummy variable male that identifies male employee as 1 and female as 0. 1. 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. 1. Age: is the numerical data type reflecting the age of an employee. 1. Marital Status: the dummy variable married = Yes (1) if the individual is married and No (0), otherwise. 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. sector, b. gender, c. educational attainment, d. age and e. 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 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) 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. c) Interpret your hypothesis test results. (3 marks) 3. Use the following multiple regression model to explore the relationship of workers’ earnings with variables related to sector, gender, educational attainment, age and marital status Model B: Model C: Model D: Model E: a) Based on the Excel regression outputs, select the best model and explain why it is the best. b) Write down the estimated equation for the best model and interpret the slope coefficients, c) Based on the best model, 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 in c) and d). 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)? (9 marks) 4. Compare the coefficients of public variable in Model A and Model E. Explain carefully why the results are different, relating your discussion to sector wage discrimination. (4 marks) 5. Based on the Model E, 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.5 marks) 6. Based on the result in Question 5, how will your result in Question 5 change if the male/female worker is 50 years old? Explain without any calculation. (2.5 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] (2 marks) (5 + 3 + 9 + 4 + 2.5 + 2.5 + 4 + 2 = 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. 2 Notes 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: genderfemale or male degreeYes = with a university degree; No = without a university degree marriedYes = married; No = unmarried wagehourly wages measured in 2019 AU$ ageage of worker measured in years Public genderagewagedegreemarried Female4520.59375NoNo Female2321.28125NoNo Male4422.5NoYes Female2722.91667YesNo Male2823.10526NoNo Female2423.29545NoNo Female3123.34YesNo Male2824.23684YesNo Female3925YesYes Female2226.31579YesNo Female2727.77778YesNo Female6428.57143NoNo Female4429.22222YesNo Female2529.25YesNo Female2230.66667NoNo Male2830.66667NoNo Female2730.85NoNo Male5430.90909NoYes Male4531.25YesYes Male2831.875NoNo Female4632.43243NoNo Female4333.575YesYes Female6133.625NoYes Male3833.75NoNo Female2634YesYes Male3035.54054YesNo Male2835.71429NoNo Female2835.76923YesNo Female3435.97368NoYes Female2536.43333NoNo Female3236.64444YesYes Male4837.21053NoNo Female2437.25333YesNo Female2937.33333NoNo Male4137.48889YesYes Female3737.5YesYes Female5737.5NoYes Male3239.47368NoNo Male3139.8YesNo Male3140NoNo Female5140.54054NoYes Female3241.09524NoNo Male3341.225YesYes Female4541.36666NoYes Male5341.6NoNo Female3242.13158YesYes Male6342.42424YesNo Female4643.05NoYes Male5845.13044YesYes Male3045.94YesYes Female2946.24NoNo Male4647.5NoNo Male4047.67442YesNo Female5247.71111YesNo Male3948.125YesNo Male6549.47368NoYes Male5950NoNo Male4050YesYes Male5750NoNo Male2650.35YesYes Male3750.875YesNo Female3151.35135NoYes Male4451.45NoNo Male4452.5NoNo Female3152.63158YesNo Female3152.75YesYes Female5554.6YesYes Female4155.55556NoNo Female6055.59459YesYes Female3155.66667YesYes Female5156.52632YesYes Male3957.14286YesNo Male4057.76NoNo Female3057.86842NoNo Male3358.05263NoYes Male3858.45238YesYes Female3661.36666YesNo Male5362.3NoNo Male5664.47369NoYes Male4864.48NoYes Male5464.7YesNo Female4964.725YesNo Male5567.65455YesYes Male6078.91428YesNo Female4382.89474YesYes Male5698.975YesYes Private genderagewagedegreemarried Male363.009027YesYes Male616.521739NoNo Female259.2NoNo Female2210NoNo Male2310.56NoNo Female2713.5YesNo Female2714.58333NoYes Male2116.26667NoNo Male2116.42857NoNo Female3117.5NoNo Male2718NoNo Female4118.2YesYes Female2618.34211NoYes Female3119.18NoNo Female2319.18182NoNo Female2819.67442NoYes Female6319.90625NoNo Male2419.98333NoNo Male5220NoNo Female2720.18421YesNo Female2720.83333NoYes Female4921NoYes Male4721.4NoNo Female4321.4186YesNo Female3121.48889YesNo Female2721.5YesYes Male2721.81818NoNo Female6021.90476NoNo Male5222NoNo Male2222.275NoNo Male2222.5NoNo Male6022.63158NoNo Female4122.64151NoYes Male3923.08NoNo Female3924.56NoYes Female4824.57143NoYes Male3024.95NoYes Male2225NoNo Male2625.56667YesNo Male2225.66667NoNo Male3226NoNo Female4626.24NoYes Female4726.31579NoNo Male3126.90476NoYes Male6027.16667NoYes Female5327.16667NoYes Female2527.34043YesNo Female3527.625NoNo Male6328NoYes Male2828YesNo Female4228.33333YesNo Male4428.33333NoNo Female2828.62YesNo Male2428.775NoNo Male2728.85NoNo Female2628.92105YesNo Female5929