Company Z is a candy distributor where I have worked for a year or so. This company has two sets of major holiday seasons for which it prepares. These holidays require the company to take in a massive number of samples from all their vendors. Those samples are labeled and sent to the sales people or to one of their two showrooms. The first two holidays are Halloween and Christmas, for which we get in around 1,600 plus items (and growing). The second pair of holidays are Valentines and Easter, where we usually get in 850 plus new items (and growing).
We usually ask for at least twelve of each item; or if it’s a bulk item 6 LBS of it. When I joined the company, they knew about my process improvement background and asked that I do things their way first before I start to optimize anything. Their way of producing labels involved a desktop HP printer and a word template to create labels.
Normally, manually entering in labels for general office use would be great, but let’s look at the math here:
For Christmas, which is where I started doing things the company’s way, I or my temporary staff had to manually mark at least 7 of each incoming item. On rare occasions, I only needed to mark one item, depending on the size of the item or other circumstance.
7 x 1,600 = 11,200
That’s a lot of typing and or copying, pasting and printing. It took me almost 30 hours to create all the labels the first time. That’s time I could have been out helping my team work the samples or helping with the various marketing tasks assigned to me.
The Problem:
- Producing the labels quickly so that I, or the temporary staff assigned to me, can work the samples as they come in.
- Labeling all the items and having them ready to ship or put in the showroom in two months’ time.
- My hands started to hurt from that process
- Extreme monotony and repetitiveness.
The Solution:
At first, I suggested getting another printer for printing labels like the one used in the receiving area. I encountered some resistance from the company in trying to accomplish this as there were some factors that kept the company from pursuing this solution, one of them being the cost of a new printer. The printer would have paid for itself in a years’ time. The other was a lack of understanding on how to use their ERP software to edit the printing size of the labels it created. The conclusion was that the company didn’t want to take the risk.
Needless to say, I didn’t stop there.
If these were the only tools I was going to get, I knew I had to “MacGyver” my way through this. Valentines and Easter was quickly approaching and, in spite of the fact it has fewer items than the previous holidays, I was not about to put myself or my team through that again.
I researched the issue over the next week and found that the mailings tab in Microsoft Word could be used to create labels. I also found that I could import data from Excel using the select recipients list. I then knew that I could copy and paste three pieces of information I needed for multiple items from the sample requests, since they were given to me in Excel.
I started in Excel to create the following workbook.
If these were the only tools I was going to get, I knew I had to “MacGyver” my way through this. Valentines and Easter was quickly approaching and, in spite of the fact it has fewer items than the previous holidays, I was not about to put myself or my team through that again.
I researched the issue over the next week and found that the mailings tab in Microsoft Word could be used to create labels. I also found that I could import data from Excel using the select recipients list. I then knew that I could copy and paste three pieces of information I needed for multiple items from the sample requests, since they were given to me in Excel.
I started in Excel to create the following workbook.
I would copy and paste the data into the first tab and then input the number of labels I needed for each one (which was almost always 8). I then put in a sheet of blanks to separate the different vendors from each other.
I had some issues writing the code in VBA, but presented my question to stack overflow to my developer friends. Thanks to a response by Doug Coats, I have a working script that will populate the second tab with multiple copies of the data so I can import them into word.
After a few test runs, I can now hit print and go do something else for the next fifteen to twenty minutes, so long as the printer has enough ink and labels.
I then clip them to the sample request and wait for the samples to come in.
Results:
- 15 hours of research and development will save me at least 23 hours twice a year
- Increase my availability to help my temporary staff check in the samples
- Sometimes the samples come in waves, so if there are no samples for whatever reason, the extra time allows me to create email campaigns and design advertisements that help to increase sales.
