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

 

[bruderbell]

What are you trying to show with the chart? A pivot chart can very easily show the net amount located in the bin, but it seems more difficult to try to show stratification of the inventory to demonstrate age of the product. Which are you after (or something else)?

 

[lmcafee]

I wanted to embed an image (not from URL) but that didn't cut&paste.

Anyway, a stacked column chart is what is used for chart type. It shows the customer products added to the bin (ordered by date - newest on top; oldest on bottom). That works fine. The data labels show the amplifying information.

The problem is how to properly display bin amounts after subtractions (shipments or adjustments (transfers/shrinkage)).

 

[bruderbell]

So if today I brought 100lbs, then tomorrow you sold 30lbs, and Thursday I brought 100lbs, you want the chart to have one colored segment of 70 and on top of that, a diff color of 100, showing a net inventory of 170?

And then Friday you sell 60, so the chart should show a segment of 70 and on top of that, a segment of 40, for a total of 110?

If so, putting it into action is too much for me.

 

[lmcafee]

Inside every big problem is a lot of smaller problems trying to get out. Some clarity may be needed.

What is involved is commodities. In this case it is wheat. The wheat is delivered in trucks. Usually semi-trucks that hold about 1,000 bushels. Most wheat weighs 60lbs per bushel or about 60,000lbs per load. Most wheat is delivered in a short period of time (harvest). So there might be 20 trucks a day. It is stored and then the purchasing agent holds the stored wheat until they decide (market factors) to sell it.

The transaction table (spreadsheet) looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bin
[/TD]
[TD]Cust
[/TD]
[TD]Amt
[/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C1
[/TD]
[TD]60,000
[/TD]
[TD]1/28/2014
[/TD]
[TD]8am
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C2
[/TD]
[TD]59,500
[/TD]
[TD]1/28/2014
[/TD]
[TD]8:15am
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C1
[/TD]
[TD]45,000
[/TD]
[TD]1/28/2014
[/TD]
[TD]9am
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C3
[/TD]
[TD]62,000
[/TD]
[TD]1/29/2014
[/TD]
[TD]8am
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C2
[/TD]
[TD]30,000
[/TD]
[TD]1/29/2014
[/TD]
[TD]11am
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]SHIP
[/TD]
[TD]-60,000
[/TD]
[TD]1/30/2014
[/TD]
[TD]10am
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]SHIP
[/TD]
[TD]-24,000
[/TD]
[TD]1/30/2014
[/TD]
[TD]11am
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C3
[/TD]
[TD]40,000
[/TD]
[TD]1/30/2014
[/TD]
[TD]2pm
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C3
[/TD]
[TD]60,000
[/TD]
[TD]1/30/2014
[/TD]
[TD]4pm
[/TD]
[/TR]
</tbody>[/TABLE]

That is a typical. So if, for example, Bin 1 is empty the first load added to the bin would be the first entry into the spreadsheet or 60,000lbs at 8am on 1/28/2014. Then the next load would be added (dumped on top of the first load inside the bin) and the bin would contain 119,500lbs. Then on the 29th another 62,000lbs is added which brings the total to 182,000lbs for 3 customers. No problem. The entries are date and time sorted (oldest in first and newest in last).

So the Pivot table (just using the 5 columns listed) can easily be compiled and a chart created that shows (at the end of the 3nd load) 3 entries in a stacked column chart with their respective amplifying information (bin, customer, amount, date and time). Of course those can be summed to get the Bin inventory in total.

Then there are 2 shipments on the 30th of 84,000lbs. Those shipments would need to reduce the bin inventory by that amount. The new bin chart should not show the last add (62,000lbs shipped) plus 22,000lbs of the 59,500lbs of the 2nd add.

If there is a transfer from bin 1 to bin 2 then the resulting totals would need to also be adjusted.

Does that help?

 

[lmcafee]

Just a note that the shipments can be/are included if I do a plain vanilla Pivot Table from the transaction spreadsheet and the totals for the bin amounts would be correct (+'s offset by -'s), however, the detail (individual entries) would remain for the adds even though they were shipped. That is what I am trying to allow for somehow.

 

[Smitty]

I wanted to embed an image (not from URL) but that didn't cut&paste.

You can post an image to something like ImageShack, then use img tags with the link here ([ img ] LINK [ / img ] - no spaces).

If you'd like to post a shot of your sheet(s), you can use the Board's HTML Maker (you'll find the link in my sig).

 

[bruderbell]

Now we're getting somewhere. The way I see this happening is via another (current AMT) column, which at the start is a clone of the AMT column. A clever guy could come up with a macro that starts at the top of the Current AMT list and finds the first negative entry, then reduces the entries above (checking to match the bin) until the reduction is properly accounted for. So I see it leaving the Current Amt cell for the negative entry as a zero after having reduced the values above. Then it finds the next negative, matches bins, and reduces until zero, and so forth. I'll see what I can come up with. Seems like a good challenge.

 

[lmcafee]

I need the original transactions in tact. I have considered something like a "Do While" (is that a function in XL?) or similar. When the negative is encountered begin by retrieving the newest (bin_of_negative = bin_of_positive) value and if the newest value is >= negative then MOVE entry to archive spreadsheet. That would satisfy the reduction. I would not show shipments on the chart (shipment amounts would be in a different (outbound) column). The shipments (once the macro runs (on_spreadsheet_open) would also need to be moved to the archive.

I suppose too many years in the technology business has made me seek the simplest solution (and, hence, the easiest to maintain) possible. Maybe a journal made out of paper ....

 

[lmcafee]

PS - Thanks for the help!