Physical Inventory in Binned and Lotted warehouse

[JayTheGeek]

Hi folks, I have a dilemma on my hands and would like to see if there is experience online that I can leverage.

Our environment is Macola ES 360 V4.0.0.323 on SQL2000. We are a distribution company with four stocking locations in our primary company. Each warehouse is multibin. Most items are assigned dye lots. We have approx. 2700 stocked items.

One of our locations is a distribution hub and gets most of the PO Receiving. Three other locations get most stock via 'Transit' inventory. All four locations sell and 'confirm ship' inventory. We move inventory in significant volume.

We did our first inventory back in December 2006, things did not go well. After posting count batches, close to half our inventory was in error. Location quantities were wrong compared to count quantities and location quantities did not match the sum of bin and/or lot quantities.

19 man days of data entry later, we got things back in Balance.

We are scheduled to do inventory again at the end of May. I have spent the last three weeks testing.

I have created count batches for each of my physical warehouses; so I have four batches to test, each with different combinations of post dates and the use freeze quantities flag. I created the test batches in our live database to capture a few days of inventory movement before migrating the database to our test environment. I have done data entry for a select few hundred items for each batch. I have posted twice using different values for the post date. (That was based on my theory of what I did wrong in December)

I had hoped to prove that the posting date or freeze date was the source of my problems. I have posted six distinctly different batches I have not had one post 'clean'.

Although the pre post and post register report 'no errors found' what I am left with is similar to the December results where a significant portion of my database is out of balance.

Has anyone out there done physical inventory in a similar environment? Have you seen similar posting quantity errors?


Regards,

Jay

 

[tpllc]

Jay,
You use a couple of terms that I’m nut sure exactly what you mean so I’ll ask some specific questions.
1. What do you mean when you said that none of the batches posted clean?
2. When you say that the pre-post and posting report show no errors what do you mean? These reports are not designed to ferret out inventory count variance just invalid account numbers and debits and credits not being equal.
3. I never see any reference to the important reports like the tag variance report.
a. After you enter the tag counts you should be running the variance by tag report to see what count errors are discovered.
b. Have you run this report?
c. Does it show little or no variance? This is what will determine the values that get posted to the inventory.


Since you are multi bin and lot controlled your process is more complicated. But in some respects it is easier to discover the transaction errors. Each and every transaction is uniquely identified.


You gave some background information but I have another question about your process. You state that you are doing a lot of volume. Are you using bar code scanners to report the transactions in the warehouse? I have many years in the distribution environment and this experience tells me that it is very difficult to properly record the correct bin and lot information in the environment that you have described by manual methods.

You are on a very new version of the code so can I assume that you are a new installation? The purpose of this question is to try and determine how much training you may have had.

It doesn’t look like you are doing periodic cycle counting so I wonder why you are doing confirm shipping? This is not the only reason to do confirm shipping but it usually is the primary one. Why do you do confirm shipping.

In general this process of taking physical inventory with frozen inventories, multi bin and lot controlled items is not a new process and I have a few customers that are somewhat comparable to what you have described. They are not experiancing these types of issues.

You pretty much have all the advanced features running so if you weren’t well trained I could see there being room for errors. I am not assuming that you are making errors or don’t understand the process but I just have to try and evaluate your processes and skill level. Please don’t take offense as none is intended. It is very difficult process for you to describe everything that you are doing and for me to determine if everything is being done correctly via a written report like this but we still can try.

I probably raised more questions than provided answers but I really need more information to provide assistance. Have you discussed these issues with your reseller? This really sound like some consulting is in order.




Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[MacolaHelp]

In addition, using quantity adjustments and transfers can create problems in maintaining an accurate balance between location quantities, bin quantities, and lot quantities. These 3 files can all operate independently, and you must use issue and receipt transactions as well as physical count processing to keep the 3 in balance rather than qty adjusts and transfers. Also, when you do physical count processing, if there is no inventory in a particular bin, you don't get a tag to count. You have to research any discrepancies you have between counted bins and stock status, and determine if you need to enter an extra tag for inventory that was located in an unknown bin in macola's brain.

As Steve points out, you have many reports that you can and should run to determine inventory discrepancies and solutions before you post. Have you reviewed your procedures and results with your reseller? And, the dates are very important in how the inventory posts to frozen and current. I'm not sure this forum will be able to provide you with a definitive solution as your processing is fairly complex.

 

[JayTheGeek]

Steve,

Thanks for the quick and thorough follow-up.

First the experience question; certainly no offence taken. I do appreciate you taking the time to dive into what is certainly not a simple issue.

I am relatively new to Macola. I have been in this company/position since February 2006. My first couple months were spent in an intensive learning curve with online training, meetings with the VAR and review of business processes. We went live with Macola August 1, 2006. It was a very aggressive time schedule both for me and this company. You are quite correct to identify training & procedures as risk areas.

I do have several years experience in Distribution. Over 20 years in MIS/IT and hands on experience with several ERP packages. I am very comfortable with SQL and Crystal Reports etc. So let’s call me a Macola rookie with aptitude.

I would actually be happy to hear that I was doing something wrong. Compared to software problems, procedures can be adjusted relatively easily.

Now on to your specific questions:

1) When I said ‘posting clean’, I was referring to the goal of no occurrences of items out of balance between Location/Bin/Lot layers. I have a Crystal report that I have used to audit these item balance issues. It was helpful in identifying and resolving issues last December, so I have used it as the first tool to report my testing results.

Even that is not thorough enough. I am throwing more rigorous research at the issues today. (see below)

2) I agree with your comments on the posting registers. They do say “no errors found” on the bottom. I agree that is not really meaningful with respect to the issues that I am finding.

3) I have run tag variance reports and I have staff reviewing these today. Because of the nature of my testing (approx 10% of item/bins/lots counted) there are some expected significant variances on this report.

For the most part, this report does accurately display what I would expect. It shows where quantities were entered and the appropriate variance where it should. It shows the significant volume of zero count records. But it does not correlate with the ‘outcome’ of the posting process.

i.e. One of our items ASTKAQT had two different dye lots in three different bins, so three count cards were assigned. Two cards were zeroed out and one card was entered with a quantity of 6. The count variance report accurately displays these count transactions. After posting the count batch, the item location shows the expected quantity of 6. However the bin record is absent for the count that was entered. In this example the dye lot record is correct, and does show the actual bin counted.

So to your point ‘this is what will determine the values that get posted’ OK. But what I am really curious about is the actual transactions (logic) used to post this. The Item Transaction History reports shows IM/P transactions for each count card, which look OK. And an IM/Q transaction to do the Location quantity/cost adjustment. It is underneath the hood of these transactions that I think my problems occur.


I do agree that binned and lotted is a little more complex on the surface. There certainly is more detail underneath. But yes, I should be able to track each and every transaction. It just takes a little time.

Yes we did purchase Wisys, but it is not yet implemented. We certainly have designed business process to support automation at the critical points. Today we do have a paper bound process and staff dedicated to data entry for warehouse tasks. I have designed paperwork and procedures to facilitate the recording and data entry of Bins and Lots. Our accuracy is getting better and for the most part data entry is done well within one business day.

This issue does relate to our choice of confirm shipping. By design I want the warehouse staff recording Bin and Dye lot of a shipped order. They are the only ones that can do this with an expectation of accuracy. Confirm shipping means once the order is shipped, inventory is adjusted. Today we can be four hours out. Once the process is automated this process should allow for the best opportunity to achieve accuracy and real time inventory updates.

Yes this issue is in the hands of my reseller and it is currently being reviewed by Macola Support. I have been monitoring this site for a couple months and have some respect for the quality of advice and the ‘openness’. This is a serious issue for my company and I am open to any and all feedback and opinions.

For the most part your comment regarding other customers doing physical counts with Bins and Lots is the ‘value’ that I was hoping to find on this forum. So let’s make sure that I have answered your questions sufficiently. Then I would like to ask how our procedures may or may not match with those that are successful doing this process.

Regards,

Jay

 

[JayTheGeek]

MacolaHelp,

Thank you as well for contributing.

Yes I agree that the various adjustments and the three layers do create a complex environment that can go out of balance. Unfortunately, by necessity, I have become a bit of an expert on this. We have encountered and identified several significant bugs related to Bin & Lot transactions. As a result I have created reports to monitor this “Item Balance” and my warehouse managers routinely monitor and resolve issues.

It is the nature of these bugs that motivates me to cast a wide net with this question. I can’t believe that we are the only customer turning large volumes of inventory in a multi location, binned and lotted environment. However I have personally been responsible for bringing issues to the attention of Macola support and generating bug reports or modification requests. So I feel uncomfortably close to the ‘bleeding edge’.

I agree with your comments on the various transactions. As a rule we don’t do ‘Q’ adjustments. We will do Bin or Lot adjustments to true these values against the Item-Location quantity. Then Issue or Receive if location adjustment is required.

That said, I am curious to know your opinion on the transactions used in the post process. A series of ‘P’ transactions for each count card, then a Q transaction to adjust location quantity and cost. This is not working for me. What do you think?

Your next point is bang on the money. We do include a large quantity of blank tags in our count batches. This is due to our inventory movement and the timing and logistics of creating a count batch and getting physical cards applied to warehouse shelves.

We will routinely post a ‘blank’ count card with an Item/bin/lot that did not exist when the batch was created. I think that this may be a significant risk area for us. Do others create these batches a few days in advance and trust the ‘freeze date’ to track changes?

I am carefully reviewing the reports and item transaction history for those items out of balance. It would be great to see these errors before I post, but we are talking about humans reviewing hundreds of pages of reports to catch errors. I see that as ‘role reversal’. I am far more likely to design my own query report to catch these discrepancies.

Thanks for the help. And yes, as I said above, I am working with the reseller and Macola support on this issue. I don’t ‘expect’ a definitive solution from this forum – but I would be please if that was forthcoming. (I have lots of relevant Terminal Server and SQL experience to barter with!)

Based on what I have read on this forum, I would be a fool to not ask the question. Any opinions advice and direction is appreciated.

Regards,

Jay

 

[MacolaHelp]

Jay,
I don't have any extra time tonight to put my brain around this, but I could reinvent some scenarios over the weekend if you want. Yes, there have been MANY problems associated with using lots and bins together. I was on the bleeding edge of it several years ago, and it was an uphill battle to work with exact support and programming to identify the problems and to seek correction. We also had multi location, but used hard allocations and did not confirm ship. So, my experience may be different than yours.

There are subtle differences in the program behavior based on what switches you turn on. I have another multilocation site that uses multibin, and we discovered problems during the last inventory related to trying to enter tag counts for binned items that were no longer in the bin master file. Essentially, we discovered that we needed to be sure a bin master record existed for that item with a QOH of 0. If the bin master record didn't exist, it updated the iminvloc but not the bin master. I'm not sure how it would behave with lotting added as well. Every software release can behave slightly differently due to the changes they make "under the hood", as you put it.

The P record in the iminvtrx is the count you entered in physical count processing. The program generates the Q record "behind the scenes" to adjust the iminvloc QOH to your counted quantity. When you run the inventory history transaction by item, do you have it show the bins? You should be able to see that the total amount in the bins equals the total QOH for that location. If it doesn't match, the item should be flagged on the report. This report will not have expected discrepancies on it, nor will the bin history or serial/lot history until you post. I can't remember offhand if the B (bin adj) or L (lot adj) is written into the iminvtrx file or not.

In my experience, the best way to preprint tags to distribute to the floor to tag the shelves for counting inventory has been to determine the day the inventory count will begin. Then print all necessary tags and plenty of extras using the freeze date of the day you plan to begin the inventory. This works because entering transactions on or before the freeze date affects both the frozen and current QOH. So, if you are going to do inventory on May 31, but start tag printing and distributing on May 27, any issues or receipts done between the 27th and the 31st adjust frozen and current. If you enter a transaction dated June 1, it only affects current on hand.

So, you begin counting on May 31 and maybe it takes 2 days to count everything. If items are received or issued while the inventory is being counted (and double counted if necessary), you need to be sure to enter the transaction at the time the inventory is physically added to or taken from the bin. Without bar code scanning, your timing could be off by several hours. You could have a scenario where your receiving dock gets 10 pallets of something at 7am on the 31st, but it doesn't get to the bin location until 10am. Meanwhile, the bin may have been counted at 8:30. By the time that tag is entered, macola would have thought that the bin location had 10 more pallets than your counter would have seen. This is where you need to have your procedures and timing clearly defined. And, you may need to manage this using the before and after freeze dates carefully.

Another potential pitfall: if you enter inventory on a blank tag, the tag is not associated with an item with a QOH, therefore, no variance can be calculated because it had no QOH at printing time to compare it against.

The variance reports can be run by % or qty, so you can double check the items that will give you the most pain. You can also create your own crystal, access query, etc, to do your own comparison of the various bins/lots to the total location QOH.

I think your biggest challenge is going to be trying to figure out what HAPPENED when the inventory posted, right or wrong. Then figure out what it SHOULD have been so you can determine where the process broke down. In your case, you have several places the failures could have occurred. If you can figure that out and explain it in language a programmer can understand, you can probably get it fixed. Trying to determine if it was your process or the program or a combination of the two can also muddy the waters. Until you are sure you are following the intended process properly, you'll have your hands full getting any programming changes to the software.

At the site I referenced before where the bin master record didn't exist where the item was counted, we determined that we will add a 0 QOH bin master record for this item before entering the count tag.

Hope this helps. If you have other questions, I'll try to let you know what I've encountered and how we worked it through.

 

[tpllc]

Jay,
Macolahelp has some excellent points especially with regards to the frozen inventory. It is very difficult to try and count while the target is moving. I always tell my clients to stop movements during the actual counting. Once the counting is complete, the next day normal processing can begin. I know that sometimes this cannot be done but it makes it awfully difficult to count. In environments that cannot stop movement then cycle counting is a better option. I believe in the long term cycle counting is always preferred, but that is another subject.
Do you have to use a lot of blank tags?
If so your inventory procedures are very suspect. If you remember my comment about having multi-bin and lot control made it complicated but was also a value in tracking. As Macolahelp stated counting items that have no bin record or lot record caused issues.

As you can see this can be a very complicated process. Your reseller should be very involved in this process. I would not rule out coding issues either and they will be responsible for communicating with Exact on your behalf.




Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[JayTheGeek]

Thanks again guys.

I did not make as much headway as I had hoped today. Still digging through reports and reconciling quantities and movement of times through the count cycle.

At least I can say that there are a couple consistent patterns showing up. So I am making some progress.

MacolaHelp! You are on to something; "If the bin master record didn't exist, it updated the iminvloc but not the bin master."

This fits one of the patterns that I am seeing.

So I don't want to jump to any conclusions yet. I want to be thorough and accurate. I will have a better picture of things tomorrow. I will update this thread with my findings.

Thanks again.

Jay

 

[NEmacGuy]

I am quickly reading your post, and just want to confirm, that I have run into the issue with ES and physical inventory with Serial Lot records. Basically put it does not work (or did not as of a point in time). There is an enhancement request to get it to work, not considered a bug, currently if you do a physical, any serial lots that are counted differently than the system count, must be adjusted by qty adjustments, and ot, bin adjustments as well.
If you need more info, will try to help. This was an issue in progression many years ago, but the eventualy it supported bin/lots for physcial cycle counting fully. My guess is the ES code was started when this still had not been enhanced.

 

[NEmacGuy]

There is one easy way to verify, count more of a lotted item that you have (in a test company) and you should receive a Serial/Lot adjustment screen if system says 10 and you count 12, you have to assign two serial / lot numbers, but the adjustment code is not connected to the physcial / cycle count code.
You will end up with 12 on-hand and 10 serial / lots.

 

[JayTheGeek]

Thanks, NEmacGuy.

Yes, there certainly are a few ‘known issues’ from progression that were ported over to ES. More than one of these relate to Bin and Lot processes. The example you provided will be tested again this week. I think that I agree with your expected results.

 

[JayTheGeek]

Here’s is a bit of an update for you all. It seems that the bulk of the variances that I see from my first round of testing are attributed to two issues. I have to say at this point that I feel the number of occurrences reflects on my testing procedures. So I will be reviewing and updating my test data in order to get more realistic information from my second posting.


First, and by far the predominant issue found is an extra bin and lot record with no matching quantity at the location level. These were all PO or In Transit Receiving that occurred between creating count batches and the freeze date. In my test scenario many of these were zeroed out leaving a large number of potentially unrealistic transactions. However, even my test samples of Receipts entered on blank tags resulted in these errors. I think this is the ‘real’ issue.

This certainly fits the conclusions that I drew after our first count last December. I will be reworking my test data to accomplish two things. Create more thorough and realistic data sets for these receiving items. And throw more subtle variations at this process with respect to count numbers and expected variances.

I am at a loss trying to understand the mathematics in the Q record posted following these items. But I will provide a concise example once I have a clean test.


Second, and what appears to be a real ‘self imposed’ issue for us, is the non existing bin. I think this issue can be managed with procedures. We certainly have done some clean up of zero quantity bins. It is a shame that this process does not automatically clean up associate lot records. There is an additional system utility to clean up lots.

(FYI there is an added weakness here in that you could have a zero quantity BIN record with a quantity in the associated LOT record)

My problem was in the creation of my count batches. I included zero quantity bins. So as I went through and recorded quantities on the worksheets for testing, I included quantities for several items that did not have Bin records. This certainly did expose what seems to be a known issue amongst the members of this forum. I will be sure to exclude these zero Bins from my count.


I do have a small quantity (less than 5%) of issues that don’t fit these two models. They could be items with a combination of the two issues or something completely different. Again, once I rework my test data I will evaluate these in more detail.


... on to test #2

Jay

 

[tpllc]

Jay,
It sounds like you are getting closer to the problem. This can be a very complicated issue with all the options that are in play. You are right on track with you plan to increase the complexity on a step by step basis so you can control the process. You should know what the results should be before you do the test.

As far as multi bin and lots it is complex. I'm not sure that the way that it is done with multiple tables is the best but it evolved as the code grew and I'm not privey to the decision making process that created the multi tables that can be out of balance like you described, but that is the way it is.

I'm not sure what you mean by PO or intransit inventory. The frozen inventory should work for transactions that happen after the freeze, but the counting timing is crutial. In other words if you freeze the count and prepare the tags then material is received before you count, all should be well. But if the material is received after you count and before the freeze date you will have problems as the frozen inventory will be adjusted but your count will not be updated. The basic assumption is that no transactions happen between the counting and the freeze date. By defination the frozen quantity includes all transactions that occur on or before the freeze date or posting of the inventory which ever happens first. This means that you cannot count in the morning and then receive, ship or transfer any inventory in the afternoon after the count and expect the inventory counts to be correct.

Macola doesn't do a good job of handleing in transit inventory and customers find creative ways to work this out. What do you mean by intransit inventory?

Thanks

Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[JayTheGeek]

Steve,

I certainly agree with your comments about multiple tables and 'evolved' code. (don't get me started) Systemic problem in 4GL applications that have been around a while.. (don't get me started...) Same issue in Great Plains and Solomon.

Lol

Jay

 

[JayTheGeek]

Purchase Orders (PO) and “Transit Inventory” are my two types of receiving into our stocking warehouses. Generally speaking most of our PO Receiving goes into our Distribution Warehouse. Then we Truck the inventory to any of three other branches using the “transit inventory” function. At these branches most of the inventory is received via “transit inventory”. A small portion is PO receiving at the branches. Both types of receipts have the same impact on this issue.

The critical time window for this issue is between creating the count batches and the actual freeze date. We generally schedule the actual count for four days and close the doors to the business. This will always be a Thursday through Sunday timeframe. We will spend some time Thursday morning cleaning up paperwork. I will use the Friday as the freeze date. (so for our upcoming count May 31 through June 3, with a freeze date of June 1) The count happens Thursday & Friday. Saturday is data entry and recounts. Then Sunday is my day to post and report.

We use physical count cards. These need to be printed ahead of time, distributed to our warehouses and applied to the actual bins. So I will need to create count batches a full week prior to the count; the evening of the May 23rd. I will be printing all day of the 24th and 25th in order to get cards shipped over the weekend for a guaranteed delivery the morning of the 28th. That will give the warehouses over two days to apply tags to the warehouse bins.

So even if I reduce movement by stopping In Transit and not accepting PO receiving the week of the count. I still have two days of vulnerability.

It is the receiving on the 24th or 25th that will cause the variances. And logistically speaking these will be two busy days as a result of the shutdown of business the following week. All our “transit inventory” trucks will carry more stock than usual. And our distribution warehouse will likely receive twice as many containers as they would on a normal business day.

In this example the 28th, 29th and 30th would still be active business days with customers and shipping, just no receiving. I still need to test if internal Bin to Bin transfers will have an effect on my inventory variances.

Here is the preliminary conclusion from my testing:

If any receiving creates a new bin or lot record, which was not assigned a Tag No during count batch creation, there will be a variance. Macola will correctly adjust the Location Quantity when the item is received. However when posting the count batch (‘Q’ transaction) it will use the Location Quantity on hand adjusted by any variance between the sum of count tags and freeze quantities to create the new Location Quantity on Hand. The resulting records for that item will show a variance between the location quantity and the Bin & Lot quantities that is equal to the quantity of my receiving.

It does not matter if I count the received goods or not, I still end up with a variance. It only changes which set of numbers is correct; the Location quantity or the Bin/Lot quantity.

I will follow up with specific examples.

 

[JayTheGeek]

Well, I have also confirmed that bin to bin transfers are a problem. If product is moved from a bin with a Tag # to a bin that was not present when the count batch was created, it will result in a record out of balance between Location, Bin and Lot quantities.

 

[tpllc]

Jay,
It is starting to look like a bug in the code. Have you discussed this with your reseller or do you have support directly with Exact?
The way you are describing things it looks like what you are doing is correct and in the correct order. The problem seems to stem from their not being a frozen tag for the items that you have received after you opened the batch and before the cut off.
When you count the items that are received after the creation of the batch I assume that you are using a blank tag? I also assume that on that blank tag you are recording the bin and lot information?
The receipt of the goods should create the lot and bin records so one would assume the posting of inventory would find these records.

I have read your procedure and I have a sort of off the wall suggestion, since you basically shut down before you start counting and process all of your transactions before you start counting why not forego the frozen inventory option. That is really designed to solve the problem of needing to start transactions before you can post. In your example you review and post on Sunday so there really is no need to use the frozen option. This way the changes in the acttal inventory at the time you post will be used to calculate the delta between the count and the perpetual.
I would suggest that you test this but I'm betting that it will work. Let me know.


Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[JayTheGeek]

Thanks again Steve for validating my situation. 'Yes' to everything in the top half of your post.

Yes this was posted to Exact's support the same day as I started this thread. Other than phone calls to the reseller, Exact has yet to respond to that post. My reseller has been responsive and active on this issue.

To freeze, or not to freeze...

I was looking at this yesterday. Of the four test batches that I created (for our four stocking locations) two had the freeze turned on and two had the freeze turned off.

At a summary level; I see no significant difference in the number of occurrences or the nature of the problems resulting from the post process in these two scenarios.

Today I am going through to clean up a few of the test items and repost. I will certainly take a more thorough look at the posting results for clean samples utilizing these two different options. I agree that logically it might make sense to no use frozen values.

I'll let you know what we find.

 

[tpllc]

Jay,
I'm gald to hear that your reseller is being proactive. I have always found that Exact support is most helpful when I can provide a very concise defination of th eproblem and a good explaination of the testing that I have done to prove what is actually happening. There are so many different configuration possibility that it is some times difficult for them to replicate your situation. It loooks like you have a good start on the documentation. I'm very interested in the result of the non frozen inventory results.

Thanks

Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[NEmacGuy]

Jay,
Glad you are getting somewhere. Do you have a variance report of Item Location to Bin to Serial/Lot_Bin. The last time I ran into an issue like you, I used crystal to report only the items that were in variance.

Best of luck