October 6, 2008 - Monday 2:30 p.m.

Financial Aid to Finance

Margie Carrington – Canada College

San Mateo Community College District

Velda Long – Taft College

Laurie Neil – Strata Information Group (SIG)

Sign up

• Sign the sheet and we will email you the presentation and scripts later today in a zip file

• The presentation will also be posted on the website after the conference

Balancing/Reconciliation

• We care about Financial Aid to Finance because we have to balance transactions

• Who is responsible at your institution?– FA office?– Student Accounts/Cashiering?– Fiscal/Finance/Business Office?

• What tools are available to you?• How often should the process be

completed?– It’s not just for “the audit”

Definitions

• Outside source of funds/data• Financial aid funds• Detail codes• Student account• General Ledger• FOAPAL• Exemptions• Contracts• Resources

Outside Source of Funds/Data

• FFELP (ELM, Lenders, OpenNet)• COD (Pell, ACG, TEACH, DL)• Foundation (Scholarships)• HR/Payroll (Work-study)• CSAC (CalGrant)

Financial Aid Funds

• Based on how awards are displayed for:– award letters– Student bills– the General Ledger– the institutional budget

• RFRBASE• RFRMGMT• ROAMGMT

Detail codes

• Based on how transactions should appear on the student account

• TSADETC• TSAAREV

Student Account

• Where charges and payments display• Source of student bills• Appication of Payments matches

charges and payments• Refunds result when there is a credit

balance• TSAAREV• TGRAPPL• TSIAPPL (shows payment/charge

matches)

General Ledger

• The institution’s view of charge and payment activity (revenue and expense)

• FGIBDSR• FGIBDST• FGITBAL

FOAPAL

• Fund – Pell, Federal Grant• Organization – Financial Aid

Department• Account – Revenue or Expense, GAPS

drawdown, Student grant payment• Program - Academic• Activity – Instruction (optional)• Location – Hospital (optional)

Exemptions

• A method of determining a different amount for certain charges, based on a student’s characteristic

• Senior discount• Dependent of an employee• TSAEXPT

Contracts

• A process for recording the intent to bill an outside party, and receiving payment and applying it to student accounts.

• Voc Rehab• Tuition paid by employers • TSACONT

Resources

• Funds a student receives that will not show up as a payment on the student account.– Veterans Benefits– Free room and board from a relative or

friend

• Resources reduce unmet need for financial aid awarding.

• RPAARSC

How funds flow through Banner

• Financial Aid Funds are awarded (RPAAWRD)

• Then Disbursed (RPEDISB) • To the student account (TSAAREV) via

detail codes• Which are tied to accounting strings

(FOAPs)• Which update the General Ledger

(FGIBDSR)

Financial Aid Funds

• Set them up in a way that makes sense for your institution– One general scholarship fund– Many scholarship funds– Separate federal funds

• Pell Even Year• Pell Odd Year

– (OR use Term based detail codes)– Each aid year is a new year; you can

change your setup then if it will help you

Detail Codes

• One to one relationship – Between FA funds and detail codes – Cannot use one detail code for 2 different funds

• Either a one to one relationship– Between detail codes and FOAPALs

• OR• Many to one relationship

– Between detail codes and FOAPALS– You can use the same accounting string (FOAPs)

for multiple detail codes

Detail Codes – TSADETC page 1

• Distinguish FA detail codes– By starting them all with the same letter; Z

%– By querying based on the FA type

• Set security to prevent manual entry– Use detail code security in Student AR so

that any financial aid detail codes (category – FA) cannot be manually entered

– Ensures that FA and Student AR stay balanced at all times

Detail codes – TSADETC page 2• Accounting Distribution • Charge Detail Codes; the detail code type is a charge or C

– the amount is positive• then line A) of the detail code is credited.

– the amount is negative• then line A) of the detail code is debited.

• Payment Detail Codes; the detail code type is a Payment or P – the amount is positive

• then line B) of the detail code is debited. – the amount is negative

• then line B) of the detail code is credited.• Rule Classes 1, 2, and 3 each perform different functions.

– Rule Class 1 • used for the initial entry of the detail code (AT INPUT).

– Rule Class 2 • used in Application of Payments for liquidating UNLIKE codes. • A charge type code C and a payment type code P.

– Rule Class 3• used in Application of Payments for liquidating LIKE codes. • Both sides are charge type code C or both sides are payment type code P.

Disburse/Apply/Post

• RPEDISB - Each RPRADSB has an FA fund, a term and a date

• TGRAPPL - Each TBRACCD has a detail code, a term and a date

• A/R GL interface - Each FGRTRND has a FOAPAL and a date

Detail codes post to the GL

• Based on the accounting in the detail code, that FOAPAL line in the GL is updated– TGRFEED process recognizes the detail codes that

have not been “fed”, summarizes the transactions and inserts them into the GURFEED table

– FURFEED process takes the feed records from GURFEED (general finance interface table), performs a few edits, and inserts them into FGBTRNI (transaction interface table)

– FGRTRNI process creates JVs from the FGBTRNI interface records

– FGRACTG posts the JVs to the General Ledger/FOAPAL– Banner A/R User Guide, page 67 for more info.

Multiple sources for the data

• Financial Aid• Student Accounts• General Ledger• Outside Sources

Tools Available

• Banner Reports– RFRBUDG (RFIBUDG/ROAMGMT)– FGRBDSR/FGRBDST (FGIBDSR/FGITBAL)

• Excel – Easy to find staff who know it– Data can easily/accidentally be changed

• Custom Reports– More skill required– Less chance of balancing errors

Balancing – Recommended steps

• First compare RFIBUDG totals to your RPRADSB records– Click the button on ROAMGMT (Reconcile All Funds) OR– Use the rslfndbl.sql script to select and display fund

balances for all funds for a selected year. These balances are calculated based on the sum of all student awards from the RPRAWRD table. These calculated totals should be compared to the totals displayed on the Fund Budget Inquiry Form for all funds to determine if a fund is out of balance.

– If a fund is out of balance, you can run the rupfndbl.sql script to update the balances stored in the RFRASPC table to match the student-by-student totals stored in the RPRAWRD table. Refer to the rupfndbl.sql script description for information on fund balance reconciliation. Note: The aid year is hardcoded into this script and should be replaced with &AIDY so that the script prompts for aid year at run time.

– More detail available in the Banner Financial Aid TRM (Technical Reference Manual) Supplement

Balancing Recommended Steps

• Second compare Financial Aid to Student Acounts– Easier if you do not allow manual entry of category

FA detail codes– RPRADSB records to TBRACCD records– Non-disbursable funds will have no records– Some detail codes will only have Resource records

• Third compare Student Accounts to General Ledger – Not a one to one relationship– Multiple student account transactions are

summarized in one GL transaction– TBRACCD records to FGRTRND records

Balancing Recommended Steps

• Fourth compare Financial Aid records to outside data source records…This is the tricky part!– Work-study– Loans– COD (Pell, ACG, DL)– Foundation– External Scholarship sources– CalGrants

Work-Study

• HR/Payroll• Banner ID• Once a month; beware of late

adjustments• Has to match on term• Track transactions before/after July 1

Loans

• SSN• Term• Formats will vary depending on the

source of the data

COD

• SSN• Term• Banner has many processes to assist

with balancing• All COD files (statement of account,

reconciliation, etc.) can be loaded to Banner and viewed on forms

Foundation

• SSN• School ID• Name• Fund – Foundation? General Ledger?

FA?

CalGrants

• Data available from the WebGrants system– Accept/Reject Report– Payment Activity Report– WebGrants Reconciliation Report– Leave of Absence Report– Withdrawn Student Report

• http://www.csac.ca.gov/cgm/Chap10.pdf• This is dated October 2003• Brad’s presentation – Tuesday 11:00 am

Banner Processes to Aid Balancing

• COD (Pell, ACG, DL) – complete package• Other Grants, Loans, Scholarships –

Not so much!

Other Resources

• Past Summit Presentations• Ron Coker – SunGard

– Session S-0468 Summit 2008

• Brent Shock – Miami University– Session 476 Summit 2006

• Find somone at your institution who has the CD with presentations from those years

SQL from Colleagues

• Two processes to assist with balancing• Aid outside fsyear

– Lists aid disbursements for a given fund (AR detail code) that were outside the corresponding fiscal year. Used for reconciling FISAP (which is on an aid year basis) to GL (which is on a fiscal year basis). (LGN note: doesn’t use FGRTRND)

• FA_reconciliation– 2 scripts– First creates a table– Second creates

Sue Schwab, Andrews Universityaid_outside_fsyr.sql

• SQL• rem Lists aid disbursements for a given fund (AR detail code) that were• rem outside the corresponding fiscal year. Used for reconciling FISAP• rem (which is on an aid year basis) to GL (which is on a fiscal year• rem basis).

• rem Written by Sue Schwab• rem August 4, 1999

• rem 9/8/00 - refined term/date select to avoid getting all fall semester• rem disbursements (required term to be in given aid year when transaction• rem date is greater than last day of that aid year). --ss• rem 10/1/01 - changed to select any transactions with terms in the • rem aid year but dates outside the fiscal year, or with terms outside• rem the aid year but dates in the fiscal year (I don't know WHAT I was• rem thinking using the aid year dates!). --ss

• accept aidy prompt 'Check aid for what aid year (eg 9899)? '• accept fund prompt 'For what AR detail code (EG08 SEOG, EL01 PERK, or other)? '

• set term off• set echo off• set verify off

Sue’s script page 2• column raidy new_value xraidy noprint format A1• column fund new_value xfund noprint format A1• column rdate new_value xrdate noprint format A1• column aidy heading 'Aid Yr' format A7• column id heading ID format A10• column name heading Name format A25 trunc• column term heading Term format A7• column amount heading 'Disburse|Amount' format 999,999.00• column edate heading 'Disburse|Date' format A12

• ttitle left 'ANDREWS UNIVERSITY - AID DISBURSEMENTS OUTSIDE OF FISCAL YEAR' skip 1 -• left xraidy ' ' xfund skip 1 -• left xrdate skip 2

• set pagesize 59• set linesize 80

• break on aidy skip 2 on id on name on report• compute sum of amount on aidy report

• spool aid_outside_fsyear

• select '&aidy' raidy,• rfrbase_fund_title fund,• to_char(sysdate,'MM/DD/YYYY HH24:MI') rdate,• spriden_id id,• spriden_last_name||', '||spriden_first_name||' '||• substr(spriden_mi,1,1) name,• stvterm_fa_proc_yr aidy,• tbraccd_term_code term,• tbraccd_amount amount,• tbraccd_effective_date edate• from spriden,• stvterm,• robinst,• rfrbase,• tbraccd

Sue’s Script page 3• where tbraccd_pidm = spriden_pidm and• spriden_change_ind is null and• robinst_aidy_code = '&aidy' and• tbraccd_term_code = stvterm_code and• tbraccd_detail_code = rfrbase_detail_code and• (• (tbraccd_term_code in • (select stvterm_code /* term in aid year */• from stvterm• where stvterm_fa_proc_yr = '&aidy') and• (tbraccd_effective_date < to_date(substr('&aidy',1,2)||'0701',• 'YYMMDD') or /* date earlier than fiscal year */• tbraccd_effective_date >= to_date(substr('&aidy',3,2)||'0701',• 'YYMMDD'))) /* or date later than fiscal year */• or• (tbraccd_term_code not in • (select stvterm_code /* term not in aid year */• from stvterm• where stvterm_fa_proc_yr = '&aidy') and• (tbraccd_effective_date >= to_date(substr('&aidy',1,2)||'0701',• 'YYMMDD') and /* date in fiscal year */• tbraccd_effective_date < to_date(substr('&aidy',3,2)||'0701',• 'YYMMDD'))) • )• and• tbraccd_detail_code = upper('&fund')• order by aidy, name, term• /• spool off

• set echo on• set verify on• set term on

• exit

Cathy Scofield - Santa Fe Community College Fa_reconciliation

• CREATE TABLE FA_RECONCILIATION• (• ID VARCHAR2(9 BYTE),• PIDM NUMBER(8),• NAME VARCHAR2(70 BYTE),• SSN VARCHAR2(9 BYTE),• TERM1 VARCHAR2(6 BYTE),• TERM2 VARCHAR2(6 BYTE),• TERM3 VARCHAR2(6 BYTE),• AID_YEAR VARCHAR2(4 BYTE),• FUND VARCHAR2(6 BYTE),• FA_PAID1 NUMBER(12,2),• FA_PAID2 NUMBER(12,2),• FA_PAID3 NUMBER(12,2),• FA_PAID_TD NUMBER(12,2),• AR_PAID1 NUMBER(12,2),• AR_PAID2 NUMBER(12,2),• AR_PAID3 NUMBER(12,2),• AR_PAID_TD NUMBER(12,2),• COD_PAID_TD NUMBER(12,2)• )• TABLESPACE DEVELOPMENT• PCTUSED 0• PCTFREE 10• INITRANS 1• MAXTRANS 255• STORAGE (• INITIAL 64K• MINEXTENTS 1• MAXEXTENTS UNLIMITED• PCTINCREASE 0• BUFFER_POOL DEFAULT• )• LOGGING • NOCOMPRESS • NOCACHE• NOPARALLEL• MONITORING;

Cathy Scofield - Santa Fe Community College

FA reconciliation script.sqlDEFINE aidyear = '&AidYear'DEFINE fund = '&Fund'DEFINE fallterm = '&fallterm';DEFINE springterm = '&springterm';DEFINE summerterm = '&summerterm';

--1 delete from fa_reconciliationdelete from fa_reconciliation;commit;

--2 insert into fa_reconciliationinsert into fa_reconciliation(pidm)select distinct(rorstat_pidm) from rorstatwhere rorstat_aidy_code = '&aidyear';commit;

--3 update id, name, ssnupdate fa_reconciliationset (id, name, ssn, aid_year, fund) = (select spriden_id, spriden_last_name || ' ,' || spriden_first_name,spbpers_ssn,'&aidyear','&fund'from spriden, spbperswhere spriden_pidm = spbpers_pidmand spriden_pidm = pidmand spriden_change_ind IS NULL);commit;

Page 2--3 Update termsupdate fa_reconciliationset term1 = (select distinct(MIN(rpratrm_term_code))from rpratrmwhere rpratrm_aidy_code = '&aidyear');commit;update fa_reconciliationset term2 = (term1 + 10);commit;update fa_reconciliationset term3 = (term2 + 80);commit;

--4 delete where no fund or no moneydelete from fa_reconciliationwhere not exists (select * from rpratrmwhere rpratrm_aidy_code = aid_yearand rpratrm_term_code IN (term1, term2, term3)and rpratrm_fund_code = fundand rpratrm_offer_amt <> 0and rpratrm_pidm = pidm);commit;

Page 3--5 update FA paidupdate fa_reconciliationset fa_paid1 = (select rpratrm_paid_amtfrom rpratrmwhere rpratrm_term_code = term1and rpratrm_aidy_code = aid_yearand rpratrm_fund_code = fundand rpratrm_pidm = pidm);commit;update fa_reconciliationset fa_paid2 = (select rpratrm_paid_amtfrom rpratrmwhere rpratrm_term_code = term2and rpratrm_aidy_code = aid_yearand rpratrm_fund_code = fundand rpratrm_pidm = pidm);commit;update fa_reconciliationset fa_paid3 = (select rpratrm_paid_amtfrom rpratrmwhere rpratrm_term_code = term3and rpratrm_aidy_code = aid_yearand rpratrm_fund_code = fundand rpratrm_pidm = pidm),fa_paid_td = (select sum(rpratrm_paid_amt)from rpratrmwhere rpratrm_pidm = pidmand rpratrm_aidy_code = aid_yearand rpratrm_term_code IN (term1, term2, term3)and rpratrm_fund_code = fund);commit;

Page 4--6 update AR paid update fa_reconciliationset ar_paid1 = (select sum(tbraccd_amount)from tbraccdwhere tbraccd_term_code = term1and tbraccd_pidm = pidmand tbraccd_detail_code = (select rfrbase_detail_codefrom rfrbasewhere rfrbase_fund_code = fund)group by tbraccd_pidm);commit;

update fa_reconciliationset ar_paid2 = (select sum(tbraccd_amount)from tbraccdwhere tbraccd_term_code = term2and tbraccd_pidm = pidmand tbraccd_detail_code = (select rfrbase_detail_codefrom rfrbasewhere rfrbase_fund_code = fund)group by tbraccd_pidm);commit;

update fa_reconciliationset ar_paid3 = (select sum(tbraccd_amount)from tbraccdwhere tbraccd_term_code = term3and tbraccd_pidm = pidmand tbraccd_detail_code = (select rfrbase_detail_codefrom rfrbasewhere rfrbase_fund_code = fund)group by tbraccd_pidm),ar_paid_td = (select sum(tbraccd_amount)from tbraccdwhere tbraccd_term_code IN (term1, term2, term3)and tbraccd_pidm = pidmand tbraccd_detail_code = (select rfrbase_detail_codefrom rfrbasewhere rfrbase_fund_code = fund));commit;

Page 5--7 update COD amt due tdupdate fa_reconciliationset cod_paid_td = (select a.RERRECN_YTD_DISB_AMTfrom rerrecn awhere a.rerrecn_aidy_code = aid_yearand a.rerrecn_pidm = pidm--and a.rerrecn_pay_reason_cde = '01' --This was in original code, but I do not know its functionand a.rerrecn_process_date = (select Max(b.rerrecn_process_date)from rerrecn bwhere b.rerrecn_pidm = pidm and b.rerrecn_aidy_code = aid_year));commit;

--18 fa reconciliation reportset linesize 180set pagesize 1200set newpage 0set feedback offset showmode offset verify offset echo off

spool c:\fa_reconciliation_&Fund&AidYear

ttitle left 'Santa Fe Community College - Office of Financial Aid ' skip 1 - left 'Reconciliation Report ' &aidyear skip 1 - left 'for Fund ' &fund skip 1 - right 'Date: 'repdate skip 1 - right 'fa_reconciliation.sql' skip 3

Page 6column ID heading IDcolumn name heading Name format a40column ssn heading SSNcolumn fa_paid1 heading 'FA Paid | &fallterm' format 99999.99column fa_paid2 heading 'FA Paid | &springterm' format 99999.99column fa_paid3 heading 'FA Paid | &summerterm' format 99999.99column fa_paid_td heading 'FA Total' format 99999.99column ar_paid1 heading 'AR Paid | &fallterm' format 99999.99column ar_paid2 heading 'AR Paid | &springterm' format 99999.99column ar_paid3 heading 'AR Paid | &summerterm' format 99999.99column ar_paid_td heading 'AR Total' format 99999.99column cod_paid_td heading 'COD Total' format 99999.99

select distinct ID, Name,SSN,FA_paid1,fa_paid2,fa_paid3,fa_paid_td,ar_paid1,ar_paid2,ar_paid3,ar_paid_td,cod_paid_td from fa_reconciliation;

spool off

Audience Participation

• Additional Banner AR or Finance reports you use?

• Additional scripts you could share?

Questions?

• Margie Carrington– carringtonm@smccd.edu

• Velda Long– vlong@taftcollege.edu

• Laurie Neil– lneil@sigcorp.com