How do I prepare a allowance payment tracker?

[Guest]

Hi everybody. We come to a close to pay our employee's 13th month bonus. We
need to pay this bonus in two different payments 50% each. Can you help me
prepraring a kind of tracker so it would be my base to prepare my payslips.
Also if you can design kind of pay slip for this bonus as well. It is our
company's policy that if an employee has compeleted his one year at the time
of bonus payment, he/she is eligible for one month salary. If an employee
joint the company later his/her bonus payout will be prorated accordingly
(monthly salary/12 * # of months worked. Thanks for your cooperation.

 

[Guest]

I'm going to lay the formulas out individually, some could be combined to
keep the worksheet smaller, but this will give you a better view of what is
going on:
column B has the employee's monthly salary in it
Column C has the employee's hire date
Column D has date the bonus is based on
Column E computes the number of months worked when date in D occurs (for row
2)
in E2:
=(YEAR(D2)-YEAR(C2))*12+MONTH(D2)-MONTH(C2)
in F2 we calculate bonus, full month's pay for 12 or more months of
employment, 1/12 per month for less
in F2:
=IF(E2>=12,B2,(B2/12)*E2)
in G2 calculate 50% of that since you said you pay it out in 2 payments
in G2:
=F2/2

I prepared a .xls file showing this method and also how to combine the
formulas into a single cell for calculating the bonus, it is available for
download at:
http://www.jlathamsite.com/uploads/CalculateBonus.xls

I'm going to stay away from the 'pay slip' design part of it - since that is
probably something your accounting department needs to get in on - when my
company hands out any type of bonus check for a special occasion we just
include the normal payday type of stub along with a nice letter from either a
supervisor or the company president saying why they got the bonus. And the
stub serves to show how come even though we got a bonus of $100, we only got
a check for $65 (taxes and all, you know).

 

[Guest]

Dear Sir,
Thanks much, it is really appreciated and it works very well. Have a great
life.

Daoud Fakhry