Some service-focused companies reward their sales members with bonuses for their performance. This article will guide you on how to model 2 types of bonuses in your forecast.

  1. A fixed bonus payment if the sales target is met
  2. A bonus payment as a percentage of the sales exceeded

 

1. A fixed bonus payment for achieved target

In this example we assume the following:

  • Monthly sales target = $10,000
  • A bonus payment of $1,000 in any month the employee exceeds the sales target
  • Forecast for first month = $11,000
  • Forecast for second month = $9,000

To model this, we will set up a Driver as a multiplier to determine if the sales target has been met. The driver calculation is set to multiply by 1 if the target is achieved and multiplied by 0 if it isn’t.

Setup:

  1. Create a Sales element named Sales, a Costs element named Bonus, a Driver element named Bonus Driver and another Driver element named Sales Target ($)
  2. Enter your forecast sales figures within the Sales element
  3. Enter your targeted sales value of $10,000 in Sales Target ($)


Bonus Driver element

  1. Go into the Bonus Driver element in your Chart of Accounts
  2. Set the Driver Method to Add Formula
  3. Click on Edit to begin building the bonus calculator
  4. Create a formula to determine if your sales target has been met by taking Sales less Sales Target ($)
  5. The formula will return values so that any months that have exceeded the target will be positive, and months that fall short will be negative
  6. Set your Driver Constraints to Enter Min & Max
  7. Set your Max = 1 and Min = 0
  8. All positive values will now return the value 1, and the negative values will return 0. We have now created a multiplier to be used in the calculation of the Bonus expense
  9. Click Save & Close to exit the Driver

 

Bonus Cost element

  1. Go into the Bonus expense account in your Chart of Accounts
  2. Change the Expense Method to Driver x Rate
  3. Using the Element Selector, link the calculations to Bonus Driver
  4. Enter the bonus value you’d pay for the sales target being met (e.g. $1,000) in the Rate per Unit line for the periods that you're awarding the bonus
  5. Any months that have exceeded the target will report the bonus value as the formula is set up to calculate the multiplier x bonus amount
  6. Click Save and Close to exit
  7. Review your reports in Analysis

 

2. Bonus payment as a % of exceeded sales

In this example we assume the following:

  • Monthly sales target = $40,000
  • Sales staff get 20% for every dollar that exceeds the sales target
  • Forecast for first month = $55,000
  • Forecast for second month = $39,000

Setup:

  1. Create a Sales element named Sales, a Cost element named Bonus and 2 Drivers called Sales Target and Dollar Sales Exceeded
  2. Enter your forecast sales figures within the Sales element
  3. Enter the sales target of $40,000  in the Sales Target driver

 

Dollar Sales Exceeded driver element

  1. Go into the Dollar Sales Exceeded driver
  2. Set the Driver Method to Add Formula
  3. Click on Edit
  4. Create a formula to calculate the number of dollars the sales staff have exceeded the sales target by taking Total Sales (or relevant Sales line) less the Sales Target driver
  5. The calculation returns the excess value of the target sales so that you can use the Bonus % can be applied against it

 

Bonus Cost element

  1. Go into the Bonus expense element in the Chart of Accounts
  2. Change the Expense Method to Add Formula
  3. Set the Formula to Dollar Sales Exceeded multiplied by the percentage of bonus awarded i.e. 20% or 0.2
  4. Select the Formula View Setting at the bottom right and choose to Include the Result Only When Positive
    formulasetting.gif
  5. The calculation will now return the value that is 20% of the exceeded sales as your Bonus
  6. Click Save and Close to exit
  7. Review your Reports in Analysis

Related Resources