Each quarter the dealer will receive a bonus for each vehicle sold if sales are greater than the expected volume in row 4. The bonus is per vehicle. For example, since Dealer #1 sold more than 400 vehicles in the first quarter Dealer #1 will receive a bonus of $32,475. In cell F8, use a function to calculate the amount of the bonus for Dealer #1. Use all cell addresses in the formula. Use absolute references as needed so that you can autofill this formula down and across.

Autofill the formula in F8 down to F17 and across to I17. Check your results. Make sure there are bonuses for all dealers that have exceeded the expected volume in each quarter.

In cell J8, use a function to count the number of bonuses for all four quarters for dealer#1. Autofill down.

Some dealers will receive an additional year-end bonus, determined by the entries in G2 and H2. In cell K8, use a function to enter the bonus amount in H2 if the dealer has received the number of bonuses in G2. Use absolute references as needed so that you can autofill this formula down. Autofill down.

Bonuses to Dealers Year End Bonus 1Q 2Q 3Q 4Q 500 75 80 85 80 400 410 415 39 Bonus per vehicle sold Expected volume Number of Vehicles Sold Quarterly Bonuses Total Total Bonus Year End Number of Bonus Amount for Dealer# 1Q 2Q 3Q 4Q BonusesA mount the Year 1 433 521 460 477 2 315 414 398 243 3 284119 272 205 4501 429 395 459 5 321 339 294157 6 536 249 234 211 7 311 496 484 504 8 383210102365 9 210 406 183 409 10 185 355 495 510