+ Reply to Thread
Results 1 to 16 of 16

Salary Projection Calculation

  1. #1
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Salary Projection Calculation

    Here is what I am trying to accomplish:
    For employees who were hired during 2020, forecast how much salary they are missing in their YTD salary number. Example: If they were hired 2/1/2020, they would have missed 2 pay periods, the partial adjustment would need to be 2* the current month salary.

    So far I have that part figured out. I simply calculate the number of pay periods are between the first pay period in 2020, in our case it is 1/9/2020 and their start date. The issue becomes, what if they were hired 1 week in to the pay period, say 1/16/2020 with the next payroll being 1/23/2020. This is where I can really use some suggestions on how to sharpen my formula. I am thinking that I need to get the percentage of the pay period they are hired in, but not sure how to put that into effect.

    Attached is what I have so far

    Many thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    On what date does the 2020 pay period start?
    Your worksheet has 12/22/201 as The last pay period in 2019.

  3. #3
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    12/23/2019 - 1/3/2020 is the what we are paid for in the first pay period on 1/9/2020
    Is that what you are asking?

  4. #4
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    You need to know how many pay periods missed once hired so you have to be able to work back to the first possible date that a 2020 hire would start being paid correct?

  5. #5
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    I believe that is what I have so far, its the issue of people being hired that are being paid for partial pay periods

  6. #6
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    I presume people are paid for the one week they worked in the two-week pay period?

  7. #7
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    You are correct.
    But from a forecasting perspective, in 2021, we want to project how much this person is worth for the full year. So if the person is short a week and we use that number as the basis for our calculation, the calculation is going to be incorrect.

  8. #8
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    There seems to be no column with the rate of pay?
    What does column F represent? It is titled Pay_Periods and then the data beneath are dates that don't seem to be in a pattern relating to rest of the document.

  9. #9
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    I am using the "MONTH OF SEPTEMBER 2020 PAY" as the basis for the rate of pay

  10. #10
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    I'm not sure I'm grasping your situation.
    See if the yellow areas are what you are looking for.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    How are you using the calculation "Weeks Missed" and why is "Yearly" multiplying by 24?

  12. #12
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    calculation "Weeks Missed": # of days from 12/22/19 to Hire date divided by 7. This gives the # of weeks they weren't employed by your firm I believe (unless that should be from 12/31/19?
    You shared "MONTH OF SEPTEMBER 2020 PAY" as the basis for the rate of pay"; I presumed two pay periods/month per year to calculate full year's pay.

  13. #13
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    You are correct in all of that, its just it does not look like you are referencing "Weeks Missed" in any salary calculations.
    Also, my mistake, I didn't say that we have 26 pay periods in our company. My apologies

    Also, thank you very much for all your input, this is very helpful!

  14. #14
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    its just it does not look like you are referencing "Weeks Missed" in any salary calculations.
    Correct; I just decided to calculate it "my way"; not that Weeks Missed couldn't have been used; I just wasn't sure it was right.
    You can adjust the 26 weeks part by changing the start date of the calculation if that somehow represents 26 weeks.

  15. #15
    Registered User
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    57

    Re: Salary Projection Calculation

    The "Weeks Missed" was very helpful, I think that is what I will use, or at least purpose.
    Thank you for your help on this!

  16. #16
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: Salary Projection Calculation

    You are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Salary Monthly Calculation
    By DimitraBoz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2018, 10:28 AM
  2. Salary Calculation Between two dates
    By Shanu Singh in forum Excel General
    Replies: 2
    Last Post: 05-15-2017, 12:55 PM
  3. [SOLVED] Salary Calculation Question
    By Blu3 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2016, 05:05 AM
  4. Income Projection/ Revenue Projection templates
    By penza in forum Excel General
    Replies: 1
    Last Post: 10-02-2015, 06:36 AM
  5. Help in Calculation Salary
    By AVG123 in forum Excel General
    Replies: 12
    Last Post: 09-19-2015, 08:45 AM
  6. [SOLVED] Employee salary calculation help!
    By mhweiler in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-21-2015, 10:05 PM
  7. salary calculation
    By tariqnaz2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2013, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts