Skip to content

Stock Taking

Stock Taking

Background Information and Definition of Terms

Stock taking allows the shop owner to verify that the system shop inventory tallies with the inventory on the shop

Closing Value

The closing Stock is a snapshot of the inventory quantity at the end of stock take period by closing date. It is inserted by the cron job at the begining of next stock take.

Stock Take Value

During stock take, the clerk manually counts products in stock. This represents the counted quantity.

Stock Take Status

Stock take statuses:

  1. Pending - 0
  2. Draft - 1
  3. Sent - 2
Stock Reconciliation Status

Stock reconciliation statuses:

  1. Pending - 0
  2. Draft - 1
  3. Sent - 2

Periodic Stock Take (daily, weekly, monthly, yearly)

Stock take periods are selected from shop settings. These can be:

  1. Pending
  2. Reconciled

A system cron job runs on a daily to fetch the list of shops whose stock take duration has reached i.e :

  1. Shops with daily stock take
  2. Shops with weekly stock take that on the the 7th day of the week
  3. Shops with monthly stock take that on the final day of the month

Database Design

A db table is created with the following columns

id, inventory_id, product_id, shop_id, stock_take_period_id, previous_stock_take_period_id, local_id, local_inventory_id, closing_value, stock_take_value, stock_take_date, stock_reconciliation_date, period_start_date, period_end_date, stock_take_frequency, company_id, stock_take_status, stock_reconciliation_status, cron_status


classDiagram
  Product <|-- StockTake
  Shop <|-- StockTake
  class Shop{
    +int id
    +String Shop Name
  }  
  class Product{
    +int id
    +String Product Name
  }

  class StockTake{
    +int id 
    +int inventory_id
    +int product_id
    +int shop_id
    +int stock_take_period_id
    +int previous_stock_take_period_id
    +uuid local_id
    +uuid local_inventory_id
    +Float closing_value
    +Float stock_take_value
    +DateTime stock_take_date
    +DateTime stock_reconciliation_date
    +DateTime period_start_date
    +DateTime period_end_date
    +int stock_take_frequency
    +int company_id
    +int stock_take_status
    +int stock_reconciliation_status
    +int cron_status
  }
Values for the following columns are system generated:

expected_quantity = opening_stock + purchases + stock transfers + (all positive adjustments) + (all negative adjustments - (sum of all inventories sold in all sales)

Below is a sample payload for creating stock take:

{
  "localId": "658a7d8f-a4e3-4f6c-9b29-1fe0207554bd",
  "stockTakeDate": "2023-04-14",
  "stockTakeDuration": "monthly",
  "stockTakeFrequency": 1,
  "stockTakePeriodId": 13,
  "stockTakeStatus": 1,
  "stockTakeValues": [
    {
      "inventoryId": 168,
      "localInventoryId": "0d3490b9-301d-4e98-a484-3206a9faf154",
      "quantity": 150.0
    },
    {
      "inventoryId": 174,
      "localInventoryId": "ce348100-97e8-478c-912e-7dfcbb7b1635",
      "quantity": 10.0
    },
  ]
}

This will create entries in the stock reconciliation table for each stock take values.

The following values will be modified

counted_quantity, stock_take_date, reconciliation_date will be set tot the values sent on the request. Note that this value was initially set to null

| id | inventory_id | product_id | shop_id | shock_take_period_id | local_id | closing_value | stock_take_value | stock_take_date | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | 168 | 23 | 15 | 1 | 658a7d8f-a4e3-4f6c-9b29-1fe0207554bd | 10 | 150 | 30/March/2023 | | 2 | 174 | 25 | 15 | 1 | 658a7d8f-a4e3-4f6c-9b29-1fe0207554bd | 12 | 10 | 30/March/2023 |

Stock take record will be created with status draft or sent.

How stock is reconciled

Once a stock take is perfomed by clerk, the shop owner performs the reconciliation. If there is any positive or negative variance, there will be +ve or -ve adjustments on the inventory and inventory transactions will be created.

Alternative scenarios

  1. Alternative scenario 1:

    1. When a cron job fails

      When a cron job fails, the stock take record will only be created once the cron job runs again or the user sends stock take values

      If the cronjob runs before the user sends stock take values, the closing_quantity is set to null but all other values are updated accordingly

  2. User does not approve stock take value due to dispute

    1. Inventory values remain as is
Clarifications:

Why does the closing_quantity remain as null when it could be calculated from the inventory transactions? - Inventory transactions may not always show correct inventory values.

UI Process:

Select period for the stock take

  1. Start date
  2. End date
  3. Frequency (daily, weekly, monthly)

View list of inventories on the shop

Count inventory value on the shop and input the values on the system

Save the values as draft until stock take is complete

Once stock take is complete, the user then clicks on send stock take


Below is a sketch of the interface:

UI Sketch