Obtaining Counts for the SS300 Report using Payroll History Spreadsheet - Sartox
Obtaining Counts for the SS300 Report using your Payroll History Spreadsheet - Sartox
|
Select the year that you need to obtain information for.
Select “List of Object Codes” from the drop down list.
|
|
|
List all of the object codes that apply to the categories needed for the SS300 Report.
|
|
|
Refresh the data.
|
|
|
Click on the Payroll History Pivot Table tab. Then click on the Full Account with Names button at the top – this will give us the best starting point for the report.
|
|
|
Drag the full acct code to the top
|
|
|
Drag all of the data files (such the Payline Gross, Net Pay, total hours, etc) off the table – LEAVING ONLY THE NET GROSS
|
|
|
Your pivot table should only contain the Full Name and the Net Gross columns.
|
|
|
If you have a Grand Total column on the table, right Click somewhere in the table and select Table Options
Remove the check mark on the Grand totals for rows. Click OK
|
|
|
Click on the Object in the top part of the pivot table and drag down ON TOP OF the Net Gross heading.
|
|
|
This will position your object codes across the top of the table.
|
|
|
If the table is too wide and you want to work with only one section at a time (for instance teachers - object code 121), then filter the object code for 121 and 181 to get the supplements.
|
|
|
Your table would look like this…
|
|
|
Click on the cell to the right of the last object code heading on the table and type in the heading “Teacher Suppl?”
|
|
|
On the row below the Teacher Suppl, you will use a formula to determine if the person was paid from BOTH 121 and 181.
In our case, the 121 amount is in column B and the 181 amount is in column C
|
|
|
The line that we are entering the formula on is D34 (row 34 in this case is the first person in the list). This formula indicates that if B34 is not equal to 0 AND C34 is not equal to 0, then put “Yes” in the cell – otherwise put “No”.
You should have either a “Yes” or “No” in the cell. Click on the cell with the new formula (D34 in our case). Click on the lower right corner of the cell with the formula and drag it down the entire table. This will copy the formula to the other lines.
|
=IF(AND(B34<>0,C34<>0),"Yes","No")
|
|
To count the number of people paid from 121 and the number of people with supplements, we will need 2 additional formulas.
Click on cell F30 and type “# of Supplements”. Click on cell H30 and type “# of People”
|
|
|
Click on Cell F31 and we will add a temporary formula to do a calculation for us
=COUNTIF(D:D,"Yes")
|
=COUNTIF(E:E,"Yes")
This formula will count any entries in Column E that have “Yes” in them. This will count the number of 121 teachers that received supplements.
|
|
Click on Cell H31 and add a temporary formula to do a calculation on for the total number of people paid from 121.
|
=COUNTIF(B34:B65000,">0")
This formula will count any entries in column B beginning with row 34 that are greater than 0 – thus counting the number of people paid out of 121.
|
|
Note – if you need to now break that out by Fund – filter on Fund. For example, we will filter on Fund 2 in the top section of the table to obtain counts for Local Funds (Note – we are still looking at Object code 121). Note that the numbers will adjust
|
|
|
To obtain totals for a different section, adjust the Filter for the object code. For example, to see Principals, Filter the Object Code for 114 and 181.
|
|
|
Note that again, the totals will adjust. Be sure to check your filter for FUND and select All. If you need to break down these numbers by type of School (High, Middle, Elementary) then filter by the Location filter at the top and select all locations that are Elementary etc. Note – if you have Office 2003, you will need to drag the location to the bottom portion of the table in order to filter for multiple locations.
|
|
Related Articles
Obtaining Counts for the SS300 Report using Payroll History Spreadsheet - LINQ
Obtaining Counts for the SS300 Report using your Payroll History Spreadsheet - LINQ Select the year that you need to obtain information for. Select “List of Object Codes” from the drop down list. List all of the object codes that apply to the ...Position Counts for Salaried Positions – Payroll Assignments
Position Counts for Salaried Positions – Payroll Assignments Spreadsheet Select “Type of Pay – S” on the Settings tab. Using this setting will bring in all employees that are coded as Salaried. Note that this will also bring in supplemental and ...Using the Sartox Payroll Verification Spreadsheet
Using the CCI Payroll Verification Spreadsheet Open the Payroll Verification Spreadsheet Enter the date of the PREVIOUS payroll run that you would like to compare the current QDE with. Click on the Refresh Data button For each person listed, you will ...Using the CCI Spreadsheets to determine who has worked more than 30 hours
Using the CCI Spreadsheets to determine who has worked more than 30 hours This example will show you how to format your payroll history pivot table to highlight hourly employees that have greater than 30 hours per week. Remember – you can’t hurt ...Position Counts for Salaried Positions – Payroll Assignments Spreadsheet
Position Counts for Salaried Positions – Payroll Assignments Spreadsheet Select “Type of Pay – S” on the Settings tab. Using this setting will bring in all employees that are coded as Salaried. Note that this will also bring in supplemental and other ...