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.
- A fixed bonus payment if the sales target is met
- 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:
- Create a Sales element named Sales, a Costs element named Bonus, a Driver element named Bonus Driver and another Driver element named Sales Target ($)
- Enter your forecast sales figures within the Sales element
- Enter your targeted sales value of $10,000 in Sales Target ($)
Bonus Driver element
- Go into the Bonus Driver element in your Chart of Accounts
- Set the Driver Method to Add Formula
- Click on Edit to begin building the bonus calculator
- Create a formula to determine if your sales target has been met by taking Sales less Sales Target ($)
- 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
- Set your Driver Constraints to Enter Min & Max
- Set your Max = 1 and Min = 0
- 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
- Click Save & Close to exit the Driver
Bonus Cost element
- Go into the Bonus expense account in your Chart of Accounts
- Change the Expense Method to Driver x Rate
- Using the Element Selector, link the calculations to Bonus Driver
- 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
- 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
- Click Save and Close to exit
- 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:
- Create a Sales element named Sales, a Cost element named Bonus and 2 Drivers called Sales Target and Dollar Sales Exceeded
- Enter your forecast sales figures within the Sales element
- Enter the sales target of $40,000 in the Sales Target driver
Dollar Sales Exceeded driver element
- Go into the Dollar Sales Exceeded driver
- Set the Driver Method to Add Formula
- Click on Edit
- 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
- 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
- Go into the Bonus expense element in the Chart of Accounts
- Change the Expense Method to Add Formula
- Set the Formula to Dollar Sales Exceeded multiplied by the percentage of bonus awarded i.e. 20% or 0.2
- Select the Formula View Setting at the bottom right and choose to Include the Result Only When Positive
- The calculation will now return the value that is 20% of the exceeded sales as your Bonus
- Click Save and Close to exit
- Review your Reports in Analysis
Related Resources