Calculation Process for Average Usage

In this article

Effective Inventory

Average Daily Sale

Stock Cover Days

Forward Sales Profile

Suggested Quantity

Cross Docking

Planned Sales Demand

Reorder Point

Maximum Inventory

For BOM and Component Replenishment Journals, refer to Calculation Process for BOM and Components for information about their calculation process.
Note: Cross Docking is not supported for BOM and Component Replenishment Journals.

Effective Inventory

The system calculates the inventory that is going to be used in the rest of the calculations.

Effective Inventory =

Inventory

+ Quantity on Purchase Order

- Quantity on Purchase Return Order

- Quantity on Sales Order

+ Quantity in Transfer In

- Quantity in Transfer Out

+ Quantity in Assembly Header

- Quantity in Assembly Line


Note: It is possible to set up which document types (Purchase Order, Sales Order, ...) should be considered in the calculation. This can be done in the Replen. Setup page, in the Effective Inventory FastTab.

Average Daily Sale

The calculation of the Average Daily Sale process is executed in the calculation of the Replenishment Item Quantity record. The calculation is based on the defined Sales Profile for the Item, Variant and Location combination.

The calculation process below is applied to every Sales Profile Line:

Quantity

  • Calculates the sum of the sold quantity
    (CALCSUMS(Quantity)) from the Item Ledger Entries table according to the Date Period.

Correction Quantity

  • Calculates the sum of the correction quantity as defined in Sales History Adjustments.
    (CALCSUMS("Corrected Quantity")) from the Sales History Adjustments table according to the Date Period.

Quantity correction

  • Corrects the Quantity with Correction Quantity.
    (Quantity = Quantity - Corrected Quantity)


Tip: The Quantity field is a negative number. To lower the sale the Corrected Quantity value must be negative.

Other fields in the Replenishment Item Quantity record:

Adjusted Sales = Corrected Quantity

  • If the field Stock Out Functionality is set, the system calculates the number of Out of Stock Days for the Date Period
  • The system calculates the Average Daily Sales as follows:
    Average Daily Sale = -Quantity / (No of Days – No of Out of Stock Days)

No. of Days Out of Stock = No of Out of Stock Days

No. of Sales Dates = No of Days with sales

Average Daily Sale

  • Calculates the Average Daily Sale according to the Sales Profile Line Weight
  • Average Daily Sale = Average Daily Sales * Weight / Total Weight
    - or -
  • Average Daily Sale = Average Daily Sales * 1 / No of Sales Profile Lines

See also: Replenishment Sales Profile, Sales History Adjustment, Replenishment Out of Stock Days.

Stock Cover Days

For items with Stock Coverage Days Profile

If the Stock Coverage Days Profile is used, the Stock Cover Days are dynamically calculated based on the next replenishment calculation day and the parameters as defined in the Replen. Coverage Days Profile. The Warehouse Stock Cover Days and Store Stock Cover Days are calculated as follows:

Warehouse Stock Cover Days = No. of Days until Next Calculation Day + Inbound Whse. Handling Time + Coverage Buffer Days - Store Closing Days

Store Stock Cover Days = No. of Days until Next Calculation Day + Inbound Store Handling Time + Coverage Buffer Days - Store Closing Days

If the item is having Enable Lead Time Calculation activated, the Inbound Location Handling Time is added on top of the Lead Time instead, that is, the Sourcing Location Lead Time for transfer and Vendor Lead Time for purchase replenishment. This makes up the Lead Time Cover Days. For purchase replenishment with cross docking, the Lead Time covers the total time from which the stocks are shipped by the vendor to the warehouse, and then transferred from the warehouse to the store.

The Stock Coverage Period is shifted to start a day after the stock arrives, that is, after the Lead Time Coverage Period.

For Purchase to Warehouse:

Lead Time Cover Days = Vendor Lead Time + Inbound Whse. Handling Time

For Purchase to Store:

Lead Time Cover Days = Vendor Lead Time + Inbound Store Handling Time

For Transfer to Store:

Lead Time Cover Days = Sourcing Location Lead Time + Inbound Store Handling Time

For Purchase to Warehouse with Cross Docking:

Lead Time Cover Days = Vendor Lead Time + Inbound Whse. Cross Docking Handling Time + Sourcing Location Lead Time + Inbound Store Handling Time

For example, in a Purchase to Store scenario, a store is open from Monday to Saturday and has been set up to have 1 day of inbound location handling time and 2 coverage buffer days. The replenishment calculation is set up to be run every Monday and Wednesday. The selected vendor needs 2 days of lead time to deliver the items.

Replenishment calculation on Monday (01.06.2020) :

Lead Time = Vendor Lead Time + Inbound Store Handling Time

= 2 + 1

= 3 days

 

The stock will arrive 3 days later after 01.06.2020 which is on 04.06.2020.

 

Store Stock Coverage Period (Days) = No. of days until next calculation day + Store Coverage Buffer Days

= 2 (Tuesday until Wednesday) + 2

= 4 days

 

The Store Stock Coverage Period will start a day after the stock arrived and ends 4 days later, which is from 05-06-2020 until 08-06-2020 (including the Store Closing Days).

 

Store Stock Coverage Days = Store Stock Coverage Period (Days) - Store Closing Days

= 4 – 1

= 3 days

Replenishment calculation on Wednesday (03.06.2020):

Lead Time = Vendor Lead Time + Inbound Store Handling Time

= 2 + 1

= 3 days

 

The stock will arrive 3 days later after 03.06.2020 which is on 06.06.2020.

 

Store Stock Coverage Period (Days) = No. of days until next calculation day + Store Coverage Buffer Days

= 5 (Thursday until Monday) + 2

= 7 days

The Store Stock Coverage Period will start a day after the stock arrived and ends 7 days later, which is from 07.06.2020 until 13.06.2020 (including the Store Closing Days).

 

Store Stock Coverage Days = Store Stock Coverage Period (Days) - Store Closing Days

= 7 – 1

= 6 days

For items without Stock Coverage Days Profile

If the Stock Coverage Days Profile is not used, the system will use the Wareh Stock Cover Reqd (Days) field to replenish a warehouse and the Store Stock Cover Reqd (Days) field to replenish a store.

Stock Cover Days = Store Stock Cover Reqd (Days)

-or-

Stock Cover Days = Wareh Stock Cover Reqd (Days)

With Enable Lead Time Calculation activated for the item, the Stock Coverage Period is shifted to start a day after the stock arrives, that is, after the Sourcing Location Lead Time for transfer and Vendor Lead Time for purchase replenishment, which makes up the Lead Time Cover Days. For purchase replenishment with cross docking, the Lead Time covers the total time from which the stocks are shipped by the vendor to the warehouse, and then transferred from the warehouse to the store.

For Purchase:

Lead Time Cover Days = Vendor Lead Time

For Transfer:

Lead Time Cover Days = Sourcing Location Lead Time

For Purchase with Cross Docking:

Lead Time Cover Days = Vendor Lead Time + Sourcing Location Lead Time

Note: When replenishing a warehouse, the system tries to use the Wareh Coverage Days Profile, or the Wareh Stock Cover Reqd (Days) field, if the profile for the warehouse is not defined. If both of them are empty, the system will look for the Store Coverage Days Profile, or finally, the Store Stock Cover Reqd (Days) field, if the profile for the store is not defined.

See also: Replen. Coverage Days Profile, Replenishment Batch Calculation Calendar, Lead Time Calculation

Forward Sales Profile

The system uses the Warehouse Forward Sales Profile if the Replenishment Journal is to replenish the warehouse. Whereas if the Replenishment Journal is to replenish the stores, or if it is working with transfer or purchase with cross docking, the system uses the Store Forward Sales Profile. You must fill in the corresponding field for the system to calculate the Forward Sales Ratio.

The system calculates the pre and post date periods specified in the Forward Sales Profile according to the system date (WORKDATE).

If the Use Replenishment Calendar field in the Forward Sales Profile is set, the system will substitute the dates if they are found in the Replenishment Calendar with the days not found in the calendar subtracted by one year (-1Y).

The system sums up the quantity (CALCSUMS("Valued Quantity")) for the periods with filters specified in the Forward Sales Profile record (Division, Item Category, Retail Product Group, and/or Location).

The Forward Sales Forecast Factor is found in the following way:

Forward Sales Forecast Factor:= Total Post Period Sale / Total Pre Period Sale

See Also: Forward Sales Profile (Store/Warehouse), Replenishment Calendar.

Suggested Quantity

The system can suggest the quantity that the warehouse or the store needs according to the following formula:

System Suggested Quantity = Average Daily Sales * Required Stock Cover Days * Forward Sales Forecast Factor - Effective Inventory

 

Or, when Lead Time Calculation for the item is enabled:

System Suggested Quantity = Average Daily Sales * Required Stock Cover Days * Forward Sales Forecast Factor - Projected Effective Inventory

For items having Lead Time Calculation enabled, the System Suggested Quantity is calculated based on the Projected Effective Inventory, instead of the Effective Inventory. For more information, refer to the Projected Effective Inventory and System Suggested Quantity sections under Lead Time Calculation.

If the System Suggested Quantity is less than zero, the System Suggested Quantity is set to zero.

If the Effective Inventory or Projected Effective Inventory is greater or equal to the System Suggested Quantity, the System Suggested Quantity is set to zero.

Note: If there is a Planned Sales Demand within the required stock cover period, the System Suggested Quantity is adjusted to reflect the Planned Sales Demand.

Cross Docking

If the Replenishment Journal is to replenish the warehouse with cross docking to the stores, the system suggests the quantity needed according to how much should be cross-docked to the stores.

The Replenishment Journal calculates the to Cross Dock for the store using the Store Stock Cover Days, and the System Suggested Quantity is then set to the Quantity to Cross Dock.

Any available effective inventory in the warehouse will be deducted from the cross docking demand. If you wish to ignore the warehouse effective inventory and order the full demand quantity, you can enable the option by using the Warehouse Effective Inv. Handling field in the Replen. Setup.

For items having Lead Time Calculation enabled, the Quantity to Cross Dock is calculated based on the Projected Effective Inventory, instead of the Effective Inventory. For more information, refer to the Projected Effective Inventory section under Lead Time Calculation.

Quantity to Cross Dock = Average Daily Sales * Store Stock Cover Days * Forward Sales Forecast Factor - Effective Inventory

 

Or, when Lead Time Calculation for the item is enabled:

Quantity to Cross Dock = Average Daily Sales * Store Stock Cover Days * Forward Sales Forecast Factor - Projected Effective Inventory

Example:

Condition:

  • The store has 3 days as Stock Coverage. The system calculates how much the store needs for 3 days.
  • Average Daily Sale = 10
  • Store Stock Cover Days = 3
  • Forward Sale Forecast Factor = 1,1
  • Effective Inventory = 5

Result:

  • Quantity to Cross Dock = (10 * 3 * 1,1) - 5 = 28
  • System Suggested Quantity = Quantity to Cross Dock = Quantity = 28
  • The Quantity in the Purchase Order for the warehouse is 28, and when the Purchase Order is received into the warehouse, the staff cross-dock (pick) the 28 to be delivered by a Transfer Order to the store.

Note: If there is a Planned Sales Demand within the store stock cover period, the Quantity to Cross Dock is adjusted to reflect the Planned Sales Demand.

Planned Sales Demand

If there are Planned Sales Demand records for the required stock cover period (warehouse or store), the system sums the quantities from the Planned Sales Demand records, and the System Suggested Quantity will be adjusted to include the Planned Sales Demand quantity.

Demand Quantity = Average Daily Sale * (Required Stock Cover Days – No. of Planned Sales Demand Days) + Total Planned Sales Demand Quantity

System Suggested Quantity = Demand Quantity * Forward Sales Forecast Factor - Effective Inventory

 

Or, when Lead Time Calculation for the item is enabled:

System Suggested Quantity = Demand Quantity * Forward Sales Forecast Factor - Projected Effective Inventory

Note: Quantity to Cross Dock will also be adjusted if there is a Planned Sales Demand within the store stock cover period.

See also: Planned Sales Demand.

Reorder Point

If the field Reorder Point is greater than zero and System Suggested Quantity is less than the Reorder Point, the System Suggested Quantity is set to Reorder Point and Decision is set to Brought up to Reorder Point.

Maximum Inventory

If the field Maximum Inventory is greater than zero and the sum of System Suggested Quantity and Effective Inventory does not equal Maximum Inventory, the System Suggested Quantity equals Effective Inventory subtracted from the Maximum Inventory. The field Decision is set to Brought to Maximum Inventory.

Condition:

  • System Suggested Quantity + Effective Inventory <> (not equal to) Maximum Inventory

Result:

  • System Suggested Quantity = Maximum Inventory – Effective Inventory.