automating compliance via business intelligence n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
AUTOMATING COMPLIANCE VIA BUSINESS INTELLIGENCE PowerPoint Presentation
Download Presentation
AUTOMATING COMPLIANCE VIA BUSINESS INTELLIGENCE

Loading in 2 Seconds...

play fullscreen
1 / 37

AUTOMATING COMPLIANCE VIA BUSINESS INTELLIGENCE - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'AUTOMATING COMPLIANCE VIA BUSINESS INTELLIGENCE' - elana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
automating compliance via business intelligence

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
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
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
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
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
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

Data Warehouse

Legacy Data Flow

Budget

Module

Other Views

Extract Pgm

FRS

Genledger

Crystal

Reports

Transaction

Entry

System

Extract Pgm

FRS

SubLedger

GASB View

Actuals

Encumbrances

legacy data warehouse limitations
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
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
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
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
Real-time Multi-Ledger Update

BI

Fund

Ledger

Extractor

Transaction

Entry

(Budget,

Reqs, POs

Reservations

Goods Recpt,

Invoices,

JEs, Material

Issues, etc.)

Costing

Ledger

Extractor

Grant

Ledger

Extractor

Budget

Ledger

Extractor

Actuals

Encumbrances

scope of gasb solution
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
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
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
Example - Fund and Grant Splitting

Fund and grant blank on vendor line in invoice

example fund and grant splitting1
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
Example - Fund and Grant Balancing

Transfer across funds and grants

example fund and grant balancing1
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
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
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
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
Extraction to SAP Data Warehouse

BI

Fund

Ledger

Extractor

Fund

Acctg

Items

Master

Data

Groups

Costing

Ledger

Extractor

Grant

Ledger

Items

Grant

Ledger

Extractor

Budget

Ledger

Items

Costing

Line

Items

Budget

Ledger

Extractor

Master

Data

extraction to sap data warehouse1
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
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
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
GASB Data Flow in BI

Cash flow

Cash Flow Statement Totals

Rev & Exp and

Net Assets Statement

Totals

Net Assets

Rev & Exp

SPL Totals

FM Totals

SPL & FM Totals

SPL Lines

FM Lines

building summary levels in bi
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 bi1
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
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

Johannes Lombard

SAP BI [Business Intelligence] Practice

LSI Consulting, Inc

1400 Main St

Waltham MA 02451

M     1-919-412-8616

E      jlombard@lsiconsulting.com

W     www.lsiconsulting.com

Please note: This e-mail may contain confidential and /or legal privileged information. If you are not the intended recipient or have received this mail in error please notify the sender immediately and delete this e-mail including any attachments. Any unauthorized copying, disclosure of the material in this email or of parts hereof is strictly forbidden.

Query Design Example - Statement of Net Assets
query publication options in bi
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
benefits
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
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 drill-down 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
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