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|
Holly shares her experience with our set up service and coaching option.
She purchased the QB for Amazon Sellers course, but had some specific issues that she wanted to go over before she set up the Chart of Accounts and processes she wanted to use. She was a bit behind in her accounting because she needed to convert from Excel to QB. It was a little overwhelming for her. We opted to use the set-up-for-you service. We worked closely together for a few days and got the job done. Seeing the process described in the course didn’t fit perfectly with her desired processes, I created a new way to post for her business. (The course purchase was not in vain since she still uses the course for other aspects of QB and the knowledge is useful for any business model.) Here’s her review of the service.
Southermomflair’s Amazon bookkeeping course is an excellent course for FBA sellers that want to get a grip on their accounting books without learning becoming too overwhelming! It provided me with a high-level yet detailed approach to accounting for Amazon FBA with Quickbooks that is easy to follow. The videos are short and sweet and best of all straightforward on what and where to click. The Excel worksheet in the course is an absolute gem, essential, and excellent in capturing the core details of FBA sales and expenses while tracking inventory is made a synch. The ability also to continuously review the videos and explanations has helped me not only to analyze but understand better the various aspects of my business and to get clearer on how Quickbooks can be transparently used so that I can concentrate on growing my business. One of the best features, though, is the support for this course. It is simply TOP-NOTCHED! Any question(s) you might have will be answered thoroughly and there is a great comfort level knowing there are years of experience on the other end. Guess that’s that Southern Hospitality!
Vicki’s course has been a great starting point for me in terms of using QB to better track and control my business. It’s not a one-size-fits-all thing, and (for me, at least) it didn’t cover everything that applies to my business or tell me exactly how to do everything I’d want or need to do with the software. It has, however, been a great resource for learning how to develop a unique QuickBooks system as a business tool. Vicki has been really great about answering questions she hasn’t covered in videos, helping me adapt what she has here for my situation when I don’t see how to make it work “out of the box”, and generally just being responsive and enthusiastic about making sure I get what I need out of the resources she’s provided. I’d definitely recommend this course as a resource for someone taking an active role in running their business better.
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.