2017/2018 General Operating Budget Forecast Guideline

Printer-friendly versionPDF version

Reminder:

1) Forecast Carryforward Summary

This year there is one additional summary format that has been added to this year's forecast request. The Carryforward Summary is a schedule showing the change in your total carryforward for the year. 

Please submit this overall summary (one per fund and division/college) in addition to the regular forecast worksheets that make up your submission. The new summary report can be added as an extra tab in your excel forecast submission. The excel file with instructions can be downloaded from the Budget Policies and Procedures Page.

2) OMAFRA funded operating departments

For OMAFRA operating departments (defined as support departments with more than just faculty research projects and especially those units that generate user fees and other revenues), please submit a forecast that summarizes total revenues and expenses in the General Operating budget format outlined in Appendix B of the Forecast Guideline.  Your OMAFRA submission in the revenue/expense format should also include the new Carryforward summary format noted in new item #1 above.

Due Date

Completed forecasts are to be submitted to the Budget Office by Friday February 9, 2018.

Overview:

Please use these guidelines to prepare a forecast of year end results for the current 2017/2018 fiscal year.

The fundamentals for building your forecast and explaining your surplus or deficit should be part of your normal financial management routines.   Appendix A - “Forecast Reporting Guidelines” provides step by step instructions for preparing a forecast in a consistent format for summary purposes.  Notes and explanations are an integral part of every forecast submission.

Please provide a forecast for each of your areas of responsibility (division, department and/or project) separately by fund for the General Operating funds (100 - General Operating; 102 - OVC Special Grant; 104 - Diploma Education; 105 – OVC HSC) and the OMAFRA Agreement funds (110 – OMAFRA/VCEP, 112 – OMAFRA Growing Forward 2).

The Dean’s Office or Directorate office is responsible for accumulating, consolidating, reviewing and approving the detailed forecasts for their respective college or directorate.

The Budget Office has to compile all the college/divisional forecasts into the University summary which becomes part of the budget documents presented to the Board of Governors in April. To be able to do this efficiently and accurately, we need consistent submissions from all units.  Please ensure that your forecast is prepared in the formats identified in the “Completed General Operating/Diploma Education Department Forecast Sample” (Appendix D), the “Completed OMAFRA Project Forecast Sample” (Appendix E), and the Forecast Carryforward Summary (Appendix F).

The FRS system functionality allows the direct download of FRS report data into an Excel spreadsheet. Detailed instructions are provided in Appendices below. The recommended method for building your forecast is by running the FRS reports, then exporting the report to Excel.  Please follow the step-by-step procedures as described in Appendix B (General Operating/Diploma Education/OMAFRA operating departments/Forecast by Department), and Appendix C (OMAFRA Forecast by Project).

Please review your division/college forecast to ensure the information is correctly consolidated, calculated and the budget column reconciles to FRS with any additional adjustments clearly noted.

PLEASE E-MAIL YOUR COMPLETED AND APPROVED FORECAST EXCEL WORKSHEET TO ONE OF THE BUDGET OFFICE STAFF IDENTIFIED BELOW.

For assistance, please contact the Budget Office:

                  Ryan MacLean    x56184                 

 ryanmacl@uoguelph.ca

        Stephanie Ugrnov- Devost   x54368       

sugrnov@uoguelph.ca

Michelle Curtis  x52833

mcurti07@uoguelph.ca

Larry Shuh  x52860

lshuh@uoguelph.ca

Angeline Li   x52044

(On Leave)

 

SUBMISSION DEADLINE: February 9, 2018

Appendix A: Forecast Reporting Guidelines

A.  Purpose of this Forecast:

The purpose of this forecast is to determine the total 2017/18 surplus / (deficit) position for each operating department, division or college.  The results will be compiled to forecast the total University surplus / (deficit) position and will be presented for information, as part of the 2018/2019 University of Guelph budget presentation.

B.  Assumptions:

Each department will have different methods and capabilities of completing this forecast.  Some have ongoing forecasting procedures; others have periodic forecasting efforts depending upon both the time of year and status of their budget.  This requirement to complete a forecast should be an extension of those procedures and not in addition to or separate from them.  Methods of forecasting expenditures and revenues may include using historical trends, using current commitments for personnel costs and using planned expenditures not yet recorded on FRS.  The segregation of special purpose or special project funds from the rest of operating funds may be helpful in compiling this information.  Please include notes on the forecast sheets to outline your assumptions or any other relevant information.

C.  Degree of Accuracy:

A very acceptable target for accuracy would be 1% of budget.  For example if your total unit budget is $500,000 and you forecast a $25,000 surplus, an actual result of a $20,000 to $30,000 surplus would be excellent.  Administrators should focus on a percentage of budget for accuracy, not absolute dollars.  By its nature, any forecast contains a certain element of estimation and intelligent guesswork.  This exercise is no exception.  You will not be able to forecast exactly nor is it expected that you will.  However, your information is the basis of the consolidated University forecast and accuracy is very important.

D.  General Instructions:

  1. As part of this Forecast you need to create a separate forecast report for each sub-fund in General Operating (fund #100,102,103,104,105) OMAFRA (fund #110,112). The following sections are a description of the forecast report format.

See Appendix B and C for the steps to create a forecast as an Excel spreadsheet.

See Appendix D and E for a sample of the report format to be submitted.

  1. The first two columns of this form contain the current Month-to-Date actual (Mtd Actual) and Year to Date actual (Ytd Actual).  These amounts represent the actual expenses and revenues currently recorded in FRS for 2017/18.
  2. The column entitled `Comm' represents the committed payroll and general expenses. The commitments in payroll object codes are estimates of costs for salary and benefits for all regular full time employees to April 30, 2018 and many temporary appointments to the earlier of the end of the their current appointment or April 30, 2018. Non personnel expenses are committed amounts for un-received goods from open Purchase Orders to April 30, 2018. Please note that you should review all commitments in detail to determine the validity of including them in your forecast. You may encounter the occasional negative commitment amount in your FRS reports.  These amounts are errors that may occur for various reasons against open PO’s in the Oracle system.  Negative commitment amounts result in an overstatement of the remaining Balance in the account.  Your forecast should not include any negative commitment amounts! Please review Procurement Services information web page on De-Commitment to have unneeded commitments removed.
  3. The next column entitled `Additional (Net) Rev/Exp' is the column you are requested to input estimated expenses and revenues (up to April 30, 2018) which have not yet been recorded in FRS for 2017/18.
  4. The column entitled `Y/E Forecast' is the estimated total 2017/18 expenses and revenues.  This column is the sum total of the Ytd Actual, Comm and Additional (Net) Rev/Exp columns.  Do not include Mtd Actual in your Y/E Forecast column calculation as this amount is already included in the Ytd Actual amount.
  5. The column entitled ‘Budget’ represents your 2017/18 Budget.  This column includes all base and one-time funding.
  6. The last column entitled ‘Balance’ represents the surplus/deficit for 2017/18.   This column is the calculated difference between the Budget and Y/E Forecast columns. 
  1. The department number and name must be inputted into the forecast report.  The FRS report header information such as department number and name, is not automatically brought into Excel, therefore we require you to manually input this information
  2. Notes should be made at the bottom of the form describing assumptions made and explaining any significant surplus or deficit forecasted.  Include information for any surpluses which are committed at year end for future equipment purchases or multi-year projects.
  3. Colleges and Divisions must create a summary worksheet for the college or division as well as individual department forms.  Please summarize the departmental detail onto summary worksheets for each fund type in your College/Division.
  4. OMAFRA projects are created separately showing net revenue and expense.  The amount which is inputted to the Additional Net Rev/Exp column is the netted amount resulting from estimated revenues and expenses not recorded in FRS. Your forecast should be calculated for each project. Category detail (i.e. Personnel, Operating) is not required.
  5. Completed forecasts are to be submitted to the Budget Office by Friday, February 9, 2018.

Appendix B: General Operating/ Diploma Education/ OMAFRA Operating Departments/ Forecast by Department

Step 1:  Create FRS Report - “Department Summary by Expense Category”

Login to FRS
Select ‘Favorites’ from the menu bar.
Select ‘Canned Reports’.
Select ‘Department Summary by Expense Category.
At the Prompt screen:
    Enter Fund:      100 (General Operating), 102 (OVC Special Grant), 104 (Diploma Education), 105 (OVC Health Sciences), 110 (OMAFRA)
    Enter Unit:     your department
    Click ‘Run’ button
The following report format should be displayed:

Title: FRS Print Screen 1 - Description: University of GuelphAn image of FRS- Department Summary by Expense Category

Step 2:  View Report in Excel

At the lower left-hand corner, click on the ‘Excel’ option.

Title: FRS Print Screen - Description: University of GuelphPrint Screen of FRS- Department Summary by Expense CategoryInstruction- Export to Excel function

Step 3a:  Create Forecast Report in Excel

The following Excel spreadsheet should appear:

Title: Building an Operating Budget Forecast- Microsoft Excel Print Screen  - Description: An image of the Microsoft Excel Actual Summary report Export from FRS Unformatted

Step 3b:  Format Forecast Report in Excel

To Format this spreadsheet more appropriately:

Highlight the data
Select ‘Format’ from the ‘Home’ menu (Excel 2010).
Select ‘AutoFit Column Width’ from the drop down menu.

The following changes to the spreadsheet should appear:

Format Forecast Report in Excel - sample diagram

Step 4:   Insert Columns into the Spreadsheet

Insert two columns between Comm and Budget.
Label them Additional Rev/Exp and Y/E Forecast (as displayed below).

Insert Coumns into Spreadsheet -sample diagram

Step 5:  Setup Excel Spreadsheet Formulas

Setup row totaling formulas in columns F, G and I for row 3 (Total Revenue), row 12 (Total Expenses) and row 13 (Grand Total).
Setup formulas in column G for rows 2, 4 – 11 which add columns D, E and F.
Setup formulas in column I for rows 2, 4 – 11 which subtracts column G from column H.
See sample formula in columns F, G and I displayed below.

* Where there are no Revenue rows, select the appropriate rows for the calculation totals.

Setup Excel Spreadsheet Formulas- sample diagram

Note:   Please ensure that you do NOT include column C (Mtd Act) in your calculation in column G, as the amounts from column C are already included in column D (Ytd Act).

Step 6:  Type the Department Number into the Excel Spreadsheet

Type the department number and name into cell location A16.

Step 7:  Save your Forecast Spreadsheet

Click File Save As and type a filename which includes the dept/college/division identifier and Fund type. Before clicking Save, please ensure that the ‘Save as type’ field is changed from ‘CSV (Comma Delimited)’ to ‘Excel Workbook’.                                                                             

Step 8:  Determine and Enter your Forecast 

Determine the revenue which is not included in Ytd Actual (column D) but needs to be included in the fiscal year ending April 30, 2018.  Key this amount, if any into the appropriate cells.
Determine the expenses which are not included in Ytd Actual (column D) or Commitments (column E) but needs to be included in the fiscal year ending April 30, 2018.  Key these amounts into the appropriate cells. Remember to add the appropriate benefit amounts for estimated salary costs that are not already reflected in the commitment column.

Column F (Additional Rev/Exp) is the only column where you should key any values.  

Note:  Please do not key values into the Mtd, Ytd, Comm, Y/E Forecast, Budget or Balance columns.

Step 9: Review and Approve your Forecast

Save the spreadsheet.

  1. Review the accuracy of your forecast:
    1. spreadsheet calculations are correct
    2. includes all your units and all projects (excluding PDR projects)
    3. includes revenue, recoveries and transfers from all sources
    4. includes expenses from all sources (vendors/suppliers/employees).
  1. Analyze the surplus/deficit (Grand Total in Column I) and provide explanation for the difference.
    1. Where a significant surplus exists, please provide an analysis of how this surplus was generated.
    2. Where a deficit exists, provide the causes for the deficit and how the department will be repaying this amount.
  2. Prepare the Forecast Carryforward Summary (see Appendix F)
  3. Acquire formal approval of final forecast from the responsible officer (Dean/Director/VP etc) for the Operating unit.

Step 10:  Submit your Forecast   

Email your completed and approved spreadsheets and comments to one of the Budget Office staff members listed on page #1.

Appendix C: OMAFRA Forecast by Project

Step 1:  Create FRS Report -   “Summary by Project”

Login to FRS.
Select ‘Actual’ from the menu bar.
Select ‘Summary’.
Select ‘Project’.
At the Prompt screen:
    Enter Fund:      110, 111 or 112
    Enter Unit:     your department
    Status: All
    Group: None,                Then By: Program 1 - Project
    Click ‘Run’ button

Step 2:  View in Excel

See Appendix B, Step 2.

Step 3:  Create Forecast Report in Excel

See Appendix B, Step 3a & 3b

Step 4:  Insert Columns into the Spreadsheet

See Appendix B, Step 4.

Step 5:  Setup Excel Spreadsheet Formulas

At the bottom of your project listing, setup a row which totals all your projects for the following columns:  Additional Net Rev/Exp, Y/E Forecast and Balance.
Setup formulas in column G for all project rows which adds columns D, E and F
Setup formulas in column I for all project rows which subtracts column G from column H.
See sample formula in columns F, G and I displayed below.
Setup Excel Spreadsheet Formulas

Note:   Please ensure that you do NOT include column C (Mtd Act) in your calculation in column G, as the amounts from column C are already included in column D (Ytd Act).

Step 6:  Type the Department Number into the Excel Spreadsheet

Type the department number and name into column A, first or second empty row in your spreadsheet.

Step 7:  Save your Forecast Spreadsheet

Click File Save As and type a filename which includes the dept/college/division identifier and Fund type. Before clicking Save, please ensure that the ‘Save as type’ field is changed from ‘CSV (Comma Delimited)’ to ‘Excel Workbook’.

Step 8:  Determine and Enter your Forecast 

Determine the revenue by project which is not included in Ytd Actual (column D) but needs to be included in the fiscal year ending April 30, 2018.
Determine the expenses by project which are not included in Ytd Actual (column D) or Commitments (column E) but needs to be included in the fiscal year ending April 30, 2018.

Net the additional revenue with the additional expenses.  Key this amount in column F.

Column F (Additional Net Rev/Exp) is the only column where you should key any values.  

Note: Please do not key values into the Mtd, Ytd, Comm, Y/E Forecast, Budget or Balance columns.

Step 9:  Review and Approve your Forecast

Save the spreadsheet.

  1. Review the accuracy of your forecast:
    1. spreadsheet calculations are correct
    2. includes all your units and all projects (excluding PDR projects)
    3. includes revenue, recoveries and transfers from all sources
    4. includes expenses from all sources (vendors/suppliers/employees).
  1. Analyze the surplus/deficit (Grand Total in Column I) and provide explanation for the difference.
    1. Where a surplus exists, provide a plan on how this amount is to be spent in subsequent year(s).
    2. Where a deficit exists, provide the causes for the deficit and how the department will be repaying this amount.
  1. Acquire formal approval of final forecast from the responsible officer (Dean/Director/VP etc) for the Operating unit.

Step 10:  Submit your Forecast

Email your completed and approved spreadsheets and comments to one of the Budget Office staff members listed on page #1.

Appendix D: Completed General Operating/Diploma Education/ OMAFRA Operating Departments/ Department Forecast Sample

Completed MTCU/Diploma Education Department Forecast Sample

Appendix E: Completed OMAFRA Project Forecast Sample

Completed OMAFRA Project Sample

Appendix F: Forecast Carryforward Summary

There is one additional summary format that has been added to the request. It is provided with instructions in a separate excel file.  The Carryforward Summary is a schedule showing the change in your total carryforward for the year.  Please submit this overall summary (one per fund and division/college) in addition to the regular forecast worksheets that make up your submission.

The Summary can be used in conjunction with your approved Carryforward plan submission from the prior year under the terms of the new Carryforward policy for you to assess progress and compliance.  The excel file can be downloaded from the Budget Office Policies and Procedures Page.

Instructions:

Step #1: Identify the Unit being summarized (ie College #1) and the Fund source (Fund 100 General Operating).

Step #2: Enter the Carryforward amount included in your 2017/2018 Budget for this unit into Cell C13 - you may have to use FRS to confirm the current amount.

Step #3: Populate revenue and expense amounts from your completed forecast in coloured cells C14, C15, C16 and C17.  These values are copied by formula to the summary format below and calculates the ending carryforward for your forecast.

Step #4: Ensure that the Forecast Carryforward - April 30, 2018 (cell D25) matches the overall balance from your completed 17/18 forecast submission for the unit.

Step #5: Review the results shown and provide notes of explanation for significant changes in carryforward for the year.