SI-CRE-FC20 Project Cost Forecasting - Budget Allocation

Applies to

• Product version: Velixo NX
• ERP: Sage Intacct
• Functional areas: Financials (GL, Budget), Project
• Industry: Construction
• Subscription Plan: Construction, every plan
• Template type: Production Report template

Description

The SI-CRE-FC20 Project Cost Forecasting - Budget Allocation Report Template is tailored for construction companies and is considered an essential tool for project directors or Finance professionals to:

• distribute the remaining costs of a project over the duration of the project, on a monthly basis.
• choose how to spread the costs among several distribution methods.
• create monthly forecasts as budget entries in Sage Intacct for Finance professionals to visualize forecasted cash flows

This report extracts the Last Cost at Completion Forecast from Project Estimates (typically populated by one of Velixo's Cost To Complete Report Templates), then based on the actual Job To Date values (extracted directly from the GL), it calculates the remaining costs to distribute over time for each cost codes, based on the planned start date and planned end date of each cost code.

Note: Velixo offers several ways to distribute costs: Bell Curve, Front-Loaded, Back-Loaded, Linear, or Manual.

Tip: This Template works particularly well with the SI-CRE-TOOL11 Update Project Cost Codes (to maintain/update the Planned Begin Date and Planned End Date of each cost code of a project) and the SI-GL-RT7 Cash Flow Forecast (budget-based) or SI-GL-BT8 Profit and Loss Forecast (budget-based) to report on the forecasted values over time.

Coverage

This workbook includes the following sheets:

Typical audience

The typical users of this type of report are Project Directors, Accountants and Finance professionals.

Features

The following features are used by this report. To use this report, please ensure that your Velixo license includes all of them, (or contact Support or Sales):

• Financial functions
• Project Functions
• Query functions
• Budget Writeback

Preview

Download this template

Download

💡Having trouble downloading the file?

Simply right-click on the link and select Save link as

Important Note: every sheet of this Template is locked and protected by default, with no password (blank) by default. To unlock a sheet, simply go to the Review tab and select the "Unprotect Sheet" option (or right click a Sheet and select "Unprotect").

Documentation

Navigating and using the SI-CRE-FC19 Cost to Complete (Amount) by Cost type template is a fully dynamic experience that should be efficient and easy to use. It was specifically created for forecasting the remaining costs of a project over time in the future, enabling users to allocate costs based on several distribution methods, throughout the duration of a project, for all cost codes of the project.

Cost Forecast sheet

Filters

Located in the upper left of the sheet, the filters include:

• Entity: lists all entities present in Sage Instance (excluding groups). For ease of use, Velixo shows you the Entity id that you may be familiar with along with the entity name.
• Project: a dropdown list of all the available projects in the Sage instance. When a project is selected, the Project Manager field is automatically populated, allowing for easy identification of the primary contact for the project.
• As of: a date field. If left blank, it will default to the current date.

The remaining two Filters are input parameters used for writeback and for spreading the costs. They will be covered in a separate section of this documentation.

Cost codes and Planned dates

The first four columns of the report return a dynamic list of cost codes and their respective planned start and end dates.

Cost code headers are also displayed and are conditionally formatted in bold with Top and Bottom borders to clearly differentiate them from the main cost codes.

This includes the TOTAL row (first row) that returns the aggregated value of every relevant metric of the report.

Tip: Use the SI-CRE-TOOL11 Update Project Cost Codes to easily maintain the Planned Start Date and Planned End Date of cost codes. This Template allows you to update these dates in one go without jumping from screen to screen within Sage Intacct.

Job To Date

This section represents the actual costs incurred up to the As of date filter (since the project inception).

Actual Costs are extracted from the GL using the Accrual book.

Note: The Job To Date amount is extracted from the General Ledger accounts associated with the following out-of-the-box QuickStart account categories (which can be reconfigured in the report if needed):

• Cost of Sales - Materials
• Cost of Sales - Labor
• Cost of Sales - Equipment
• Cost of Sales - Subcontract
• Cost of Sales - Other
• Cost of Sales - Overhead

Last Cost at Completion Est.

This column returns the last "Cost at Completion forecast" Project Estimate for this project, based on the As of Date.

To override this logic, it is also possible to manually specify a valid EstimateId in cell G14.

Distribution columns

This section includes several columns used to distribute values and write them back to Sage Intacct.

• Writeback Status: the status of the Velixo SI.WritebackBudget function. This will show "Pending" when the function is ready to perform a Writeback action or "Line Uploaded" after a successful writeback action, or an error if it requires further actions from the user. See the Using the report section for more details.
• Budget Distribution Type: is an informative column, showing which distribution method is used to distribute amounts over months for a given cost code, as configured in the Std Cost Code Mapping sheet.
• Shift Days (+/-): by default, Velixo will start distributing amounts from the first month (found from the list of Planned Start Dates), however it is possible to shift this distribution by a specific number of days. This is configured in the Std Cost Code Mapping sheet.
• Amount Distributed: is the total of the distributed amounts by month. This is an informative column only. This column includes conditional formatting, it will show a red background to alert the user that a value may be different from the Remaining Cost to Distribute value, due to the selected distribution method over time or rounding errors. In most cases, it will be just a few cents wrong. Note that Velixo doesn't add this rounding variance back to the amount that is written back to Sage.
• Remaining Cost to Distribute: is the main Amount column of the report, and the amount column the distribution is based on. It is simply calculated as follows: Last Cost at Completion Est. - Job To Date.
• Note: an optional user-input field, corresponding to the memo field of a Budget entry.

Monthly Budget Distribution columns

This dynamic section displays as many months as the number of months there are between the Planned Start Date of the first cost code and the Planned End Date of the last cost code.

The Remaining Cost to Distribute amounts are automatically allocated based on the Budget Distribution Type configuration.

Manual Distribution section

If the Distribution method of a Cost Code is set to Manual, then users can manually enter amounts in the Manual Distribution section on the far right of the report.

For usability purposes, the rows set to Manual are highlighted in light blue in the Manual Distribution section, denoting that users should enter their manual distributions there. These entered amounts will then be used in the Monthly Budget Distribution section for their respective months.

Std Cost Code Mapping sheet

This sheet lists all standard costs codes of your Sage Intacct instance. It is a good idea to map them all so it applies to every project.

This sheet allows users to:

• override the Default GL account (as specified in the Options sheet) used by the Budget Writeback function in the Main report. Simply enter an account no in the GL Account (for Writeback) column.
• Map a Budget optionfor each standard cost code among:
  • Front-Loaded (Curve): use this option to adjust the distribution so that more spread occurs at the beginning of the project timeline and less at the end of the timeline
  • Back-Loaded (Curve): use this option to adjust the distribution so that less spread occurs at the beginning of the project timeline and more at the end of the timeline
  • Bell Curve: use this option to adjust the distribution so that less spread occurs at the beginning and also at the end of the project timeline and more at the middle of the timeline
  • Linear (Curve): use this option to distribute amount evenly over the duration of the project.
  • Manual: use this option to manually enter amounts for each month of the project timeline

Note: Using the Standard Cost Code (STANDARDTASK) object was preferred by Velixo over the Cost code (TASK) object so that the Excel table would not refresh and users would not lose their configurations.

• Budget shift days (+/-): use this column to enter a number to shift the start of the monthly distribution of each cost code by a specified number of days.

Configuring the report

Now that we've covered all the fields and purpose of the Template, let's now dive into a step-by-step guide on effectively utilizing the report.

• Set the Connection Name on the Options sheet: crucial for all processes, set the Connection Name on the Options sheet by updating cell A2. Ensure it matches the name used during Velixo application login.

• Specify the cost code header format: by default, this template uses the cost code header format from the standard Sage Instance demo instance. If the last 4 digits of a Cost Code are 0s (zeros), it usually means a Cost Code is a Cost Code Header used for grouping Cost Codes. This is what Velixo uses to conditionally format Cost Code rows in the main report. Please review this setting and match the format to the Cost Code sequence of your Sage Intacct instance in order to leverage this conditional formatting.

• Enter a Default GL Account: This GL Account will be used by default to populate the GL Account (for Writeback) column of the Std Cost Code Mapping sheet, but it can be overridden in this sheet. It is required that every cost code be mapped to a valid GL Account for writing back to Budget (since the report is not at the Cost Type granularity).
  

• Update the Options Sheet: as the foundation for the filters on the main report, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Sage Intacct instance.

• Review the parameters for the Job To Date column (optional): expand the grouped rows from rows 1 to 6 to display additional configurations such as: account codes and books. Those values should be standard yet users can still modify them:
  • Account codes: this is set to default based on the mapped account codes to the following QuickStart categories. Users can opt to use account groups as an alternative.
    • Cost of Sales - Materials
    • Cost of Sales - Labor
    • Cost of Sales - Equipment
    • Cost of Sales - Subcontract
    • Cost of Sales - Other
    • Cost of Sales - Overhead
  • Book: by default, the generic ACCRUAL book is used.
• Description: This is a generic description for the Budget that you may change.

Using the Report

As a project director or Finance pro, this report would typically be used as follows:

Choose selections

• Choose desired filters: after updating the Options sheet, select desired filters for the report on the main sheet:
  • Entity: select from the list of locations
  • Project: select from the dropdown list
  • As Of (optional): you may leave the field as a blank value for the report to use the current date
  • Forecast Budget: enter a custom BudgetId for your forecast. Use a name that will work across projects for the given reporting period.
  • Forecast Start (optional): by default,ult this value is pre-populated with the earliest Planned Begin Date of all cost dates. They may however optionally override it.

Automatic report update: selecting any filter will automatically update report balances. Begin analyzing the report with the latest data after refresh. However, in some cases, you may have to refresh the entire workbook by clicking Refresh > Entire Workbook from the Velixo NX ribbon.

Perform Writeback

• Once the required details are populated and that you have reviewed the cost distribution, you can perform the writeback action by clicking Current Writeback > Current Worksheet

• After completing the Writeback process, the Status in column X will change to "Line uploaded" for each cost code row

• You may now navigate to Sage Intacct to confirm that your new BudgetId and the cost code budget entries have been successfully created.

Version History