Microsoft Excel

4 minute read

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

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

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

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!

Take the challenge!

Download the file to get started.

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

A screenshot of a computer

Description automatically generated

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. 

A screenshot of a spreadsheet

Description automatically generated

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

A table with numbers and text

Description automatically generated

  • 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

A screenshot of a data

Description automatically generated

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

A table with numbers and a purple box

Description automatically generated

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:


  Read:   

  XLOOKUP and VLOOKUP in Excel  

    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!

Download the file to get started.


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.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial

Join the Excel conversation on Slack

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

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.