One of the most powerful features in eSellerPro is its ability to manage Kits. Kits are made up of multiples of a single SKU or multiple SKU’s but taken together produce a new product and one which can be unique.
In ecommerce unique products are like gold
Kits in eSellerPro are very cool and John knows first hand how powerful the use of these kits are, but there is one part of the kits that is not ideal, updating the kit prices. John shows us how to tackle this challenge using a little excel magic.
Before we dive into how to tackle updating kit prices in eSellerPro, John is absolutely right, In ecommerce unique products are like gold and we cover over free or inexpensive ways of leveraging kits aptly named Podcast sell more with the same products and this can work for anyone whether they’re using eSellerPro or not.
eSellerPro & Versatile Inventory
We were able to create an inventory system in eSellerPro that enabled users to deal with the complexities of inventory management, multiple sales channels and the requirements needed for larger businesses.
The outcome of this is what you see in eSellerPro today and while we’re focusing on kits, they make up just one part of the inventory system, there are several inventory types in eSellerPro, such as Master SKU’s, Listing SKU’s, Stocked Sub SKU’s, Variation SKU’s, a rare type called Holding SKUs and Kits which we’ll focusing on in this article.
eSellerPro Kit Products
One of the benefits of using eSellerPro for kit products is its ability to manage the stock which makes up the kit. In short kits are amazing.
It’s possible to sell individual SKU’s [say a camera case], as well as Kits of SKU’s [a camera and a case] and eSellerPro will keep the stock accurate. Thus it’s possible to hold say just 10 SKU’s yet make up 100’s of unique products (or close to a million in one case).
The Downside of Kit Products in eSellerPro
While eSellerPro does an excellent job in keeping the stock levels correct, so you don’t oversell every 2 minutes with complex inventory, but it does not count the individual SKU costs in the kit. This is where the guide that follow steps in and makes it easy for you to do so.
So if your selling price is based on the item costs there will be manual work to do in calculating the cost price of the kits. For many of you, your suppliers regularly change pricing then this addition work can seriously negate the benefits of kits. This is especially true if you use formulas to calculate selling prices in your Channel Profiles. Additionally if you post accounts to accounting software your cost of goods lines may be incorrect.
Thanks to John, Help is at Hand
If you are familiar with importing and exporting in eSellerPro then there is a simple way in which your kit costs can be updated.
Here’s one way how to do it and if you know a better way let us know!
Step 1 – Backup!
Before you start export your kits and save the export somewhere safe.
(You can do this using the process explained in step 4)
Step 2 – Create the Layout
Now create an Import/Export Layout with the following columns:
An example of such a layout is below, with the key areas highlighted.
Step 3- Search, Filter & Export
Sort your inventory by ‘Kit Parts’ and export these with this layout. Save the export spreadsheet. Lets call it “StockNumberCostEach.xls”.
A screen shot on how to filter for just the kit parts is show in the screen shot below.
Step 4 – Export the Kits Using the Standard Kits Layout
Using the search filter in Inventory, search for the Product Type: “Kits” and along top press the Excel icon. Now export these using the layout shown below:
The resulting spreadsheet will have columns with Stock Number and Quantity for all the kit parts in your kits.
Step 5 – Crack Open Excel
We are going to use Excels VLOOKUP function to find the individual parts prices and add them up.
Once you have your Kit Export Spreadsheet you will need to copy the StockNumberCostEach.xls spreadsheet to a new worksheet in the Kit Export Spreadsheet.
You can then use the VLookup formulas in the following download to calculate the kit pricing. A typical cell formula is similar to this:
If we just lost you, don’t worry, John’s provided us with an excel spreadsheet ready for use. You can download this spreadsheet, with the formula’s included here:
This spreadsheet assumes your kit part pricing export has been copied to a worksheet called “Part Pricing” and it has formulas for kits with up to 17 parts. (scroll to the right if you can’t see them).
Add the data from the two exported sheets for the kits and the kit parts and enter them into the sheet above.
Note: If you have kits with more just follow the formulas given and don’t forget to copy the results and use “Paste As Values”. You can then delete all the columns except the StockNumber and final Prices.
Step 6 – Import Into eSellerPro
Then simply import the spreadsheet using your StockNumberCostEach.xls layout as the “Import Kits” option on the same tab as shown in Step 4.
Step 7 – Create a Support Ticket
Finally – Send eSellerPro a request to add automatic updating of kit costs to their development schedule.
(this was John’s suggestion and I’m leaving this in, I laughed very loudly when I first read his guide, I hope you do too!)
Updating Kits in eSellerPro Summary
Kits whether in eSellerPro or not can give your business the edge and also help customers by combining products into new products so the purchasing process is a lot simpler for them.
The base of this article was provided by John Hewitt from Bamford Trading, an eSellerPro user since 2006. I’d like to express my thanks to John for sharing this with us and if you have any questions, just pop them in the comments box below.