PROJECT DATA FILE: Employee Satisfaction.xlsxPreview the document
This data file contains information on an organization’s employees and their employment characteristics, as well as responses on an internal employee survey to 4 dimensions of work and/or life satisfaction.
There are 10 tasks required to complete this assignment. Work each within the attached Excel file, then submit your completed workbook.
1. Prepare a table using COUNTIF that shows the number of employees in each job position (you’ll need to get an unduplicated list of positions).
2. Use IF to create a new variable (column) that shows with a 0/1 “one-hot” or “dummy”-coded value whether an employee’s last performance rating was at least 4.0 (0 = < 4.0, 1 = > 4.0).
3. Prepare a table using either COUNTIFS or SUMIF that keys off of your new 0/1 variable and shows the number of employees in each job position with performance ratings of at least 4.0.
4. Prepare a table using AVERAGEIF that shows the average performance rating in each job position (you’ll again need to get an unduplicated list).
Then, use VLOOKUP to create a new column so you can see the average performance rating for each employee’s job position next to their own performance rating (insert the new column next to the employee rating).
5. Use IF to create a new variable (column) that shows with text of “Above Avg” or “Below Avg” whether an employee’s last performance rating was above the average for their job position (key off of your column above).
6. Prepare a table using AVERAGEIFS that shows the average raise amount in a cross-tabulation of job position vs. above/below average performance ratings (job position should be the rows in your table).
7. Use a PivotTable with Count and the “Show values as % of row [or column] total” option to prepare data for a column chart to compare the % of men / women within each job position. Format the chart appropriately.
Repeat this analysis (and create a new chart) for the % men / women within each job level by copy-and-pasting your PivotTable within the sheet.
8. Use a PivotTable with Average and grouped Row Labels to create a line chart showing the average # of previous employers at time of hire for current employees who were hired over the past 11 years of hire dates (filtered to 2010 to 2020, grouped by year). Add data labels and format.
(You might want to try adding Gender to the Column Labels area in your PivotTable to see whether the trend differs by gender category.)
9. Use a PivotTable with Average to create a table showing the average monthly income for men vs. women within each of the titled job positions. Sort this table by the overall average for each position, and create a horizontal bar chart. Add data labels and format.
(You might want to try copy-pasting this PivotTable [so you can modify it], then using the “Show values as % Difference from…” option with “Gender” as the “Base Field” and “Male” as the “Base Item” to show the pay differentials as a % difference across gender categories.)
10. Use a PivotTable with Count and a hierarchical / nested Row Label structure + the “Show values as % of Parent Row” option to create the data for two pie charts comparing the distribution of work-life satisfaction responses across employees with and without required overtime.
Create the two pie charts based on the response percentages for these two employee groups. Make sure to arrange the 4 different response options in the correct order, and make sure to copy-paste or use a cell reference “pointer” formula to get the data out of the PivotTable first.
We value our customers and so we ensure that what we do is 100% original..
At Custom Writing, we believe in exemplary services that are fully geared toward customer satisfaction. That is why we don’t shy away from giving you the following guarantees;
Trusting us with your work is the best decision you have made, our pleasure lies in seeing you satisfied at 100%. If in the rear chance it happens that you are not satisfied, then know that we will equally not be satisfied. But worry not, our 30 days- Money back guarantee is all you need and that is what we promise you..Read more
We utilize profoundly equipped and gifted writers who produce unique papers liberated from any form of plagiarism. To guarantee this, we run all papers finished by our scholars through a Plagiarism checker to ensure uniqueness and originality. In any case, on the off chance that you have vulnerabilities about the originality or falsification of any paper we have finished and conveyed to you, please get in touch with us straight away. We will quickly investigate, and if the paper is seen as counterfeited, we will take suitable actions including but not limited to, revising the paper for free and in extreme cases we will activate the money back guarantee.Read more
We have an obligation deliver great and specially composed assignments. Our revision strategy endeavors to ensure total client satisfaction, comfort, and a genuine feelings of serenity. We make minor updates and corrections to the underlying request as part of our continuous assistance. However, revisions should just incorporate changes and alterations that were not effectively met, in the underlying request and that are inside the rules as per the current request structure..Read more
Our client's Data is an Integral part of our business but clearly, we are not in the business of offering our clients' very own data to others. We realize that you care how your online data is utilized and shared, we equally value your trust that we will do so cautiously and sensibly. We Promise to ensure the security of your own data during transmission by utilizing encryption conventions and programming. Likewise assist us with securing your information by not sharing your passwords and usernames.Read more
In submitting a request with us, you consent to the services we give. We will strive to take the necessary steps to convey a far reaching paper according to your prerequisites. Equally we depend on your cooperation to guarantee that we convey on this order.Read more