SHOW WORK FOR EXCEL GRAPH
Golden Pond Retirement Community (GPRC) is a home for elderly individuals with moderate income. The facility provides residents with meals, social activities, and assistance with the activities of daily living (e. bathing, medications, dressing, and transportation). The table below summarizes the average annual wage for Golden Pond employees: Pay Grade 1 1 2 2 2 2 3 3 Golden Pond Job Title Custodian Dishwasher Activities Assistant Dietary Server Housekeeper Security Assistant Cook Personal Care Assistant Receptionist Administrative Assistant Admissions Clerk Cook Development Clerk Mechanic Activities Director Dietary Supervisor Facilities Supervisor Housekeeping Supervisor Admissions Coordinator Business Manager Personal Care Director Development Director Executive Director 3 4 4 4 Average Annual Number of Wages Employees $ 20,384 4 $ 19,760 3 $ 25,168 2 $ 24,128 16 $ 24,710 6 $ 26,624 5 $ 31,200 3 $ 27,456 26 $ 30,680 3 $ 29,640 2 $ 33,280 2 $ 42,640 3 $ 37,440 1 $ 54,080 2 $ 41,600 1 $ 66,560 1 $ 63,232 1 $ 56,160 2 $ 87,360 1 $ 90,064 1 $ 85,010 1 $ 91,520 1 $ 105,019 1 4 4 5 5 5 5 6 6 6 7 8 As the table above indicates, GPRC has 88 employees occupying 23 different job titles that are part of an 8-level pay structure (L.e. 8 pay grades). The business manager for GPRC gathered salary data from numerous salary surveys. The salary data collected is shown in the table below: See below to learn how this value was calculated. Survey State Healthcare Salaries Survey State Healthcare Salaries Survey State Healthcare Salaries Survey State Healthcare Salaries Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics SHRM Survey SHRM Survey SHRM Survey SHRM Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Compensation Consultant Survey Compensation Consultant Survey Compensation Consultant Survey Compensation Consultant Survey Average Survey Pay Annual Sample Grade Wages Size 2 22,594 2420 3 $ 28,721 4,512 4 $ 42,593 1,180 5 $ 54,998 1,477 4 $ 46,530 122 5 $ 59,475 126 6 $ 96,811 91 7 $ 106,492 74 8 $149,348 13 1 $ 19,802 25,251 2 $ 24,554 36,440 3 $ 36,439 78,122 4 $ 50,850 54,682 5 $ 61,500 24,008 6 $ 100,641 48,987 7 $ 103,920 18,111 8 $125,530 18,407 5 $ 74,885 3,720 6 $ 102,829 2,987 7 $129,615 2,673 8 $196,510 3,147 1 $ 26,522 452 2 $ 32,382 884 3 $ 37,369 1,253 4 $ 51,937 1,288 5 $ 72,688 1,261 6 $ 99,074 449 5 $ 69,746 150 6 $ 85,537 150 7 $ 92,677 150 8 $ 110,041 150 As displayed, 6 sources of salary data were used. Each survey source contained information for numerous job titles. The GPRC business manager read through all of the surveys and selected the job descriptions in each survey that were equivalent to GPRC jobs. The equivalencies were based on the similarity of required knowledge, skills, and abilities. The corresponding pay data for the jobs listed in the surveys were then grouped into equivalent GPRC pay grades to form a survey average annual wage. AL AaBbCcDdEe AaBbCcDdEe Normal No Spacing For example, the State Healthcare Salaries Survey contained salary information for Waitress/Waiter, Housekeeper, Maintenance Worker, Janitor, Driver, Activities Aide, and Security Staff. The business manager assessed all of these jobs as being equivalent to GPRC jobs in pay grade 2 (i.c. Activities Assistant, Housekeeper, Dietary Server, Security). The business manager then used the salary information in the survey to calculate a weighted average annual salary (977,271,480 + 3,420 - $22,594) for the State Healthcare Salary Survey in pay grade 2. The table below summarizes the business manager's calculations: Average Assigned Average Annual GPRC Pay Annual Sample Wages x Grade Wages Size Sample Size $22,820 491 $11,204,591 $ 22,597 467 $10,552,799 2 $ 22,598 419 $ 9,468,562 2 $ 22,371 510 $ 11,409,375 $ 22,592 455 $ 10,279,360 $22,593 565 $12,765,045 2 $ 22,596 513 $11,591,748 State Healthcare Survey Waitress/Waiter Housekeeper Maintenance Worker Janitor Driver Activities Aide Security Staff NN NNN Total 3,420 77,271,480 Survey (Market Wage) $ 22,594.00 -- Number of businesses reporting wage data for this job description. - Weighted Average Annual Salary for jobs in the State Healthcare Salary Survey that are equivalent to jobs in GPRC's pay grade 2. Assignment: Using the GPRC and salary survey data provided above (ie the first two tables, not the third), construct a line chart that displays GPRC's pay structure relative to the external market. In your chart, create two lines for the external market. The first external market line should include survey data from all sources. The second external market line should include data from all sources except the Bureau of Labor and Statistics (BLS). Be sure to use weighted averages to create your market lines. I have included a sample chart below to give you a better understanding of what I want you to produce. The gray, blue, and orange lines on the chart are based on completely different data. However, your X axis should display Pay Grades 1 through 8 and your Y axis should be in dollars. Focus of red and the $100.000 $90.000 Suneyf No Data $30,000 570.000 W550.000 Golden Pond RC SRDCORE 550.000 Survey $20,000 $30.000 $20,000 $10.000 50 1 2. Pay Grade Pa/Grace After you create the chart, answer the following two questions: 1. Based on the chart you created, do you believe that GPRC is executing a lead, match, or lag pay level strategy? Explain your answer. 2. Which market line (with BLS data or without BLS data) do you think most accurately reflects reality? Why? So, your final deliverable should include an Excel chart showing three trend lines and the written answers to two questions. Some things that I will be looking for on the chart are all of the necessary titles, legends, axis labels, and units. There should only be three lines on the chart, as described above. Golden Pond Retirement Community (GPRC) is a home for elderly individuals with moderate income. The facility provides residents with meals, social activities, and assistance with the activities of daily living (e. bathing, medications, dressing, and transportation). The table below summarizes the average annual wage for Golden Pond employees: Pay Grade 1 1 2 2 2 2 3 3 Golden Pond Job Title Custodian Dishwasher Activities Assistant Dietary Server Housekeeper Security Assistant Cook Personal Care Assistant Receptionist Administrative Assistant Admissions Clerk Cook Development Clerk Mechanic Activities Director Dietary Supervisor Facilities Supervisor Housekeeping Supervisor Admissions Coordinator Business Manager Personal Care Director Development Director Executive Director 3 4 4 4 Average Annual Number of Wages Employees $ 20,384 4 $ 19,760 3 $ 25,168 2 $ 24,128 16 $ 24,710 6 $ 26,624 5 $ 31,200 3 $ 27,456 26 $ 30,680 3 $ 29,640 2 $ 33,280 2 $ 42,640 3 $ 37,440 1 $ 54,080 2 $ 41,600 1 $ 66,560 1 $ 63,232 1 $ 56,160 2 $ 87,360 1 $ 90,064 1 $ 85,010 1 $ 91,520 1 $ 105,019 1 4 4 5 5 5 5 6 6 6 7 8 As the table above indicates, GPRC has 88 employees occupying 23 different job titles that are part of an 8-level pay structure (L.e. 8 pay grades). The business manager for GPRC gathered salary data from numerous salary surveys. The salary data collected is shown in the table below: See below to learn how this value was calculated. Survey State Healthcare Salaries Survey State Healthcare Salaries Survey State Healthcare Salaries Survey State Healthcare Salaries Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Local Chamber of Commerce Survey Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics Bureau of Labor and Statistics SHRM Survey SHRM Survey SHRM Survey SHRM Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Trade Association Survey Compensation Consultant Survey Compensation Consultant Survey Compensation Consultant Survey Compensation Consultant Survey Average Survey Pay Annual Sample Grade Wages Size 2 22,594 2420 3 $ 28,721 4,512 4 $ 42,593 1,180 5 $ 54,998 1,477 4 $ 46,530 122 5 $ 59,475 126 6 $ 96,811 91 7 $ 106,492 74 8 $149,348 13 1 $ 19,802 25,251 2 $ 24,554 36,440 3 $ 36,439 78,122 4 $ 50,850 54,682 5 $ 61,500 24,008 6 $ 100,641 48,987 7 $ 103,920 18,111 8 $125,530 18,407 5 $ 74,885 3,720 6 $ 102,829 2,987 7 $129,615 2,673 8 $196,510 3,147 1 $ 26,522 452 2 $ 32,382 884 3 $ 37,369 1,253 4 $ 51,937 1,288 5 $ 72,688 1,261 6 $ 99,074 449 5 $ 69,746 150 6 $ 85,537 150 7 $ 92,677 150 8 $ 110,041 150 As displayed, 6 sources of salary data were used. Each survey source contained information for numerous job titles. The GPRC business manager read through all of the surveys and selected the job descriptions in each survey that were equivalent to GPRC jobs. The equivalencies were based on the similarity of required knowledge, skills, and abilities. The corresponding pay data for the jobs listed in the surveys were then grouped into equivalent GPRC pay grades to form a survey average annual wage. AL AaBbCcDdEe AaBbCcDdEe Normal No Spacing For example, the State Healthcare Salaries Survey contained salary information for Waitress/Waiter, Housekeeper, Maintenance Worker, Janitor, Driver, Activities Aide, and Security Staff. The business manager assessed all of these jobs as being equivalent to GPRC jobs in pay grade 2 (i.c. Activities Assistant, Housekeeper, Dietary Server, Security). The business manager then used the salary information in the survey to calculate a weighted average annual salary (977,271,480 + 3,420 - $22,594) for the State Healthcare Salary Survey in pay grade 2. The table below summarizes the business manager's calculations: Average Assigned Average Annual GPRC Pay Annual Sample Wages x Grade Wages Size Sample Size $22,820 491 $11,204,591 $ 22,597 467 $10,552,799 2 $ 22,598 419 $ 9,468,562 2 $ 22,371 510 $ 11,409,375 $ 22,592 455 $ 10,279,360 $22,593 565 $12,765,045 2 $ 22,596 513 $11,591,748 State Healthcare Survey Waitress/Waiter Housekeeper Maintenance Worker Janitor Driver Activities Aide Security Staff NN NNN Total 3,420 77,271,480 Survey (Market Wage) $ 22,594.00 -- Number of businesses reporting wage data for this job description. - Weighted Average Annual Salary for jobs in the State Healthcare Salary Survey that are equivalent to jobs in GPRC's pay grade 2. Assignment: Using the GPRC and salary survey data provided above (ie the first two tables, not the third), construct a line chart that displays GPRC's pay structure relative to the external market. In your chart, create two lines for the external market. The first external market line should include survey data from all sources. The second external market line should include data from all sources except the Bureau of Labor and Statistics (BLS). Be sure to use weighted averages to create your market lines. I have included a sample chart below to give you a better understanding of what I want you to produce. The gray, blue, and orange lines on the chart are based on completely different data. However, your X axis should display Pay Grades 1 through 8 and your Y axis should be in dollars. Focus of red and the $100.000 $90.000 Suneyf No Data $30,000 570.000 W550.000 Golden Pond RC SRDCORE 550.000 Survey $20,000 $30.000 $20,000 $10.000 50 1 2. Pay Grade Pa/Grace After you create the chart, answer the following two questions: 1. Based on the chart you created, do you believe that GPRC is executing a lead, match, or lag pay level strategy? Explain your answer. 2. Which market line (with BLS data or without BLS data) do you think most accurately reflects reality? Why? So, your final deliverable should include an Excel chart showing three trend lines and the written answers to two questions. Some things that I will be looking for on the chart are all of the necessary titles, legends, axis labels, and units. There should only be three lines on the chart, as described above
