Calculate total payroll for month based on start date, pay level, and account

[mrreisinger]

Needing help coming up with a formula to calculate salary expected to be used based on employee date hired, account, and pay level. See below. There are 3 tabs on my worksheet, Budget Forecaster, New Employee Tracker, and Pay Chart. Joe Smith was hired on 6/10/18 as a GS7(pay scale) which will be charged to account 121G. Jane Doe was hired on 6/25/18 as a GS9 and charged to account 121G. Looking at chart below, Joe makes $2760.96/pay period and Jane makes $3116.07/pay period. However, there are 2 pay periods in June so the total for the month for Joe would be $5521.92 and Jane would only get paid for 1 pay period. Total the amount charged to account 121G is $8637.99. Question is, how can I get this to reflect as a formula in the Budget Forecaster tab under the month of June? I will be doing this for hundreds of people and right now it takes hours to put this together. I need the pay chart dollar amount pulled based on the Pay scale of the employee and input into the corresponding month based on what account it needs to pull from and also account for if they get paid once, twice, or three times in the month depending on their start date. I know this is a lot, but i'm basically looking for a miracle. Any help or ideas to get something like this computed?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Budget Forecaster
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[TD]Jul
[/TD]
[/TR]
[TR]
[TD]Account
[/TD]
[TD]2 Pay Periods/mo
[/TD]
[TD]2 PP/mo
[/TD]
[TD]3 PP/mo
[/TD]
[/TR]
[TR]
[TD]111G
[/TD]
[TD]$43,602.21
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]121G
[/TD]
[TD]$11,122.23
[/TD]
[TD]?
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Employee Tracker
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date
[/TD]
[TD]Selectee
[/TD]
[TD]Pay
[/TD]
[TD]Account
[/TD]
[/TR]
[TR]
[TD]6/10/18[/TD]
[TD]Joe Smith[/TD]
[TD]GS7
[/TD]
[TD]121G
[/TD]
[/TR]
[TR]
[TD]6/25/18
[/TD]
[TD]Jane Doe
[/TD]
[TD]GS9
[/TD]
[TD]121G
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pay Chart
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS7
[/TD]
[TD]$2760.96
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS8
[/TD]
[TD]$2931.92
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS9
[/TD]
[TD]$3116.07
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robby19]

How do you denote how many pay periods are in a month? Are they tied to a certain date, or is it based off of the amount of weeks within the month? With this information I believe we could provide you with a formula to automate your process.

 

[mrreisinger]

Right now I just put a 2x or a 3x above the month to remind me how many pay periods there are. Basically it's a 2 week pay period that starts on Sunday and ends on the following Saturday. So example would be the pay period starts 6/10/2018 and ends 6/24/2018. I guess I could always put a calendar on a separate tab and if there's a way to specify that start date to that calendar it would read 1,2, or 3 pay periods to calculate. Or if you have another idea of how to generate that, I'm open to all avenues. Heck even if I just keep the 2x or 3x and link that into the equation. Does that help? What I did before was put all the info on 1 line and then manually calculate what each person makes for 1 pay period then do a 2x column and a 3x column. I would then add up those lines depending on what month it was and that was my best guess.

 

[Robby19]

To make this a fully automated process I would encourage you to assign actual date values to each pay period. This way a formula could review when the person was hired, look at the hire date vs the pay period date(s) of the current month and assign how many times they should get paid.

Also, another issue you could potentially run into in this scenario would be if an employee was promoted from GS7 to GS8. If you were to change their grade level and the formula was not prepared for that, then it would change all of the data for the previous months.

Secondly, I am assuming that the GS pay scale you are referring to is also associated with the US Government. If that is the case, how do you handle the different steps within the GS pay? For example, GS7 step 1 may get, for example, 1000 per pay period, but a GS7 step 5 may get 1500 per pay period. If this doesn't apply to you then please disregard.

I know this doesn't provide you with an actual answer to your initial question, just want to gather information as well as consider all avenues.

 

[Robby19]

Your pay calendar could look something like;

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6-May-2018[/TD]
[TD]3-Jun-2018[/TD]
[TD]1-Jul-2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20-May-2018[/TD]
[TD]17-Jun-2018[/TD]
[TD]15-Jul-2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]29-Jul-2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrreisinger]

No those are all great questions and I'm glad you understand the US Government pay system as this will definitely help!


For your first question, I'm not exactly sure how I would assign date values to each pay period, could you give an example?

Second, this is a forecaster for future months. The individuals listed are not ones that have actually hit the pay system yet, they either just accepted the job and have not started working yet or the name will be "vice Andersen", as in the job used to belong to Andersen but is now posted and they are in the process of boarding and hiring the job. Since we know the job will most likely be filled within the next month, I usually just put a start date 2 pay periods out (4 weeks). This tab links into my "master" tab that has multiple other tabs of info set up just like this one. I do have one that pulls all current pay data for currently paid employees from our main database system used by our agency. I'm trying to build the forecaster to follow that same data from the database.

They will all start at Step 1 so that's not an issue.

 

[Robby19]

See post #5 as this gives you a reference point for setting up the pay calendar.

Are you forecasting only for 1 month. For example, if I am selected for a position and my start date is 20 June, will you forecast my pay period amount into July? Or once I actually start work, do you not want to forecast me anymore since I am now an official employee?

 

[mrreisinger]

I would want to forecast that person for the rest of the fiscal year ending 30 Sept. This would give me an accurate estimate at the point and time I calculated this. However, the next pay period the employee would be "on the books" and would show up on my database generated data so I would then just delete him from the New Employee table and hopefully this calculation would adjust. Make sense? The big reason I have this forecaster is to determine how much money I will be short or over for the year in each account. I can track by month so I know where to cut or adjust accounts so I don't go in the negative in an account.