Docstoc

Salary Increment Format - Excel

Document Sample
Salary Increment Format - Excel Powered By Docstoc
					Form 2003 Data Verification - Teacher Salary Grid                                              Please enter your School District number at Step 1 below



    Step 1: Enter your School District number here:


For each teacher salary grid used in the district:
    Step 2: Enter the category in the first column for each grid. First character of each category must be 4-6, total length 2 characters.
            Note: Enter categories in ascending order down the column (for example: 4, 41, 5-, 5, 5+, 6, 6+, 6M)
    Step 3: Enter the base salary on the pay grid for each combination of category and experience.

Grid 1                                                                        Experience                                                               Maximum
Category         0          1          2          3         4          5          6            7            8          9         10          11   12   Category 6
                                                                                                                                                         Salary
                                                                                                                                                                0




 - If you need to add or close a second salary grid, use the "Add Grid" or "Close Grid" buttons.
 - Do not enter Administrator salary grid.




7/22/2011                                                       e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                    1. Grid
Form 2003 Data Verification - Data Import                                                    Please return to tab 1. Grid and enter your School District number

To import data:
 If data is in a 97 character text file, according to the machine-readable record format and data field specifications (Form 2003, Appendix 1):
            [or a 99 character text file, where the 2 additional characters indicate the month when the teacher receives a salary increment.
             Record months as follows: October = 2, November = 3, . . . June = 10. See "Increments (Automatic)" worksheet for details.]
              Step 1: Run Open Text File Tool.
                                                    Open Text File
               Step 2: Look at the opened records to ensure they are in the correct 15 [or 16] columns (refer to column headings). If not, fix text file and re-run macro.
               Step 3: Copy and paste records to the appropriate columns of the "Records" worksheet in this workbook.
 If data is in a different format:
               Step 1: Import data to an Excel document in 15 columns as listed in "Records" worksheet in this workbook.
                          [or 16 columns including increment dates, where the 2 additional characters indicate the month when the teacher receives a salary increment.
                           Record months as follows: October = 2, November = 3, . . . June = 10. See "Increments (Automatic)" worksheet for details.]
               Step 2: Look at the opened records to ensure they are in the 15 [or 16] correct columns. If not, fix file to match correct columns.
               Step 3: Copy and paste records to the appropriate columns of the "Records" worksheet in this workbook.

To Check Data for Global Errors:
             Step 4:   Run Refresh Data Tool.
                                                    Refresh Data
             Step 5:   Check that the total number of records. If incorrect, ensure all appropriate fields are filled for each record.
             Step 6:   Check below for global data errors. Fix errors as required.                                              Total Records
                                                                                                                                       0
Global Data Errors
Column Heading                     Missing Fields in Records                                        Data Range Errors
Form Number            check
District Number        check
S.I.N.                 check
Surname                check
Position Code          check
Special Case Code      check
Categories             check
Increment              check
% FTE                  check
Base Salary            check
Admin. Allowance       check
Isolation Allowance    check
Other Allowance        check
Total Salary           check
             No global errors in data.
             Step 7:   Go to Verification and Summary worksheets.




   7/22/2011                                                       e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                              2. Import
Form SD S.I.N.   Surname   Given Names   Position Special Category Increment % FTE Base Sal   Admin     Isol   Other Total Sal Incr. Month Error




7/22/2011                                                 Page 3/11                            e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls, 3. Records
Form 2003 Data Verification                      Please return to tab 1. Grid and enter your School District number

Step 1: Scan the Error column on the "Records" worksheet for errors. Fix data as necessary.                    Errors Count * Code
  Note: If there is no data in the Error column on the "Records" worksheet, Refresh Data.                Missing S.I.N.        0    1
  Note: Error Codes (1 through 8) are defined on this worksheet.                                          S.I.N. invalid       0    2
Step 2: Press the "Verify Data" Button to list records with errors.
                                                                       Verify Data            Cat. must start with 2-6         0    3
  Note: This tool will take some time and will manipulate data on your screen.                   Category not on grid          0    4
Step 3: Fix any records with Errors on the "Records" worksheet.                        Cat./Incr. must occur together          0    5
Step 4: If necessary, check the grid to ensure consistent with district records.                Increment not on grid          0    6
Step 5: Re-run the "Verify Data" Tool until no more unexplained errors occur.      Base Salary does not match Grid             0    7
                                                                                 Total Salary <> Base + Allowances             0    8
 Number of Records                                                                                        Total Errors         0
    On Grid(s): 0                       District Number: 0                                        * Error Codes (1 through 8) match the
 Not on Grid(s): 0                     Number of Grids: 0                                    Error Column on the "Records" worksheet.
          Total: 0                 Tolerance from Grid: $4
Note: the Verify Data Tool must be run again every time Records data is changed to update the Data Verification Report.
                                                                                                                Base
 Error Code * S.I.N.            Surname                   Given Names              Category Increment Salary             FTE




  7/22/2011                                                    Page 4 e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls, 4. Verification
Form 2003 Summary Report A (as at Sept. 30, 2010)                                Please return to tab 1. Grid and enter your School District number
                                   Number of Educators by Position Code, Special Case Code and Grid Position

                                          Educators                                    Educators                                            Number of Educators
                                       by Position Code                           by Special Case Code                                          on the Grid *
                                  Job      Number       FTE                   Special   Number      FTE                     Educators on Grid:                                0
                                Position     of          of                    Case        of        of                     Educators Not on Grid:                            0
                                 Code      Records Educators                   Code     Records Educators                   Total Number of Educators:                        0
                                    0              0     0.0000                 A              0     0.0000                 - Data is taken from the Grid Summary Report below.
                                    1              0     0.0000                 B              0     0.0000                 * Note: the Summarize Data Tool must be run again
                                    2              0     0.0000                 C              0     0.0000                    every time data on the "Records" worksheet is changed
                                    3              0     0.0000                 D              0     0.0000                    to update the Grid Summary Report.
                                    4              0     0.0000                 F              0     0.0000
                                    5              0     0.0000                 T              0     0.0000
                                    6              0     0.0000                 X              0     0.0000
                                    7              0     0.0000                none            0     0.0000
                                    8              0     0.0000                Total           0     0.0000
                                    9              0     0.0000
                                   10              0     0.0000
                                   11              0     0.0000
                                 Total             0     0.0000

Grid Summary Report *
To update Grid Summary Report:
   Step 1: Run Summarize Data Tool.
                                        Summarize Data
    * Note: the Summarize Data Tool must be run again every time data on the "Records" worksheet is changed to update the Grid Summary Report.

                              FTE Educators by Increment
                                 0.0000    0.0000      0.0000

FTE Educators                   Increment
                   Count of S.I.N.
 by Category       Category          0      (blank)   Grand Total
        0.0000     (blank)           1                    1
             0     Grand Total       1                    1




    7/22/2011                                                           e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                         5. Summary A
Form 2003 Summary Report B (as at Sept. 30, 2010)                                Please return to tab 1. Grid and enter your School District number
                                    Summary of Salaries and Allowances by Position and Special Case Codes

 Used in                                                                  Job    Headcount   FTE                Base            Admin       Isolation        Other          Total         Average
 Average Educator Position Name                                         Position Educators Educators            Salary        Allowance    Allowance      Allowance         Salary         Salary
Ed. Salary                                                               Code                                                                                                             per FTE
           Educators without Special Case Codes **

           Administrative Officers
                              Dir. of Instruction                          1            0           0.0000               $0           $0            $0             $0                $0         $0
                              Principal                                    8            0           0.0000                0            0             0              0                 0          0
    *                         Vice Principal *                             9            0           0.0000                0            0             0              0                 0          0
           Board Office based Teachers--Instruction Support
                              Supervisor of Instruction                    2            0           0.0000                0            0              0             0                 0          0
                              Teacher Consultant                           3            0           0.0000                0            0              0             0                 0          0
                              Co-ordinator                                 4            0           0.0000                0            0              0             0                 0          0
    *                         Helping Teacher                              5            0           0.0000                0            0              0             0                 0          0
    *                         Other Instructional Support                  6            0           0.0000                0            0              0             0                 0          0

           Board Office based Teachers--Testing and Assessment
                            Professional Staff-Teachers                    7            0           0.0000                0            0              0             0                 0          0

           School based Teachers
    *                      Department Head                                 10           0           0.0000                0            0              0             0                 0          0
    *                      Regular Teachers receiving Admin                11           0           0.0000                0            0              0             0                 0          0
                            allowance other than Dept Head
    *                      Regular Teachers                                0            0           0.0000                0            0              0             0                 0          0

           TOTAL Educators with no Special Case Codes **                                0           0.0000               $0           $0            $0             $0                $0
           Special Category Educators
                            Provincial Resource Program                    A            0           0.0000               $0           $0            $0             $0                $0         $0
                            Youth Custody/Res. Attendance Centre           B            0           0.0000                0            0             0              0                 0          0
    *                       Distributed Learning Educators                 C            0           0.0000                0            0             0              0                 0          0
                            School for the Deaf                            D            0           0.0000                0            0             0              0                 0          0
                            Francophone Education Authority                F            0           0.0000                0            0             0              0                 0          0
    *                       Continuing Education                           T            0           0.0000                0            0             0              0                 0          0
    *                       Exchange Teachers                              X            0           0.0000                0            0             0              0                 0          0
           TOTAL Educators with Special Case Codes                                      0           0.0000               $0           $0            $0             $0                $0

           TOTAL Educators                                                              0           0.0000               $0           $0            $0             $0                $0


    *      * The FTE assignment of Vice Principals at the category 6 maximum salary is used in calculating the Average Salary for the Supplement for Salary Differential.
           ** Educators listed by position code do not include special category educators.

- After data are verified and summaries are checked for reasonableness, export data for transfer to Data Management by using the Export Data Tool (select Tools, Export Data).




   7/22/2011                                                              e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                               6. Summary B
                                          Form 2003 Data Verification - Data Export


            After data is verified and summaries are checked for reasonableness, export data for transfer to Data Management.

            To export data:
                    Step 1: First scan your records data for the following potential hotspots:
                            - Quotation marks and commas in names can cause problems for data export.
                            - Blank cells should be empty - they should not have a space in them. In particular, beware of allowances.

                    Step 2: Run the Export Data Tool.      Export Data

                    Step 3: Name the text file in the following format: 2003-### where ### equals your district number.

                    Step 4: Send the text file to the Ministry of Education by email or by FTP transfer.
                            - We suggest that you open the file in a text editor application (such as "Notepad").
                            - The file must be accompanied by a printout which follows the format of the Form 2003.
                              You may use a printout of the "Records" worksheet to fulfill this requirement.

                            Mailing Address: Ministry of Education
                                             Data Management Unit
                                             PO Box 9170
                                             Stn Prov Govt
                                             Victoria, BC V8W 9H7
                                             Attention: Kathy Cordner
                                             Fax: (250) 356-0277

                            Email              Email the text file as an attachment to <Kathy.Cordner@gov.bc.ca>




7/22/2011                                           e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                             7. Export
      Data Collection for Teacher Salary Increments after September 30, 2010                             Please return to tab 1. Grid and enter your School District number
                                                                           Manually Entered Increments

      This data collection is intended to account for incremental teacher salary costs which districts recognize after September 30, 2010.
      This information will be used to adjust the Supplement for Salary Differential in the recalculation of district grants.
      This data must be separately submitted to the Funding Dept. See Step 3 below for contact information.

      * Only include teachers which are eligible for the Supplement for Salary Differential.
        Eligible position codes are types 0, 5, 6, 10 and 11.       Exclude teachers with special case codes of types A, B, D and F.

      For each month from October 2010 to June 2011:
       Step 1: Input the number of teachers (headcount and FTE) receiving an incremental salary in the given month.
       Step 2: Input the annual salary cost of the increments paid to all of the teachers receiving an increment for the given month.
                The annual salary cost is the difference on the teacher salary grid between the old increment and the new increment.
                By applying a conversion rate, this annual salary cost is then converted to the actual salary cost which applies to the 2010/11 school year.

                For example:
                  (1) If one FTE teacher is raised one increment on the teacher salary grid from $48,000 to $50,000 in January (at an annual salary cost of $2,000),
                   - enter 1 FTE teacher in January, and
                   - enter $2,000 as the annual salary cost of increments in January.
                   The actual salary cost applying to the 2010/11 school year is $2,000 * 60% = $1,200.
                 (2) If five more FTE teachers are raised one increment on the salary grid from $48,000 to $50,000 in February (at an annual salary cost of $2,000),
                   - enter 5 FTE teachers in February, and
                   - enter $2,000 * 5 = 10,000 as the annual salary cost of increments in February.
                   The actual salary cost applying to the 2010/11 school year is $10,000 * 50% = $5,000.

                                                                      2010                                                            2011                                           2010/11
                                                        October     November      December        January      February       March          April         May           June         Total
      Number of Teachers receiving      Headcount
      an increment each month:               FTE
      Annual Salary Cost of Increments:
      Conversion Rate:                                        90%          80%           70%           60%           50%           40%           30%           20%           10%
      Actual Salary Cost
      (applying to the 2005/06 school year):

                                                                                                                                          Total 2010/11 Salary Increment Cost
                                                                                               Total # of FTE Teachers Eligible for Supplement for Salary Differential in district
                                                                                                                                   Average Increase in Salary from Increments

       Step 3: Print the "Data Collection for Teacher Salary Increments after September 30" form.
               Please submit this worksheet by fax to the Funding Dept, Ministry of Education
               by October 15, 2010, ATTN: Michael Lebrun, Funding Analyst at (250) 387-1451.
               or by e-mail at         Michael.Lebrun@gov.bc.ca
               Note: Do not submit the "Increment List" worksheet to the Ministry of Education. This sheet is background information on the increments.

      Districts should either enter increments after Sept. 30, 2010 manually on this sheet or automatically on the next sheet.




7/22/2011                                                                  e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                             8. Increments (Manual)
 Data Collection for Teacher Salary Increments after September 30, 2010                                Please return to tab 1. Grid and enter your School District number
                                                                    Automatically Generated Increments

 This data collection is intended to account for incremental teacher salary costs which districts recognize after September 30, 2010.
 This information will be used to adjust the Supplement for Salary Differential in the recalculation of district grants.


 * Only include teachers which are eligible for the Supplement for Salary Differential.
   Eligible position codes are types 0, 5, 6, 10 and 11.          Exclude teachers with special case codes of types A, B, D and F.

      Step 1: On the "Records" worksheet in this document, input the month for any teacher receiving an incremental salary after September.
              Record increment months as follows: October = 2, November = 3, . . . June = 10
              Note: this information can be imported to the "Records" worksheet along with the original records if it is part of the original extract file.
      Step 2: View the increment summary table below.
              - The table records the number of FTE teachers marked as receiving an incremental salary in the given month.
              - It also automatically calculates the annual salary cost of the increments paid to all of the teachers receiving an increment for the given month.

                 The annual salary cost is the difference on the teacher salary grid between the old increment and the new increment.
                 By applying a conversion rate, this annual salary cost is then converted to the actual salary cost which applies to the 2010/11 school year.
                 For example, if one FTE teacher is raised one increment on the salary grid from $48,000 to $50,000 in January (at an annual salary cost of $2,000),
                 the actual salary cost applying to the 2010/11 school year would be $2,000 * 60% = $1,200.

                                                                    2010                                                             2011                                           2010/11
                                                     October      November      December        January      February        March          April         May           June         Total
 Number of Teachers receiving
 an increment each month:                    FTE         0.0000        0.0000       0.0000         0.0000        0.0000        0.0000        0.0000        0.0000        0.0000        0.0000
 Annual Salary Cost of Increments:                           $0            $0           $0             $0            $0            $0            $0            $0            $0            $0
 Conversion Rate:                                          90%           80%          70%            60%           50%           40%           30%           20%           10%
 Actual Salary Cost
 (applying to the 2010/11 school year):                      $0            $0            $0             $0            $0             $0             $0          $0             $0             $0

                                                                                                                                         Total 2010/11 Salary Increment Cost                  $0
                                                                                              Total # of FTE Teachers Eligible for Supplement for Salary Differential in district
                                                                                                                                  Average Increase in Salary from Increments

      Step 3: Note that only the salary increment costs for teachers with Code 0 will be included for the Supplement for Salary Differential.
          Increment Eligibility Codes - see the Increment List worksheet for a list of all teachers marked with an increment and their increment eligibility code.
            0 - teacher is marked as receiving an increment after September, and is eligible for the Supplement for Salary Differential.
            1 - the teacher is already at the maximum increment for their category on the grid.                                                 Increment costs
            2 - the teacher's category and/or increment are not on the grid.                                                                    will not be generated
            3 - the teacher's position and/or special case code is not eligible for the Supplement for Salary Differential.                     for teachers
            4 - the teacher's base salary does not match grid.                                                                                  with codes 1-4.

      Step 4: Print the "Data Collection for Teacher Salary Increments after September 30" form.
              Please submit this worksheet by fax to the Funding Dept, Ministry of Education
              by October 15, 2010, ATTN: Michael Lebrun, Funding Analyst at (250) 387-1451.
              or by e-mail at      Michael.Lebrun@gov.bc.ca
              Note: Do not submit the "Increment List" worksheet to the Ministry of Education. This sheet is background information on the increments.

 Districts should either enter salary increments using the Increments column on the "Records" worksheet or should use the previous Increments (Manual) sheet.


7/22/2011                                                               e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                                      8. Increments (Automatic)
List of Teachers with Salary Increments after September 30, 2010                               Please return to tab 1. Grid and enter your School District number

This worksheet is for districts which have marked teachers eligible for salary increments on the "Records" sheet, to be used with the Increments (Automatic) sheet.
Step 1: Run the Increment List Tool to list teachers marked with salary increments.                                                   $0 Total Increment Cost
                                                                                             Increment List Tool
  Note: this tool will take some time and will manipulate data on your screen.                                                          0 Headcount # of Teachers Marked
Step 2: Only the salary increment costs for teachers with Code 0 will be included for the Suppl. for Salary Diff'l.               0.0000 FTE               as Receiving Increment
       - If necessary, adjust the Increment month on the "Records" worksheet and re-run the Increment List Tool.                        0 Headcount # of Teachers Qualifying for
                                                                                                                                  0.0000 FTE               Incr. with Suppl. For Sal.
            Increment Eligibility Codes                     Code     Count
                                                                0          0 - teacher is marked as receiving an increment after Sept., and is eligible for the Suppl. for Sal.
                                                                1          0 - the teacher is already at the maximum increment for their category on the grid.
                                                                2          0 - the teacher's category and/or increment are not on the grid.
                                                                3          0 - the teacher's position and/or special case code is not eligible for the Supplement for Sal Diff'l.
                                                                4          0 - the teacher's base salary does not match grid.

Note: the Increment List Tool must be run again every time Records data is changed to update this report.

                                                                                                                  Base        Salary          Incr.       Incr.     FTE     Incr. Elig.
S.I.N.         Surname                   Given Names              Position Special Category Increment             Salary     with Incr.       Cost       Month       %        Code




   7/22/2011                                                                         Page 10/11                   e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls, 9. Increment List
                                               Average Educator Salary Determination
                                  Please return to tab 1. Grid and enter your School District number

                                         Eligible                      Total               Average Salary             Average          Average Salary
                                        Educators                     Eligible                (before                Increment           (including
                                  Headcount       FTE                 Salary                Increments)               Increase          Increments)
             Eligible Teachers:
                             0                0        0.0000                    $0
                             5                0        0.0000                     0
          Position Code      6                0        0.0000                     0
                       or   10                0        0.0000                     0
     Special Case Code      11                0        0.0000                     0
                             C                0        0.0000                     0
                             T                0        0.0000                     0
                             X                0        0.0000                     0
        Total Eligible Teachers               0        0.0000                    $0
        Eligible Administrators               0        0.0000                    $0
*      Total Eligible Educators               0        0.0000                    $0                         $0                                #VALUE!

    Base Salary and Isolation Allowance for Eligible teachers are used in calculating the Average Educator Salary.
    Note: Teachers without a category are not included in the Average Educator Salary.
          Therefore, there may be some differences between this report and Summary B.
    Vice Principals at category 6 maximum salary are used in calculating the average funded salary for the Average Educator Salary.
*   The category 6 maximum salary is taken from the Grid worksheet in this spreadsheet.                                    $0

    - Please note that the Average Educator Salary is only accurate if data for all eligible educators has been entered properly.
      This includes continuing education teachers.




     7/22/2011                                        e8b8ee5a-0f4d-41c5-95cf-2406a1f97375.xls                                10. Average Ed Salary

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:530
posted:7/22/2011
language:English
pages:11
Description: Salary Increment Format document sample