Log In
Register
Home
New Member
October 27, 2020
Offline
Hi,
I am a VBA beginner, trying to build a solution to automate calculation based on dynamic variables.
Attached an example (done manually) for reference.
Greatly appreciated if anyone can help.
Thank you.
Trusted Members
December 20, 2019
Offline
Can you please describe what you are trying to achieve?
Is column C actual sales? and row 2 the target?
do you want the percentages to work out on only the part between the target? for example 1% of the amount between 60k & 70k and then 5% of the amount between 70k and 80k?
what is the 10x10 grid for at the bottom of the page?
New Member
October 27, 2020
Offline
What i am trying to do is to automate the allocation of the commission based on Sales (C3:C17).
Column C is the actual sales and row 2 is the target
The commission to be awarded only the actual sales is between the target - for example, 1% of the amount between 60k & 70k and then 5% of the amount between 70k and 80k
Example Business Case Scenario
a) 1% Commission for achieving the 60K target (60K, 120K, 180K, ...)
b) 5% Commission for achieving the 70K target (70K, 140K, 210K, ...)
c) 9% Commission for achieving the 120K target (120K, 240K, 360K, ...)
If a sales order is 66.8K, the dealer gets a commission of 1%
If a sales order is 133.6K, the dealer gets a commission of 26% (where 1% from achieving the 60K Target and 25% for achieving the 120K target)
In the example file attached, I manually allocated the commission (using the 10x10 grid) based on the actual sales per order and a simple formula to calculate the total commission to be payout.
The manual effort is time-consuming and not error-proof.
1 Guest(s)
