Part 3: Inventory Management

By: Mike Catalfamo

Over the past few weeks, we have been looking at some of the pitfalls you might encounter during an integration project and I have provided some ideas about how to approach them. If you haven’t read the earlier posts yet, you can start with the first post here.

My goal for this post is to highlight some things to consider about inventory as it pertains to integration. Selling ice cream is not the same as selling clothing which is not the same as selling books or services so I will try to keep this high-level. If you have any more specific questions or feedback, I would love to hear from you.

Updated: Since posting, I received some feedback that I didn’t provide some clear tips about how to handle unit/pricing conversions. Comments and feedback are encouraged!

• Try to ensure that your selling units of measure match your stock-keeping units of measure whenever possible.
• If you only ship full cases, try to ensure your customers order by the case.
• It’s very possible your trading partner will not pay your invoice if the unit prices don’t match exactly what was ordered, particularly in edi relationships.
• Speak with your accounting department to determine the number of significant decimal places used by your ERP/accounting system.
• If you need to sell in different units than you keep stock, work through some example orders and do the unit conversion calculations on prices to see if any rounding issues arise (more than 2 decimal places produced). See the example above.
• Speak with your accounting department if necessary to see how they will expect rounding issues to be handled.
• Ideally your ERP system will be able to manage the unit conversions since it will be more likely able to handle this without issue. If your ERP can’t do unit/price conversions AND you can’t get your customer to order product in your stock keeping units, the calculation may need to be done during the data transformation step of the integration process which may require some assistance and collaboration with accounting regarding the handling of the lost or gained pennies.

1) Who’s the Boss?

No, I’m not talking about the 80’s sitcom. This might be obvious but you should make sure any item changes are made in one system only (master) and pushed to the second system. If you start changing items in both systems, you completely lose track of which items are most up-to-date. That said, as a best practice, the master should be your accounting/ERP system.

2) Item Matching

The item/product ID in the web store is not likely going to match what’s set up in the accounting system since the web stores usually assign a unique product identifier. This is a critical field to match on when creating the order and creating separate item SKU numbers for the same part would be a nightmare for managing inventory. Your best bet is to include your accounting SKU in one of the item extra fields in the web store so it can be used for matching during integration. If there isn’t a field in the web store, make sure your Integration Platform has cross-reference tables to manage this for you.

3) UoM / Price Conversions

Do your selling units of measure match your stock keeping units of measure? If you inventory in ‘eaches’ but sell in ‘cases’, you’re introducing complexity, perhaps unnecessarily. This tends to be more of an issue in edi relationships than web store ones since your trading partner will expect invoicing to be in the same units that were sent on the original order or you won’t get paid. While the unit conversion itself can be done using cross-reference tables in either the accounting system (if it’s supported) or during the data transformation, the unit price conversion can be problematic due to rounding issues.

Example
Sandy keeps stock in ‘eaches’ (units) of books but one of her trading partners purchases and get invoiced by the case. When posting the order, she needs to convert 1 case to 12 eaches to keep inventory straight in her system. Let’s say that the trading partner pays $70 per case. To post the order, some arithmetic needs to be done to convert from a case price to a unit price ($70/12 = $5.8333333333333333333333333333333). If the accounting system only accepts 4 decimal places, we round to $5.8333. When it comes time to invoice, the trading partner is going to expect to see 1 case for $70 and if we convert 12 eaches at $5.8333 back to 1 case, that totals $69.9996, not $70. This might not seem like a big deal since $69.9996 will round up to $70 but I have encountered this as being an issue more often than not.

Sometimes the business relationship requires you to do this but you and your sales reps should be aware of implications of selling in different units than you keep stock when negotiating with new customers. You should also speak with your accounting system consultant to determine if the accounting system can handle this for you more elegantly.

4) Item Attributes – Let’s say you’re selling a women’s black v-neck t-shirt and a women’s white v-neck t-shirt in small, medium and large, how many SKU’s should you have in your accounting system? Let’s break it down:

Black – Small
Black – Medium
Black – Large
White – Small
White – Medium
White – Large

I think obvious answer is 6, so why the question? Well, some accounting systems allow you to set up one SKU and separate things by lot or use item attributes that are sub-items of the main/master item. This should work fine if your accounting system allows you to manage discrete quantities for each variation and preserves this in the back-end data import/export process. One thing that will always work is to set these up as completely separate SKU’s and apply some structured naming conventions.

Carrying on with the t-shirts, there would be 3 components to the SKU number, the style, colour and size so we would end up with something like the following where the base SKU is the same for all the items since they’re all women’s v-neck t-shirts:

Women’s V-Neck T-Shirt: Style = 1043
Colour – Black = “BK”, White = “WH”
Size – Small = “SM”, Medium = “MD”, Large = “LG”
1043-BK-SM
1043-BK-MD
1043-BK-LG
1043-WH-SM
1043-WH-MD
1043-WH-LG

With this setup, you won’t have problems if you ever change ERP systems since the items are already broken down to their lowest-common denominator.

Next time we’re going to look at Order Completion and wrapping up with some final thoughts.

Part 1: Introduction
Part 2: Creating Orders Automatically
Part 3: Inventory Management
Part 4: Order Completion

Article by Mike Catalfamo

Mike Catalfamo has over 10 years' experience delivering integrated eCommerce and EDI solutions and is the Technical Sales Engineer at eBridge Software Inc. When not chasing around his toddler, a few of Mike's interests include playing music, photography, reading, practicing GTD and exploring ways to make the most of technology to integrate and automate information and systems.

EDI Integration, Technology Corner, Webstore Integration, eCommerce eCommerce, ecommerce integration, Knowledge, Mike C, Series