- If then formula excel on current sheet how to#
- If then formula excel on current sheet full#
- If then formula excel on current sheet download#
This is the major benefit of this formula. See the Total Payout % column on Sales Table tab of the example file. Now that we are able to calculate the payout in one cell using one SUMPRODUCT formula, we can apply the formula to a whole list of employees in a table. This design would give the rep more motivation (higher payout rate) as he/she gets closer to achieving their goal of 100% attainment. If the goal is to achieve 100% attainment of quota, then it is probably best to increase the payout rate (rate curve slope) in each tier up to 100%. Negative differential can also mean that the rate curve is poorly designed. And the monetary motivation is not as great for attainment in tier 4. This might mean that there is more emphasis for the rep to attain sales on their quota in tier 3. For each additional percentage point of attainment, the sales rep is compensated at a lower rate than tier 3. Not only does it make for a confusing calculation, it also tells you that the rate of compensation is not as great in tier 4. This negative differential rate in tier 4 is important to note. That brings the total payout back down to 87.5%. Or, the slope of the rate curve is flatter in tier 4 than tier 3. The negative product is a result of the payout rate being less in tier 4 than tier 3. The sum of the first three tiers is 95% (45%+12.5%+37.5%), which is higher than the actual payout of 87.5%. The 90%-40% is the remaining balance of attainment for tier 2, which is 50%.
50% is the payout rate for tier 1, and 90% is the total attainment. The rate curve is displayed below, and you can think of the payout rate as the slope of the curve at each tier. The payout rate is also known as the rate curve.
This means that for every 1% attained, the payout will be 0.50 of the 20% total payout. In the image below the Payout Rate for the 0%-40% range is 0.50. This is basically the amount paid for each percentage increase in attainment in each tier. The payout rate at each tier is the total percentage of payout in the tier, divided by the total percent of attainment possible in the tier. The differential rate is the difference between the payout rates at each tier.
First we have to calculate the differential payout rate for each tier. The SUMPRODUCT function can be used to calculate the entire payout.
If then formula excel on current sheet download#
You can download the sample workbook below to follow along. Or we could use some complicated IF statement to determine the payout all in one formula. Typically we would have to calculate the payout at every tier and then sum the payout amounts to get the total amount. So the total payout on 50% of quota would be 27.5%. The last 10% of quota attainment is calculated by finding the payout rate at each tier. What if the rep sells 50% of his/her quota? The rep would receive 20% payout for the first 40% of quota, and an additional 7.5% payout for the last 10% of quota. The difficult part is when the attainment amount falls in between the ranges. If they sell 60% of quota, they will receive 35%, and so on down the table. For example, if the rep sells 40% of their quota then they will receive 20% of their commission.
If then formula excel on current sheet full#
If the sales person (rep) achieves sales that are at the top end of each tier, then they will receive the full payout amount in the Total Payout column. The first column contains the tiered ranges of Quota Attainment and the second column contains the Payout % for each tier. The following is an example of a tiered rate table for sales commissions. This is a simpler calculation then the one presented below.
If then formula excel on current sheet how to#
If your commission plan tiers are not cumulative, then you might want to checkout my article on how to calculate commissions with VLOOKUP. Calculating commissions on a tiered rate structure can be difficult because you are trying to determine the cumulative payout based on different rates at each tier, and the achievement amount might fall in between one of the tier ranges. In this post I will explain how to calculate a dollar or percentage commission payout in one cell using the SUMPRODUCT function.
0 kommentar(er)
