How to build a Inventory usage formula

English
#1

Hello all,

We utilize Libre for our entire inventory of small parts. Since taking over the Warehouse Manager position I have been trying to enhance our productivity by adding usage to our current spread sheet. With that said, I have been searching for a formula that will calculate usage based on number of items used, date and aging by date. Not an easy formula for me to figure out so I am reaching out for some help. Below is what I have and what I need. If this can’t be done with just one column than maybe a few.
CELL - B8 IS QTY ON HAND
CELL - B4 = TODAY()
CELL - E2 - DATE OF INITIAL INPUT OF THE INVENTORY
CELL - H8 - THE CELL I AM LOOKING TO HAVE THIS FORMULA.

Now if there is a Calc template for inventory that already has this information including conditional formatting Awesome! Just need the link. But if not, can anyone provide a formula to try.

Thank you,
Jason

#2

I think you didn’t get an answer, b/c it is not possible to deduce what you are looking for. Try to be as clear as possible and show 1. what you have, and 2. what you want, 3. present it in a readable formatted text or table.
e.g. I don’t understand what you’re trying to tell by “CELL - B8 QTY ON HAND CELL - B4” what is “CELL”, what is “QTY”, is this a calculation anyway ( are the “-” indicating a subtraction?)?

#3

Sorry, to clarify. What i am trying to get is a usage formula based on the starting inventory and a date range. Each time a unit is deducted from the inventory the formula would calculate the usage of that one item in days. If nothing is taken from the inventory the number would continue to grow… Basically i have 1 cell with the quanty of the item, 1 with the date the item entered into inventory and 1 with todays date. The forth cell is the column i am looking to get this formula added.

#4

ROSt52, We manually deduct the numbers as we use them. And items/day.

#5

@rjb562 - I am a bit familiar with inventory issues and willing to give you a hand. However, before someone can tell you which formula you need, it is important to understand:

  • which stock parameters can you measure - could you please list them up with explanation what the parameters mean eg. you want to calculate the usage based on number of items used - usage over which period of time? or number of items used - again over which period of time and at which point in time do you get the information of "number of items used

  • what do you want to achieve - sketch it in words please eg. I got the feeling that you want to make a forecast calculation or reorder point calculation etc

Most likely it will even become necessary to understand about your order and delivery workflow. Could you possibly inform on the industry in which you are working and if you are a manufacturer, service provider or dealer.

Maybe you also can add your location (country) to your profile to consider legal reasons for parts stock.

#6

@rjb562 - When I am reading “Each time a unit is deducted from the inventory …” I get the impression that your inventory number is changing automatically. Is this assumption correct?

What is the time for for which you want to calculate the usage? items/day? items/week? items/month? or?

Did you consider to look for an open source inventory management system? Also I don’t know details, such systems should be available.

#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.