Writing codes to project salary - How & where to start

[UongSaki]

I need help in writing a visual basic codes to make a salary projection for my company budget. The cost would include an annual increase as agreed with different unions and an employee date of hired or date of last salary increase due to a step increase.

Time between steps for:

Union Code Job Code step Time Between Step Salary
Union A 1000 1 6 ms 10.00000
Union A 1001 2 1 yr 10.50000
Union A 1002 3 1 yr 11.07500
Union A 1003 4 1 yr 11.57625
Union A 1004 5 12.15506
Union B 1005 1 6 ms 15.00000
Union B 1006 2 1 yr 15.75000
Union B 1007 3 1 yr 16.53750
Union B 1008 4 1 yr 17.36438
Union B 1009 5 18.23259
Union C 1010 1 1 yr 15.00000
Union C 1011 2 6 mo 15.75000
Union C 1012 3 6 mo 16.53750
Union C 1013 1 1 yr 15.00000
Union C 1014 2 6 mo 15.75000
Union C 1015 3 6 mo 16.53750
Union C 1016 4 6 mo 17.36437
Union C 1017 5 6 mo 18.23259
Union C 1018 6 6 mo 19.14422
Union C 1019 7 6 mo 20.10143

On my employee data I have EmpID, Union, Job Code, Current Step, Current Pay.

I would like to make a salary projection for the next three years


Thank you,

UongSaki

 

[SkipVought]

Hi,

1) your INTERVAL, Time Between Step, should ALL be the SAME UNITS, either years or months, WITHOUT the unit designator included.

2) The employee data is missing one important column, DateOfLastStep

3) Does not the CurrentStep define the CurrentPay?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

and what about the rows without a Step Value???
[tt]
Union A 1004 5 12.15506
[/tt]

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

ok those are the LAST interval -- except fot the last example?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[UongSaki]

Hi Skip,

Sorry, the time between step in Union C should be blank. This is the last step and any employee who reaches that step will no longer get a step increase. Union C employees have either 3 or 7 steps. And yes, the current step defines the current pay and I do have the date of last pay increase.

Union C 1010 1 1 yr 15.00000
Union C 1011 2 6 mo 15.75000
Union C 1012 3 16.53750
Union C 1013 1 1 yr 15.00000
Union C 1014 2 6 mo 15.75000
Union C 1015 3 6 mo 16.53750
Union C 1016 4 6 mo 17.36437
Union C 1017 5 6 mo 18.23259
Union C 1018 6 6 mo 19.14422
Union C 1019 7 20.10143

Thank you,

UongSaki

 

[SkipVought]

Where is the date of the last increase for an employee?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[UongSaki]

That date is coming from the Employee data, Employee table.

Thank you,

UongSaki

 

[SkipVought]

please post a sample of you employee data.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[UongSaki]

Hi Skip,

Thank you for taking the time to help me. These are the portion of data in tables that I'm trying to project salary cost for each employee. I copied&Pasted from WordPad.

TblEmployee

Persno UnionGroup PayGroup PayLevel HourlyRate LastSalatyIncreaseDate DateOfHire
11527 B B420 1 17.3050 11/08/04 11/08/04
11518 B B466 1 21.4994 10/04/04 10/04/04
10956 B B392 1 15.1858 10/18/04 11/13/01
11503 B B564 1 34.2476 08/23/04 08/23/04
11453 B B543 1 30.9865 07/06/04 07/06/04
11451 B B543 1 30.9865 06/21/04 06/21/04
10790 U U45 3 20.8900 11/01/04 09/10/01
10738 U U45 3 20.8900 11/01/04 08/13/01
10716 U U45 3 20.8900 11/01/04 07/30/01
10721 U U45 3 20.8900 11/01/04 07/30/01
11179 G G295 3 35.4583 07/12/04 04/08/02
11076 G G295 4 37.2330 12/22/03 01/02/02
11135 G G295 4 37.2330 07/19/04 01/28/02
10983 B B395 2 16.1191 10/25/04 11/19/01
11396 B B517 2 28.7245 09/27/04 03/29/04
10736 B B448 4 22.7403 10/25/04 08/13/01
10492 B B424 4 20.3028 10/25/04 04/02/01
11123 G G260 4 31.9921 07/19/04 01/22/02



TblSalary

PayGroup PayLevel UnionGroup EffectiveDate HourlyRate
B392 1 B 13-May-02 $15.1858
B392 2 B 13-May-02 $15.8994
B392 3 B 13-May-02 $16.6680
B392 4 B 13-May-02 $17.4696
B392 5 B 13-May-02 $18.3043
B420 1 B 13-May-02 $17.3050
B420 2 B 13-May-02 $18.1395
B420 3 B 13-May-02 $19.0069
B420 4 B 13-May-02 $19.9294
B420 5 B 13-May-02 $20.8955
B466 1 B 13-May-02 $21.4994
B466 2 B 13-May-02 $22.5318
B466 3 B 13-May-02 $23.6186
B466 4 B 13-May-02 $24.7715
B466 5 B 13-May-02 $25.9904
B543 1 B 13-May-02 $30.9865
B543 2 B 13-May-02 $32.5018
B543 3 B 13-May-02 $34.0830
B543 4 B 13-May-02 $35.7629
B543 5 B 13-May-02 $37.5198
G295 1 G 02-Jan-03 $32.1650
G295 2 G 02-Jan-03 $33.7780
G295 3 G 02-Jan-03 $35.4583
G295 4 G 02-Jan-03 $37.2330
G295 5 G 02-Jan-03 $39.0829
U47 1 U 01-Nov-04 $18.7300
U47 2 U 01-Nov-04 $20.0700
U47 3 U 01-Nov-04 $21.4100
U47 4 U 01-Nov-04 $22.7600
U47 5 U 01-Nov-04 $24.0800
U47 6 U 01-Nov-04 $25.4300
U47 7 U 01-Nov-04 $26.7500
U69 1 U 01-Nov-04 $28.4100
U69 2 U 01-Nov-04 $30.3700
U69 3 U 01-Nov-04 $32.3000

TblScheduleContractIncrease

UnionGroup Contractual Increase in % EffectiveDate
U 11/01/2005 1.250%
U 11/01/2006 2.000%
U 06/07/2007 2.250%
B 04/15/2005 1.250%
B 04/15/2006 2.250%
B 04/15/2007 2.000%
G 06/01/2005 3.000%
G 06/01/2006 1.000%
G 06/01/2007 1.000%


TimeBetweenSteps


UnionGroup PayGroup PayLevel TimeBetweenStep NextPayLevel
B B392 1 6 2
B B392 2 12 3
B B392 3 12 4
B B392 4 12 5
B B392 5
B B420 1 6 2
B B420 2 12 3
B B420 3 12 4
B B420 4 12 5
B B420 5
G G295 1 6 2
G G295 2 12 3
G G295 3 12 4
G G295 4 12 5
G G295 5
U U47 1 12 2
U U47 2 6 3
U U47 3 6 4
U U47 4 6 5
U U47 5 6 6
U U47 6 6 7
U U47 7
U U50 1 12 2
U U50 2 6 3
U U50 3 6 4
U U50 4 6 5
U U50 5 6 6
U U50 6 6 7
U U50 7
U U55 1 12 2
U U55 2 12 3
U U55 3
U U60 1 12 2
U U60 2 6 3
U U60 3 6 4
U U60 4 6 5
U U60 5 6 6
U U60 6 6 7
U U60 7
U U64 1 12 2
U U64 2 12 3
U U64 3
U U65 1 12 2
U U65 2 12 3
U U65 3
U U66 1 12 2
U U66 2 12 3
U U66 3
U U68 1 12 2
U U68 2 12 3
U U68 3
U U69 1 12 2
U U69 2 12 3
U U69 3


Thank you,

UongSaki

 

[MichaelRed]

One issue is not (yet) covered in the list of issues.

A more accurate projection could be devised if the previous change rates (retirement, replacement, and other transitions) were available and considered.

This should be available from other sources (Human Resources, or your employee Table / data.

Also, the Union C step 3 missing needs to be resolvalble, i.e. known to either be an end or have an interval for each (individual) employee.





MichaelRed

 

[UongSaki]

Hi Michael,

You are correct that the data (retirement, replacement, and other transitions) are in employee table or related employee tables. I don't know how incorporate them in the calculation that why I just want to project the cost of employees salary assuming that they were to stay with the company until, say, June 30, 2008. By the way, these data are down loaded from SAP.

As for the union C, there are groups with only 3 steps and there are groups with 7 steps.

Thank you,

UongSaki

 

[MichaelRed]

Assuming 'group' ~~ Job Code? Then these need to be known, and the 'Step' 3 interval needs to be included. A (perhaps) clearer (moere normalized?) approach would be to create seperate 'Union Codes', like 'Union C3' for the 3 steppers and 'Union C7' for the seven step program.

Including ther retention data would, admitedly, be a complication and it could be done via a number of approaches. One of the easier would be to generate stats for each employee union, job, and step detailling how many leave at each interval (step). Asume all of them leave at the mid-point of the step interval and are replaced (instantly?) with a new employee in hte same union/job at step 1. One could also 'assume that the new (replacement) employee did not start to work for any given interval (two weeks, one month, ...). The Stats re lost employees would probably be better if done seperatly as this is not likely to change without some major change in the business, which would be easily noticed -and disrupt any such projection.




MichaelRed