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 [1].
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.
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:
Ryan MacLean x56184 |
Stephanie Ugrnov- Devost x54368 |
Michelle Curtis x52833 |
Larry Shuh x52860 |
Angeline Li x52044 (On Leave) |
SUBMISSION DEADLINE: February 9, 2018
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.
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.
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.
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.
At the lower left-hand corner, click on the ‘Excel’ option.
The following Excel spreadsheet should appear:
To Format this spreadsheet more appropriately:
The following changes to the spreadsheet should appear:
* 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).
Type the department number and name into cell location A16.
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’.
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.
Save the spreadsheet.
Email your completed and approved spreadsheets and comments to one of Budget & Financial Planning staff members listed on page #1.
See Appendix B, Step 2.
See Appendix B, Step 3a & 3b
See Appendix B, Step 4.
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).
Type the department number and name into column A, first or second empty row in your 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.
Save the spreadsheet.
Email your completed and approved spreadsheets and comments to one of Budget & Financial Planning staff members listed on page #1.
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.