S
spence
I am trying to build a five year month-by-month personnel budget. I have my
staff names in column A, their anniversary dates (always on the first of a
given month) in column B, and their current monthly salary in column C. Row A
contains months from July 2007 through June 2012.
My assumptions are:
1. 2.5% raise on anniversary date of years one and two of employment
2. 3% raise on anniversary date of years three through five of employment
3. 1% raise on anniversary date every year thereafter
My big challenge is that we have relatively frequent internal promotions,
and at the time of a promotion, the anniversary date changes and the above
rate schedule resets back to the beginning. My goal is to build a workbook
that will calculate wage increases based on the anniversary date and wage
schedule and will update properly when I change the anniversary date of an
employee who gets a promotion. Additionally I want my raise percentages
referenced from cell values so that I can change them across the board if we
decide to alter the schedule.
I’ve been working on this for a couple of days with different designs and
can’t seem to figure out how to get all the variables accounted in my design
and formulas. I’d be happy to share my latest design structure but I thought
it might be better if I first just asked for some assistance on how to
approach the whole thing. I'm using Excel 2003. Thanks for any guidance.
spence
staff names in column A, their anniversary dates (always on the first of a
given month) in column B, and their current monthly salary in column C. Row A
contains months from July 2007 through June 2012.
My assumptions are:
1. 2.5% raise on anniversary date of years one and two of employment
2. 3% raise on anniversary date of years three through five of employment
3. 1% raise on anniversary date every year thereafter
My big challenge is that we have relatively frequent internal promotions,
and at the time of a promotion, the anniversary date changes and the above
rate schedule resets back to the beginning. My goal is to build a workbook
that will calculate wage increases based on the anniversary date and wage
schedule and will update properly when I change the anniversary date of an
employee who gets a promotion. Additionally I want my raise percentages
referenced from cell values so that I can change them across the board if we
decide to alter the schedule.
I’ve been working on this for a couple of days with different designs and
can’t seem to figure out how to get all the variables accounted in my design
and formulas. I’d be happy to share my latest design structure but I thought
it might be better if I first just asked for some assistance on how to
approach the whole thing. I'm using Excel 2003. Thanks for any guidance.
spence