Excel Challenge 36: Calculate and Maximize Commission With What-If Analysis

Ready to test your problem-solving skills with Excel challenge #36?

Everything you need to participate in the challenge can be found on this page. To take part:

1. First, watch the challenge video and read the instructions below the video.
2. Review the previously published video(s) and article(s) on which the challenge is based.
3. Download the Excel worksheet you will use to complete the challenge tasks.
4. Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge 🧠

Here’s the scenario to be solved from the download file:

It’s a very exciting time of year for our organization’s sales teams! It’s when each team member finds out how much commission they have earned based on the sales revenue they have generated throughout the year. 

This challenge is split into several exercises:

👉Exercise 1

First, we need to calculate the commission for each employee based on the information in the commission table. 

• The commission is displayed as decimals in the commission table. 
• Use the ‘Sales’ column to calculate the commission. 
• Give the commission table a meaningful name to make the formula easier to construct (optional). 

Once you have the commission rates listed in column F, modify the formula so column F shows the monetary value instead of the commission amount e.g. 9,000 instead of 0.15.  

• Each employee’s commission is a percentage of their sales. 

• Edit the formula to add error handling. 
• Replace any cells that show an #N/A error with a blank. 

👉Exercise 2

• Use a formula to calculate the total commission for each sales team. 

• In cell I17, calculate the total commission for all sales teams. 

👉Exercise 3

Our manager has a budget of 700,000 for all sales team commissions. If the calculation in cell I17 is less than 700,000, use What-If Analysis tools to change the total commission to 700,000 by changing the commission percentage in cell J8. 

If the calculation has been completed correctly, the top bracket in the commission table should be 32%. 

How quickly can you complete this challenge? Fifteen minutes? Ten? Less?

If you’re just getting started with Excel, all the knowledge you need to solve this challenge has been shown in the following resources:

If you’re already proficient with Excel, this should be quite easy. Maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!

The solution🪄

We hope you enjoyed taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical real-world Excel skills.