Ideas for bin managment charts

[lmcafee]

I work part time at a small (and old) grain mill. There are several grain bins where product is stored. Grain can be added (customer brings it in), subtracted (product is shipped) or moved between bins internally (transferred from one bin to another). I want to use XL to keep track of these events by:

1. Working from 1 spread sheet that identifies the events and has a corresponding Pivot Table/Chart
2. Display the product located in each bin via a Pivot Chart (I "kind of" got that part figured out via a stacked column chart)
3. Provide total inventory in a Pivot Chart

Item 1 is easy. I just record the amount (taken from weight provided by the scale) and add an entry to spreadsheet. Those are the base transactions - amount is positive (inbound) or negative (outbound) or transferred(e.g., bin 1 to bin(s) x or x and y and so on.

The difficulty is in charting the bin reductions. All grain is loaded into the bin from the bottom to the top but is removed from the bins from the top to the bottom (last in / first out or LIFO). I associate a date with each transaction which also correlates the amount value.

There are a few options I have considered.

• One would be to remove the positive values (sorted by date) to an archival spreadsheet (remove original add transactions from primary driving spreadsheet) when the grain is shipped. If it is transferred then just add a new record with the bin location of the moved grain. There is the issue of "splits" to original transactions. For example, a shipment would be all of a single add and a partial of another (the grain would be pulled from 2 or more added transactions).
• Another would be to generate a new "Action" value in the original sheet (ADD, SHIP, XFER). That "flag" would leave the records in the original spreadsheet but require further filtering of transactions to Charts.
• Another category is "adjustments." Those would be to simply (after a bin "measurement" allow for grain lost via shrinkage (e.g., waste from cleaning operations and so on).

I am seeking advice/ideas from people more experienced with Pivot Tables and Charts to accomplish this. I can email what I have so far if that would clarify objective. Thanks in advance for any suggestions - LMc

 

[lmcafee]

I think it seems logical to set a macro to run at spreadsheet open that will delete the "current amt" column already there, then copy the amt column, then run the LIFO code to balance the books. Have you been able to figure out why it threw an error when a bin goes to zero balance?

It must have been something I did/was doing wrong on the error. I have since ran it several times w/o a problem. I do have a macro that copies the original sheet and creates a "LIFO" sheet where I run your macro. As soon as I get through the chart work it will be complete for now. Just been too busy with other tasks to spend the time.

Thanks for the help-

 

[bruderbell]

Brunderbell - Well, I think I am almost there with the project. I have a question you may be able to answer. I want to chart the Bin entries as per the LIFO results. When I produce the chart it "sums" by customer. I would like it to not sum but just show the individual amounts as sorted (date / time). It is a dumb question but I'm not seeing an easy way to show the detail?

My computer here won't let me use any of the cool plugins that let me post HTML snapshots of my workbook, but I can describe it to you. I inserted a pivot chart, then put bins in the "axis fields," date in the "Legend Fields," and [sum of] current amt in "values." Then, change the chart type to stacked column. You should be looking at a chart with bin numbers as the horizontal labels, with different colored segments corresponding to dates in the columns. The oldest dates are at the bottom, so the chart should be a fair representation of what is actually in your bins.

 

[lmcafee]

My computer here won't let me use any of the cool plugins that let me post HTML snapshots of my workbook, but I can describe it to you. I inserted a pivot chart, then put bins in the "axis fields," date in the "Legend Fields," and [sum of] current amt in "values." Then, change the chart type to stacked column. You should be looking at a chart with bin numbers as the horizontal labels, with different colored segments corresponding to dates in the columns. The oldest dates are at the bottom, so the chart should be a fair representation of what is actually in your bins.

Thanks and that is what I got as well. But when I tried to correlate the customer with the Bin location amount I got zip. I was hoping to get the "layers" in the stacked chart to report customer and amount. I will keep at it and thanks for the update.

 

[lmcafee]

Thanks and that is what I got as well. But when I tried to correlate the customer with the Bin location amount I got zip. I was hoping to get the "layers" in the stacked chart to report customer and amount. I will keep at it and thanks for the update.

I did just add the customer to the Legend Field. Duh. But I have looked and looked for how to add default "Data Labels" for a Pivot Chart in XL. Just like you would select a chart type or a background. I have read a few articles but usually it is just one series at a time. But I am going to polish this a bit and push it over for consideration. Thanks ~