The average stock level is calculated for every item/warehouse record in the Item file. It is updated by running the average stock level calculation. The main purpose of calculating the average stock level is to be able to calculate the inventory turnover rate. The inventory turnover rate indicates the number of times per year that the average stock level is turned around (sold).
The average stock level calculation should be run at least once every forecast period, but can be run more frequently. When you run the average stock level calculation it takes into consideration all inventory transactions in the Distribution system, from the date of the last average stock level calculation to today’s date.
Note: Summarised inventory transactions are not included in the calculation. Therefore, you should not summarise inventory transactions unless you have run the average stock level calculation for the period. This job is controlled through the Job schedule batch function. See About scheduling periodical jobs for details.
Calculation process
The average stock level is calculated using the present stock level and the inventory transactions. The end-of-day stock level is calculated for each day, going back to the date of the last average stock calculation.
The average stock level is updated and the number of days is increased with the number of days from the last calculation date until today.
The calculation works as follows:
- If today’s date is greater than the date of the last average stock level calculation, the system calculates a new average stock level for the item/warehouse combination.
- The system retrieves the present physical stock from the Warehouse file, reads all the inventory transactions for today, and subtracts them from present physical stock to get the current stock level. Today’s inventory transactions are not included in the calculation. They will be included when this program is run the next time.
- The system reads all inventory transaction records until it finds an inventory transaction record with a date less than the date of the last average stock level calculation.
- For each day, it calculates the stock level from the end of the previous day to the end of the current day.
- Outgoing stock balance is calculated for each day.
Updated information
The following information is updated for each item/warehouse combination in the Item file through the average stock level calculation:
- Date for last average stock calculation
- Present average stock level year to date (YTD)
- Number of days that average stock YTD is based on
- Average stock level last year (LY)
- Number of days that average stock, LY is based on
Note: These fields are not displayed in the Item file. However, this information can be viewed if you print the Inventory control item information printout and set Print details to YES.
Note: The average stock quantity and value are printed on the Inventory turnover report.
Calculation formulas
The following formulas are used in the average stock level calculation:
Note: The number of days covered, from and including date of the last average stock level calculation up to but not including the current date, is called (n) in the formulas below.
| The total accumulated stock level for the period covered is calculated: |
| TOTLVL = (SL1+SL2+..+SLn)
where TOTLVL is total accumulated stock level, and |
| The average stock level within the period covered is calculated: |
| AVGST = TOTLVL/n
where AVGST is average stock level within the period covered. |
| The values in the Inventory control item information are calculated: |
| ASTY = (ASTY * NDTY + AVGST * n)/(NDTY + n)NDTY = NDTY + nLWAC = Date of today
where ASTY is present average stock level year to date, |
Calculation example
This example displays the average stock level calculation for a specific date.
| Parameter | Value |
|---|---|
| Current stock on hand | 825 pcs |
| LWAC (last average stock level calculation) | 10/4 |
| Today’s date | 14/4 |
| ASTY (present average stock level year to date) | 885 pcs |
| NDTY (number of days the average stock level is based on) | 52 days |
The following inventory transactions exist for the item/warehouse record:
| Warehouse | Date | Inventory transaction |
|---|---|---|
| PROD/WHS | 14/4 | -10 |
| PROD/WHS | 13/4 | -20 |
| PROD/WHS | 13/4 | -5 |
| PROD/WHS | 12/4 | +10 |
| PROD/WHS | 11/4 | -10 |
| PROD/WHS | 10/4 | +10 |
| PROD/WHS | 10/4 | -25 |
Based on the transactions above, the outgoing stock level for the each day is:
| Date | Stock level |
|---|---|
| 13/4 | 835 |
| 12/4 | 860 |
| 11/4 | 850 |
| 10/4 | 865 |
The number of days covered is calculated as follows:
| (n) = 4 TOTLVL = (SL1+SL2+..+SLn) TOTLVL = (835+860+850+865) = 3410 |
The average stock level within the period covered is calculated:
| AVGST = TOTLVL/n AVGST= 3410/4 = 852,5 ASTY = (885 * 52 + 852,5 * 4) / (52+4) = 882,67 NDTY = 52 + 4 = 56 LWAC = 19930414 |
This tells you that on the 14th of April the average stock level, from the beginning of the year is 882,67 (stock units) for this particular item/warehouse record.
Turn of year
When you run the Turn of year routine in IBS Distribution, the values in the YTD fields are moved to the LY fields, and the YTD fields are set to zero. In addition, the date for last calculation is set to zero.
When the first average stock level calculation for the new year is run, it uses only inventory transactions from the present (new) year. Therefore, if you run the average stock level for the last time on December 15th of the old year, any inventory transactions that have entered the system after the 15th and before the turn of the year are not included in the first average stock level calculation for the new year.
To include the last inventory transaction statistics of the old year in the average stock level, the average stock level calculation should be run as late as possible in the year. Preferably after the last inventory transactions have been registered, but before the turn of year routine is executed.
If it is a new a calendar year, and you are ready to begin calculating average stock level for the new year; make sure that the Turn of year routine has been executed. If this routine has not been run, the results of the average stock level calculation is still based on the previous year’s statistics.
Enquiries and printouts
- Item file enquiry
- Inventory management file list printout – displays the Item file information that has been updated through the calculation, if Print details is set to YES.
- Inventory turnover report – displays the average stock quantity and value.