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]

Thanks Smitty. That's good information going forward.

 

[lmcafee]

Perhaps some other information since we are into this would be beneficial.

Bin capacities are physical based on material - not weight. I am in Wyoming. Last year was the worst drought on record. So wheat that was very high in protein (a side effect of drought) was also very low in moisture. A test weight (TW) per bushel was 8-9%. Whereas, this year due to intermittent rain (and hail) was high > 12% on average. So the bins would contain the same amount of physical wheat but the weight would vary significantly between years. Our example, bin 1, has a physical capacity of 5,532 bushels but could contain between 315,324lbs to 348,516lbs. I just know it is full when the overflow starts spitting out wheat as I fill it. Therefore, it is not necessary to create a Pivot Chart with a stated capacity. Empty always is.

If the Pivot Table and, subsequent Pivot Chart, has negative (less than empty) entries it is merely the shipment information. I'm not so sure that is a bad thing because it shows when the shipment was made. But it is probably better not to show it graphically to eliminate confusion. That was why I was thinking that shipments, while recorded in the transaction sheet, could be also moved to the archival sheet.

What is in the bin on any given day is ultimately the goal. I enter the transactions daily and email them to the general office (in Nebraska) so they have a snapshot. The problem arises in that there are other problems that present themselves subsequent to events like grain testing for foreign material, e.g., rye. For some reason bread makers don't want rye in the wheat unless they are making rye bread and then they don't want wheat in the rye. A sample may not be processed for days after the wheat is added to the bin so we need to be able to determine which customer brought in the wheat with rye on a delayed basis. Then I am confronted with moving the product from bin to bin to isolate the problem wheat. That is another reason transfers are important. Movement of grain between the bins is essential. For example, if "wet wheat" needs to be moved to a bin with a dryer then it shows the activity. I think a simple "Xfer" transaction type is sufficient.

And, while it is on my mind, "when" all this occurs is important. I suppose a "manual" calculation that runs a macro would be important? For example, if I completed the transaction log without any specific order the shipments might be recorded before the actual preceding adds. Hmmmm. I suppose I would enter all the transactions and then "run_macro" which would be able to do a sort of the entries (date and time) and process them in the correct order?

Got to go ship some product-

 

[lmcafee]

This exercise is good for my thinking about the problem.

What I am really needing is a snapshot of any bin selected at any point in time. When I was contributing on inventory systems I kept the transactions in a data table as a "+" or "-" value. Then when someone wanted a current inventory the report simply took the date provided and then summed the transactions. It was way better than trying to maintain an historic (summed) inventory value.

Perhaps the best thing is to add a date filter to a Pivot Table/Chart that would provide a snapshot of a bin on any given day by simply summing the historical records (records before selected date) and omitting the shipments from the displayed graphic.

Hmmmm. It might need some refinement but might also provide some traction.

 

[lmcafee]

This exercise is good for my thinking about the problem.

What I am really needing is a snapshot of any bin selected at any point in time. When I was contributing on inventory systems I kept the transactions in a data table as a "+" or "-" value. Then when someone wanted a current inventory the report simply took the date provided and then summed the transactions. It was way better than trying to maintain an historic (summed) inventory value.

Perhaps the best thing is to add a date filter to a Pivot Table/Chart that would provide a snapshot of a bin on any given day by simply summing the historical records (records before selected date) and omitting the shipments from the displayed graphic.

Hmmmm. It might need some refinement but might also provide some traction.

A little more thinking overnight. The summation for a "Bin Inventory" would work using this method. Just a straight calculation of "how much" is in the bin or "how full" is the bin. It wouldn't solve the LIFO problem (record matching).

 

[bruderbell]

This has been fun thinking about. I don't think it is doing 100% of what you want, but I think it is getting closer.

First, here I added a column to your dataset from above. I copied the Amt and called it "Current Amt." Then I wrote this code. For it to work, you need to highlight the data in the current amount column. And, like you stated, the data needs to have been sorted by date and time. For convenience, I created a "DateTime" column to combine the two. My formula there was

=D2+IF(LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12)>=1,(LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12))/24,LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12))

But I digress.

Heres the code.

Sub balance()
Do While Selection.Offset(-1, q).Resize(1).Value <> "Bin"
q = q - 1
Loop
For Each x In Selection
y = 1
    Do While x < 0
        If x.Offset(0, q).Value = x.Offset(-y, q).Value And x.Offset(-y).Value <> 0 Then
            Select Case Abs(x.Offset(-y).Value) / Abs(x.Value)
            Case 1
            x.Offset(-y).Value = 0
            x.Value = 0
            Case Is < 1
            x.Value = x.Value + x.Offset(-y).Value
            x.Offset(-y).Value = 0
            Case Is > 1
            x.Offset(-y).Value = x.Offset(-y).Value + x.Value
            x.Value = 0
            End Select
        End If
    y = y + 1
    Loop
Next x
End Sub

So it effectively gonkulates the deductions on a LIFO basis. After you run it, you can then chart the inventory with the bins as the axis field, datetime as the legend fields, and sum of current amt as the values. I get a nice chart.

For transfers, you would just need to make two entries in your database: one removal from the losing bin, and one addition to the gaining bin. It should work out, shouldn't it?

 

[lmcafee]

This has been fun thinking about. I don't think it is doing 100% of what you want, but I think it is getting closer.

First, here I added a column to your dataset from above. I copied the Amt and called it "Current Amt." Then I wrote this code. For it to work, you need to highlight the data in the current amount column. And, like you stated, the data needs to have been sorted by date and time. For convenience, I created a "DateTime" column to combine the two. My formula there was

=D2+IF(LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12)>=1,(LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12))/24,LEFT(E2,LEN(E2)-2)+IF(RIGHT(E2,2)="am",0,12))

But I digress.

Heres the code.

Sub balance()
Do While Selection.Offset(-1, q).Resize(1).Value <> "Bin"
q = q - 1
Loop
For Each x In Selection
y = 1
    Do While x < 0
        If x.Offset(0, q).Value = x.Offset(-y, q).Value And x.Offset(-y).Value <> 0 Then
            Select Case Abs(x.Offset(-y).Value) / Abs(x.Value)
            Case 1
            x.Offset(-y).Value = 0
            x.Value = 0
            Case Is < 1
            x.Value = x.Value + x.Offset(-y).Value
            x.Offset(-y).Value = 0
            Case Is > 1
            x.Offset(-y).Value = x.Offset(-y).Value + x.Value
            x.Value = 0
            End Select
        End If
    y = y + 1
    Loop
Next x
End Sub

So it effectively gonkulates the deductions on a LIFO basis. After you run it, you can then chart the inventory with the bins as the axis field, datetime as the legend fields, and sum of current amt as the values. I get a nice chart.

For transfers, you would just need to make two entries in your database: one removal from the losing bin, and one addition to the gaining bin. It should work out, shouldn't it?

Impressive! I'm not sure until I do some Trial & Error scenarios how it will work. I also don't know where to insert the code you wrote or how/when it is run. But I will let you know for sure as time permits. I have a couple of other immediate tasks at hand. Thanks - LMc

 

[bruderbell]

Impressive! I'm not sure until I do some Trial & Error scenarios how it will work. I also don't know where to insert the code you wrote or how/when it is run. But I will let you know for sure as time permits. I have a couple of other immediate tasks at hand. Thanks - LMc

I have it in "ThisWorkbook" in VBA. To get to VBA from excel, hit alt+f11. This runs only when called, and you can call it either from the developer tab (excel 2010), macros, or you can assign a hotkey to it. Give it a try and let me know.

 

[lmcafee]

I have it in "ThisWorkbook" in VBA. To get to VBA from excel, hit alt+f11. This runs only when called, and you can call it either from the developer tab (excel 2010), macros, or you can assign a hotkey to it. Give it a try and let me know.

I did the cut & paste work and saved it as a macro in the spreadsheet (saved the SS as a XlSM type with the "balance" macro). When I execute the macro I get an error. It displays "400" and is a Visual Basic error apparently. I will research.

 

[bruderbell]

Did I fail to mention that you should highlight the data that needs balanced, prior to running the macro? Don't highlight the column header "Current AMT", just the data.

With VBA open, trying stepping through the macro to ID what line is throwing the error. So first highlight the data, then go to VBA, put your cursor on the title of the macro, and start pressing F8

 

[lmcafee]

Did I fail to mention that you should highlight the data that needs balanced, prior to running the macro? Don't highlight the column header "Current AMT", just the data.

With VBA open, trying stepping through the macro to ID what line is throwing the error. So first highlight the data, then go to VBA, put your cursor on the title of the macro, and start pressing F8

Okay. I added a column "current amt" per your previous note. I highlighted the data (not the column heading) and called the "step into" for the macro. What happens is this.

1. It enters "Do While" and cycles the loop 4 times before falling through
2. It does the next "Do While" once and falls through then forces the error

Here is the data I am using (sorry about the "crowding") Amt and cust ARE separate.

SHIP

C1

C3

SHIP

SHIP

SHIP

C3

C1

C1

C2

C3

C2

C1

<colgroup><col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="width: 64"]Bin[/TD]
[TD="width: 64"]Amt[/TD]
[TD="width: 64"]Cust[/TD]
[TD="width: 68"]date[/TD]
[TD="width: 64"]current amt[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]-500[/TD]

[TD="class: xl64, align: right"]2/6/2014[/TD]
[TD="align: right"]-500[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="class: xl64, align: right"]2/5/2014[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]90000[/TD]

[TD="class: xl64, align: right"]2/4/2014[/TD]
[TD="align: right"]90000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]-100000[/TD]

[TD="class: xl64, align: right"]2/3/2014[/TD]
[TD="align: right"]-100000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]-8000[/TD]

[TD="class: xl64, align: right"]2/2/2014[/TD]
[TD="align: right"]-8000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]-100000[/TD]

[TD="class: xl64, align: right"]2/1/2014[/TD]
[TD="align: right"]-100000
[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]50000[/TD]

[TD="class: xl64, align: right"]1/31/2014[/TD]
[TD="align: right"]50000
[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]200000[/TD]

[TD="class: xl64, align: right"]1/30/2014[/TD]
[TD="align: right"]200000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]90000[/TD]

[TD="class: xl64, align: right"]1/29/2014[/TD]
[TD="align: right"]90000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100000[/TD]

[TD="class: xl64, align: right"]1/28/2014[/TD]
[TD="align: right"]100000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]50000[/TD]

[TD="class: xl64, align: right"]1/27/2014[/TD]
[TD="align: right"]50000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]20000[/TD]

[TD="class: xl64, align: right"]1/26/2014[/TD]
[TD="align: right"]20000[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]10000[/TD]

[TD="class: xl64, align: right"]1/25/2014[/TD]
[TD="align: right"]10000[/TD]

</tbody>

LMc