Slicing and Dicing Budgeting at its best

1
Slicing and DicingBudgeting at its best

• September 15, 2009
• Nercomp SIG
• College of the Holy Cross

1
2
Presenter - Richard Trudel

• PeopleSoft Systems Integration Project Manager
• Responsible for PS Financial System
• Responsible for Data Warehouse ETL
• Responsible for Budgeting Module

2
3
Presenter - Richard Trudel

• 27 years in Technical area supporting HRMS and
  Financial Systems
• 10 years in Functional area HRMS and Payroll
  Taxes, Fixed Assets, AP, and Expenses
• 8 PS life cycle implementations HRMS /
  Financials
• Worked on versions from 4 to 9.0 Tools 8.49

3
4
Overview

• We will cover the use of flex formulas to gain
  efficiencies and data integrity. We will look at
  using sqrs to gain accuracy and efficiencies.
  We will cover the use of worksheets to prepare
  the budget and be the one place for data entry.
  We will cover the use of multiple models to help
  secure and process data and the use of Journal
  Class for data clarity.

4
5
Agenda/Contents

• Overwiew of Brandeis PS Systems
• Overview of our Upgrade Strategy
• Overview of our 27 Models
• How we use Flex formulas
• Sqrs to help gain efficiencies
• Worksheets to aid data entry
• Processes from EPM into Financials

5
6
Agenda/Contents

• Journal Class
• Issues encountered / Resolutions
• Benefits derived
• Future Use
• Questions

6
7
Brandeis and PeopleSoft - Now

• Financials v9.0 Tools 8.49.21
• GL, AP, PO, Grants (Post Award), Contracts,
  Projects, AR, Billing, CC
• Use Budget Checking extensively
• Upgraded to v9.0 April 2008
• Upgraded to Tools 8.49.21 April 2009
• Working on (Pre-Award)

7
8
Brandeis and PeopleSoft - Now

• HRMS v8.9 Tools 8.49.12
• HR, Base Benefits
• Payroll, Commitment Accounting
• Position Management
• Position Management Reporting Bolt-on
• Time Collection Bolt-On
• Employee and Manager Self-Service

8
9
Brandeis and PeopleSoft - Now

• SA v9.0 Tools 8.49.06
• SEVIS/PASS
• Admissions Prospects Applicants
• Student Records
• Student Financials
• Campus Community
• Housing Bolt-on
• Academic Advisement
• Student Self-Service

9
10
Brandeis and PeopleSoft - Now

• EPM v9.0 Tools 8.48.06
• ODS and WHSE loads
• Financial Information
• HRMS Information
• Selective Student Admission Information
• Will be doing CS maps
• Reporting
• Micro Strategy
• Planning and Budgeting
• Financial

10
11
Overview of our Upgrade Strategy

• Move to Linux for App Server
• Move to Linux for DB Server
• Move to latest Maintenance Packs Bundles
• Move to latest Tools - 8.49.xx
• Move to Unicode Database
• Move to latest Ascential DataStage

11
12
Overview of our Upgrade Strategy

• Move to 3 separate Windows Virtual Boxes
• Financial Maps
• HRMS Maps
• Campus Solution Maps
• Start September 2009 completed by February
  1,2010
• Move to EPM9.1 and Tools 8.50.xx
• Start August 2010 completed by February 1, 2011

12
13
Overview of our 27 Models

• AS has 6 models
• 1 For each type of Salary budget
• FAC
• STF
• STD
• 1 for OPEX
• 1 for Endowment
• 1 for Gifts

13
14
Overview of our 27 Models cont)

• Major RCMs have 1 model each (ex.)
• Student and Enrollment
• Facilities
• Library, ITS
• Heller, IBS
• Centers and Institutes
• Minor RCMs have 1 model each (ex.)
• Presidents Office
• Financial
• Operations

14
15
Model Setup Naming Conventions

• Helped identify which
• Activity Groups built BUD_10_xxx
• Scenario Groups built SCN_10_xxx
• Security built SEC_xxxxxx
• Models built MDL_10_xxxxxx
• Models Staged STAGE_xxxxxx
• Models Exported EXPORT_xxxxxx

15
16
Model build Special SQL

• to suit our needs
• Developed SQL to create scenarios
• Scenarios for Original Budgeted
• Scenario for Adjusted Budget
• Developed SQL to populate our Budgeting Database
  from our Production EPM db
• Developed SQL to correct any Budget loading
  issues

16
17
Model build Activity Groups

17
18
Model build Activity Groups

18
19
Model build Activity Groups

19
20
Model build Activity Groups

20
21
Model build Scenario Groups

21
22
Model build Model

22
23
Model build Model

23
24
Model build Model

24
25
Model build Model

25
26
Model build Model

26
27
Model build Scenario Manager

27
28
Model build Flex Formula Source

28
29
Model Process Flex Formulas

29
30
Model Process Flex Formulas

30
31
Model Process Flex Formulas

31
32
Model Process Method Group

32
33
Model Process SQRs for Efficiencies

• Two areas needed attention
• All Fringe chart strings for flex calc
• SQL to select all chart strings needing fringe
• Inserts to table
• Gifts and Endowment Revenue Entries
• SQL to gather dollars needed to create tax
• SQL to create revenue entry
• Tree used to obtain taxing rates

33
34
Model Process Worksheets

34
35
Processes into Financials

• Export Data to Staging Table run in EPM
• Run Ascential Map data to Fin run in EPM
• Import Data to Financial Table run in FIN
• Run SQR build Revenue Entries run in FIN
• Run Allocation for ORG
• Run Allocation for REVEST
• Post JEs
• Run Archive job

35
36
Journal Class Usage

• OBP Determined need for using
• OBP Established the classes and usage
• LTS Created PeopleCode and Permission Lists
• LTS Created necessary changes in FIN for
  Allocations
• OBP Will establish what scenarios in EPM
• LTS Will create SQL to build scenarios

36
37
Journal Class Usage

37
38
Issues encountered - Solutions

• Two maps needed to be updated for the new FY of
  processing
• Flex Formulas do not create new rows nor flag
  situations for a missing chart string
• We checked that all rows were present after calc
• If not, we entered by hand and re-calculated
• When using line items as basis for calculating
  other line items
• ensure that they are not picked up

38
39
Benefits Derived

• Opportunity to used as delivered
• Budget Office and ITS formed a partnership
• Helped relieve limited resources on Budget side
• ITS learned how functionality needed little
  change
• Budget Office formed a new vision of the future
• helped by drafting project plan
• Made sure that deadlines were met

39
40
Future Budgeting

• Brandeis wants to
• Distribute to more RCM Managers
• For input into budgets
• Budget Office does final approval
• Add Position Management
• account for Budgeted and Filled
• account for Budgeted and Unfilled
• help departments plan for future positions
• help University obtain position savings

40
41
Future Budgeting

• Brandeis wants to
• Use Top Down Budgeting
• To help users stay within Overall Budget for RCM
• Bottom Up Budget will draw from Top Down Budget
• Use more Flex formulas where possible
• Helps maintain accuracy of dollars
• Allows for quick changes to be made
• Helps reduce Data Entry
• Reduce number of approvals
• Eliminate reviewer process

41
42
QUESTIONS?
42
43
Contacts
Richard TrudelPS Systems Integrator and Data
Architect Library Technology ServicesBrandeis
UniversityE-mail rltrudel_at_brandeis.edu Phone
781-736-4532
43