Infor Visual ERP Delivery Partner


Infor Services Partner


How can Jigsaw Puzzles help you reconcile Inventory in Visual ERP?

I am sitting at my desk looking up at the bookshelf which is filled with jigsaw puzzles. And I started to reflect. I have had a love relationship with jigsaw puzzles since I was very young. My mother claims she gave my 3 sisters and me puzzles to do to keep us quiet. But she may not have realized, she was teaching us problem solving skills.

Now today I believe that I use those skills when I reconcile inventory in Visual ERP. Let me explain.

1. With a puzzle, we start with the edge pieces. We collect them and them put them together thus giving some structure. With inventory, I start with General Ledger balances versus the Inventory Balance Report. This lets us know what accounts are out of balance. Seems like a good place to start.

2. The next step is grouping the pieces by colour. I like to start with the bright colours – red, purple, green, yellow or orange. With these colours, I am able to create little mini-puzzles by grouping them together. I will make the little cluster and then put them into the big puzzle. With Visual ERP my next step is digging into more information about the General Ledger. Here’s some of the things I look for:

a. Are there any postings from invalid journals? For inventory accounts, I would only expect to see postings from the following journal types: Purchases, Work In Process, Finished Goods, Shipments and Adjustments. If there are any other journal types they are wrong and need to be reversed.

b. Are there any unposted entries? These I can find with some scripts. They may be years old but we still need to address them because inventory is an accumulation of ALL activity from the beginning of your database.

c. Are there PO’s using inventory accounts? We can use Vendor Inquiry to find these or a script can also find them quickly.

d. Has an inventory account been used as the Inventory Adjustment Expense Account? This would mean the entry both debited and credited inventory, thus having zero impact to the general ledger. But when it comes to the subledger (inventory list), a new costs layer has appeared (adjust in) or disappeared (adjust out).

Think of these general ledger checks as the different bright colours or more obvious balancing issues.

3. Now we are starting to see the puzzle take shape. But there are lots of gaps. These usually are the lighter colours which are more difficult to put together. In Visual ERP, these are the Inventory Layers. Here’s some of the things that I do within Visual to uncover layer problems.

a. Inventory Valuation – Negative Balance Cost Layers. This report shows us instances where the part on hand quantity is not in sync with the unused inventory layers. These can typically be fixed using utilities that come with Visual.

b. Cost Distribution Exceptions – This one is looking at layers to see if there are connected FIFO layers that don’t have the same cost. In this case, these can be fixed with costing tools.

c. Cost Analysis Tools – These are for each manufacturing journal type to ensure the manufacturing side is in sync with the financial postings. Many times, all you need to do is set the Posting Candidate flag to Y to force Visual’s costing to relook at these transactions.

I have to admit that when I first started working with Visual these were the main things that I would look for. As my puzzling skills have matured, here are some of the things that I look for now instead.

d. Incorrect On Hand Quantity – This compares the transaction quantities to the quantities for each part.

e. Bad FIFO Layers – Instances where the quantity used for costing is greater than the transaction quantity. This is rare but does happen once in a while.

f. Out Layers Not Costed – This could be caused by negative inventory but there are a few other potential causes which I dig into.

g. Negative transaction costs – This shouldn’t happen but there are some rare instances that could cause this anomaly.

4. After we get all the layers (or the colours) sorted it is time to make the entire puzzle come together. Since the colours haven’t allowed us to complete the masterpiece, it is now time to sort the pieces by shape. One knob with 3 holes, two knobs with two holes, three knobs with one hole, etc. You get the gist. But they all need to be organized. We now look at the shape of each piece versus the open areas on the puzzle.

With Visual ERP this is where it can get a little tricky. What are the shapes? Now I look at the postings by part ID by journal type. This series of rather complicated queries allows me to know which part, which journal type and which transaction is causing the imbalance. Each one is like a separate puzzle piece. By looking at this level of detail I can figure out how this piece of the puzzle fits.

So, my mother thought she was just keeping us quiet but boy she sure did teach us a lot more. Puzzles, Visual ERP. They are magic. I can spend hours working on my puzzles. They bring me so much joy.

As I reread this, I feel like I haven’t given you all the answers for doing puzzles. If you want to know a bit more about the fixes please go to THIS LINK where we have the detailed steps for some of the puzzles. Happy puzzling.