Classic Student Payables Review Report

Classic Student Payables Review Report

Access: Kindo Payable Administrators

This reporting option allows you to use excel to create a pivot table for ease of reporting to boards and heads of faculty.
The report will clearly show you the percentage paid for each payable item, and is broken down based on your general ledger codes, so different areas within the school are easily identified.

You can edit the data initially, before creating the pivot table if you just want to report on one particular area.
You can also select the date range you would like the report to be for, whether it is monthly, quarterly or for your final end of year report.

Those that are familiar with Pivot tables can also filter the data once the pivot table is created.
You can then create graphs from the pivot tables for a visual interpretation of your data, great for reporting to the board. 



This is Phase one of this reporting function. The data can be accessed, but you will need to create the pivot table manually, using the instructions below.
Phase two will see the pivot tables being created automatically for you.
Phase three will see additional graphs created for you also.

Accessing the Data 

 
1. Login to the shop site https://shop.tgcl.co.nz 
2. From the main Payable Admin Page, select the Reporting option. 
3. Select Kindo Payables, and Classic Student Payables Review options.



4. Enter in your date range for the required reporting period.



5. Click on Run Report. This may take a bit of time to run, depending on the amount of data. 

6. Select Results to view your data as a CSV file.  Your CSV file will have the following columns: 
  1. Remarks 1 – This is your GL code 
  2. Payable Name 
  3. Student payable count – number of students the payable has been applied to 
  4. Applied total dollars 
  5. Net paid total dollars 
  6. Percent paid 
  7. Date first applied 
  8. Date first payment 
  9. Total receipts excl refunds 
  10. Total refunds 
  11. Total current paid 
  12. Proto payable ID 

Creating the Pivot Table

You can easily create a Pivot Table which can be used for board reporting.
It is a great way to present the data in an easy to view format. 

1. Highlight all the cells containing data, including row 1.

2. Select Insert, then Pivot Table, and From Table/Range.



3. This will open up the following pop-up box:



The Table/Range should automatically populate from your highlight spreadsheet.

Ensure New Worksheet is selected.

Click OK.

4. A new tab will open in your workbook (Sheet 1), with the Pivot Table Fields selection box.



5. In the Pivot Table Fields box, you need to click on the field name, and drag it into the appropriate area, as seen in the picture below.
  1. Remarks1 field into Row area
  2. Payable name into Row area
  1. Sum of applied total dollars into Values area
  2. Sum of net paid total dollars into Values area
 
 
As you click and drag the fields into the area boxes you will see the pivot table being created in Sheet 1.

If you click outside of the Pivot table at any point the Pivot Table Fields box will disappear.  You just need to click in the pivot table for the fields box to appear again.

6. Ensure you are in the PivotTable Analyze tab and then select Fields, Items & Sets.



7. Select Calculated Field option.


 
The reason we calculate the % this way and not from the percent paid field, is that it’s more accurate.
The field option doesn’t keep the bolded summary line separate, so includes this as part of the calculation.
 
8. The following pop-up box will appear:



Click on net paid total dollars and then Insert Field.
 
9. This field will then get entered into the Formula bar, and the cursor will also be in this field.  Click on the / on your keyboard.
 
10. Click on applied total dollars and then Insert Field.

11. You should end up with your formula field looking like the image below.  Click OK.



12. This will then add a percentage column to your pivot table.



You can edit the names of the columns and change the columns from general to percentage and dollar values as required.



13. To change the cells to display as a percentage or dollar value, you can simply highlight the column, then select Home, and change General to either Currency / Accounting or Percentage.



You need to save the document as an Excel file, to retain the format of the workbook and pivot table.

Any questions? 
Contact our Customer Support on 0508 4 KINDO (0508 454 636) 
Open 8am - 4pm weekdays.

    • Related Articles

    • SMS - Kindo Student Links Report

      Access: Payable Administrators This reporting option allows school to view which of their students are not currently linked to a myKindo account. If students are not linked to a myKindo account, they will not be able to see their outstanding payments ...

    • Aged Student Payables by Student ID

      Access: Kindo Payable Administrators This reporting option allows you to view and export outstanding payable amounts, based on the month they were applied. 1. Select the Reporting tab from the main Payables Admin page. 2. Select Kindo Payables and ...

    • Kindo Payables Report - General Ledger Code order

      Access: Payables Administrator This will show you all outstanding payables. The report is in general ledger code order, and then room number, then student name. 1. Login to the shop site and select the Admin option. 2. Click on the Reporting button ...

    • Kindo Payables Report - Room number order

      Access: Payables Administrator This will show you all outstanding payables. The reports are in Room number order, and then student name, so will show you all outstanding payments for each class and students within that class. 1. Login to the shop ...

    • Payables Reporting Overview

      Access: Payable Administrators This page provides an overview of how to view and export reports for unlinked student, POS payments, outstanding payable amounts and School Managed Accounts. You can also create a pivot table for Board of Trustees ...