Microsoft Excel - Calculate monthly incentive with the help of macro

Asked By John on 20-Jan-13 10:11 AM

would like to calculate month’s incentive to the sales rep. and Supervisor with the help of macro. Result need to come in sheet2 (Amount obtained for each Representatives and Supervisor). Data contains in sheet one. Can somebody give a solution?

Calculation as given below

5 representatives and 1 supervisor are here

If Sales amount =>5000 then Scheme Amount = representatives X 100 (5*100 =500)

In Second sheet result should come as follows

If all representatives are present then in sheet2 representative Column Scheme Amount/ representatives presented. (500/5=100) 100 each for (all) representatives

If any representative or representatives which are absent then in sheet2 representative Column scheme amount/ representatives present.

If any representative takes Half Day then he will get half of Scheme Amount

If Supervisor is present he should get alone amount of 100 as scheme amount.

If Supervisor is half day then he will get half of 100 scheme amount

If he is absent then he do not get scheme amount.

Legend

P – Present

H – half Day

A – Half Day

Sheet1

Date Sale
Amount
Scheme
Amount
Rep1 Rep2 Rep3 Rep4 Rep5 Supervisor
01/01/2012 7600   A P P H P P Legend
02/01/2012 6500   P P P P P P P   = Present
03/01/2012 4000   P P P P H H H  = Half Day
04/01/2012 3500   P P P P P P A   = Absent
05/01/2012 8000   P A A P P P
06/01/2012 3500   P P P H A P
07/01/2012 9000   A H P P P P
08/01/2012 12000   P P P P P P
09/01/2012 6500   P P P P P H
10/01/2012 7500   P P P P P p
11/01/2012 8000   P P P P P p
12/01/2012 9000   P H P A P p
13/01/2012 5500   P P P P P H
14/01/2012 6500   P P H P P h
15/01/2012 8500   P P P P P p
Sheet2
Date Sale
Amount
Scheme
Amount
Rep1 Rep2 Rep3 Rep4 Rep5 Supervisor
01/01/2012 7600              
02/01/2012 6500              
03/01/2012 4000              
04/01/2012 3500              
05/01/2012 8000              
06/01/2012 3500              
07/01/2012 9000              
08/01/2012 12000              
09/01/2012 6500              
10/01/2012 7500              
11/01/2012 8000              
12/01/2012 9000              
13/01/2012 5500              
14/01/2012 6500              
15/01/2012 8500              

Donald Ross replied to John on 20-Jan-13 10:40 AM
John,

are you working with a set scheme amount of 500 or a set fee of 100 per rep for a sale?  as I read you post carefully it seems like you are taking the number of reps hired x 100 to = 500 then looking at how many are actually there on the day of the sale for example only 4 are present on the sale day and one supervisor. 
if you are working with 500 dollars each rep will get 125 500/4=125 and the supervisor will get 500
or
if you are working with 100 per rep avail the 4 x 100 = 400 and so each rep will get 100 and the supervisor will get 400
this is an easy fix we just need to understand your scheme a little better.  (or at least I need to understand a little better)

Thanks Don

John replied to Donald Ross on 20-Jan-13 11:22 AM
Sir, if there is sale 5000 or more then 100 for each rep. here 5 reps employed. so it will be 500. If any of the rep take half day then he will get half of hundred and his half divided to other four person. If one rep is absent his amount divided for other 4 rep.
If supervisor is present then he will get alone 100 only. If he take half then he will get half of 100. If he take leave then he did not get anything.
 
Harry Boughen replied to John on 20-Jan-13 03:41 PM
Hello John
Perhaps you could have given details of the answers that you want in the tables.
Do these answers look correct?
Scheme    Rep1    Rep2    Rep3    Rep4    Rep5    Supervisor
Amount                        
516.67    0.00    0.00    166.67    166.67    83.33    100
600.00    100.00    100.00    100.00    100.00    100.00    100
0.00    0.00    0.00    0.00    0.00    0.00    0
0.00    0.00    0.00    0.00    0.00    0.00    0
600.00    166.67    0.00    0.00    166.67    166.67    100
0.00    0.00    0.00    0.00    0.00    0.00    0
537.50    0.00    62.50    125.00    125.00    125.00    100
600.00    100.00    100.00    100.00    100.00    100.00    100
550.00    100.00    100.00    100.00    100.00    100.00    50
600.00    100.00    100.00    100.00    100.00    100.00    100
600.00    100.00    100.00    100.00    100.00    100.00    100
537.50    125.00    62.50    125.00    0.00    125.00    100
550.00    100.00    100.00    100.00    100.00    100.00    50
500.00    100.00    100.00    50.00    100.00    100.00    50
600.00    100.00    100.00    100.00    100.00    100.00    100

Harry
John replied to Harry Boughen on 20-Jan-13 09:26 PM
Correct sir, Thank you very much
Harry Boughen replied to John on 20-Jan-13 10:05 PM
Hi John,
It didn't need a macro.  See the attached file.
john1.zip
Regards
Harry
John replied to Harry Boughen on 20-Jan-13 10:14 PM
THANK YOU SIR
CAN WE DO THIS WITH MACRO
Harry Boughen replied to John on 20-Jan-13 11:12 PM
Hi John,
Yes it can but is there a particular reason why you wish to use a macro?
And which particular parts should be macro and which by formula?
Harry
Donald Ross replied to John on 20-Jan-13 11:16 PM
John sorry I did not see your answer until harry got you fixed up.

However I do have one thing for you looking at what harry did you need to make one small change.
on your first sheet in colum C change your formula from =IF(B3>5000,500,0)
 to =IF(B3>=5000,500,0) to get credit for a sale that is 5000.
and drag it down.

And as for needing a macro why do you need a macro? 

Don
Harry Boughen replied to Donald Ross on 21-Jan-13 03:24 AM
Good catch Donald.  I seem to have missed seeing the = sign in the original post.
Regards
Harry
Donald Ross replied to Harry Boughen on 21-Jan-13 08:37 AM
Lucky catch Harry, lucky.. 

:) thanks
John replied to Harry Boughen on 21-Jan-13 11:07 AM
Sir one more help I need in this matter. If sales more than =6000 then incentive need to make 600. 
If the calculation make by macro then I can modify it.

Thanks and regards
Harry Boughen replied to John on 21-Jan-13 02:35 PM
Hi John,
You can still do that by formula.
=IF(B3>=6000,600,IF(B3>=5000,500,0))
Enter in C3 and copy down.  The rest remains the same.
Regards
Harry