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 Budget & Financial Planning 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.

Budget & Financial Planning 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 Budget & Financial Planning STAFF IDENTIFIED BELOW.

For assistance, please contact Budget & Financial Planning:

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. 

2. 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
3. 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.
4. 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.
5. 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.
6. Completed forecasts are to be submitted to Budget & Financial Planning 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”

Step 2:  View Report in Excel

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

Step 3a:  Create Forecast Report in Excel

The following Excel spreadsheet should appear:

Step 3b:  Format Forecast Report in Excel

To Format this spreadsheet more appropriately:

The following changes to the spreadsheet should appear:

Step 4:   Insert Columns into the Spreadsheet

Step 5:  Setup Excel Spreadsheet Formulas

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

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 

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).

2. 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.
3. Prepare the Forecast Carryforward Summary (see Appendix F)
4. 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 Budget & Financial Planning staff members listed on page #1.

Appendix C: OMAFRA Forecast by Project

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

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

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 

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).

2. 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.

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 Budget & Financial Planning staff members listed on page #1.

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

Appendix E: Completed OMAFRA Project Forecast 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 Budget & Financial Planning 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.