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

Deb Ashby

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

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.
4. Put yourself to the test!

Take the challenge!

## The challenge đź§

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:

#### What is Goal Seek in Excel?

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!

Take the challenge!

## 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.