Solved Physical Inventory Adjustment (1 Viewer)

[ahmad_rmh]

Dear Experts,

Does someone has an idea of physical inventory adjustment?

My inventory structure based on the following;

1. Goods Received from Vendors.
2. Defective/Unmatched Goods Returned to Vendors.
3. Stock Transferred to the Branches from Main Warehouse.
4. Stock Returned back from Branches to Main Warehouse.
5. Disposal of Expired /Damaged Stock at Main Warehouse.
6. Physical Inventory Adjustment Vs Book Balance.

and at the end,

7. Net quantity on Hand on a specific date.

Kindly share your ideas.

 

[CJ_London]

are these separate columns in a table? separate tables? or separate records in a table?

And what is Net quantity on Hand on a specific date?. - this should be calculated as and when required

Edit: if your question is which is the best way - separate records in a table would be my recommendation

 

[ahmad_rmh]

=> Each one is in separate tables and sub tables.

=> Quantity on hand is that when some one will make a date selection, or wants to know what was the balance in stock of each item on a specific date then the net Quantity on hand should have to be shown after meeting the above criteria.

 

[arnelgp]

1. good received, if "as per specs", record and put/create warehouse location.
2. defective, issue short/damage report, do not add to warehouse.
3. short quantity, issue short/damage report, record actual quantity received.
4. "out of spec", issue non-compliance report, put to quarantine, until qc decides if it can be used to other proj.
5. stock transfer/return from whs to whs, record positive qty to receiving whs, negative to originating whs.
6. disposal, entered as - quantity, add remarks as unusable/expired or damage.
7. physical inventory, put the actual remaining quantity.

note 5-7 will use 1 table (transaction) with a field to signify the "activity" being carried out (ex: start-up, transfer, receipt, disposal, etc).
for further assistance, consult your Material Controller or Warehouse Supv on your current system.
you may also consult your QC/Accounting for any methods you have.

balance of an item = qty from last inventory + qty received (from last inv date + 1) - qty issued (from last inv date + 1)

if there is no inventory yet, Quantity from start-up + qty received (from start-up)- qty issued (from start-up)

 

[June7]

Review http://allenbrowne.com/AppInventory.html

 

[gemma-the-husky]

A comprehensive inventory management system with costing is far from trivial.

In general terms I would try to have a single transactions table with a movement type field. If you then get an over or shortage at a stock-take you can simply insert an adjustment-type transaction to adjust the balance.

in order to avoid having to recalculate stock balances back to the distant past, you can also insert a transaction with a new stock balance at a certain point in time.

So say you have 1000 records for stock item 1234, showing a current on hand total of 14,500. Your stock take counts 14,200

So you insert a stock adjustment transaction record of -300 to account for the shortage.
Then insert a new stock balance record of 14,200, to avoid having to sum all the old items.

Something like that.

 

[ahmad_rmh]

A comprehensive inventory management system with costing is far from trivial.

In general terms I would try to have a single transactions table with a movement type field. If you then get an over or shortage at a stock-take you can simply insert an adjustment-type transaction to adjust the balance.

in order to avoid having to recalculate stock balances back to the distant past, you can also insert a transaction with a new stock balance at a certain point in time.

So say you have 1000 records for stock item 1234, showing a current on hand total of 14,500. Your stock take counts 14,200

So you insert a stock adjustment transaction record of -300 to account for the shortage.
Then insert a new stock balance record of 14,200, to avoid having to sum all the old items.

Something like that.

Sure, I agree with your point of view and it should be like this.

Kindly suggest, how to make the query as per the above criteria to come up on the net ending balance because some of the items at the end of the month are out of balance and the remaining matches with the book balance.

And We have to adjust only those items which are out of the balance.

 

[gemma-the-husky]

Well it comes back to how you store your data. You need to sum all the transactions between date/time A and date/time B. The more your data is split into multiple tables, the more sub-calculations you need to do. If you have one table, it's one query. If you have 10 different tables, it's 10 different queries, and a summation. You also need to manage how you protect your data. If you strike a new balance as of today 4th July, you can't then have users changing data before 4th July, as that would affect your new starting balance as of 4th July.

 

[jdraw]

@amhad_rmh

Have you tired drawing a picture to model your data and transactions involved? You have received a lot of good info from others, but a picture that you understand will help put that info into an arrangement that you can reference when designing and testing your database structure.
There are several threads dealing with Inventory that may offer insight.
As Dave said in post #6, inventory is not trivial. Get you design tested thoroughly before getting too deep into physical database.

 

[ahmad_rmh]

@amhad_rmh

Have you tired drawing a picture to model your data and transactions involved? You have received a lot of good info from others, but a picture that you understand will help put that info into an arrangement that you can reference when designing and testing your database structure.
There are several threads dealing with Inventory that may offer insight.
As Dave said in post #6, inventory is not trivial. Get you design tested thoroughly before getting too deep into physical database.

All of the work has been done, but the only thing i am facing is physical inventory adjustment.

 

[ahmad_rmh]

I have attached my database file.

Kindly suggest corrections and solution of Inventory Adjustment.

Adjustment form is not allowing me to enter the adjustment Quantity.

 

[arnelgp]

i don't think that is correct?
you create a separate table for physical inventory.
if there are discrepancy, that is what your organization will tackle.
will they report it as just "lost" or will they account someone?
calculation of the balance is still:

balance of an item = qty from last inventory + qty received (from last inv date + 1) - qty issued (from last inv date + 1)

if there is no inventory yet, Quantity from start-up + qty received (from start-up)- qty issued (from start-up)

 

[CJ_London]

the problem is you have addressed each action as a separate 'thing' so you have many tables and to get a view of transactions you are going to need a union query. As you have been advised several times transactions should all be in one table. Only way I can see you can do it if you carry on with your existing structure is to have another table for adjustments.

And calculating your quantity on hand is going to be very problematic in the real world.

So far as I can see you have actions for stock coming in, stock being transferred between warehouses, stock returned to suppliers etc. I don't see anything that indicates stock going out (of the business)/sold.

compare your tables and relationships with the one suggested by June in post 5

 

[ahmad_rmh]

the problem is you have addressed each action as a separate 'thing' so you have many tables and to get a view of transactions you are going to need a union query. As you have been advised several times transactions should all be in one table. Only way I can see you can do it if you carry on with your existing structure is to have another table for adjustments.

And calculating your quantity on hand is going to be very problematic in the real world.

So far as I can see you have actions for stock coming in, stock being transferred between warehouses, stock returned to suppliers etc. I don't see anything that indicates stock going out (of the business)/sold.

compare your tables and relationships with the one suggested by June in post 5

I am making database for the Main Warehouse who is receiving inventory and transferring inventory to the branches. For accounting, sales and production we are using QuickBooks and don't want to integrate QuickBooks with the branches.

 

[CJ_London]

OK so the branches will not be using this app, only the main warehouse?

Either way, you don't need the from warehouse field (or the warehouseID fields in the other tables) because it is always going to be 1. And with the addition of a 'direction' field (Transfer/StockReturn), you don't need the stock return tables.

Further provide some more alternatives to the new direction field, you don't need the receipts, goodsreturn or disposal tables.

Further still, put these alternatives in a table and include a multiplier field (which will contain a 1 or -1) so when you have your query you can multiply the quantity by the multiplier so the calculation of net stock becomes a lot easier.

Your stock adjustment then simply becomes another 'direction' option.

And you have reduced the number of tables from 10 to 3 with a much more efficient way of calculating values - probably only need a couple of queries

Only other comment is what about goods in transit? Goods leave the main warehouse and arrive at the branch a day or two later - so you need to be considering stock that is goods in transit - or is that the responsibility of the branches? And what if you send the branch 100 items and they claim they only received 80? or 120?

 

[June7]

Did you review link in post #5?

 

[ahmad_rmh]

Did you review link in post #5?

reviewing on it

 

[ahmad_rmh]

I have attached database file and screenshot of inventory adjustment form, i want to show QOH in the form but it does not allow me for data entry. Without this it works.

What is the solution?

 

[CJ_London]

nothing attached

 

[ahmad_rmh]

nothing attached

See i have attached again