TASK C (Performance) The "individual bonus" and "back office bonus" paid to sales agents are based on the number of years that the agents have been with Fabrikam Inc. as shown in the array/table AIS:C20. Set the range A4:J12 as a table called "Performance" Assign the name "Commission to the range AIS:C20 Use VLOOKUP to determine each agent's Individual Bonus Rate in cells Es to En Use VLOOKUP to determine each agent's Back Office Bonus Rate in cells FS to FIT . Cells Ell and Fll: Michael Nash's bonuses will indicate N/A because he has not spent up to 1 year. Mask this error message using an IFERROR statement such that any such error will appear as a blank. Goal Achieved (G5 to G11): Use an IF statement to indicate with a YES or NO whether or not an agent achieved their sales goal. Use and If statement to calculate each agent's bonus (in cells H5 to 1112) only those who achieved their goals, Use and If statement to calculate each agent's Back Office Bonus in cells 15 to 111. Note that every agent is paid this bonus regardless of whether or not they achieved their goal as long as they have been with the company for at least one year. Inc In Back Office (in cells J5 to JIT): This is the total of agent bonus and back office bonus Totals, use appropriate formulas to evaluate the totals in cells H12 to J12. AutoSave 04 Mini-project 1 - MS EXCEL Search File Home Insert Page Layout Formulas Data Review View fo A sert Function @ AutoSum Recently Financial Logical Text Date & Lookup & Math More Used- Time Reference Trig Functions Function Library Help PDF Suite 2020 Define Marie Use in Formula Nome Manager Coate fem Selection Defined Names ce Precedents show formules Tisce Dependents A Enter Checking - FRemove Ano Evaluate for Formula Auditing Wato Windo SUM X VLOOKUP 2 3 Standard and Performance Bonus Back office Bonus Incin Back Office 4 Agent s Carey, Richard 6 Ortiz, David 7 Calafato, Ryan Akers, Kim 9 Carson, Nicole 10 Moschell, unda 11 Nash, Michael 12. Totals 13 14 Years with Individual Back Office Fabrikam Sales Goal Actual Sales B_VLOOKUP lookup_value, table_array. colinde num (range Sockup 12 S3,375,000 $3,900,000 VLOOKUPE 12 S3,375,000 $3,500,000 10 $ 2,875,000 $2,700,000 5 S 2,500,000 6 2,000,000 4 S 1,500,000 5 1,224,000 1s 800,000 $ 925,000 0.5 $ 300,000 S 220,000 $ 14,725,000 5 15,069,000 Years with Fabrikam 15 16 17 1 2 5 10 15 individual Bonus 1.00% 1.50% 2.00% 2.5096 3.008 Back Office Bonus 2.00% 1.50% 1.50% 1.00 1.00 19 20 ASFA Grades o fabrikam sales C-Performance D Modifying without E-Madiso O B