P&L Compensation Report - Exercise Using a selection criteria, work out of the employees should be promoted and what their bonus will be. Follow the instructions below in sequence and only provide answers in the yellow cells. Step Instructions 1 In cells H21:H39, use the IF statement to calculate the GBP P&L figure for each employee based on the "FX Table" data 2 In cells 121:129, use VLOOKUP to calculate the bonus amount based on the GBP P&L and the "Bonus Rates" data 3 In cell 130, use the SUM function to compute the total employee bonuses 4 In cells J21:J29, calculate whether they will be promoted using the AND function combined with the IF statement Note: employees will be promoted if their GBP P&L is more than 500,000 and they have scored a rating of 3 or higher Note: the output in these cells should be either 0 or 1 (1 if promoted) 5 In cell J30, use SUM to count the number of employees who will be promoted 6 In cells M21:M23, use the SUMIF function to sum the total GBP bonus payments for each desk 7 In cells, M27:M29, use the INDEX and MATCH functions to look up the GBP bonus for each EQT desk employee ON GBP P&L GBP Bonus Promotion Bonus Summary Desk GBP Bonus INF FID EQT Employee ID 11099 11100 11101 11102 11103 11104 11105 11106 11198 Desk FID EQT FID INF EQT FID INF EQT FID Currency EUR USD EUR GBP EUR EUR GBP GBP USD Transaction Report Rating P&L 2 453,222 4 1,725,661 2 752,997 3 661,000 3 962,521 3 952,316 4 1,256,461 2 300,018 2 650,001 EQT Employee Bonus Employee IDGBP Bonus 11100 11103 11106 NN Total per GBP FX Table Currency EUR 1.1578 USD 1.5840 GBP 1.0000 Bonus Rates Desk Rate INF 10.0% FID 9.5% EQT 9.0% Promotion hurdles Criteria Hurdle GBP P&L 500,000 Rating 3 P&L Compensation Report - Exercise Using a selection criteria, work out of the employees should be promoted and what their bonus will be. Follow the instructions below in sequence and only provide answers in the yellow cells. Step Instructions 1 In cells H21:H39, use the IF statement to calculate the GBP P&L figure for each employee based on the "FX Table" data 2 In cells 121:129, use VLOOKUP to calculate the bonus amount based on the GBP P&L and the "Bonus Rates" data 3 In cell 130, use the SUM function to compute the total employee bonuses 4 In cells J21:J29, calculate whether they will be promoted using the AND function combined with the IF statement Note: employees will be promoted if their GBP P&L is more than 500,000 and they have scored a rating of 3 or higher Note: the output in these cells should be either 0 or 1 (1 if promoted) 5 In cell J30, use SUM to count the number of employees who will be promoted 6 In cells M21:M23, use the SUMIF function to sum the total GBP bonus payments for each desk 7 In cells, M27:M29, use the INDEX and MATCH functions to look up the GBP bonus for each EQT desk employee ON GBP P&L GBP Bonus Promotion Bonus Summary Desk GBP Bonus INF FID EQT Employee ID 11099 11100 11101 11102 11103 11104 11105 11106 11198 Desk FID EQT FID INF EQT FID INF EQT FID Currency EUR USD EUR GBP EUR EUR GBP GBP USD Transaction Report Rating P&L 2 453,222 4 1,725,661 2 752,997 3 661,000 3 962,521 3 952,316 4 1,256,461 2 300,018 2 650,001 EQT Employee Bonus Employee IDGBP Bonus 11100 11103 11106 NN Total per GBP FX Table Currency EUR 1.1578 USD 1.5840 GBP 1.0000 Bonus Rates Desk Rate INF 10.0% FID 9.5% EQT 9.0% Promotion hurdles Criteria Hurdle GBP P&L 500,000 Rating 3
