excel pivot table for outstanding stock

excel pivot table for outstanding stock

Didn't find your answer?

I have a list of stock items purchased since year dot.

I have a list of stock items sold since year dot.

I have tried to use a pivot table to show the outstanding unsold items without success.

Can anyone offer any help please?

Replies (3)

Please login or register to join the discussion.

By mrme89
07th Jun 2013 17:01

See PM.

Thanks (1)
By bro0010
08th Jun 2013 12:54

excel pivot table for outstanding stock

Hi,

I'm assuming that the total number of transactions is less than the number of rows available in your Excel worksheets.  Consequently, this post is based on an Excel PivotTable using an Excel list data source.

You haven't given much detail about your data so I'm working on the presumption that your data contains at least the following three elements:

Stock part referenceTransaction dateQuantity

In Excel construct a single list with 5 columns (I've added two sample rows):

PartDateQtyIn/OutSigned QtyA000101-May-1310In10A000131-May-136Out-6

The first three columns are populated by your two lists. The fourth column, In/Out, is added to identify the source stock list. Finally, I've added a fifth column to allow you to change the sign (if you need to) on the "Out" transactions - to represent the effect on the stock balance. You can use a simple formula or Copy and then Edit | Paste Special | Values | Subtract to populate the Signed Qty with the negative values.

Base your PivotTable on this list, have Part as a row field and Sum of Signed Qty as a data field and you should have your answer as at the date the original lists were extracted. Call this worksheet "Balance Pivot"

However, I suspect what you really want to do is to calculate the balance at any given date up to the date of extract. The standard way of achieving this would be to base your PivotTable, not on an Excel list, but on an external data source using the Excel ODBC driver. I'm not going to suggest doing that as it takes many people out of their Excel "comfort zone".  Instead, add a sixth column and a seventh column to the data with the labels Include and Reporting Date respectively. On the "Balance Pivot" sheet put "Stock Reporting Date:" in cell C1 and put a date in cell D1. Name cell D1 as StockDate. In the Include column put the formula =if(B2 <=StockDate,"Yes","No") in row 2.  In the Reporting Date column put the formula =StockDate in row 2.  Copy these formulas all the way down to the bottom of the data. Make sure your PivotTable data source incorporates the two new fields. Show Include as a Page field and show the Reporting Date column as a column field. Set the Include page field to Yes and you should have your answer. The drawback to this approach, compared to a parameterised ODBC query, is that you have to manually refresh the PivotTable every time you change the date in cell D1 on the Balance Pivot sheet.  Use a formula and/or conditional formatting to highlight where the Reporting Date column data item (the StockDate at the last refresh) does not equal the entered "Stock Reporting Date:" in D1.

To see the outstanding stock items you would order the Part field by Sum of Signed Qty. Descending order will have all the parts in stock at the stated date at the top of the list; out of stock items in the middle of the list; and, problem items (negative balances) at the bottom of the list.

I hope this helps.

 

Ian

www.onionrs.co.uk

Sage reporting in Excel

Thanks (2)
avatar
By Towards excellence
09th Jun 2013 14:22

Wow, how generous of you to explain in such detail to the OP!

Hats off to you bro0010..

SA

 

Thanks (2)