Northwind 2.0 Developer Edition: Inventory

Definitions are very important. They can be done in various ways, none of which is correct above all others. But regardless of the approach, your definitions must be internally consistent. This Northwind template files use the following definitions.  (See also modInventory.) 

• A product is bought when Northwind receives the product on a PO.

• A product is sold when Northwind invoices an order for that product.

• Product Available is the number of units physically on the shelf. This includes units already allocated to order(s) but not yet shipped.

• Product to Sell is product that is physically on the shelf and not allocated to any existing orders - and thus available to sell.

• Product On Order means that it is on a PO with status of Approved.

The previous version of Northwind templates also had inventory tracking, albeit somewhat convoluted and poorly documented. Hence, herein we aim to do better. We followed an article from Allen Browne: the physical quantity on hand is the result of a simple calculation: 

[Last Stock Take Quantity] + [Sum of quantities Bought since that day] – [Sum of quantities Sold since that day] = [Physical quantity on hand] 

To see inventory in action here in the NW 2 Developer Edition, let’s start simple, with a new Product. Give it a name that you’d expect to see in a wholesale grocer’s catalog. 

We'll use these numbers: 
> Reorder Level of 50 
> Target Level of 40 
> Min Reorder Qty of 100
Initially, this product has no orders, no purchase orders, and one Stock-Take record with a quantity of 0. 

Create an order for this product only, with quantity of 10.
Observe the status of the line item will be No Stock. We don’t have any product on hand. The order cannot be invoiced until we have purchased some products from one of our vendors. 

To get some stock on hand, we create a purchase order for this product.
Observe the quantity of 100 will be set as a default.

Advance the PO to the Approved status.
The Product form now shows Qty No Stock of 10, and Qty on Order of 100, and the order line item on your sample order now shows a status of On Order. 

Advance the PO to the Received status.
The ReallocateInventory procedure runs and it goes back in time looking for order line items in status of No Stock or On Order, and if there is sufficient quantity remaining, it “fulfills” them by setting the status to Allocated. This will happen to our order, and we can now advance it to the finish line. Done!

In NW 2.0 we handle a few other scenarios, including these:

• When a previously allocated order line item is deleted, that quantity becomes available for allocation, and we run the same procedure as above.

• When an allocated order line item’s quantity is decreased, the delta becomes available for allocation as well.

• When the Product in an allocated order line item is changed, the old product’s quantity is available again to be allocated.

This may all seem pretty fancy and all-encompassing. Worry not. Inventory tracking can be complex, but this template set does not intend to provide a turn-key solution to run any business. It is a showcase of what is possible with Access, which may give you some ideas for your own implementation. 

• Partial invoices, and partial shipments.

• Products returned (either by customers, or to vendors).

• Preferential treatment in product allocation for our best customers.

• Warehouse location (“bin”) for each product, and support for scanning in and out with a barcode scanner. Print a barcode label to mark the bin.

• Product expiration – a very important concept in the food business.

• Purchase orders received and (partially) returned because some items are dented, expired, etc. RMA process.

Northwind 2.0 Developer Edition: Things you should know 

Northwind 2.0 Developer Edition: All topics