Prior to reading the instructions on the Forecast Summary Worksheet, it is beneficial to review the following sections of the IU Accounting Standards Book to gain foundational information along with requirements and best practices:
Forecasting refers to the process of using current and historic cost data to predict future costs and assists fiscal officers and leadership in projecting future outcomes and/or trends. The Forecast Summary Worksheet is a management tool that allows the user to view actual revenues and expenses and remaining fiscal year encumbrance balances alongside an adjustable projection column. This enables the user to change the final projected balances to gain a better understanding of how business decisions or operational changes may affect financial outcomes. The Forecast Summary Worksheet is also key in goal setting (i.e., setting target profit or revenues) as it helps project the financial position in which the entity is projected to finish. Additionally, it is important for internal control compliance through monitoring and keeping expenditures under a certain budgeted dollar amount. The main purposes of this report are for planning and control.
How the Forecast Summary Worksheet Works
Unlike other reports in the Controller’s Office Reporting Tools, the Forecast Summary Worksheet has a column that allows the user to add values to manipulate financial data outcomes. It displays actuals through the selected period, remaining encumbrances for the fiscal year (includes internal and external encumbrances (IE, EX) with a parameter option to include pre-encumbrances; cost shares (CE) are not included), and an adjustable Projections column where the user can input data to adjust forecasted balances. The remaining columns display the new projected Forecast balance (summation of the first three values that changes based on user input into the projections column), the total fiscal year Budget (user has the option to select Current Budget, Adjusted Base Budget, or both to be displayed on separate tabs), and the Variance of the adjusted Forecast number to the Budget.
The user can see the impact certain financial decisions might have on their current fiscal year balances. In scenarios where an initial budget was never prepared for the entity, the Forecast Summary Worksheet will only have actuals populated and the budget column with zeroes. It assumes that since nothing was budgeted for, no activity is expected. A quick way of checking for whether an initial budget was prepared is by looking at the Budget column for zeros throughout.
The Forecast Summary Worksheet can be used in conjunction with the Forecast Report. Whereas the Forecast Report displays where a unit is projected to end the year next to where they had planned or budgeted to end, this worksheet enables the user flexibility to adjust the forecasted balance without making any actual adjustments to actuals, encumbrances, or budgets. This is key in aiding units in predicting and planning for revenue and expense variations. It allows the user to gauge potential outcomes of business decisions or operational changes and their impact on financial performance. For specific descriptions and details on each column of the worksheet, see the collapsible section directly below.
Column Header
Description
Object Description
Identifies the descriptive name of the object code.
Object Code
Organizes and catalogs financial data and classifies a financial transaction (i.e. income, expense, asset, liability, or fund balance).
Sub Obj
The sub-object code specifies a more detailed breakdown of an object code.
Actual Through [Period Selected] (1)
Summation of the actual general ledger balances for a given object code or object level for the current fiscal year through the specified period.
Remaining Encumbrances (2)
There are four types of encumbrances within the Kuali Financial System. These include Internal Encumbrances, External Encumbrances, Pre-Encumbrances and Cost Share Encumbrances. By default, this report will include Internal Encumbrances (balance type code IE) and External Encumbrances (balance type EX) for the current fiscal year. The user has the option to include Pre-Encumbrances by checking the box in the report parameters. This report does not include Cost Share Encumbrances (balance type CE).
The remaining encumbrance amount for each object code is the initial encumbrance less the amount relieved to date. For example, if the initial encumbrance was for $12,000 and $8,000 had already been relieved, the remaining encumbrance would be $4,000.
Projections (3)
Adjustable column where the user can input values to manipulate forecasted balances.
Forecast (1) + (2) + (3)
Summation of Actuals + Remaining Encumbrances + Projections.
FY XXXX Current/Adjusted Base Budget
The Forecast Summary Worksheet can be used for a variety of reasons, however, they will generally fall into one of two categories:
Forecast/assess if the unit (campus, RC, organization or account) will end the year over or under budget – select Current Budget in the report parameters.
Identify any budget increases or decreases needed in subsequent years – select Adjusted Base Budget in the report parameters.
The user can select Both in the report parameters which will generate both the Current and Adjusted Base Budget as two separate tabs within the report.
Variance Forecast-Budget
Calculates the difference between the Forecast amount and the Current/Adjusted Base Budget amount.
The Forecast Summary Worksheet can be located at the top of this page - refer to the top right blue button labeled "Go to Report" which will automatically redirect users to the report within the Controller's Office Reporting Tools.
In addition, the worksheet can be found in One.IU. To find the worksheet, search for “Controller’s Office Reporting Tools” in the search bar, and select Controller’s Office Reporting Tools (Report Center) in the drop down menu. Mark this task as a favorite by clicking the heart icon next to the start button and then select Start or click on the title.
Once in the Controller’s Office Reporting Tools, users will see all available reporting tiles. Navigate to the Financial Statements folder. New tiles open, which display all the available financial reports. Select Forecast Summary Worksheet.
Running the Report
Define the search parameters to return results relevant to your organization or to a specific account. The search parameters available in the Forecast Summary Worksheet are divided into three parameter types: chart of account report parameters, report specific parameters and display parameters.
If there are questions related to running the report, requirements or reviewing results, please contact your (RC) fiscal officer or campus office. Each campus may have individual specific requirements related to the Forecast Summary Worksheet, so be sure to reach out to the related campus office or fiscal officer prior to quarter closings.
General Notes: Do not include any special characters other than approved wildcards in any of the below parameters. Additionally, do not run reports by campus as it will take up valuable computing services.
Users can enter the fiscal year, fiscal period, responsibility center (RC), organization code and account for their respective organization to narrow their report results. If unsure, several of the parameters have a valid values function which displays the available codes and corresponding descriptions.
Used to limit to a specific RC. Users are encouraged to use this parameter in conjunction with a chart of accounts code. For assistance determining your RC code, refer to KFS RC Lookup.
Check/Uncheck – The organizational hierarchy presents the financial data based on organization code and provides a high level review of the data at a more consolidated level. In many cases an organization reports directly to another organization. By selecting the organization hierarchy, users will be able to see all data from the selected organization plus any organizations that report to the selected organization.
Report specific parameters are parameters specific to the generation of the report.
Parameter
Description
Budget Type
Used to select which budget type to be displayed in output:
Current Budget – presents information using Current Budget balances.
Adjusted Base Budget - presents information using Adjusted Base Budget balances.
Both – presents information for Current Budget and Adjusted Base Budget displayed in separate tabs.
Subtotal Options
The subtotal option is used to show subtotals within the worksheet at different levels:
No Subtotal by Object Code – presents financial information at a high level in a consolidated format such as net income (loss) subtotal.
Subtotal like Income Statement - organizes subtotals similarly to the Income Statement which is shown at the object level. Users are recommended to use this option as it is easily comparable to the Financial Statement Report.
Subtotal by Classification by Object Code – this provides information at the highest level of detail by showing subtotals of all the revenue object codes together and similarly, the expense object codes together. The net income, or loss, is the final subtotal.
Include Pre-Encumbrance Balances
Check/Uncheck – includes Pre-Encumbrance (PE) financial balance type in the report.
Display parameters are parameters that define and restrict the visual presentation of the Forecast Summary Worksheet. They are found on the lower part of the parameters.
Parameter
Description
Include Accounts Page (Cover Page)
Check/Uncheck – includes a cover sheet tab showing all the accounts included in the reports.
Include Closed Accounts
Check/Uncheck – includes the accounts closed during and in prior periods. Users are encouraged to check this box especially for comparative periods.
Exclude Transfers from Operating and Non-Operating Margins (Required for UCO closing materials)
Check/Uncheck – transfer object codes are pulled from above the Net Income line and presented below Net Income to aid in financial decision-making.
Report Style
The report style parameter is used to limit level of detail required in the reports:
Consolidated – presents financial information in a consolidated format for the display level requested i.e. organization or account.
Detail by Account – higher level of detail showing the different accounts separately.
Detail by Account and Sub-Account – Highest level of detail showing different sub accounts within an organization or account separately. Note: users are not able to select detailed levels if the org hierarchy box has been checked – see chart of accounts report parameters above for more detail.
Select the Output Format
Report will be generated in Excel version only.
Select the Output Destination
The user has the option to choose where they want their report to go once it is ready. The user can either select Wait for it to pop up on the screen or Email. If the user selects email output destination, an email will be sent with the link to the selected output format.
Click Save Parameter Settings to save your parameters for future use of this report. For instructions on how to save settings, review the Save Parameter Settings document on the Controller's Office Reporting Tools page.
