WIP Dexterity Reports

At month end, you may want to make sure that the WIP accounts in Service Management balance with the WIP accounts in the general ledger (GL). You can run reports that show you which service transactions have and have not been posted through to the GL - and which transactions have been posted in the GL, but not to Service Management.

WIP accounts in Signature Service Management and WIP accounts in the general ledger (GL) get "out of balance" mostly because of service invoices being posted with unposted costs remaining.

The costs that are committed on a purchase order do not become actual costs until they are received through a receivings transaction entry. You can bill a customer before receiving actual costs, and when the service invoice is posted, WIP accounts are credited and COGS accounts debited for ALL service call costs, including unreceived PO lines. In this case, WIP accounts are relieved based on the committed cost remaining for the unreceived PO lines.

When committed costs remain, those amounts are not tracked in the GL. By default, you will not be able to post a service invoice that contains committed costs. This will be controlled with options on the Invoice Options window, as well as other checkboxes that deal with posted costs.

About Trailing Costs

Trailing costs are the costs that remain unposted after a service invoice has been posted, for a closed service call. Trailing costs could be partial PO shipments that were not received, a purchase price variance (PPV) that appeared on a receipt, or other committed costs that were not posted before a service invoice was posted.

Using WIP Reports at Month End

The following reports are included to help you reconcile WIP and Progress Billings accounts as part of the month-end closing process:

WIP report: Printed by year and period and shows amounts for each service call summarized by division.
Reconcile report: Prints debit/credit amounts by WIP and COGS along with the extended cost to reconcile the amounts posted to ensure they balance.
Exceptions reports: Shows which costs have been posted in the GL but have NOT been posted in Service Management, and vice versa.

For example, for costs that are in Service but not the GL, you may have posted to the GL, but did not post the GL batch yet. Conversely, for costs in the GL but not in Service, you may have posted costs from any other module to those accounts, for example, an adjustment entry to the account not assigned to a service call.

Sample Report Sequence

The following steps demonstrate how these reports can be used to help reconcile WIP and Progress Billings accounts at month end.

Step 1: Run the Reconciliation Summary Report

Run the summary version of the Reconciliation report to see which amounts balance; you can filter the report for the month you are reconciling

If you open the Summary Inquiry window to view GL activity (Inquiry > Financial > Summary), the Net Change for a period should match the same account's Extended Amount for the same date range on the Reconciliation report.

Extended Amount = WIP Debit - WIP Credit + COGS Debit - COGS Credit

Step 2: Run the Reconciliation Detail Report

If summary report totals do not balance, you can view transaction-level detail for the period. This may help identify discrepancies.

Step 3: Run the Trailing Cost and Trailing PPV Cost Reports

If you cannot identify the issue, it may be due to a trailing cost or purchase price variance (PPV). This report checks for trailing costs and PPV costs in any account that is set up as a Cost Account or Progress Billing account in Invoice or Maintenance Account Setup. Cost of sales and sales accounts are not checked.

Trailing costs may exist due to a trailing invoice, for example, a vendor invoice that is posted after a service call is closed. The transaction will still appear in the GL WIP accounts but will not appear in Service.

A PPV occurs when the amount that is relieved from WIP when posting a service invoice is different than the amount that was debited to WIP. This may occur if there is a trailing invoice; when there is no actual cost at the time of invoice posting, the Service WIP accounts are relieved based on committed cost remaining, and the amounts may not agree. The impact of a PPV does not appear on the service call but may be identified with this report.

Step 4: Run the Exception Reports

If you still cannot pinpoint the issue, these reports identify transactions that were posted to your Service WIP accounts but not to the GL, and vice versa. All accounts that have been set up as WIP or Cost service invoice or maintenance accounts, as well as any account that has been posted to for a service call, will be checked.

The report identifies journal entries along with the type of transaction and the user who posted it. For example, the issue may be a payable that debited WIP without the service call filled in, or a journal entry that was made directly to the GL instead of through Service. The report helps you identify the transaction and assess user training needs.

Step 5: Make Any Adjustments to GL or Service Accounts

You can adjust cost amounts in the Signature Transaction entry or Service Invoice windows. If you have trailing costs, you may select to create an administrative service call to transfer the cost into the subledger via a clearing account.

About Updated Records (Additional Aid to Reconciliation)

To help you to tie back/reconcile amounts in the general ledger with amounts in Service Management, several additional records/tables will be updated because of performing certain actions, such as posting a purchase order invoice (from the Purchasing Invoice Entry window) or posting a service invoice. These are listed below.

After posting a Purchase Order invoice that contains committed purchase order costs (costs that have been included on a posted service invoice as part of committed costs remaining), a new record is created in the SV_Invoice_Trailing_Costs table to show the trailing cost amounts. This allows you to tie these amounts back to the service call.
After posting a Purchase Order invoice with PPV (purchase price variance) on a closed service call, a new record is created in the SV_Invoice_Trailing_PPV_Costs table to show the amount of the variance (positive or negative) in a field called Trailing Cost.
After posting a service invoice and RM transaction the account indexes for the Invoice Account and Invoice Account Credit accounts will be stored in the SV000815 table.
After posting a plus button
transaction to a service invoice the account index for the WIP Credit Account will be stored in the SV000810 table.
After transactions are posted that apply to a service call the following fields are updated accordingly in the SV000810 or SV000815 tables: Divisions, Journal Entry, Referenced TRX Number, Transaction Source, Account Index Credit, Account Index Debit, and GL Posting Date.
After Signature Payroll Posting, the account indexes for the OH (overhead) Account Index CR (credit) and OH (overhead) Account Index DR (debit) will be stored in the SV000810 table.
For maintenance contracts, after the revenue recognition process is run the account indexes used for progress billings and contract earnings will be stored, along with the GL Journal Number and the line sequence numbers created.
For maintenance contracts, after creating invoices for billing schedules we will store the RM Document Number to match in the SV_Contract_Billing_MSTR table or SV_Master_Contract_Billing_MSTR (for master contracts). In the SV_Maint_Invoice_MSTR table, the account index will be stored containing the progress billing amounts.

Posting Setup

Before you run the WIP reconciliation reports, your posting options must be set up to create journal entries for transactions. You must set this up for the following product series': Inventory (with an origin of Transaction Entry) and Sales (with an origin of Sales Transaction Entry). These may have already been set up, but we recommend that you double-check the setup.

Select Microsoft Dynamics GP > Tools > Setup > Posting > Posting. The Posting Setup window opens.
In the Series drop-down, select Sales.
In the Origin drop-down, select Sales Transaction Entry.
Under Create a Journal Entry, select the Transaction radio button.
In the Series drop-down, select Inventory.
In the Origin drop-down, select Transaction Entry.
Select OK.

Running the WIP Reports

Select Reports > Service Management > Service > WIP Reports. The Service Management WIP Reports window opens.
Enter a Start Date and an End Date.
Select a GL Account Number. To run for all accounts, leave this field blank.
Select to run by all or individual Division, Customer, Location and/or Contract.
Select to run a WIP report (summary or detail), Reconcile report (summary or detail), or Exception report. For the Exception report, you can select to run a report for Service Costs not in GL or GL Costs not in Service.
Select Print.

Dexterity report examples appear below.

If you are using SSRS reports, these Dexterity reports are replaced with the SSRS versions; refer to WIP SSRS Reports for examples of the SSRS WIP reports.

WIP Report - Summary

WIP Report - Detail

WIP Reconcile Report - Summary

WIP Reconcile Report - Detail

WIP Exception Report - Service Management Costs Not in GL

WIP Exception Report - GL Costs Not in Service Management

Service Invoice Trailing Costs Report - Summary

Service Invoice Trailing Costs Report - Detail