ctcLink Accounting Manual | 50.50.60 Financial Aid Reconciliation

Each of the following departments is responsible for a portion of financial aid fund reconciliation:

• Financial Aid
• Student Finance
• Finance

It is imperative for college staff from various departments to work together to ensure financial aid is correctly accounted for and reconciled.

This section walks through the reconciliation steps (after Financial Aid has been disbursed) from Financial Aid disbursement records, Customer Service Student Award Disbursements, the Expenses processed into the General Ledger, and finally, in the Treasury module where the funds are reconciled in the Semi-Manual Reconciliation (SMR) to the bank.

The primary example provided is for federal aid and drawdown from G6, the reconciliation process is similar for all funding sources.

50.50.60.2.a Financial Aid Reconciliation Queries

The queries used to reconcile Financial Aid to Student Finance to the General Ledger (FA-SF-GL) are:

FA Data

• QCS_FA_DISB_INFO_LIMITED_POP (Campus Solutions query folder: FA Recon)

CS Data

• QCS_SF_E214_ACCTG_LN_CF_DTL (Campus Solutions query folder: Student Financials)
• QCS_SF_ITEM_1ST_2ND_ACCOUNTS (Campus Solutions query folder: Student Financials)

GL Data

• QFS_GL_JRNL_ACCT_ANALYSIS (Finance query folder: General Ledger)

After the reconciliation, funds will need to be drawn down/requested from the Department of Education’s G6 website, Washington Student Achievement Council, State Board, and all other sources.

In ctcLink the amount requested will be entered by invoice and recorded using the accounts receivable module.

When the funds are received by the bank and the AR payment transaction is processed these items will be manually reconciled in the SMR.

50.50.60.2.b Running the Financial Aid Query

Step 1: Run the QCS_FA_DISB_INFO_LIMITED_POP Query.

This query looks at the offered/accepted and authorized/disbursed amounts in Financial Aid by Item Type.

Navigation: Menu > Reporting Tools > Query > Query Viewer.

To ensure all data for a specific Aid Year is returned, it is important the date range is all inclusive since Aid Year and Fiscal Years may overlap.

This query may need to be ‘scheduled’ to run to success.

Step 2: Create a Pivot Table

When completed, open and save the query results in Excel and create a pivot table with the following parameters:

Do NOT use the Disbursed Total amount field as this represents a running total of disbursed aid for the academic year and will be overstated.

Step 3: Change the design of the pivot table:

50.50.60.2.c Running the E214 Query

When financial aid is applied to student accounts, all payments are initially recorded in Fund 790-285-98009. The E-214 is designed to distribute all payments or waivers to the receivable chartstring.  Financial aid payments reduce the receivable with an offset (debit) to internal cash (1000199).  All unapplied financial aid is recorded in Fund 790-285-98009 with debit to internal cash and credit to a liability. This is why using only the internal cash (1000199) in the following query provides the results necessary to reconcile.

Step 1: In Campus Solutions run CS_SF_E214_ACCTG_LN_CF_DTL for the period being reconciled

NOTE: If the data set is too large, limit it by using account 1000199. The file in the example was set to run to HTML; however, this query might need to be scheduled if the data set is larger.

If this query has not previously been scheduled a new Run Control ID must be added.

Navigation: Campus Solution > NavBar > Menu > Reporting Tools > Query > Query Viewer (or > Schedule Queries) > QCS_SF_E214_ACCTG_LN_CF_DTL

NOTE: Colleges should limit the E214 query by account 1000199 to keep the data manageable. The example below was run to HTML; however, you likely will need to schedule this query if your data set is larger.

Step 2: Create Pivot Table

Open the query in Excel and move the tab over to the saved FA Query Excel workbook saved in the first step above. This way the two data sets are in the same file and allows the creation of a tab viewing both Pivot tables side-by-side in the reconciliation process.

Pivot the E214 Query by Financial Aid Disbursements (E214 by Term) with the following PivotTable Fields:

Insert the pivot table in the same worksheet as the pivot table from the ‘QCS_FA_DISB_INFO_LIMITED_POP’ query.

50.50.60.2.d Identifying the Variances between FA Query and E214 Query

The Financial Aid Disbursement variances will need to be researched and provided to the Financial Aid Office for investigation and correction. Once the correction has been made the above reconciliation will need to be redone to assure data integrity and completion of the FA to CS portion of the reconciliation.

Step 1a

Isolate the variances by Item Type and Term begin by using the pivot tables shown above.

Step 1b

Calculate the difference by Term and then by Item Type/Term as shown below.

Step 2

Pivot the FA Query and the E214 – Cash Distribution query as demonstrated below.

Step 3

Place the two pivot tables side by side and create a VLOOKUP to find any discrepancies by Student for Financial Aid to research and correct. This is VLOOKUP formula: VLOOKUP Formula: =IFERROR(VLOOKUP(E8,$A$8:$C$483,3,FALSE),0).

• VLOOKUP Explained: =VLOOKUP (What to look up, where to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
• IFERROR Explained: Since the VLOOKUP formula returns an #NA if the value in E8 is not found, the IFERROR formula is added so returns a ‘0’ instead of the #N/A.

• Next, calculate the variance from the grand total in the E214 Pivot Table by the amount in the VLOOKUP to find any of the discrepancies.

Step 4

Send these discrepancies to the Financial Aid department to review and correct.

• Once the corrections have been made, the reconciliation process will need to be rerun to confirm the corrections processed as expected.

50.50.60.2.e Running the General Ledger Query

Step 1

In CS run the QCS_SF_ITEM_GL_1ST_2ND_DETAIL query (Navigation: NavBar > Menu > Reporting Tools > Query > Query Viewer or > Schedule Queries) and export to Excel.  In Excel filter for the item types being reconciled.  Note the fund for each Item Type. Also identify the revenue or contra account used for each Item Type/Funding Source.

For a complete list see Second Journal Set Financial Aid Expenditure Accounts.

Step 2

In FSCM run the QFS_GL_JRNL_ACCT_ANALYSIS query (Navigation: NavBar > Menu > Reporting Tools > Query > Query Viewer or > Schedule Queries) for the period being reconciled. (NOTE: Use the fund(s) identified above in the Second Journal Set (SJS). It may be necessary to add a new Run Control ID.

The pivot table above identifies YTD expenses for each Item Type by expense account.  The Request amounts are based on college/G6 records.  If all accounting records are correct, the difference between ‘Total Requested from G6’ and the ‘YTD Expense’ should be the amount Available to Request.

Drawdowns for Federal grant expenditures should be done at least monthly using the ED G6 online system. At the beginning of each academic quarter funds should be drawn down from ED. The amount is determined by what is needed to cover the Pell/SEOG checks/electronic payments issued to students that will be distributed within the next three business days (an estimate can be used). If the college runs the SEOG match (25%) in the same class/department used for the federal portion, then the college should claim only the federal portion when drawing down the revenue.

The G6 (previously G5) is a grants management system developed by the U.S. Department of Education (ED) This system enables an institution to request payments or return funds to ED for the following Title IV programs (G6 HELP):

• Federal Pell Grant Program
• TEACH grant program
• Federal Supplemental Education Opportunity Grant (FSEOG)
• The Direct Loan Program (Subsidized, Unsubsidized & Plus Loans)

Once ready to process a drawdown or refund, go to G6ED grants management system. To access G6:

1. The user must have a valid and active User ID.
2. Servicers must select the payee using the Select Payee (Servicers) procedure.

A drawdown occurs when college initiates a request for funds through the G6 website, and the funds are transmitted from the U.S. Department of the Treasury to the school's bank account (also known as cash receipt).

After accessing the college the award, a payment request can be set up:

Click the Approve button to approve the payment and complete the task. Funds will be deposited into the bank account previously set up by college staff responsible for federal student aid.  Click Here for detailed information on the Department of Education funding process.

After the funds have been requested from the G6 site an invoice for the amount G6 will be sending will need to be created in ctcLink.

In the above screenshot from G5/6 the requested amount is $100; however, the example will show several different amounts/draws.

Funds should be requested from WSAC at least monthly using the secure portal CSAW (College Bound - WCG Award Warehouse).

Payment requests may be made any time from late-July through late-June of the academic year.

Payments for a new year will not begin until the college’s annual participation agreement is approved, and the final interim report is reconciled for the prior year.

Requests for financial aid funds from SBCTC are made through the Online Budget and Invoicing System (OBIS) should be submitted monthly or quarterly depending on program guidance.

Normally, the Second Journal Set should record the expense/cash directly to the funding chartstring.

Requests from other sources (Achieving the Dream, etc.) will vary and each college should be aware of each program’s requirements.

Navigation: FSCM > NavBar > Menu > Billing > Maintain Bills > Express Billing

1. The Express Bill Entry search page displays.
2. Select the Add a New Value tab.
   1. Enter Business Unit
   2. Keep Invoice as NEXT
   3. Enter Bill Type Identifier
   4. Enter Bill Source
   5. Enter Customer
      1. FIN00102 - US Department of Education
      2. 001000535 - WA Student Achievement Council
      3. FIN00107 - State Board for Comm & Tech Colleges
   6. Enter Invoice Date
   7. Enter Accounting Date

      

3. Select Add
4. The Billing General page displays and is populated with the entered information.

   

5. In the Bill Lines section, add one or more Bill Lines:
   1. Enter Table = "ID" which stands for PS/Billing Charge ID.
   2. Select a Charge Code by using the lookup button next to the Identifier field. If the Charge Codes have not been set up, the Revenue Distribution will need to be entered manually.
   3. Entering a Description or selecting the Header Notes link to add invoice details is optional.
   4. Enter a Quantity
   5. Enter a Unit Price
   6. Save page

      

6. Upon saving the page, a unique Invoice number will be generated, and the Gross Extended amount will be updated.

Navigation: FSCM > NavBar > Menu > Accounts Receivable > Payments > Online Payments > Express Deposit

Express Deposits allow the entry of a deposit and application of the payment at the same time within ctcLink. The receivables system must be updated with deposit information after the received payments have been deposited into your bank.

The Billing Item IDs or PeopleSoft Invoices for which the payments are received must be used to make Express Deposit entries.

Visit the ctcLink Reference Center for complete instructions on Entering an Express Deposit.

Navigation:  FSCM > NavBar > Menu > Banking > Reconcile Statements > Semi-Manual Reconciliation

Once the funds are received by the bank and the Express Deposit has been processed these transactions can manually reconciled in the SMR.