4

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.

11
  • 1
    Ugh! US Date format - it would be much easier for those of us not in the USA to have ISO date (yyyy-mm-dd) which should work anywhere ;) Jul 16, 2012 at 16:06
  • Excel recognizes the values/format as date values, if that helps
    – Matt
    Jul 16, 2012 at 16:08
  • Nope it doesn't I'm afraid. Only if you have your Windows locality set to something that uses US date formats. Here in the UK, the default is dd/mm/yyyy not mm/dd/yyyy - neither format is especially helpful for processing dates which is why I always use ISO (yyyy-mm-dd) which works everywhere. Jul 16, 2012 at 16:25
  • As to your problem. I've been scratching my head. I think that you will have to generate a table of dates to achieve what you want. You can then merge that with the transaction data. Personally, I wouldn't be doing this in Excel as it makes my tiny brain ache just to think about it - time to break out MS Access I suspect. Jul 16, 2012 at 16:26
  • 1
    :( I'm not even sure what I'd do with this data in access; I haven't used the program in ages. But getting the missing dates in is not as big of a problem as the other two pieces: Having a dynamic count of the zeroes in the running totals, and generating consecutive days out of stock in the source data. Would those really be any easier in access?
    – Matt
    Jul 16, 2012 at 16:38

2 Answers 2

2

I reckon it (or most of it?) can be done in Excel without VBA, but trying to explain how is another matter.

  1. It helps to be able to identify Opening Balances by Item so add ColumnA into your inventory ledger (referred to as Sheet1 henceforth) and put =B2&D2 in A2 (assumes “Tran Type” was in A1).

  2. Don’t want to interfere with Sheet1 so in Sheet2!ColumnB put one instance of each item (Data > Advanced > List range > =$D2:$D[last occupied row] > Copy to another location > Copy to: [within Sheet1] > Unique records only, then copy resultant list)

  3. Populate 6/30/12 balances in Sheet2!ColumnC [‘manually’ is probably easiest if you copy from Sheet1 after sorting by Item within Tran Type]

  4. Put 6/30/12 (if you must use that notation!) into Sheet2!C1 and =1+C1 into Sheet2!D1.

  5. Put =AND(D1>=$A2,D1<=$B2) into Sheet2!D2 [for use with counting stockouts within a time range].

  6. Put the start date for counting stockouts in A2 and the end date in B2.

  7. Put =IF(VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$C,3,FALSE)>D$1,"",IF(VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$C,3,FALSE)=D$1,VLOOKUP("Opening Balance"&$B4,Sheet1!$A:$E,5,FALSE),C4+SUMIFS(Sheet1!$E:$E,Sheet1!$C:$C,Sheet1!K$2,Sheet1!$D:$D,$B4)))’ in Sheet2!D4 and copy down for all Items.

  8. Copy Sheet2!ColumnD across as far as required [I suggest not more than +1 year!) and note last column [here assumed to be Q].

  9. In Sheet2!A4 put =COUNTIFS(C4:Q4,0,C$2:Q$2,"=TRUE") and copy down for all Items.

  10. If your Items really are named as in the example you could set up more than you think you will need for the period you have chosen but otherwise would have to watch out for new Items.

  11. After your chosen period, transfer the latest values as though ‘Opening Balances’ in a new sheet.

  12. Try any alternatives!

0
0

I did not quite get all of your points, but lets start with the last, which I think I have a good solution for you. Maybe we can build it from there.

So I used some neat fomulars to get to this table:

Tran Type   Txn Date    Item    Quantity    Stock   Outstock day    Restock day Days without stock
Receiving   03.06.2012  Item4   5           5           
Receiving   30.06.2012  Item1   4           4           
Shipping    02.07.2012  Item3   -1          -1          
Shipping    03.07.2012  Item4   -1          4           
Shipping    03.07.2012  Item4   -1          3           
Shipping    03.07.2012  Item4   -1          2           
Shipping    03.07.2012  Item4   -1          1           
Shipping    03.07.2012  Item4   -1          0       03.07.2012     03.08.2012      31
Receiving   09.07.2012  Item3   10          9           
Shipping    14.07.2012  Item2   -1          -1          
Receiving   01.08.2012  Item2   7           6           
Receiving   02.08.2012  Item3   3           12          
Receiving   03.08.2012  Item4   5           5           
Receiving   09.08.2012  Item4   10          15          
Receiving   10.08.2012  Item5   11          11          
Receiving   11.08.2012  Item6   12          12          
Receiving   12.08.2012  Item4   13          28          
Receiving   13.08.2012  Item8   14          14          

Stock

C2 = SUMIF($C$1:$C2,$C2,$D$1:D2)

-> this will give you a running stock just from the start, without specifying a starting point.

Outstock day

F2 = IF(E2=0,B2,"")`

Restock day

G2 = IF(E2=0,=INDEX($B:$B,MATCH($C2,OFFSET($C:$C,ZEILE($C2),0,ROWS($C:$C)-ROW($C2),1),0)+ROW($C2)),"")`

-> this works, if you sort your data by date, and when there are no shipments made for an item, after it his out of stock

Days without stock

H2 = IF(E2=0,G2-F2,"")

-> here you might want to watch out, substracting dates may not always result in the correct count, but this is another problem.

The main magic is the offset function combined with match. By offsetting the range match is working on, you exclude any data before the row you are on right now, match is then looking for the first occurence of the current item.

I was working on a way to solve this with this formular, but ran into trouble adjusting it, to get only the first date for the current item - and not for any item.

VLOOKUP("Receiving",OFFSET(A:C,ROW(C2)-1,0,ROWS(C:C)-ROW(C2)-1,3),2,FALSE)

Hope this will help you or other, I am happy to extend this ...

1
  • 1
    @pnuts yes, ZEILE=ROW, sorry for that. And yes I've seen that the interest was shiftet, but this interested myself and as I got to a solution, thought I should share it.
    – Jook
    Sep 27, 2012 at 6:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .