9.2 Student Financials to Finance GL Recon

Purpose:  Use this document as a reference to understand a method of reconciling Student Financial transactions batched/journalized data posted into the General Ledger in the Finance Pillar. This particular guide involves two separate pillars and assumes that users have access to query viewer.  In addition, the queried data will be downloaded into a spreadsheet and analyzed using pivot tables.

Audience:  Campus Solutions staff, Student Financials staff, Financial Aid staff, Finance Staff

How to Use this Guide:

Below, we display 4 queries that use dates which correspond with a "period" in Finance.  Each of the querie's data is then analyed for Fund, Class, and Department ID using Pivot tables.   

Navigation:  Navigator > Reporting Tools > Query > Query Viewer or Schedule Query

CS Pillar - Student Financials

Query #1

  1. Use the query titled QCS_SF_E214_ACCTG_LN.

The E214 query contains student related transactional data. The results may be larger than the normal Query Viewer can handle, you may need to use Schedule Query.

  1. Download into spreadsheet format.
  1. Create a pivot table using the data from Query #1 and isolate the Fund, Class, and Department ID you are researching.  Notice that within the account numbers, the item types are nested.
Screenshot of queried data in pivot table

Query #2

  1. Use the query titled QCS_SF_E215_ACCTG_LN.
  2. Download into spreadsheet format.

The E215 query contains departmental receipt transactional data (non-student.)

  1. Create a pivot table using the data from Query #2 and isolate the Fund, Class, and Department ID you are researching.
Screenshot of queried data in pivot table

Results from CS Query #1 and CS Query #2 will differ, however, both sets of data will be batch/journalized into the Finance Pillar.

Finance Pillar

Query #3

  1. Use the query titled QFS_GL_ACCT_ANALYSIS.
  2. Download into spreadsheet format.

The QFS_GL_ACCT_ANALYSIS Query has been developed and used to verify GL ChartStrings. For the following example a specific Department’s ChartString, 149-509-49197, will be analyzed. When Student Financial Journals are processed in the system, those lines are batched or journalized together and the outcome summary is posted into the General Ledger.

  1. Create a pivot table using the data from Query #3 and isolate the Fund/Department ID you are researching.
Screenshot of queried data in pivot table

Query #4

  1. Use the query QFS_GL_SF_JRNL_VERIFY_BY_CF.
  2. Download into spreadsheet format.

The QFS_GL_SF_JRNL_VERIFY_BY_CF Query provides another source of verification. The General Ledger isolates data by the Period. Make sure your criteria for your Pivot table is correct.

  1. Create a pivot table using the data from Query #4 and isolate the Fund/Department ID you are researching.
Screenshot of queried data in pivot table

Compare SF vs. Finance Pivot Tables

When reconciling the pivot tables data side by side, remember that item types are not viewable within the Finance Pillar.  Use "queried" data from Student Financial's in order to create batched balances manually through the pivot table process in order to balance against the system batched/journalized data that is available from the Finance Pillar.

The below pivot tables show accounts that balance.

Image of SF and Finance pivot tables next to each other for comparison.

What happens when the data doesn't balance?

Corrections should always occur from where they originate.

As we are feeding data from Student Financials (Student Account transactions, Cashiering transactions, Financial Aid disbursements/refunds, etc...) into the Finance Pillar, all correction should originate from within the Student Financials Office.

The item type level of detail is not found within the Finance Pillar, that level of data is batched/journalized into a single balance in order to feed (over the fence) into the Finance Pillar.  

Therefore, if the Finance Pillar discovers a balancing issue, then communication should be sent to the Student Financials Office with as much detail as possible.  With that communication, those responsible in the Student Financials Office can investigate the issue and correct using the proper cashiering processes.

Accounting Dates and Periods

As financial data moves between Student Financials and the Finance Pillar, one variable to keep in mind is processing time.  Several Student Financials Office Processes are automated to run throughout the day and it is possible to have processes cross midnight. This may result in balance discrepancies between the Student Financials and the General Ledger in the Finance Pillar.  

Sometimes adding or removing a day from the Accounting Date query parameter fields may help in balancing against a particular  Finance Period.

0 Comments

Add your comment

E-Mail me when someone replies to this comment