Analyzing Inventory in QuickBooks Online

Analyzing Inventory in QuickBooks Online

Once you know how to track and manage inventory in QuickBooks Online you’re ready to start analyzing inventory in QuickBooks Online.

This means we’re going to look at some reports.

There are two key reports you are going to want to start looking at:

  • Inventory Valuation Summary
  • Sales by Product / Service Summary

As the titles suggest, the valuation summary gives you a look at what you have on hand (what you own). The Sales by product shows you what is selling, but it also gives you some very useful data – The COGS, Gross Margin and Gross Margin % for each product.

Analyzing Inventory in QuickBooks Online – Inventory Valuation Summary

This is where you look to see what you have in inventory. As the business owner you have a very good and reliable sense of whether or not this looks right.

  • Export the report to Excel or Google Sheets.
  • Right away you can start cleaning things up from this report.
  • The first thing I do is sort it by quantity. Look for the negative quantities. This could suggest we’re missing purchases? Or PO’s didn’t get turned into bills? Were those bills paid? If so we need to find those payments, and see how they were recorded, if not to the bills.
  • Next check the 0 quantity items. Look at the items. Is there anything here that you know you have stock in?
  • Then go right down to the items with the largest quantities. Does this look right? Should you have this much stock in these items?

It’s a bit subjective, but in my experience the business owner has a really good sense of what does or doesn’t look right. You know your products, and you have a sense of what sells and what doesn’t.

If the quantities do look right, you might want to look at the items you have a lot of stock in.

What is the turnover rate on these items? Are you at risk of overstocking? Should you run a promotion and blow out some of that inventory?

It may be fine. You may be stocking up for your busy season. There is no right or wrong answer on this stuff. Analyzing inventory in QuickBooks Online is about looking at it with an open mind. Don’t assume it’s right, and don’t assume it’s wrong. The goal is to make sure we can explain it, whichever way it goes.

Analyzing Inventory in QuickBooks Online – Sales by Product / Service Summary

  • I also dump the sales by product / service summary into Excel (and then convert it to Google Sheets).
  • Next I add in a column that calculates the COGS/unit for each item. It’s a good double check. As a business owner you will know when one of these doesn’t look right. I wouldn’t. That’s why it takes the accountant AND the business owner to analyze this stuff properly.
  • Sort it first by Quantity. You want to see what your top selling item is.
  • Then sort it by Gross Margin %. You want to see what your most profitable item is.

In the video here you’ll see that I’ve identified several items with a high profit margin but low sales volume. These are the products we want to sell more of. Higher sales volume on these products will translate to more dollars right to the bottom line.

This is just a start, but it should give you a very good start at analyzing inventory in QuickBooks Online. Even if this is all you did each week / month, you will be on top of things.

In this market you have to be fast, and you have to stay ahead.

Analyzing inventory in QuickBooks Online means looking at reports. In order for the reports to be useful, your data needs to be timely and accurate. That means good bookkeeping all around.

Not sure if your bookkeeping is there? Want help getting this analysis going for your eCommerce company? Contact us. Let’s get the conversation started!

Tracking and Managing Inventory in QuickBooks Online

Tracking and managing inventory in QuickBooks Online

Tracking and managing inventory in QuickBooks Online means we need to know what it cost us to get that inventory ready for sale so that when we sell it, we know how much gross profit we made on that item.

When you’re done here, check out Part 2 – Analyzing Inventory in QuickBooks Online

Buy low, sell high!

Wait! That’s not inventory, that’s stocks? Or is it?

When you think about it, it’s the same idea. I buy a product at one price and my hope is to sell it at a higher price.

It’s more than what you paid for the item. It’s the shipping to get it into your warehouse. Any other costs incurred to get that product on the shelf in a warehouse ready to ship once it’s sold.

Until the product is sold YOU own it. This is why it is classified as an asset. Inventory sits on your balance sheet until it’s sold.

Once inventory is sold it moves, at cost from the inventory asset account to the Cost of Goods Sold (COGS) account.

At the same time of sale, you recognize the revenue (what you sold it for) and then you get paid – cash in the bank!

Any expense you incur after this is no longer cost of goods sold. It’s a selling expense. For example if you pay for shipping to deliver the product to the customer. That’s not COGS. It’s a selling expense.

If you sold that inventory for more than what it cost, you now have that revenue in the income section of the income statement. The cost of goods sold is also now on the income statement (transferred over from the inventory asset).

The difference is your gross profit.

Divide that gross profit by the income and that is your gross profit percentage.

Divide the Gross Profit by the COGS and that is your markup.

If you’re an ecommerce business owner or anyone who sells inventory these are some of the numbers you need to know.

Your Products

In QuickBooks Online your products are listed in the products and services area. This area of QuickBooks Online is the virtual shelf. All of the products you sell sit here.

Each product in QuickBooks Online has to be unique. In other words, you might have 3 versions of the same product on your sales channels for SEO purposes, but those all need to map back to a single item in QuickBooks Online.

The distinguishing feature in QuickBooks Online is the Product Name. You would think it would be Sku also, but in QuickBooks Online you can have duplicate Sku’s. I would never recommend this, but QBO won’t stop you.

The first thing you’ll want to do in QuickBooks Online is set up your products. It’s a pretty simple form to fill out. I highly recommend setting up a master sku list before doing this. The master sku list will allow you to lay it all out in a spreadsheet format, so you can make sure you have all of your products listed. This also lets you map (where applicable) multiple versions of the same item on your sales channels, to the single item in QuickBooks Online that they go with.

Inventory (you own your products)

This is the key to understanding how tracking and managing your inventory in QuickBooks Online works. When you buy it, you own it. It’s an asset, it lives on the Balance Sheet, and it’s called, “Inventory.”

Your inventory will stay there until you either sell it, or throw it away. We take inventory counts in part to get the right quantities of the items we have in stock. The other reason we take inventory is to identify inventory that isn’t going to sell. When that happens we write it off. This is the danger of overstocking your inventory.

If you buy 100 items at $5 each, then you have $500 of inventory in stock. That’s what you own. When you sell it, you move the cost from inventory to Cost of Goods Sold (COGS)

Cost of Goods Sold (COGS)

Initially this is what it cost you to buy the product. So if you buy 100 items at $5 each, then you have $500 of inventory in stock. When you sell that inventory, your cost of goods sold is the quantity you sold times $5.

Important!

QuickBooks Online tracks your inventory on what is called FIFO basis. That means First In, First Out.

So if you bought 25 of an item at $4.50 each, and then 75 of that item at $5.00, QuickBooks Online will track it accordingly.

The average cost of your inventory is $4.88/unit. On reports you will see it reflected at average cost, but when you record a sale, the COGS on that sale (and the amount taken from inventory) is based on FIFO.

The first 25 items you sell will be costed at $4.50. The next 75 at $5.00.

Watch the video so see what this all looks like.

Then check out part 2:

Analyzing Inventory in QuickBooks Online

 

Use Recurring Transactions in QuickBooks Online for Every Monthly Bill

Use Recurring Transactions in QuickBooks Online for Every Monthly Bill

I’ve learned the hard way in some cases, not to rely on bills coming in the mail as my trigger to enter and pay them. Bills get lost, and when that happens and I don’t pay them on time, my clients don’t want to hear that I didn’t get the bill. This is why I use recurring transactions in QuickBooks Online for every bill.

Back when I was still dealing with paper, there was a simple process for managing accounts payable. You get a bill in the mail, and it goes into an inbox. A real, physical inbox. Remember those? Then you enter the bill, and it shows up on an unpaid bills report. That report is then reviewed and decisions are made about which ones to pay. We’ve come a long way since those days, but I still see clients.

Even then, bills would get lost or missed. Now it’s almost worse, because they get emailed to my clients, and I have to rely on them to forward them.

Actually I don’t.

We have our clients give us their logins to almost every service they pay, and we set up our systems to make sure nothing gets missed while we have access to everything. We don’t need to wait for emails.

Recently I was working with a client who is more involved than most in the decision making process about which bills to pay. He kept a list in a spreadsheet as I’ve often done, but now he was asking me to update his spreadsheet as well as in QuickBooks Online.

This was double work. There had to be a better way.

I set up recurring transactions in QuickBooks online for every monthly bill.

I set up a recurring bill for each monthly bill that he pays, working backwards from the due date. Each bill is now automatically posted 30 days before it is due. This gives us plenty of time to pay them, from the time that they show up.

In fact this is another problem I’ve seen.

You get a bill from your credit card company and it’s due in 10 days. That’s not enough notice, and now you’re scrambling to get the bill paid, often at the expense of other bills that need to be paid.

When the bill is for a different amount each month, you set up a default, estimated amount. The in the recurring bill description you put in a note to “get the right amount.”

If you are reviewing the unpaid bills report weekly, then you will have plenty of time to log in, get the actual bill, and update the amount.

If you use recurring transactions in QuickBooks Online to manage your bills, you will never miss a bill. And when we’re using a service like Bill.com (soon to be replaced with billbeez), the bills sync over to bill.com. When bills are sent in there we add them to the existing bill that synced over from QuickBooks Online.

Here’s our process for paying the bills.

Either the client or I will run the unpaid bills report.

Then we export it to Excel (would love to be using Google Sheets but there is no direct export).

In a column off to the right I will either set a cell equal to the open balance, or I will key an amount if I want to make a partial payment.

At the bottom of that column I will total up the amount to pay.

Once my client confirms or edits this, then I go into Bill.com and schedule the payments. This means I also have a document and either an email or a conversation in Slack confirming my client’s approval to pay these exact bills.

If you want to see what this looks like taken several steps further, sign up for a $25 monthly subscription right here on my site, and review this course:
Remarkable Reports for Bookkeepers

Progress Invoicing in QuickBooks Online

Progress Invoicing in QuickBooks Online is a long awaited feature and it was recently added. Last month I believe (see posting date above). You start with an estimate, and then you can create invoices off of that estimate.

When you are doing progress invoicing in QuickBooks Online you have three choices:

  • Invoice the whole thing.
  • Invoice a percentage across the board.
  • Invoice specific items.

Construction companies need progress invoicing in QuickBooks Online.

You still can’t do the cost side yet, but if that is a concern, here’s how you can get your construction company into QuickBooks Online.

There are other use cases.

Let’s say you’re an eCommerce company selling specific, high value items.

You send an estimate to your customer.

The customer approves.

Then you bill as you ship.

Progress invoicing in QuickBooks Online is the perfect way to handle the partial shipments.

Let’s say you’re a contract killer for hire?

You send an estimate.

Your customer approves.

Then you bill 50% up front, and 50% on completion!

Just wondering who’s paying attention here!

Progress invoicing in QuickBooks Online cures a big headache for those who need it.
Learn how to do it (see video above).
Find the use cases for it.
Implement it.
Then figure out what you’re going to do with the time you just free’d up!

8 Reports Every Company Should Set Up in QuickBooks Online

8 Custom Reports to Set Up in QuickBooks Online

Working in this cloudy world means that we are running things on the basis of extreme efficiency. We work exclusively in QuickBooks Online so that we can leverage apps that make it easy to share data from one place to another. In essence this is how we can be super efficient. That increased efficiency allows me to spend time with clients on things that matter. Things like reports. Here are 8 reports every company should set up in QuickBooks Online.

When I take on a new client, first I on board them. Once the books are caught up and cleaned up, I make an appointment with the client to review the basic reports.

I start with the Balance Sheet and The Profit and Loss. This helps the client to see what I have created for them. It also helps to confirm that the client understands where things are going and why. Finally it gives the client an opportunity to give input on how they would like to see things presented on the financial statements. There are many things that are not subject to rules, and allow me to custom design what the reports look like with one important aim in mind.

The client should be able to read and understand the financial reports.

After I’ve reviewed the reports in some detail with my clients, I have 8 reports every company should set up in QuickBooks Online. These reports are then added to a weekly schedule. They are automatically sent the client every week.

I ask the client what day they would like to get them.

Once that day is set, then our day to update the books is now set. We generally work on each client, on the same day every week. The day we do the work is two days before the client’s reports go out. This gives us time to make adjustments if needed.

1. Balance Sheet – Year to Date Monthly

The balance sheet is our checklist for making sure the books are accurate. That’s why this is the first of the 8 reports every company should set up in QuickBooks Online. Analyzing your balance sheet on a monthly basis provides much better context. When you can see trends on a balance sheet (eg) accounts receivable keeps going up, you can spot problems and address them promptly. Steadily increasing accounts receivable could indicate a collections problem. It could also mean that sales keep increasing. So when we see something like this, we want to investigate further, and looking at the Statement of Cash Flows will confirm which answer it is here.

2. Profit and Loss – Year to Date Monthly

The second of the 8 reports every company should set up in QuickBooks Online is the Profit and Loss. Just like the balance sheet, we want this grouped by month. The information becomes much more meaningful.

One thing I like to do when I analyze the monthly P&L, is to go straight to the bottom, and look at the net income trends. When a month is either much higher or much lower than normal, I scan up and down that column to understand why. Is income much different? Total Expenses? Both?

There can be a lot of reasons. Maybe rent is being booked as paid, instead of with a bill. This means we wind up with double rent in one month, and $0.00 rent in another. This can skew your information. For this reason, I like to set up a monthly recurring bill for things like rent, to make sure the expense is recorded in the proper period. This way I can see the true cost of operating the business each month.

Even if you are “cash basis” that is only a means of reporting for tax purposes. Using the very example I illustrated above, you can see why a business cannot function properly using cash basis reporting.

Using the example above, you can begin to see how you can clean up a lot of what is on the books, both in terms of timing, and classification.

If your monthly payroll expenses are not pretty consistent, there is a good chance it is being booked net, and not gross. Get the payroll reports and fix that!

3. Statement of Cash Flows – Year to Date Monthly (only after reviewing with client)

The third of the reports to set up in QuickBooks Online is the Statement of Cash Flows. This report will fill in the gaps left behind by the other two reports. If we are making a good profit each month, but not collecting, the Statement of Cash Flows will confirm this.

If we are making a good profit, and collecting, but servicing a ton of debt, the Statement of Cash Flows will confirm this.

The tricky thing about the Statement of Cash flows is that where the others analyze balances in accounts, this statement analyzes the changes to those balances. This makes it a little confusing to read, and that’s why I do not recommend sending this to a client until you’ve had a chance to review it with them.

If you ARE the client reading this (or an accounting professional in need of help so you can better understand and explain it), sign up for a 1:1 session with me and I will walk you through how to read this statement.

On the other side, once you and your client have walked through the Statement of Cash Flows, and everyone understands how to read this, the value you create is enormous!

NOW we are getting strategic. Now we are adding value instead of merely compiling a set of books for the tax return to be filed. This is what in my experience clients desperately crave, and so few of us are offering, because no one thinks they have the time.

If you think you don’t have the time, then go back and re-read this post from the beginning. I started off by explaining exactly how and why you should have the time now.

4. Accounts Payable (A/P) Aging Summary

The fourth report of 8 reports every company should set up in QuickBooks Online is the Accounts Payable Aging Summary. This one should be obvious. I like the aging summary because it highlights where we may be very late in paying people. Oftentimes when I review this with a client I get a ton of insights about what is going on with their business.

They are in a dispute with a vendor, or they have a unique relationship with loose payment terms. There are often stories behind these situations, and listening to them helps me understand a lot about my client as well as their dealings with these vendors and how they do business.

Of course we also often find errors, as in, “I KNOW we paid this…” Those are easy to find and correct. Especially since we don’t tolerate any plugged numbers on a bank rec, so we know there can’t possibly be any payments missing from the books. In a case like this we’ve probably doubled the expense by recording the payment directly to that expense. A quick search in that vendor’s transaction history will usually reveal the error, so we can re-code it to accounts payable and then apply it to the bill.

5. Accounts Receivable (A/R) Aging Summary

The fifth of the 8 reports every company should set up in QuickBooks Online is the Accounts Receivable Aging Summary. This is important for the same reasons as the A/P Aging. I learn about the relationships my client has with their customers, and we find and fix errors as in when something is paid, but showing up on this report.

Once we clean up this report, a weekly review that normally takes no more than about 60 seconds is all it takes to KEEP this clean.

6. Bank and Credit Card Register Reports – Last 30 Days

The sixth of the 8 reports every company should set up in QuickBooks Online is the Register Report. This one is super important. Depending on how many bank and credit card accounts we have, this can actually be a number of reports, but once you understand how to use it, you can apply it to any bank or credit card account on the books. I set it up for every single one, no matter how light the activity, and I include it in my client’s weekly reports.

I have not seen a date range option like this in any other accounting product, but QuickBooks Online.

This lets you see a rolling activity report for the past 30 days. My clients will sometimes ask me why I am sending them a report with activity they already saw last week?

Every watch a movie or read a book for the second or third time,and find you picked up on things you had not seen previously?

This process, ensures that my clients and I have had 4 looks at every transaction that went through every bank and credit card account, before they cycle off the report.

There is a scant chance of something passing us by that doesn’t belong. The client can also see where we’ve coded these (in the split column) and sometimes my clients comment on that and ask me to code it somewhere else. As long as it doesn’t violate any rules, or create some kind of conflict, my policy on these things is “have it YOUR way!”

7. Uncleared Transactions

The seventh of the 8 reports every company should set up in QuickBooks Online is the Uncleared Transactions. I don’t know why this isn’t a very standard report. Every bookkeeper, and every business owner should be looking at this at least once per month. I include it weekly because why not? Old uncleared transactions will falsely reduce the register balance in a bank account, causing a skewed picture on the balance sheet. In short, we have more money than we think we do, and this will almost always account for the exact dollar difference between what the bank says we have and what QuickBooks says. Especially if we are reconciled every month.

This one requires a bit of customization beyond just grouping by a cycle like months. Watch the video to learn how to set this one up.

8. Uncategorized Income and Uncategorized Expenses

The eighth of the 8 reports every company should set up in QuickBooks Online is the Uncategorized Income and Expenses. This one should be obvious. I do like to use two separate accounts for these and include them on separate reports. So this is really two reports, but the same idea.

Since we are using QuickBooks Online exclusively with clients, we are finding more and more clients who will look at this report and fix it themselves. I LOVE those clients (you know who you are)!

If a client does not know how to go in there and fix it themselves, I will usually export these reports and put them into Smartsheet at first. After one round, we normally don’t accumulate more than a few of these in any given week.

I have all of my clients in Slack.

We have a channel called “uncategorized.”

When we only have a few, it is much easier for the client to comment in Slack about where to code them.

Also since we use QuickBooks Online, we can link directly to the transactions in slack. This makes it so easy to fix these on the fly.

Log in. Click the link. Edit the transaction. Keep moving forward.

That right there is a perfect example of how much more efficient it is when you are doing everything (and I mean everything) in the cloud.

So there you have it!

8 Custom reports to set up in QuickBooks Online and how to use them!

Watch the video to learn how to set these up.