1. Midyear Forecasting March/April 2019

2. Midyear ForecastingAgenda • 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)

3. Midyear ForecastingReminders 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)

4. Midyear ForecastingContext • 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)?

5. Midyear ForecastingPurpose(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?

6. Midyear ForecastingBest 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 HandSOn

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

8. Midyear ForecastingProcess Outline • Create Worksheets • Enter Projections • Review Results

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

10. Midyear ForecastingProcess Outline

11. Midyear Forecasting1. 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)

12. Midyear Forecasting1. Projecting Year-End OB Funding and Expenditures Enter Projections • Additional Funding • Outstanding funding commitments • HandSOn 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

13. Midyear Forecasting1. 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

14. Midyear ForecastingProcess Outline

15. Midyear Forecasting2. 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)

16. Midyear Forecasting2. 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

17. Midyear Forecasting2. 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?

18. Midyear ForecastingProcess Outline

19. Midyear Forecasting3. 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(i7:i28) * adjust depending on number of rows and if also adding subtotals by category

20. Midyear Forecasting3. 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

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

22. Midyear ForecastingAnalyze Fund Balance Trend What Does It All Mean?