Problem 5.2 Conditional Formatting

Step 1: In worksheet "Part2" there is a list of salespersons and their quarterly output. It is your job to assess the data for anyone who receives a bonus. Employees get a 10% bonus of their total sales if they have total annual sales above $10,000. Employees also get a quarterly bonus of 3% if a single quarters sale is above $3,000. You are to use conditional formatting to:

i) If an employee gets a quarterly bonus (single quarter above $3,000), the cell in the quarter in which they received the bonus, should be highlighted light blue with dark yellow text.

ii) If an employee gets an annual bonus (year total greater than $10,000), the Total column for that employee should be highlighted light yellow with dark green text.

iii) The top 15% of total earning employees should be marked with a green flag icon in their total column.

You must use conditional formatting to complete these tasks.

Step 2: There is also a column for date each person turned in their sales report in December. You are to use conditional formatting such that if the user turned in their report after 12/20/2021 a red icon with an X is placed in the cell, and if they turned it in on or before 12/20/2020 a green icon with a check is placed in that cell.

B D E F G H 1 Name 2 Longenecker, Sean 3 Desai, Christopher 4 Matthews, Rachel 5 Dodds, Westley 6 Wadsworth, Lydia 7 Rosene, Jacob 9 Wadsworth, Jeffrey 8 Walton, Erin 10 Matthews, Aaron 11 Barmer, Qian 12 Dodds, Lindsey 13 Goldberg, William 14 Yang, Kalene 15 Blum, Elizabeth 16 Kelly, Kali 17 Goldfarb, Benjamin 18 Knizner, Michael 19 Walton, Jazmine 20 Weddington, Eric 21 Badgett, Joseph 22 Barksdale, Brian 23 Falsy, Rigsbee 24 Goldberg, Benjamin 25 Folk, Christopher 26 Dodds, Ashley 27 Zhao, Zachary Q1 Q2 Q3 Q4 Total Date Report Quarter Annual Turned In Bonus Bonus Due Date 12/20/2021 $3,000.00 $10,000.00 12/20/2021 12/29/2021 12/4/2021 12/1/2021 $1,789.00 $ 7,462.00 12/3/2021 $3,614.00 $ 7,064.00 12/6/2021 12/8/2021 12/9/2021 $2,357.00 $ 387.00 $2,734.00 $ 732.00 $ 6,210.00 $2,807.00 $3,978.00 $ 255.00 $2,335.00 $ 9,375.00 $2,551.00 $1,144.00 $1,802.00 $1,634.00 $ 7,131.00 $ 153.00 $2,897.00 $1,750.00 $1,129.00 $ 5,929.00 $1,211.00 $ 678.00 $3,784.00 $ 109.00 $3,174.00 $ 167.00 $ 283.00 $ 953.00 $3,937.00 $1,018.00 $ 6,191.00 $1,344.00 $ 440.00 $3,168.00 $2,679.00 $ 7,631.00 $2,757.00 $ 987.00 $ 395.00 $3,638.00 $ 7,777.00 $2,221.00 $2,258.00 $1,186.00 $ 956.00 $ 6,621.00 $2,092.00 $1,649.00 $3,551.00 $ 845.00 $ 8,137.00 $ 672.00 $465.00 $2,084.00 $1,916.00 $ 5,137.00 $3,737.00 $ 395.00 $2,314.00 $2,475.00 $ 8,921.00 $ 958.00 $ 128.00 $3,112.00 $3,314.00 $ 7,512.00 $1,669.00 $ 839.00 $3,480.00 $ 441.00 $ 6,429.00 $ 594.00 $3,226.00 $1,470.00 $ 54.00 $ 5,344.00 $ 833.00 $3,206.00 $3,047.00 $3,082.00 $ 10,168.00 $3,062.00 $1,611.00 $ 643.00 $2,929.00 $ 8,245.00 $ 190.00 $1,453.00 $ 379.00 $ 984.00 $ 3,006.00 $2,034.00 $2,672.00 $ 645.00 $3,612.00 $ 8,963.00 $ 1,201.00 $1,426.00 $1,909.00 $ 291.00 $1,523.00 $ 543.00 12/30/2021 12/5/2021 12/5/2021 12/6/2021 12/12/2021 12/16/2021 12/12/2021 12/9/2021 12/15/2021 12/24/2021 12/21/2021 12/28/2021 $3,431.00 $ 7,967.00 $3,170.00 $ 5,527.00 12/23/2021 12/23/2021 $ 756.00 $1,929.00 $2,817.00 $1,447.00 $ 6,949.00 $3,278.00 $ 237.00 $3,153.00 $1,643.00 $ 8,311.00 $2,008.00 $ 654.00 $1,767.00 $2,011.00 $ 6,440.00 12/20/2021 12/18/2021 12/29/2021 $ 173.00 $3,924.00 $3,152.00 $2,571.00 $ $3,345.00 9,820.00 12/12/2021 $3,112.00 $2,875.00 $ 351.00 $ 9,683.00 12/15/2021 12/19/2021 12/15/2021 28 Williams, Jace 29 Zhao, Shane 30 Barclay, Alicia 31 Lloyd, Christopher 32 Davis, Chad 33 Folk, Paul 34 Badgett, Max 35 Khader, Maile $1,435.00 $3,861.00 $3,790.00 $1,081.00 $ 10,167.00 $1,803.00 $1,302.00 $1,420.00 $2,531.00 $ 7,056.00 $1,093.00 $ 967.00 $2,577.00 $2,557.00 $ 7,194.00 $2,896.00 $3,447.00 $2,524.00 $ 992.00 $ 9,859.00 $ 959.00 $1,486.00 $2,189.00 $3,666.00 $ 8,300.00 $3,059.00 $3,761.00 $2,358.00 $2,146.00 $ 11,324.00 $3,558.00 $1,279.00 $3,503.00 $3,409.00 $ 11,749.00 12/3/2021 12/31/2021 12/28/2021 12/20/2021 12/12/2021 36 K