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.

 

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.

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).

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.

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:

• StockNumber
• CostEach

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:

=(VLOOKUP(B3,’Parts Pricing’!$A$1:$B$671,2,FALSE))*C3

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:

http://lastdropofink.co.uk/kits-Formula.zip

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!)

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.