Construction template demo - Cost to Complete (Amount)

Applies to

• Product version: Velixo NX
• ERP: Sage Intacct
• Functional area: Financials (GL, Budget), Project, Purchasing
• Plan: Demo
• Template type: Demo template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Cost to Complete sheet
    • Filters
    • Summary Area
    • Main report
• Configuring the report
• Using the report
  • Choose selections
  • Enter values (Optional)
• Version History

Description

The SI-CRE-FC1.demo Cost to Complete (Amount) Report Template is tailored for construction companies and is considered an essential tool for project managers to:

• manage the various costs associated with the cost codes of a project
• track progress over time
• analyze key financial and project data to ensure the project remains on-time and within contractual budget
• forecast the Cost at Completion as well as the projected Over/Under value of a project, supported by an optional user-input Cost to Complete amount

This report automatically calculates the Cost at Completion based on the actual Cost to Date and Commitments yet to be Invoiced. The optional user-input Cost to Complete is then added to this value in order to vary the calculated Cost at Completion.

This report would use the Project Contract's Revised amount for each cost code, should the calculated Cost at Completion figure be lower.

Use of this Cost to Complete (Amount) method means that for each reporting period, every cost code item that has incurred cost must be reviewed by a project manager and an accountant.

This report can also be used to analyze the project data over time, by entering a date in the past.

Coverage

This workbook includes the following sheets:

• Cost to Complete: the main sheet of the Workbook, the Cost-to-Complete sheet displays various balances by cost code.
• Options: the template’s settings sheet that is used for data validation lists (hidden) and various lookups and configurations.
• Information: the information sheet of the report, used for storing useful details about the report such as code, version, and article link to keep track of the origin of the report.

Typical audience

The typical users of this type of report are Project Managers. Accountants and Finance professionals may also use this Template to write back to Sage Intacct.

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
• List functions
• Query functions

Preview

Download this template

Download

💡Having trouble downloading the file?

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

Documentation

Navigating and using the SI-CRE-FC1.demo Cost to Complete (Amount) template is easy and designed for clarity, flexibility, and effectiveness. This report was specifically created for reporting and forecasting construction projects, enabling users to review and estimate a project's cost at completion by cost codes.

Cost to Complete 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.

Summary Area

This area highlights the key KPIs of a Project, summarizing various figures, as calculated in the main report section:

• Contract and Estimates Summary: this section displays the difference between the contract price and the total project estimates. It also includes the profit margin amount and percentage, offering insights into the project's margin based on the estimates. 
  • the Contract column is based on information extracted from the Contract object
  • the Estimate column is based on the Total cells of the report in the Estimate section, which is extracted directly from the Primary Project Estimate 
• Schedule: this section compares the key dates of the project, originating from the Project Contract. scheduled start and completion dates in the contract and the total project estimates.
  • Start date: for both Estimated and Revised columns, the Scheduled Start date is used. If Scheduled Start date is not specified, the Actual Start date is used. For the Actual column, the Actual Start date is used
  • Completion: for Scheduled Completion date is used for Estimated column. For the Revised column, the Revised Completion Date is used unless it is not specified, in which case the Schedule Start date is used
  • Days: returns the number of days between the start date and the completion date, or today's date if the project doesn't have an Actual completion date
• Cost Status: provides an overview of the current costs incurred compared to the project estimates. It includes details on the actual costs, remaining budget, and percentage of costs utilized, giving users a clear view of the project's financial status.
• Margin Analysis: this offers an in-depth look at the project's profitability by comparing estimated margins with actual margins. It includes details on the profit margin, change order margin, adjusted margin amount and percentage.

Main report

This section provides the report breakdown by cost codes, including detailed information on estimates, commitments, costs to date, and forecasts. 

Cost codes

The first two columns of the report return a dynamic list of cost code for the selected project, which includes headers. Headers are conditional formatted in bold with Top and Bottom borders to clearly differentiate with the main cost codes. This includes the TOTAL row that returns the aggregated value of every relevant metric of the report. Values from this row are also used in the Project Summary section located above the main report area.

Estimates

The Estimates section details all of the various project estimates of the project for each cost code, estimated including Original, Change Orders, Revised, and Pending. These numbers are extracted using the SI.PROJECTESTIMATE function for the relevant Cost code using the relevant Workflow Type.

• Original (A): Uses the Original Workflow Type
• Change Orders (B1): Uses the Approved Orders Workflow Type
• Revised (C1): does not use the function, but instead sum the Original and Change Orders numbers.
• Pending Changes (B2): Uses the Pending Changes Workflow Type
• Revised incl. Pending chgs (C2): sums the Revised and Pending Changes column

Commitments

The Commitment section includes several Commitment metrics that are essential for tracking all purchase orders, subcontract orders and invoices by cost codes and as a whole.

To offer Velixo drilldown, these figures are extracted from the General Ledger, and therefore require Project-related POs and Subcontract Orders and Invoices to synchronize with their corresponding Sage Intacct books (which is a standard feature of Sage Intacct). If this setting is not enabled in your instance, please contact your partner or Velixo Support.

• Original (D): show commitment amounts for Purchase Order and Subcontract Order document types, posting to the GL against the following books: Purch Orders and Subcontracts
• Posted changes (E): show commitment amounts for PO Changed Order and Subcontract Change Order document types, posting to the GL against the following books: PO CO and Sub CO
• Revised (F): is the Sum of Original and Posted changes
• Commitment Invoiced (G): show commitment amounts for purchase orders that have been invoiced, have an AP Bill and have been posted to the GL against books: Sub Reversal, and PO Reversal
• Balance To Invoice (H): is the commitment amount that is yet to be billed, and is simply calculated as the difference between Revised (F) and Commitment Invoiced (G)

Cost To Date

This section represents the actual costs incurred up to the As of date filter (since the project inception). It also conveniently includes a Month To Date column to track cost code postings in the current month.

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

Forecast

This section displays the forecasted Cost at Completion and the derived (Over)/Under calculations.

• Last Cost at Completion: is an informative-only column that helps in the process of forecasting. It will return values only if an EstimateId is specified in Cell P28. This value may either be manually populated by a user, or is automattically populated after a successful Writeback action
• Cost to Complete (J): is a simple user-input Amount column that users can use to adjust the Cost at Completion values for each Cost code
• Cost at Completion (K):is a calculated field that returns the greatest amount among:
  • the Project Revised Estimate (including Pending Changes)
  • the sum of: Job To Date (I), Balance to Invoice (H) and Cost to Complete (J)
• (Over)/Under: simply calculated the difference between the Revised Estimate (C2) and Cost at Completion (K
• (Over)/Under Percent: is the variance percent between the Revised Estimate (C2) and Cost at Completion (K) 

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.

• 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 Commitments and Cost to date balances (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

• Books: the report comes pre-configured with default books that correspond to the standard user-defined books in the Sage instance. This setup is designed to work seamlessly out-of-the-box, however, the report still allows users to modify these settings as needed.

Using the report

As a project manager, 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: you may leave the field as a blank value for the report to use the current date

• 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.

Enter values (Optional)

Under the Forecast section, populate the Cost to Complete columns for each Cost code row. This will represent the additional cost to complete the task. The amounts specified will be added to the existing revised estimate.

Version History