Module: Computing historical stock levels

Executive summary

To work effectively, STOCK needs to know the exact quantity of stock day by day in the past. It is helpful especially when evaluating stockouts, but the information is also useful in computing stock levels, turnover and other indicators.

These data are not readily available in ERP systems for the past – and, if they are, their export is rather time consuming.

Therefore, STOCK includes a module that – based on the current stock on hand and all stock transactions at warehouses – computes the value of stock for each day in the past.

Functional description

The module computes the stock at the beginning of the day, both in quantity and value. Based on the – now available – stock on hand for a given day, the STOCK system computes the average monthly stock, again, both in quantity and value.

Computing historical stock levels in quantity

The computation takes as its base all the warehouse transactions that are not adjusted for cancellations (module: Cleaning cancellations).

As a basis for the computation, we take stock on hand at the beginning of the day, for which the calculation is taken.

For the next day in the past, all daily transactions are subtracted from the current stock on hand and it is the value considered as stock on hand at the beginning of the given day. For the days between today and the day of these transactions, stock is taken to be the current stock on hand.

For example, suppose that for March 15, 2016, the stock on hand is 10 items; the first transaction before today is March 12, 2016 – a single sale of 4 items and a single purchase of 5 items:

• 15. March 15: stock on hand is 10 items (i.e., current stock on hand)
• March 14: stock on hand at beginning of the day is 10 items (the same as the current stock on hand, since there were no transactions on March 14)
• 13. March 13: stock on hand at the beginning of the day is 10 items
• March 12: stock on hand at the beginning of the day is 9 items; we add 4 items to the stock on hand, i.e. 10 items (4 items were sold, hence, the stock on hand increases by 4) and we subtract 5 items (5 items were bought, hence, the stock on hand must decrease by 5 items)

The calculations go in this manner until the first transaction in the history of the product in the given warehouse.

Negative stock

It may happen that there is less than zero of the given item in the stock. This is an error in the ERP system: it allows to sell and issue stock transactions even when there is 0 items of the given product in stock – and to receive the product later (even several days later, in some cases).

In such a case, we refer to the stock on hand as zero: STOCK however ‘remembers’ that the product is ‘in debt’ and the moment the new supply of the product arrives to the warehouse, it is added to the ‘negative’ value of the stock. So, it could also happen that after having received 5 items of the product, the stock on hand is 0.

Computing historical stock levels in value

To compute the value of stock on hand, we use stock prices exported to the SIDI for the given transaction. As a rule, we use a price exported for the given product in the given warehouse as a stock price.

The value of the stock is estimated by the stock price of the last warehouse transaction. So, to continue with the previous example, the stock on hand is 10 items, and let us assume that its stock price is 3 CZK per piece. Let us further assume that on March 12, the stock transactions (expressed in price, i.e., in value) are 2 CZK per piece; the stock on hand between March 12 to 14 is estimated as 2 CZK per piece.

When calculating stock prices, we ignore transactions with stock price of 0 and those transactions whose stock price is left blank.

If for a given day there are more transactions at different stock prices, the final stock price used for the computation for the given day is the weighted average of these stock prices.

In all calculations of value, STOCK always starts by calculating the quantity and based on the quantity, the value is always set as the quantity times the stock unit price. However, suppose that the customer just wants to reevaluate the warehouse and to do so, s/he creates a stock transaction with a quantity of 0 and a new price (i.e., the ‘reevaluated’ price). STOCK deals with such transactions as well, notice, however, that this functionality must be explicitly enabled. In this case, the reevaluated price is applied to the total stock at the given time, consequently, the stock price includes this revaluation from then on.

Computing average monthly stock in quantity and value

For monthly views (in charts and tables), an average of stock levels in the given period is used. Specifically, it is neither the figure at the beginning nor at the end of the month that is used for computations.

This module takes care of computing monthly levels of stock based on computations for days. As a stock level in a given month, Planning Wizard computes the average stock on hand for each day (and averages it for a month), both in quantity and value.