How to retrieve Closing Stock Balance on a particular date?[MIGRATED]

posted Jan 8, 2009, 5:55 PM by janet@wavelet.biz   [ updated Jun 9, 2011, 9:07 PM by Unknown user ]
There are 2 ways to retrieve closing stock on a particular date :

(A) Through Accounting Module

To do this, you just need to follow the steps below :
  1. Go to Accounting -> GL Listing
  2. Select PC Center, Date range
  3. Select the inventory GL, click Select GL for Viewing
  4. Click Printable
(B) Through Inventory Module

To do this, you just need to follow the steps below :
  1. Go to Inventory -> Stock Reports -> Historical Stock Balance
  2. Select date, location
  3. Refer to the Cost(Txn) column
  4. You need to total up the Cost(Txn) column for each location, in order for you to know your Closing Stock Balance
  5. The difference between the Total of Cost(Txn) and inventory GL is caused by Reset MA. You can get the Reset MA amount from Inventory -> Stock Reports -> Stock Adjustment by Reset Moving Average

How does Cost(Txn) gets its value?

As you know, Inventory GL is populated when the following transactions are done :
  • Goods Received Note
  • Purchase Return
  • Invoice
  • Sales Return
  • Stock Adjustment
  • Trade In
  • Reset MA
As you also know, depending on the Transaction, either the Transaction Price or the Cost Price will be recorded in the Inventory GL, as shown below :

 Transaction What is being recorded in Inventory GL 
 Goods Received Note
 Quantity * Transaction Price
 Purchase Return
 Quantity * Transaction Price
 Invoice Quantity * Cost Price
 Sales Return
 Quantity * Cost Price 
 Stock Adjustment
 Quantity * Transaction Price
 Trade In
 Quantity * Transaction Price
 Reset MA  The difference in price 

Thus, by using the formula above, the system is able to calculate Cost(Txn).

Why the difference between the Total of Cost(Txn) and inventory GL is caused by Reset MA?

Just for your information, when Reseting the MA, below is what happened in the Accounting side :

 Inventory DR
 Inventory Variance

Cost(Txn) only takes into consideration all transactions which involved Stock Quantity, either Quantity is being deducted or incremented. However, when you perform this Reseting of MA, there's is no Stock Movement, thus, it is not being captured.

Why when stock balance quantity is ZERO, but Cost(Txn) still showing some values?

This is caused by Reset of MA. For example :

 No  Transaction Type
 Transaction  Unit Cost
 Values captured in Cost(txn)
 Values captured in Inventory GL
 Stock Balance Quantity
 1 GRN 10 quantities @ RM10 / unit RM10 RM100 RM100 10
 2 Reset MA
 Reset MA to RM9 / unit  RM9   -RM10 10
 3 Invoice 10 quantities  RM9  -RM90 -RM90 0
  Total   RM10 RM0 0 

Thus, after step 3, you will see that your Stock Balance Quantity is actually ZERO, but your Cost(Txn) will be showing RM10. However, as mentioned earlier, the difference between the Cost(Txn) and inventory GL is you Reset MA value, thus,

Cost(Txn) :   RM10
Reset MA : - RM10
Total        :    RM0

Why is the Cost(Txn) showing negative value?

This can happen in various situations, for example :

(1) Back-dated Transactions

 No DateTransaction Type Transaction
 Unit Cost
 Values captured in Cost(Txn)
 Values captured in Inventory GL
 Stock Balance Quantity 
 1 2008-01-01
 GRN  10 quantities @ RM10 / unit RM10 RM100 RM100 10
 2 2008-01-05 GRN 10 quantities @ RM9 / unit
 RM9.5  RM90 RM90  20 
 3 Back date to 2008-01-04  Invoice  20 quantities  RM9.5 RM190 RM190  0 

Thus if you generate you Historical Stock Balance report as of 2008-01-04, below is what you will see :

Cost (Txn) =  RM100 - RM190 = -RM90