Skip to main content
Skip table of contents

SI-CRE-RT8 Monthly Billings to Cost

Applies to

  • Product version: Velixo NX
  • ERP: Sage Intacct
  • Functional area: Financials, Budgeting, Project, Other
  • Industry: Construction
  • Plan: All
  • Template type: Production Report template



Description

The SI-CRE-RT8 Monthly Billings to Cost template tracks and compares inbound billings with various outbound costs incurred for construction projects, allowing you to get a full picture of cash inflows and outflows from several areas of Sage Intacct.


It includes timesheet and expense entries for staff, Direct Cost Journals, AR Invoices, and AP Bills helping project managers and financial teams ensure they have a single report detailing all transactions in order to gain better visibility on the cash flows of a project.


Coverage

This workbook includes the following sheets

  • All Transactions: a consolidated list of transactions from the AR Invoices, AP Bills, Expenses, PDCJ Journals, and Timesheet Entries tabs.
  • Analysis: a summary of all transactions, presented in a pivot table format.
  • AR Invoices: a list of all open AR transactions for the selected Project ID.
  • AP Bills: a list of all open AP bills for the selected Project ID.
  • Expenses: a detailed list of staff expenses by employee for the selected Project ID.
  • PDCJ Journals: a list of all posted expense journals for the selected Project ID.
  • Timesheet Entries: a list of all timesheet entries by employee for the selected Project ID.
  • Rate Tables: the source table for labor rates used in the timesheet entries.
  • 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, Controllers and Project Managers.


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

  • 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

The SI-CRE-RT8 Monthly Billings to Cost template is designed to be easy to use. It helps you track monthly billings and costs (including timesheet entries for staff) so you can keep a close eye on your project's financials. By comparing revenues and expenses, this template gives a clear picture of how things are going and helps manage cash flows more effectively.


Filters

Located at the upper left of the of the All Transactions sheet, the filters include:

  • Project: a dropdown list of all available projects in the Sage Intacct instance. If no project code is selected, the template will display all available project codes within the Sage instance.
  • From (date): this field defines a Start date, based on the posting date, from which the data will be displayed. If left blank, it will capture all transactions regardless of their posting date.
  • To (date): this field defines an End date, based on posting date, up to which the data will be displayed. If left blank, it defaults to the current date.



All Transactions

The main report consolidates all data from the other sheets into a table that can be easily filtered by column. It includes detailed information to help users with their analysis:

  • Project ID: the project ID linked to the transaction.
  • WBS #: the task number within the project.
  • Cost Code: the cost code assigned to the transaction.
  • Cost Code Name: the name corresponding to the cost code.
  • Vendor ID: the vendor associated with the AP bills.
  • Vendor Name: the name corresponding to the vendor ID.
  • Bill/Inv #: the reference number for AP bills or AR invoices.
  • Date: the transaction posting date.
  • Account Code: the associated GL code.
  • Description: the description of the transaction.
  • Item: the item linked to the transaction.
  • Amount: the total value of the transaction.
  • Billed Amount: the amount billed to the customer (for AR invoices).
  • Cost Amount: the total cost incurred for the expense.
  • Document Type: the type of transaction.
  • Hours: the labor hours recorded on the timesheet entry.
  • Labor Rate: the corresponding rate for the labor hours.
  • (Over)/Under: the difference between the billed amount and the cost amount.



Analysis

This worksheet presents a summary of data on the All Transactions tab, presented as a pivot table to make it easier to display information and optionally customize it. It includes the most commonly used details as headers by default, but it can be easily customized to fit the user's preferences.



AR Invoices

This tab pulls all open AR invoices for the selected Project ID within the specified date range. It provides all the necessary AR details for analysis and includes additional columns that help generate the main table in the All Transactions tab.



AP Bills

Similar to how the AR Invoices tab works, this tab pulls all approved/open AP Bills for the selected Project ID within the specified date range and for GL Accounts starting with "5" (configurable). It provides essential AP details for analysis and includes additional columns that contribute to the main table in the All Transactions tab.



Expenses

This tab lists all approved/open staff expenses for the selected Project ID within the specified date range. It includes all posted staff expenses information.



PDCJ (Payroll Direct Cost Journal)

This tab displays all posted payroll direct cost journals (PDCJ) for the selected Project ID within the specified date range. It provides detailed information on payroll-related costs for analysis.



Timesheet Entries

This tab includes all timesheet entries for the selected Project ID within the specified date range. It provides detailed records of labor hours for analysis.



Rates Table

This tab extracts rates based on the Rate Table ID specified on the Options sheet. It serves as the source for calculating labor rates on the Timesheet Entries tab.



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.



  • Specify the Default Labor Rate Table ID: this ID is required to generate the Rates Table data, which is then used to calculate labor rates for the Timesheet Entries.



  • Identify the columns for Labor Rate matching: this section lists the dimensions used to match rates between the Rates Table and Timesheet Entries. These dimensions represent the most common details found in the Rates Table, making it easier for users to work with. Select the dimensions currently in use on the selected Rates Table to ensure accurate matching with the Timesheet Entries.



  • Go to the PDCJ Journals tab and expand the grouped rows at Row 5 to reveal the GL Account # Filter (cell C4). By default, this is set to "5????" to filter for expense accounts. Specify the account code series used for expenses in your Sage instance.



  • (Optional) Go to the Expenses tab and expand the grouped rows at Row 7 to reveal the Expense Type Filter (Cell C5). This is blank by default. Specify the expense type needed, if applicable.



  • Navigate to the All Transactionssheet and choose the filters to apply.
    • Project
    • From (date)
    • To (date)



  • Adjusting the filter will automatically update all tabs in the report, including the transaction tables under AR Invoices, AP Bills, Expenses, PDCJ Journals, and Timesheet Entries.



  • After the automatic refresh is complete, go to the Analysis tab and refresh the pivot table.



Version History


VersionReleased onChanges
111 Sep 2024N/A. Initial version of the template.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.