The Color-Size Conundrum: Rapidly Setting Up Items with Color and Size Attributes using Microsoft Dynamics NAV with LS Retail
In the first blog of this series, The Color-Size Conundrum, Matt Street outlined different ways manufacturers handle the problem of tracking color and size combinations for their merchandise. This blog offers a demonstration of how to streamline this process.
For retailers, tracking items by the most detailed level possible — color and size, for example — results in more effective and profitable business operations. But for most software systems, the effort required to set up and enter data for this level of detail is painful and time-consuming.
ArcherPoint is constantly working on ways to make these inventory management processes easier. Join ArcherPoint's Wm. Matthew "Matt" Street, Retail Operations Lead, in this informative YouTube video, where he demonstrates how to streamline this process. In the video, Matt shows how to set up and track items by color, size, and vendor, including barcodes, prices, and more, using Microsoft Dynamics NAV and LS Retail.
Below is a transcript of the video:
Hello, my name is Matt Street and I’m the Retail Operations Lead at ArcherPoint.
Today we’re going to explore rapidly setting up items with color and size attributes. We’re going to build these items from a subscription that we found of data of vendor information. First let us establish what we’d want to track at the color-size level. Obviously what we sell to our customers, we’d like to identify what sizes and colors we sold, what quantity on hand we have at each store and in our warehouse, the barcodes that are associated with those individual color-size items, the cost from the vendor (because we know that some colors and sizes may cost different than others), prices to the customer, and also replenishment rules – what sizes and colors do we want to stock more of, or less of, at each of our individual stores.
There are a lot of retail systems out there that have the features and functions necessary to track these items that we just identified. But that’s usually not the issue when it comes to tracking information at this level. Usually it’s the amount of set up data entry that makes tracking items at a color size level painful to a lot of retailers.
At ArcherPoint we concentrate on making client’s processes successful rather than just showing them the features and functions of the software. So we look for a way to make this process of setting up and maintaining items with color-size attributes quick and easy.
So let’s start our demonstration of how to set up items with color size.
We’ll start by launching our ERP system, our retail system, which is Microsoft Dynamics NAV 2013 with LS Retail. We mentioned before that we had a subscription service of item information and color-size from a data-source. And how we go about bringing that in is to what we call this holding area. And I’ll get this started and explain a little bit more about that.
We’re going to type in an effective date for our pricing purposes because we can also bring this information in to update items with their prices. And then we’ll select the file that was sent to us. This subscription, I believe, comes on a monthly basis, and it will take just a little bit. So let’s take a look at the actual data file, I have an open copy of it here. You can see that it’s a lot of data. It is 162,000 records of individual SKUs, with all their color-size information, descriptions, classifications, the vendor that it comes from, etc. We bring it into this holding area and we’ll talk a little bit while it’s still working.
We bring it into this holding are for a couple of reasons. One, it’s not our philosophy for you to have data from an external source come into our ERP system and affect the real items. For example we don’t want items created that we didn’t intend to create. So we’re bringing it into a holding area where we can manipulate, search, change it, etc., before we really turn it into an item in our system that we’ll sell to our customers. The second reason is that we may have different forms of information that we want to bring in, so bringing it into a common holding area allows us to take multiple different files that differ slightly for example have different fields in them, different columns, maybe have slightly different meanings for fields, and allows us to bring it into a custom place. So from this point on, it doesn’t matter what data source we got it from, as long as we were building this information and we mapped its data to this.
So you can see that we have our 162,000 records that we imported into here. At this time I’d like to be focused on getting in an item into our system ready to be sold, ready to be purchased. So we’re going to go through and look for a particular item. We’re looking for a particular item that was from the Cherokee vendor code, and we are going to look for a particular style, 1067, which should be in the pant category.
You can see that we have all these style 1067s with their different color-size values. You can see that pricing changes, depending on some of the size values, some of the plus-sizes. You can see that we have different UPC codes, color descriptions, we’re bringing in the detail descriptions, etc., so we can look at this information. It’s all part of the same group because we selected just that. We’ll select that and tell it that we want to convert this raw data that we got from the vendor into our next step which is our Vendor Catalog or what we call a Vendor Item Library.
So we’ll look at this Vendor Item Library record. You can see that we have information about the item. A lot of this was defaulted from the product group code that was associated with pant type that we had in the raw data. And we have all our detailed options in a structured form. Along with the data of all the options of all the color-sizes it brought in and structured the unit cost and unit prices, references and so on. And from here we can take the final step and say that we want an item generated from this. So in this case we have a lot of options, most of these again default from the product group that we associated with the type of goods that the vendor told us a pant. We’ll say OK here.
Now you can see the difference between a vendor item library record that has an item associated with it and notice that these are grayed out and I can no longer change them, because if I want to change values I need to do that on the item itself. So this is our item card, this is where the item becomes a real item that we can sell at our POS, or on our website, or through a sales order. This is the base level information about the item, what you particularly would say is associated with the style. This is what controls all the color sizes, in other words, what gives them their defaults, right? So this item can be analyzed at the style level, and we can track things at the color-size level which in our system we call variance. So, if we look at a few of these, and look at the purchase prices, which came from our file, which would be the price that we would pay the vendor for this particular color-size within this style. We have all our 81 variants, 9 colors, 9 sizes here. Notice that the price is changing depending mostly on size. 4X and 5X cost a little bit more than the 2X and 3X, that cost a little bit more than the large, medium, and smalls. So those would be our purchase prices. We could also look at our sales prices, and these would be our suggested retail prices. The subscription service has the ability to do some calculations with this. So you can do profit margin pricing, or calculate unit prices based on the cost plus a profit percentage, and so on, with a couple other methods.
So that you aren’t stuck with the price that the manufacturers give, you can do some massaging to that based on your preferences and pricing mechanisms. So these are the prices again, all 81 variants, different prices for the 4X and 5X and within the different colors. We can also look at a list of our bar codes. So this would be all the barcodes in order and what color size they represent. Very similar to that is this information in the variant framework. This adds a little bit of additional information, but I think the key here is that our one little button on the Vendor Item Library set up all this information for us based on the information that came from the file.
So here we have this column called Valid, so if we bring in this information, and you brought all the color-sizes into an item, and at some point in time or initially you decide that you’re not going to be selling certain colors or certain sizes you can just uncheck these boxes and then they will no longer be treated as sellable items or purchasable items. But you can always come back and turn them back on.
We also have these things called dimensional weight. The dimension that this is referring to is the variant dimension, where dimension one is color and dimension two is size. So we can actually specify weights that we want to give the various color and sizes for when we are ordering in bulk it will allocate and recommend the quantities you should buy of each color-size based on these weights. Of course you can always change them, it’s just a starting place.
So the other thing I want to show that we set up with the one little button on the Vendor Item Library creation is the replenishment information. In this case we have about 26 different location codes to find plus the 81 different variants within this one item. So you can take 26 times 81 and that’s how many records you would be looking at here. It allows you to essentially have a grid of each combination and set the reorder points and maximum inventory for each one of those. And this data is actually dependent on what you choose here. And I’m showing the most basic level of replenishment that we do. We can do manual estimates, we can analyze average usage and recommend quantities to replenish with. We can do like for like which says for every one you sell we will replace it, or we can do demand plan which is an advanced forecasting system that does its plan outside the system and then brings it back into this to give you a little more advanced statistics on how you’re doing replenishment. So the key here is how we set up the structure and we can change things based on any combination of variant.
It’s not likely that you’re going to stock the same number of each color and each size in each store. We have clients that do that at the store color-size level and every value can be different. So by changing to at least allow them to set up the initial structure for it and just change the values that we would like these to be in any way that you want we can give them a leg up on the data entry for this. This could also be copied out into Excel and they could use a standard Excel spreadsheet to change all these values or in fact any of this information, and then paste it right back in.
So now that we’ve seen our item and we’ve seen what was set up from the one button that we pushed on the Vendor Item Library record, let’s go and see what we can do with it.
So what we’re going to do is go create a new Purchase Order for this item. We have one vendor in our system. So we’ll set this up, we’ll put in our single item that we have generated in the system. That information comes back, and now pay attention closely when we type in the quantity here: As soon as I tab off that field, it’s going to bring me up a matrix and you can see all nine colors across the top, all nine sizes down the side. And if I want to make adjustments, and say that we need a few more of these, in these sizes, and all these colors. The same thing for down here with the mediums. We can do that and it will automatically adjust our quantity up here, and if we needed to get back to 200 for budgetary concerns we could subtract out some items there. So it’s doing its little calculation, and now we see that we’re going to be ordering 228 of this style and if we want to see the breakdown of color-size then we can always bring up this matrix and adjust it.
So just so we get some inventory into our system to sell, we’ll go ahead and post this. And normally you may receive an invoice independently of one another. For convenience, we’ll do everything at once and pretend. So it’s posting each of the 81 lines even though it looks like one line here it’s really 81 lines behind the scenes that it’s working with.
So our Purchase Order is now posted we can go back take a look at this item just see if there’s anything different about it and, yes, we now have 228 on hand. We can go look at the quantities and retail availability by variant and that will show us that we currently have 2 on hand, 0 on order, and 2 available to sell. If we want to see where those two items are stocked at we can come look at all our various locations and see we have two in store one.
Ok, so our next step is to see if we can sell this item. We could sell it through a sales order, through an e-commerce site, or we could sell it through our POS.
So for this next demonstration we’ll use our POS. Log on with our staff ID, and we’ll paste in our various barcodes here. So here we’re ringing up the large royal blue. We’ll scan in this barcode and see that his is a 2X, and it has a different price than the large did, same style. And finally we’ll put in a 4X item and see yet again that price is different. So we’ve sold three of our items, we’ll go ahead total this, pay cash, say OK.
When it posts, we can see a virtual copy of the receipt over here, and we’ll log off our POS. Go back and look to see what has happened to our item now. And we were at 228, we’ve sold three, and now we’re at 225.
So that’s the end of today’s demonstration. It was meant to show just how quick we could set up an item with a rather large amount of variants. We could’ve used our data file for our vendor and set up many more. We could’ve also created a Vendor Item Library with hundreds of items all at one time and then gone into the Vendor Item Library and created items with all their variants, all the prices that came from that library data, set up the replenishment data, costs, barcodes, etc.
I hope that was an informative way to set up items using our systems.
Thank you for listening.
Author: Wm. Matthew Street, Solutions Consultant/Retail Product Lead at ArcherPoint
Ready to learn even more about improving your retail operation? Schedule a call with one of our retail experts today!