Automatic Stock Order Calculations

Overview

This article covers the setup and logic used in Softbase's Automatic Stock Order feature.  The Automatic Stock Order feature allows users to set up parts and identify days of stocking levels to be used to automatically calculate how much to order of the part based on the demand history of the part.  

Setup

Set up the Minimum Days of Supply, Maximum Days of Supply and Months to Average Over on the Auto Order Tab per Parts Group.

These fields can be found by going to Parts -> Setup Toolbar Button -> Group Tab -> Auto Order Tab

Setup the Part Record as Automatic Order on the General Tab on the Part Record.  

The Status has to be Current for the Auto Stock feature to work if the Part's Absolute Minimum is not greater than 0.  

If the Part has an Absolute Minimum set greater than 0 on the Pricing/Qty Tab on the Parts Window then the Status of the Part can be Current, Phase In or New for the Auto Stock feature to work.   

Calculation

When the user chooses the Generate Button in the Auto Stock Tab of the Purchase Order  the following will take place

• A check will be made to ensure you have an Open PO selected and if you don't then it will notify you that you need one to continue.  

• All parts in the Selected Warehouses and Parts Groups will be gathered that could be considered for Auto Order. 

  • In order to be considered for the Auto Order Program the following criteria must be met. 

    • The Part must be set up with the Automatic Order Checkbox set to True on the General Tab of the Part Record.

    • The Part must be setup with a Status of Current, Phase In or New on the General Tab of the Part Record

      • If the Part is set to a Status of Phase In or New then it must also have an Absolute Minimum greater than 0,which is set on the Pricing/Qty Tab of the Part Record, to be considered.  

• Each Part that is considered for Auto Stock will be reviewed by the system to determine if it needs to be ordered or not.  

  • The system will look at the Parts Group of the Part and look at the Minimum Days of Supply, Maximum Days of Supply and Months to Average Over value for the Part Group, which can be found on the Auto Order Tab of the Groups Setup.  

  • It will calculate the Total Demand of the Part by looking at the Months to Average Over of the part and gathering the demand of the part for the Current Month and the amount of previous months that matches the Months to Average Over Value.  The Current Months Demand will only be included if the day the Auto Stock was run on was greater than the 9th day of the month.  

    • If the Months to Average Value was 3 and the Current Month is June and day is the 8th, it would take demand from  May, April and March to determine the Total Demand.  

    • If the Months to Average Value was 3 and the Current Month is June and day is the 21st, it would take demand from June, May, April and March to determine the Total Demand.  

  • After it gets the Total Demand it will calculate a Monthly Average by taking the Total Demand and dividing by the Months to Average Over value.  

    • If the day of the month the Auto Stock is being ran for is the 10th or greater the calculation for the Monthly Average would be the Total Demand divided by the calculation of the Month to Average Over value plus the calculation of Current Day of the Month divided by 30

  • After it gets the Monthly Average  it would calculate the Minimum Quantity and Maximum Quantity by taking the Total Demand of the Part and multiplying the calculations of the (Minimum Days of Supply / 30) multiplied by the Monthly Average and (Maximum Days of Supply / 30) multiplied by the Monthly Average.  

  • Once it has the Minimum Days of Supply and Maximum Days of Supply it will look at the Quantity on Hand of the Part being considered to order and add any quantity of that part on existing Stock Orders and see if that amount is less than the Minimum and Maximum Days of Supply.  If it is, the suggested amount to order should be the amount that takes it up to the Maximum Days of Supply. 

    • If the Absolute Minimum Quantity field is set on the Part then if the Quantity On Hand plus the Quantity on existing Purchase Orders is less than the Absolute Minimum Quantity then it will order up to that quantity, not the Maximum Days of Supply.  

    • If the Ordering Block Qty is set on the Part then it will order that Quantity and not the quantity based on Minimum and Maximum Days of Supply.

    • If the Full Bin Quantity is set it will order up to that Quantity but no more than that quantity, even if the suggested quantity based on Minimum and Maximum Days of Supply is greater than the Full Bin Quantity. 

    • The system will also take into account any quantity for stock that is on an open purchase order versus the suggested quantity based on Minimum and Maximum Days of Supply.

Example

Current Month is June of 2023, the day is the 8th and the Part is set up with the Automatic Order set to True and the Status set to Current.  

Part's Current Quantity On Hand is 10

Part Group of the Part has the Months to Average Over set to 3, Minimum Days of Supply set to 30 and Maximum Days of Supply set to 45

Part had a Total Demand of; June 2023 - 6, May 2023 - 12, April 2023 - 14, March 2023 - 10

Total Demand = 36 --- (Since the day of the month is not greater than the 9th day Total Demand is figured by using demand from May, April and March).  

Average Monthly Demand = 12 ---- (36 Total Demand / 3 Months to Average Over)

Minimum Quantity = 12 ---- ( 30 Minimum Days of Supply / 30 Days) * 12 Average Monthly Demand

Maximum Quantity = 18 ---- (45 Maximum Days of Supply / 30 Days) * 12 Average Monthly Demand

Generating an Auto Stock Order for this Part Group it will add this Part with a Quantity of 8 to the Purchase Order.