Updating Kit Product Costs in eSellerPro Automatically

In ecommerce unique products are like goldOne 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:

  • StockNumber
  • CostEach

An example of such a layout is below, with the key areas highlighted.

eSellerPro Custom Export Layout

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.

eSellerPro Filter Kit Products

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:

eSellerPro Export Kit Products

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

Excel Worksheet for eSellerPro Kits

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.

9 replies
  1. Rik Powell
    Rik Powell says:

    Really good thanks! Do you use a unique barcode for the kit, if you wanted to sell on Amazon? Is it easy to make kits in the first place on ESP? I think Matt you showed me something similar with the pink glitter photo frames you liked?!!! Best wishes as usual, Rik.

    Reply
  2. ESP User
    ESP User says:

    Sadly the Kit side of ESP is not as up to scratch as would be believed. Exporting (in the example above) works fine, however exporting a very large number of items causes the export to fail. ESP will throw a dialogue stating that the file export is complete however no file will be created.

    As a result smaller exports must be done individually before combining into a larger spreadsheet. This has been raised numerous times with them to no avail.

    Reply
  3. The Emporium Direct
    The Emporium Direct says:

    Great post Matt and John, Very clever and something I personally agree with very much.

    Couple of things!!

    1. if you want to sell your kit on Amazon do you apply a new barcode, one would have to aquire a barcode from GS1 or similar?
    2. please can you produce a MO/Bamford Trading style easy to implement video on how to create kits in ESP?

    Best regards to you both as usual.
    Rik

    Reply

Trackbacks & Pingbacks

  1. Updating Kit Product Costs in #eSellerPro Automatically http://t.co/UwrEQJXJ <= Do you use kits & struggle keeping the prices right?

  2. Updating Kit Product Costs in eSellerPro Automatically http://t.co/x908GvTE via @lastdropofink

  3. Updating Kit Product Costs in #eSellerPro Automatically http://t.co/XnvIdaDl <= Do you use kits & struggle keeping the prices right?

  4. Updating Kit Product Costs in eSellerPro Automatically: One of the most powerful features in eSel… http://t.co/rWRin6RD @lastdropofink

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *