Calculations for Usage-Based Purchase Order Builds

When using the override option, you are choosing to bypass the normal logic used for standard purchase order builds. Minimum and maximum quantities assigned to items will not be considered in this case and quantities will be determined based upon the item's usage history for a period you specify. All builds are processed from the Order Entry transaction in Purchasing using the Build (F7) function.

Selection, vendor match, and options panels function the same regardless of whether the build is being processed using overrides or not.

Use the Menu Marker icon near the "Options" panel to access override settings.

It's possible to adjust the days supply, usage period start date, growth factor, and "include existing orders" check box. Days supply and the start date are minimally required to use this option. Growth factor and the "include existing orders" are optional.

Days Supply

This integer (whole number) is used to calculate the usage period that will be used for the build calculation. The number of days designated is added to the "Start of Usage Interval" in order to determine the "end date" of the period. If any particular item was not established until after the start of the period, the usage period is set to the last 30 days by default.

Start of Usage Interval

This date determines the beginning date of the usage period. The start date defaults to one year prior to the current date but can be adjusted as needed. For example, if today is July 15, 2016, the start date will default to July 15, 2015. If we did not change this date (7/15/2015) and specified a 60-day usage period, the ending date would be set to September 13, 2015. Date formats can vary based upon your regional settings.

*The start date is always included as the first full day of the usage period being considered.

Usage information is maintained in the Stock Value table in the SQL database. Usage records the inventory quantities "used" over a period time. The term "used" often refers to quantities that were sold or returned as a result of Point of Sale activity. Usage also includes quantity changes resulting from transfers between branches as well as items used as materials for manufacturing, for example. The usage quantity for any period may be either positive or negative. Usage data is recorded for Year and Month (YYYYMM) periods.

For each month in the usage period being considered, we calculate the average daily usage for that month. The daily average usage is then multiplied times the number of days being considered for each month in the date range. You can view an item's usage history from Item Maintenance or Inquiry as well as from most application Folders (F4) that reference inventory.

Note: There is also an unrelated item setting named "usage interval" (Usage Int. on the Branch Codes tab of Item Maintenance). This item setting is not used for the override build.

Growth Factor

This optional setting indicates a percentage increase or decrease affecting quantities for the build calculation. Your company may be selling more or less of any particular product or group of products. For example, if you know that sales have increased 3% so far this year, you may want to specify a 3% growth factor so that the quantities you order reflect this trend. Growth factor is applied to the total quantity calculated for the period you specify.

✓ Include Existing Orders

If checked, this setting indicates that the build quantity should consider each item's current quantities when determining the quantity to order. If not checked, the item's on-hand and other quantities will not be considered.

For most items and when this option is selected, build quantities will be adjusted based upon the current on-hand as well as any quantities due to be received or due to be sold (from customer orders) in the near future. The "near future" are periods of time determined using each item's "lead time" and "days supply" settings. If the item has no days supply or lead times specified, a 7-day period is assumed. These are used to update an item's POQDue and COQDue quantities on a daily basis or whenever orders (customer and purchase orders) are added or modified. For manufactured items, the item's Committed and On-order quantities are used instead of POQDue and COQDue.

Calculation Example

For our example, we will be using the following settings and item usage...

On-hand: 51

POQDue (Purchase Order Quantity Due): 0

COQDue (Customer Order Quantity Due): 16

 Days Usage: 60 Days

 Start Date: 5/12/2015 (May 12, 2015)

 Growth Factor: 3.5%

The override build is determined as follows:

1. For each month in the period, take the total usage and number of days in the month to calculate the daily average for that month period.

2. Determine the number of days in each month to use. The starting month and ending month may represent less than a full month's usage, so the daily average is multiplied times the # of days being used from that period.

3. The sum of the monthly periods determines our order quantity needed for the period. If no growth factor was specified and the "include existing users" was not checked, this would be the order quantity (the figure might be rounded based upon the item settings).

If you specified a growth factor, the growth factor would be applied to either increase or decrease the order quantity...

4. The total usage calculated for the period is added to the calculated growth (either negative or positive):

 

BuildQty = UsageTotal + (UsageTotal x (GrowthFactor/100))

 

407.8562 = 394.064 + (394.064 x (3.5/1000))

5. If the item has Quantity Rounding enabled, we'd now round this figure to the nearest integer (408).

If you checked the "include existing orders" option, we also consider the item's current on-hand, POQDue, and COQDue quantities...

6. We calculate the total On-hand plus the Expected Quantity to be Received (POQDue) and subtract the COQDue (Quantity Required for Customer Orders due to be delivered). This total is subtracted from our build quantity.

BuildQty = BuildQty - (Onhand + POQDue - COQDue)

373 = 408 - (51 + 0 - 16)

If an item wasn't established prior to the starting period you choose, the most recent 30 days of usage will be considered instead. This option orders items regardless of any minimum or maximum settings. Lead Time and Days Supply can have an effect on the COQDue and POQDue figures IF used to calculate an order quantity. Vendor-item settings such as order multiples and break quantities are considered after the build quantity is calculated. Growth factor can be either a positive or negative decimal figure (negative would reduce order quantities, positive factors increase quantities). There is no need to enter the growth factor as a decimal; for example, entering .05 is 5/100 of 1%, not 5%.