The intent of this article is to provide a breakdown of the Forecast Worksheet. This article will:
- provide an overview of each main section
- identify and describe each column
- define the calculations for each column
The Forecast Worksheet can be broken down into 8 main sections of the Forecast
The Forecasting section of the Cost Management Module is designed for environments with Accounting Integrations. At this time, Forecasting is not available for stand-alone Cost Management modules. Article Content Navigation
Budget Values
The Budget Values section of the forecast worksheet is the listing of Cost Codes and Categories from the Estimate Info Section of the project at the time the forecast was created. These columns are "frozen" and will stay in place when scrolling to the right of the forecast worksheet. For Summarized Forecasts, the category column will be blank, please refer to the Summarized by Cost Code article.
#1: Extras
The Extras column is only applicable to Sage 300 customers that have setup Extras in Sage 300 for Estimate Cost Codes. This column can be hidden by right clicking in the Extras column header, selecting Columns, and de-selecting Extras from the list.
#2: Cost Code
The list of Cost Codes from the Estimate Info section of the project
#3: Cat. (Category)
The category of the assigned Cost Code to forecast against.
Estimate
The Estimate section of the forecast worksheet displays the information as it exists in the Estimate Info section at the time the forecast worksheet is created.
#4: Original Units
The Original Units column is only applicable to Sage 300 customers that have setup Units within Sage 300. This column can be hidden by right clicking in the column headers, selecting Columns, and de-selecting the column from the list.
#5: Original Units Rate
The Original Units Rate column is only applicable to Sage 300 customers that have setup Units with Unit Rates within Sage 300. This column can be hidden by right clicking in the column headers, selecting Columns, and de-selecting the column from the list.
#6: Original
The Original column is the Original Estimate value as it is held within the Estimate Info section
#7: Approved Change Orders
The Approved Change Orders column is the value of all approved Contract Change Orders that are hitting Estimate Info at the time the forecast worksheet was created
#8: All Pending Changes
The All Pending Changes column is the value of all pending Contract Cost Event Types
#9: Total Units
The Total Units column is only applicable to Sage 300 customers that have setup Units within Sage 300 for Estimate Original units from the list. This column can be hidden by right clicking in the column headers, selecting Columns, and de-selecting the column from the list.
#10: Total
The Total Column is the sum of the Original (#6) value plus (+) all Pending Changes (8) plus (+) all Approved Changes (#7)
Total Column calculation: #10 = #6 + #7 + #8
Costs
The Costs section of the forecast worksheet displays the information as it exists in the Commitment Info section at the time the forecast worksheet is created.
#11: Original Commitment
The Original Commitment column is populated by the total Original Commitment Amount values that hit that cost code from the Commitment Info section
#12: Approved Commitment Changes
The Approved Commitment Changes column is populated by the Total Approved Change Orders values that hit that cost code from the Commitment Info section
#13: Pending Commitment Changes
The Pending Commitment Changes column is populated by the Pending Change Orders values that hit that cost code from the Commitment Info Section
#14: Total Commitment
The Total Commitment Column is the sum of the Original Commitment (#11) value plus (+) all Pending Commitment Changes (#13) plus (+) all Approved Commitment Changes (#12)
Total Column calculation: #14 = #11 + #13 +#12
#15: Non-Commitment Cost
The Non-Commitment Cost column is only applicable to Sage 300 customers. This value is all JTD Costs that are not included within a commitment.
#16: Actual Units
This is a Sage 300 value shared to us
#17: Actual Unit Rate
Actual units divided by JTD cost from Estimate
#18: Prior Total Cost
The Prior Total Cost column is populated with the Total Cost value of the previous Forecast Worksheet when Copy Worksheet is used.
#19: Total Cost
The Total Cost column is the sum of the Total Commitment (#14) value plus (+) the Non-Commitment Cost (#15)
Total Cost Calculation: #19=#14 + #15
#20: Total Cost Delta
The Total Cost Delta column is the difference between the Prior Total Cost column (#18) value minus (-) the Total Cost column (#19)
Total Cost Delta Calculation: #20=#19 - #18
Balance to Complete
The Balance to Complete section of the forecast worksheet that compares the Estimate Section to the Cost Section.
#21: Balance to Complete Units
The Balance to Complete Units column is the difference between the Estimate section's Total Units column (#9) minus (-) the Cost section's Actual Units column (#16)
Balance to Complete Units Calculation: #21=#9 - #16
#22: Balance to Complete $ (Value)
The Balance to Complete $ column is the difference between the Estimate section's Total (#10) value minus (-) the Cost section's Total Cost column (#19)
Balance to Complete $ Calculation: #22=#10 - #19
Forecast
The Forecast section of the forecast worksheet is where any costs not included in the Costs Section of the Forecast can be identified for a cost code.
This section is where you forecast the cost to complete the project.
#23: Units
The Units column can be used to identify units in the additional forecasted cost. If lump sum, the unit's column remains one (1.00) and the unit cost would be entered as the lump sum.
#25: Unit of Measure
The Unit of Measure column is where the Unit of Measure can be included, however it is not required.
#26: Unit Cost
The Unit Cost column is where the additional cost value is identified.
#27: Forecasted Cost to Complete
The Forecasted Cost to Complete column is the product of the Units column (#23) multiplied (x) by the Unit Cost column (#26).
Forecasted Cost to Complete Calculation: #27=#23 x #26
#28: Total Forecasted Job Cost
The Total Forecasted Job Cost column is the sum of the Total Cost from the Cost section (#19) plus (+) the Forecasted Cost to Complete column (#27).
Total Forecasted Job Cost Calculation: #28=#19 + #27
#29: Forecasted Variance
The Forecasted Variance column is the difference of the Total from the Estimate section (#10) minus (-) the Total Forecasted Job Cost column (#28)
Forecasted Variance Calculation: #29=#10 - #28
#31: % Of Estimate Used
The % of Estimate Used column is the calculated percentage of the Total Estimate (#10) divided by (/) Total Costs (#19) multiplied (x) by 100.
% Of Estimate Used Calculation: #31=#10 / #19 x 100
This column will be moved into the Costs Section in a later version of the Forecast section.
Risk
The Risk section of the forecast worksheet is a location to include risk on your forecast. Risk is identified as a percentage.
#32: Risk on Remaining Work
The Risk on Remaining Work column is where risk can be incorporated into the Forecast numbers. Risk is entered as a percentage and is used to calculate the Forecasted Job Cost with Risk (#33).
#33: Forecasted Job Cost with Risk
The Forecasted Job Cost with Risk is the sum of the Forecasted Cost to Complete (#27) plus (+) the calculated risk percentage calculated value (the calculated value is the percentage entered into Risk on Remaining Work (#32) multiplied (x) by the Forecasted Cost to Complete (#27)).
Forecasted Job Cost with Risk Calculation: #33=#27 + #32 x #27
#34: Forecasted Risk Variance
The Forecasted Risk Variance column is the difference of the Total from the Estimate section (#10) minus (-) the Forecasted Job Cost with Risk column (#33)
Forecasted Risk Variance Calculation: #34=#10 - #33
#35 Notes
The Notes section is a location to simply capture any notes that may need to be captured about a specific line item.
Important Notes
If Commitment Cost Code is not an Estimate Cost Code, it will not show in the forecast until the commitment has been synchronized with Sage 300 and Estimate info is updated with the sync. (Quick Adding an Standard Cost Code from the Org Level to the Job via a Commitment)
If the Commitment Cost code selected is a header cost code, it will not show on the forecast. (Also, the Commitment will not synchronize with Sage 300)
If a Commitment Event is Quick Adding a Cost Code Commitment Line, it will not show on the forecast. (Sage 300: Quick Add Commitment Line via Commitment Event)
The Forecast Tab is created as a snapshot in time of the Estimate Info section of the project.
Additional Resources
Summary Info Section vs. Forecast Summary Tab
Defining the Change Event Pending Types Included in Forecasts
Setting the Labor Category Used in Forecasting
