How much of your current stock goes further than a year’s consumption?

WHAT:

This calculation reveals whether you have bought more than 12 months’ consumption. The stock surplus is calculated from how much you have sold during the last 12 months. This calculation does not include any forecast. “Stock surplus 12 months” gives you a calculated column with the value of your stock surplus for each item.

WHY CALCULATE THE STOCK SURPLUS?

The stock surplus calculation gives you an indication of whether your stock is correctly compound or which items you probably are having too much of. If you want to reduce your stock value without influencing the level of service negatively, then have a look at items with huge stock surplus.

COLUMNS NEEDED IN THE CALCULATION:

• Consumption in pcs, 12 months (NUMBER)
• Stock/number of pcs in stock (NUMBER)
• Cost price (NUMBER)
• Date created (DATE)

FORMULA:

if([Consumption per day] = 0, [Stock]*[Cost price], if([Stock]-[Consumption per day]*360<= 0, 0, [Stock]-[Consumption per day]*360)*[Cost price])

NB. Consumption per day is calculated using Consumption in pcs, 12 months and Date created

Example: Stock surplus
Do you have an item with an average consumption each day = 5 pcs with a stock of 10.000 pcs and a cost price = 50 dkkr., then you can easily calculate the size of the stock surplus.

Because [Consumption each day] ≠ 0 and [Stock]-[Consumption each day]*360 > = 0, the last part of the formula is used.

Stock surplus = ([Stock]-[Consumption each day]*360 days)*[Cost price]
= (10.000 pcs – 5 pcs * 360 days) * 50 dkkr.

The stock surplus (value) = 
 408.750 dkkr.

Example: No stock surplus
If [Stock]-[Consumption each day]*360 < = 0, then the stock surplus is set to = 0.

For example 
10.000 pcs – 40 pcs * 360 days = – 4.600

RELATED RECIPE:

• Adequacy in days: Calculate how many days your stock will last.