Pennsylvania BANNER Users Group 2006

1
Pennsylvania BANNER Users Group2006

• Bucknells Gift Processing-Finance Relationship
  in BANNER

2
General Announcements

• Please turn off all cell phones/pagers
• If you must leave the session early, please do so
  as discreetly as possible
  
• Please avoid side conversations during the
  session
  
• Questions will be answered at the end of the
  session
  
• Thank you for your cooperation

3
Introductory Remarks

• Bucknell University
• Located in Lewisburg, Pennsylvania
• Student Enrollment (FTE) 3,600
• Annual Operating Budget 193 million
• 2006 Total Endowment 522 million
• 2006 Gift Revenue 21.2 million
• 19.8 million spendable gifts
• 1.4 million deferred giving

4
Background

• In July of 2004 Records Gift Processing (RGP)
  at Bucknell University converted to the BANNER
  system to join the rest of its campus. This
  conversion also brought an awareness to other
  offices as how their vested interests would be
  impacted, as RGP would now have a working
  relationship with all BANNER modules.

5
Background

• Before joining BANNER, RGP and Finance identified
  reconciling issues in gift revenue between what
  RGP recorded in its stand-alone database and what
  Finance had in BANNER. With RGP converting to
  BANNER, we immediately recognized the
  opportunity to reduce, and even possibly
  eliminate, these reconciling issues.

6
Background

• RGP has five full-time staff members. With one
  direct supervisor, the staff has one person
  primarily responsible for gift entry, a second
  person for biographic entry, and a third person
  for pledge and biographic entry. All three are
  cross-trained to do biographic and pledge/gift
  processing. A fourth person is primarily
  responsible for gift receipt, secondary
  acknowledgement, and pledge reminder processes.

7
Background

• What is a reconciling issue and what caused it?

8
Background

• Reconciling issues were identified at the
  designation/fund level and also broader reporting
  levels, where Advancement and Finance showed
  different amounts of money. This was a problem!

9
Background

• Reconciling issues were due primarily to gift
  adjustments that were not always recorded the
  same way in both systems. BANNER promised to
  alleviate these headaches.

10
Background

• Prior to BANNER, approximately a third of one
  person in the Finance Office was dedicated to
  gift processing with clean-up at the end of the
  year.
• Now, the Finance Office staff has divide and
  conquer approach to gift accounting. The process
  is split between the Accountant for daily
  reconciliation of clearing accounts, Associate
  Controller and Controller for oversight of
  gift/pledge processing in BANNER-Finance. In
  total, approximately half to three quarters of
  one person dedicates their time to gift
  accounting.

11
The Goal

• If, for example, Finance says it has 1,000,000
  total in the general ledger, RGP wants 1,000,000
  total in gifts (and pledge payments) recorded in
  the Advancement module and to have each
  designation/fund show equal amounts in the
  Finance and Advancement modules.

12
Building the Relationship Outside BANNER

• During conversion, RGP and Finance got to know
  each other and learn respective office outcomes
  but also agreed to commit to the overarching
  common goal of having equal charitable
  contributions recorded in both modules.

13
Building the Relationship Outside BANNER

• Advancement produces the VSE report through an
  adhoc report and do not use the VSE coding in
  BANNER.
  
• Wouldnt it be really cool if the total for VSE
  was the same, to the penny, as what the gift
  revenue is in the financial statements?!

14
General Operation

• All charitable contributions received by Bucknell
  are processed by RGP and fed to Finance using
  ADPFEED.
  
• Currently, pledges are NOT fed to Finance.
• After gifts are entered in BANNER and fed to
  Finance (daily), paper gift reports are produced
  and sent by RGP to Finance along with checks,
  cash, etcfor deposit to the bank.

15
Building the Relationship Outside BANNER

• Our relationship outside BANNER is continuous.
  As gifts and pledges are secured for new
  purposes, the offices (along with Finance),
  regularly communicate how (and sometimes if) a
  new fund can, or should, be created within the
  Universitys budget structure and considering
  donor intent.

16
Building the Relationship Inside BANNER

• The relationship inside BANNER between RGP and
  Finance comes together through five data areas
  
• 1. Designations (ADADESG)/Funds
• 2. Gift/Payment Types (ATVGIFT)
• 3. Pledge/Vehicle Codes (ATVPGVE)
• 4. Pledge Category Codes (ATVPCAT)
• 5. Pledge Conditions (AGAPCON)

17
Designations/Funds

• Designations/Funds
• Funds are created first by Finance in the
  general ledger, and then the designation is
  created in ADADESG for RGPs use.
  

18
Designations/Funds

• The designation field only allows 10 characters.
  Bucknells format is that the first character is
  a letter to represent the nature of the fund,
  such as unrestricted (U), designated (D),
  restricted (R), endowed (E), other (O), etc. The
  next six characters is the six-digit fund number
  created by Finance. The last three characters,
  if needed, as a suffix.

19
Designations/Funds

• For example, our Annual Fund is coded U110011000,
  where U means Unrestricted, 110011 is the fund
  number created by Finance as part of the FOAPAL.
  The last three characters, 000, are used but are
  not necessary to further define the designation,
  i.e., 001, 002, 003.

20
Designations/Funds

• Use of the six-digit fund number helps in
  communication between offices. When Finance has
  a question about fund number 110011, RGP can
  quickly and easily identify the fund in ADADESG
  and see both the designation code and description.

21
Designations/Funds

• An example of when we use a three-digit suffix
  within the designation code would be for our
  gifts-in-kind. All GIK designations begin with
  K126499. The three-digit suffix is then used to
  further define the purpose of the GIK.
• For example, a GIK for the Art Gallery would be
  entered to K126499001, a GIK to the Chemistry
  Department would go to K126499002, a GIK to
  Residential Life would go to K126499003, etc
• Since GIKs are fed to Finance but do not impact
  the ledger totals, we are able to record all GIKs
  to one fund number (126499).

22
Designations/Funds

• GIKS, along with Volunteer Services gifts, do not
  effect BANNER-Finance ledgers, via ADPFEED,
  because these two gift types are controlled
  through a Rule Class code on each gift type in
  ATVGIFT (Gift/Payment Types). The rule code will
  credit an account code while debiting an account
  code within the same FOAPAL thus having no effect
  to the ledger

23
(No Transcript)
24
(No Transcript)
25
Designations/Funds

• Once the designation is created, then a campaign
  code is assigned.
  
• The gift/pledge can now be entered in BANNER.

26
Gift/Payment Types (ATVGIFT)

• Gift Type codes are stored in the ATVGIFT table,
  a table owned by Finance.
  
• Bucknell uses 63 different Gift Types.
• Many Gift Types were developed based on a
  combination of how a gift can be made (check,
  credit card, cash, securities) and Vehicle Codes
  (ATVPGVE). Which is all driven by VSE reporting.

27
Gift/Payment Types (ATVGIFT)

• At Bucknell, it was decided early in the
  conversion process that Finance will own this
  table.
  
• The reason for this decision was that this table
  will manage how every transaction in
  BANNER-Alumni feeds into BANNER-Finance using the
  rule codes assigned to each gift type. Thus
  directly impacting Bucknells Financial
  Statements.

28
Gift/Payment Types (ATVGIFT)

• For example, we have a Gift Type code of CK for
  check but also a code of AK for gift
  annuity/check for VSE reporting.
  
• We also have specific codes for payroll deduction
  and matching gift as specifically defined by
  BANNER-Alumni.

29
Gift/Payment Types (ATVGIFT)

• One advantage of the exploded gift types is
  that Finance can assign a Rule Code for correct
  recording to the general ledger for each
  combination.
• Since the Rule Code code plays such a significant
  role in the Advancement-to-Finance feed process.
  (Again, ATVGIFT is owned by Finance.) Any
  additions, deletions or changes to the table are
  done as a joint effort between Finance and
  Advancement.

30
Gift/Payment Types (ATVGIFT)

• Given our use of 66 Gift Types and the Rule Class
  assigned, ATVGIFT is the key connection between
  both offices within BANNER.
  
• Finally, it is important to note that Bucknell
  decided early not to use the canned rule codes as
  delivered in BANNER. Bucknell developed their
  own rule codes while verifying with the
  action-line and a consultant the actions each
  rule code.
• (this will be further discussed later in the
  presentation)

31
(No Transcript)
32
Pledge/Gift Vehicles (ATVPGVE)

• Most of Bucknells Pledge/Gift Vehicle codes
  (ATVPGVE) are for planned giving purposes, such
  as Deferred CGA, Flexible CGA, Charitable Lead
  Annuity Trust (CLAT), Charitable Remainder
  Unitrust (CRUT), etc...
• We have a specific code for Third Party Pledge
  Payment, since BANNER requires it.

33
Pledge/Gift Vehicles (ATVPGVE)

• ATVPGVE is a table that is owned by Finance. Any
  additions, deletions or changes to the table are
  done as a joint effort between Finance and
  Advancement.

34
(No Transcript)
35
Pledge Category Codes (ATVPCAT)

• Bucknell uses three codes for Pledge Category
  Irrevocable, Revocable and Conditional.
  
• The use of these three codes is determined by
  input from Finance, Gift Planning and any other
  appropriate fund-raising office.
  
• ATVPCAT is owned by Advancement, however changes
  to this table are done with input from Finance.

36
(No Transcript)
37
Pledge Category Codes (ATVPCAT) and Pledge
Conditions (AGAPCON)

• It is within ATVPCAT that the conditional flag is
  set. At Bucknell, we decided to have a separate
  code for Conditional and check the conditional
  flag.
• If you use the conditional flag, BANNER requires
  a Condition Type to be entered on Pledge
  Conditions (AGAPCON). We simply use a type of
  Conditional and enter the terms of the condition.
  Advancement can also easily indicate with radio
  buttons on the form if the condition was met or
  not.

38
Pledge Category Codes (ATVPCAT)

• A special note about Pledge Category codes
• BANNER requires that one Pledge Category code be
  selected in the Statement of Giving and Pledge
  Rules block of the Advancement Control form
  (AGACTRL).
• You must enter a Pledge Category code for
  Unconditional Pledge Category Definition. We use
  the code for Irrevocable. Deciding which code to
  use depends on your operation.

39
How does this information feed to BANNER-Finance?
40
Impact to Ledger Gift/Contributions

• Each contribution that feeds into BANNER-Finance
  has its own rule code as defined on ATVGIFT.
  
• As each transaction posts to the BANNER-Finance,
  no transaction will post directly to a cash
  account. Each transaction posts to a clearing
  account to be reconciled at the end of each month.

41
Impact to Ledger - How does the rule code know
what to do?

• The rule codes are designed to credit/debit a
  transaction in the operating ledger while posting
  the offsetting transaction to a clearing account
  in the general ledger.
• This is managed by the process codes and
  modifiers that are behind every rule code.
  Again, we did not use the canned rule codes as
  supplied in BANNER.

42
Impact to Ledger - How does the rule code know
what to do?

• Example
• 100 cash gift from Mr. Big Shot
• A) The transaction would be
• Debit - Cash Awaiting Deposit-Alumni
• Credit - Annual Fund Gift Revenue
• B) When the check gets deposited at the bank and
  the deposit slip is returned, then an entry will
  be done is
  
• Debit Bank Cash
• Credit Cash Awaiting Deposit-Alumni

43
Impact to Ledger - How does the rule code know
what to do?

• Example
• 10,000 CGA stock gift from Mr. Big Shot
• A) The transaction would be
• Debit Stock Clearing-Alumni
• Credit Big Shot Endowment CGA Revenue
• B) When the check gets deposited at the bank and
  the deposit slip is returned, then an entry will
  be done is
  
• Debit Bank Cash
• Credit Stock Clearing-Alumni

44
Impact to Ledger - How does the rule code know
what to do?

• First we will look at the General Ledger

45
(No Transcript)
46
(No Transcript)
47
Impact to Ledger - How does the rule code know
what to do?

• Next the Operating Ledger

48
(No Transcript)
49
Reconciling

• By having the offset transaction post to clearing
  accounts, each transaction is reviewed at a high
  level for posting correctly.
  
• Many transactions are reviewed at a detailed
  level (planned giving contributions, stock gifts,
  athletic gifts, new funds held in trust by
  others, etc.) to ensure proper accounting from
  the point of receipt.

50

• XR1 Current YTD Totals Last
  YTD Totals Dollar Difference
  
• ------------------------------
  ----- ----------------------------
  -------------------
  
• I. TOTAL RECEIPTS
  
  
• Outright Gifts
  10,732,328.63 15,667,015.92
  -4,934,687.29
  
• Pledge Payments
  10,448,361.04 14,798,789.00
  -4,350,427.96
  
• Total Receipts
  21,180,689.67 30,465,804.92
  -9,285,115.25
  
• II. TOTAL PLEDGES
  
  
• Pledges Due by 6/30
  1,291,759.26 1,856,443.37
  -564,684.11
  
• III. TOTAL COMMITMENTS
  22,472,448.93 32,322,248.29
  -9,849,799.36
  
• IV. PLEDGES DUE AFTER 6/30
  77,186,434.36 65,693,459.86
  11,492,974.50
  
• 1. Bucknell Fund Unrestricted
  
  
• Total Receipts
  4,093,859.41 3,966,285.97
  127,573.44
  
• Pledges Due by 6/30
  209,742.76 84,946.39
  124,796.37
  
• Total Commitments
  4,303,602.17 4,051,232.36
  252,369.81
  
• 2. Designated Funds
  
  
• Total Receipts
  2,358,205.02 2,085,019.49
  273,185.53
  

51
Reconciling

• Go to UR to Finance June excel worksheet

52
Reporting Gift Reporting

• There are many internal and external reports that
  use the gift/contribution information
  
• Bucknells Financial Statements
• VSE Reporting
• NACUBO Endowment
• 990 Tax Exempt Reporting
• Grant/Contract Stewardship
• Etc

53
Reporting Gift Reporting

• All reporting is directly related to Bucknells
  Financial Statements. How is that accomplished?
  
  
• Standard monthly report (go to UR to Finance
  June PDF)
  
• Relationship of VSE to Financial Statements (go
  to Financial Statements to UR PDF)
  
• Relationship of XR1 (internal Advancement report)
  to Financial Statements

54
Reporting Pledges

• We have discussed in detail the gift reporting
  and reconciliations but have not touched on
  pledge reporting. To refresh our memories
  
• No pledges are fed into BANNER-Finance
• No external reporting is done except on the
  Financial Statements
  
• Since we are not in a campaign, no internal
  reporting structure has been defined regarding
  pledges. However, we are in the process of
  defining internal pledge reporting.

55
Reporting Pledges

• Periodically throughout the fiscal year pledges
  are reviewed for determination if the pledges are
  still valid or collectible. At the end of each
  fiscal year, Finance will ask management to
  review the outstanding pledges.
• From this review, allowance and discount
  assumptions are determined.

56
Reporting Pledges

• Go to PDF - 2006 Pledges Receivable
• Go to FY2006 Pledges Receivable .xls

57

• Current YTD Totals Last YTD Totals Dollar
  Difference
  
• -----------------------------------
  ------------------- -------------------
  -------------------
  
• I. TOTAL RECEIPTS
  
  
• Outright Gifts
  10,732,328.63 15,667,015.92
  -4,934,687.29
  
• Pledge Payments
  10,448,361.04 14,798,789.00
  -4,350,427.96
  
• Total Receipts
  21,180,689.67 30,465,804.92
  -9,285,115.25
  
• II. TOTAL PLEDGES
  
  
• Pledges Due by 6/30
  1,291,759.26 1,856,443.37
  -564,684.11
  
• III. TOTAL COMMITMENTS
  22,472,448.93 32,322,248.29
  -9,849,799.36
  
• IV. PLEDGES DUE AFTER 6/30
  77,186,434.36 65,693,459.86
  11,492,974.50
  
• 1. Bucknell Fund Unrestricted
  
  
• Total Receipts
  4,093,859.41 3,966,285.97
  127,573.44
  
• Pledges Due by 6/30
  209,742.76 84,946.39
  124,796.37
  
• Total Commitments
  4,303,602.17 4,051,232.36
  252,369.81
  
• 2. Designated Funds
  
  
• Total Receipts
  2,358,205.02 2,085,019.49
  273,185.53
  

58
Reporting Pledges

• Pledges impact Bucknells Balance Sheet by 16.9
  million (after discounting and allowances) versus
  accounts receivable by 4.3 million.

59
Pennsylvania BANNER Users Group2006

• Open Floor for Other Questions?

60
Pennsylvania BANNER Users Group2006

• Have a great day!