QUESTION: PLEASE COMPLETE THE EXCEL WORKSHEET WITH FORMULAS PROVIDED.

In column P, Ken wants to see an overall effectiveness rating of all of their dealers. All of the dealers should be classified as either Excellent, Good or Poor based upon the following criteria: Excellent: Received a Quarterly Shipping Rebate (completed in step 4) in 3 or more of the 4 quarters AND also reached their Overall Sales Volume Bonus. Good: Received a Quarterly Shipping Rebate (completed in step 4) in at least 1 of the 4 quarters OR also reached their Overall Sales Volume Bonus. Poor: They were neither Excellent nor Good Apply conditional formatting to change the formatting on cells displaying "Excellent" to be Green Fill with Dark Green Text, "Good" to display Yellow Fill with Dark Yellow Text and "Poor" to display "Light Red Fill with Dark Red Text". Hint: These 3 different formats are 3 the top 3 choices of conditional formatting so use the values from this dropdown as opposed to making up your own coloring scheme. It should look something like the accompanying image when complete (but with different values in the different cells).

Part 2

In rows 30 & 31 for each column that contains a rebate/bonus (G through M), create formulas that a) determines the number (count) of dealerships receiving this rebate/bonus and b) the average value of the bonus (include dealerships that did not earn a bonus in the average calculation). Make sure that your formatting conforms to the best practices that we learned back in Module 1!

617 G H I J K L M N O P Q R SL C D E Manufacturer Rebates to Dealers 10 20 30 Shipping Rebate: $84 $97 $88 Expected volume: 369 464 477 40 Annual $141 n/a 385 7.40% 8,450 2.50% 6 Additional Inputs: Overall Sales volume bonus 7 equal to or over 7.4% Overall Sales volume bonus if 8 >=2.5 and =2.5 and