Decrementing inventory

[SolCo83]

Hi,

I don't know if I can make sense of this, but I work with inventory have a need to use excel to increase decrease inventory based on sales and restock. It'll be great if I could get some help with this.

Lets say that I house 100 different items with their own unique sku/upc in stock and 50 units for each of them.


And in a spend of a week 4000 orders come in. Not all of the 100 items are being ordered. Some are single item orders and some are multiple quantity and/or multiple items.

How do I check if the quantity is enough for the all the orders?

eg. What I do currently is use a table of the current inventory and vlookup the sku/upc to enter in the current stock for the item in the order to see if it can be allocated to it.
The problem is, if there are many of the one item being ordered in different orders on different days, I can't know if the latest orders can have the item allocated to it because the current inventory table does not decrement on it's own.

What I need help in is, if there's a function(s) that can help me allocate and decrease the table number or something? Anything.

Hope my explaination is clear enough. Sorry if it isn't, in which case, please ask if there are questions.

Thanks.

 

[ChrisM]

There are several ways to solve this, but the exact solution will depend on the layout of your workbook and how you intend to review the information (ie daily, weekly, etc. If daily, would you want to see the next 30 days plan, etc)

One way is to use SUMIFS formulas:

First create a single data table to hold all transactions (starting inventory, receipts, sales). This table should have four columns: Type (Start Balance, Receipt, or Sales), SKU #, Date, Quantity (Assume these are columns A:D)

Began the table by putting your 100 parts in to rows 2-101 with a Type = "Start Balance" and the appropriate date and quantity on hand.

Every time you sell or receive a SKU, add a row to the table

This is your data table, now you just need to build your reporting sheet:

Put your SKUs down one column, then your desired time period across row 1 or whatever.

To get the on-hand balance for the first SKU for whatever date you want, use this formula (assuming SKU is in A2 and Date is in B1): =SUMIFS(Data!$D$2:$D$10000, Data!$B$2:$B$10000, $A2, Data!$C$2:$C$10000, ">=" & B$1)

 

[Smitty]

Welcome to the Board!

This kind of thing is better off in Access. And there are pre-built Order/Inventory management templates you can use.

 

[SolCo83]

@ChrisM
Thanks. I'll give that a try.
Appreciate the help.

@Smitty
I'll try this out if the above getting to burdensome.
Thanks.