You will create an Excel worksheet for calculating bonuses for auto dealerships based on the quantity of vehicles sold. If a dealer sells above the expected volume, they receive a bonus for each vehicle sold.
- Open Boston Dealerships and save it as Boston Dealerships (with your name on the end).
- Review and understand the data before you begin.
- 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.
- 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.
- In cell L8, enter a formula to total the bonuses for each dealer.
- Dealers that received 4 bonuses already will not get a year-end bonus. Change the entry in G2 to 3.
- Change the entry in H2 to 250.
- Apply comma format with no decimal places to F8:L21
- Add data bars to column L.