UK Redundancy

[Rob]

Hi,

I have the unpleasant task of calculating redundancies for employees and
since the rules have changed to the UK statutory calculations and I'm in
need of a new spreadsheet. I have searched the web for such a file in Excel
but come up blank - does anyone know where I might be able to download a
file?

Thanks in advance, Rob

 

[Otto Moehrbach]

Rob
Explain what "calculating redundancies for employees" means. Explain it
as you would for someone who has no knowledge of your business. Also, what
are "the UK statutory calculations"? HTH Otto

 

[Guest]

Just post a link to the DTI forms you need to file and we can help.

 

[Rob]

The UK rules are those shown below, quite complicated (well I think so).

1. STATUTORY REDUNDANCY PAY (Employment Rights Act 1996 s.162 unless stated)
a.. Statutory pay £290 per week
b.. (i) 1/2 week's pay for each year of service before 22nd birthday
c.. (ii) 1 week's pay for each year of service between 22nd and 41st
birthday.
d.. (iii) 1 1/2 week's pay for each year of service after 41st birthday
e.. (iv) Only 20 most recent years (maximum) to be taken into account.
Thanks, Rob

 

[Sandy Mann]

Rob,

After going round in circles all day I came up with this:

Cells A1:I1 I lebelled:
"Date of Birth","Start Date","Age at Start Date","Age Now","Age
Ranges","Years of Service","Effective Years of Service","Redundancy
Pay","Weekly Redundancy Pay" respectively.

In cells E2:E5 I entered the labels:
"Under 22","22-40","41 - Date"

Next I entered the fomulas:

C2: =IF(COUNT(A2:B2)=2,DATEDIF(A2,B2,"y"),0)
D2: =IF(COUNT(A2:B2)=2,DATEDIF(A2,TODAY(),"y"),0)
F2: =IF(C2<22,MIN(D2-C2,22-C2),0)
F3: =IF(AND(D2>21,C2<40),MIN(MIN(D2,40)-MAX(C2,22),18),0)
F4: =IF(D2>40,D2-MAX(C2,40),0)
F5: =SUM(F2:F4)
G2: =IF(COUNT(A2:B2)=2,MIN(F2,20-SUM(G3:G4)),0)
G3: =IF(COUNT(A2:B2)=2,IF(G4=20,0,MIN(MIN(F5,20)-F4,F3)),0)
G4: =IF(COUNT(A2:B2)=2,MIN(F4,20),0)
G5: =SUM(G2:G4)
H2: =G2*I2*0.5
H3: =G3*I2
H4: =G4*I2*1.5
H5: =SUM(H2:H4)

You can then if you want hide columns F & G

It has been only lightly tested so you should give it some exacting
peramiters that you know the correct answer to.

I suppose that you could combine all the formulas into a monster formula but
I would not want to maintain it then!

If you want me to send you a copy of the spreadsheet then reply privitely
correcting my e-mail address as it says in the signature.



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk