Midyear Forecasting MarchApril 2019 1 Midyear Forecasting Agenda

Midyear Forecasting March/April 2019 1

Midyear Forecasting Agenda • Introductions (5 min) • Previous Session Reminders (5 min) • Context and Purpose(s) for a Midyear Forecast (10 min) • Best Practices for Accurate Data (5 min) • Process and Examples (55 min) • What Does It All Mean? (10 min) • Open Lab (30 min) 2

Midyear Forecasting Reminders from Previous Session • Focus was on: – Executive Summaries/Financial Narratives • Determining your audience • Determining your scope – Understanding the annual accounting cycle – Different reporting tools and what data they contain (pros and cons) • Prior period data (i. e, mostly reporting on the past) 3

Midyear Forecasting Context • Where are we in the accounting cycle? – Budgeted last July – Funded the Budget, including 9/1 commitments in September/October – Spent for half the fiscal year – Transferred many mid-year funding commitments and possibly brought in additional funding • Are we on track so far for the year (past-looking)? • Are we on track for year-end (forward-looking)? 4

Midyear Forecasting Purpose(s) • To determine whether you are overspending (and whether you have sufficient reserves to cover the overspending) • To determine whether you have sufficient funding to support incremental activities – One-Time Activities vs. Ongoing Activities • To determine your year-end fund balance status and compare to prior year balances – Why are reserve levels trending the way they are? – Are you sustainable? – Are you building reserves for a rainy day to ensure future sustainability? 5

Midyear Forecasting Best Practices for Accurate Data • Transactions have been entered and approved in a timely fashion • Expenditures have been reconciled and reviewed prior to forecasting • Outstanding Oracle expenditure commitments have been reviewed and suppressed (if appropriate) – Labor schedules are also accurate through the end of the year • Outstanding funding commitments have been gathered from Hand. SOn 6

Midyear Forecasting Process Overview 1. Download reports 2. Enter anticipated revenue 1. Outstanding Hand. SOn funding commitments 2. Anticipated gifts or designated revenue 3. Enter or modify anticipated expenses 1. Specific Oracle expenditure commitments 2. Include burn rate when appropriate 4. Determine end-of-year remaining balance in each PTA 1. OB then Non-OB 2. Non-OB: Know how your OB PTAs are funded so you know where to move year-end surplus or deduct for overdrafts 5. Consolidate information into projected reserve report(s) 6. Analyze and summarize 7

Midyear Forecasting Process Outline • Create Worksheets • Enter Projections • Review Results 8

Midyear Forecasting Process Outline • Create and Enter Projections in 3 Worksheets 1. Projecting Year-End OB Funding and Expenditures 2. Projecting Year-End Non-OB Fund Balances 3. Reviewing Non-OB Fund Balance Multi-Year Trend 9

Midyear Forecasting Process Outline Project OB Funding & Expenditures Project Non-OB Fund Balances Review Non-OB Multi-year Balances • Additional funding and outstanding commitments • Remaining expenditures and transfers out • Additional Non-OB revenue • OB funding adjustments • Add projected Non-OB balance • Compare projected fund balances by Award 10

Midyear Forecasting 1. Projecting Year-End OB Funding and Expenditures Create Worksheet – Using Browser • Navigate to H&S Financial Dashboard in OBI • Open OB Fund Balance Review • Enter Award Number = OB Award and Fiscal to Date GL Period Name = FEB-2019 • Export to and open with Excel – Using Excel • Add three columns: – Additional Funding (R) – Remaining Expenditures and Transfers Out (S) – Projected Balance (T) • Enter formula for Projected Balance (T) = YTD Available Balance (N) + Additional Funding (R) - Remaining Expenditures and Transfers (S) 11

Midyear Forecasting 1. Projecting Year-End OB Funding and Expenditures Enter Projections – Additional Funding • Outstanding funding commitments – Hand. SOn for HSDO and PPO – Other schools and departments • Drill down to investigate details in OBI or FFIT – Remaining Expenditures and Transfers Out • Projection methodologies (projection methodology will vary by PTA) – – Expenditure commitments YTD annualized Prior year actuals Current Year Budget • “Proof of Concept” H&S OBI projection tool • Drill down to investigate details in OBI or FFIT 12

Midyear Forecasting 1. Projecting Year-End OB Funding and Expenditures Review Results • Where OB PTA has a surplus, – How was this PTA funded? – Can all or a portion of the surplus be returned to your Non-OB fund(s)? – Will any of the surplus be required to fund OB PTA deficits? • Where OB PTA has a deficit, – Will all or a portion of the deficit be funded from your Non-OB funds? • These decisions will impact the projected Non-OB fund balances 13

Midyear Forecasting Process Outline Project OB Funding & Expenditures Project Non-OB Fund Balances Review Non-OB Multi-year Balances • Additional funding and outstanding commitments • Remaining expenditures and transfers out • Additional Non-OB revenue • OB funding adjustments • Add projected Non-OB balance • Compare projected fund balances by Award 14

Midyear Forecasting 2. Projecting Year-End Non-OB Fund Balances Create Worksheet – Using Browser • Navigate to H&S Financial Dashboard in OBI • Open Non-OB Fund Balance Review • Enter Organization = Org Code and Fiscal to Date GL Period Name = FEB-2019 • Export to and open with Excel – Using Excel • Add four columns: – Additional Revenue (Q) – Remaining Expenditures and Transfers Out (R) – OB Funding Adjustments (S) – Projected Balance (T) • Enter formula for Projected Balance (T) = YTD Available Balance (L) + Merged Pool Remaining Payout (M) + Additional Revenue (Q) - Remaining Expenditures (R) + OB Funding Adjustments (S) 15

Midyear Forecasting 2. Projecting Year-End Non-OB Fund Balances Enter Projections – Additional Non-OB Revenue • Designated income • Gift pledge payments – Remaining Expenditures and Transfers Out – OB Funding Adjustments • Projected Balance from Projecting Year-End OB Funding and Expenditures – Review funding sources when projecting pullback of surplus – Determine funding sources when projecting funding of deficit 16

Midyear Forecasting 2. Projecting Year-End Non-OB Fund Balances Analyze Results • Where the Non-OB fund balance is in deficit, – Is the deficit the result of overfunding your OB from this source? • If so, what is the impact on OB PTAs when this funding source is reversed or partially reversed to resolve the deficit? – Will all or a portion of the deficit be funded from your other Non. OB funds? 17

Midyear Forecasting Process Outline Project OB Funding & Expenditures Project Non-OB Fund Balances Review Non-OB Multi-year Balances • Additional funding and outstanding commitments • Remaining expenditures and transfers out • Additional Non-OB revenue • OB funding adjustments • Add projected Non-OB balance • Compare projected fund balances by Award 18

Midyear Forecasting 3. Reviewing Non-OB Multi-Year Fund Balance Trend Create Worksheet – Using Browser • • • Navigate to H&S Financial Dashboard in OBI Open Department Reserve Reports Select Reserves tab Enter Award Org Name and Fiscal Year Between 2014 -2018 Export to and open with Excel – Using Excel • Add one column: – 2019 (I) • Enter formula for Grand Total = sum(i 7: i 28) * adjust depending on number of rows and if also adding subtotals by category 19

Midyear Forecasting 3. Reviewing Non-OB Multi-Year Fund Balance Trend Enter Projections – By Award, add Projected Balance from Year-End Non-OB Fund Balance worksheet to the new column 2019 Analyze Results – Compare projected 2019 fund balances by award, by category, and in total, to prior years balances 20

Midyear Forecasting 3. Reviewing Non-OB Multi-Year Fund Balance Trend Analyze Results 21

Midyear Forecasting Analyze Fund Balance Trend What Does It All Mean? 22