assistance with budget design
 

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 a

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.

Ive been working on this for a couple of days with different designs and
cant seem to figure out how to get all the variables accounted in my design
and formulas. Id 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

assistance with budget design
 

Hi

Let me try to help you.

Is it what you currently have?

A B C D
E
Name A-Date Cur-Sal 07JUL 07AUG
Peter 1/1/2000 10,000

I think doing the calculation is not difficult, but if you change the
A-Date, then your history will be loss, right? How would you store that?

if you don't mind to share your design so I could help you.

Leung





"spence" wrote:

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 a

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.

Ive been working on this for a couple of days with different designs and
cant seem to figure out how to get all the variables accounted in my design
and formulas. Id 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