Marcum LLP successfully created a computer-readable WIP report to help contractor clients provide financial performance data to surety carriers that can be automatically extracted with no rekeying of data. Marcum created the report by performing a one-time mapping using a spreadsheet add-in that converted the WIP data into XBRL format. XBRL is an open data standard that makes data computer-readable. The mapping took approximately five minutes. For subsequent periods, generating the computer-readable WIP will take no more time or work than preparing a traditional spreadsheet-based WIP report.
The benefit of a computer-readable WIP is that it can be automatically consumed by surety carriers, eliminating manual data entry. Entering WIP data by hand can take from 20 minutes to several hours for each report; and with potentially hundreds of WIP reports entered during a single reporting period, this can delay sureties’ ability to respond on a timely basis to contractors’ need for credit.
Work-in-Process Data
The WIP report is a periodic accounting of costs, revenues and profits about the individual jobs or projects of a contractor. It provides a snapshot of the financial health of the contractor at a point in time and may be provided to a surety carrier, a bank, or in some cases to the Small Business Administration (SBA) if the contractor is enrolled in the SBA’s surety bond guarantee program. WIP data is typically provided once a year or once a quarter. The WIP is developed either by the contractor, through their own internal systems in Excel or PDF, or by the contractor’s accounting firm. The WIP may be first sent to a bonding agent for review and input, before it is delivered to the surety and potentially other stakeholders.
Sample WIP Spreadsheet
Role of the Accounting Firm
Accountants, like Marcum LLP, receive WIP data from their contractor clients in a variety of formats. Sometimes it is generated through reporting software that the contractor uses to manage its financials and is delivered to the accounting firm in PDF; sometimes it is provided in Excel format; and sometimes the WIP is simply delivered as a paper document.
The accounting firm creates the WIP report in Excel and typically sends it back to the contractor for final approval.
Problems with the Current Process
The contractor then forwards the WIP to their key audiences like the surety carrier, sometimes in Excel, or more typically, in PDF. The surety then manually rekeys the data line by line into their internal financial system. This process is time-consuming, labor-intensive and can be error-prone. Data then used for analysis by the surety may be outdated and because the data must be interpreted by data entry staff, as each contractor may use different terminology in the WIP, it can be prone to errors.
Automating WIP Data Extraction
Marcum LLP demonstrated how they could create a computer-readable WIP report by using an Excel add-in tool from Altova, a software company that specializes in tools for developers. This add-in is one method of creating the automated WIP but ultimately it is expected that contractors will have a range of tool options, including using existing contractor software packages with embedded XBRL modules. The availability of these different options ensures that the contractor will be able to seamlessly integrate the standard WIP into their existing process.
Marcum created a sample open and closed WIP schedule. Working with the Excel add-in, Marcum was able to complete the automated WIP the first time in less than five minutes. The WIP contained nine contracts but the task could have been performed within the same five minute timeframe regardless of the number of contracts on the WIP.
The add-in opens a template in the contractor’s original WIP spreadsheet as shown below with areas where data from the contractor’s spreadsheet is extracted shown in blue. Sections in black are automatically calculated in Excel. The template is designed to accommodate as much information as the contractor wishes to provide. All data fields are not required so the contractor follows their traditional practice in terms of what data they choose to report.
On the right side of the worksheet add-in, the WIP Report Pane allows the accounting firm or contractor to identify and map to job numbers, contract name, start date, and end date data that reside in their original spreadsheet.
Spreadsheet Add-In
Marcum then highlights other data fields needed such as estimated revenue and cost, costs to date, and billed to date.
The diagram below shows how the mapping is performed by selecting the appropriate range in the original WIP spreadsheet to map to the fields in the WIP report pane. Certain fields, such as revenue earned to date, are calculated automatically.
Original WIP Spreadsheet with Rows Highlighted to Map to the Template
The add-in template extracts data from the contractor’s original spreadsheet. Marcum then validates that the XBRL version of the WIP has been created successfully with no errors by using a validation check on the add-in tool. The Export button allows Marcum to create, save and export an XBRL version of the WIP report.
WIP Add-In – Validating Data and Exporting XBRL Version
Marcum went through the same process for the completed schedule. Once the mapping is complete and the data validated, the XBRL version of the WIP report is generated with a single keystroke, and sent on to the surety carrier or other stakeholders who need the data.
The surety can then automatically extract all the data from the XBRL document into their financial systems in seconds. The carrier can then perform their analysis and respond to the contractor on a significantly more timely basis.
For subsequent WIP reporting periods, the contractor or their accounting firm simply updates their original Excel spreadsheet with any new data which is then automatically mapped by the add-in. The automated XBRL version of the WIP can then be generated with no additional work involved. The mapping is a one-time process that does not need to be repeated.
If the contractor has a new project that must be added, they simply add a new row into the spreadsheet and copy over the formula.
The Altova tool that Marcum used also has a feature which allows it to automatically map from a WIP report that is created using the standard NASBP template for Work-in-Process reports. The NASBP developed this template as part of their Producer’s Toolkit for frequently used forms (http://www.nasbp.org/informed/forms/toolkit).
This feature in the Altova add-in means that any contractor that uses the NASBP WIP template and that has the Altova tool can create an XBRL-version of its WIP report in a single keystroke, with no mapping process required. Contractors that use their own custom WIP reports will need to perform the one-time mapping but those using the NASBP template will be able to bypass that processing step.
Gains for the Contractor and for the Surety
Automation solves the challenge of how to consistently and accurately convey financial data from contractors to their stakeholders. Using standards will reap benefits to sureties and to contractors for years to come.
Sureties gain from a more efficient, accurate process. Contractors gain from getting faster access to credit and the comfort of knowing that the financials used to evaluate them are current and accurate.
Learn more about the program
Learn more about the Altova add-in
Contact us: info@xbrl.us
