--- title: "Stock Re-Order Algorithm/Data Setup" canonical: "https://kb.myframeworks.com.au/space/PROSTIXV48DOC/31097126/Stock%20Re-Order%20Algorithm%2FData%20Setup" format: markdown --- # Overview The reorder algorithm is a mathematical equation that looks at stocking levels for a product and calculates a suggested reorder quantity at a certain point in time so that required stocking levels are maintained. Certain mandatory product information is used in the stock reorder algorithm to determine a suggester reorder quantity. This mandatory product information is stored in two of the Product Maintenance options for a product: **Stock Data** and **Suppliers**. There are two Stock Re-Ordering Algorithms controlled be System Tailoring Code **I****nvStkRord** that defines which algorithm is in use, either: 1. **MintoMax** = when at minimum or below order up to max or, 2. **Standard** = dynamically determines order quantity based on sales and lead times ## Explanation of the MintoMax and Standard Algorithms ## MintoMax Algorithm In order to provide a means of running an alternate stock re-ordering algorithm a new tailoring code has been introduced. This alternate algorithm has been simplified so that only the maximum stock level, minimum pack levels and minimum reorder quantities are considered. Stock requirements for scheduled stock re-ordering are calculated using the following formula: A simplified calculation so that only the **maximum stock level**, **minimum pack levels** and **minimum reorder quantities** are considered. > ⚠️ **Average Daily Sales** and **Lead Times** are NOT taken into consideration in this calculation. Stock is ordered when the product is equal to or below the **Minimum Stock **level and is then ordered up to the **Maximum Stock** Level. Minimum order quantities are respected, however, never more than the maximum stock level is ordered. The order quantity is reduced if the pack quantities would result in the order quantity plus the stock on hand exceeding the maximum stock. ## Standard Algorithm Stock requirements for scheduled stock re-ordering are calculated using the following formula: - **Quantity Available: **Stock on Hand ***plus*** Outstanding Purchase Orders ***minus*** Outstanding Customer Orders - **Suggested Order Quantity: **Average Daily Sales (promotions can be excluded) ***multiplied by*** Lead Time in days ***minus*** Quantity Available ***plus*** Minimum Stock - If the Suggested Order Qty < Min Stock and Qty Available > 0, Then Suggested Order Qty = Min Stock – Qty Available. - If the Suggested Order Qty < Min Stock and Qty Available < 0, Then Suggested Order Qty = Min Stock. The Suggested Order Quantity is rounded up to the relevant increment of the supplier Pack Size unless the flag **InvOrdPack** is ticked and the product is a slow mover. That is, sales for the previous six months are less than the supplier Pack Size. In this case, there will be no rounding, so the Suggested Order Quantity will be for a broken pack size, usually without any quantity break reduction in cost. - Then, if the Suggested Order Quantity is less than the supplier's Minimum Order Quantity, it is rounded up to the supplier's Minimum Order Quantity unless the product is a slow mover. - Then, if the Suggested Order Quantity exceeds the Maximum Stock, it is rounded down to or below the Maximum Stock by increments of the Pack Size (unless Maximum Stock = 0 in which case – no change). - If the Suggested Order Qty < 0 set it to 0. > 📝 **Flags applicable to this feature:** > 📝 > 📝 - **InvStkMin: **Default for the Below Minimum field > 📝 - **InvOrdPack:** For slow moving products. That is, sales for the previous six months are less than the Supplier Pack Size. The stock re-order algorithm will round up to the nearest multiple of the supplier's pack quantity. --- # Product Maintenance Options Required ## Product Details The reorder algorithm is a mathematical equation that looks at stocking levels for a product and calculates a suggested reorder quantity at a certain point in time so that required stocking levels are maintained. Certain mandatory product information is used in the stock reorder algorithm to determine a suggester reorder quantity. These suggested quantities can be reviewed and modified before a purchase order is generated. Supplier information stored against a product is taken into consideration when this purchase order is generated (for example, the supplier’s minimum order quantity), therefore the mandatory information is stored in two of the Product Maintenance options for a product: Stock Data and Suppliers. ## Stock Data Certain fields in the Stock Data option are mandatory to the working of the stock reorder algorithm and for the creation of a purchase order. Refer to the 'Stock Data Options' collapsible link below for an explanation of some of these options.

For more information about the Stock Data options, click here

**Location Code ** In the future, this code prints on the Stock Reorder Report so that the product can be physically checked if required. --- **Min Stock** What is the minimum level of stock that is required on hand at any one time? The reorder process uses this value in three areas – firstly, the reorder process prompts the user whether products with stock on hand Below Min are to be considered – this is the value the reorder process is referring to. Secondly, this value is used as part of the Quantity Available calculation. Thirdly, it is used when calculating the Suggested Order Quantity --- **Max Stock** What is an acceptable maximum stock level of this product? If the Suggested Order Quantity exceeds the this value, it is rounded down to or below the Maximum Stock by increments of the Pack Size --- **Lead Time** How long between generating the stock reorder and the delivery date of the next order (not this order). Why - if stock is ordered weekly and the time between ordering and delivery is 3 days, the stock on the purchase order is not on the shelves for 10 days. However, during the next 10 days, this stock becomes depleted. This figure is used in calculating the Suggested Order Quantity. --- **Product Type** The reorder process prompts the user whether Non-Stocked items are to be considered in the process. A product with a type of 1 is a non-stocked item. Threshold This value is used to determine the excess stock calculation required by the flag 'InvExBrStk', that is, before an order is placed, all other branches within this state are checked for excess stock. --- **Branch Supplier** Purchase orders generated during the stock reorder process for this product, for this branch, will be generated for this supplier. If the nominated supplier is not an existing preferred/alternate supplier for the product, it is created as an alternate supplier and pricing details should be keyed so that the correct information can be used in the generation of the purchase order. --- **Performance Cat** Used in conjunction with excess stock calculation and the 'InvExBrStk' flag. When performance categories are set up in **'System Administration' - 'Configuration' - 'Miscellaneous Tables'**, each category has a yes/no entry in the 'Inter Brn' column to indicate whether it must be transferred to other branches when in excess. ---

## Supplier Details While there is a large amount of supplier detail that can be stored against products for the preferred and any number of alternate suppliers, certain pieces of information are mandatory in the stock reorder process. Supplier details are maintained via the **'Suppliers'** option within **'Product Maintenance'**. Refer to the 'Supplier Detail Options' collapsible link below for an explanation of some of these options.

For more information about the Suppler Data options, click here

**Inner Pack Qty** The Suggested Order Quantity is rounded up to the relevant increment of this inner pack quantity when the automatic purchase order is generated from the reorder process. This is generally the conversion factor between the Stock/Sell UOM and the Purchasing UOM. --- **Minimum Order** If the Suggested Order Quantity is less than this supplier minimum, the order quantity is rounded up to this minimum order quantity unless the product is a slow mover. --- **Stock Source** Applicable for the buying groups where this supplier’s product is ordered/sent either from a warehouse (W) e.g Mitre 10 or directly (D) from the supplier e.g. Ryobi. The reorder process allows either W’s or D’s only to be included in the reorder or both. ---

## Scheduled Task for Stock Reordering The following details refer to the scheduled task: - The reorder algorithm is a mathematical equation that looks at stocking levels for a product and calculates a **suggested reorder quantity** at a certain point in time so that required stocking levels are maintained. - Certain mandatory product information is used in the stock reorder algorithm to determine a suggester reorder quantity. This mandatory information is stored in two of the Product Maintenance options for a product: **Stock Data** and **Suppliers**. ## For the Report The **minimum** and **maximum** values at the branch are not changed by the Scheduled Stock Reordering task. The minimum and maximum used is the one available in 'Product Maintenance', then 'Stock Data' for the branch you are reordering at the time the task runs. If you wish to use the old values, make sure the task runs and finishes before the Stock Forecasting task. To use the latest figures, schedule these two tasks so that the Stock Forecasting has updated the max and min values before the Stock Re-ordering task starts. Bypass the product if no stock, no orders and not sold for 6 months when not using the system flag '**InvStkAct**'. # The Reorder quantity is calculated by - Anything in **Bold** is a value stored in ProStix. - Anything underlined is calculated within this program. - The available quantity is calculated as per Stock Enquiry availability; Stock On Hand – Customer Orders + Purchase Orders – Deferred Sales. - Calculate the number of days = the difference between the **To Date** and **From Date**. Calculate the number of months = Divide the difference number of days by 30 to work out the months. - If the number of months is zero, use the company field **Stock Re-order**. - Calculate the total sales for the number of months using the month in the **From Date** as the starting month. - If **promotions are excluded,** the product invoice lines with promotions for the branch are removed from the current sales total. - If **purchase promotions are excluded, **the product purchase lines with promotions for the branch are removed from the available quantity and the purchase order quantity. - Daily Average sales are calculated by dividing the total sales by the number of days. If the **To Date** is the current system date, then the total sales are divided by the number of months less 1, multiplied by 30, plus the number of days into the current month. - Lead time will be from the stock data for the **branch lead time,** or **stock requisition default lead time** if a CDF order will also add in any **CDF lead time**. - The reorder quantity is the lead time multiplied by the average daily sales, less the available quantity, plus the **branch product minimum stock quantity**. - Where the reorder quantity is less than the **branch minimum,** then the reorder is changed to the **branch minimum** less the available quantity. - Where the available quantity is negative, the **branch minimum** is used plus the available quantity. - The reorder quantity is increased to be a pack quantity size. - The reorder quantity is then rounded down to the **maximum quantity** if the branch has a maximum level and the reorder quantity plus the available quantity is greater than the **maximum quantity** by subtracting the available quantity from the **maximum quantity**. - Where this value is negative the reorder value becomes 0. > Macro (anchor) # Then Stock Re-Ordering Algorithm - CPF Stock requirements for system-generated stock re-ordering are calculated using the following formula: **Stock on Hand + Outstanding Purchase Orders - Outstanding Customer Orders = Quantity Available** - For the purpose of calculating Quantity Available above, Factory Works Orders and requisitions are treated like Outstanding Purchase Orders. Factory Works Orders Allocated and Deferred Sales are treated like Outstanding Customer Orders. **Average Daily Sales (Promotions can be excluded) x Lead Time in Days - Quantity Available + Minimum Stock = Suggested Order Quantity** - If the Suggested Order Quantity is then less than the Minimum Stock Quantity then it is rounded up to the Minimum Stock Quantity. - If the Suggested Order Quantity then exceeds the Maximum Stock, then it is rounded down to or below the Maximum Stock. - If the Suggested Order Quantity is then rounded up to the relevant increment of the Supplier Pack Size if tailoring flag InvCPFPack is 'yes'. - Once the re-order quantity has been calculated, it is then checked against the max stock value set at the branch product level. If the calculated re-order quantity + stock available is greater than the max then the re-order value is re-calculated as quantity max - quantity available.

For more information about the Run-Time Options, click here

**From Period: ** Period to be considered for calculation of Daily Sales Average. --- **To Period: ** Flexible - can be any historical period range. Could be last 3 months. Could be next 3 months from last year. --- **Below Minimum** ** = 'no' ** Consider all products in specified range for re-ordering. This should be the normal and default setting. --- **Below Minimum ** **= 'yes'** Consider only those products in specified range where Quantity Available < Minimum Stock. --- **Non-stock Items ** **= 'no'** Self-explanatory. 'Yes' would include non-stock products in run. --- **Collective Items** **= 'no'** Default is 'no'. 'Yes' would include collectively ordered products in report. --- **Exclude Promos** ** = 'yes'** Exclude Promotional Sales from calculation of Daily Sales Average. --- **Preferred Only ** **= 'yes'** Only include products for which the specified supplier is the preferred supplier. If run with 'no' - also include products for which the specified supplier is an alternate supplier. --- **Zero Orders** **= 'yes' **Include products where Suggested Order Quantity = 0. (Stock Re-Order Report only). --- **Branch ** Self-Explanatory. --- **Stock Source** W = Warehouse, D = Direct, B = Both. Default is B. Include products where 'stock source' of product/supplier table agrees. Ignore product/supplier 'stock source' setting if 'B' specified. ---

# Stock Re-Order Matrix This describes the setup and background operation of the Stock Reorder Algorithm using the Stock Reorder Matrix. In order to use this function instead of the existing stock reorder algorithm, the '**InvStkMtx**' flag must be on. This determines 'Use stock re-order matrix when ordering stock'. # Stock Ordering Overview *Note*: This example is based upon the Stock reorder number of months being 3 months. This is keyed into the company setup - A six-month sale figure is determined from the sales of the item over the past three months added to the projected sales over the next three months (taken from the corresponding period the previous year, which allows for factors such as seasonal variance to be factored in). - The six-month sale figure is divided by twenty-six (26) weeks to obtain the mean weekly sales rate. > ℹ️ **Note***:* These breakdowns of the percentage ratings are only an example, the user can designate whatever percentages they want. Based on the sales performance over the defined six-month period, the stock lines are assigned 'stock rankings' as follows: - Lines corresponding to the top twenty (20) percent of sales by department and by quantity (units). - Lines corresponding to the intermediate sixty (60) percent of sales by department and by quantity (units). - Lines corresponding to the bottom twenty (20) percent of sales by department and by quantity (units). # Matrix Stock Performance Categories These percentages are set up as follows: **'System Administration'**, then** 'Configuration'**, then** 'Miscellaneous Tables'**, then** 'Matrix Stock Performance Categories' ** The table has the following fields: - Code - Description - Ranking % # Re-order Cycle codes These also need to be set up as follows. **'System Administration'**, then** 'Configuration'**, then **'Miscellaneous Tables'**, then **'Re-Order Cycle Codes'** The table has the following fields: - Code - Description - Days - Batch Day These cycle codes must then be set up against each product using **'Product Maintenance'**. Based on the frequency with which stock lines are ordered from a particular supplier, the stock lines are assigned an 'order cycle' as follows: - 1: Items ordered on a weekly basis. - 2-3: Items ordered according to a two-week cycle; '2' denoting the first week and '3' the second week of the cycle. - 4-6: Items ordered according to a three-week cycle; '4' denoting the first week, '5' the intermediate week and '6' the final week of the cycle. # Reorder Matrix Codes These must also be set up as follows. **'System Administration'**, then** 'Configuration'**, then **'Miscellaneous Tables'**, then **'Re-Order Matrix Codes'** The table has the following fields: - Code - Description - Days These are determined by combining the Matrix Stock Performance Categories with the Re-order Cycle Codes to form the Reorder Matrix Code. *Note:* This is set up by the ideal number of days. The ideal number of days stock to retain within the store is determined according to the stock ranking and the order cycle, as set out in the following matrix: 1------- 2------- 3------- 4------- 5------- 6------- A:42 63 63 70 70 70 B:28 42 42 49 49 49 C:21 35 35 49 49 49 For example, a line with a stock ranking of ‘B’ and an order cycle of ‘4’ would be ideally stocked for 49 days (seven weeks), as taken from the corresponding entry in the week holding matrix. - A 'model stock' figure for the item is determined by multiplying the mean daily sales figure by the ideal number of days stock to retain within the store. - On performing a check of the stock balances, if the stock in balance is less than the model stock figure, the number of items required to bring the balance up to the model stock is determined and the purchase order generated accordingly. > ✅ The stock re-order option to use to generate suggested purchase orders is via Stock Re-Ordering. Refer to [Modifying a Stock Re-Order](https://sterlandsupport.atlassian.net/wiki/spaces/PROSTIXV48DOC/pages/31099940).