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]

For it to work...the data needs to have been sorted by date and time.

Your data there is sorted differently than what we had in the first dataset you gave. I could make the macro sort the data oldest to newest, but for now it'd be easier to just sort it before running the macro.

The macro looks at the top value for current amount (which it assumes to be the oldest). If it finds a negative value, it looks at values in the rows above the found negative, matches bin numbers, and makes the deduction - this is the LIFO mechanism.

Since you have that data sorted in opposite order, it is finding that first negative value, and it starts trying to look for a positive value above that one. It throws an error, since there is none. We could make the macro more robust, but for now just try ordering the data oldest to newest (like one would expect with a logbook).

 

[lmcafee]

Your data there is sorted differently than what we had in the first dataset you gave. I could make the macro sort the data oldest to newest, but for now it'd be easier to just sort it before running the macro.

The macro looks at the top value for current amount (which it assumes to be the oldest). If it finds a negative value, it looks at values in the rows above the found negative, matches bin numbers, and makes the deduction - this is the LIFO mechanism.

Since you have that data sorted in opposite order, it is finding that first negative value, and it starts trying to look for a positive value above that one. It throws an error, since there is none. We could make the macro more robust, but for now just try ordering the data oldest to newest (like one would expect with a logbook).

I guess I had also figured that out. I started with a negative balance which would not happen. Here is what was calculated:

Bin	Amt	Cust	date	current amt
1	10000	C1	1/25/2014	10000
1	20000	C2	1/26/2014	20000
1	50000	C3	1/27/2014	50000
1	100000	C2	1/28/2014	100000
1	90000	C1	1/29/2014	90000
1	200000	C1	1/30/2014	200000
1	50000	C3	1/31/2014	50000	520000
1	-100000	SHIP	2/1/2014	-100000
1	-8000	SHIP	2/2/2014	-8000
1	-100000	SHIP	2/3/2014	-100000	-208000	312000
1	90000	C3	2/4/2014	90000
1	100	C1	2/5/2014	100	90100
1	-500	SHIP	2/6/2014	-500	-500	89600
	401600				401600
	610100
	-208500
	401600

<tbody>
</tbody>

That is summation over time and arrives at the final result as your code returned (the -500 is not included?). That is, the final -500 doesn't seem to be included (89,600 vs 89,100). Seem right?

Bin	Amt	Cust	date	current amt
1	10000	C1	1/25/2014	10000
1	20000	C2	1/26/2014	20000
1	50000	C3	1/27/2014	50000
1	100000	C2	1/28/2014	100000
1	90000	C1	1/29/2014	90000
1	200000	C1	1/30/2014	42000
1	50000	C3	1/31/2014	0	312000
1	-100000	SHIP	2/1/2014	0
1	-8000	SHIP	2/2/2014	0
1	-100000	SHIP	2/3/2014	0	0	312000
1	90000	C3	2/4/2014	89600
1	100	C1	2/5/2014	0	89600
1	-500	SHIP	2/6/2014	0	-500	89100
	401600				401100
	610100
	-208500
	401600

<tbody>
</tbody>

What remains is to decide on how to work with it. I am vacillating a bit. Perhaps a worksheet with your macro that has a "date selection" would be best. Leave the base transactions but select "snapshot dates and xfer them to another sheet, perform the calculation and graph it. What is happening now is the 50,000lb amount on 1/31 is zeroed out but the preceding are not.

I don't know. I will need to think about it a bit more.

Thanks for the macro. It seems to work for the most part so far.

 

[lmcafee]

And something I had not considered is if the macro works for multiple bin selections, e.g., bin 1, 2 and 3 for example. I will try it.

 

[bruderbell]

So here is the balancing of that sheet:
10000 10000
20000 20000
50000 50000
100000 100000
90000 90000
200000 150000 142000 42000 42000
50000 0 0
-100000 -50000 0 0
-8000 0 0
-100000 0 0
90000 89600 89600
100 0 0
-500 -400 0 0

You can see that the -100k takes all of the 50k, then takes 50k from the 200k, leaving 150k. Then the -8k takes from the remaining 150k to leave 142k. Then -100k takes from that 142k, leaving 42k. the next negative is the -500, which takes first from the 100. -400 is left and comes from the 90k, leaving 89600. Overall sum is 401600.

Is that not how you see it working?

*sorry about that table...it looks good in my post but squishes left upon posting.

 

[lmcafee]

And something I had not considered is if the macro works for multiple bin selections, e.g., bin 1, 2 and 3 for example. I will try it.

Interesting. I ran the macro with 3 entries on bin 2 which were 60,000, 20,000 and -20,000. The 20,000 and -20,000 cancelled each other out but the first entry (date wise) remained at 60,000. When I added a -30,000 then there was 30,000 removed from the 60,000 amount. The bin 1 records worked as before.

Bin	Amt	Cust	date	current amt
1	10000	C1	1/25/2014	10000
1	20000	C2	1/26/2014	20000
1	50000	C3	1/27/2014	50000
1	100000	C2	1/28/2014	100000
1	90000	C1	1/29/2014	90000
1	200000	C1	1/30/2014	42000
1	50000	C3	1/31/2014	0
1	-100000	SHIP	2/1/2014	0
1	-8000	SHIP	2/2/2014	0
1	-100000	SHIP	2/3/2014	0
1	90000	C3	2/4/2014	89600
1	100	C1	2/5/2014	0
1	-500	SHIP	2/6/2014	0
2	60000	c1	31-Jan-14	30000
2	20000	c1	01-Feb-14	0
2	-20000	SHIP	02-Feb-14	0
2	-30000	SHIP	03-Feb-14	0

<tbody>
</tbody>

 

[lmcafee]

So here is the balancing of that sheet:
10000 10000
20000 20000
50000 50000
100000 100000
90000 90000
200000 150000 142000 42000 42000
50000 0 0
-100000 -50000 0 0
-8000 0 0
-100000 0 0
90000 89600 89600
100 0 0
-500 -400 0 0

You can see that the -100k takes all of the 50k, then takes 50k from the 200k, leaving 150k. Then the -8k takes from the remaining 150k to leave 142k. Then -100k takes from that 142k, leaving 42k. the next negative is the -500, which takes first from the 100. -400 is left and comes from the 90k, leaving 89600. Overall sum is 401600.

Is that not how you see it working?

*sorry about that table...it looks good in my post but squishes left upon posting.

I just used the automated summation in XL when I compiled my data. Let me revisit it. I did get an error (400) when I added two Bin 3 entries and put a 50,000 add and then (for the same day) a -50,000 shipment (net 0).

 

[lmcafee]

So here is the balancing of that sheet:
10000 10000
20000 20000
50000 50000
100000 100000
90000 90000
200000 150000 142000 42000 42000
50000 0 0
-100000 -50000 0 0
-8000 0 0
-100000 0 0
90000 89600 89600
100 0 0
-500 -400 0 0

You can see that the -100k takes all of the 50k, then takes 50k from the 200k, leaving 150k. Then the -8k takes from the remaining 150k to leave 142k. Then -100k takes from that 142k, leaving 42k. the next negative is the -500, which takes first from the 100. -400 is left and comes from the 90k, leaving 89600. Overall sum is 401600.

Is that not how you see it working?

*sorry about that table...it looks good in my post but squishes left upon posting.

Bin	Amt	Cust	date	current amt		From XL summation
1	10000	C1	1/25/2014	10000
1	20000	C2	1/26/2014	20000
1	50000	C3	1/27/2014	50000
1	100000	C2	1/28/2014	100000
1	90000	C1	1/29/2014	90000
1	200000	C1	1/30/2014	200000
1	50000	C3	1/31/2014	50000	520000	520000
1	-100000	SHIP	2/1/2014	-100000		420000
1	-8000	SHIP	2/2/2014	-8000		412000
1	-100000	SHIP	2/3/2014	-100000		312000
1	90000	C3	2/4/2014	90000		402000
1	100	C1	2/5/2014	100		402100
1	-500	SHIP	2/6/2014	-500		401600	401600
2	60000	c1	31-Jan-14	60000
2	20000	c1	01-Feb-14	20000
2	-20000	SHIP	02-Feb-14	-20000
2	-30000	SHIP	03-Feb-14	-30000
3	50000	c1	02-Jan-14	50000
3	-50000	SHIP	02-Jan-14	-50000

<tbody>
</tbody>

That (401,600) is also the value returned by the macro - my bad. And yes, the LIFO function (adjusted entries) is also correct. I wasn't looking at it correctly.

And it works for all highlighted entries as expected. I don't know why "0" on bin 3 threw the error though.

 

[lmcafee]

I am wondering about the best structure for this exercise. I think it would be best to leave the original transaction sheet in tact. Then have another worksheet in the workbook that contains extracted transactions from the original worksheet. In that worksheet have it constructed to pull transactions from the original worksheet by date and bin(s). Then run the macro as needed. Because your macro modifies "Current Amt" the original "Amt" is untouched and there is no data loss. The option to pull by dates for a bin or bins would be problematical using it as it is since there would need to be a starting Bin balance for the "From Date" value. Hmmm.

Given the life cycle of the spreadsheet data would be 1 year (harvest through mill empty and cleaning for next harvest) there wouldn't be that many transactions (hundreds at the most). So maybe the best thing would be to just run the macro on workbook "open" for all bins all the time to get a current status.

What do you think/suggest?

 

[lmcafee]

Your data there is sorted differently than what we had in the first dataset you gave. I could make the macro sort the data oldest to newest, but for now it'd be easier to just sort it before running the macro.

The macro looks at the top value for current amount (which it assumes to be the oldest). If it finds a negative value, it looks at values in the rows above the found negative, matches bin numbers, and makes the deduction - this is the LIFO mechanism.

Since you have that data sorted in opposite order, it is finding that first negative value, and it starts trying to look for a positive value above that one. It throws an error, since there is none. We could make the macro more robust, but for now just try ordering the data oldest to newest (like one would expect with a logbook).

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?

 

[bruderbell]

I am wondering about the best structure for this exercise. I think it would be best to leave the original transaction sheet in tact. Then have another worksheet in the workbook that contains extracted transactions from the original worksheet. In that worksheet have it constructed to pull transactions from the original worksheet by date and bin(s). Then run the macro as needed. Because your macro modifies "Current Amt" the original "Amt" is untouched and there is no data loss. The option to pull by dates for a bin or bins would be problematical using it as it is since there would need to be a starting Bin balance for the "From Date" value. Hmmm.

Given the life cycle of the spreadsheet data would be 1 year (harvest through mill empty and cleaning for next harvest) there wouldn't be that many transactions (hundreds at the most). So maybe the best thing would be to just run the macro on workbook "open" for all bins all the time to get a current status.

What do you think/suggest?

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?