Planning User Guide
Updated July 2017
Planning at Penn
Planning refers to the set of tools used by the University for the quarterly forecast of the current fiscal year, the budget for the upcoming fiscal year, and the budget for the next four years. Each school and responsibility center determines the individuals involved in that process – either at the central or department level – and at what time they are involved during the year, and the level of detail of the forecast and budget.
Planning allows each school or center to create a realistic budget and keep it up to date for any significant changes. Most schools and centers have budgets and forecasts at a higher level of detail than appears in the actuals, but exactly what should be budgeted or forecast and where is a decision that each school or center makes based on their need to manage. As the year unfolds, the school or center will update the forecast as the plan changes or as they identify significant differences in the actuals.
Planning provides three things:
- a place to type in your forecast and budget,
- canned reports to compare forecast and budget to the actuals,
- an adhoc reporting tool to drill down and analyze the data.
Data entry forms and canned reports can be accessed using a supported web browser or Smart View, which is also the adhoc reporting tool. To enter data a user has to be at the lowest level of org, fund, object, program, cref, and employee, and navigating to the right place can be problematic. For reporting, on the other hand, we encourage users to start by looking at their school or center(or piece there) of at the highest possible level (meaning all orgs, all funds, all programs, and all crefs) and this can be very valuable. You can then drill down as needed.
The same vendor software is also used by the Health System to plan their clinical activity, but the two implementations are separate. This document is about the University implementation, which is our plans for our academic activity. About 100 users, mostly in the Perelman School of Medicine, use both implementaitons. Both systems send their person level salary plans to the data warehouse each night so that users can report on both the academic and clinical activity that is planned.
University Planning Applications
PennPlan is the name of the University's application for Budget Planning Tools, and it is hosted on the Oracle cloud. Revexp and Salary are used to enter consolidated forecast and budget data and to see consolidated actuals for the current and prior year. Actuals for 7 years (we will be working our way up to 10 years, adding a year at a time) are in PennHist and GAAPHist. Reporting and ad hoc query on an RCM basis is done in PennHist, and reporting on a GAAP basis is done in GAAPHist.
Daily Schedule
Planning is normally available from 7 am through midnight, seven days a week. If we know of any changes during the normal working hours of 9am to 5 pm EST on Monday through Friday we will email all planning users. If we know of any changes outside these hours, we will not email all planning users, and so you should ask (by writing to mailto:PLANNINGHELP-L@LISTS.UPENN.EDU) if you are in then and see a problem.
PennHist is normally updated with information from the general ledger by 7am each morning. . If you save data entry changes to your budgets or forecasts during the day, those changes will go to PennHist immediately, when you save. If you make other changes to your budgets or forecasts during the day (eg, with copy adjust) then those changes will go to PennHist overnight.
GAAPHist is updated once a day, typically by about 10 am each morning, using the actuals from the day before and the forecast and budgets from the previous midnight.
Plans for Revenue and Expense are uploaded to the warehouse each night and appear in the FINQUERY universe. Person level plans for salary expense are uploaded to the warehouse every night and appear in the RSALMGT universe. We send the the files after midnight.
Several time-intensive calculations run overnight, from midnight to 7 am. For example, if a user budgets endowment or gift income and the fund is subject to overhead, the overhead charge is budgeted to the endowment or gift fund when saved, but the overhead recovery is budgeted to the school’s 000000 fund overnight. If grant or service center salaries are budgeted, employee benefits for that employee are saved in the same org, fund, program, cref combination when the salary is saved, but the dependent tuition is moved to the 000000 fund overnight.
Annual Schedule
In mid-July, three days after the close of 13th month, the oldest fiscal year is dropped (after we have reached 10 years) and the new fiscal year is added. The starting carryforward positions are populated and the original (Spring) budget for the current year is copied by the Budget Office to the Working Forecast.
Starting carryforwards are refreshed in August and finalized in September once the books are officially closed. In September, AIF income for all endowment funds is populated by the Budget Office based on the spending rule.
Forecasts are due September 15, January 15 and April 15. The Budget Office balances the forecasts to make sure that all internal object codes net to zero, then copies the forecasts to the Fall, Winter, or Spring version, depending on the quarter. Five year budgets are due at the end of October. The five year budgets are balanced in early November, copied to Fall, and reconciled with the long term model maintained by the Treasurer’s Office in preparation for the Trustee’s December meeting.
Schools and the library meet with the Provost and the Budget Office in November and December to discuss their plans. Resource Centers and Administrative Centers meet with their management and the Budget Office in February and March. Budgets are copied to Meeting when the briefing book is produced. Final resource allocations decisions are made and guarantee letters are issued in March. Schools and centers finalize the details of their budgets for the upcoming fiscal year in April. The Budget Office again makes sure all internal object codes net to zero in May. See the budget process for further detail.
The budget for the upcoming fiscal year is sent to the University’s general ledger in mid-June each year and copied from Working to Spring. The budget uploaded to the ledger comes back as the Final Budget, and should match the Spring Budget except for rounding.
The Chart of Accounts in Planning
The University chart of accounts (COA) is represented in Planning as dimensions called Orgs, Funds, Objects, Programs, and Center References. The naming convention in Planning is to show the name of the segment with a hyphen prefixing the code so that it is clear whether 1234 refers to ORG-1234, OBJ-1234, or PROG-1234. Although the general ledger knows that center references are different for each school or center, Planning does not, so a prefix is placed next to the value with the school or center code to distinguish between CREF-02-1234 in the School of Arts and Sciences and CREF-56-1234 in the Law School. The decoded values are shown as code+ hyphen+ decoded value so 1234-name of organization, or 1234-name of program or 123456-name of fund.
All valid values for most COA segments (org, program and cref) are automatically added to PennPlan overnight, and removed when they are no longer needed.
The object dimension is maintained separately, and only the consolidated object codes (eg, only 5200 for travel) are available in Revexp and used for budgets and forecasts. The object codes used are documented in RCM Template 1A.
For sponsored programs, we budget and forecast only federal (599999) and non-federal (599998), and we only show those funds. All other funds are added automatically overnight, and removed when they are no longer needed.
For performance reasons, before using Planning for the first time, users need to set their preferences to the subset of orgs, funds, programs and crefs they will be using. Most forms will then display only that subset of the COA selected.
Fund follows the RCM Fund Group rollups. For performance reasons, individual grant funds are not included in PennPlnx. Instead, grants are budgeted to fund-599998 for all non-federal grants and to fund-599999 for all federal grants.
Object code includes the normal objects from the ledger (see Template 1A) plus a handful of “fake” objects for Planning purposes, like Input Assumptions (Standing Faculty, Staff FTEs, Course Units) and Gifts to Endowment.
There is no CNAC segment in Planning because the center is derived as the parent of the org, and NAC 0 is implied for all budgets and forecasts, with some exceptions (activity in Capital Gift funds is moved to NAC 1, and Gifts to endowment become object 4400 with an offsetting object code 1710 in NAC 2). NAC (0 or 1) is shown as a separate segment in PennHist. NAC 2 is not included in PennHist, but does appear in GAAPHist.
There is no BC segment in Planning, and the value for BC is derived, based on the fund, as part of the warehouse load each night. This is hardcoded logic, and needs to be modified if you create a new fund with a BC of 2, so please be sure to notify the Budget Office if you do.
Other Dimensions in Planning
Planning includes several other dimensions, including Year, Version, Scenario, and Time Period, and the amounts. Also, there is an employee dimension for salary planning.
Year is the fiscal year, and appears as FY followed by the two digit fiscal year (e.g. FY15). Planning has a 13 years range: 7 closed fiscal years (working up to 10), the current fiscal year, and the 5 budget years. A new year is added and the oldest one dropped (once we reach 10 years) in late July each year.
Version is the source of the data. Final is the version for data that comes from the ledger, like the actuals. Working is the version that users enter data in for forecasts and budgets. Snapshots of the Working version are taken at various times of year, so we have a historical record: these are Fall, Winter, Spring, and Meeting.
Scenario is what kind of data. Forecast is the short-term outlook for the current fiscal year. Budget is our proposal for the upcoming year and four years following (five in total). Actual is the actual from BEN. Operating is the BEN operating budget (after doing data entry in BEN) and Encumbrance is the BEN encumbrance. There are also several variance scenarios, which compare a budget to an actual or a forecast. These are named with A for actual, B for Budget and F for forecast, with Final and Working implied. For example, 16B v 15F % is the percentage difference between the FY16 Working Budget and the FY15 Working Forecast, and 15B v 14A is the dollar (if no % sign) difference between the FY15 Final Budget and the FY14 Final Actual.
Time Period is the length of time in the fiscal year. There are twelve months (ADJ is part of June), four quarters, and YearTotal, which combines all of the months that exist. YTD includes the months from July through the most recently closed month in the ledger, so in mid-February it includes July though January . Note that in this example, the February data that does exist is included in YearTotal . YTD is very handy because it is updated automatically each month, and YTD for prior years includes the same months as it does for the current year.
Although all the months are there for the actuals, we normally forecast and budget only an annual amount which is stored in June. We can still compare Yeartotal to Yeartotal, and we can use seasonality to say normally at this time of year we are X% through the actuals, so we should compare to X% of the budget.
Amount for a given Org, Fund, Object, Program, Center Reference, Year, Version, Scenario, Time Period is the amount from BEN (like GL Balances) or as forecast or budgeted in PennPlnx. The amount for a quarter, YTD, or Yeartotal is normally the sum of the relevant months. Some accounts, like the number of Standing Faculty, are called time balance accounts and aggregate differently. Example: If you had 100 standing faculty in July and 99 in August and 103 in September, then the amount for Q1 is 103, the most recent month’s amount. Because the official census is in November, we use the November amount for YearTotal for Faculty and Staff FTEs.
Employee is used for person-level budgeting. Most schools and centers budget their faculty and staff as a pool, by using the surrogate employee PENNID-0001ANY (0001-Anyone or No one). Some schools budget by person, and have arranged to have all of their relevant people and positions loaded, where the code is PENNID-123445678 and the decoded value is 12345678-name of person. These are organized by home department, or home org (Horg) from PennWorks.
What’s Automatic
The budget office populates the AIF spending rule income for every endowment funds based on the market value as of the end of the prior fiscal year. You don’t have to, and can’t, change this. You should forecast or budget any subsequent additions or liquidation (in obj-1710) to endowment funds, or any true Gifts to Endowment, using form S1, and it will generate an estimate of the resulting income.
The system calculates the employee benefits for any salaries that you budget, given the planned EB rates, which the budget office maintains.
The system calculates the overhead charge and recovery for endowment and gift funds, based on which funds are subject to overhead and their forecast or budgeted income. If you want more overhead recovery, you can’t just budget more recovery: you have to budget more funds that will pay overhead.
What’s not Automatic, but needs to be something specific
Everything else needs to be forecast or budgeted manually, but while most things are completely at your discretion, some things have a right or wrong answer. For more information, see Budgeting Revenue and Expense.
The indirect cost recovery that your school receives must be 88.5% of the F&A charges to your grants.
Resource Transfers should net to what you are giving or getting from another school or center, and you should provide a list of the others involved, and the amounts, so we can confirm they are budgeted.
Your traditional undergraduate financial aid should be the planning parameter times your planned traditional undergraduate tuition.
Your capital funding should match the amount in your capital plan. If that is wrong, you need to talk to Facilities.
Your debt service should match the amount in the estimate you received from the Treasurer’s Office. If that is wrong, you need to talk to the Treasurer’s Office.
Your funding and your allocated costs and space charges must match the amount in the Guarantee letter. If they are wrong, you need to talk to the Budget Office.