Ask Lana
Help Center
How can we help? 👋
All Categories
Stock In Trade
Calculation Logic

Calculation Logic

View detailed formulas used to calculate Sales In, Sales Out, Closing Stock, and SIT across scenarios.

Formula - Actual

Section
Requirement
Source/Formula
Note
Editable
Actual Sales Out month n (Ac.S.Out n )
Displayed in Boxes
From Actual Sales Out upload
No
Displayed in Value
From Actual Sales Out upload
No
Actual Sales In month n (Ac.S.In n )
Displayed in Boxes
From Actual Sales In upload
No
Displayed in Value
From Actual Sales In upload
No
Actual Closing Stock month n (Ac.Cl.S n)
Displayed in Boxes
From Distributor Inventory upload
No
Displayed in Value
From Distributor Inventory upload
No
Actual SIT month n(Ac.SIT n)
Displayed in Days
Ac.Cl.S.Value.n /Sum (Ac.S.Out Box.n→x * Product.Value.n→x) * n→x*30
This calculation can be applied at any leveln→x is the number of months to consider and is specific to each workspaceAc.Cl.S.Value.n is the closing stock Value and can be expressed either considering listed price of Distributor GSVProduct.Value can be listed price or Distributor GSV depending on each workspace
No

Formula - Unconstrained

Section
Requirement
Source/Formula
Note
Editable
Future Sales Out month n (Fu.S.Out n)
Displayed in Boxes
From Scenario Sales Out forecast
No
Displayed in Value
Fu.S.Out box * Product Value
This calculation can only be applied at terminal level (distributor-Product)
No
Future Sales In month n (Fu.S.Inn)
Displayed in Boxes
Fu.S.InBox.n = Sum(Fu.S.OutBox.n→x) / n*30 * Fu.SITn - Fu.Cl.SBox.n-1 + Fu.S.OutBox.n or Fu.S.InBox.n = Fu.CLSBox.n - Ac.Cl.SBox.n-1 + Fu.S.OutBox.n
• This calculation can be applied at any level n→x is the number of months to consider and is specific to each workspace
Yes
In Value
Fu.S.InValue.n = Fu.S.In Box.n * Product Value
This calculation can only be applied at terminal level (distributor-Product) Product.Value can be listed price or Distributor GSV depending on each workspace
Yes
Future Closing Stock month n (Fu.CLSn)
Boxes
Fu.CLSBox.n = Ac.Cl.SBox.n-1 - Fu.S.OutBox.n + Fu.S.InBox.n or Fu.CLSBox.n = Sum(Fu.S.OutBox.n→x / n*30 * Fu.SITn
• This calculation can be applied at any level n→x is the number of months to consider and is specific to each workspace
Yes
Value
Fu.CLSGSV.n = Fu.CLSBox.n * Product Value
• This calculation can be applied at any level Product.Value can be listed price or Distributor GSV depending on each workspace
Yes
Future SIT month n (Fu.SITn)
Displayed in Days
From the Distributor SIT target file
This calculation can only be applied at terminal level (distributor-Product)
Yes
Fu.Cl.S.Value.n / Sum (Fu.S.Out Box.n→x * Product Value n→x) * n*30
This calculation can be applied at any level n→x is the number of months to consider and is specific to each workspace Fu.Cl.S.Value.n is the closing stock Value and can be expressed either considering listed price of Distributor GSV Product.Value can be listed price or Distributor GSV depending on each workspace
Yes

Formula - Constrained

Section
Requirement
Source/Formula
Note
Editable
Constrained Future Sales In month n (Con.Fu.S.Inn)
Displayed in Boxes
From Scenario DemandFullfilmentMetrics
No
Displayed in Gross Sales Value
Con.Fu.S.In box * Product Value
This calculation can only be applied at terminal level (distributor-Product)
No
Constrained Future Sales Out month n (Con.Fu.S.Out n)
Displayed in Gross Sales Value
Con.Fu.S.Outbox.n * Product Value
This calculation can only be applied at terminal level (distributor-Product)
No
Displayed in Boxes
If month n = Future Plan starting month: If Fu.S.Outbox.n < or = Con.Fu.S.Inbox.n + Ac.Cl.Sbox.n-1 Then Con.Fu.S.Out n = Fu.S.Outbox.n Else Con.Fu.S.Out n = Con.Fu.S.Inbox.n + Ac.Cl.Sbox.n-1
This calculation can only be applied at terminal level (distributor-Product)
No
If month n >= Future Plan starting month +1: If Fu.S.Outbox.n < or = Con.Fu.S.Inbox.n + Fu.Cl.Sbox.n-1 Then Con.Fu.S.Out n = Fu.S.Outbox.n Else Con.Fu.S.Out n = Con.Fu.S.Inbox.n + Fu.Cl.Sbox.n-1
This calculation can only be applied at terminal level (distributor-Product)
No
Constrained Future Closing Stock month n (Con.Fu.CLSn)
Displayed in Gross Sales Value
Con.Fu.CLSbox.n * Product Value
This calculation can only be applied at terminal level (distributor-Product)
No
Displayed in Boxes
If month n = Future Plan starting month: Con.Fu.CLSbox.n = Ac.Cl.Sbox.n-1 - Con.Fu.S.Outbox.n + Con.Fu.S.Inbox.n
• This calculation can only be applied at terminal level (distributor-Product)
No
If month n >= Future Plan starting month +1 : Con.Fu.CLSbox.n = Con.Fu.Cl.Sbox.n-1 - Con.Fu.S.Outbox.n + Con.Fu.S.Inbox.n
This calculation can only be applied at terminal
No
Constrained Future SIT month n (Con.Fu.SITn)
Displayed in Days
Sum (Con.Fu.S.Out Box.n→x * Product Value n→x) * n*30
This calculation can be applied at any level
No

Notes

  • If an adjustment is made in the Value (Sales In or Closing Stock), the tool will first convert it into an adjustment in volume, perform the recalculation and then translate back to Value.
  • The table will display a warning icon if your adjustments result in negative numbers. You'll need to address these discrepancies by expanding the table and identifying the source. In case you decide to simulate anyways, any negative Sales in will be considered as “0”.
  • If the sales out of 3 consecutive periods are Null the SIT won’t be calculated and instead “NAN” will be displayed.
  • If any editable value is “0”, it means that the aggregated level won’t be able to redistribute the lower levels as it is based on the child ratio (which is in this case 0). You will have to edit the atomic level (Distributor-Product).
Did this answer your question?
😞
😐
🤩