Case Analysis #1
Instructions: For this case analysis, you’ll be using several of the concepts and skills we’ve covered thus far. Specifically, you’ll use the lookup formulas, pivot tables/charts, and you’ll be summarizing data in terms of descriptive statistics. As a final submission, you will need to submit two documents. 1. Your Excel file showing the lookup formulas, pivot tables/charts, and any other data visualizations or formulas you’ve created. 2. A written technical “report” (in Word Document or PDF format) that includes a title page, plus a 2-3 page long report. The report should include in text tables/charts as needed (the 2-3 pages should be single spaced, but that also includes tables/charts which will take up space). All of your work should be cleaned up and high quality – something you’d be comfortable sharing in a professional environment.
Scenario: Your organization has recently experienced exceptionally high levels of turnover and relatively poor performance in several departments. You have now collected data evaluating three outcomes that were deemed important through an initial round of interviews with managers. The three outcomes are satisfaction with opportunities, burnout, and job performance. The primary goal of this report is to investigate how each department is doing in regard to the three indicators collected (satisfaction with opportunities, burnout, and performance). However, the organization has also increasingly been concerned with creating an inclusive workplace. Thus, the stakeholders have also asked that you provide summary statistics that would help show any differences among demographic groups on these three ratings and/or any drastic differences in representation in terms of headcounts. Note, this organization is located in Alameda County, a demographically diverse area.
Overall, the stakeholders have asked for you to do the following:
- Investigate how each department is doing in the three outcomes that were evaluated (i.e., satisfaction with opportunities, burnout, performance).
- Investigate if there are any equity-related concerns across the three outcomes (satisfaction with opportunities, burnout, and performance). Specifically, the stakeholders are interested in if there are equity concerns within any of the departments.
- Recommendations to leadership/management: Based on your findings, the stakeholders want you to develop a set of recommendations for leadership/managers.
- Recommendations for next steps in evaluation: Based on your findings, the stakeholders want you to develop a set of recommendations for next steps in the evaluation process. This should include three components: 1) what additional information you want to collect, 2) why that information should be collected, and 3) include a 5-10 question follow-up survey to administer to employees and/or managers. You may indicate that the survey should be directed at a specific department and/or group of employees or to all departments/employees.
Step 1 (Excel): Although you have collected the data, the data are now stored on three separate files. Using the Vlookup or Xlookup formula(s), move the data from the “Employee Survey Data” and “Performance Data” sheets onto the “HR Data Sheet”. Note, you must use the lookup formulas to accomplish this (copy/paste or other methods will receive 0 points).
Step 2 (Excel): Clean the data in accordance with the “Data Explanation” tab. Note, you do not need to clean the ID, Supervisor ID, Dept, Job Level, or Race/Ethnicity columns.
Section 3 (Excel): Use pivot tables/charts, and/or the various Excel formulas (e.g., averageif, countif, etc.) to create summary tables and charts for the data. I suggest creating multiple separate pivot tables/charts for this section. Then you can copy all or only a subset of them into your report. What you include in the report should be what you view as the most informative and important pieces of information given the stakeholders’ goals for the project.
Note, there are of course many comparisons and descriptive statistics you can provide. You do not need to provide every single possible breakdown in the report. However, at a minimum, you should analyze and provide a breakdown for the three outcomes of interests (satisfaction with opportunities, burnout, and performance), based on department, sex, and race/ethnicity. In the report you must include at least one table and at least one chart.
Section 4 (Word Document/PDF): Provide a short technical report (2-3 pages single spaced in length), that includes at least one table and one chart. Note if you include multiple tables/charts (which I do encourage) you may need to go over the 3 pages, which is perfectly okay. The 2-3 pages is a rough guide. The report should detail, what steps you took in creating the report, what you found, what you recommend to leaders/managers, and what you recommend for next steps in evaluation (e.g., collecting more data/information). The report can be organized in any manner you see fit, but it might be helpful to organize the report by job role, then walk through the demographic comparisons. All material in the report should be clean, and high quality. It should be something you’d feel comfortable sharing in a corporate environment. The earned points will be based on both meeting the requirements outlined in this document as well as the quality of that work.
|Rubric – Case Analysis #1|
|Excel Sheet: Vlookup or Xlookup formulas are appropriately used to transfer data from the Employee Survey Data and Performance Data tabs onto the HR Data Sheet Tab.||5|
|Excel Sheet: After data are all stored on the HR Data Sheet tab, data are cleaned appropriately (in line with the Data Explanation tab).||5|
|Excel Sheet: Uses formulas (e.g., averageif, countif, etc.) and/or pivot tables/charts to obtain summary statistics for satisfaction with opportunities, burnout, and performance. At a minimum, comparisons for each outcome are conducted by department, sex, and race/ethnicity.||5|
|Word Document: 2-3 page “technical report” that includes the following components (could be longer if many tables/figures are included): Provides brief details about the steps you took.Details the descriptive summary (statistics) of what is happening in regard to satisfaction with opportunities, burnout, and performance based on department, sex, and ethnicity.|
Provides at least one high quality table which provides descriptive statistics (e.g., counts, averages, standard deviations, etc.) and supports your interpretation of what is happening in the organization.
Provides at least one high quality data visualization supporting your interpretation(s) about what is happening in the organization. Recommendations to leadership/management: Based on your findings, the stakeholders want you to develop a set of recommendations for leadership/managers.Recommendations for next steps in evaluation: Based on your findings, the stakeholders want you to develop a set of recommendations for next steps in the evaluation process. This should include three components: 1) what additional information you want to collect, 2) why that information should be collected, and 3) include a 5-10 question follow-up survey to administer to employees and/or managers. You may indicate that the survey should be directed at a specific department and/or group of employees or to all departments/employees. Includes a final high-level paragraph with the take-away message(s).
Do you need urgent help with this or a similar assignment? Say no more, we have just the experts you need to help you. Place your order.