• 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

S

Surrena

Member
Hi All,

I’ve just been assigned the task of taking over the reporting of monthly employee vacation accruals. This report has already been created and the person who created it used advanced formulas, however I am totally green in this area. In attempting to recreate the report I noticed that some of the formulas aren't working properly (see the attached). Well, they work fine until I get to the accruals for our San Francisco employees.

Because of California law vacation accruals for California employees; specifically the carryover accruals are calculated differently. Carryover accruals for California employees cannot exceed 1 1/2 times their annual allotment. While I understand the rule, I can’t seem to grasp how to create a formula that would provide the total number of days accrued plus the total carryover days not to exceed 1 1/2 times the total annual. Looking at the report, would anyone be able to assist me and or recommend a better way to create the accruals based on the information provided? Any assistance will be greatly appreciated.
 

Attachments

  • Vacation Accruals.xlsx
    58.7 KB · Views: 15
Hi ,

Can you work out a formula using the MIN function ?

Suppose the annual allotment is named AnnualAllotment ; the total number of days accrued is DaysAccrued , and the total carryover days is named CarryOverDays.

A formula such as :

=MIN(1.5 * AnnualAllotment , DaysAccrued + CarryOverDays)

would restrict the total accrual to 1.5 times the annual allotment.

Narayan
 
Surrena -

I've made your worksheet into a Excel Table - it helps with many things including selecting Locations, for example.

In the attached file, your CA calculations appear to be on two lines for each employee - one being for California Vacation the other row being for California Vacation Carryover. Its not the best way to structure your table as its confusing as hell and you can't total for the CA employees *easily*

I think what Narayan is suggesting that you combine these rows by using his MIN formula.

Also, now that it's a Excel Table, you will see that it's much easier to write the formula as Narayan suggested as they use the Structured Reference which is essentially the Column Heading - what I am not sure of is which column has CarryOver Days - is it Col L?
 

Attachments

  • Vacation Accruals -DME Table.xlsx
    56.8 KB · Views: 15
Hi David -
Thanks for your input it is greatly appreciated.
This report was created by someone else. If you know if an easier way to do this I am definitely open to hearing and learning about it.

Column E (you'll need to expand it) has the description (There's two rows; one for regular vacation and the other for carryover vacation) and if applicable, column F lists number of vacation days carried over. Again, thank you for your feedback.
 
Hi Narayan,

Thank you for your feedback, it is greatly appreciated. I will try using your suggestion and let you know how it worked out. Again, thank you very much!
 
Give me a few moments here this morning and I should be able to sort it out for you by applying Narayan's Excellent formula into a single row -that's where this is confusing - the two rows ...
 
There's something awry with the logic of the formula in F which I can't quite put my finger on

=IF(OR(E2=$E$2,E2=$E$276),F2/C2*D2,F2)

I don't understand why your formula is checking for =OR(*US/CAN Vacation, *California Vacation) - because OR essentially means any one of those values

And it's not as if those values are anything other than text expressions ... they don't refer to any constants/values etc.

You've also got Circular references in this worksheet, which is what I'm going to look at next ...

We need a few more pairs of eyes on this! Where's the Cavalry?:DD
 
Again, thanks David!
As I mentioned earlier I inherited this report from another colleague (formulas were created by her). I am attaching the report that shows the formulas working (Note due to the sensitivity of the data Columns A & B were deleted).

If you know of a better way to that I can accomplish the desired outcome which is to determine the total maximum vacation payout please share. I am open to all suggestions.
 

Attachments

  • Sample Report - Complete.xlsx
    63.3 KB · Views: 11
Also, I forgot to mention that for the CA (California Employees) the accrual is processed a little differently as these employees are allowed to carry over vacation not to exceed 1 1/2 times their annual allotment.
 
I'm off on a Conference Call in a few minutes, but I'm getting close to simplifying the formulas for you, a la Narayan's:)awesome::awesome:) suggestion. Perhaps another of the Chandoo Devotees will also chime in here, while I'm away, to get you the result sooner.

I understand the Logic of the CA employees - I'm a Californian :cool::DD
 
Tell us again how you calculate accruals for Non-California Employees. Which values do you use, specifically. What is the difference between columns "Calculated Accrual" and "Accrual YTD"?

Where do these Carryover Accruals appear in the Table? I suspect they have a different name ....
 
Hi ,

As far as I can see , the relevant columns are only M , N and O.

Column M had the following formula in M2 :

=IF(OR(K2=$K$2,K2=$K$279),L2/G2*H2,L2)

I have replaced this with the following more easily understandable :

=IF(OR(K2="*US/CAN Vacation",K2="*California Vacation"),L2/G2*H2,L2)

Column N does not have anything till row 275 , and thereafter it has only error values ; can you explain what should be calculated in this column ?

Column O had the following formula in O2 :

=IF(AND(K2=$K$275,N2>1.5*L2),M2+(1.5*L2-N2),M2)

I have replaced this with the following more easily understandable :

=IF(AND(K2="*California Vacation",N2>1.5*L2),M2+(1.5*L2-N2),M2)

Since this formula is dependent on what is in column N , at present it does not make sense. Once you explain regarding column N , we can rewrite this if required.

Narayan
 
NARAYANK991 said:
Hi ,

As far as I can see , the relevant columns are only M , N and O.

Column M had the following formula in M2 :

=IF(OR(K2=$K$2,K2=$K$279),L2/G2*H2,L2)

I have replaced this with the following more easily understandable :

=IF(OR(K2="*US/CAN Vacation",K2="*California Vacation"),L2/G2*H2,L2)

Column N does not have anything till row 275 , and thereafter it has only error values ; can you explain what should be calculated in this column ?

Column O had the following formula in O2 :

=IF(AND(K2=$K$275,N2>1.5*L2),M2+(1.5*L2-N2),M2)

I have replaced this with the following more easily understandable :

=IF(AND(K2="*California Vacation",N2>1.5*L2),M2+(1.5*L2-N2),M2)

Since this formula is dependent on what is in column N , at present it does not make sense. Once you explain regarding column N , we can rewrite this if required.

Narayan
Click to expand...


Narayan -

As you've no doubt gathered, Once you get to Row 275 the logic of the Calculation changes and each employee is "calculated" on two rows - the first being to calculate *California Vacation - the next to calculate *California Vacation Carryover. Once we understand how each calculation is structured we'll be able to formulate a single row solution, and the worksheet will become much clearer, and dare I say it, more useful.

I guess Surrenna has gone home for the evening - I suspect she's on the East Coast of the USA - we may not get an answer til Tuesday am (Monday is a Holiday in the USA). I will be enroute to NYC myself and a little out of contact for the week for Excel challenges! However, I now you will solve it!

Thanks

D
 
Hi Narayan & David,

Thank you both for your assistance and patience.

Two things to note, 1 - this report was created by someone else and it's logic was not explained to me (basically I was left to figure it out on my own :() and 2 - I am totally green with the use of advanced formulas. Although, green I've been trying to understand the logic in order to provide a clear explanation to you and from the looks of it I'm not doing so well, sorry :(!

Yes, as you've noticed the calculation for CA employees is a bit different due to the policy surrounding carryover vacation. If I understand the logic correctly; what my predecessor was trying to do in creating the formula for CA employees on row 1 (275) was to 1 - stop the calculated accrual for regular vacation when an employee's carried over vacation + regular vacation exceeds 1 1/2 the annual vacation entitlement; Row 2 (276) CA Carryover Vacation should continue to remain fixed (cannot be altered) as they were accrued in the previous year and carried over to current year.

Does the above explanation make sense? Again, if either of you can suggest/create an easier way to reach the desired outcome which is to determine the maximum vacation payout amount based on the current month's accruals it would be greatly appreciated. Again, thanks for your continued assistance it is greatly appreciated.
 
Hi ,

Can we keep the workbook aside , and concentrate on the logic ?

Do you know what the logic for the calculations is , or are you just a user of the workbook ?

If you can explain clearly what is to be calculated , what is the input data which will be used for calculations , and what the calculation logic will be , we can put in the necessary Excel formulae.

The second workbook that you have uploaded contains a formula which has error values , and nothing much can be understood from it. You need to set aside this workbook , and explain what you want done ; we can then see how it is to be done.

Narayan
 
Hi Narayan,

Yes, I am a just a user of the workbook.

However, I do believe I understand what outcome the workbook is attempting to reach. That’s to determine the maximum vacation pay the organization must pay out in each given month.

What is to be calculated?


Total Months – If hire date is less than current year; return 12 if not return total months’ employee will work in current year.

Current Month – Based on hire date this number should reflect the total number of months worked so far in the previous year (for example; if an employee’s hire date is 12/31/15 and today date is 9/3/16 the calculated number in this column should return 9, if an employee’s hire date is 5/1/16 and today’s date is 9/3/16 calculated number of months in this column should return 5).

Calculated accruals – We want to arrive at the number of days accrued thus far in the current year by taking the Annual Entitlement/Total Months * Current Month.

CA (California Carryover) – In this column the logic is to calculate the accrual for our California employees however, if the CA Vacation Carryover + the Calculated accruals total exceeds 1 ½ the Annual Entitlement we want to see is the total maximum amount allowed.

Accrual YTD – In this column the logic is to calculate the total accrual days (row 1 regular accrual days taking into account limit for CA employees; row 2 total carryover vacation days).

Current Formula for row 1 written in words:

=IF(AND(Time Off Plan = California Vacation, CA>1.5*Annual Entitlement), Calculated Accrual +(1.5*Annual Entitlement – CA),Calculated Accrual)

Current Formula for row 2 written in words:

=IF(AND(Time Off Plan = California Vacation, California Vacation Carry Over>1.5*Annual Entitlement), Calculated Accrual +(1.5*Annual Entitlement – CA), Calculated Accrual)

Max Days to Pay – Show calculated maximum days to pay (How many days to pay)

Current Formula written in words:

=IF(Annual YTD – Days taken as of Report Date <0,0, Accrual YTD – Days taken as of Report Date)

Daily Rate of Pay - We want to display the daily rate of pay so to arrive at this number we divide the Base Salary by 260 (Base Salary/260 returns Daily Rate of Pay)

Max to Pay - We want to calculate the total dollar amount to be paid out by multiplying the Daily Rate of Pay by the Max Days to Pay (Daily Rate of Pay * Max Days to Pay).

I hope the above explanation provides clarity to what it is exactly my predecessor is trying to achieve with this workbook.

Again, thanks for your continued assistance.
 
Hi ,

That's a wonderfully detailed and clear explanation ; I wish all problems were posted in this manner.

Can you please wait while I digest all that you have said , and go through the worksheet to see what changes will have to be made ?

I will either get back to you with doubts , if I have any , or I will upload the revised workbook.

Narayan
 
Surrena -

Let me see if I can provide an example for a CA Employee, which will help us get the logic for the Pesky Californians.
Let's Say David Evans works in your SF Office and has worked there for 10 years. Last year (2015), David received 20 days of Vacation, of which he only took 8, thus he has 12 days "carried over" into 2016. David continues to accrue at the rate of 20 days per year, however, this year he has not taken any vacation. If he were anywhere other than CA, at year end he could claim to have 32 days "accrued" or owing to him. However being in CA, his accrued amount would be capped at 30 days, being 1.5*Annual entitlement. Is this logic correct?

OK - if it is, and I *think* it is - where in your current worksheet is the Carried over amount from previous years stored?
 
HI David,
The way this report was created both of the Annual entitlement and CA vacation carryover days are listed in the Annual Entitlement column; row 1 & row 2 (row 1 accrual days, row 2 carryover days).

Thanks,
 
Hi Narayan,
Yes, of course I can wait. Again, thanks for your continued support and assistance both are greatly appreciated.
 
OK - Surrena - I've worked up an example for you - I took two example employees from each location and popped them into a New Excel Table. I've also included some features to calculate current dates, Report Date drop down in the table etc.
You will see that you no longer require two rows for each CA employee - that was the bit that was a little confusing to be honest ....

The only slight difference is that you now put the amount carried over from previous year into Col H - I've only done that for the CA employees, but I think it would make sense to include it for all employees and modify the formula to accommodate the change. Also - Accrual YTD is now superfluous - it is the same as Calculated Accrual.

You will notice that the formulas use the column headings and not cell references - it's a feature of Excel Tables known as Structured References. I think it makes understanding what goes on there a lot easier, although the column headings can be a little verbose!

Take a close look at this for compatibility with what you were computing with your prior worksheet - I've made a cursory review of it, but I'm rushing to prepare for a trip to NYC - some of the other lurkers around here will no doubt identify any errors and point them out to me!

The key formula is below - it was @NARAYANK991 who came up with it many messages ago; his insight is always spot-on, fair play.

=IF([@[Time Off Plan]]= "*US/CAN Vacation",
[@[Annual Entitlement]]/[@[Total Months]]*[@[Current Month]],
MIN(([@[Annual Entitlement]]/[@[Total Months]]*[@[Current
Month]])+[@[CA Carryover Amount]],
1.5*[@[Annual Entitlement]]))

All the IF statement does is identify the *US/CAN Vacation records - anything else is California and it's pesky rules, which are handled by the MIN statement ....

Hope this helps you get the model working and keep playing with Excel!
 

Attachments

  • Vacation Accruals - DME with Excel Table.xlsx
    83.6 KB · Views: 23
Hi David,

Thanks for your help, I really appreciate it.
The report looks awesome! However, looking at the report I am even more confused. Your solution is far more advanced than I am with Excel. If you recall I am green basically a beginner. I was hoping to get something that was a bit more simple and easier to understand. Would you mind assisting me with that please?
 
Surrena said:
Hi David,

Thanks for your help, I really appreciate it.
The report looks awesome! However, looking at the report I am even more confused. Your solution is far more advanced than I am with Excel. If you recall I am green basically a beginner. I was hoping to get something that was a bit more simple and easier to understand. Would you mind assisting me with that please?
Click to expand...
Truthfully, @Surrena, it is simpler now than in its previous incarnation. However, you're probably only having some anxiety with some features which are new to you - so let's see if I/we can help you understand some of these features.
The biggest difference between the two approaches is that the new one uses Excel's table feature. The table feature causes the formulas to be expressed in a more literal manner as the column headings, rather than cell references.
There are also some date manipulation formulas in there which negate the need to edit formulas as the report date changes .... If these are where you confusion lies, let us know and we can explain then in more detail. Finally, there is the IF statement, which was used in the original, but in a slightly different manner. If "IF" is causing confusion let us know, and we' all help clarify it.
The good news with the New table us that each individual is represented by a single row, whereas the Old table had two rows for each CA employee - not necessary, very confusing and I suspect was causing circular references.
I'm in Philadelphia and doing this from my phone. I can probably respond sooner but Excel is hard on my phone!
Don't let this scare you - you will get it in no time, and you will be on your way to bring an Excel whiz!
 
You must log in or register to reply here.
Back
Top