Transcript Slide 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)
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 University
aid_outside_fsyr.sql
•
•
•
•
•
SQL
rem
rem
rem
rem
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).
•
•
rem
rem
Written by Sue Schwab
August 4, 1999
•
•
•
•
•
•
•
rem
rem
rem
rem
rem
rem
rem
9/8/00 - refined term/date select to avoid getting all fall semester
disbursements (required term to be in given aid year when transaction
date is greater than last day of that aid year). --ss
10/1/01 - changed to select any transactions with terms in the
aid year but dates outside the fiscal year, or with terms outside
the aid year but dates in the fiscal year (I don't know WHAT I was
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
from
'&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
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.sql
DEFINE
DEFINE
DEFINE
DEFINE
DEFINE
aidyear = '&AidYear'
fund = '&Fund'
fallterm = '&fallterm';
springterm = '&springterm';
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;
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;
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;
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;
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
Page 6
column
column
column
column
column
column
column
column
column
column
column
column
ID heading ID
name heading Name format a40
ssn heading SSN
fa_paid1 heading 'FA Paid | &fallterm' format 99999.99
fa_paid2 heading 'FA Paid | &springterm' format 99999.99
fa_paid3 heading 'FA Paid | &summerterm' format 99999.99
fa_paid_td heading 'FA Total'
format 99999.99
ar_paid1 heading 'AR Paid | &fallterm' format 99999.99
ar_paid2 heading 'AR Paid | &springterm' format 99999.99
ar_paid3 heading 'AR Paid | &summerterm' format 99999.99
ar_paid_td heading 'AR Total'
format 99999.99
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
– [email protected]
• Velda Long
– [email protected]
• Laurie Neil
– [email protected]