BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE

1. BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE PRESENTED BY KENNETH BRIDGES & DAWN GAMADANIS KENNESAW STATE UNIVERSITY

2. Agenda • Session 1 – Learn about SAS® KSU Financial Data Mart (FDM) • Discussion/Questions • Break • Session 2 – Budget Development and Position Management • Discussion/Questions • Break • Session 3 – Comprehensive Financial Reports and Analytics • Discussion/Questions • Contact Us

3. University Facts • 24,600 Students • $347m Budget with over 600 department accounts • 2,200+ employees • Five Major Divisions • Eight Academic Colleges

4. Purpose and Objectives

5. STATEMENT OF PURPOSE Business intelligence is about effectively managing critical data assets and its transformation into information in such a way that drives strategic planning, executive decision-making, and management reporting throughout your organization. Thus, data drives our need to be well-informed. The statement of purpose is about pursuing continuous improvement to be well-informed, to provide self-servicingof information on the desktop, and positively effect decision support at all levels of the organization.

6. CASE STUDIES Grants Post-Award office spent routinely up to two weeks, sometime longer, to data enter and reconcile grants in QuickBooks and compile reports to send to Principal Investigators. Business Managers ran multiple PeopleSoft reports and queries daily, data enter into QuickBooks, to reconcile accounts and summarize information into reports for Deans and Chairs. Budget and Planning Office spent most of time in data entry mode for Budget Prep instead of analyzing the data; lacked ability to track position funding changes during the year and unable to provide personal service changes to department managers.

7. FACTOID With over 600 local public queries plus hundreds of private ones to choose from or model from more than 300+ BOR delivered queries, pulling data from PeopleSoft can be somewhat challenging for most users to navigate which on occasion results in…well, you know

8. KEY OBJECTIVES • Establish a robust, scalable, and practical alternative to meeting KSU’s institutional demands for enterprise intelligence and analytics • Provide easy access to financial information and analytics for decision support, strategic initiatives, and executive management reporting • Provide self-service functionality for users to build, customize, and share reports with common data dictionary, dimensions, and measures through a secure portal

9. Focus on Customer Needs

10. LISTEN TO OUR CUSTOMERS CUSTOMER-FOCUSED OBJECTIVES

11. KSU COMMON CHALLENGES • ERPs lack robust reporting tools and analytics • Queries are hard to learn and very often require exporting to Excel for further aggregation and analyses • Shadows systems proliferate as a means to track and reconcile accounts for business managers and grants post-award office • Information not easily shared and data not securely distributed • Inability to obtain revenue, expenditure, and budget data at multiple organizational levels • Budget managers were being held accountable but lacked adequate tools to track budgets and spending • PeopleSoft terminology oftentimes misunderstood – what’s in pre-encumbrance as opposed to an encumbrance? Why should I care? • Inability to set aside/reserve funds for future spending and have it reflected in remaining balances

12. DATA ENABLERS Accessibility To multiple (unlimited) data sources + Transparency For common version of the truth + Consistency To strengthen data quality and data integrity + Safeguard To secure data for confidentiality on a need to know basis + Ease of Use Transition is intuitive and familiar

13. Introduction ofFinancial Data Mart (FDM)

14. FDM TIMELINE

15. FDM SOURCE DATA OTHER DATA SOURCES

16. FDM SCHEMA PROCESS * Nightly Refresh Monday - Friday

17. FDM ETL PROCEDURES

18. FDM USER PERSPECTIVES

19. FDM DATA PERSPECTIVES

20. FDM DATA DIMENSIONS

21. KSU FDM MODEL

22. Cubes, Hierarchies and Measures

23. FDM OLAP CUBES BUDGET PREP BUDGET GLOBAL • Budget Prep • ADP Code Exceptions • Aggregate Detail • Fringe Exceptions • Grant Positions • Lump Sum Variances • OBP Fund by Expense • OBP Fund by Function • Personal Services • Position Counts • Position Employee Exception • Budget Position Management View (new) • Comprehensive Financial Report (CFR) Global • CFR Budget Global • CFR Encumbrance Global • CFR Expenses Global • CFR Payroll Global • CFR Personnel Services Global • CFR Revenue Global

24. FDM SAMPLE OLAP CUBES FINANCIAL SECURE FINANCIAL GLOBAL • Comprehensive Financial Report (CFR) Secure • CFR Budget Secure • CFR Encumbrance Secure • CFR Expenses Secure • CFR Payroll Secure • CFR Personnel Services Secure • CFR Revenue Secure • Vendor Risk Payments Secure • Transportation Secure • Financial Encumbrance (Fraud Detection) • Vendor Risk Payments Global • Transportation Global GRANTS • Grants Cube Global • Grants Cube Secure

25. FDM SAMPLE HIERARCHIES

26. FDM DATA MEASURES Budget Financial • Sum of Budget Amount • Sum of Proposed Revenue • Sum of Proposed APPROP • Sum of Proposed ORG • Sum of Proposed Budget • FTE Counts • Sum of Current Salary • Sum of Temporary Amended • Sum of Permanent Amended • Sum of Next Year Base Budget • Sum of Total Budget Amount • Sum of Total Fund Balances • Sum of Total Expenses • Sum of Pre-encumbrance Amount • Sum of Encumbrance Amount • Sum of Expended Amount • Sum of P-Card Amount • Sum of Reserve Amount (Departmental Set-Asides) • Sum of Free Balance • Sum of Payroll Expenses

27. FDM DATA MEASURES Financial • Sum of Personal Services • Sum of Non-Personal Services • Sum of Revenue Amount • Risk Transaction Count • Sum of PO Quantity • Sum of Quantity Received • Sum of Quantity Remaining

28. FDM DATA MEASURES BUDGET PREP • Sum of APPROP Variance • Sum of Current APPROP • Sum of Current Grant • Sum of Current ORG • Sum of Current Revenue • Sum of Current Salary • Distributed Proposed Salary • Sum of Equity Adjustment • Distinct Count of FTE • Sum of Total Fund Balance • Sum of FTE • Sum of Grant Variance • Sum of ORG Variance • Sum of Proposed APPROP • Sum of Proposed Budget Salary • Sum of Proposed Fringe Estimate • Sum of Proposed Grant • Sum of Proposed ORG • Sum of Proposed Revenue • Sum of Proposed Employee Salary • Sum of Revenue to Expense Variance • Sum of Revenue Variance • Sum of Supplement Amount • Sum of Total Expenses • Sum of Distributed Salary • Sum of Distributed Budget

29. Statistics

30. FDM USER ACCOUNTS

31. FDM PROJECT PORTFOLIO

32. FUNCTIONAL USER ORGANIZATION

33. DISCUSSION/QUESTIONS Kenneth Bridges Director Enterprise Financial Reporting kbridges@kennesaw.edu 770-499-3422 Dawn Gamadanis Director Budget and Planning dgamadan@kennesaw.edu 770-499-3293