Long Term Budget Planning - Forecasting

A financial forecast is an estimate of future financial outcomes for a project. The Forecasting Template by Project Grant is used as necessary to assist faculty with long term budget planning for projects. The forecasting tool includes salary information in Wolverine Access as well as “actual” fringe benefit rates.  Accurate financial forecast relies on the additional following pieces of information:

• Known funding

• Known expenses

• Trending expenses

• Ability to track unexpected expenses

• Status of project aims and scientific progress

• Ability to make financial assumptions based on project knowledge

The Forecasting Template is not a shadow system to be maintained on a monthly basis.  It is a tool which uses a template and Business Objects reports to assist in the projection of various scenarios. This document can be saved for future reference, but is not considered to be a final financial document or formal record for audit.

This Forecasting Template allows users to import data from a Business Objects query to a spreadsheet. Once the data is imported, the template functions like an excel file.

Business Objects Query set-up (This step only needs to be taken the first time the query is used)

***THE FOLLOWING STEPS ARE VERY IMPORTANT*** 

Copy the query into the “My Favorites” folder. This is ABSOLUTELY necessary in order for the query to be in an editable state.

• Right-click the Report title and select Organize > Copy.

• Click on the My Favorites folder. From the toolbar, select Organize > Paste.

The report is now located in the My Favorites folder. In the future, you should always run the report from the My Favorites folder.

Running the Business Objects Query

• Open the report from the “My Favorites folder 

• Enter End Date:  This should be the most recent SOA close date

• Enter P/G

• Job Eff Seq should always be: “Max Sequence”

• Enter Estimate Begin Date (This date will reflect the 1st month to start forecast. In most cases you would use the 1st day of the month after business last closed, but you could also enter a retroactive start date for the forecast)

• Enter Estimate End Date: (This date will reflect the end date of the forecast)

• Click Run

Copying the Business Objects query to the Template

 **Do not save the data to an excel sheet for copying and pasting**

• In the top right corner, verify that Design mode is selected.

• Click the edge of the table so that the  4-directional arrow displays.

• Select File > Copy from the left panel to copy the entire table. Alternatively, press Ctrl+C.

• Data from each tab will need to be copied and pasted. The names of the tabs on the Business Objects report correspond with tabs on the Template. On the Template, each tab has instructions for where the data should be pasted. 

• To ensure you are using the most current version of the template, download a new version for each use.

The data pulled via the Business Objects report is for the last month closed, therefore it is necessary to manually enter the lump sum of encumbrances.

• Find the M-Report for this Project/Grant.  Calculate the total of the blue columns (direct and indirect costs), and enter that total on the “Exp Forecasting” tab, cell L8.

• Verify that the summary tab, (A)Current Budget and (B) Total Charges to Date, match the current M-Report.

The Template can now function as an excel worksheet to forecast various scenarios.

• Enter/change information only in the Addt’l Bdgt and Exp Forecasting tabs.

• Only change or enter information in the YELLOW cells.

Note: the salary calculations are generic, and may require that you modify formulas for salary caps and/or reduced appointments. See the following website for guidance: Budgeting Personnel