F
Forgone
I'm working on a salary budget template which takes leave loading into
consideration and having some difficulties trying to figure out how to
do the calculation if the total leave exceeds blocks of 4 weeks (20
working days).
I've got two formulas to work out leave loading.
The constants are: entitled to 4 weeks a year off (20 working days)
Leave Loading rate is 17.5%
Maximum leave loading payable on four weeks is $1,294.30
The first (simple) is:
=(Base Salary * No. of Weeks Off / 52 * Leave Loading Rate)
The second is:
=((Base Salary / No of Work Days**) * No of Days off * Leave Loading
Rate)
** = Networkdays(1/7/2009,30/06/2010)
To figure out whether the "calculation" exceeds the maximum rate
is....
=If ((leave loading calc < leave max,leave loading calc, leave max)
This is ok if an employee takes 4 weeks or less paid leave but if they
take more leave (accrued over the years) which is where I'm now
struggling........
The award states that the manimum annual leave loading that may be
paid on four weeks annual leave is $1,294.30 but if the employee takes
8 weeks paid leave then the same calculation needs to be done twice to
determine whether the first lot of 4 is less than or greater than and
again for the next 4.
I'm struggling to figure out how to achieve that. Any suggestions?
consideration and having some difficulties trying to figure out how to
do the calculation if the total leave exceeds blocks of 4 weeks (20
working days).
I've got two formulas to work out leave loading.
The constants are: entitled to 4 weeks a year off (20 working days)
Leave Loading rate is 17.5%
Maximum leave loading payable on four weeks is $1,294.30
The first (simple) is:
=(Base Salary * No. of Weeks Off / 52 * Leave Loading Rate)
The second is:
=((Base Salary / No of Work Days**) * No of Days off * Leave Loading
Rate)
** = Networkdays(1/7/2009,30/06/2010)
To figure out whether the "calculation" exceeds the maximum rate
is....
=If ((leave loading calc < leave max,leave loading calc, leave max)
This is ok if an employee takes 4 weeks or less paid leave but if they
take more leave (accrued over the years) which is where I'm now
struggling........
The award states that the manimum annual leave loading that may be
paid on four weeks annual leave is $1,294.30 but if the employee takes
8 weeks paid leave then the same calculation needs to be done twice to
determine whether the first lot of 4 is less than or greater than and
again for the next 4.
I'm struggling to figure out how to achieve that. Any suggestions?