Adjust Bonus Formula based on Volume of Sales from Sale1 but triggers when Sale1 and Sale2 hit the trigger.

Abundance

New member
Joined
Feb 24, 2021
Messages
2
I want a formula that would adjust a Bonus to be paid out every period of time (Bonus Period). The volume of the Sales will increase or decrease each Bonus Period and the amount of the Bonus should be adjusted.

Each Bonus Period will be triggered after every 1000 sales.
The Bonus will start at 100 and will be reduced by 50% after 100 Bonuses have been paid out. The Bonus will continue to be reduced by 50% every 100 Bonus Period for 7 times.

100 paid out 100 Bonus Periods 1st 100 Bonus Periods
50 paid out 100 Bonus Periods 2nd 100 Bonus Periods
25 paid out 100 Bonus Periods 3rd 100 Bonus Periods
12.5 paid out 100 Bonus Periods 4th 100 Bonus Periods
6.25 paid out 100 Bonus Periods 5th 100 Bonus Periods
3.13 paid out 100 Bonus Periods 6th 100 Bonus Periods
1.56 paid out 100 Bonus Periods 7th 100 Bonus Periods

Here is where it gets tricky.

The sales are from 2 different sources. Sales1 and Sales2
The Bonus is triggered when 1000 Sales occur.

The Sales Volume is Totaled from only one source "Sales1".

The reason for adjusting the Bonus is because the Sales2 could be used to game the payout by making a lot of tiny sales and very quickly in order to force a Bonus Period to end.

Sales1 can have dramatic growth and double the sales volume between Bonus Periods, or it could have limited to moderate growth as well. The Bonus will be paid out regardless but we want to adjust the amount between the Sales Team and a Charity. Sales Team will get 100% of the Bonus if the growth rate is within 5% of the Total Sale Volume from the last 2 Bonus Periods compared to the curent 2 Bonus Periods.

Period 1 = 50,000
Period 2 = 52,000
Period 3 = 51,500
Period 4 = 53,000
Period 5 = 10,000

Durring Period 5 Someone Gamed the system. They forced the end of a Bonus Period by creating many sales from Sales2 and limited the time for transactions in Sales1 to occur.

The idea is to reduce the Bonus paid out if this occurs by send a larger amount of the Bonus to a charity.
 

Abundance

New member
Joined
Feb 24, 2021
Messages
2
Looks like I stumped you all. My solution was to use the Percent Different from both Sales1 from the last period to the current,
then do the same for Sales2, and solve for the Percent Change between the Percent Different of Sales1 and Sales2. Use this value (between 0 and 2) as a control to change the increase or decrease of the Bonus.
 

Subhotosh Khan

Super Moderator
Staff member
Joined
Jun 18, 2007
Messages
24,691
Looks like I stumped you all. My solution was to use the Percent Different from both Sales1 from the last period to the current,
then do the same for Sales2, and solve for the Percent Change between the Percent Different of Sales1 and Sales2. Use this value (between 0 and 2) as a control to change the increase or decrease of the Bonus.
It is excellent that you solved your problem.

In this forum, at least I do not answer "commercial" questions as long as a "student" has some legitimate unanswered question.
 
Top