1. Home
  2. /
  3. User Guides
  4. /
  5. Inventory Control
  6. /
  7. Inventory control formulas

Inventory control formulas

Related topics

This document describes the formulas that are used in the inventory calculations. See About working with inventory calculations for more information.

The following sections are included:

Forecast components

Note that all references to forecast fields in this chapter relate to the base and level forecast. The base forecast is the value that is calculated according to the formulas in this document. After that calculation there are other forecast components, customer forecast and dependent forecast (if DC1 Manufacturing is active) added to the base forecast to give the actual forecast. See About working with forecasts for more information regarding the different forecast components.

Forecast formulas – period forecast calculation

When calculating period forecast it is important to know which forecast period certain values belong to. Therefore, the period index (i) is included in the formulas where (i) represents an active forecast period.

The period forecast for period (i), is based on demand and forecast data from previous periods, (i-1), (i-2), (i-3)….. depending on which method is used to calculate forecast in the system.

This is true for all forecast periods starting with the oldest one up to the present forecast period.

For future forecast periods (i+1), (i+2), (i+3)….. the period forecast is based on the period forecast for the present period (i), plus any adjustments (for example, season workday).

Note: The period forecast calculation is performed in the beginning of forecast period (i), calculating a forecasted demand for period (i). The forecast period ID that (i) represents is the start forecast period entered on the start screen for the Inventory calculation program. That means that “i” can be any forecast period, from the oldest one up to the present one.

In the period forecast calculation the mathematical expressions listed below are used. When applicable, the name of the file where the resulting values originate is listed.

Expressions used

Expression Description Origin
D(i) Demand in period (i) Forecast file
DA(i) Demand adjustment for period (i) Forecast file
ND(i) Net demand for period (i) Calculated value
F(i) Forecast for period (i) Forecast file + calculated value
L(i) Forecast level for period (i) Forecast file + calculated value
T(i) Trend level for period (i) Forecast file + calculated value
a(i) Alpha value Forecast control code table
b(i) Level factor b(i)=1-(1-a(i))exp2
c(i) Trend factor c(i)=(a(i))exp2 / b(i)
n Number of periods MA Forecast control code table
PRC(i) Sales promotion actual increase % period (i) Sales promotion table
PQT(i) Sales promotion actual quantity increase period (i) Sales promotion table
SP(i) Sales promotion index period (i) Sales promotion table
W(i) Workday index for period (i) Active forecast period file
SI(i) Season index for period (i) Season profile table
WE(i) Seasonal weight period (i) Season profile table
AW Average period weight Season profile table
UL(i) Upper limit for demand (when auto adjustment activated) Calculated value
LL(i) Lower limit for demand (when auto adjustment activated) Calculated value
Adj % Adjust percentage IC control file
Mrk % Markup/Markdown percentage Forecast control code table
N(i) Real number of working days in period (i) Active forecast file
NRM Normal number of workdays/period IC control file
NP Number of forecast periods/year IC control file
i Present forecast period at calculation time (CCYYPP)
i-1 First historical forecast period found with non-zero season index
y Forecast period ID for period one year before period (i)

Forecast calculations made on historical demand

Exponential smoothing

Adjustment & normalisation of historical forecast period values
Automatic demand adjustment
If Auto adjust demand in the IC control file is set to YES:

  • UL(i-1) = F(i-1) * (100 + Adj %) / 100}
  • LL(i-1) = F(i-1) * (100 – Adj %) / 100
  • DA(i-1) = UL(i-1) – D(i-1) if D(i-1) > UL(i-1)
  • DA(i-1) = LL(i-1) – D(i-1) if D(i-1) < LL(i-1)
Manual demand adjustment
If Auto adjust demand in the IC control file is set to NO:

  • DA(i-1) = Demand adjustment from the Forecast file for period (i-1)
Net demand
  • ND(i-1) = D(i-1) + DA(i-1)
Sales promotion index
If a sales promotion record does not exist for period (i-1)/(i) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(i-1) = 1
  • SP(i) = 1
If a sales promotion record exists in percentage for period (i-1)/(i) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(i-1) = (100 + PRC(i-1))/100
  • SP(i) = (100 + PRC(i))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(i-1) = 1
  • SI(i) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(i-1) = WE(i-1) / AW
  • SI(i) = WE(i) / AW

where AW is the average period weight for the season profile.
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(i-1) = 1
  • WI(i) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(i-1) = N(i-1) / NRM
  • WI(i) = N(i) / NRM
Demand values from the previous period are normalised
  • ND'(i-1) = ND(i-1) / (WI(i-1) * SI(i-1) * SP(i-1))
If sales promotion in quantities:

  • ND(i-1) = ND(i-1) – PQT(i-1)
Calculation of new forecast
Normal exponential smoothing (no trend)
  • F'(i) = (1-a(i-1)) * L'(i-1) + a(i-1) * ND'(i-1)
Trend exponential smoothing
  • c(i)=(a(i))exp2 / b(i)
  • F'(i) = L(i) + T(i)
  • L(i) = (1-b(i-1)) * L'(i-1) + b(i-1) * ND'(i-1)
  • T(i) = (1-c(i-1)) * T(i-1) + c(i-1) * (L(i) – L(i-1))
Reapply season and workday adjustments on new forecast:

  • F(i) = F'(i) * SI(i) * WI(i) * SP(i)
If sales promotion is expressed in quantities, then the formula will be:

  • F(i) = F'(i) + PQT(i)

Moving average

Adjustment & normalisation of historical forecast period values
Moving average reads (n) number of periods backwards in the Forecast file. The first period read is the one preceding the present forecast period.

For all historical periods, the demand is adjusted and normalised. The following adjustments are performed for all records read where the period ID is (x):
Automatic demand adjustment
If Auto adjust demand in the IC control file is set to YES:

  • UL(x) = F(x) * (100 + Adj %) / 100
  • LL(x) = F(x) * (100 – Adj %) / 100
  • DA(x) = UL(x) – D(x) if D(x) > UL(x)
  • DA(x) = LL(x) – D(x) if D(x) < LL(x)
Manual demand adjustment
If Auto adjust demand in the IC control file is set to NO:

  • DA(x) = Demand adjustment from the Forecast file for period (x)
Net demand is calculated
  • ND(x) = D(x) + DA(x)
Sales promotion index
If a sales promotion record does not exist for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • P(x) = 1
If a sales promotion record exists in percentage for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = (100 + PRC(x))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index is calculated
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(x) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(x) = WE(x) / AW
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(x) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(x) = N(x) / NRM
Demand values from all the previous periods are normalised
  • ND'(x)=ND(x) / (WI(x) * SI(x) * SP(x))
If sales promotion in quantities:

  • ND'(x) = ND(x) ‘ PQT(x)
So, the above adjustments are done for all read forecast periods.
Calculation of new forecast
  • F'(i) = ( ND'(i-1) + ND'(i-2)…+ ND'(i-n) ) / n
Reapply adjustments on new forecast:
  • F(i) = F'(i) * SI(i) * WI(i) * SP(x)
If sales promotion is expressed in quantities, then the formula will be:

  • F(i) = F'(i) + PQT(i)

Forecast copy

DA(y) is from the Forecast file for period (y).
Net demand is calculated:
  • ND(y) = D(y) + DA(y)
  • F(i) = ND(y) * (100 + Mrk %)/100

Forecast calculation for future periods

The calculations described so far are period forecast calculations based on historical demand data. These calculations cover historical periods up to and including the present forecast period.

The period forecast calculations for future forecast periods are calculated differently, by using the present period forecast as a base. The calculations are described in this section.

Normalisation

Normalisation of present period’s forecast for period (i)
Sales promotion index
If a sales promotion record does not exist for period (i) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(i) = 1
If a sales promotion record exists in percentage for period (i) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(i) = (100 + PRC(i))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index is calculated
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(i) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(i) = WE(i) / AW
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(i) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(i) = N(i)/NRM
Normalise starting forecast
  • F'(i) = F(i) / (WI(i) * SI(i) * SP(i))
If sales promotion is expressed in quantities, then the formula will be:

  • F(i) = F'(i) + PQT(i)

Exponential smoothing

For all future forecast periods, (x) is the period ID going from (i+1) to (i+(NP-1)) the period forecast is calculated.

If the Trend code field on the Inventory management panel in the Item/Warehouse file is set to NO:

Sales promotion index
If a sales promotion record does not exist for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = 1
If a sales promotion record exists in percentage for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = (100 + PRC(x))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index is calculated
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(x) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(x) = WE(x) / AW
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(x) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(x) = N(x)/NRM
Reapply adjustments to get new forecast for future period (x)
  • F(x) = F'(i) * SI(x) * WI(x) * SP(x)
If sales promotion is expressed in quantities, then the formula will be:

  • F(x) = F'(i) + PQT(x)

If the Trend code field on the Inventory management panel in the Item/Warehouse file is set to YES:

First calculate normalised trend forecast:

  • F'(x) = L(i) + (j+1) * T(i)

where (j) is the sequence number of periods read into the future, so that the first period after the present is j = 1. Thereafter apply adjustments on normalised forecast.
Sales promotion index
If a sales promotion record does not exist for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = 1
If a sales promotion record exists in percentage for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = (100 + PRC(x))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index is calculated
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(x) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(x) = WE(x) / AW
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(x) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(x) = N(x)/NRM
Reapply adjustments to get new forecast for future period (x)
  • F(x) = F'(x) * SI(x) * WI(x) * SP(x)
If sales promotion is expressed in quantities, then the formula will be:

  • F(x) = F'(i) + PQT(i)

Moving average

Start by calculating the adjustment indices for period (x):
Sales promotion index
If a sales promotion record does not exist for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = 1
If a sales promotion record exists in percentage for period (x) for the key that the item belongs to (with or without warehouse) in the Sales promotion table:

  • SP(x) = (100 + PRC(x))/100
If sales promotion is expressed in quantity, then the value PQT is directly used in formulas.
Season index
If Season profile on the Inventory management panel in the Item/Warehouse file is blank:

  • SI(x) = 1
If Season profile on the Inventory management panel in the Item/Warehouse file is completed:

  • SI(x) = WE(x) / AW
Workday index
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to NO:

  • WI(x) = 1
If Adjust workdays on the Inventory management panel in the Item/Warehouse file is set to YES:

  • WI(x) = N(x)/NRM
Reapply adjustments on new forecast
  • F(x) = F'(i) * SI(x) * WI(x) * SP(x)
If sales promotion is expressed in quantities, then the formula will be:

  • F(x) = F'(i) + PQT(i)

Forecast copy

DA(z) is from the Forecast file for period (z) where (z) is the period ID of one year before (x), as (x) goes from (i+1) to (i+NP-1).
Net demand
  • ND(z) = D(z) + DA(z)
  • F(x) = ND(z) * (100 + Mrk %)/100

Accumulated year forecast

The future period forecasts have been calculated as described above, F(x) for (x) going from (i+1) to the ‘most future’ period, (i+NP-1) where (i) is the period ID for the present period.

These are updated in the Forecast file for the corresponding forecast period. At the same time they are accumulated, starting with F(i) + F(i+1) + F(i+2)…. + F(i+NP-1).

This sum is the year forecast, the sum of all future period forecasts, including the present one. This value is updated on the Inventory management panel in the Item/Warehouse file.

Forecast error calculation

The following forecast error values are calculated:

  • Mean absolute deviation (MAD)
  • Mean relative deviation (MRD)
  • Tracking signal
  • Forecast deviation

Expressions used:

Expression Description Origin
MA(i) MAD in period (i) Forecast file + Calculated value
MR(i) MRD in period (i) Forecast file + Calculated value
D(i) Demand in period (i) Forecast file
DA(i) Demand adjustment for period (i) Forecast file
ND(i) Net demand for period (i) Calculated value
F(i) Forecast for period (i) Forecast file + Calculated vale

MAD, MRD & Tracking signal calculation

Adjustment & normalisation of historical values

Demand adjustment is made on the demand value for the previous period.
If Auto adjust demand in the IC control file is set to YES:

  • UL(i-1) = F(i-1) * (100 + Adj %)/100
  • LL(i-1) = F(i-1) * (100 – Adj %)/100
  • DA(i-1) = UL(i-1) – D(i-1) if D(i-1) > UL(i-1)
  • DA(i-1) = LL(i-1) – D(i-1) if D(i-1) < LL(i-1)
If Auto adjust demand in the IC control file is set to NO:

  • DA(i-1) = Demand adjustment from the Forecast file for period ‘i-1’
Net demand is calculated
  • ND(i-1) = D(i-1) + DA(i-1)
  • MR(i) = (1-0.2) * MR(i-1) + 0.2 * (ND(i-1) – F(i-1))
  • MA(i) = (1-0.2) * MA(i-1) + 0.2 * ABS(ND(i-1) – F(i-1))

Note that alpha = 0.2 is always used.
Tracking signal
  • TR(i) = (MR(i)/MA(i))

Tracking error is signalled on the Inventory calculation error report if the absolute value of the calculated tracking signal is greater than the tracking limit set in the Forecast control code table and the tracking limit is greater than zero.
Forecast deviation
  • (F(i) – F(i-1) / F(i-1)) * 100

Forecast deviation error is signalled on the Inventory calculation error report if the calculated forecast deviation is greater than the forecast deviation limit set in the Forecast control code table and the forecast deviation limit is greater than zero.

Replenishment formulas

The EOQ is stored on the Inventory management panel in the Item/Warehouse file per item/warehouse record.

Expressions used:

Expression Description Origin
K Ordering cost Replenishment control code table
r Stock interest Replenishment control code table
C Cost price/unit Item file
D Annual forecast Item file/Inventory management
NP No of forecast periods/year IC control file
NRM Average number of workdays/period IC control file
BRT Basic replenish time for item/wh Calculated value
n No of months demand Replenishment control code table
nl No of months demand for lower limit Replenishment control code table
nu No of months demand for upper limit Replenishment control code table
LL Lower limit for EOQ  
Ul Upper limit for EOQ  

Calculations

Wilson method
  • EOQ = SQRT[[2 * K * D]/ (C * r)]

Note that ‘r’ is used as a decimal number, 25% stock interest means 0.25 is used in the formula.

The calculated EOQ is checked against upper and lower limit for EOQ, set as number of months demand.

  • LL = D/12 * nl
  • UL = D/12 * ul

If calculated EOQ is less than LL, then EOQ = LL
If calculated EOQ is greater than UL, then EOQ = UL
Period dependent method
EOQ is calculated from NC and using the forecast retrieval routine. The forecasted demand during NC no of days, starting at the first day outside the basic replenishment time (BRT). NC=Number of days to cover=(NP*NRM) / (D/(EOQ from Wilson))

Note: (D/(EOQ from Wilson)) represents the number of orders per year.
BRT is calculated as
Transportation time + lead time + administration time

The calculated EOQ is checked against upper and lower limit for EOQ, set as number of months demand.

  • LL = D/12 * nl
  • UL = D/12 * ul

If calculated EOQ is less than LL, then EOQ = LL
If calculated EOQ is greater than UL, then EOQ = UL
Demand control
  • EOQ = D/12 * n
Replenishment error calculation
EOQ deviation
  • EOQ dev = (EOQ(new) – EOQ(old) / EOQ(old)) * 100

EOQ deviation error is signalled on the Inventory calculation error report if the calculated EOQ deviation is greater than the EOQ deviation limit in the Replenishment control code table and the limit is greater than zero.

Service formulas

The ROP and SST are stored on the Inventory management panel in the Item/Warehouse file per item/warehouse record.

Expressions used:

Expression Description Origin
EOQ Economic Order Quantity Item/Warehouse file, Inventory management
SVL Service level Service control code table
MAD Mean absolute deviation Forecast file
RT Total replenishment time Calculated value
NRM Average number of workdays/period IC control file
pr Percentage of DL Service control code table
pl Percentage of DL Service control code table
pu Percentage of DL Service control code table
LL Lower limit for SST Calculated value
UL Upper limit for SST Calculated value
k Safety factor Statistical probability table

Service based

Total replenishment time (RT)
Transportation time + lead time + administration time + replenishment interval (purchase type 2 only) + forecast period interval (purchase type 3 only).

The replenishment interval is retrieved from Work with periodic purchase & order targets for the item/warehouse records.

Forecast period interval, no of days per forecast period, is retrieved from the IC control file.
  • SST = k * 1.25 * MAD * SQRT(RT/NRM)

where 1.25 * MAD is the standard deviation of forecast errors over the forecast interval, and 1.25 * MAD * SQRT(RT) is the standard deviation of forecast errors over the planning time.
(k) is a statistical safety factor retrieved from a probability function table as the corresponding value of the probability function G(x). The formula for G(x) is:

  • G(x)=(EOQ / (1.25 * MAD * SQRT(RT/NRM))*((100 – SVL)/100)
DL is forecasted demand from today, for RT no of days. This is retrieved from the Forecast file via the forecast retrieval function.

The calculated SST is checked against upper and lower limit, set as a percentage of demand during replenishment time.

  • LL = pl/100 * DL
  • UL = pu/100 * DL

If calculated SST is less than LL, then SST = LL
If calculated SST is greater than LL, then SST = UL

  • ROP = DL + SST
Demand based
  • SST = pr/100 * DL
  • ROP = DL + SST

Service error calculation

ROP deviation
  • ROP deviation = (ROP(new) – ROP(old) / ROP(old)) * 100

ROP deviation error is signalled on the Inventory calculation error report if the calculated ROP deviation is greater than ROP deviation limit in the Service control code table and the limit is greater than zero.

SST deviation

  • SST deviation = (SST(new) – SST(old) / SST(old)) * 100

SST deviation error is signalled on the Inventory calculation error report if the calculated SST deviation is greater than the SST deviation limit in the Service control code table and the limit is greater than zero.

Related topics