SI-CRE-RT6 WIP

Applies to

• Product version: Velixo NX

• ERP: Sage Intacct

• Functional area: Financials, Budgeting, Project

• Industry: Construction

• Plan: All

• Template type: Production Report template

Description

The SI-CRE-RT6 WIP (Work In Progress) template is an essential tool that is part of construction accounting. It calculates the progress of all ongoing work, allowing an accountant to see what's been done and what's left to do - helping accountants manage budgets effectively

This template is designed to track the financial status of ongoing projects, including the accumulation of costs and revenues that have not yet been billed or recognized. It allows accountants to expand certain metrics or drill down to the underlying details to better analyze data.

Additionally, this template automates data entry by facilitating the creation of Work In progress journals directly to Sage Intacct - journals that are automatically reversed on the first day of the following reporting period.

Coverage

This workbook includes the following sheets:

• Work in Progress: the primary report that tracks the financial status of ongoing projects, including accumulated costs and revenues, and features writeback functionality to create journals in Sage Intacct.

• Control: the recon sheet for comparing contract balances with GL budget amounts.

• Options: the template’s settings sheet that is used for data validation lists 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 Finance professionals: Accountants, CFOs and Controllers.

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):

• Project functions

• List functions

• Query functions

• Journal Writeback

Preview

Download this template

💡Having trouble downloading the file?

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

Documentation

Navigating and utilizing the SI-CRE-RT6 WIP template is straightforward, designed for ease of use and efficiency. This template helps businesses monitor and manage work in progress by tracking project costs and billings, and it features writeback functionality to create journals in Sage Intacct at a specific data, corresponding to the reporting period of a construction business.

Filters

Located at 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.

• As of: a date field. If left blank, it will default to the current date.

• Project Category: a dropdown list displaying all available project categories within the Sage Intacct instance.

• Project Status: a dropdown list showing all potential statuses a project can have.

Project details

To further assist users with analysis, additional data and project attributes such as Project, Project name, Type, Category, Manager, Department, Status, Start Date, and Planned End Date are included in the report, offering a comprehensive overview of each project's key details and context.

Contract Value

This refers to the total agreed-upon amount for a project in Sage Intacct. The amounts are directly sourced from the Project Contract entity and categorized as follows:

• Original (A): based on the contract's initial agreed-upon price.

• Change Orders (B): reflects the total value of approved contract changes.

• Pending Changes (C): represents the value of changes that are awaiting approval.

• Revised (D): the updated contract value, including the original amount plus approved change orders.

Estimated Cost and Profit

Estimated cost represents the projected total cost of completing a project, including all anticipated expenses and resources required. The balances are extracted using the SI.PROJECTESTIMATEAMOUNT function.

Estimated profit is calculated by subtracting the Estimated Cost (E) from the Revised Contract Price (D). Additionally, Gross Profit Percentage (GP %) is provided to offer a clearer view of the project's profitability relative to its total revenue

Actual Cost

Refers to the real, incurred expenses associated with a project, including all paid costs and expenditures recorded during the project's execution. These figures represent job-to-date costs and are categorized based on the nature of the expense:

• Materials (I1): based on the "Cost of Sales - Materials" QuickStart category

• Labor (I2): based on the "Cost of Sales - Labor" QuickStart category

• Equipment (I3): based on the "Cost of Sales - Equipment" QuickStart category

• Subcontract (I4): based on the "Cost of Sales - Subcontract" QuickStart category

• Other (I5): based on the "Cost of Sales - Other" QuickStart category

• Overhead (I6): based on the "Cost of Sales - Overhead" QuickStart category

Month-to-Date (H) and Cost-to-Date (I) figures are included to compare the current month's costs (which may inform on progress) against the total costs incurred since the project's inception. Additionally, a Percentage Complete (% Complete) metric is provided, comparing the overall costs to the estimated costs, which helps measure the project's progress and performance relative to its budget and timeline.

Earnings and Billings

Earned To Date (K) is calculated by multiplying the % Complete (J) by the Revised Contract Price (D). This approach provides an estimate of the revenue earned to date based on the project's progress. The same percentage is also used to assess earned revenue against the total estimated revenue, offering insights into how much revenue has been recognized in relation to the project's completion.

Billed To Date (M) balances are directly sourced from the General Ledger using the SI.TURNOVER function, with the "Billings" as the default account group. The Percentage Billed (N) is calculated by comparing the Billed To Date (M) amount with the Revised Contract Price (D), providing a measure of how much of the contract value has been invoiced relative to the total.

(Over)/Under Billings (O) is calculated by first multiplying the % Complete (J) by the Revised Contract Price (D) to determine the expected billings based on project progress. This amount is then compared to the Billed To Date (M); the difference reveals whether there is overbilling or underbilling relative to the client.

Forecasted Cost

Refers to the projected total expenses expected to be incurred on a project by its completion. This estimate is extracted directly from Sage Intacct using the SI.PROJECTESTIMATEAMOUNTfunction.

To ensure accuracy, the latest Estimate ID must be manually entered in the Estimate ID (R) column. This is essential because the SI.PROJECTESTIMATEAMOUNT function relies on the Estimate ID to retrieve the correct data. If the Estimate ID is outdated or missing, the function may return incorrect or incomplete information.

And similar to other columns, the Forecast % Complete has been added to provide insight into the project's progress by comparing actual costs incurred to the total forecasted cost.

Create Journal Entry

The main feature of this report is the ability to create a journal entry directly in Sage Intacct using the SI.WRITEBACKJOURNALfunction. This allows users to conveniently upload data from the report with ease.

To complete this process, the following fields must be filled out:

• Posting Option: select the desired state of the journal entry (JE) upon upload.

  • Save as Draft: the JE will be uploaded in a Draft state.

  • Post: the JE will be uploaded in a Posted state.

  • Post and Reverse: the JE will be uploaded in a Posted state, and a reversal entry will be automatically created for the first day of the following month (based on the "As of" date).

• Department: the department to which the JE should be posted.

• Location override for closing account: specifies a custom location dimension for the closing account. If left blank, the entry is posted without a location dimension.

• Writeback by location: this option will only be available if the selected entity is Top Level.

  • Yes: journal entries will be posted using the location dimension associated with the project. 

  • No: journal entries will be posted without any location dimension specified.

• Journal Type: the specific journal where the JE should be recorded.

For this feature, the required GL codes for the writeback must be specified on the Options sheet:

• Cost in Excess of Billings (Debit): this is an Asset account that represent the costs that have been incurred but not yet billed to the customer. This account reflects the company's right to bill for work that has been completed but not yet invoiced.

• Billings in Excess of Cost (Credit): this is a Liability account that represent billings to customers that exceed the costs incurred to date. This account reflects an obligation to complete the work for which the company has already billed the customer but not yet incurred corresponding costs.

• Net Over or Under Billings (Closing): this is a Revenue account that represents the net amount of Over billings and Under billings at the end of a reporting period. This balance usually indicates the financial impact of billing discrepancies compared to actual costs incurred.

Control (Estimates vs GL Budget)

To enhance data validation, a control tab has been added to the report to reconcile estimates with GL budget data. This ensures that Project Contract balances have corresponding entries on the GL budget side. Similar to the main tab, each section is compared against the GL budget to verify accuracy and alignment.

Expanding the grouped columns will reveal two primary columns: Project Contract and Contract - GL Budget.

Project Contract

The balances are retrieved by Project ID directly from Sage Intacct's Project Contract records using the Velixo SI.QUERYLOOKUP.

• Original: the initial price of the contract.

• Change Orders: the price of approved contract changes.

• Revised: the updated contract price, including both the original amount and approved changes.

• Pending Changes: the price of contract changes that are still pending approval.

Contract - GL Budget

The Contract - GL Budget, on the other hand, is directly pulled from the budget figures recorded in the General Ledger (GL) using the Velixo function SI.BUDGETTURNOVER.

These balances are extracted from accounts mapped to the following QuickStart categories: Cost of Sales - Materials, Cost of Sales - Labor, Cost of Sales - Equipment, Cost of Sales - Subcontract, Cost of Sales - Other, and Cost of Sales - Overhead. Additionally, the balances are categorized based on the Budget ID:

• Original: extracted from the budget ID Contract - Original

• Change Orders: extracted from the budget ID Contract - Approved

• Revised: derived from both Contract - Original and Contract - Approved budget IDs

• Pending Changes: extracted from the budget ID Contract - Pending

The budget ID, along with the QuickStart categories or account groups, can be configured in the expanded rows starting at Row 7.

Contract Variance

Any differences between the Project Contract and Contract - GL Budget will be reflected in these columns and categorized in the same way as the two main columns.

Project Estimate Cost at Completion

This section compares the estimated balances with the balances recorded in the GL budget.

• Estimated: extracted directly from Sage using the SI.PROJECTESTIMATEAMOUNT function. This includes both the original contract and approved change orders.

• Budget ID: the ID used to pull the GL budget figures. By default, this is set to the Budget ID specified on the Work in Progress sheet.

• GL Budget: extracted directly from the GL budget using the SI.BUDGETTURNOVER function, based on the specified Budget ID.

• Variance: highlights any discrepancies between the estimated balances and the GL budget.

Project Estimate Forecast Cost at Completion

This section compares the Forecasted Estimates with the figures recorded in the GL Budget.

• Estimate ID: the most recent estimate ID for the project, used as the basis for the balances shown in the Forecasted column.

• Forecasted: the latest estimate balance for the project.

• Budget ID: the ID used to extract the GL budget figures for the project.

• GL Budget: the budget figure recorded for the project.

• Variance: highlights any discrepancies between the Forecasted estimate and the GL budget.

User Guide

Using the report

Now that we've covered the report overview, let's dive into a step-by-step guide on effectively using 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.

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

• Update the GL codes for the JE Writeback: specify the GL codes for the following accounts according to your Sage Intacct configuration. Note that the default accounts in the template are based on the Velixo demo instance and may not be applicable to your setup.

• Go to the main sheet and expand the grouped rows in Row 7. Velixo provides default values for these fields, but can be adjusted as needed based on the Sage Intacct configuration.

  • Account Group

  • Book/Budget ID

• After reviewing the default values in the grouped rows, select the desired filters for the report:

  • Entity

  • As Of

  • Project Category

  • Project Status

• After changing the filters, Velixo will automatically trigger a refresh to update the data. Once the refresh is complete, review and analyze the updated information.

• If necessary, initiate the writeback process. However, ensure that you first complete all required fields for the writeback journal.

  • Posting Option

  • Department

  • Journal type

• If the Posting option is set to "Post and Reverse", check the reversal date in cell AK2. By default, it is set to the first day of the month following the "As of" date, but can be adjusted according to user preference.

• The writeback process is successful when the status of the lines changes from 'Pending' to 'Line Uploaded.' Additionally, the created journal will automatically open once the writeback is complete.

Here is an example of a record after it has been uploaded to Sage Intacct:

Using the Control tab (Estimates vs GL Budget)

The Control tab is straightforward, as it primarily pulls data from the main sheet and will be automatically populated once the Velixo refresh is complete.

To fully utilize the tab, you need to provide three key inputs:

• Budget ID (Project Estimate Cost at Completion): this defaults to the Budget ID set on the main tab and will update automatically when the main sheet’s Budget ID is changed.

• Estimate ID: required for calculating the Estimate Forecast Cost. You must manually specify this ID.

• Budget ID (Project Estimate Forecast Cost at Completion): needed to calculate the Estimate Forecast Cost based on the budget.

Version History