Calculate your sales commission with Excel scenarios

Every salesperson knows the importance of boosting company sales to maximize their commission. And in many cases, the commission isn't based simply on total sales — the commission rate is related to the unit mark-up rate. Mark up an item too high though, and you won't be able to close the sale. How do you strike the right balance?

Generate potential scenarios

It's often helpful to develop a number of scenarios, and see which ones yield the best results. But calculating how to get the most value for the customer, your company, and yourself is often a time-consuming process.

Instead of doing the math yourself, why not let Microsoft Office Excel 2003 do it for you? Just enter the values that might change as Excel scenarios, and then view a summary of how your other numbers will change as a result.

The following illustration shows an Excel worksheet summary of a sales proposal for Fabrikam, Inc. The standard mark-up rate is 12% per unit, and at that rate you will receive a 5% commission. The worksheet already contains a formula to calculate the Commission $ based on the Total $ sold and the Commission %.

Sales proposal with standard mark-up and commission

Sales proposal summary


If you can raise the mark-up rate above 12%, you will receive a 6% commission, but it's likely that Fabrikam will purchase fewer units because of the higher price. You can enter each possible mark-up rate, units sold, and associated commission, and then see which one adds up to the best sale.

Create a scenario

  1. In Excel, on the Tools menu, click Scenarios .
  2. In the Scenario Manager dialog box, click Add.
  3. In the Add Scenario dialog box, in the Scenario name text box, type a name for the scenario.
  4. In the Changing cells text box, enter the cells that you want to change, or hold down CTRL and click each cell in the worksheet.

In this example, you want to change cells B3 (units), B5 (mark-up), and B8 (commission rate). You can either click these cells while holding the CTRL key, or enter B3,B5,B8 in the text box.

  1. Click OK.
  2. In the Scenario Values dialog box, type the values you want for the changing cells.

In this example, change the value of $B$3 (units) to 22, the value of $B$5 (mark-up) to .14, and the value of $B$8 (commission rate) to .06.

  1. Click OK to create the scenario.
  2. If you want to add more scenarios, repeat steps 2 through 7. This example includes a scenario for each % Mark-up from 10% to 14%, and then varied the values for Units and Commission % accordingly.

Scenario Manager dialog box

  1. When you are finished adding scenarios, click Close to close the Scenario Manager.

Display a scenario

Now you can look at how changing the numbers affects the bottom line.

  1. On the Tools menu, click Scenarios.
  2. Select the scenario you want to see from the Scenarios box, and then click Show. The values in your worksheet will change automatically.
  3. When you're finished, click Close to close the Scenario Manager.

View the results in the best format for your needs

Now you can view each set of numbers by showing the different scenarios. For an even better perspective, try viewing the results in a scenario summary or a Microsoft PivotTable® report. By using these tools, you can view the results of all your scenarios in a number of ways.

Create a scenario summary or a PivotTable report

  1. On the Tools menu, click Scenarios.
  2. Click Summary
  3. In the Scenario Summary dialog box, click Scenario summary or PivotTable report.
  4. In the Result cells text box, enter the references to the cells for which you want to see new values changed by scenarios.

This example uses B7,B9 to show the changes to the total commission.

  1. Click OK.

Your report appears on a separate tab next to the current worksheet.

Scenario summary with variables for each scenario and the result cells

Scenario summary


The PivotTable report displays the result cells for each scenario. You can use the drop-down arrows to display the results you want to see.

PivotTable report with result cells for each scenario

PivotTable report

Sum it up

It's clear that with a mark-up rate of 10%, your total sales dollars are higher, and your commission — even at the lower rate of 5% — is larger. Now you know how to focus your energy for best results.

It can take hours to work out these figures by hand. Excel scenarios make it easy by doing the calculations for you, and returning the results you're looking for.

 
 
Applies to:
Excel 2003