PRESENTED BY KENNETH BRIDGES DAWN GAMADANIS KENNESAW STATE

PRESENTED BY KENNETH BRIDGES & DAWN GAMADANIS KENNESAW STATE UNIVERSITY

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

� 24, 600 Students �$347 m Budget with over 600 department accounts � 2, 200+ employees �Five Major Divisions �Eight Academic Colleges

Purpose and Objectives

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 selfservicing of information on the desktop, and positively effect decision support at all levels of the organization.

Grants Post-Award office spent routinely up to two weeks, sometime longer, to data enter and reconcile grants in Quick. Books and compile reports to send to Principal Investigators. Business Managers ran multiple People. Soft reports and queries daily, data enter into Quick. Books, 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.

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 People. Soft can be somewhat challenging for most users to navigate which on occasion results in…well, you know

�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

Focus on Customer Needs

Enhance and streamline budget development and reporting Ability to perform financial analytics for budget and planning Easy-to-use self-service reporting tools for enterprise Manage financial data integration to better enable decision support Support budget management tracking and accountability Promote data quality and integrity with “one version of the truth”

� 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 � People. Soft 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

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

Introduction of Financial Data Mart (FDM)

2010 Financial Data Mart build began (ETL Loads, Tables, Metadata) 2011 Initial Cubes and Information Maps (metadata) developed 2012 Released Comprehensive Financial Report (CFR) and Budget Preparation report set 2013 Released Position Management and Vacancy Lag report set 2013 Released Grants (Sponsored) report set 2013 Released Fraud Detection (encumbrances) report utilizing Forecast Server 2014 Planned release of new Key Performance Metrics dashboard analytics 2014 Planned completion of CFR Enhancements

ADP People. Soft Financials Banner OTHER DATA SOURCES FINANCIAL DATA MART

EXTRACT (PSFIN Table Copy/Link to Banner and ADP Schema) TRANSFORM LOAD OLAP (Apply Rules and Filters) (Detail Data Store) (Cube Rebuilds) * Nightly Refresh Monday - Friday

ETL Procedure Process Source Data A - Foundation Tables Copy Account, Department, Fund, Class, Program, Project Grants tables B - Financial KK Copy KK Budget, Ledger KK, KK Activity, KK Source, KK Referenced tables C – GL Tables Copy Pers. Serv. BOR, JGEN BOR Tables, GL Journals, Ledger tables D – Other PS Tables Copy Expense Sheet, PO, Receiver, Requisition, Speed Chart, Speed Type tables E – Accounts Payable Copy Voucher, Line, Acctg Line, Payments, Payment Voucher XREF, Vendor tables F – Budget Preparation Copy All Budget Prep tables G, H 1 – H 3, J, M Transform Local copied source FIN tables L – Position Management Package Local source FIN tables & ADP DW Schema P – Integration Push data From FDM to other database systems

User Groups Data Perspective Budget and Planning Budget, Actuals, Positions – Budget Global, Financial Global Finance and Accounting Procurement and Contracting Actuals – Financial Global Internal Audit Office of Research – Post Awards Business Affairs Managers (Academic Units) Deans and Chairs (Academic Units) Administrators and Department Managers Budget, Actuals, A/R Sponsored – Grants Secure Budget, Actuals, Positions – Financial Secure

• • BUDGET DATA EMPOYEE DATA JOB DATA PAYROLL DATA POSITION DATA BUDGET PREP DATA POSITION MANAGEMENT DATA • • BUDGET DATA DIRECT REVENUE A/R DATA EXENSES DATA EMPLOYEE DATA PAYROLL DATA INDIRECTS DATA • • • BUDGET GLOBAL GRANTS SECURE FINANCIAL GLOBAL FINANCIAL SECURE • • • JOURNAL DATA A/R DATA VENDOR DATA VOUCHER DATA PO AND RECEIVER DATA PAYROLL DATA REQUISITION DATA KK TRANSACTION LIFE-CYCLE DATA P-CARD DATA AGENCY FUND SHIPMENT DATA BUDGET DATA REVENUE DATA EXPENSES DATA ENCUMBRANCES DATA • FUND BALANCES • VENDOR SPEND DATA • USER SECURITY DATA

• • • BUDGET PERIOD FISCAL YEAR QUARTER ACCOUNTING MONTH TIME • • • POSITION NUMBER EMPLID JOB CODE PAYGROUP ACCOUNT CODE EARNINGS CODE POSITION/EMPLOYEE DIVISION UNIT DEPARTMENT FUND PROJECT ID CLASS PROGRAM ACCOUNT ORGANIZATION DESCRIPTIVE NAMES • • • ACCOUNT NAME MANAGER NAME DEPARTMENT NAME EMPLOYEE NAME PROGRAM FUNCTION FUND NAME DIVISION NAME CLASS NAME ACCOUNT ROLLUP NAME PROJECT NAME POSITION TITLE

Budget and Planning Fact Book and Surveys • Position Management • Budget Preparation • Budget to Actual • Financial Profiles • Survey Data Financial Services/ Procurement and Contracting • Vendor Profiles • Vendor Risk • Open Requisitions/Purchase Orders • Vendor Spend Comprehensive Financial Reports (CFR) Model Risk Management • Internal Audit • Unit Assessments • Fraud Detection • • Division/Units Budget to Actuals Vendor Spend Proposed Budgets Position Amendments Inventory Management • Distribution Analytics Contract and Grants • Private Investigators Reports • Grant Accountants & Control Reports • Budget to Actuals • Spend Activity • Payroll Activity

Cubes, Hierarchies and Measures

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

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

DIMENSION PERSPECTIVE HIERARCHY FISCAL YEAR FINANCIAL FISCAL YR/DIV/DEPT/FUND/PROJNM/ACCT ROLLUP/ACCT NAME FISCAL YR/FUND/CLASS/ACCT ROLLUP/ACCT NAME/DEPT FISCAL YR/DIV/DEPT/FUND/CLASS NM/PROJ NM/ACCT ROLLUP/ACCT NAME FISCAL YR/FUND/CLASS NM/ACCT ROLLUP/ACCT NAME/DEPT FISCAL YR/FUND/DEPT/CLASS NM/PROJ NM/ACCT ROLLUP/ ACCT NAME BUDGET PERIOD/FUND/CLASS/DEPT BUDGET PERIOD/DIV/DEPT/FUND BUDGET PERIOD/POS NBR/EMPLID/EMP NAME/ACCT CD PROJECT NAME GRANTS PROJ NM/BUD REF/ACCTG PERIOD/BUD ACCT TYPE/ACCT NAME PROJ NM/BUD ACCT TYPE/ACCT ROLLUP/ACCT NAME

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 Expended Amount �Sum of P-Card Amount �Sum of Reserve Amount (Departmental Set-Asides) �Sum of Free Balance �Sum of Payroll Expenses

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

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

Statistics

400 350 345 292 300 250 200 Last Year 150 This Year 100 50 0 16 Total users * (+34%) 20 Department Business Mgrs Project Mgrs & staff (+23%) (+233%) (+29%) * 3 Global Users

Accountability Regulatory Strategic Support 77 Total Active Projects

Budget and Planning Finance and Accounting Risk Management Office of Research (Grants Post-Award) Procurement and Contracting Others

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