Discussion of Stanford University Budget Process & Systems

1. Discussion of Stanford University Budget Process & Systems September 6, 2005 Dr. Andrew Harker Director of Budget Management aharker@stanford.edu 650.725.0666

2. Outline • Stanford Financial Overview • Stanford Financial Management • Changing Role of the “Budget” • Financial Information Systems • Budget Monitoring/Variance Reporting • Demonstration of Systems

3. Financial Overview • Fiscal Year: September – August • In addition to the University: • Two Hospitals • Limited Liability Corporation(s) • Financial data presented both in GAAP and “Fund Accounting” formats • The following are some of the ways we look at/present financial information:

4. 2005/06 Consolidated Budget by Line Item $ in millions

5. Consolidated Budget by Unit 2005/06 Total = $2.8B Academic Units 61%

6. 2005/06 Consolidated Budget by Fund Type $ in millions

7. FY2004 Statement of Activities to Consolidated Budget Crosswalk (in millions of dollars)

8. FY2004 Operating Results vs BudgetConsolidated Budget Version (in millions of dollars)

9. FY2004 Consolidated Budget Results by Fund Type (in millions of dollars)

10. FY2004 Consolidated Budget Results by Fund Type (in millions of dollars)

11. Outline • Stanford Financial Overview • Stanford Financial Management • Changing Role of the “Budget” • Financial Information Systems • Budget Monitoring/Variance Reporting • Demonstration of Systems

12. Financial Management Overview • Modified Centralized Budget Model • Formula Units: Med School, Business School, Continuing Studies/Summer Session • Non-formula Units (~30) • Funds controlled by budget units • Although there also are “University” funds • ~30k cost centers; ~10k positions • ~200 people responsible for budget entry

13. Financial Management Overview Stanford Consolidated Financials University “Non-formula Units” (Provost GF Allocations) Hospitals LLCs Auxiliary Enterprises Continuing Studies/ Summer Session Graduate School of Business School of Medicine

14. Annual Budget Cycle CONTINUALLY WORKING TO DO BETTER THAN BEFORE Provost’s Budget Group FALL WINTER • Load “9/1” Consolidated Budget into financial reporting system • Analyze past year’s financials • Tie GAAP to Fund Accounting • Variance Analysis of prior year • Update LRF assumptions • Preliminary budget letters from units • Begin budget meetings w/Provost’s Budget Group • Continue budget meetings • BoT approves tuition, R&B, payout rate, salary program • Determine final GF allocations • High level Projections and Variance Analysis SUMMER SPRING • From Units (“Bottom-up”): • 6 month YE projections & Variance Analysis • Next year’s Consolidated Forecast • Including Formula (“autonomous”) • “Top-down” Consolidated Budget Forecast • Formulate Projected Statement of Activities • HROs enter proposed salaries for next year • Present Budget Plan to BoT for approval • Load proposed salaries, endowment income projections into Budget System • Units enter their detailed budgets into Budget System • Preliminary Budget Letters from units • Continue Variance Analysis and YE Projections • Close out fiscal year “Local Management Budgets”: units rebudget “Revenue/Expense Control” as they choose, to display on fund/expenditure statements; these budgets, however, are “ignored” by Budget Office

15. Overview of the Non-formula Budgeting Process Board of Trustees – Policy Decisions on Salary Program, Tuition, And Financial Aid Capital Planning Compensation Analysis Tuition and Financial Aid Analysis Budget Office Creates Long Range Forecast Research Volume and Rate Projections Budget Group – Oversight and Advice on Strategic Priorities Academic and Administrative Units Develop Budget Plans New Initiatives Provost Makes Allocation Decisions Analysis of School Balance Sheets and Consolidated Income Flows General Fund Allocations to Academic and Administrative Units Decisions on Major Program Priorities and Initiatives Amount of University Reserve Consolidated Budget Plan – Submitted to Board of Trustees in June

16. How are funds budgeted? OB Designated Grants & Contracts Auxiliary Endowed University UR Service Center Gifts “Pooled” budgets at department and fund type level, at detailed codes Detailed Budgets, usually to specific cost center and natural account level, and position level Budgeted at combination of specific CC and “Pools” at department and fund type level, at detailed codes

17. Budget Process • Org Chart for President (see handout) • Finance function reports to President • Org Chart for Provost (see handout) • Budget function reports to Provost

18. Outline • Stanford Financial Overview • Stanford Financial Management • Changing Role of the “Budget” • Financial Information Systems • Budget Monitoring/Variance Reporting • Demonstration of Systems

19. How has the budget evolved? • Until 1991, budgeting focused on “Operating Budget” • General Funds plus Restricted Funds supporting on-going operations (mainly endowed chairs) and Auxiliaries • Dr. Gerhard Casper became president in 1991; declared intention to focus on all funds – “Consolidated Budget” • No systems in place to support this kind of budget development, so for several years stumbled through using Excel to develop high level Consolidated Budgets • At this same time, the University embarked on a long-term process to replace “legacy” systems with “market-place” solutions, including budget formulation tool to support new budgeting demands • In the legacy system, the “budget” also served a “control”/fund tracking function • For unrestricted funds (OB, Aux, SC), the legacy accounting system did not track funds separately by Cost Center

20. Into what has it evolved? • The notion of a “static budget”: the 9/1 Consolidated Budget • For reporting to the Board, report only variances rather than “revised” budgets • Once the budget is set in September, units manage their budgets “locally”, for their own purposes, revising them (or not) at their discretion • Variance reporting to the Budget Office is done against the 9/1 version of the budget • We developed a custom web application (“iBudgets”) due to limitations/complications of entering budget revisions into Oracle • Oracle functionality “freed” the fund tracking role of the budget on unrestricted funds • Continual refinement of what we should include in the budget • Fund accounting view vs. GAAP view • E.g. we don’t budget depreciation, but we budget some expense accruals

21. Outline • Stanford Financial Overview • Stanford Financial Management • Changing Role of the “Budget” • Financial Information Systems • Budget Monitoring/Variance Reporting • Demonstration of Systems

22. Financial Information Systems • PeopleSoft (HR & Student Information) • Oracle • General Ledger (GL): • Asset, Liability, Equity, Revenue, and Transfers detail • Expenditure summary • Grants Accounting (GA): Expenditure detail • Fixed Assets, AP, AR, PO, etc. • Hyperion (Essbase, Planning, Reports, Analyzer, Master Data Management) • Reportmart (reporting portal) • Pre-written reports via Portal & DW ad-hoc querying • Business Objects • Brio (now Hyperion Intelligence) • Custom web apps: iJournals, iBudgets

23. Pre-1996, all systems were “legacy” (home-grown) Mainframe database, hard-coded reports “Operating Budget” only General Funds, plus restricted funds supporting on-going operations (mainly endowed chairs) Data managed mainly by staff in Controller’s Office Home-grown salary management and analysis system (“SMAS”) Used for salary planning/setting and turning those plans into salary budget data Push to all-funds budgeting led to conclusion that no home-grown system would adequately support users History of Budget Solution

24. Stanford has had a strong relationship with Hyperion Solutions since 1995 Along with University of Pennsylvania, Stanford first University using Pillar (microcomputer-based product) Pillar used for FY96 through FY03 budget entry 12 different, separate motherships (databases) and administrators due to size & internal management practices Implementation, using Hyperion consulting, took 8 months Adapting to work with facets of fund accounting Started with version 2.0; each year used a new version of software After initial work by Hyperion consulting, all training and documentation done in-house Once completed, Pillar data loaded into University Data Warehouse to consolidate all 12 files and to facilitate data extraction/feed to legacy mainframe batch accounting system Hyperion Pillar

25. Why Move From Pillar to Planning? “Push/Pull” • “Hit the wall” with Pillar limitations • Size issues • Reporting Issues (e.g. P&L by cost center) • Integrating data from all files for review, analysis, and reporting required loading data into another environment (our data warehouse) • Institutional move to Marketplace Web-based financial systems • Started evaluation process in 2001 • Final evaluation between Hyperion Planning and Solver/ Great Plains (Microsoft) • Chose Hyperion based on established relationship, track record, and product capabilities • Simultaneous conversion from legacy financial systems and chart of accounts caused significant implementation issues

26. Our experience with Pillar shaped our initial design of Planning Position budgeting: Units x Rates Line-item Detail Reforecasting/Revising budgets Reporting Usability on Macs (initially*) as well as PCs Security/Access Administration/Integrity of metadata Central vs. Distributed Maintenance Integration into Financial/UDW systems Pillar to Planning

27. Technology/Infrastructure paradigm shift From: microcomputer “spreadsheet”/relational database To: server-based, network-configured, multidimensional database with complex computer program interactivity Infrastructure issues rose to the top Substantial reliance on IT support where previously not needed Pillar to Planning

28. Design Issues of paramount concern: Security (user access to data) To allow complex Position Budgeting Design & Maintenance of Forms & Reports Metadata “overhead”: Size of the cubes Org Structure Chart of Accounts Number of Cost Centers Tech Infrastructure unique to Universities Kerberos Authentication vs. “Firewall” Performance (apparent speed to users, control over calculations) Pillar to Planning

29. Hyperion Planning initially an immature product Version 1.6 (then) vs 3.5 (now) We fell short of due diligence in vetting implementation design Did not challenge our model assumptions sufficiently Our initial design constrained use of Planning/Essbase functionality (e.g. security) Created a massive amount of unnecessary grunt work in form and report duplication Unique characteristics of a university technology infrastructure Some initial design specifications could not be met (separate salary planning vs. consolidation cube) Led to change in design to meet salary planning needs To balance cube size and performance: 3 separate apps Oracle Financials implementation difficulties derailed implementation Hindsight: Planning “issues”/”growing pains”

30. System in Use the first time: A Bumpy Ride • System went live with version 3.0 in May 2003 for FY04 Budget formulation • Chose a mix of cost-center detail vs. “pooled” budgeting • Because Essbase security not enabled, security of data access achieved through form/report design • Thousands of forms and reports created • Many forms/reports not ready for many users • Some user issues with centralized calculations of business rule • Some system HTML bugs (screen redraw issues) • Analyzer not implemented • Integrated workflow not utilized • Much more dynamic reporting than Pillar, but no user control/ability to design • “Real-time” central access to data was useful

31. Revisited system design and assumptions for FY05 Utilized Hyperion consulting to vet ideas, verify proposals Add additional metadata (cost centers) for more detail Thorough Hyperion infrastructure review Many software link/install issues resulted from several version upgrades over two years Changed design to enable use of native Essbase security Prelude to use of Analyzer to give users powerful ad-hoc analysis and reporting Significantly reduced number of forms/reports to be created: let the system do the work Version 4.1 for FY07 process Improvements in functionality for administrators and end-users Improvements in performance and stability Import summarized data from three different apps for a Consolidated View This process is more or less programmed to be done seamlessly within the software’s functionality Smoothing out the rough edges: “Growing Up”

32. A web-based budget formulation and reporting system Accessible from home/wherever Better control over integrity of metadata “Real-time” access to budget entry progress Much greater ability to monitor the budget along the way Better budget process management Much more flexibility in report design than Pillar Better ability to report on Consolidated Budget Including ability to incorporate “crosswalk” to Statement of Activities No software overhead on user machines System stability (no unplanned down time—more or less) What we have gained: the benefits

33. Hyperion Products • Hyperion Planning for Data Entry • Hyperion Reports for canned reports • Hyperion Analyzer for “dashboard” and ad-hoc query • Hyperion Essbase—an “OLAP” database—is the engine • Hyperion Master Data Management (Razza)—a hierarchy maintenance tool; currently used to maintain the “master” university admin org hierarchy • Hyperion Business Intelligence (Brio) Used for web portal report writing and limited ad-hoc querying Business Objects Products • Business Objects desktop • Web BO Also used for web portal report writing and user-controlled ad-hoc querying

34. Current Resources • Hyperion: • UBO Staff: ~4 FTE • Support & train the users; maintain & enhance the app • ITSS (infrastructure) support: ~2 FTE • Support the servers, networking, databases • Funding incorporated in “Core” system support budget in ITSS • Medical School: ~2 FTE • Support their own users; configure app for their needs • Infrastructure: • Hyperion applications: 6 Windows Servers • Development, Test/Training, Production environments

35. System Architecture

36. Current State of applications/systems • Hyperion applications • Target audience: budget officers, finance managers, department managers • Not down to the level of the reconciler of the PTA • Used by ~ 200 users through the web (Internet Explorer on PC) • Used for annual budgeting process (except Sponsored by OSR and Capital projects) • those detail budgets are entered directly into Oracle GA • Also used by Medical School for current Year-end Projections & tied to next year’s budget • Application owned/maintained by UBO; ITSS supports infrastructure

37. Hyperion: A Common “look & feel” of entry forms and reports • Budget entry: • Users can define the subset of object codes/expenditure types they want people budgeting at • Access to data limited by users’ security (maintained in Hyperion) • Working towards “standard” views that are compatible with both Consolidated Budget and external Financial Statements • Budget Reporting/Analysis • A variety of views of the data that show fund type, scenario, year, unit across rows or columns • Hyperlinked drill-through capability to “investigate” numbers from a high level down to a certain level of detail • Does not store transaction-level detail; ReportMart3 will provide this support through reports that will show detailed transactions that tie to the aggregated data

38. Budget Entry via Hyperion Planning: Portal Screen

39. Budget Entry via Hyperion Planning: Non-Salary

40. Budget Entry via Hyperion Planning: Annual Rate

41. Budget Entry via Hyperion Planning: Percent Distribution

42. Budget Analysis & Reporting using Hyperion: “cross-tab” views

43. Budget Analysis & Reporting using Hyperion: “time series” views

44. Budget Analysis & Reporting using Hyperion: “time series views”

45. Budget Analysis & Reporting using Hyperion: “slicing & dicing”

46. Outline • Stanford Financial Overview • Stanford Financial Management • Changing Role of the “Budget” • Financial Information Systems • Budget Monitoring/Variance Reporting • Demonstration of Systems

47. Goals Moving Forward • Use Hyperion for more than just budgeting • Load Actuals from EDW • Use it for Variance Analysis • Enable units to develop Rolling Forecasts • Roll-out Hyperion Analyzer • Web-based tool that accesses the data stored in Hyperion • Works as a “dashboard” • Works as an ad hoc analysis tool

48. Bus Objects Query Oracle (Actuals) Star Schema Bus Objects Collection Hyperion (Budgets) Star Schema DSS Excel UBO reviews each SS and then loads into Hyperion Units email Excel SS to UBO Analysis by Units Visual Basic Macro Variance Report Spreadsheets Current Variance Analysis Process

49. Variance Reporting via Excel Spreadsheet • (see handout)

50. Future Variance Analysis & Reforecasting Process • Stored within Hyperion: • 9/1 Consolidated Budget (12 months of data from Budget cycle) • Actuals (e.g. Sep ~ Mar of Actuals from Oracle) • Goals: • To identify and analyze variances from budget • To create a Rolling Forecast (Sep ~ Mar Actuals + Apr ~ Aug Budget) with capability to adjust remaining budget numbers