annual increment

[bibra]

while creating a payroll there are annual increments which are given on the 1st of july every year
is there any video or thread which gives the details of how to build the increment into the payroll

conditions

--percentage increment could vary from year to year
--also the date date of announcement of the increment also varies--so for this year the increment is announced in october so i would have to calculate the supplementary claim from july to september also

 

[bibra]

Wow! That does seem quite complex although taken on the basis of one row at a time, from table to table, that may not be so.

For the benefit of others who may view, the explanations mentioned are annotations within the document that bibra has mentioned via his link.

So, where/how is the best place to install an increment percentage with least disruption to the overall layout of the sheet?

looking for elegance
am a fan of automation
nice to see that excel has absorbed almost everything from access
disruption is not a problem
can start from scratch since its a sample of the population
always see videos from mr excel to keep updated

 

[BrianJN1]

I am far from being a bookkeeper.

I'm not understanding the 2.57 multiplying factor.
If that is the result of prior increments over time, AND the Base matrix and Grade Pay hasn't changed in that time, can you not adjust the 2.57 to be in line with the new increment? Will that not then reflect upon your employee debit/credits?

Sorry, I am lost but do admire what you have constructed.

 

[bibra]

I am far from being a bookkeeper.

I'm not understanding the 2.57 multiplying factor.
If that is the result of prior increments over time, AND the Base matrix and Grade Pay hasn't changed in that time, can you not adjust the 2.57 to be in line with the new increment? Will that not then reflect upon your employee debit/credits?

Sorry, I am lost but do admire what you have constructed.

sure
will love to explain
2.57 is the pay fixation done once in ten years
multiplication factor is decided once in ten years

next pay fixation will be in 2026

whereas annual increment is done july every year
the employee is moved to the next level in the pay matrix

the third is a six monthly review done based on inflation known as DA Dearness Allowance
announced as a percentage by the Govt
once the pay is refixed after ten years DA percentage goes back to zero
and then from 1 jan 2016 every six months DA is announced based on inflation
say in jun 2016 for example 4% DA
and then in dec 2016 2% DA


since announcement of DA is variable and can be sometimes announced late also by two months so one has to cater to arrears also
since the salary would have 4% DA instead of Say 6% DA

need to build in this part of DA into the worksheet
every six months and the catering to arrears on account of any delays on notifications

 

[BrianJN1]

Suppose that you put "Annual Increment" in S1 and your annual increment in S2, eg 0.5%.
Then in S5 use your mouse to construct this formula =R5 +R5*S$2
Actually that formula will come out as: =[@[01-Jul-2016]]+[@[01-Jul-2016]]*S$2
In the copy of your original workbook that I still have but will soon delete (link that you voided by removing the file) that formula autocopied down Col S as well as creating a new DropDown menu.
Now, if you copy the contents of the drop-table of Col S and Paste Values into Col R, will that do as you require?
Maybe that is a starting point to address some of those statements that you have just included if that thought does not address them.

 

[bibra]

Suppose that you put "Annual Increment" in S1 and your annual increment in S2, eg 0.5%.
Then in S5 use your mouse to construct this formula =R5 +R5*S$2
Actually that formula will come out as: =[@[01-Jul-2016]]+[@[01-Jul-2016]]*S$2
In the copy of your original workbook that I still have but will soon delete (link that you voided by removing the file) that formula autocopied down Col S as well as creating a new DropDown menu.
Now, if you copy the contents of the drop-table of Col S and Paste Values into Col R, will that do as you require?
Maybe that is a starting point to address some of those statements that you have just included if that thought does not address them.

ok
now i have a reference date say today's date--28 sep 2017
and i have the DA rates which change every 6 months
say1 jan 2016 --0%
1 jul 2016--2%
1 jan 2017--3%
1 jul 2016--1%

so depending upon today's date the DA rate needs to be summed up and added to my salary
so 0+2+3+1=6% needs to be added to my salary

second part
reference date needs to be variable so i can roll back my data to see what was the salary given in jul 2016 and aug 2016 because that would have been given with DA rates of only 0+2+3=5% DA
the DA rates have been announced late in sep 2017 but with retrospective effect from 1 jul 2017
so i need to add 1% to the salary already disbursed in july and aug 2017 and add it to the salary of sep 2017 as arrears

third part
notifications with retrospective effect of many allowances/addons are normally delayed in government deptts and a mechanism needs to be built in for arrears

 

[BrianJN1]

Earlier you stated that you wanted minimal disruption to your formulae and general layout.
Later you seemed to take a different view.
I had noted that your single worksheet was quite complex.

I think that if you broke your worksheet into different parts and allocated them to different worksheets then you see and address each component more easily.
As such you could have a series of columns(tables0 that are pertinent to your respective requirements.

 

[bibra]

i am looking for a link on youtube

i had come across a video on youtube showing how to use line numbers in maintaining a book keeping record by accountants for accounts receivable etc
unfortunately i deleted the link to the video before i could peruse it
can someone please direct me to the correct link