Inventory items - Quantity columns

1

The latest version (24.6.16.1660) is implementing inventory quantity columns in the following way.

Not every business will see changes to their balances in quantity columns. If you see changes, this topic explains how the columns are being calculated and what to do if the column balances don’t reflect reality.

But first, how the columns are calculated:

Qty owned

  • All general ledger transactions are included. No change here. It has been always this way.

for example:

  • Purchase invoices increase this
  • Sales invoices decrease this
  • Delivery notes and Goods receipts have no effect here because they are not general ledger transactions.

If you are using Delivery Notes and Goods Receipts, you can use 3 more columns to track physical quantities:

Qty to deliver

  • Sales invoices increase this (unless invoice marked as Acts also as a delivery note)
  • Delivery Notes decrease this
  • Credit notes decrease this (unless credit note marked as Acts also as a delivery note)

Qty to receive

  • Purchase invoices increase this (unless purchase invoice marked as Acts also as a goods receipt)
  • Goods receipts decrease this
  • Debit notes decrease this (unless debit note marked as Acts also as a goods receipt)

Qty on hand

  • Goods receipts increase this
  • Delivery notes decrease this
  • Sales invoices are excluded (unless invoice marked as Acts also as a delivery note)
  • Purchase invoices are excluded (unless purchase invoice marked as Acts also as a goods receipt)
  • Credit notes are excluded (unless credit note marked as Acts also as a delivery note)
  • Debit notes are excluded (unless debit note marked as Acts also as a goods receipt)
  • All other general ledger transactions are included

In other words, invoices, debit notes and credit notes have effect on Qty owned but no effect on Qty on hand. Delivery notes and goods receipts have effect on Qty on hand but no effect on Qty owned.

If you are using Sales Orders, then there are another 2 columns available:

Qty reserved

  • Sales orders increase this
  • Delivery notes linked to sales orders decrease this

Qty available

  • This is Qty on hand minus Qty to deliver minus Qty reserved.
  • This is used to see how much more inventory is physically available on hand that can be immediately sold and delivered.

If you are using Purchase Orders, then 2 more columns can be used:

Qty on order

  • Purchase orders increase this
  • Goods receipts linked to purchase orders decrease this

Qty to be available

  • This is Qty available plus Qty on order.

If you are using minimal stock levels feature where you can set desired quantity for each inventory item, then 2 more columns can be used.

Qty desired

  • This is desired quantity set on inventory item

Qty to order

  • This is Qty desired minus Qty to be available. In indicates how much inventory should be ordered.

Also acts as delivery note / goods receipt

Sales invoices, purchase invoices, credit notes and debit notes can be marked to also act as delivery notes / goods receipts. This means you can still have these documents skip Qty to receive and Qty to deliver if you prefer.

Creating Delivery Notes from “Qty to Deliver” figure

If you have a balance to clear in Qty to deliver or Qty to receive column, you can create delivery note or goods receipt to clear the balance.

To automate this process, go to Customers tab and enable Qty to deliver column. This will show you Qty to deliver figure for each customer.

image

Click on the figure under Qty to deliver column, select all inventory items that have been already delivered and click New Delivery Note button.

image
image794×319 5.54 KB

This will copy inventory items from Qty to deliver to new delivery note. Delivery note will decrease your Qty to deliver and decrease your Qty on hand.

Do the same under Suppliers tab if applicable except the column is named Qty to receive and you would be creating New Goods Receipt. Goods receipts will decrease your Qty to receive and increase your Qty on hand.

This is essentially how you fix your Qty to receive, Qty to deliver and Qty on hand balances. This will have no effect on your Qty owned or figures on your financial statements.

9 Likes
2

@lubos, congratulations. You have moved inventory management back towards the elegance that characterized the program years ago: straightforward information, clear instructions, yet considerable flexibility based on which features are enabled.

No doubt some users will initially complain. But you have done much to encourage correct inventory management. Users who think you have broken their workflow would do well to consider why they are really doing what they have been doing. Much of the forum discussion surrounding this issue has revealed needlessly complex practices.

5 Likes
4

For Qty reserved & Qty on order , Is it possible to close the balance from the purchase order or sales order ? If the inventory is a solid goods 1 is 1 and 2 is 2. It’s very easy to manage. But if the inventory is liquid there’ll always be a discrepancies between the order and the invoices (± 2%). Eg. we purchase order 50,000 kg oil, the goods received won’t be exactly 50,000 kg but will be ranged from 49,000 - 51,000 kg. So in my situation the numbers of the Qty reserved & Qty on order very big. If I could just closed it in batch from the outstanding sales order or purchase order that would be great.

5

@rully_p yes - just mark Sales Order or Purchase Order as Cancelled and that will remove it from Qty reserved and Qty on order columns.

But I think Cancelled is wrong term. I’ll probably rename it to Inactive which is more generic.

1 Like
6

Hi I think I understand how this works.

However, we use purchase order entry and sales order entry and we prefer to update inventory movement via sales invoice and purchase invoice. I dont see this option on the recent upgrade. Kindly consider this options on future development.

We adopted this approach because;

  1. No double handling of data.
  2. Inventory and ledger is updated using single entry. Possibility of ommissions is minimised.
  3. We use manual GRVs because our stores location do not have access to manager all the time.
  4. Accounts staff work as stores team as well. Separating inventory update and ledger update may not add value to our workflow.

Alternatively you add option to receive/deliver on purchase invoice and sales invoice so that we have single entry that update inventory and ledger same time.

We look forward to your update.

7

I’m thinking whether this can’t be solved in current model.

What if you don’t use Qty to receive, Qty to deliver and Qty on hand columns and make them hidden?

Then just use Qty owned column which you treat as quantity on hand. Thougths?

1 Like
8

I think Closed much better term. Cancelled is wrong. Inactive more appropriate for inventory and assets or COA.

9

Previous approach was the best one in which you can specify on Invoice level whether you want to track or not. It was fit for those who want to track every order and those who dont. Also who sometimes track sometimes dont.
Maybe replacing Column -Qty delivered with just a Delivered (equivalent to no tracking) checkbox would have worked.

1 Like
10

Qty available

  • This is Qty on hand minus Qty to deliver minus Qty reserved.
  • This is used to see how much more inventory is physically available on hand that can be immediately sold and delivered.

Why are we being forced to use delivery notes or goods receipt if we just want to use sales order & qty reserved & Qty available column?

I think previous approach was better. Now if the user forgets to issue delivery notes/ GR after invoice, qty available will not show the right number.

I think in most small to medium business issuing an invoice means inventory in or out together with invoice.

So I think @shahabb suggestion of single checkbox like “All items received/delivered” in invoice level which would automatically issue DN/GR would be better if qty available is to be calculated from “Qty on hand” is made compulsory

Or even better approach in my opinion is qty available should be calculated from “Qty owned” instead of “Qty on hand” if Delivery notes & Goods receipt notes tabs are not enabled.

11

Maybe Status with “Completed/Filled” option as well as “Cancelled” option which would manually override the current status of orders.
Also Order status (Like delivery status previously) would be nice to have as a column.

12

@lubos
You may need to re-consider these effect.
Some businesses only make use of purchase invoice (to increase stock) and sales invoice (to reduce stock). Some don’t even need qty to recieve, qty to deliver, qty reserved, qty on order, qty to be available, qty desired, qty to order. All some need is only qty owned which is the status of their current inventory.

Kindly do something

1 Like
13

@nickyrub that’s why there is Edit Columns button where you select which columns you want to see. For new businesses, only Qty owned column is enabled by default.

14

Thanks Lubos, I understand but I want to believe that these new effect has taken toll on inventory quantity by location

15

Blockquote
“Qty available” should be calculated from “Qty owned” instead of “Qty on hand” if Delivery notes & Goods receipt notes tabs are not enabled

@lubos Can we expect this feature(condition) to be added to the current implementation?

16

The update has made sales invoice excluded from inventory quantity by location report.

2 Likes
17

I thought i was the only one experiencing this…
Find the attached proofs

1
1740×753 32.7 KB

sales invoice records have been excluded and its displaying only inventory transfer records, which resulted into negative records
@lubos please help resolve asap

18

So my question is what of those of us who have a little over 100 customers each for our about five different business who we have created a lot of Sales invoices for. Are we going to click them one by one and do the new delivery notes. Thanks.

19

@RAPHAEL_KENYA_SWANZY Good point. I think there will need to be ability to batch create goods receipts and delivery notes for all customers / suppliers at once. This would be useful if someone is tracking Qty owned only and wants to transition to Qty to deliver, Qty to receive and Qty on hand so they can do that quickly without too much clicking around.

20

So am using version 24.6.17.1665, And currently am unable to Balance our stocks with all the branches with the system stocks against the physical stocks we have. Figures showing under REPORT/INVENTORY QUANTITY BY LOCATION are very confusing. The total Quantity owned on our inventory item column for example :Product with code 001 COA MIXTURE is 118 in total for all locations , Yet under REPORT/INVENTORY QUANTITY BY LOCATION figures show there for each locations are more than thousands. I have upload a screen shot for your understand. Please help with this because its messing up our business balances. thanks.

Screenshot 2024-06-17 220734
Screenshot 2024-06-17 2207341921×1161 226 KB

Screenshot 2024-06-17 213001
Screenshot 2024-06-17 2130012562×1530 296 KB

21

Can you Please help With the step by Step approach with the batch create for that problem.
Or a video tutorial.
Thanks.