E3 A 125% Format Organize Insert Table Chart Text Shape Media Comment Collaborate Pivot Table View Zoom Add Category Sheet + Lookup Dataset Performance Data Questions Sheet Name Lookup Dataset Workplace Violence & Performance Base Pay Bonus Rate Year-End Bonus Pe Employee ID Last Name First Name Facility Sexual Harassment Rating Pe Training Completed $56,897.00 Kenan San Francisco, CA Ye: Background O EA441 Linkous Miami, FL $65,234.00 EA4412 Suarez Luis No Barcelona, ES Yes $51,236.00 EA4413 Zuckerman Mandie EA4414 Swartout San Francisco, CA No $59,687.00 Shayna $54,852.00 Duplicate Sheet EA4415 Ronaldo Cristiano Miami, FL No EA4416 Yes $45,896.00 Tyler Chun Barcelona, ES Delete Sheet EA4417 Brown Charlie San Francisco, CA Yes $49,875.00 $39,865.00 EA4418 Jensen John Miami, FL No $35,698.00 EA4419 Vasquez Maria Barcelona, ES Yes Horace San Francisco, CA No $42,519.00 EA4420 Mckay $45,523.00 EA4421 Lafountain Floyd Miami, FL No $48,527.00 EA4422 Ward Shauna Barcelona, ES Yes EA4423 Connor Johan San Francisco, CA Yes $51,531.00 Yes $54,535.00 EA4424 Hernandez Wendy Miami, FL Barcelona, ES Yes $57,539.00 EA4425 Bailey Courtney EA4426 San Francisco, CA Yes $60,543.00 Morgan Alex $63,547.00 EA4427 Chastain Brand Miami, FL Yes Yes EA4428 Mess Lione Barcelona, ES $66,551.00 $69,555.00 EA4429 Hamm Mia San Francisco, CA Yes EA4430 Sanchez Pablo Miami, FL Yes $52,369.00 No $49,856.00 EA4431 Solo Hope Barcelona, EA4432 Johnson Ricky San Francisco, CA Yes $65,269.00 Yes $67,542.00 EA4433 Mbappe Kylian Miami, FL Barcelona, ES $67,953.00 EA4434 Williams Serena Yes $61,593.00 EA4435 Tom Yes Brady Miami, FL EA4436 Patrick Barcelona, ES Yes $41,579.00 Mahomes $54,235.00 EA4437 Biles simone Miami, FL Yes EA4438 Salah Mohamed Barcelona, ES Yes $59,685.00 $64,239.00 EA4439 da Silva Marta Miami, FL YesE3 A 125% Format Organize Insert Table Chart Text Shape Media Comment Collaborate Pivot Table View Zoom Add Category Sheet + Lookup Dataset Performance Data Questions Sheet Name Lookup Dataset Workplace Violence & Performance Base Pay Bonus Rate Year-End Bonus Pe Employee ID Last Name First Name Facility Sexual Harassment Rating Pe Training Completed $56,897.00 Kenan San Francisco, CA Ye: Background O EA441 Linkous Miami, FL $65,234.00 EA4412 Suarez Luis No Barcelona, ES Yes $51,236.00 EA4413 Zuckerman Mandie EA4414 Swartout San Francisco, CA No $59,687.00 Shayna $54,852.00 Duplicate Sheet EA4415 Ronaldo Cristiano Miami, FL No EA4416 Yes $45,896.00 Tyler Chun Barcelona, ES Delete Sheet EA4417 Brown Charlie San Francisco, CA Yes $49,875.00 $39,865.00 EA4418 Jensen John Miami, FL No $35,698.00 EA4419 Vasquez Maria Barcelona, ES Yes Horace San Francisco, CA No $42,519.00 EA4420 Mckay $45,523.00 EA4421 Lafountain Floyd Miami, FL No $48,527.00 EA4422 Ward Shauna Barcelona, ES Yes EA4423 Connor Johan San Francisco, CA Yes $51,531.00 Yes $54,535.00 EA4424 Hernandez Wendy Miami, FL Barcelona, ES Yes $57,539.00 EA4425 Bailey Courtney EA4426 San Francisco, CA Yes $60,543.00 Morgan Alex $63,547.00 EA4427 Chastain Brand Miami, FL Yes Yes EA4428 Mess Lione Barcelona, ES $66,551.00 $69,555.00 EA4429 Hamm Mia San Francisco, CA Yes EA4430 Sanchez Pablo Miami, FL Yes $52,369.00 No $49,856.00 EA4431 Solo Hope Barcelona, EA4432 Johnson Ricky San Francisco, CA Yes $65,269.00 Yes $67,542.00 EA4433 Mbappe Kylian Miami, FL Barcelona, ES $67,953.00 EA4434 Williams Serena Yes $61,593.00 EA4435 Tom Yes Brady Miami, FL EA4436 Patrick Barcelona, ES Yes $41,579.00 Mahomes $54,235.00 EA4437 Biles simone Miami, FL Yes EA4438 Salah Mohamed Barcelona, ES Yes $59,685.00 $64,239.00 EA4439 da Silva Marta Miami, FL Yes] 125% v bl :1; Q] W LEJ 69 $ @ View Zoom insert Table Chart Text Shape Media Comment Collaborate Formal Organize + Lookup Dataset Performance Data Sheet Sheet Name MGMT 408: Lookup Functions Activity Scenario: You are working on determining how much each employee should receive as a year-end bonus. You've been given the employee records, their performance scores, and the bonus rates for the year-end bonuses. Complete the following steps to ll in the "Lookup Dataset" sheet and answer the questions below. Questions Background 7 Step 1: The rst step is to get the data from the "Performance Data" sheet into the "Lookup Dataset" sheet. There are several ways we can do this, but to help us practice use the Vlookup or Xlookup formulas. Note, if you are unsure how to do this, refer to the Powerpoint slides for an Excel Formulas. Once you've lled in the entire Performance Rating column in the "Lookup Dataset" sheet, move on to step 2. Duplicate Sheet Step 2: Now that you've inserted the performance data, we need to calculate the average performance rating and standard deviation for thejob Delete Sheet performance ratings. For the average performance score and standard deviation, enter a formula below that will calculate each. To do this, you will need to use the =average and = STDEV.S formulas. Average Performance Score Standard Deviation