Cost of Goods

[ssheldon]

Hi everyone!

Just downloaded the spreadsheet and I really like it so far. The one thing I would like is to be able to enter my Cost of Goods before the item sells. Almost all of my products are one-offs, purchased from thrift stores, online, etc. By entering the product cost right after listing I can file away my receipts as many items take months to sell and I would never remember what I paid at the time.

Is there a way to add the Item Purchase Price, and right next to that the supplier and purchase date, which would then also be carried over to the Sold Items page when the item sells? That way you would know your ending inventory value for year end tax purposes and also have good documentation to prove your COGS if need be.

Thanks!

Sue

[Scott]

Hi,
I'm glad you're enjoying the program so far.
Let me try to help you out with your question.

The costs and additional details can be entered immediately in the product master* tab.
Once the item sells it will pull this data into the sold item data; these are paired together by the item listing ti

Since you are selling one-off items, you may choose to use the sku field for something like the supplier name if that is more valuable in your specific case. 

Let me know if I can be of further assistance.

*fixed my typo (thanks hunter!)

[Hunter]

Just to clarify, by "inventory tab" he means the "Product Master" section of MyCostPro.  I have attached a couple screenshots that hopefully solidify Scott's answer.  Thanks again for giving MyCostPro a try!  We are glad you are enjoying it.

[ssheldon]

Thanks guys for the quick response.

I noticed in one of your videos you used the *CD* example to pull in Cost of Goods on all items with CD in the title. Could this wildcard be used in the SKU field instead, something like:

GW-07-12-2017-1.99-1234

or do you have to have something in the title that connects it to the sold item.

These are the types of SKUs I use for Amazon listings (with an automated random 4-digit suffix) using Inventory Lab, so if I'd ever need to produce a receipt I can look in the July receipt folder for a Goodwill receipt from July 12, 2017.

Also, any plans to have a Cost of Goods column in the Active Listings sheet, for end of year inventory value and more automation? Unless I'm missing something in how the Product Master sheet works, that still would seem to be redundant since all of the items I have listed are already in the Active Listings sheet. If we could have a Costs section in that sheet with Supplier, Date Purchased and Cost of Goods I would think that would cover everything. Then we wouldn't even need to mess with skus in eBay unless needed for internal tracking.

I think your program is much needed for ebay sellers (like me) who struggle to keep track of there COGS and year end inventory value and sit surrounded by piles of receipts trying to organize for tax time

[Hunter]

    I believe the feature you are after is really what Scott intended to accomplish in the product master section.  The idea would be to enter all your costs there, so once the item sells, that data then appears as part of the transaction on the sold list...and is therefore applied in all of the charts/reports automatically. 

    The wildcard for listing title for sure maps to the rows in the sold list.  The sku does as well, but I'm not sure if Scott implemented the wildcard feature for skus. That's a good idea.  I also like the idea of adding a column in the product master section for you to record where you purchased your item.  Similarly, I like the idea of adding a column to the active list section to include your product costs there as well.  Not only is it good for valuing your inventory come tax time, but it's convenient to see it there in case you want to update your asking price as well.

    I'm the back-end magic guy, and Scott is the primary spreadsheet person. I'll make sure he reads all this and gives you a more definitive answer about how the sheet currently works. He is traveling today, but he should be able to respond soon enough.  I'll also push for those new features to be added soon because I think they could be helpful to many of our customers. Thank you for your feedback! Scott will reply soon with a better explanation of how the sheet currently works.

[ssheldon]

Thank you Hunter! I am a member in the FB Group 'Accounting We Will Go' and if you read through some of the posts there you realize how many struggle with this. Some are Amazon only, ebay only and some multi-channel. A lot of people use Inventory Lab (which is only for Amazon) just for accounting purposes only and pay over $35/monthly (I use it both for listing and accounting).

Very promising product you have here. I don't know how I didn't hear about it sooner, but as soon as understand it fully I will definitely spread the word. Maybe in the future you can add Amazon.

Sue

[Hunter]

    I really appreciate the compliment about our product.  We will certainly consider adding Amazon/Etsy in the future, but there is a big overhaul in the works currently that will improve our compatibility across platforms and give our customers many more exciting features to make eBay selling and accounting easier.  It may be a little while before it sees the light of day, but I guarantee it will be worth the wait.

    As for spreading the word, we would be greatly appreciative of any help you may be able to offer.  I think we have a good product for our price point.  It really just takes time to catch on...many people are just tied to their old methods and routines for selling.  If you would be willing to help spread the word, we setup a nifty affiliate program that pays you for each paying customer you are able to recruit.  Here is a link to the info if you are interested.  I think Scott is back from his important business travels today, so I'll poke him again to see if he can add a little to my previous answer for you.  Thanks again for your feedback.  We really do value it!   

[Scott]

ssheldon,

Thanks for the good questions, im going to do my best to help out here.

Q) You want to know if you can use *wildcard* on SKU field instead of item description field
A) Not currently, but I understand the scenario and how it could be helpful.  Keep in mind the SKU field would have to populated on the ebay side, so that it would be included in the sales download, or else there would be nothing to tie the two together.  Let me know if im explaining that poorly.  If its a big item for you maybe we can start a new thread on the product feature forum to make sure we get all of the details right.

Q) Add cost of goods sold column to Active Listing Sheet as well as additional info (inventory source, cost, and purchase date) to help with end of year inventory.
A) The Active Listing Sheet is just a snapshot of what is currently "active" so any data you input there would go away after the item sold.  For that I create the Product Master tab.  If you think it would be helpful, I can update that to include the inventory source and purchase date (cost is already there).  Also, I will explain below* how to update your active items into the Product Master very quickly.

*Make Product Master for all of your Active Listings Quickly.
Update your active listings with the "check active listings on ebay" button, then:
1. On the Active Item List tab, make sure the "Click Layout Assist" button is set to ":off"
2. Highlight a list of Item Titles by left clicking the mouse (hold it down) and drag to select as many item titles as you want (they will be highlighted in light blue)
3. Hit Ctrl-C to copy to the clipboard
4. Navigate to the Product Master tab.  (Click Home, then Product Master)
5. Click (single left click) in the first empty cell for Listing Description
6. Ctrl-V to paste.  [optionally, you can use "paste-special, values only" to keep the formatting cleaner visually]
7. Now you can fill in your COGS and they will be saved and automatically transferred to the sold item tab

[ssheldon]

Thanks Scott,

This is how I am getting it to work (so far I'll try to state details in case someone else in the group wants to do the same. My main objectives are to be able to input my cost of goods when listing so I don't have to do it later, and get a total Cost of Goods Sold and Current Inventory Value at year end.

If you have an eBay store, the listing form has an area where you can add your Custom Label (SKU). Unlike Amazon, with eBay your SKU's don't have to be different for every item so if I bought 5 books at the Goodwill on July 4th for $2.00 each, all of those books can have a SKU named 2.00-GW-07.04.2017

So I created a new sheet at the end of the MyCostPro workbook and named it Inventory on Hand. I updated my Active Item List, copied the blue Listing section (which includes the SKU I entered in eBay), and did a Paste Special into the new Inventory on Hand sheet. (The paste special just pastes the static data without formulas). I added 2 columns, one for supplier and one for purchase date.

After doing a bit googling I found if I entered this in the COGS column (with G3 being my SKU column):
=LEFT(G3,FIND("-",G3)-1)

it would grab anything before the dash from my SKU (which is my cost) and then all I had to do was drag the formula down and all of my costs were pulled from my SKU and entered instantly. It can do the same for supplier (using MID=) and purchase date (using RIGHT=) which is why I put dashes in my SKU to separate the sections. Now on Dec. 31st or Jan 1st all I have to do is do what I did above, SUM it up and I have a total value of Inventory at year end for Uncle Sam. Sweet!

I also tested this on the Sold Items List for Cost of Goods and it worked there also. If you could add a Supplier and Purchase Date column to the sold items list then that would be complete and you'd be able to get a good overview of your best suppliers and how long items take to sell.

Obviously, this wouldn't work for everyone if they already had different SKU systems in place. But my bet is most small sellers haven't even used the SKU option in eBay yet.

[Scott]

Wow, good idea to add data to the ebay listing that isnt normally available for your record keeping! 
Also in the future if you need any help with formulas like that, dont hesitate to come to me directly and I can let you know how to do what you want by formula.

[Hunter]

That is definitely a smart way to do things. Scott will be getting to work soon to add the new columns, and we'll let you know when it's done. When finished, you just need to redownload  the sheet and use the nifty import feature to copy in your old stuff into the new version.

[ssheldon]

Hey Scott,

I just noticed that when I used the above method to pull in cost, the ROI field doesn't get filled in. I'm assuming because I added a formula in the cost field now not an actual static number. Thought I'd bring it up while you were working on adding the other two columns. No biggie if it's a problem but thought I'd mention it in case it was a simple OR statement that could be added or something.

[Scott]

My guess is the formula is pulling the price out as a "string" value (alpha-numeric).   Use "value()" in the formula to make sure it stores it as a number.

example:
=VALUE(LEFT(G3,FIND("-",G3)-1))

I think this will solve the issue and the ROI and profit should calculate automatically...

[ssheldon]

My guess is the formula is pulling the price out as a "string" value (alpha-numeric).   Use "value()" in the formula to make sure it stores it as a number.

example:
=VALUE(LEFT(G3,FIND("-",G3)-1))

I think this will solve the issue and the ROI and profit should calculate automatically...

Worked perfect! Thanks Scott...

[Scott]

. Np... that's why I'm here!