Moving beyond Excel: Budget Entry, Monitoring, and Reforecasting

Dr. Andrew HarkerDirector of BudgetManagement

2

(Used without permission)

3

Excel: A Necessary Evil

Universally usedVery flexibleUncontrollableA nightmare to “consolidate”Distributed files; no version controlExcel will probably never be fully out

of the equationUsers will always want to export/import

4

What should a budget system do?

Salary planning across cost centersAnnotation/line-item detailConsolidation & distributionEasy to add additional units, cost centers,

natural classificationsBe able to tag data with attributes (e.g.

fund type, level of restriction)Be able to report dynamically in rows and

columnsBe able to handle Fund AccountingAbility to do “what-if?” analysis

5

The Marketplace

Larger scale implementations: (e.g. > $100k)Oracle: Hyperion Planning, Hyperion Strategic

Financewww.oracle.com/hyperion

Prophixwww.prophix.com

Smaller scale, smaller budget: (e.g. < $100k)Alight Planning

www.alightplanning.comProphix Express

www.prophix.comAdaptive Planning

www.adaptiveplanning.com

6

A Case Study

How Stanford has used Oracle’s Hyperion Planning for six years for budget formulation, reforecasting, monitoring, and variance analysis

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

8

Budgeting at Stanford

$4.0B in Revenues and Expense~30 decentralized budget units~350 end users>50k cost centers; >10k

employeesFund Accounting standardsHigh-level forecasting several

months before detailed budget exercise“Top-down” and “Bottom-up”

9

How are funds budgeted?

“Pooled” budgets at department and fund type level, at

detailed codes

OB

Auxiliary Service Center

Designated

Endowed

Gifts

Grants & Contracts

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

University UR

10

(Used without permission)

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

12

Organization of the Data

Aggregate data is entered and stored in the system by:Budget UnitFund TypeYearScenario (e.g. Budget Plan, Booked

Budget)Version (Bottom-up, Top-down)Budget Category (revenue &

expense)

13

The Hyperion Workspace

Access to both reports and

data controlled by security

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

15

Budget Entry: Non-Salary

Using Supporting Detail, users can annotate a particular entry or build up a calculation such

as cost per person times number of people

Data can be exported to Excel

Using Cell Text to make notes

16

Budget Entry: Salary Percent Distribution

Salaries are calculated using $ times % charged to each

cost center

17

Consolidation & Distribution

The system has an organizational outline/hierarchy that facilitates drill-down and drill-up

18

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

19

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

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

21

Variance Analysis & Reforecasting Process

Stored within Hyperion:High-level Budget Plan9/1 Consolidated Budget (12 months of data

from Budget cycle)Year-end ProjectionActuals (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

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

23

Previous Variance Analysis Process

Hyperion (Budgets)

Oracle

(Actuals)Star Schema

Star Schema

DSS

Bus Objects

Collection

Bus Objects Query

Excel

Visual Basic Macro

Variance Report Spreadsheets

Analysis by UnitsUnits emailed Excel SS to UBO

UBO reviewed each SS and then loaded

into Hyperion

Data Warehouse

24

The budget monitoring process using Hyperion

Analysis by Units

Hyperion BI+ / Business Objects

Hyperion

Oracle

(Actuals)EDW

Since Hyperion will only hold data at a

certain level of detail, transactional

reporting environment

provides queries for analysis when more

detail is needed

Units enter their analysis/forecasts

into Hyperion

UBO enters “top-down”

forecast/budget into Hyperion

Budget, Variance, & Reforecast

Reports (printed & on-line)

Ad-hoc analysis & reporting

25

The Variance Report

26

Viewing information by fund type

Categorization of similar types of Cost Centers (attributes of the members)

27

Changing your view on the fly

Select a different

organizational unit

28

Developing reports for analysis

29

Looking at Historical Progression

30

Delving into the data

31

Creating analytical reports

32

Investigating the data

33

Investigating the data

A hyperlink launches another report that shows where this data comes from

34

Units submit their analysis text via Hyperion

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.

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

37

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

“The Fine Print”

38

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

Leveraging the Tool

© 2009 Alight Planning Slide 39

Alight Customers 150+Construction

EnerCrestMB Petroleum ServicesMcBurney CorporationBiscayne AquacultureCMTS Inc.

Higher EducationPenn StateNat’l Univ SingaporeMarywood UniversityBates Technical CollegeTufts Medical Center

Health CareKaiser PermanentePittsburgh MercyHealthcare PartnersPrecision TherapeuticsRoyal Berkshire

ManufacturingHaws CorporationNycomedIce River SpringsKuehne ChemicalWeyerhaeuser

Non-ProfitCalif Academy SciencesAirlift NorthwestJobs for the FutureCovenant HouseOpen Learning Exchange

TechnologyVerizonSchoolwiresiParadigmsSales Team LiveApptix

Wholesale/RetailGap/AthletaCapespanKona GrillMerillat IndustriesOld Port Cigars

OtherDown East EnterprisesSwan/Dolphin at DisneyTwo Chefs on a RollMonterey Gourmet FoodsOdom’s Tenn Pride

Business Services B2B CFOCFP VenturesEBMSG&A PartnersHunter Warfield

© 2009 Alight Planning Slide 40

Alight’s Use by Campus Size

© 2009 Alight Planning Slide 41

Custom Revenue Recognition

© 2009 Alight Planning Slide 42

Driver Based Planning– Any Higher Ed Metric

© 2009 Alight Planning Slide 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 44

Alight Meets Planning RequirementsPlanning 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 45

Alight Planning in Higher Education

School Penn State University Tufts Medical Center Bates Technical College Nat’l Univ of Singapore Marywood University

Use Case Capital Planning Budgeting & Allocations Capital Planning 5-Yr Forecast Annual Budget & 5-Yr

Forecast

46

Adaptive PlanningWeb-based

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/

48

(Used without permission)

49

Contact

Andrew Harker, Ed.D. Director of Budget Management University Budget Office Stanford University E-mail: aharker@stanford.edu