I have an inventory ledger. It looks like this:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping 7/14/12 Item2 -1
Opening Balance 6/30/12 Item3 3
Shipping 7/2/12 Item3 -1
Opening Balance 6/30/12 Item4 5
Shipping 7/3/12 Item4 -1
Shipping 7/3/12 Item4 -1
Shipping 7/3/12 Item4 -1
Shipping 7/5/12 Item4 -1
Shipping 7/5/12 Item4 -1
Receiving 7/9/12 Item4 10
The ledger obviously just marks each transaction for a given period: opening balance, shipping, receiving, and returns.
I use that inventory ledger to form a pivot table, which looks like this:
Sum of Quantity Column Labels
Row Labels 6/30/12 7/2/12 7/3/12 7/5/12 7/6/12 7/9/12
Item1 4 4 4 4 4 4
Item2 7 7 7 7 7 7
Item3 3 2 2 2 2 2
Item4 5 5 2 0 0 10
The function of the pivot table is to keep a running total of the inventory levels for each individual item, per day.The pivot table format is helpful because it maintains these totals for every single day that our warehouse processed transactions for ANY item, as opposed to the source data which just lists the dates of transactions as they happened for that particular item.
Using this running total data in the pivot table, I'd like to keep track of the days where inventory was at 0 quantity for each item. Here's the tricky part: I'd like to keep this count as part of the pivot table, so that the count would be dynamic when I group the data by month/quarter/year (so we can see total days out of stock by item for each period).
If I can do something with the base data that will allow me to have this Out of Stock info, that's great. If I can do it directly within the Pivot Table, that's even better.
Another more pie-in-the-sky solution would allow me to also generate data for consecutive days out of stock once an item hits zero, and then see averages for this by time period in the pivot table. I know most of this has to be done in the source data, and I even know how it needs to be done, but I can't quite string it together in a formula. First, I sorted the data by Date instead of by Item name. I created a new column to keep running inventory totals ("Running Total" is the column name). Once the running total hits zero, I need to find the next row where the Date is later than the Current transaction, where the Item name is the same, and where the transaction type is "receiving". From there, I'd need to find the difference between transaction dates. Can someone please help me come up with a formula for this as well?
Lastly, since our data only contains days where the warehouse processed transactions, I'm wondering if there's a way to get the missing dates (i.e., weekends and holidays) displayed on the pivot table as well? This isn't majorly important, but it would impress the top brass.
EDIT: After seeing many of the replies here, I've opened up to trying to resolve this issue through Access or SQL Server as well, and I've reposted the question on StackOverflow with a focus on using queries and calculated columns to figure it out.
That said, I'm still very open to solving this through excel if anyone has any more ideas! Since I can get the running totals in my source data, I'm wondering if I can do the Out of Stock with if statements in a new column.
If the running total is Zero, then I need to find the very next transaction for the same item, where the transaction type is "Receiving", and then return the date of that next transaction into the column. I'm not sure how to combine all those elements into one formula, though. Once I return the date of the next Receiving transaction, I could then subtract the Txn Date of the Current row for the difference in days.
It'd look like this:
Tran Type Txn Date Item Quantity Stock Out of Stock
Opening Balance 6/30/12 Item4 5 5
Shipping 7/3/12 Item4 -1 4
Shipping 7/3/12 Item4 -1 3
Shipping 7/3/12 Item4 -1 2
Shipping 7/5/12 Item4 -1 1
Shipping 7/5/12 Item4 -1 0 4 Days
Receiving 7/9/12 Item4 10 10
From there, I could do another pivot table with totals by item, or somesuch.