In Part 1 of this mini-series we looked at the most common forms of overflow errors and examined the causes and troubleshooting methods for those. But I also mentioned that we had recently received several data files for 'analysis and/or repair' that contained a different form of overflow error unlike those commonly seen in the past. I challenged you to think about the various 'value' fields commonly associated with overflow errors, and then identify the potential field we haven't looked at. Well if you have been scratching your head and telling yourself that you 'didn't have a clue' as to the field, then I am just going to tell you and then show you the culprit.
While overflow errors can involve 'computed values' like the example I gave you last time in which 'cost', 'quantity' and 'unit of measure' all combine to exceed the limits of a field, we could see that it required a remarkable 'comedy of errors' almost to force QuickBooks to compute a value that was an 'overflow'. Well this uncommon form of overflow error also involves computation, but computation associated with a single field and a single value. That field is the Item 'Average Cost' field.
Item with Overflow Average Cost
Now that I have told you, I know that you are wondering how you would ever identify this field as the source of the corruptions preventing the Verify utility from completing successfully, and causing the overflow error warning message (shown in this article's headline) to be displayed. It might seem that you could see this error from the item list, sorry the Average Cost isn't a field displayed there. How about in a report, like the Inventory Valuation Summary, well Average Cost does appear in that cost, but only if the item(s) with this error are active, inactive items don't show up in the valuation. That takes us back to our old friend the 'Add/Edit Multiple List Entries' feature found in QuickBooks Accountant (Desktop) versions. You can add the 'average cost' field to that list and if you move the columns around to display only the Item Name/Number field and the Average Cost field, you will get a list that looks like the one below.
Overflow list via Add/Edit Multiple Item feature
It's easy to select Inventory Items, using this feature, and look at either All your items, only your actives, or your inactives, and sort the list by the Average Cost column. You will quickly find the errors. You should do the same thing with Inventory Assembly items in case the computation error impacts an assembly's average cost.
Fixing the error is pretty simple as well, you simply need to make an 'Inventory Adjustment', but there are a couple of unique things in the approach to doing this. You want to be certain to adjust the overflow as of the date it first appeared. In order to do this you need to run an Inventory Valuation Detail report for the item in question, if it is inactive you will need to make it active to include it. When you identify that the 'cost' of a transaction becomes either irregularly skewed in amount, or shown as an overflow, then that is the date you want to us. Note, this could impact your financials for that period, and subsequent periods, you therefore want to insure you have made a back-up of your data immediately prior to making the repairs below.
You also need your adjustment to be a 'net sum gain', so this is one of the rare occasions in which the 'adjustment account' should be the same account as the inventory asset account associated with the item. This will insure that both the debit and credit to re-set the overflow to a 'real value' will zero out within your inventory account. Now make an adjustment of both quantity and value that you compute as the actual cost multiplied by the quantity on hand at the time of the transaction. This may require a little research, but it probably isn't anything that you, as a skilled ProAdvisor, hasn't done before when auditing a client's inventory over a fiscal period. When you save the adjustment, the result should be a change in the average cost to the actual value as of the date the item went into overflow status.All subsequent historical transactions for that Item will be re-valued as of the repaired average cost.
Now you can identify, track down, troubleshoot and repair this unusual form of overflow error, just like the good data detectives I know you all are. Of course, if you want to see this overflow issue, and how to troubleshoot and repair it 'live', be sure to sign-up for one of this fall's Woodard Master Climber Boot Camps.