Explore Velrada:

GO

Skip to Content
INSIGHTS
Understanding Work in Progress in Dynamics 365 Business Central from a mathematical perspective

By Sandeep Gawankar, CPA Australia, CA India, MCP and D365 BC Senior Functional Consultant

Microsoft Dynamics 365 Business Central Project Management module is a comprehensive project management solution designed to help project-centric businesses to manage their projects from start to finish within one tool and connect other involved departments such as sales and finance. It allows the businesses to manage, allocate and control the resources at disposal and complete projects on time and within budget, driving project success, and profitability and includes work in progress (WIP) calculations for correcting financial statements. Project Management can be utilized without the WIP processes but is more efficient and operationally rewarding to calculate and post WIP within the system.

From a financial point of view, the concept of WIP means calculating (and posting to General Ledger (G/L)) costs and revenues that need to be recognized at a certain point in time, and then aligning the costs and revenues that are already posted in the system to the result of the calculated recognized costs and revenues.

Business Central supports the following methods of calculating and recording the value of work in process:

  • Cost Value – Recognizes WIP when the customer is invoiced.
  • Cost of Sales – Recognizes WIP when the customer is invoiced.
  • Sales Value – Recognizes costs as they are reported.
  • Percentage of Completion – Recognizes costs as they are reported.
  • Completed Contract – Completed contract does not recognize revenue and costs until the job is complete.

Each method calculates WIP based on budgeted usage and sales and actual usage and sales. These amounts are displayed on the job task lines. However, the calculations are complex and are performed by the application in the background providing no visibility to the User. This article attempts to decipher the mathematical calculations using a traditional formula-based approach providing step by step clarity and eventually validating the system calculations.

Cost Value Method

Cost Value method calculates WIP amount as the sales value of posted usage cost – estimated sales value of the invoiced usage.

Example of a Job with one Task

WIP amount = usage (total cost) * billable (total price) ÷ budget (total price) – budget (total cost) ÷ budget (total price) * billable (invoiced price)

WIP amount = C*F/B-A/B*H

WIP amount (I) = -$50

WIP Cost amount =if WIP amount is positive then Usage Total Cost

if WIP amount is negative then Usage Total Cost - WIP amount

C-WIP amount

$300.00
Recognised CostsC-I$300.00
Accrued Cost amountI -$50.00
WIP Sales amountH $600.00
Recognised Sales amountH $600.00
Job Cost AdjustmentDr$50.00
WIP Accrued Costs Cr-$50.00
Job Sales AppliedDr$600.00
WIP Invoiced SalesCR-$600.00
WIP Invoiced SalesDr$600.00
Recognised Sales-$600.00
Jobs Cost AppliedCR-$300.00
WIP Costs AccountDr$300.00
WIP Costs AccountCr-$300.00
Recognised CostsDr$300.00

Job Card reflecting Job Task, WIP-Method

Unposted WIP Calculations

JOB WIP Entries

Cost of Sales Method

Cost of Sales method calculates the WIP amount as the posted usage cost – estimated usage cost based on percentage billed. Percentage billed is based on actual sales / contract value.

Example of a Job with one Task

WIP amount = usage (total cost) – billable (invoiced price) ÷ billable (total price) * budget (total cost)

WIP amount = C-H/F*A

WIP amount  (I) = -$50

WIP Cost amount = if WIP amount is positive then Usage Total Cost
if WIP amount is negative then Usage Total Cost - WIP amount
C-WIP amount
$ 300.00
Recognised CostsH/F*A$300.00
Accrued Cost amountI -$50.00
WIP Sales amountH $600.00
Recognised Sales amountH $600.00
Job Cost AdjustmentDr$50.00
WIP Accrued Costs Cr-$50.00
Job Sales AppliedDr$600.00
WIP Invoiced SalesCR-$600.00
WIP Invoiced SalesDr$600.00
Recognised SalesCr-$600.00
Jobs Cost AppliedCR-$300.00
WIP Costs AccountDr$300.00
WIP Costs AccountCr-$300.00
Recognised CostsDr$300.00

Job Card reflecting Job Task, WIP-Method

Unposted WIP Calculations

JOB WIP Entries

Impact of JOB WIP Entries on the Balance Sheet and Income Statement

Sales Value

Revenue is booked based on billing dates, while costs of goods sold are matched proportionately to the billed revenue, allowing for smoothing of profitability over time.

Example of a Job with one Task

WIP amount = usage (total price) * billable (total price) ÷ budget (total Price) – billable (invoiced)

WIP amount = D*F/B-H

WIP amount  (I) = -$100

WIP Sales amount if WIP amount is positive then Billable Invoiced Price
if WIP amount is negative then Billable Invoiced Price + WIP amount
H+I
$ 500.00
Recognised CostsC$250.00
Accrued Sales amountI $0.00 (If, I is positive)
WIP Cost amountC$250.00
Recognised Sales amountH+I$500.00
Job Cost AdjustmentDr$0.00
WIP Accrued Costs Cr$0.00
Job Sales AppliedDr$600.00
WIP Invoiced SalesCR-$600.00
WIP Invoiced SalesDr$500.00
Recognised SalesCr-$500.00
Jobs Cost AppliedCR-$250.00
WIP Costs AccountDr$250.00
WIP Costs AccountCr-$250.00
Recognised CostsDr$250.00

Job Card reflecting Job Task, WIP-Method, WIP Posting Method and Unposted WIP Calculations

Unposted WIP Calculations

JOB WIP Entries

Impact of JOB WIP Entries on the Balance Sheet and Income Statement

Percentage of Completion Method

Revenue is recognized proportionately based on the percentage of completion, comparing expected costs to actual costs, ensuring consistent profit margins each month and accurate reflection of the work completed.

For this method, the WIP amount is always posted to the WIP accrued sales account (an asset account), while the value of the billable price (invoiced price) will be posted to the WIP invoiced sales (contra asset or liability account).

Example of a Job with one Task

WIP amount = usage (total cost) ÷ budget (total cost) * billable (total price) – invoiced (total price)

WIP amount = C/A*F-H

WIP amount  (I) = -$100

WIP Sales amount WIP Amount
I
$ -100.00
Recognised CostsC$250.00
Accrued Sales amountI $0.00 (If, I is positive)
WIP Cost amountC$250.00
Recognised Sales amountH+I$500.00
Job Cost AdjustmentDr$0.00
WIP Accrued Costs Cr$0.00
Job Sales AppliedDr$600.00
WIP Invoiced SalesCR-$600.00
WIP Accrued SalesDr$500.00
Recognised SalesCr-$500.00
Jobs Cost AppliedCR-$250.00
WIP Costs AccountDr$250.00
WIP Costs AccountCr-$250.00
Recognised CostsDr$250.00

Job Card reflecting Job Task, WIP-Method, WIP Posting Method and Unposted WIP Calculations

Unposted WIP Calculations

JOB WIP Entries

Impact of JOB WIP Entries on the Balance Sheet and Income Statement

Completed Contract

With the Completed Contract method, revenue and cost are not recognized until the job is complete.

All usage is posted to the WIP costs account (asset) and all invoiced sales are posted to the WIP invoiced sales account (liability) until the job is complete.

Example of a Job with one Task

WIP amount = WIP Cost amount = usage (total cost) until the job is complete. When the job is complete, the WIP Cost amount is zero and the Recognized Cost amount becomes the usage (total cost).

WIP amount = C

WIP amount (I) = $250

WIP Sales amount (J)contract (invoiced price) until the job is complete. When the job is complete, the invoiced sales amount is zero and the Recognized Sales amount becomes the contract (invoiced price).
H
$ 600.00
Recognised CostsC-I$0.00
Recognised Sales amountH-J$0.00
Job Cost AdjustmentDr$0.00
WIP Accrued Costs Cr$0.00
Job Sales AppliedDr$600.00
WIP Invoiced SalesCR-$600.00
Jobs Cost AppliedCR-$250.00
WIP Costs AccountDr$250.00

Job Card reflecting Job Task, WIP-Method, WIP Posting Method and Unposted WIP Calculations

Unposted WIP Calculations

JOB WIP Entries

Impact of JOB WIP Entries on the Balance Sheet and Income Statement

D365 BC also provides the ability to exclude certain job tasks from the WIP calculations, a function which could be relevant for retentions in the construction industry. If required, retentions could be presented as a Job Task and excluded from WIP by using the Excluded option on the WIP-Total column.

Job Tasks reflecting WIP Excluded Tasks.

Depending on the dynamics between % Invoiced and % Completed, the choice of the WIP method will result in either accelerating cost or deferring cost or accruing or deferring revenue as shown in the below image. Refer to the situations where WIP cost or WIP revenue is added or subtracted from the actual cost of revenue.

As has been observed in the calculations made in this document, the Cost Value and Cost of Sales or the Sales Value and % of Completion produce the same outcome even though the formulas for the calculation are different. The suitability of a particular WIP method could be based on industry requirements, for example, Percentage of Completion method is relevant in the construction sector or be based on the business transactions, such as Completed Contract could be useful for in-house capital projects.

Interested in taking the next step with Dynamics 365? Reach out and we’ll be in touch.
Back to top