October 6, 2008 - Monday 2:30 p.m. Financial Aid to Finance Margie Carrington – Canada College San Mateo Community Col

1. 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)

2. 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

3. 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”

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

5. Outside Source of Funds/Data • FFELP (ELM, Lenders, OpenNet) • COD (Pell, ACG, TEACH, DL) • Foundation (Scholarships) • HR/Payroll (Work-study) • CSAC (CalGrant)

6. Financial Aid Funds • Based on how awards are displayed for: • award letters • Student bills • the General Ledger • the institutional budget • RFRBASE • RFRMGMT • ROAMGMT

7. Detail codes • Based on how transactions should appear on the student account • TSADETC • TSAAREV

8. 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)

9. General Ledger • The institution’s view of charge and payment activity (revenue and expense) • FGIBDSR • FGIBDST • FGITBAL

10. 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)

11. Exemptions • A method of determining a different amount for certain charges, based on a student’s characteristic • Senior discount • Dependent of an employee • TSAEXPT

12. 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

13. 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

14. 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)

15. 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

16. 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

17. 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

18. 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.

19. 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

20. 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 thetransactions 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.

21. Multiple sources for the data • Financial Aid • Student Accounts • General Ledger • Outside Sources

22. 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

23. 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

24. 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

25. 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

26. Work-Study • HR/Payroll • Banner ID • Once a month; beware of late adjustments • Has to match on term • Track transactions before/after July 1

27. Loans • SSN • Term • Formats will vary depending on the source of the data

28. 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

29. Foundation • SSN • School ID • Name • Fund – Foundation? General Ledger? FA?

30. 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

31. Banner Processes to Aid Balancing • COD (Pell, ACG, DL) – complete package • Other Grants, Loans, Scholarships – Not so much!

32. 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

33. 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

34. 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

35. 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

36. 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

37. 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;

38. Cathy Scofield - Santa Fe Community CollegeFA reconciliation script.sql DEFINE aidyear = '&AidYear' DEFINE fund = '&Fund' DEFINE fallterm = '&fallterm'; DEFINE springterm = '&springterm'; DEFINE summerterm = '&summerterm'; --1 delete from fa_reconciliation delete from fa_reconciliation; commit; --2 insert into fa_reconciliation insert into fa_reconciliation (pidm) select distinct(rorstat_pidm) from rorstat where rorstat_aidy_code = '&aidyear'; commit; --3 update id, name, ssn update fa_reconciliation set (id, name, ssn, aid_year, fund) = (select spriden_id, spriden_last_name || ' ,' || spriden_first_name, spbpers_ssn, '&aidyear', '&fund' from spriden, spbpers where spriden_pidm = spbpers_pidm and spriden_pidm = pidm and spriden_change_ind IS NULL); commit;

39. Page 2 --3 Update terms update fa_reconciliation set term1 = (select distinct(MIN(rpratrm_term_code)) from rpratrm where rpratrm_aidy_code = '&aidyear'); commit; update fa_reconciliation set term2 = (term1 + 10); commit; update fa_reconciliation set term3 = (term2 + 80); commit; --4 delete where no fund or no money delete from fa_reconciliation where not exists (select * from rpratrm where rpratrm_aidy_code = aid_year and rpratrm_term_code IN (term1, term2, term3) and rpratrm_fund_code = fund and rpratrm_offer_amt <> 0 and rpratrm_pidm = pidm); commit;

40. Page 3 --5 update FA paid update fa_reconciliation set fa_paid1 = (select rpratrm_paid_amt from rpratrm where rpratrm_term_code = term1 and rpratrm_aidy_code = aid_year and rpratrm_fund_code = fund and rpratrm_pidm = pidm); commit; update fa_reconciliation set fa_paid2 = (select rpratrm_paid_amt from rpratrm where rpratrm_term_code = term2 and rpratrm_aidy_code = aid_year and rpratrm_fund_code = fund and rpratrm_pidm = pidm); commit; update fa_reconciliation set fa_paid3 = (select rpratrm_paid_amt from rpratrm where rpratrm_term_code = term3 and rpratrm_aidy_code = aid_year and rpratrm_fund_code = fund and rpratrm_pidm = pidm), fa_paid_td = (select sum(rpratrm_paid_amt) from rpratrm where rpratrm_pidm = pidm and rpratrm_aidy_code = aid_year and rpratrm_term_code IN (term1, term2, term3) and rpratrm_fund_code = fund); commit;

41. Page 4 --6 update AR paid update fa_reconciliation set ar_paid1 = (select sum(tbraccd_amount) from tbraccd where tbraccd_term_code = term1 and tbraccd_pidm = pidm and tbraccd_detail_code = (select rfrbase_detail_code from rfrbase where rfrbase_fund_code = fund) group by tbraccd_pidm); commit; update fa_reconciliation set ar_paid2 = (select sum(tbraccd_amount) from tbraccd where tbraccd_term_code = term2 and tbraccd_pidm = pidm and tbraccd_detail_code = (select rfrbase_detail_code from rfrbase where rfrbase_fund_code = fund) group by tbraccd_pidm); commit; update fa_reconciliation set ar_paid3 = (select sum(tbraccd_amount) from tbraccd where tbraccd_term_code = term3 and tbraccd_pidm = pidm and tbraccd_detail_code = (select rfrbase_detail_code from rfrbase where rfrbase_fund_code = fund) group by tbraccd_pidm), ar_paid_td = (select sum(tbraccd_amount) from tbraccd where tbraccd_term_code IN (term1, term2, term3) and tbraccd_pidm = pidm and tbraccd_detail_code = (select rfrbase_detail_code from rfrbase where rfrbase_fund_code = fund)); commit;

42. Page 5 --7 update COD amt due td update fa_reconciliation set cod_paid_td = (select a.RERRECN_YTD_DISB_AMT from rerrecn a where a.rerrecn_aidy_code = aid_year and a.rerrecn_pidm = pidm --and a.rerrecn_pay_reason_cde = '01' --This was in original code, but I do not know its function and a.rerrecn_process_date = (select Max(b.rerrecn_process_date) from rerrecn b where b.rerrecn_pidm = pidm and b.rerrecn_aidy_code = aid_year )); commit; --18 fa reconciliation report set linesize 180 set pagesize 1200 set newpage 0 set feedback off set showmode off set verify off set 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

43. Page 6 column ID heading ID column name heading Name format a40 column ssn heading SSN column fa_paid1 heading 'FA Paid | &fallterm' format 99999.99 column fa_paid2 heading 'FA Paid | &springterm' format 99999.99 column fa_paid3 heading 'FA Paid | &summerterm' format 99999.99 column fa_paid_td heading 'FA Total' format 99999.99 column ar_paid1 heading 'AR Paid | &fallterm' format 99999.99 column ar_paid2 heading 'AR Paid | &springterm' format 99999.99 column ar_paid3 heading 'AR Paid | &summerterm' format 99999.99 column ar_paid_td heading 'AR Total' format 99999.99 column 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

44. Audience Participation • Additional Banner AR or Finance reports you use? • Additional scripts you could share?

45. Questions? • Margie Carrington • carringtonm@smccd.edu • Velda Long • vlong@taftcollege.edu • Laurie Neil • lneil@sigcorp.com