Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Dear Experts,
I have loaded data as per below which carries data for Motor & NON MOTOR classes. Further to explain, the data contains CY-Actual , LY-Actual & the related budget etc. Now I need to pay Target incentives for RA, based on the figures given. In the process, following criterians to be satisfied.
1: Achievement to be maintained 75% or above
2: For 75% achievement , Rs 2000, is paid, if the achievement is >75% but less than 100%, we my pay Rs 150/= for each points (Eg, if the achievement is 95%, payment should be (20*150), if the achi is 100%, payment should be (25*150) etc.
3:If the achievement is >100%, will be paid 225/= for each points (Eg, If the achi is 110%, it will be (10*225) etc.
Above criterias are for MOTOR class. If you suggest me 4 expressions namely 1 for 75% achievement, 2nd for 75%-100%, & 3rd for to above 100% in three different columns & the last columns in incorporating all three conditions together, (1st+2nd+3rd).that will be highly appreciated.
When it is for NON MOTOR class the figures are as follows:
1: 75% achievement Rs 1500
2: From 75% to 100%, 100/= per point, & above 100%, each points will carry 125/=
If I am provided with the requested solutions for Motor, I will be able to prepare it for NON MOTOR.
NOTE:
Once the calculations are done as above, following condition to be satisfied in order to release the calculated incentives which is as follows
A:To release the Motor incentives, it should be having a positive growth.
B: To release the NON MOTOR incentives it should be again having a positive growth.
c: If the negative growth of one class is appeared as positive once both classes growth are considered, then incentives of negative class too can be released. (EG, in the month of February NON MOTOR takes 14% negative growth, but in total products (MOTOR + NON MOTOR) it shows a positive growth rate which is 13%.
Running total growth was considered to satisfy the last condition of which expression given as below:
Formula for running Growth : (if(Dimensionality()=3, RangeSum(above(TOTAL RangeSum([ACT-CY]),0,RowNo(TOTAL))),Sum ([ACT-CY]))-if(Dimensionality()=3, RangeSum(Above(TOTAL RangeSum([ACT-LY]),0,RowNo(TOTAL))),Sum([ACT-LY])))/if(Dimensionality()=3, RangeSum(Above(TOTAL RangeSum([ACT-LY]),0,RowNo(TOTAL))),Sum([ACT-LY]))
Formula for Achievement: Sum ([ACT-CY])/Sum (BUD)
Formula for Growth: (Sum ([ACT-CY])-Sum ([ACT-LY]))/Sum ([ACT-LY])
Thanks in Advance
Neville
