BUDGET AND FINANCIAL REPORTING - PowerPoint Presentation

Slide1

BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE

PRESENTED BYKENNETH BRIDGES& DAWN GAMADANIS KENNESAW STATE UNIVERSITY Slide2

AgendaSession 1 – Learn about SAS® KSU Financial Data Mart (FDM)Discussion/QuestionsBreakSession 2 – Budget Development and Position ManagementDiscussion/Questions

BreakSession 3 – Comprehensive Financial Reports and AnalyticsDiscussion/Questions

Contact UsSlide3

University Facts24,600 Students$347m Budget with over 600 department accounts2,200+ employeesFive Major DivisionsEight Academic CollegesSlide4

Purpose and ObjectivesSlide5

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

CASE STUDIESGrants 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. Slide7

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

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

Focus on Customer NeedsSlide10

LISTEN TO OUR CUSTOMERS

CUSTOMER-FOCUSED OBJECTIVESSlide11

KSU COMMON CHALLENGESERPs lack robust reporting tools and analytics Queries are hard to learn and very often require exporting to Excel for further aggregation and analysesShadows 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 distributedInability 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 spendingPeopleSoft 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

balancesSlide12

DATA ENABLERSAccessibilityTo multiple (unlimited) data sources+TransparencyFor common version of the truth+ConsistencyTo strengthen data quality and data integrity+SafeguardTo secure data for confidentiality

on a need to know basis+Ease of UseTransition is intuitive and familiarSlide13

Introduction ofFinancial Data Mart (FDM) Slide14

2010Financial Data Mart build began (ETL Loads, Tables, Metadata)2011Initial Cubes and Information Maps (metadata) developed2012

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

FDM TIMELINESlide15

FDM SOURCE DATAOTHER DATA SOURCESSlide16

FDM SCHEMA PROCESS* Nightly Refresh Monday - FridaySlide17

FDM ETL PROCEDURESETL ProcedureProcessSource DataA - Foundation TablesCopy

Account, Department, Fund, Class, Program, Project Grants tablesB - Financial KKCopy

KK Budget,

Ledger KK, KK Activity, KK Source, KK Referenced tables

C – GL Tables

Copy

PersServBOR

,

JGEN BOR Tables, GL Journals, Ledger tables

D – Other

PS Tables

Copy

Expense Sheet, PO, Receiver,

Requisition, Speed Chart, Speed Type tables

E – Account

s Payable

Copy

Voucher, Line,

Acctg

Line, Payments, Payment Voucher XREF, Vendor tables

F – Budget Preparation

Copy

All Budget Prep tables

G, H1

– H3, J, M

TransformLocal copied source FIN tablesL – Position ManagementPackageLocal source FIN tables & ADP DW SchemaP – IntegrationPush dataFrom FDM

to other database systemsSlide18

FDM USER PERSPECTIVESUser GroupsData PerspectiveBudget and PlanningBudget, Actuals, Positions – Budget Global, Financial Global

Finance and AccountingActuals – Financial GlobalProcurement and Contracting

Internal Audit

Office of Research – Post

Awards

Budget, Actuals, A/R Sponsored –

Grants Secure

Business Affairs

Managers

(Academic Units)

Budget, Actuals, Positions –

Financial Secure

Deans and Chairs (

Academic Units)

Administrators

and

Department Managers Slide19

FDM DATA PERSPECTIVESSlide20

FDM DATA DIMENSIONSSlide21

KSU FDM MODELSlide22

Cubes, Hierarchies and MeasuresSlide23

FDM OLAP CUBESBUDGET PREPBUDGET GLOBALBudget PrepADP Code ExceptionsAggregate DetailFringe ExceptionsGrant PositionsLump Sum VariancesOBP Fund by ExpenseOBP Fund by FunctionPersonal ServicesPosition Counts

Position Employee ExceptionBudget 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 GlobalSlide24

FDM SAMPLE OLAP CUBESFINANCIAL SECUREFINANCIAL GLOBALComprehensive Financial Report (CFR) SecureCFR Budget Secure

CFR Encumbrance SecureCFR Expenses SecureCFR Payroll SecureCFR Personnel Services SecureCFR Revenue SecureVendor Risk Payments SecureTransportation Secure

Financial

Encumbrance (Fraud

Detection)

Vendor Risk Payments Global

Transportation Global

GRANTS

Grants Cube Global

Grants Cube SecureSlide25

FDM SAMPLE HIERARCHIESDIMENSIONPERSPECTIVEHIERARCHYFISCAL YEARFINANCIALFISCAL YR/DIV/DEPT/FUND/PROJNM/ACCT ROLLUP/ACCT NAME

FISCAL YR/FUND/CLASS/ACCT ROLLUP/ACCT NAME/DEPTFISCAL 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

BUDGET

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

PROJ NM/BUD REF/ACCTG PERIOD/BUD ACCT TYPE/ACCT NAME

PROJ NM/BUD ACCT TYPE/ACCT ROLLUP/ACCT NAMESlide26

FDM DATA MEASURESBudgetFinancialSum of Budget AmountSum of Proposed RevenueSum of Proposed APPROPSum of Proposed ORGSum of Proposed BudgetFTE CountsSum of Current SalarySum of Temporary AmendedSum of Permanent Amended

Sum of Next Year Base BudgetSum of Total Budget AmountSum of Total Fund BalancesSum of Total ExpensesSum 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 ExpensesSlide27

FDM DATA MEASURESFinancialSum of Personal ServicesSum of Non-Personal ServicesSum of Revenue AmountRisk Transaction CountSum of PO QuantitySum of Quantity ReceivedSum of Quantity RemainingSlide28

FDM DATA MEASURESBUDGET PREPSum of APPROP VarianceSum of Current APPROPSum of Current GrantSum of Current ORGSum of Current RevenueSum of Current SalaryDistributed Proposed SalarySum of Equity AdjustmentDistinct Count of FTESum of Total Fund BalanceSum of FTESum of Grant VarianceSum of ORG Variance

Sum of Proposed APPROPSum of Proposed Budget SalarySum of Proposed Fringe EstimateSum of Proposed GrantSum of Proposed ORGSum 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 BudgetSlide29

StatisticsSlide30

FDM USER ACCOUNTSSlide31

FDM PROJECT PORTFOLIOSlide32

FUNCTIONAL USER ORGANIZATIONSlide33

BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE

SAS®

Reports and Analytics Portfolio

Budget and Planning

Budget

Preparation

CURRENT Budget Preparation - Fund Balance Detail

CURRENT Budget Preparation - Fund Balance Summary

CURRENT Budget Preparation - Fund Balance Department Rollup

CURRENT Budget Preparation - Fund Balance Department Detail

CURRENT Budget Preparation - Distribution Percents less than 100

CURRENT Budget Preparation - Inactive Positions

CURRENT Budget Preparation - Lump Variances

CURRENT Budget Preparation - Fund Balance Personal Services Detail

CURRENT Budget Preparation - Fund Position Counts

REV Budget Preparation - Revenue Changes

APPROP Budget Preparation - Non-Personal Services Changes

PSV Budget Preparation - Personal Services Charges

Budget

and Planning (continued)

AGGREGATE

Budget Preparation - Fund Balance Summary

AGGREGATE Budget Preparation - Fund Balance Detail

AGGREGATE Budget Preparation - Department Fund Balance

AGGREGATE Budget Preparation - Department with Negative Account Balances

AGGREGATE Budget Preparation - Department with non-zero Balances by PeopleSoft Chartfields

AGGREGATE Budget Preparation - Grant Positions

AGGREGATE Budget Preparation - Summer Faculty Positions

AGGREGATE Budget Preparation - Part Time Faculty Positions

AGGREGATE Budget Preparation - Year over Year Comparison

AGGREGATE Budget Preparation - Active Budgeted Positions

AGGREGATE Budget Preparation - OPB Fund by Expense

AGGREGATE Budget Preparation - OPB Fund by FunctionSlide34

BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE

SAS®

Reports and Analytics Portfolio

Budget and Planning (continued)

Budget

Preparation

EXCEPTIONS Budget Preparation - ADP Code Exceptions

EXCEPTIONS Budget Preparation - Fringe Cube Exceptions

EXCEPTIONS Budget Preparation - Fringe Exceptions

EXCEPTIONS Budget Preparation - Position EMPLID Exceptions

EXCEPTIONS Budget Preparation - ZERO APPROP/ALL PROJGRT

Budget - Proposed Budget Schedules

Notes and Instructions

Department Budget Summary

Detail Personal Services

Division Budget Summary

Budget - Amendment Postings

Format Status

Budget - CFR Global

CFR Statistics

CFR Consolidated

CFR Fund Balance

CFR Fund Balance by Account Category

CFR Budget

CFR Revenue

CFR Revenue by Period

CFR Expenditures

CFR Expenditures by Period

CFR Travel

Expenditures

CFR Personal

Services Expenditures

CFR Operating

and Equipment Expenditures

CFR Pre

-Encumbrances and Reserves

CFR ADP

Payroll

Information

CFR

IndirectsSlide35

BUDGET AND FINANCIAL REPORTING FOR THE ENTERPRISE

SAS®

Reports and Analytics Portfolio

Budget and Planning (continued)

Budget

Preparation

EXCEPTIONS Budget Preparation - ADP Code Exceptions

EXCEPTIONS Budget Preparation - Fringe Cube Exceptions

EXCEPTIONS Budget Preparation - Fringe Exceptions

EXCEPTIONS Budget Preparation - Position EMPLID Exceptions

EXCEPTIONS Budget Preparation - ZERO APPROP/ALL PROJGRT

Budget - Proposed Budget Schedules

Notes and Instructions

Department Budget Summary

Detail Personal Services

Division Budget Summary

Budget - Amendment Postings

Format Status

Budget - CFR Global

CFR Statistics

CFR Consolidated

CFR Fund Balance

CFR Fund Balance by Account Category

CFR Budget

CFR Revenue

CFR Revenue by Period

CFR Expenditures

CFR Expenditures by Period

CFR Travel

Expenditures

CFR Personal

Services Expenditures

CFR Operating

and Equipment Expenditures

CFR Pre

-Encumbrances and Reserves

CFR ADP

Payroll

Information

CFR

IndirectsSlide36

DISCUSSION/QUESTIONSKenneth BridgesDirector Enterprise Financial Reportingkbridges@kennesaw.edu770-499-3422Dawn GamadanisDirectorBudget and Planningdgamadan@kennesaw.edu770-499-3293