Moving beyond Excel: Budget
Entry, Monitoring, and
Reforecasting
Dr. Andrew Harker
Director of Budget
Management
 (Used without permission)
2
Excel: A Necessary Evil
Universally used
Very flexible
Uncontrollable
A nightmare to “consolidate”
Distributed files...
What should a budget system do?
 Salary planning across cost centers
 Annotation/line-item detail
 Consolidation & dist...
The Marketplace
 Larger scale implementations: (e.g. > $100k)
 Oracle: Hyperion Planning, Hyperion Strategic
Finan...
A Case Study
How Stanford has used Oracle’s Hyperion
Planning for six years for budget
formulation, reforecasting, moni...
Overview
Using Hyperion Planning, Stanford has
brought discipline and analytical ability to
its on-going budget monito...
Budgeting at Stanford
$4.0B in Revenues and Expense
~30 decentralized budget units
~350 end users
>50k cost centers; ...
How are funds budgeted?
OB Designated
...
 (Used without permission)
10
Concepts/Rationale
 Hyperion is an excellent tool for analysis of
summarized data
 Generates fast results for known...
Organization of the Data
Aggregate data is entered and stored
in the system by:
Budget Unit
Fund Type
Year
Sc...
The Hyperion Workspace
Access to both
reports and data
con...
Budget Entry Portal Screen: Table of Contents
We have organized forms
...
Budget Entry: Non-Salary
Using Cell Text
to make notes
...
Budget Entry: Salary Percent Distribution
Salaries are calculated using $
...
Consolidation & Distribution
The system has an
organizational
...
Budget Analysis & Reporting using Hyperion: “cross-tab” views
...
Budget Analysis & Reporting using Hyperion: “time series” views
...
Budget Analysis & Reporting using Hyperion: “slicing & dicing”
Using the P...
Variance Analysis & Reforecasting Process
 Stored within Hyperion:
 High-level Budget Plan
 9/1 Consolidated Budget...
Rube Goldberg’s Pencil Sharpener
Open window (A) and fly kite (B). String (C) lifts small door (D) allowing moths (E) ...
Previous Variance Analysis Process
...
The budget monitoring process using Hyperion
Hyperion BI+
...
The Variance Report
25
Viewing information by fund type
Categorization of
similar types of
Cost Centers
(attributes of the
...
Changing your view on the fly
Select a different
organizational u...
Developing reports for analysis
28
Looking at Historical Progression
29
Delving into the data
30
Creating analytical reports
31
Investigating the data
32
Investigating the data
A hyperlink launches
another report that
...
Units submit their analysis text via Hyperion
34
Reports to aid in reforecasting
“Run-rate” Report: How much of last year’s total expense did the first 4 months
represent?...
What we have gained: the benefits
 A web-based budget formulation and reporting system
 Accessible from home/where...
“The Fine Print”
 Scalability is always at the forefront
 We have to manage the system around the large number of
...
Leveraging the Tool
 Our Land & Buildings division has developed an application to
calculate and forecast out charge-ou...
Alight Customers 150+
Business Services Construction Technology
B2B CFO E...
Alight’s Use by Campus Size
© 2009 Alight Planning Slide 40
Custom Revenue Recognition
© 2009 Alight Planning Slide 41
Driver Based Planning– Any Higher Ed Metric
© 2009 Alight Planning Slide 42
Alight Background
 The Team
 Rand Heer, founder of Pillar (acquired by Hyperion then Oracle)
 Management and de...
Alight Meets Planning Requirements
Planning in Higher-Ed Requires:
 Modeling unique activity drivers
 Students and...
Alight Planning in Higher Education
School Use Case
 Penn State University  Capital Planning
 ...
Adaptive Planning
Web-based
46
Prophix Software
 Higher Ed Clients include:
 Wayne State University
 Golden Gate University
 Lincoln ...
 (Used without permission)
48
Contact
 Andrew Harker, Ed.D.
 Director of Budget Management
 University Budget Office
 Stanford University
 ...
of 49

Nacubo Mads Budget Monitoring Presentation 2009 Revised

Presentation to NACUBO Managerial Analysis & Decision Support workshop, Scottsdale, Arizona, November 18, 2009
Published on: Mar 3, 2016
Source: www.slideshare.net


Transcripts - Nacubo Mads Budget Monitoring Presentation 2009 Revised

  • 1. Moving beyond Excel: Budget Entry, Monitoring, and Reforecasting Dr. Andrew Harker Director of Budget Management
  • 2.  (Used without permission) 2
  • 3. Excel: A Necessary Evil Universally used Very flexible Uncontrollable A nightmare to “consolidate” Distributed files; no version control Excel will probably never be fully out of the equation Users will always want to export/import 3
  • 4. What should a budget system do?  Salary planning across cost centers  Annotation/line-item detail  Consolidation & distribution  Easy to add additional units, cost centers, natural classifications  Be able to tag data with attributes (e.g. fund type, level of restriction)  Be able to report dynamically in rows and columns  Be able to handle Fund Accounting  Ability to do “what-if?” analysis 4
  • 5. The Marketplace  Larger scale implementations: (e.g. > $100k)  Oracle: Hyperion Planning, Hyperion Strategic Finance  www.oracle.com/hyperion  Prophix  www.prophix.com  Smaller scale, smaller budget: (e.g. < $100k)  Alight Planning  www.alightplanning.com  Prophix Express  www.prophix.com  Adaptive Planning  www.adaptiveplanning.com 5
  • 6. A Case Study How Stanford has used Oracle’s Hyperion Planning for six years for budget formulation, reforecasting, monitoring, an d variance analysis 6
  • 7. Overview Using Hyperion Planning, Stanford has brought discipline and analytical ability to its on-going budget monitoring and reforecasting processes with more utility and less pain than using Excel We have a process that facilitates financial information flow and analysis up and down the decision-making hierarchy 7
  • 8. Budgeting at Stanford $4.0B in Revenues and Expense ~30 decentralized budget units ~350 end users >50k cost centers; >10k employees Fund Accounting standards High-level forecasting several months before detailed budget exercise “Top-down” and “Bottom-up” 8
  • 9. How are funds budgeted? OB Designated Grants & Contracts Auxiliary Service Center Endowed University UR Gifts Detailed Budgets, usually to “Pooled” budgets at Budgeted at combination of department and fund specific cost center and specific CC and “Pools” at type level, at detailed natural account level, and department and fund type codes position level level, at detailed codes 9
  • 10.  (Used without permission) 10
  • 11. Concepts/Rationale  Hyperion is an excellent tool for analysis of summarized data  Generates fast results for known retrieval patterns  Consolidation of all units is easy  Instant access to data submitted  Don’t have to wait for linking of spreadsheets  Good roll-up and drill-through capability  “Slicing & dicing” capabilities beyond those of Excel  We design standard reports and data entry forms/templates for all the units to use  It facilitates on-going reforecasting and analysis of trends 11
  • 12. Organization of the Data Aggregate data is entered and stored in the system by: Budget Unit Fund Type Year Scenario (e.g. Budget Plan, Booked Budget) Version (Bottom-up, Top-down) Budget Category (revenue & expense) 12
  • 13. The Hyperion Workspace Access to both reports and data controlled by security 13
  • 14. Budget Entry Portal Screen: Table of Contents We have organized forms into “like” types of data for all of the cost centers for which someone is responsible 14
  • 15. Budget Entry: Non-Salary Using Cell Text to make notes Data can be exported to Excel Using Supporting Detail, users can annotate a particular entry or build up a calculation such as cost per person times number of people 15
  • 16. Budget Entry: Salary Percent Distribution Salaries are calculated using $ times % charged to each cost center 16
  • 17. Consolidation & Distribution The system has an organizational outline/hierarchy that facilitates drill- down and drill-up 17
  • 18. Budget Analysis & Reporting using Hyperion: “cross-tab” views 18
  • 19. Budget Analysis & Reporting using Hyperion: “time series” views 19
  • 20. Budget Analysis & Reporting using Hyperion: “slicing & dicing” Using the Point of View, users can change the data the report is looking at Related Content (“links”) let you drill down to lower levels of detail directly from this report 20
  • 21. Variance Analysis & Reforecasting Process  Stored within Hyperion:  High-level Budget Plan  9/1 Consolidated Budget (12 months of data from Budget cycle)  Year-end Projection  Actuals (e.g. Sep ~ Mar of Actuals from Oracle)  Goals:  To identify and analyze variances from budget  To enable complex “slicing & dicing” of budget and actuals data  To create a Rolling Forecast (Sep ~ Mar Actuals + Apr ~ Aug Budget) with capability to adjust remaining budget numbers 21
  • 22. Rube Goldberg’s Pencil Sharpener Open window (A) and fly kite (B). String (C) lifts small door (D) allowing moths (E) to escape and eat red flannel shirt (F). As weight of shirt becomes less, shoe (G) steps on switch (H) which heats electric iron (I) and burns hole in pants (J). Smoke (K) enters hole in tree (L), smoking out opossum (M) which jumps into basket (N), pulling rope (O) and lifting cage (P), allowing woodpecker (Q) to chew wood from pencil (R), exposing lead. Emergency knife (S) is always handy in case opossum or the woodpecker gets sick and can't work 22
  • 23. Previous Variance Analysis Process Bus Oracle Objects Star Schema (Actuals) Query Bus Objects Collection Hyperion (Budgets) Star Schema Excel DSS Data Warehouse UBO reviewed each Units emailed Excel SS and then loaded Analysis by Units Visual SS to UBO Basic into Hyperion Macro Variance Report Spreadsheets 23
  • 24. The budget monitoring process using Hyperion Hyperion BI+ Oracle EDW / Business (Actuals) Objects UBO enters “top-down” forecast/budget Hyperion into Hyperion Analysis Since Hyperion will Units enter their by Units only hold data at a analysis/forecasts certain level of into Hyperion detail, transactional reporting Ad-hoc environment Budget, Variance, analysis & provides queries for & Reforecast reporting analysis when more Reports (printed & detail is needed on-line) 24
  • 25. The Variance Report 25
  • 26. Viewing information by fund type Categorization of similar types of Cost Centers (attributes of the members) 26
  • 27. Changing your view on the fly Select a different organizational unit 27
  • 28. Developing reports for analysis 28
  • 29. Looking at Historical Progression 29
  • 30. Delving into the data 30
  • 31. Creating analytical reports 31
  • 32. Investigating the data 32
  • 33. Investigating the data A hyperlink launches another report that shows where this data comes from 33
  • 34. Units submit their analysis text via Hyperion 34
  • 35. Reports to aid in reforecasting “Run-rate” Report: How much of last year’s total expense did the first 4 months represent? The user can use this to help project out the current year. 35
  • 36. What we have gained: the benefits  A web-based budget formulation and reporting system  Accessible from home/wherever  A dynamic, yet controlled, system  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  Greater flexibility in report design  Better ability to report on Consolidated Budget  No software overhead on user machines 36
  • 37. “The Fine Print”  Scalability is always at the forefront  We have to manage the system around the large number of cost centers and positions we budget to  We have multiple databases (“applications”) which keep performance optimal  Storing low-level detail for units must be spread across several applications, and high level information for all units must be consolidated into one application  Significant infrastructure and IT support needs  Although the management and setup of the system is in our control  Windows-based: IE 6/7 or Firefox 2  Mac users need dual-boot Intel machines  The tool will not address skill issues  We still have fundamental issues around training non-financial people to do financial work 37
  • 38. Leveraging the Tool  Our Land & Buildings division has developed an application to calculate and forecast out charge-out rates  Developed by an analyst who moved over from my department  We are developing an application that will allow units to forecast out endowment principal growth and payout, using the university’s central assumptions and adding their own projections of new gifts, liquidations, and variations from our central assumptions  We are implementing Oracle’s Hyperion Financial Management for external financial statement reporting  We hope to develop integrations between fund accounting based- budgets and GAAP-based financial statements  We are working on developing high-level long-range forecasting models using Oracle’s Hyperion Strategic Finance 38
  • 39. Alight Customers 150+ Business Services Construction Technology B2B CFO EnerCrest Verizon CFP Ventures MB Petroleum Services Schoolwires EBMS McBurney Corporation iParadigms G&A Partners Biscayne Aquaculture Sales Team Live Hunter Warfield CMTS Inc. Apptix Health Care Higher Education Wholesale/Retail Kaiser Permanente Penn State Gap/Athleta Pittsburgh Mercy Nat’l Univ Singapore Capespan Healthcare Partners Kona Grill Marywood University Precision Therapeutics Merillat Industries Royal Berkshire Bates Technical College Old Port Cigars Tufts Medical Center Manufacturing Non-Profit Other Haws Corporation Calif Academy Sciences Down East Enterprises Nycomed Airlift Northwest Swan/Dolphin at Disney Ice River Springs Jobs for the Future Two Chefs on a Roll Kuehne Chemical Covenant House Monterey Gourmet Foods Weyerhaeuser Open Learning Exchange Odom’s Tenn Pride © 2009 Alight Planning Slide 39
  • 40. Alight’s Use by Campus Size © 2009 Alight Planning Slide 40
  • 41. Custom Revenue Recognition © 2009 Alight Planning Slide 41
  • 42. Driver Based Planning– Any Higher Ed Metric © 2009 Alight Planning Slide 42
  • 43. Alight Background  The Team  Rand Heer, founder of Pillar (acquired by Hyperion then Oracle)  Management and development team is heavy Pillar  Pillar had great success in Higher Education  Alight Positioning  Alight founded in 2004, product launch in 2006  Incorporates best components of Pillar  Unit-Rate Amount structure is great fit for Higher Ed  Targets configurations of up to 100 users  Low-cost integrated solution for capital planning, annual budgeting, rolling forecast, and long-range planning © 2009 Alight Planning Slide 43
  • 44. Alight Meets Planning Requirements Planning in Higher-Ed Requires:  Modeling unique activity drivers  Students and cohorts  Retention rates  Student/faculty ratios  What-if analysis  Changing tuition rates  Modifying faculty-student ratios  Launching new academic programs  Streamlined reporting  Custom time periods for academic calendars  Integrate with academic accounting packages (e.g. Banner)  Quickly slice & dice by program, campus, fund, etc. © 2009 Alight Planning Slide 44
  • 45. Alight Planning in Higher Education School Use Case  Penn State University  Capital Planning  Tufts Medical Center  Budgeting & Allocations  Bates Technical College  Capital Planning  Nat’l Univ of Singapore  5-Yr Forecast  Marywood University  Annual Budget & 5-Yr Forecast © 2009 Alight Planning Slide 45
  • 46. Adaptive Planning Web-based 46
  • 47. Prophix Software  Higher Ed Clients include:  Wayne State University  Golden Gate University  Lincoln Educational Services  The Jewish Theological Seminary  http://www.prophix.com/customers/industry/education/ 47
  • 48.  (Used without permission) 48
  • 49. Contact  Andrew Harker, Ed.D.  Director of Budget Management  University Budget Office  Stanford University  E-mail: aharker@stanford.edu 49

Related Documents