Working with the Excel Template

These instructions apply to the "Blank Ratification Template for Calculation and Upload.xlsx" v1.3. This template calculates the total additional amount owed to teachers upon ratification for the 21-22 school year, and creates a lump sum amount per teacher that can be uploaded into Atrieve. Following this method means that July and August monthly rates do not change (since the full adjustment is paid via the lump sum). The new grid rates should be updated for September 2022 in Atrieve.

Getting Data to put into Template

• In Atrieve, run the "Payroll Master Report" into Excel - instructions can be found at: Atrieve Payroll Master Report

• When the report opens, copy all of the data excluding the header row

  • quick method: click on cell A2, then on keyboard hold down "Ctrl" & "Shift", then on keyboard click on "End" key - this should highlight everything from cell A2 to the last row and last column

  • once all the data is highlighted, on keyboard press "Ctrl - C" to copy

• Go to the "Payroll Master from Atrieve" tab in the template

  • RIGHT CLICK on the yellow highlighted cell (cell S9 at the time of these instructions)

  • choose the clipboard 123 image under "Paste Options:"

  • 

Next Steps After Atrieve Data put into Template

1. Save the template!

2. Enter basic information needed for calculation and upload

   1. On the "Payroll Master from Atrieve" tab in the template, enter in some basic information at the top of the template including:

      1. Paycode you want to use for paying this

      2. Pay Date

      3. the Number of School Days you are paying between ratification date and end of June last teaching/working day

      4. Test Email Address if you plan on using this information to send emails via a Microsoft Word form letter method.

      5. 

3. Review the Grid Codes on the "Grid Table" tab

   1. If the Atrieve data has different grid codes than what is listed in the Grid Code Table, the "missing" codes will be identified in column L with the message, "missing in table"

      1. you can use the filter button found in cell L4 to display only the missing grid codes

   2. Enter missing Grid Code information starting on row 38

   3. IF ALL the grid codes are different from what is in the table, feel free to overwrite the existing grid codes found in column A to match your Division's grid coding.

4. Review the calculated amounts for each employee found in columns B to G

5. If some employees have different calculation values, for example, an employee is resigning before the last working day, you can enter employee exceptions in the "Exceptions Table" tab

   1. enter in their employee ID number on the table, and change/update whichever columns needed

Create Upload File for Atrieve

1. right click on the “CSV Import” tab
   

2. click on “Move or Copy…”
   

3. under “To book:”, choose (new book)
   

4. click on “Create a copy” box on the lower left corner of dialogue window
   

5. then click “OK”
   

6. this will copy this sheet to a new workbook
   

7. Click on “File”
   

8. Click on “Save a Copy”
   

9. Change the workbook type to “CSV (Comma delimited) (*.csv)
   

10. Save, and use this file to upload

You now have a file ready to be uploaded into Atrieve. Instructions on how to upload can be found at: Atrieve Import/Export Timesheet/Variable Deduction Batch

Sending Emails to Each Employee

If you want to send individualized emails to each employee, you could use a Microsoft Word mail merge document that links to this Ratification template.

Instructions on how to create an email using Microsoft Word's mail merge functionality can be found at: Creating a Word Email Template