AUTOMATING COMPLIANCE VIA BUSINESS INTELLIGENCE

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

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

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

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

5. 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.)

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

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

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

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

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

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

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

13. 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)

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

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

16. Example - Fund and Grant Splitting Fund and grant blank on vendor line in invoice

17. Example - Fund and Grant Splitting Entry split by fund in Special Purpose Ledger Entry split by grant in Grants Management

18. Example - Fund and Grant Balancing Transfer across funds and grants

19. Example - Fund and Grant Balancing Added lines balance by fund in Special Purpose Ledger Added lines balance by grant in Grants Management

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

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

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

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

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

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

26. Budget Versus Actual Data Flow in BI FM Totals FM Line Items FM InfoSources BI FM Data also supports operational budget reports

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

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

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

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

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

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

33. Reporting Using BEX Analyzer in Excel Format

34. Reporting Using Bex Analyzer in Web Format

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

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

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