Skip to main content
Dynamics 365

Negative inventory in inventory accounting

Allowing physical negative inventory may have undesirable consequences in inventory accounting, especially if the inventory costing principle is Actual and the valuation method is either FIFO or Weighted average.

Most of the issues that are related to physical negative inventory can be mitigated by using the correct configuration and maintenance of data.

Example: Why is the cost out of sync?

The following table lists the required setup for the Item model groups.

Item Inventory model Physical negative inventory Latest cost price Active planned cost
A FIFO Yes Yes No

 

The purchase from the supplier always takes place at a unit cost of 7,500.00.

The following table lists the events as they occur, in chronological order.

Financial date Reference Receipt Issue Quantity Cost amount
10/6/2017 Sales order 01 Sold

-3.00

10/6/2017 Purchase order 01 Purchased

2.00

15,000.00

10/6/2017 Sales order 02 Sold

-3.00

-22,500.00

10/6/2017 Purchase order 02 Purchased

1.00

7,500.00

10/6/2017 Sales order 03 Sold

-3.00

-22,500.00

10/6/2017 Purchase order 03 Purchased

3.00

22,500.00

10/6/2017 Purchase order 04 Purchased

2.00

15,000.00

10/6/2017 Purchase order 05 Purchased

3.00

22,500.00

10/6/2017 Sales order 04 Sold

-1.00

-18,750.00

 

The system starts issuing from inventory at cost per unit of 18,750.00 even though the cost of purchase has not exceeded 7,500.00.

Why does this happen?

In order to explain this in better detail, lets add a few more columns on the rightmost side, in green. These new columns represent the inventory balance after posting the specific transaction. The inventory balance is also known as InventSum.

Financial date Reference Receipt Issue Quantity Cost amount Quantity Value Avg. unit cost
10/6/2017 Sales order 01 Sold

-3.00

1)

-3.00

$0.00

$0.00

10/6/2017 Purchase order 01 Purchased

2.00

15,000.00

-1.00

$15,000.00

-$15,000.00

10/6/2017 Sales order 02 Sold

-3.00

-22,500.00 2)

-4.00

-$7,500.00

$1,875.00

10/6/2017 Purchase order 02 Purchased

1.00

7,500.00

-3.00

$0.00

$0.00

10/6/2017 Sales order 03 Sold

-3.00

-22,500.00 3)

-6.00

-$22,500.00

$3,750.00

10/6/2017 Purchase order 03 Purchased

3.00

22,500.00

-3.00

$0.00

$0.00

10/6/2017 Purchase order 04 Purchased

2.00

15,000.00

-1.00

$15,000.00

-$15,000.00

10/6/2017 Purchase order 05 Purchased

3.00

22,500.00

2.00

$37,500.00

$18,750.00

10/6/2017 Sales order 04 Sold

-1.00

-18,750.00 4)

1.00

$18,750.00

$18,750.00

 

Notes:

  1. The Cost per unit is 0.00. When the balance is negative, the system looks for a fallback cost to apply.
    1. First, the system looks for an active cost. This fails.
    2. Second, the system looks for cost set up in the Cost price field in the item master record. The cost is equal to 00.
  2. The Cost per unit is 7,500.00. The balance is still negative. The system looks for a fallback cost to apply.
    1. The system looks for an active cost. This succeeds.
    2. The latest cost price was set to Yes on the item record. The prior Purchase order unit cost of 7,500.00 has now become the active cost.
  3. The same condition applies as in number 2.
  4. The Cost per unit is 18,750.00. If the balance is positive at the time of posting the issue transaction, the system applies the average cost of the balance.
    1.  Average cost is calculated as: 37,500.00 / 2.00 = 18,750.00

The issue is that the inventory balance of 1 piece at 18,750.00 is overvalued because the item has never been purchased at a cost higher than 7,500.00. The reason for this overvaluation is that the first issue transaction leaves inventory at a cost per unit of 0.00. The wrong cost estimation will continue to ripple through the following transactions.

The only way to get the inventory balance back in sync is to run either the Recalculation or Inventory close jobs.

Financial date Reference Receipt Issue Quantity Cost amount Qty Value Avg. Unit cost
10/6/2017 Sales order 01 Sold

-3.00

-3.00

$0.00

$0.00

10/6/2017 Purchase order 01 Purchased

2.00

15,000.00

-1.00

$15,000.00

-$15,000.00

10/6/2017 Sales order 02 Sold

-3.00

-22,500.00

-4.00

-$7,500.00

$1,875.00

10/6/2017 Purchase order 02 Purchased

1.00

7,500.00

-3.00

$0.00

$0.00

10/6/2017 Sales order 03 Sold

-3.00

-22,500.00

-6.00

-$22,500.00

$3,750.00

10/6/2017 Purchase order 03 Purchased

3.00

22,500.00

-3.00

$0.00

$0.00

10/6/2017 Purchase order 04 Purchased

2.00

15,000.00

-1.00

$15,000.00

-$15,000.00

10/6/2017 Purchase order 05 Purchased

3.00

22,500.00

2.00

$37,500.00

$18,750.00

10/6/2017 Sales order 04 Sold

-1.00

-18,750.00

1.00

$18,750.00

$18,750.00

30/6/2017 Sales order 01

-22,500.00

30/6/2017 Sales order 04

11,250.00

1.00

7,500.00

 

The Inventory close will apply the selected Inventory model, in which case is FIFO, and then adjust the cost on the issue transactions accordingly.

Note: If the inventory balance is negative when executing the inventory close, the balance will not be adjusted. A full adjustment can only occur when the balance is positive, and enough receipts exist that can adjust the issues.

Conclusion

By default, all items should have an active cost. If you plan to allow temporary physical negative inventory, which is a valid scenario in certain business, its essential to apply an active cost before creating any transactions on the item.