12274

1© 2015 ELLUCIAN. CONFIDENTIAL & PROPRIETARY | Session ID 12274

Tips to Resolve an Out of Balance Condition

in Banner Finance

Jang Kwon, M. Comp. Sc.

Concordia University, Montreal, Canada

Monday, April 13, 2015

11:30 – 12:30 PM CST

Session ID 12274


Session rules of etiquette

• Anything that rings: turn OFF or set to silent/vibrate

• Silence is golden: Please, no side conversations

• If you must text, please do it outside the room

• Leaving early? Please do so discreetly

• During Q & A, please use the microphone so we can all hear the question

Thank you for your cooperation!


Introduction

• Accounts Payable out of balance is a dreaded incident

• Many hours/days/weeks spent trying to track down culprit

• Session will shed light on:

Accounts payable architecture in Banner Finance

Out of Balance examples and their fixes

Troubleshooting Tips


Introduction

• Accounts Payable out of balance is a dreaded incident

• Many hours/days/weeks spent trying to track down culprit

• Session will shed light on:

Accounts payable architecture in Banner Finance

Out of Balance examples and their fixes

Troubleshooting Tips

A more educated, structured approach

to tackling the problem


Presentation Contents

• What is “Out of Balance”?

• Accounts Payable Architecture

• Possible Causes for Accounts Payable Out of Balance

• Examples

• Troubleshooting: 10 Steps

• Tips

• Summary

• Questions & Answers


What is “Out of Balance”?

Definition & Types(Yes, there is more than one type)


Out of Balance Definition

• Accounts Payable is considered “out of balance” when the totals do not match between:

Open invoices

General ledger’s accounts payable account

• Out of balance conditions also exist for:

Encumbrance Control (Open encumbrances ≠ GL encumbrance accounts)

Revenue Control (Operating acct ledger ≠ GL revenue/expend/transfer accts)

Accounts Payable is the most common

out of balancetype


FGRCTRL Control Report

• FGRCTRL (in out of balance mode) will show:

Overview (i.e. totals) of current balance state (page 1)

General ledger

Open invoices

Operating account ledger

Each fund code found to be out of balance (remaining pages)

This will help isolate where the discrepancy is


FGRCTRL Report

• Enter parameters:

• Current Fiscal Year

• Report Option Code 3 (i.e. out of balance mode)

• Chart of Accounts code

• Say ‘Y’ to recreate collector table


FGRCTRL Report – In Balance

REPORT FGRCTRL MORIARTY UNIVERSITY RUN DATE: 13/04/2015COAS: X GL / Subsidiary Ledger Control TIME: 11:00 AMFISCAL: 15 PAGE: 1

OPERATING ACCT OPEN OPENACCT. ACCT. TITLE GENERAL LEDGER LEDGER ENCUMBRANCES INVOICES BAL

80000 ACCOUNTS PAYABLE <2,704.50> <2,704.50>

90000 BUDGETED REVENUE CONTROL <1,000.00> <1,000.00>

90010 BUDGETED EXPENDITURE CONTROL <1,000.00> <1,000.00>

90030 BUDGETED TRANSFER CONTROL 1,000.00 1,000.00

91000 REVENUE CONTROL <1,000.00> <1,000.00>

91010 EXPENDITURE CONTROL 1,000.00 1,000.00

91020 TRANSFER CONTROL <1,000.00> <1,000.00>

90040 BUDGETED RESERVATION CONTROL 249,999.77 249,999.77 249,999.7792000 ENCUMBRANCE CONTROL 750,000.23 750,000.23 750,000.23 ---------------------------------------------------------------------- --- TOTALS 1,000,000.00 1,000,000.00 1,000,000.00

90050 BUDGETED RESERVATION RESERVE <249,999.77>92010 ENCUMBRANCE RESERVE <750,000.23> ------------------ TOTALS <1,000,000.00>


FGRCTRL Report – Out of Balance



80000 ACCOUNTS PAYABLE <2,704.50> <2,754.50> E




91000 REVENUE CONTROL <1,000.00> <1,000.00>





Flag to indicate out of balance


FGRCTRL Report



80000 ACCOUNTS PAYABLE <2,704.50> <2,754.50> E




91000 REVENUE CONTROL <1,000.00> <1,000.00>





Difference of $50.00 between Accounts Payable account and total of open invoices


Accounts Payable Architecture

How Accounts Payable is setup in Banner Finance


Banner Finance 101: Accounts Payable Architecture

New Invoice



New Invoice

OpenApprovedCompleted



New Invoice

Posting

General Ledger

Accounts Payable Account

ADD to sum total of all open invoices




New Invoice

Posting

General Ledger




Time passes

Check run



Posting

General Ledger



New Invoice


Time passes

Check runPosting

General Ledger


SUBTRACT from sum total of all open invoices


Banner Finance 101: Back End AP Architecture

• Open Invoices total rely on database tables

FABINVH: Invoice Header Table

FARINVA: Invoice Accounting Table

FARINTX: Invoice Tax Allocation Table

• Accounts payable account entries in transaction detail table

FGBTRND: Transaction History Detail Table

FGBTRND_LEDGER_IND = ‘G’ (i.e. General Ledger)

New Invoices


General Ledger



Possible Causes for Accounts Payable Out of BalanceThe cause from the previous time may NOT be the cause this time.


Areas To Consider When Out of Balance

New Invoice

Posting

General Ledger




Time passes

Check runPosting

General Ledger


SUBTRACT from sum total of all open invoices invoice

flags



New Invoice

Posting

General Ledger




Time passes

Check runPosting

General Ledger



invoice itself



New Invoice

Posting

General Ledger




Time passes

Check runPosting

General Ledger



Posting invoices or

checks



New Invoice

Posting

General Ledger




Time passes

Check runPosting

General Ledger



Check cancellations


Subjective Causes

• Out of Balance condition may occur due to:

• Changes to invoice flags: open, completed, approved, paid, cancel

• Posting disruptions (accounts payable account does not get updated)

• Check cancellations (some cases)

• Changes from unexpected, unintended, or unorthodox methods

• Changes made through normal processing should balance


Examples

What our institution learned – the hard way!


Example # 1: Invoice Cancellations

• Invoice cancellation cases:

cancellation not posted

invoice cancelled but still reflected in Accounts Payable account

Solution: recreate invoice records and re-do invoice cancellation process

cancel amount ≠ original amount

Solution: Submit JV reflecting difference amount using JE15 rule code


Example # 2: Check Payments & Cancellations

• Check payment will mark invoice paid, reducing AP account

Check does not get posted, payment not reflected on AP side

Solution: investigate check or check run

• Check cancellation: re-open invoice, add back to AP account

Cancelling zero check did not re-open invoice

Solution: Set invoice flag from “Paid” to “Open” via SQL statement


Example # 3: Posting is stuck

• New automated process to create invoices blocked posting queue

• Offending documents removed so posting could continue


Example # 3: Posting is stuck

• New automated process to create invoices blocked posting queue

• Offending documents removed so posting could continue

• Out of balance next morning

• Invoice side contained invoice information

• Accounts payable account did not as invoices not posted

• Solution: Fix and repost documents to get back into balance


Example # 4: Back-end invoice clean up

• Preparing for year-end: close about 30 small, open invoices

• Status flags changed from open to closed in FABINVH, FARINVA


Example # 4: Back-end invoice clean up

• Preparing for year-end: close about 30 small, open invoices

• Status flags changed from open to closed in FABINVH, FARINVA


• Invoice side up to date

• Accounts payable account not updated to reflect invoice closing

• Solution: Re-open invoices, close them properly one by one


Example # 5: Revenue Control Out of Balance


• FGRCTRL control report indicated revenue control balance issue

• Solution (via back-end SQL):

Extract GL lines for fund code from FGRCTRL (FGBTRND_LEDGER_IND = ‘G’)

Extract operating ledger lines for same fund code (FGBTRND_LEDGER_IND = ‘O’)

Compare both sets of data to locate difference


Troubleshooting: 10 Steps

Order of steps required to take action!


Troubleshooting: 10 Steps

When out of balance condition appears, ask “What Changed?”:

1. Any technical, system-level disruptions (server, CPU, database)?

2. Any check cancellations?

3. Any check run issues?

4. Any posting queue problems?

5. Any invoice entry problems (manual and automated)?


Troubleshooting: 10 Steps (continued)

6. Any back-end SQL modifications to FABINVH, FARINVA?

7. Get GL list: list of open invoices charged to AP account

8. Get Open Invoice list: list of open, completed, approved invoices

9. Fields: document number (alphabetical order), invoice amount

10. Compare document counts between lists. If the counts:

DIFFER: document(s) not in both lists causing out of balance

MATCH: reconcile amounts for each document. At least one will not match.


Tips

Ways to decrease the agony of troubleshooting!


Tip # 1: Run Control Reports Daily

• Run FGRCTRL as part of other daily/nightly control reports

• This will limit suspect transactions to last 24 hours

• FGRCTRL will narrow down to fund code(s) out of balance

• Use fund code(s), checking Activity Date fields for:

Invoices

Checks

Accounts payable account (in transaction detail)


Tip # 2: Have a Clean Control Report

• In balance definition:

• Accounts payable account = sum of all open invoices





• Sum of all imbalances = 0





• Sum of all imbalances = 0

• Same fund codes will always show when FGRCTRL is run

• A burden when trying to resolve new out of balance case

• Get rid of all imbalances = clean control report

• Problem fund(s) will reflect latest out of balance situation


Tip # 3: Eliminate posting issues possibility

• Were there any posting queue issues?

• Perhaps invoice was caught in crossfire and not posted

• Check with DBA, technical staff if any posting issues found

• Check FOIAPPD screen: document may be waiting to post


Tip # 4: FGIGLAC Screen

• FGIGLAC is the General Ledger ACtivity screen

• Can shows all accounts payable transactions from front-end

• Enter Fund Code from FGRCTRL, AP account code, then look for either:

o The exact out of balance amount

o Half the out of balance amount

o Twice the out of balance amount


Tip # 5: Beware the Back-end SQL

• Change of invoice flags using SQL will trigger out of balance

• Documents did not go through proper posting process

• Consider out of balance repercussion when doing mass change


Tip # 6: Address Out of Balance Right Away

• System can run out of balance but that is not good practice

• Although a tedious digging exercise, strive to be in balance

• Out of balance issues must be resolved when closing fiscal year

• Avoids audit questions


Summary

• Check for out of balance condition daily

• Clean FGRCTRL report pinpoints error

• Back-end SQL fixes may impact balance

• Ask: “What changed?” e.g. cancelled checks

• Leave no stone unturned

• “Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth.” – Sherlock Holmes


Questions & Answers

• Questions?

• Elaboration?

• If I don’t know the answer, leave me your coordinates after the session: I will research and get back to you.


Thank You! Enjoy the rest of ellucian LIVE & The Big Easy!

Jang [email protected] complete the online session evaluation form. • Via your registration account Log into your registration account

at https://ellucianlive2015.smarteventscloud.com/connect/publicDashboard.ww

• choose the link to “Surveys”.”

Session ID 12274

12274

Documents

balance examples

balance type

balance conditions

introduction accounts

culprit session

pm cst session id

fgrctrl control report

current balance state