• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vacation Accrual Assistance

David Evans said:
OK - take a look at the attached.

I think we're closing in on a robust file, but you will see that there are some questionable values in some of the cells. Many of the Employees have maxed out their contributions too early thus limiting the amount of the match they receive. Also some have exceeded the Maximum deferral amounts for their respective ages.

If this is not computing according to your plan's rules, you'll need to illustrate where it is not reflecting the correct amounts and what you believe that amount should be, based on your understanding of the rules.
Click to expand...

Hi David,

OMG! This report is so awesome!

Again, thank you so very much for assisting me with this.

Yes, I agree. When employees max out on their 401k contributions early in the year they don’t receive the full benefits of the 401K employer match. However, in order to ensure employees receive the full benefits of the match our plan has a provision which allows the organization to provide a 401k True-Up (sort of like a catch up) match at year end.

We determine this by calculating the total 401k match an employee is expected to receive for the year based on their contribution rate and subtracting what they’ve already received by that amount at a given point in time. The remaining amount, if any is the estimated True-Up amount to be funded at year-end.

Actually, this is the true purpose for creating the report; to determine 1 – the total $$ amount the organization will require to fund True-Up payments at year-end and 2 – to determine on a quarterly basis the total $$ amount the organization will need to fund 401k ER matching contributions and accrue for these amounts. I hope this adds more clarity. If you have any further questions please let me know. Again, thank you for all your assistance it is greatly appreciated.

Please note that although in some cases an employees deferrals appear to exceed the maximum limits, I assure you that there are controls in payroll to ensure that this does not happen. In short, once an employee reaches the annual maximum amount allowed for his or her age he or she will no longer be allowed to contribute; 401k deductions will cease.
 
Last edited:
Hi,

I am back again with issues calculating vacation accruals I am new with tables and it appears my formulas are not working properly. Can someone please, please assist me?
Here is what I need to report to include.

Column G - This column should indicate a formula for total months employees will work in the current year. For example someone hired on 12/16/2016, it is estimated that he or she will work a total of 12 months in the current year.

Column H - This column should indicate a formula for the number of the current month based on the report effective date.

Column M - This column should indicate the per monthly accrual based on the Annual Entitlement if:
The Time off Plan is = to US/CAN Vacation and or is =California Vacation
In addition, Column M should also include a formula that would indicate that if the Time Off Plan is = to US/CAN Vacation Carryover and or is =Time Off Plan California Vacation, the Annual Entitlement should remain the same.

Column N - This column (pesky California Rules) :), This column should include a formula that would calculate the monthly accrual based on the annual entitlement and the report effective date not to exceed 1.5 times the Annual Entitlement for both California Vacation & California Vacation Carryover.
Thank you in advance for your assistance, it is greatly appreciated.
 

Attachments

  • Sample Excel Table.xlsx
    79.1 KB · Views: 2
Welcome back ...

I've fixed Column H for Current Month - I think .... Take a look at it and tell me if this is the answer you expect, also do likewise for a few examples in col M
 

Attachments

  • Surrena - 2017.xlsx
    83.1 KB · Views: 10
David Evans said:
Welcome back ...

I've fixed Column H for Current Month - I think .... Take a look at it and tell me if this is the answer you expect, also do likewise for a few examples in col M
Click to expand...

Hi David,
Thanks for your quick response.
When I downloaded the spreadsheet it appears to have the same issue. The Total Months Column that depicts the expected calculated months employees will will in the current year is still incorrect for newly hired employee. For example there are employees who were hire in January of 2017, the Total Months should depict that these employees will work 11 months but it shows zero. Can you please review further? Thanks!
 
Check your formula for when the condition is false - you are subtracting the number of the current month from 1, where it should be from 12, if I'm understanding you correctly ....
 
David Evans said:
Check your formula for when the condition is false - you are subtracting the number of the current month from 1, where it should be from 12, if I'm understanding you correctly ....
Click to expand...
Hi David,

That worked! Thanks!
Now, can you please assist me with Column M?
Column M - This column should indicate the per monthly accrual based on the Annual Entitlement if:
The Time off Plan is = to US/CAN Vacation and or is =California Vacation
In addition, Column M should also include a formula that would indicate that if the Time Off Plan is = to US/CAN Vacation Carryover and or is =Time Off Plan California Vacation, the Annual Entitlement should remain the same.

Column N - This column (pesky California Rules) :), This column should include a formula that would calculate the monthly accrual based on the annual entitlement and the report effective date not to exceed 1.5 times the Annual Entitlement for both California Vacation & California Vacation Carryover.

Again, thank you so very much for your assistance.
 
Why does your model have two rows for each employee? I recall this being a "feature" previously, and I don't recall how I got around it ...

Also, give us some of the answers you expect the calculation to be for a range of samples.

I think you'd be better off removing the two rows per employee and adding an additional column with carried over amount. Currently you cannot use the table for any summary information owing to the double entry method adopted.

I know you inherited this, however, it's time to think about improving it and making it easier to comprehend - it's unnecessarily convoluted in its logic.
 
David Evans said:
Why does your model have two rows for each employee? I recall this being a "feature" previously, and I don't recall how I got around it ...

Also, give us some of the answers you expect the calculation to be for a range of samples.

I think you'd be better off removing the two rows per employee and adding an additional column with carried over amount. Currently you cannot use the table for any summary information owing to the double entry method adopted.

I know you inherited this, however, it's time to think about improving it and making it easier to comprehend - it's unnecessarily convoluted in its logic.
Click to expand...
Hi David,
The report is generated from another source which outputs the information as you see it. How can I quickly get the information to display across columns from the attached report? Please advise. Again, your assistance is greatly appreciated.
 

Attachments

  • Time Off Accrual Summary Report - Sample.xlsx
    90.9 KB · Views: 5
Surrena said:
Hi David,
The report is generated from another source which outputs the information as you see it. How can I quickly get the information to display across columns from the attached report? Please advise. Again, your assistance is greatly appreciated.
Click to expand...
What does the other source look like? Give us some details/examples .. Is it Excel based?
 
You must log in or register to reply here.
Back
Top