Transcript Click to add session title here

AUTOMATING COMPLIANCE VIA
BUSINESS INTELLIGENCE
GASB 34 &35 and Regulatory
Reporting
Shanhong Wang - University of Kentucky
Judy Kisil - University of Kentucky
Shyam Jajodia - LSI Consulting
University of Kentucky - Profile
• Flagship institution of the Commonwealth of KY
university system
• Over 27,000 students, 1,900 Faculty, 9,500
employees
• Medical center with two hospitals, 500 faculty
physicians, 400 resident physicians and 3,200
health professionals
• Operating expenditures of $2.1 billion, net assets of
$2.1 billion
• $290 million in grants and contracts
GASB Requirements
• Government Accounting Standards Board (GASB)
sets the accounting standards for state and local
government bodies in the United States
• Public universities in US must comply with GASB
standards to obtain a clean audit opinion
• A qualified audit opinion can affect a university’s
bond rating
• GASB 35 requires public universities to follow GASB
34
Statements Required by GASB
• Statement of Net Assets
• Statement of Revenues, Expenses and Changes in
Net Assets
• Statement of Revenues, Expenses and Changes in
Net Assets (Budget vs. Actual)
• Not required for State Universities and other
Business Type Activities
• Many universities prepare for internal purposes
• Statement of Cash Flows
Other Regulatory Requirements
• Integrated Postsecondary Education Data System
(IPEDS) report
• Commonwealth of Kentucky reporting requirements
• Report of Federally Financed Expenditures under
OMB Circular A133
• OMB Circular A-21 (Cost Principles for Educational
Institutions)
• OMB Circular A-110 (Uniform Administrative
Requirements for Grants etc.)
Legacy Environment
• The University used SCT’s FRS system and several
other accounting systems as data sources
• A custom-designed data warehouse was used to
integrate data
• Custom programs were used to extract data from
each system and to filter and process it
• The data was summarized to the appropriate level
using custom programs
• Crystal Reports and other tools were used for
reporting
Legacy Data Flow
FRS
Extract Pgm
SubLedger
Actuals
Encumbrances
Other Views
FRS
Extract Pgm
Genledger
Crystal
Reports
GASB View
Transaction
Entry
System
Data Warehouse
Budget
Module
Legacy Data Warehouse Limitations
• Budget data not in the GASB view
• Budget versus actual statements produced in a
separate view
• Required reconciliation between GASB view and
budget reporting view
• A-133 reporting performed in separate Foxpro
system
• Required reconciliation between GASB and A133
reporting
Legacy Data Warehouse Reporting
• No double-click drill-down
• Each row and column needed reconciliation to
the detail line items
• Users ran reports to manually reconcile
summary data to the detail
• Totals were fiscal year specific and required
creation of reconciliation reports by fiscal year
Legacy Environment Maintenance
• Programming required to change extraction
programs to enhance data quality
• Programming required to make changes to data
structures
• Table structure changes
• Data Maintenance Program changes
• Formatting based on tags requiring understanding of
the mark-up language to change formats
Business Objectives of ERP System
Implementation
•
•
•
•
•
•
•
•
Gain better access to timely information
Improve decision making across the organization
Improve compliance across the enterprise
Reduce inappropriate variations in business
processes across the enterprise
Provide greater flexibility for business unit processes
Provide cost control for applications, database,
consulting and hardware
Improve support for administrative functions
University-wide
Support evolving models of internet-based
transactions
Real-time Multi-Ledger Update
Transaction
Entry
(Budget,
Reqs, POs
Reservations
Goods Recpt,
Invoices,
JEs, Material
Issues, etc.)
Actuals
Encumbrances
Fund
Ledger
Extractor
Costing
Ledger
Extractor
BI
Grant
Ledger
Extractor
Budget
Ledger
Extractor
Scope of GASB Solution
• Required by GASB
• Statement of Net Assets
• Statement of Revenues, Expenditures and
Changes in Net Assets
• Statement of Cash Flows
• Other
• Statement of Revenues and Expenditures and
changes in Net Assets (Budget versus Actual)
• Hospital Monthly Financial Statements
• Report of Federally Financed Expenditures
(A133)
SAP Modules Used
• Special Purpose Ledger – Provides data for main
financial statements
• Statement of Net Assets
• Statement of Revenues and Expenditures
• Statement of Cash Flows
• Funds Management – Provides data for budgetary
basis revenue and expenditure statements
• Grants Management – Provides data for sponsor and
other grant reporting
• Controlling – Provides data on work order cost
allocations and capital projects
SAP Transaction Processing
• Data update to ledgers is on-line and real-time
• General ledger can only be updated through a
subsidiary ledger
• Requistions, purchase orders and other entries
encumber or consume budget immediately
• Lack of sufficient budget can trigger online error
(used for grants at UK)
• Funds and grants are balanced online in real-time
Example - Fund and Grant Splitting
Fund and grant blank on vendor line in invoice
Example - Fund and Grant Splitting
Entry split by fund in Special Purpose Ledger
Entry split by grant in Grants Management
Example - Fund and Grant Balancing
Transfer across funds and grants
Example - Fund and Grant Balancing
Added lines balance by fund in Special Purpose Ledger
Added lines balance by grant in Grants Management
Cash Flow Statement by Direct Method
• Uses the Claim on Cash account (account 110000)
• This account is posted when any fund balance
changes and when cash is received or paid
• Creating the cash flow statement is as simple as
analyzing the debits and credits to this account
• Double-click drill-down to detail transactions allows
detailed analysis of individual transactions where
needed
Other Online Processing Improvements
• Available Budget information is up-to-date
• Users can drill-down to original document
• Errors due to invalid or expired accounts are
communicated to user immediately for correction
• Well designed error messages can help educate
users
• Overall compliance is improved
Reporting Improvements In the Transaction
System
• Most reports allow drill-down (double-click drill-down
to original posted document)
• It is also possible to drill-through (access information
linked to original document such as check linked to
an invoice)
• Drill-through provides access to complete audit trail
on a document including user, entry date and time,
approvals received, changes made, etc.
Extraction to SAP Data Warehouse
Fund
Ledger
Costing
Ledger
Extractor
Fund
Acctg
Items
Extractor
Grant
Ledger
Items
Master
Data
Groups
BI
Grant
Ledger
Budget
Ledger
Extractor
Extractor
Budget
Ledger
Items
Costing
Line
Items
Master
Data
Extraction to SAP Data Warehouse
• Minimal programming is required to use standard
data extractors (only to add fields etc.)
• Most data is extracted on a delta (changes since last
extract) basis to speed extraction process
• All transaction data for the fund, grant and budgetary
ledgers are extracted at the individual line item level
• Line items are kept in the data warehouse to provide
drill-down to the line item level
• Master data such as general ledger accounts, funds,
grants, fund centers, etc. and master data groupings
(hierarchies) are also extracted
Fund Accounting Data Flow in BI
SPL Summary Totals
SPL Line Items
SPL Infosource
Annuals Balances Data Source
FI Line Item Data Source
BI SPL Data also supports operational GL reports
Budget Versus Actual Data Flow in BI
FM Totals
FM Line Items
FM InfoSources
BI FM Data also supports operational budget reports
GASB Data Flow in BI
Cash Flow Statement Totals
Cash flow
Net Assets
SPL Totals
Rev & Exp
Rev & Exp and
Net Assets Statement
Totals
FM Totals
SPL & FM Totals
SPL Lines
FM Lines
Building Summary Levels in BI
• BI allows calculation of totals (called Key Figures)
using rules and formulae
• Key figures were built to address common reporting
requirements such as:
• Total debits
• Total credits
• Year to date
• Cumulative balance
• Etc.
Building Summary Levels in BI
• In addition, specialized totals were built to
calculate amounts for specific lines in GASB
statements
• Approximately 200 such totals were created for
the solution
Example - Rule for Building Specialized Totals
Summary Total NCNFOTHGIFT
Rule
Total of GL accounts 420201 and 422203
for Fund Range 0210000000 to 02199999999
and Fund Range 0710000000 to 0759999999
excluding funds 0213804500 and 0213465100
Query Design Example - Statement of
Net Assets
Query Publication Options in BI
• Query can be saved with a unique technical ID and
• Executed in WEB format or EXCEL format in the BEx
Analyzer reporting tool
• Can be shown on the Web in a default view. To do
this, you can simply choose Display Query on the
Web.
• Can be used in the Web Application Designer tool as
a data provider for Web applications
• Query can also be displayed in the tabular display in
Crystal Reports as a data source for formatted
reports
• In the NW7BI release it can be printed as a .pdf file
Reporting Using BEX Analyzer in Excel
Format
Reporting Using Bex Analyzer in Web
Format
Benefits
• One solution to meet multiple reporting
requirements reduces Total Cost of Ownership
(TCO)
• Line items in BI provide complete audit trail for
investigation of errors and misclassifications
• Production of GASB financial statements is almost
fully automated
• Inclusion of budget data facilitates monthly financial
statements for Hospital
• Statements can be generated and reviewed daily
• Frequent review improves data integrity and
compliance
Lessons Learned
• Master data (especially general ledger account)
structure must be designed with GASB reporting in
mind
• Line item detail must be in BI as users want to drilldown to SPL and FM Line Items
• Master data and accounting practices can be
changed iteratively after viewing reports
• Coding for rules can be reduced and refined by
creating user-maintained tables (NW7BI release)
• Need training on data definitions and interpretations
Lessons Learned …Cont’d
• Differences in update between Special Purpose
Ledger, and Funds Management must be
understood
• Will all expenses that update SPL also update
FM?
• At what point will expenses be recorded in FM?
(GR/IR update configuration)
• Financial statements accountant must work directly
with the BI consultant
• GASB design must be developed by a consultant
with a public sector financials background