Banner Financial Reporting at RHIT

1
Banner Financial Reporting at RHIT
Rose-Hulman Institute of Technology

2
Rose-Hulman Institute of Technology

Agenda

• Overview of RHIT
• Why change in reporting was necessary
• Overview of new processes
• Implementation steps
• Pros/cons of current process
• Concluding thoughts

3
Rose-Hulman Institute of Technology

Overview of RHIT

• Founded in 1874 as Rose Polytechnic Institute in
  Terre Haute, Indiana
• Engineering school with 1,870 students and
  approximately 450 employees
• Annual budget of approximately 70M and an
  endowment of approximately 187M
• Banner school since fiscal year 2000
• Currently running Banner Finance version 7.2
• Business and Finance department consists of
  eleven employees (four CPAs) and encompasses A/P,
  budget, endowment, accounting, sponsored programs
  and student financial services

4
Rose-Hulman Institute of Technology

Why change was necessary

• Process was functional but inefficient
• Reporting was a very manual process
• Statement of Financial Position (SFP) and
  Statement of Activities (SACT) were data extracts
  sorted within Excel
• Final reports were linked to the sorted data
  (error-prone)
• Process had to be repeated quarterly or for
  changes in Banner
• Other reports lacked supporting detail
• Flux analysis and troubleshooting very difficult
• Change in administration
• ODS purchase was not imminent
• Knew there had to be a better way

5
Rose-Hulman Institute of Technology

Overview of New Process
Banner Tables
Custom Tables
Internal F/S
FOAPAL Tables
SFP
Other Banner Tables
Stmt of Activities
FOAPAL Attributes
Budget Dev. Internal F/S
Microsoft Access
Pivot Table
Pivot Table Request
Excel
Table Detail
6
Rose-Hulman Institute of Technology

Overview of New Process

• Developed custom tables for existing reports
• Enhanced utilization of Banner attributes
• Developed Microsoft Access queries to custom
  tables and Banner tables
• Developed pivot tables within Excel linked to
  Access queries
• Formatted spreadsheets within Microsoft Excel
  that are linked to pivot tables
• We have essentially created a data mart

7
Implementation Steps
Rose-Hulman Institute of Technology

• Step 1 Developed Custom Tables

8
Rose-Hulman Institute of Technology

Step 1 Developed Custom Tables

• Separate tables were created for internal
  financial statements, SACT, budget development,
  and SFP
• Categories are assigned within the table for each
  FOAPAL combination based upon report criteria
• Data is compiled YTD by month by FOAPAL for each
  period for internal financial statements and SFP
• Tables contain multiple years
• Tables are updated by fiscal year utilizing
  Banner Job Submission
• Old fiscal year data is deleted and filled with
  new data
• Each table took approximately 200 hours to create
  and 40 hours to test

9
Rose-Hulman Institute of Technology

Step 1 Developed Custom Tables

• Example 1 Internal financial statements
• Report is designed to resemble for-profit
  financial results
• Previous output was paper and/or .lis (no
  detail)
• Criteria includes fund type, fund code, org
  codes, program codes, and/or predecessor accts
• Table includes
• 5 primary categories (e.g. Revenue)
• 17 secondary categories (e.g. Operating Revenue)
• 222 tertiary categories (e.g. Undergraduate
  Tuition)
• Categories are similar to Banner attributes
• For FY 2006, there are 8,197 FOAPAL combinations
  in the table

10
Rose-Hulman Institute of Technology

Step 1 Developed Custom Tables

• Example 2 Budget development internal financial
  statements
• Custom table is built upon data within FBBBLIN
• Categories assigned based upon internal financial
  statement specifications
• Each FOAPAL is assigned three categories from
  previous slide
• Includes all the Banner budget phases
• Can exclude phases that are not needed within
  pivot tables or Access
• Provides a way to compare budget development
  numbers to prior year internal f/s before budget
  is adopted

11
Implementation Steps
Rose-Hulman Institute of Technology

• Step 2 Assigned FOAPAL Attributes

12
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Banner Finance Attribute Types Overview
• Three levels of attributes are available
• Set codes provide mechanism for creating groups
  of attribute types (currently do not use at RHIT)
• Types primary attribute level
• Values optional subcategory for Types.
• The attributes can be assigned to any element of
  the FOAPAL (its your choice)
• Initially assigning attributes is a VERY manual
  process
• See Chart of Accounts Attribute Maintenance Menu
  section in the Finance Release 7.1 User Guide
  (pages 4-36 thru 4-39)

13
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Example 1 990 Reporting
• Created an attribute Type for Accounts
• 990CLASS 990 Account Classification
• Created 36 attribute Values that correspond to
  990 line items (e.g, professional fees)
• Assigned 500 revenue and expense accounts to the
  990CLASS attribute type and one of the 36
  attribute Values

14
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes
FTMATTT Where Types are created, assigned to
FOAPAL elements, and maintained
15
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes
FTMATTV Where Values are created and maintained
16
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes
FTMFATA Where Values and Types are assigned to
FOAPAL
17
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Example 2 Budget Reporting
• Created 10 attribute Types for Organizations
• Types include Institute, President, Rose-Hulman
  Ventures, V.P. - Academic Affairs, V.P.
  Admissions, V.P. - Business Finance, V.P.
  Development, V.P. Facilities, V.P. IAIT, V.P.
  - Student Affairs
• Assigned a Type to all 360 Organizations
• Created an Account attribute Type for budget
  categories
• Created 6 attribute Values and assigned to 500
  accounts
• Revenue, Endowment Income, Salary Expense,
  Benefits, Non-Salary Expense, Capital Expense

18
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Example 3 Designated and Restricted Balances
• Assigned all designated and restricted funds
  (approximately 290) to a VP according to
  attribute Types utilized in budget
• Types include Institute, President, Rose-Hulman
  Ventures, V.P. - Academic Affairs, V.P.
  Admissions, V.P. - Business Finance, V.P.
  Development, V.P. Facilities, V.P. IAIT, V.P.
  - Student Affairs
• Utilized to report current designated and
  restricted balances by VP

19
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Example 4 Temporary and permanent net asset
  purpose
• Financial statement footnote provides disclosure
  of restricted net asset purpose
• Created Attribute Type for Funds NetAsst
• Created the following attribute Values
• Academic Support, Capital, Financial Aid,
  Permanent Endowment - Income Unrestricted,
  Instruction, Operations and Maintenance, Student
  Activities, Unrestricted Investments at Maturity
• Assigned all temporary and permanently restricted
  funds to a category (approximately 750)

20
Rose-Hulman Institute of Technology

Step 2 Assigned FOAPAL Attributes

• Example 5 Statement of Financial Position
• Created attribute Types for Statement of
  Financial Position Accounts
• Accrued Expenses Accounts Payable Accrd
  Postretirement Benft Obligation
• Bonds and Notes Payable Cash and Cash
  Equivalents Cash Interfund
• Deferred Revenues Deposits with Bond
  Trustees Deposits and Other Liabilitiy
• FHITBO Government Receivable Inventory
• Invstmnts in Marketable Sec. Investments
  Other Other Assets
• Other Accounts Receivable Perm. Restricted Net
  Assets Pledges Receivable
• Property, Plant and Equipment Student Loans
  Receivable Student A/R
• Temp Restricted Net Assets Unrestricted Net
  Assets
• Assigned attribute Type to each account
• Would prefer Types to be Asset, Liability, and
  Net Assets and above to be Values

21
Implementation Steps
Rose-Hulman Institute of Technology

• Step 3 Create Access Queries

22
Rose-Hulman Institute of Technology

Step 3 Developed Access Queries

• Created queries that tied new tables and Banner
  tables
• Created queries for financial reports to include
  titles and predecessor information
• Separate queries for Internal financial
  statements, SFP, statement of activities, and
  budget development f/s
• 990 revenue and expense categories
• Internal financial statement table and 990
  account attributes
• Budget reporting by VP
• Internal financial statements, org attributes (by
  VP), and account attributes (budget)
• Designated and restricted balances
• SFP and fund attributes (by VP)

23
Implementation Steps
Rose-Hulman Institute of Technology

• Step 4 Developed Excel Reports

24
Rose-Hulman Institute of Technology

Step 4 Developed Excel Reports

• Created pivot tables within Excel that linked to
  Microsoft Access queries. Pivot tables provide
  ability to
• Sort and/or summarize large amounts of
  information
• Refresh information as custom tables are updated
  (very simple process)
• Drill down to underlying data
• Pivot tables store detail query information
  within spreadsheet (can take it with you)
• Developed formatted spreadsheets with data linked
  directly to pivot tables
• Spreadsheets often contain multiple pivot tables
  based on the information needed

25
Rose-Hulman Institute of Technology

Pros/Cons of Current Process

• Pros
• Current process provides tremendous reporting
  flexibility
• Custom tables simplify end-user experience
  (minimize Banner tables)
• No additional costs to create tables and utilize
  Microsoft tools (although there is an opportunity
  cost)
• Ability to utilize existing skill-sets (Access
  and Excel)
• Custom tables accommodate approximately 95 of
  reporting requests
• Current approach is reporting tool agnostic
• Cons
• Excel/Access are not enterprise reporting tools
• Excel does not provide way to distribute reports
  to end-users
• Some queries still utilize production database
  (FOAPAL attributes)
• You must recreate a pivot table within in Excel
  if you want to add/delete fields

26
Rose-Hulman Institute of Technology

Concluding Thoughts

• Thoroughly think through setup of attributes and
  pivot tables
• Make all adjustments within Banner (not Excel)
• Develop sufficient checks and balances to insure
  accuracy
• Create processes with the future in mind (ODS
  and/or reporting tools)
• Insure knowledge sharing is performed
• Trial and error was the best way for me to learn
  pivot tables
• Develop a process to insure attributes are
  assigned when new FOAPALs are created
• Efficiency provided opportunity to develop
  Cabinet and Board reporting package