GUIDANCE NOTES
Please follow the steps below to enable this spreadsheet to accurately calculate your teachers pay protection
The spreadsheet will only calculate for one teacher at a time. For every different teacher with management point safeguarding, a new spreadsheet must be used 1 Click on the 'Calculator' tab. This will take you to the input spreadsheet. Enter the teacher's name and FTE in the appropriate boxes. Enter the relevant data in each cell using the drop down menu, utilising the notes below as a guide. Only enter data in cells colured light green.
2 3
- See tables below for help with Payscale, Mgt Points, R&R Incentives & TLR codes Enter the Payscale and pay point. Data will auto-fill for remaining years, with rising increments for each year. Enter the number of Management Points and / or Recruitment & Retention Incentive Points. Data will auto-fill for remaining years. If Points are time limited, remove in the relevant year If no Points given ,enter a 0
Payscale Sept 2005
Payscale Sept 2006
Payscale Sept 2007
Payscale Sept 2008
Payscale Mgt Points R & R Incentives
Salary @ Dec 05
M1 MA1 0
M2 MA1 0
M3 MA1 0
M4 MA1 0
The payscale can be increased at any point. However if manually increased, all subsequent payscale cells must also be manually filled. (eg M6 to UPS 1)
TLR Grade
If a TLR is given in in the first year (effective Jan 2006), it must be entered in the Sept 2005 data input cell.
TLR2 A
TLR2 A
TLR2 A
TLR2 A
Management Points and R&R Incentive Points can be added or removed at any time.
Enter the number of TLR points given. Data will auto-fill for remaining years. If no TLR's given, enter a 0
TLR points can be added or increased at any time. *TLR's cannot be reduced.
* Due to the complexities of reducing TLR's once given, the spreadsheet cannot calculate for their reduction. Please speak to your HR consultant for further guidance if applicable. 4 You will notice that once the first years data has been input, the remaining years populate automatically. An incremental rise has been built into each year where appropriate. DO NOT LEAVE ANY BLANKS. If no data is required, enter a 0 (zero). If any amendments to the pre-populated data are necessary this can be done by overtyping the formula contained in the cell. However with ammendments to the payscale, all subsequent years must also be overtyped. 5 The calculator will automatically show the salary details for the staff member over the next 3 years. It will also highlight any safeguarded amount, as well as any rise in pay due from incremental progression and inflation. Based on incremental pay rises the calculator will work out the value of any future protected pay (which can be paid to the staff member as an allowance). As a visual check, the calculator will also show if the staff members pay is to be protected, and switch between red and green as appropriate. Finally for January 2009, when all Mgt point protection will cease, it will show the salary for that member of staff Please note that the final Salary figure quoted, is that received by the staff member, not the cost to the school. As stated above, the calculator can only be used for one teacher at a time. Once data has been input for a particular staff member, either print the page off, or save the spreadsheet under that teachers name, and reopen a new spreadsheet for each teacher.
6
7 8 9
Page 1
of
9
8/13/2008
10
If at any point the equations in the input cells are overwritten and need to be reinstated, ie for inputting the next teachers data, they can be reproduced by clicking on the "RESET TEACHER DATA" button. WARNING: This will automatically reset to a blank template, regardless of any 'saves' that have been made.
11
Once the data has been entered for all staff members, and printouts have been kept, the information can be input into the teaching staff salary calculator, The printouts will enable the user to identify when the management points will cease, when TLR values should be added and also what the value of the 'protected sum' should be (if applicable).
Inflation: inflation for Pay and TLR values have been set at 2.5% for each year. this can be amended, by clicking on the appropriate tab and overtyping the percentages (highlighted yellow)
Page 2
of
9
8/13/2008
Payscale codes Table: when entering staff pay scales, please use the following codes Mainscale - Point 1 Mainscale - Point 2 Mainscale - Point 3 Mainscale - Point 4 Mainscale - Point 5 Mainscale - Point 6 M1 M2 M3 M4 M5 M6
Upper Pay Scale 1 Upper Pay Scale 2 Upper Pay Scale 3
U1 U2 U3
Management Allowance Code Table: when entering staff Management Allowances, please use the following codes 1 Management Allowance Point 2 Management Allowance Points 3 Management Allowance Points 4 Management Allowance Points 5 Management Allowance Points MA1 MA2 MA3 MA4 MA5
Recruitment & Retention Incentive Code Table: when entering staff R&R Incentives, please use the following codes 1 Recruitment & Retention Point 2 Recruitment & Retention Points 3 Recruitment & Retention Points 4 Recruitment & Retention Points 5 Recruitment & Retention Points R&R1 R&R2 R&R3 R&R4 R&R5
Assumed values for R&R Incentives have been set in this calculator, however they can be ammended by changing the values in column 'D' of the "Allowances" tab. Note: R&R Incentives are not set against a national scale, and can be agreed locally.
TLR Code Table: when entering staff TLR's, please use the following codes Minimum TLR 2 value Mid-Point TLR 2 value Maximum TLR 2 value TLR2 A TLR2 B TLR2 C Minimum TLR 1 value Lower Range TLR 1 value Higher Range TLR1 value Maximum TLR 1 value TLR1 A TLR1 B TLR1 C TLR1 D
Assumed values for TLR's have been set in this calculator, however they can be ammended by changing the values in column 'B' of the "TLR Values" tab. Note: TLR values are not set against a national scale, and can be agreed locally. The minimum TLR2 value is £2,250, rising incrementally by at least £1,500 to a maximum of £5,500) The minimum TLR1 value is £6,500, rising incrementally by at least £1,500 to a maximum of £11,000)
Page 3
of
9
8/13/2008
Teacher Name: Teacher Name FTE: 1.00
January
Payscale Sept 2005 Payscale Sept 2006 Payscale Sept 2007 Payscale Sept 2008
September 2006
22,137 1,638 -
September 2007
24,436 1,638 -
September 2008
26,902 1,638 -
January 2009
26,902 -
2006
20,082 1,638 21,720
Payscale Mgt Points R & R Incentives
Salary @ Dec 05
M1 MA1 0
M2 MA1 0
M3 MA1 0
M4 MA1 0
TLR Grade
TLR2 A
TLR2 A
TLR2 A
TLR2 A
-
2,250 612 -
2,306 612 2,055 -
2,364 612 4,354 -
2,423 612 6,820
2,423 -
Mgt Point less starting TLR value Cummulative Increase in basic pay from incremental progression starting Sept 06 Safeguard protection value Protected? Final Salary
Unprotected £ 22,332
Unprotected £ 24,443
Unprotected £ 26,800
Unprotected £ 29,325
Unprotected £ 29,325
SEPT 2005 (£) MAIN PAY SCALE SALARY POINT 1 2 3 4 5 6 UPPER PAY SCALE SALARY POINT 1 2 3 CODE M1 M2 M3 M4 M5 M6 0 20,082 21,597 23,259 24,981 26,877 28,923
SEPT 2006 (£)* %age rise 2.50% 20,584 22,137 23,840 25,606 27,549 29,646
U1 U2 U3
U1 U2 U3
U1 U2 U3
31,260 32,385 33,549
32,042 33,195 34,388
ADVANCED SKILLS TEACHERS SALARY POINT 1 AST1 2 AST2 3 AST3 4 AST4 5 AST5 6 AST6 7 AST7 8 AST8 9 AST9 10 AST10 11 AST11 12 AST12 13 AST13 14 AST14 15 AST15 16 AST16 17 AST17 18 AST18 19 AST19 20 AST20 21 AST21 22 AST22 23 AST23 24 AST24 25 AST25 26 AST26 27 AST27 Converter for Annual Incremental Progression M1 M2 M3 M4 M2 M3 M4 M5 M3 M4 M5 M6 M4 M5 M6 M6 M5 M6 M6 M6 M6 M6 M6 M6 U1 U1 U1 U1 U2 U2 U2 U2 U3 U3 U3 U3
32,412 32,976 33,540 34,095 34,659 35,226 35,793 36,351 36,912 37,470 38,037 38,787 39,537 40,284 41,034 41,784 42,534 43,287 44,037 44,787 45,537 46,470 47,409 48,345 49,284 50,217 51,159
33,222 33,800 34,379 34,947 35,525 36,107 36,688 37,260 37,835 38,407 38,988 39,757 40,525 41,291 42,060 42,829 43,597 44,369 45,138 45,907 46,675 47,632 48,594 49,554 50,516 51,472 52,438
AST1 AST2 AST3 AST4 AST5 AST6 AST7 AST8 AST9 AST10 AST11 AST12 AST13 AST14 AST15 AST16 AST17 AST18 AST19 AST20 AST21 AST22 AST23 AST24 AST25 AST26 AST27
AST1 AST2 AST3 AST4 AST5 AST6 AST7 AST8 AST9 AST10 AST11 AST12 AST13 AST14 AST15 AST16 AST17 AST18 AST19 AST20 AST21 AST22 AST23 AST24 AST25 AST26 AST27
AST1 AST2 AST3 AST4 AST5 AST6 AST7 AST8 AST9 AST10 AST11 AST12 AST13 AST14 AST15 AST16 AST17 AST18 AST19 AST20 AST21 AST22 AST23 AST24 AST25 AST26 AST27
AST1 AST2 AST3 AST4 AST5 AST6 AST7 AST8 AST9 AST10 AST11 AST12 AST13 AST14 AST15 AST16 AST17 AST18 AST19 AST20 AST21 AST22 AST23 AST24 AST25 AST26 AST27
SEPT 2007 (£)* %age rise 2.50% 21,099 22,690 24,436 26,246 28,238 30,387
SEPT 2008 (£)* %age rise 2.50% change to suit inflation (could be 0) 21,626 23,258 note: Coloured cells can be altered 25,047 26,902 28,944 31,147
32,843 34,024 35,247
33,664 34,875 36,129
34,053 34,645 35,238 35,821 36,414 37,009 37,605 38,191 38,781 39,367 39,963 40,751 41,539 42,323 43,111 43,899 44,687 45,478 46,266 47,054 47,842 48,823 49,809 50,792 51,779 52,759 53,749
34,904 35,512 36,119 36,717 37,324 37,935 38,545 39,146 39,750 40,351 40,962 41,769 42,577 43,381 44,189 44,997 45,804 46,615 47,423 48,231 49,038 50,043 51,054 52,062 53,073 54,078 55,093
ADDITIONAL ALLOWANCES MANAGEMENT 0 MANAGEMENT 1 MANAGEMENT 2 MANAGEMENT 3 MANAGEMENT 4 MANAGEMENT 5 0 MA1 MA2 MA3 MA4 MA5 0 1,638 3,312 5,688 7,833 10,572
Recruitment & Retention 0 Recruitment & Retention 1 Recruitment & Retention 2 Recruitment & Retention 3 Recruitment & Retention 4 Recruitment & Retention 5
0 R&R1 R&R2 R&R3 R&R4 R&R5
0 1,002 1,971 2,985 4,158 5,415
note: Coloured cells can be altered
05-06 TLR's 0 TLR2 A TLR2 B TLR2 C 0 2250 3750 5500
06-07 07-08 08-09 %age rise %age rise %age rise 2.50% 2.50% 2.50% change to suit inflation (could be 0) 2,306 2,364 2,423 3,844 3,940 4,038 5,638 5,778 5,923
TLR1 A 6500 TLR1 B 8000 TLR1 C 9500 TLR1 D 11000
6,663 8,200 9,738 11,275
6,829 8,405 9,981 11,557
7,000 8,615 10,230 11,846
note: Coloured cells can be altered