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 in ctcLink 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.
You must have at least one of these local college managed security roles:
- ZD_DS_QUERY_VIEWER
If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.
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.
Student Financials to Finance GL Recon
Navigation: Reporting Tools > Query > Query Viewer or Schedule Query
Query #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.
- Download into spreadsheet format.
- 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.
Query #2
- Use the query titled QCS_SF_E215_ACCTG_LN.
- Download into spreadsheet format.
The E215 query contains departmental receipt transactional data (non-student.)
- Create a pivot table using the data from Query #2 and isolate the Fund, Class, and Department ID you are researching.
Results from CS Query #1 and CS Query #2 will differ; however, both sets of data will be batch/journalized into the Finance Pillar.
The criteria used in the Finance Queries should match what the end user wants to verify or reconcile. Whether it’s a specific Fund or Fund-Department Combination and/or enter a specific General Ledger Account.
The following example uses Fund 149, Department 49197, Year 2020 Period 4, and GL Account 4000100 Tuition & Fee Waiver.
Query #3. QFS_GL_ACCT_ANALYSIS - GL Ledger Table/Trial Balance Query
- Use the query titled QFS_GL_ACCT_ANALYSIS - GL Ledger Table/Trial Balance Query.
This query provides a Trial Balance or a summary of the data that has been journalized from Student Financials.
- Use the query titled QFS_GL_ACCT_ANALYSIS. Enter your desired or a combination of desired criteria: Fund, Department, Account and select From Fiscal Year and To Fiscal Year.
- 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.
- Create a pivot table using the data from Query #3 and isolate the Fund/Department ID you are researching.
Query #4. QFS_GL_SF_JRNL_VERIFY_BY_CF - SF System to Journal to Ledger
- Use the query titled: QFS_GL_SF_JRNL_VERIFY_BY_CF - SF system to Journal to Ledger.
- Use the query QFS_GL_SF_JRNL_VERIFY_BY_CF. Enter the Fiscal Year, From Acctg Period to Acctg Period. Optional to enter additional criteria to narrow down search results, such as Fund, Class, Department, Project and/or Operating Unit.
- Download into spreadsheet format.
The QFS_GL_SF_JRNL_VERIFY_BY_CF Query provides another source of verification and provides a detailed summary of the data journalized from Student Financials. By selecting the source tables, CS_SF_ACCTG_LINE or FS_SF_ACCTG_LINE, you are able to identify the data from the CS Pillar and verify has posted to the FS Pillar. Note: the General Ledger isolates data by the Period. Make sure your criteria for your Pivot table is correct.
- Create a pivot table using the data from Query #4 and isolate the Fund/Department ID you are researching.
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.
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.
- End of procedure.
0 Comments
Add your comment