Stock Reconciliation

Stock Reconciliation is the process of counting and evaluating material/products, periodically and at year end in order to:

  • Keep the actual physical stock count and book stock count in sync
  • Value the stock for preparation of the accounting statements

Stock Reconciliation is used for:

  • Posting opening stock
  • Reconciling book and actual stock

1. How to Create a Stock Reconciliation to Post Opening Stock

1.1. For Non-serialized Items

Using stock reconciliation you can update the quantity of specific items in a warehouse as of specific time.

To post opening stock:

  1. Go to Stock > Tools > Stock Reconciliation > New
  2. Set Posting Date.
  3. Set Purpose to Opening Stock.
  4. Set Item Code, Warehouse, Quantity, and Valuation Rate.
  5. Select 'Temporary Opening' in Difference Account.
  6. Select Cost Center.
  7. Save and Submit.

1.2. For Serialized Items

Using stock reconciliation you can add the items in the stcok which has serial number or the batch number.

To post opening stock:

  1. Go to Stock > Tools > Stock Reconciliation > New
  2. Set Posting Date.
  3. Set Purpose to Opening Stock.
  4. Set Item Code, Warehouse, Quantity, Serial / Batch No, and Valuation Rate.
  5. If you want to auto generate serial no / batch no then keep the serial no and batch no fields blank.
  6. For auto generation of serial no, you need to set "Serial Number Series" in the item master.
  7. For auto generation of batch no, you need to enable "Automatically Create New Batch" checkbox in the item master.
  8. Select 'Temporary Opening' in Difference Account.
  9. Select Cost Center.
  10. Save and Submit.

2. How to Create a Stock Reconciliation to Reconcile Book and Physical Stock Count

Stock Reconciliation is the process of counting and evaluating stock-in-trade, periodically and at year end in order to value the total stock for preparation of the accounting statements. In this process actual physical stocks are checked and recorded in the system. The actual stocks and the stock in the system should be in agreement and accurate. If they are not, you can use the stock reconciliation tool to reconcile stock balance and value with actuals.

To reconcile the stock:

  1. Go to Stock > Tools > Stock Reconciliation > New
  2. Set Posting Date.
  3. Set Purpose to Stock Reconciliation.
  4. Set Item Code, Warehouse, Quantity, and Valuation Rate.
  5. Select an expense account in Difference Account.
  6. Select Cost Center.
  7. Save and Submit.

3. Features

3.1 Upload Data Through Spreadsheet

If you have lot of items, you can upload the details via a spreadsheet.

  1. Download Template

    Open new Stock Reconciliation and click on Download button to download the template in CSV format.

    Stock Reconciliation

  2. Enter Data in CSV Template.

    The CSV format is case-sensitive. Do not edit the headers which are preset in the template. In the Item Code and Warehouse column, enter exact Item Code and Warehouse as created in your ERPNext account. For quantity, enter stock level you wish to set for that item, in a specific warehouse.

    Stock Reconciliation

  3. Upload the CSV file with the data by clicking on 'Upload' button.

    Stock Reconciliation

  4. Review, Save, and Submit.

    Stock Reconciliation

  5. Check Stock Ledger Report for updated stock balance.

    Stock Reconciliation

3.2 Get Stock Balance and Valuation as of Specific Date and Time

You can import the stock balance and valuation as of specific date and time by clicking on 'Items' button. You can update the Quantity and Valuation Rate as needed.

Stock Reconciliation Items Button

4. How Stock Reconciliation Works

Once a stock reconciliation is posted to update the quantity on specific date and time for an item in a warehouse, it will not be modified by subsequent stock transactions even if such transactions have a posting date which is prior to the stock reconciliation date.

Examples are as follows

4.1 For Non-serialized Items

Consider an item with code 'ABC001' in a 'Mumbai' warehouse. Let's assume that stock as on 10th January is 100 units. Stock Reconciliation is made on 12th January to set stock balance to 150 units.

Stock Ledger would look as shown below:

Posting Date Qty Balance Qty Voucher Type
10/01/2014 100 100  Purchase Receipt
12/01/2014 50 150 Stock Reconciliation

If a new Purchase Receipt entry is made on 5th January, 2014, which is prior to the date of Stock Reconciliation entry, Stock Ledger would look as shown below.

Posting Date Qty Balance Qty Voucher Type
05/01/2014 20 20 Purchase Receipt
10/01/2014 100 120 Purchase Receipt
12/01/2014
150 Stock Reconciliation

As you can see, the Balance Qty as on 10th January got updated from 100 to 120. But the Balance Qty as on 12th January did not get updated from 150 to 170.


4.2 For Serialized Items

For item ITEM-00225 has the 6 serial nos HJF00020, HJF00021, HJF00022, HJF00023, HJF00024, HJF00025 with valuation rate as 496.67 per serial no. At the end of the year user has came to know that they have only 3 serial nos against that item with valuation rate 530. So to remove the old serial nos HJF00020, HJF00021, HJF00022, HJF00023, HJF00024, HJF00025 and add the new serial nos with new valuation rate, stock reconciliation can be use as follows:

Select the item ITEM-00225 in the stock reconciliation, on selection of the item system will auto pull the existing serials nos. Then set qty as 3, valuation rate as 530 and serial no as HJF00026, HJF00027, HJF00028.

Stock Reconciliation

Before reconciliation, the valuation rate was 530 and available qty was 6, so the total stock value was 3,180. After reconciliation, the valuation rate has changed to 620 and available qty changed to 3, so the new stock value becomes 1,860. To adjust the stock value in the accounting, the system has credited extra amount 3,180 - 1,860 = 1,320 to warehouse's account and debited to stock adjustment account. The GL entries for the above entry is as follows:

To view gl entries, click on button View > Accounting Ledger

Stock Reconciliation

The stock balance after submission of the stock reconciliation: Stock Reconciliation

The general ledger for the warehouse account Nagpur after submission of the stock reconciliation: Stock Reconciliation

4.3 For Batch Items

Stock reconciliation for batch items will be used to add a new batch or to update the quantity of the existing batch. For example, the batch JHGJH00003 has the current quantity as 60 but if the user wants to make it 100 then by using stock reconciliation, user can update the batch quantity.

Stock Reconciliation

Batch-Wise Balance History report after submission of the stock reconciliation: Stock Reconciliation

  1. Stock Entry
  2. Accounting Of Inventory Stock

Next: Stock Settings