Have you outgrown the yellow legal pad and basic spreadsheets?
We all start somewhere and the trusty legal pad has been that starting place for many growing small businesses.
Some eCommerce sellers have tried basic Excel spreadsheets or Google sheets, but have found they are too complicated to even do basic calculating.
Hey, I love free spreadsheets and have used many, but you do get what you pay for, right?
Most of the free spreadsheets I have seen will work, but the time it takes to input all the data is just too costly.
These methods will work for a while but become tedious and overwhelming as your business grows.
Then tax time comes and the race is on to come up with Beginning Inventory, annual Purchases, and the Ending Inventory value.
Your CPA may have suggested using QuickBooks for accounting, but offered little help in tracking inventory.
With high hopes and determination, you start investigating the Intuit website, office supply stores, and Google trying to determine which QuickBooks you need. Hey, you may have even polled the Facebook group. Desktop, Online, Mac, PC, Pro, Premier, Enterprise. Yikes, who knows?
A lot of eCommerce sellers will give up and try to do other things to grow the business while neglecting or avoiding the whole accounting and inventory tracking subjects. They may just pull out the trusty legal pad and trudge along.
You need to make a decision on accounting and inventory tracking, yesterday.
Accounting may be your least favorite thing about running your eCommerce business. Tracking inventory is probably a close second.
I get that. Neither are as sexy as being a superhero until tax season comes around and your numbers are ready for the CPA on January 1st. Now that is definitely a superpower!
Bookkeeping is a necessary part of any business. You can’t really run one without it. Inventory tracking is just as important.
It’s not just for tax season, though.
Running reports like the Profit and Loss or the Balance Sheet is the only way you can really tell if your business is making money and your inventory value should match the amount on the Balance Sheet. If it doesn’t, there’s a problem.
The health of your business is measured in these reports.
- Balance Sheet shows your assets like cash, checking, and value of inventory
- Balance Sheet also tracks liabilities like loans, credit cards, and sales tax payable
- The Profit and Loss tracks income and expenses like eCommerce platform sales and selling fees, as well as other business expenses like labels, scales, Scotty peelers. software, subscriptions, Cost of Goods Sold, etc.
If you’re ready to get your accounting in order without struggling to learn QuickBooks by yourself, we have a course that will help. The training is specifically for Amazon Sellers but is applicable to other platforms. In the course, I teach several ways to post your accounting including a quick method that uses a General Journal entry. The entry involves using COGS instead of tracking inventory in QB. It is the method that users of Inventory Lab would most benefit from. Now, users of the ECOMMERCE INVENTORY TRACKER can take advantage of the method without the monthly fee to Inventory Lab.
Introducing the ECOMMERCE INVENTORY TRACKER
Some QuickBooks users need a way to track inventory but don’t want to do it in QuickBooks and they don’t want to pay monthly fees either!
Some sellers don’t want to use QuickBooks but need a robust inventory spreadsheet to track sales and COGS plus purchases.
The ECOMMERCE INVENTORY TRACKER offers the best of both worlds.
- ALL ITEMS LIST – This is the master list and is where we have the most flexibility. Choosing the REF # is the most important thing we do on this sheet to make it work with the other sheets. Think of this sheet as an Inventory Database – not inventory on-hand. We will input as much information on this page as possible so we get the most out of the inventory management system. Choosing the inventory items’ names can be as simple as the name on your listing or you may choose a simplified version. Some of the fields use drop-down data, such as COLOR, SIZE, SCENT/FLAVOR, VENDOR, PLATFORM, RP. While other fields like BRAND, SALE PRICE, COST, REORDER POINT, and BIN | TOTE location allow for any text you choose. There is a field for non-Amazon listing numbers according to the eCommerce platform you use for each item. The ASIN | UPC feeds the AMAZON LOOKUP cells and returns a hyperlink to available listings. Using a barcode scanner may speed up the process. Just scan the product UPC or ISBN into the ASIN | UPC cell, and presto you get the link. Cool, huh? By inputting your projected SALE PRICE, COST, FEES, and SHIPPING, you can see the projected PROFIT and ROI of each item.
- PURCHASES – On this sheet, we will input DATE, REF #, and QTY. The rest of the data will auto-fill from the ALL ITEMS LIST based on the REF# data. We will demonstrate how we can change a placeholder REF# in one of the video lessons later. Just know that you are not stuck with the number if you want to change it later. A case in point is when you want to use a custom Seller SKU created on the PURCHASES sheet. Since the Seller SKU creator is fed by the DATE, RP, UPC | ISBN, COST, FEES & SHIPPING columns on the PURCHASES sheet, you will not have the final REF# until you actually input the purchase of the items. Remember the ALL ITEMS LIST is just a database of item-specific information. The Inventory On-Hand is not changed until you input information on the PURCHASES or SALES pages. Even though a custom SELLER SKU is created on each item, you may still choose to use your own SKU. The reason the Seller SKU creator is located on the Purchases sheet is the cost may change on your items over time. Changing the SELLER SKU to reflect that will make your inventory system work better. No more guessing on COGS. You get the true cost of each item. Note the cost at the end of the SELLER SKU includes fees and shipping. Now you will know the lowest price you can list without any guesswork. Cha-ching! Input your entire beginning inventory with just a quick Copy/Paste using your platform’s reporting. I’ll demonstrate the Amazon inventory report.
- SALES – On this sheet, we will input DATE, REF #, and QTY. Most of the data will auto-fill from the ALL ITEMS LIST, except SALE PRICE, FEES, SHIPPING. Since we often do not get the sales price we originally list for our products, I’ve included these fillable cells to help see the ACTUAL PROFIT AND ROI. Knowledge is power! From the SALES sheet, we can pull the monthly sales total and COGS. We can also see actual PROFIT & ROI. All this information is sortable by platform too! Copy/Paste Seller SKU in REF# from your platform’s sales reporting to speed up posting. You can input a full page of sales in just a couple of clicks. I’ll demonstrate a payment statement input from Amazon.
- INVENTORY ON-HAND – We will not input anything on the page from day-to-day. All the other pages consolidate on this page. There is a calculator on the page for you to use to help with figuring COGS, which is useful in annual income tax preparation. The page also offers a handy REORDER PT flag, which turns red in the ON-HAND column when stock meets or falls below the previously set level. The sheet features a snapshot of IN AND OUT inventory, including pieces in inventory, purchased, and sold. Checking your Inventory On-Hand with your actual inventory in bins and totes or at FBA warehouses is made easier with this sheet.
- BUNDLES – This spreadsheet is handy for tracking all your bundles. By changing the BUNDLE NAME field on each section, you can search for them later with the FIND function in Excel. Add the name of each item in the bundle and the cost associated with it to calculate the total cost for input into your ALL ITEMS LIST. Then as you add BUNDLES and input on the PURCHASES sheet, you can add full or decimal parts of BUNDLES with one transaction. SALES inputs are just as easy to track.
- MULTIPACKS – This spreadsheet is handy for tracking all your multipacks. By changing the MULTI-PACK NAME field on each section, you can search for them later with the FIND function in Excel. Add the name of the PRODUCT, PURCHASED QTY, MULTI-PACK QTY, and the COST PER ITEM. The calculator will give the packs available in the purchased quantity, as well as, the cost per multipack. Then as you add MULTIPACKS and input on the PURCHASES sheet, you can add full or decimal parts of MULTIPACKS with one transaction. SALES inputs are just as easy to track.
- RECEIPT CALCULATOR – This spreadsheet is the ULTIMATE RA RECEIPT CALCULATOR sold on my website for $29.99. I am including this bonus in honor of adding BUNDLES and MULTIPACKS. If you have been struggling with your receipts and invoices for accounting, you will love this bonus. ENJOY!
Tutorials to help make the most of your experience with the Ecommerce Inventory Tracker
|Module 1||GENERAL INFORMATION ON INPUT REQUIREMENTS|
|Unit 1||Read First|
|Unit 2||Download This|
|Module 2||OVERVIEW OF SHEETS|
|Unit 1||Sheets Snapshot|
|Unit 2||Overview of All Items Sheet|
|Module 3||USING THE SHEETS|
|Unit 1||Excel Table Settings|
|Unit 2||How to Input Items - All Items List|
|Unit 3||How to Delete Columns and Add Dynamic Columns|
|Unit 4||How to Input New Inventory Purchases|
|Unit 5||How to Input Sold Inventory|
|Unit 6||How to Add Bundles and Multipacks|
|Unit 7||How to Use and Read Inventory On-Hand|
|Unit 8||How to Speed Up Input with Fill Series and Copy Cells Down|
|Module 4||CUSTOMIZING THE SHEETS|
|Unit 1||Excel Basics In a Nutshell|
|Module 5||APPLICATIONS DEMONSTRATED|
|Unit 1||How to Create Custom Seller SKU|
|Unit 2||How the Amazon Lookup Works|
|Unit 3||How to Use Barcode Scanner to Speed Up Input|
|Unit 4||How to Import Beginning Inventory - Amazon Demonstration|
|Unit 5||How to Copy and Paste All Items from CSV - Amazon Demonstration|
|Unit 6||How to Import Sales, Copy & Paste, Find Monthly Sales and COGS|
|Unit 7||How to Record Amazon Returns and Re-List on Another Platform|
|Unit 8||How to Figure Beginning Inventory, Purchases, and Ending Inventory|
I’d like to take a minute to introduce myself.
My name is Vicki McCay. I’m a wife and homeschooling mother, who has an Amazon FBA business and does accounting for a local small business. With many years of experience in automotive (double-entry) accounting under my belt, QuickBooks was the logical choice when I started my eCommerce journey.
Having run the Amazon business for a few years, I’ve gained valuable experience and empathy for my fellow sellers. I spend a lot of time these days helping others find their way as they try to navigate the eCommerce world.
Being a teacher at heart, I share what I have learned.
Important: Information in this document does not constitute tax or legal advice and must not be used as such. If you have tax or legal questions, please contact your professional adviser. Any course offered here is simply to demonstrate how to use the accounting software or spreadsheets.
© Southern Mom Flair - All template designs and courses are for your personal business use only and may not be resold or redistributed under any circumstances.