Luminous Product

Reconciling Inventory FAQs

In this article:
  • Recording Cost of Goods Sold (COGS)
  • Validating the Inventory Balance
  • The Correct Adjusting Journal Entry (JE)
  • Segmenting Inventory Accounts
  • The Accountant's Choice for Inventory Data

Jon Blair with Free to Grow CFO and Josh Sanders from Luminous recently provided a detailed masterclass on reconciling inventory and properly closing the books for brands using Luminous. The goal is to ensure a brand correctly records Cost of Goods Sold (COGS) and that the inventory valuation in QuickBooks ties to the valuation in Luminous.

‍

Successful reconciliation relies on the fundamental accounting equation for inventory:

‍

Beginning Balance + Purchases Received − COGS = Ending Balance

‍

The two-step process involves using Luminous's data to first record COGS and then validating the final ending inventory balance in QuickBooks.

‍

Recording Cost of Goods Sold (COGS)

‍

The first step is booking the monthly COGS journal entry in QuickBooks, leveraging Luminous as the single source of truth for costing data.

‍

Where to Get COGS Data in Luminous

‍

  1. Users go to Reports and access the COGS Insight Dashboard.
  2. The report is typically filtered by Ship Date for the month being closed. It can also be filtered by order status, order date and channel.
  3. This dashboard provides the total landed product cost of what was sold, by SKU.

‍

Intervening on Zero-Cost Transactions

‍

The report also highlights any products sold that had a zero COGS value. This usually indicates a missed landed cost recognition, such as an inbound shipment that wasn't reconciled.

‍

Crucial Intervention: Before running the final COGS total, users should address these zero-cost items. The Transaction COGS Report allows users to correct missed costs, and each SKU's cost layer history shows how the layer was created, its landed cost, and the receipt tied to it.

‍

Action in QuickBooks: After confirming all costs are accurate, the brand records the total COGS via journal entry: Debit Cost of Goods Sold and Credit Inventory.

‍

Validating the Inventory Balance

‍

After recording COGS, the next step is validating that the Beginning Balance and Purchases Received match between QuickBooks and Luminous.

‍

A. Validating Beginning Balance

‍

  • QuickBooks: Pull the balance of the Inventory Asset Account as of the last day of the prior month (e.g., June 30th).
  • Luminous: Use the Stock Snapshot Report. Set the date to 12:00 AM on the first day of the current month. Users export the report and sum the Starting Inventory Value column to reconcile with QuickBooks.

‍

Accounting Tip: To protect prior period balances from accidental changes, brands should set the closing date password in QuickBooks after closing a month.

‍

B. Validating Purchases Received (Accrued A/P)

‍

Validating purchases is often the trickiest part, as the Received Value in Luminous (goods physically received by the warehouse) often disagrees with the Bills Posted in the QuickBooks ledger. This variance is often due to Accrued Accounts Payable (A/P)—inventory received but not yet billed for.

‍

The Correct Adjusting Journal Entry (JE)

‍

If the Luminous received value is higher than the QuickBooks bill value, the difference may represent accrued A/P. Do NOT plug this variance to COGS without investigating it first. Doing so can understate COGS and make your gross margin look higher than it really is.

‍

The correct adjusting JE to get the inventory balances to tie is:

‍

  • Debit: Inventory Asset (to increase the inventory balance)
  • Credit: Accrued Accounts Payable, or “Inventory Received but Not Billed” (a liability account)

‍

Later, when the bill arrives, the accountant posts that bill directly against the Accrued A/P liability account, clearing the accrual without double-counting it in the Inventory Asset account.

‍

Segmenting Inventory Accounts

‍

To keep the reconciliation process clean, Free to Grow CFO recommends splitting the inventory account in QuickBooks into two distinct accounts:

‍

  1. On-Hand Inventory: For inventory physically received (this account reconciles with Luminous's Stock Snapshot Report).
  2. Prepaid & In Transit Inventory: Where bills or payments for inventory that has been paid for but not yet physically received are posted.

‍

Jon's approach is to post every inventory bill to Prepaid & In Transit first, then at the end of every month, move the value of everything that's been received over to On-Hand, using data from Luminous. That way, the On-Hand account mirrors the Luminous Stock Snapshot.

‍

The Accountant's Choice for Inventory Data

‍

Luminous provides the granular inventory data required to move past spreadsheet guesswork and implement sound accounting practices like accrued A/P. Want more? See how the QuickBooks integration works, or watch Jon's Free to Grow CFO episode.  Book a demo today!

Video transcript

Intro: Closing the Books With Luminous

Jon Blair: Hey, what's up, everyone? I'm Jon Blair with Free to Grow CFO, and we've got Josh from Luminous. We're going to be talking today about reconciling inventory — really, closing the books. If your brand is on Luminous, at the end of every month you need to do a couple of key things to make sure you're properly recording cost of goods sold, and ultimately reconciling your inventory valuation in QuickBooks to the inventory valuation in Luminous. So today, you're going to get a masterclass on how to do that. Ready to roll?

Josh Sanders: Let's do this.

Three Things We'll Cover

Jon Blair: All right, so we're going to talk about three key things today. The big picture is reconciling Luminous — the Stock Snapshot report. Then we're going to talk about a sneaky little thing called accrued accounts payable that you want to be aware of. These are inventory items that have been received but not billed for yet. And then we're going to talk about what you should do in QuickBooks with prepayments or deposits on inventory and in-transit inventory, so it doesn't muck up this reconciliation.

The Inventory Equation

Jon Blair: I'm going to draw some things on the screen here, and then we'll go back to where we're pulling this data from in Luminous. Here's what we have to do. We need to go back to, honestly, accounting school. What I'm drawing here is the classic equation for inventory: beginning, plus POs — I wrote POs because it's easy to write on the screen, but it's really purchases received in the month — minus cost of goods sold for the month, the landed product cost of the items you sold in the month. That equals ending.

So how do we take this and use it to get the right data from Luminous and put it into QuickBooks? There's really a two-step process. The first step is to record cost of goods sold from Luminous in QuickBooks. The second step is to validate ending inventory and confirm whether, after the cost of goods sold adjustment, it ties to what the QuickBooks ledger has as the ending inventory valuation. If they don't tie, there's a step-by-step process we have to go through to find where the issue is.

Step 1: Pull COGS From the COGS Insight Dashboard

Jon Blair: So first things first, Josh: where do we go in Luminous to get the landed product cost of what was sold in a given month?

Josh Sanders: The first report I'd recommend: if we come into Luminous and click into Reports, we're going to search for and find the COGS Insight dashboard. This report gives us a couple of different things. It allows us to filter by order status and order date. The most typical is to filter by ship date, and you can select a range — typically the previous month, or the current month you're reconciling. You're also able to filter by channel if you need cost of goods sold by channel.

This report gives you total cost of goods sold by SKU in one section, and then it also gives you a section of any products and their quantity sold that did not have a cost of goods sold tied to the transaction. Once you take a look at that, we also have a Transaction COGS report, which enables the user to filter by any of the transactions that had missed COGS, and you can go through and fix that prior to running the next steps.

Fixing Zero-Cost Items

Jon Blair: Awesome. This brings up a point I didn't think of. When you pull up the COGS Insight report and there's a zero cost for a particular SKU, do you guys recommend any intervention on the cost for that month?

Josh Sanders: Yeah, that's actually a very common theme in systems such as Luminous, where you may have missed reconciling the inbound shipment, so the landed cost wasn't recognized. But on this Transaction COGS report, you're able to come in and update the cost of goods sold for certain transactions.

Another great tool is that we're able to click into the SKU itself and identify anything that may have gone wrong, or something that wasn't logged on the cost layer itself. A recent update we rolled out is that you can actually see the history of a cost layer: how it was created, what the landed cost was, and the receipt that was tied to that cost. So you definitely want to go through and fix that before you run the COGS report again.

Recording the COGS Journal Entry

Jon Blair: Got it. So after you fix all of the zero-cost items, then you pull the COGS report. That's when you take the total from that report for the month you're closing, and you debit cost of goods sold and credit inventory. That's step one: record cost of goods sold per the data in Luminous.

Step 2: Validate Beginning Inventory

Jon Blair: Step two, after recording cost of goods sold in QuickBooks, is to check the inventory balance in QuickBooks against the inventory balance in Luminous. We've already recorded COGS in step one. Now we need to validate that beginning and purchases match in both Luminous and QuickBooks. If they do, then by virtue of this equation, ending will tie. I'll go over a few reasons why sometimes it doesn't.

The first step in this phase is to pull the beginning balance. Using July as an example, you'd pull the balance in QuickBooks as of June 30, and confirm the beginning balance in Luminous ties to that, which we'll look at in a second. If it does, you're good to move on to validating purchases. If it doesn't, you need to investigate why.

Figuring out exactly why they're off is honestly beyond the scope of this video. It could be because you didn't reconcile inventory the month before, the way we're teaching you in this video. It could be that somebody went into QuickBooks and booked an entry after the month was already closed — it tied before, but someone booked something, and now it doesn't.

Lock Your Periods With the Closing Date Password

Jon Blair: That's one of the reasons I recommend that once you close a month, you set the closing date password in QuickBooks. Once you close June, for example, you set the closing date password and date it June 30. That way, no one can go back into QuickBooks and post anything before June 30 unless they have the password. If you're not locking down your periods in QuickBooks with the closing date password, you're leaving yourself open to not being able to reconcile your beginning balance.

Finding Beginning Balance in the Stock Snapshot Report

Jon Blair: So if beginning balance in Luminous and beginning balance in QuickBooks don't match, you need to find out why. Where do we find the beginning balance in Luminous?

Josh Sanders: We're going to go under Reports again and use the Stock Snapshot report. The Stock Snapshot report lets us go back to a point in time in Luminous. We're able to choose a historical date, and also a time on that day, and see starting inventory balances — the on-hand quantity and the on-hand value at that specific date and time. So you can check Luminous against beginning balances in QuickBooks.

Jon Blair: And you can export this to Excel, right? Why don't we show everyone which column they can sum to validate the beginning balance?

Josh Sanders: Perfect. We can click this Export to Excel button, and there are a couple of different columns we'll see. We have a Starting Inventory Count, and a Starting Inventory Value that you'll be able to sum up and make sure it reconciles.

Setting the Right Date and Time

Jon Blair: Okay, so this is actually an important distinction. Using July as an example, is the beginning balance for 7/1 to 7/31 actually the ending balance on 6/30? Or do we pull it from 6/30 to 7/31?

Josh Sanders: We'd use the Stock Snapshot report, and if we were using the July timeframe, we'd set it to July 1, 2025, at midnight, to make sure we get an accurate on-hand quantity and on-hand value at that point in time.

Jon Blair: So that gives us an accurate starting balance, because the opening balance at 12:00 AM is technically the same as the ending balance on 6/30 at 11:59 PM, assuming no orders came in right at 11:59. That makes sense. And then we'd want to set the ending date to 7/31?

Josh Sanders: Yep. If we're doing the full month, we'd set it to 7/31 at 11:59 PM.

Jon Blair: Look at that — accurate inventory to the second. That's what we're talking about. You didn't have that in your last spreadsheet.

Step 3: Validate Purchases Received

Jon Blair: Let me recap what we've done so far. We've recorded cost of goods sold in QuickBooks via journal entry, and we've validated beginning inventory. Now we need to validate purchases. If COGS matches and beginning matches, then based on our inventory equation, ending has to reconcile. Show them which column to use to validate purchases in the month.

Josh Sanders: Perfect. In this same Stock Snapshot report, we have a Received Count column and a Received Value column. These show the received inventory quantities and the received value by SKU for the desired time period — in this case, July 1 to July 31.

Jon Blair: So we're validating purchases using the total of the Received Value column on the Stock Snapshot report in Luminous. What do we compare that to? We'd pull the QuickBooks ledger for the inventory account you're posting purchase bills to throughout the month, and confirm the total there.

Why Purchases Often Don't Tie: Accrued Accounts Payable

Jon Blair: Now, we need to camp on this for a second, because I'm certain most people don't realize that the purchases in their QuickBooks ledger probably don't always match the purchases in Luminous. This is a super important distinction. This is where accrued accounts payable comes into play — meaning received inventory that hasn't been billed for yet.

You may have some issues with your beginning balance tying. Hopefully you don't; I explained a couple of reasons earlier why that might happen. Cost of goods sold hopefully ties, because you pulled the data straight from Luminous. Most of your inventory reconciliation is probably going to focus on the purchases reconciliation. So let me show you a couple of reasons why it might not tie.

Don't Plug the Difference to COGS

Jon Blair: Here's the thing. What I see a lot of accountants do is notice that purchases don't tie — meaning the ending balance in Luminous and the ending balance in QuickBooks don't tie — and just plug a journal entry that adjusts inventory to match Luminous, with the other side of that journal entry going to cost of goods sold. That could be an incorrect journal entry. So do not do this without digging deeper into the variance between purchases per your QuickBooks ledger and purchases per the Luminous Stock Snapshot report.

A Worked Example

Jon Blair: Let me give you an example, which I'll draw on the screen. On the right side we have QuickBooks, and on the left side we have Luminous, which I'm just going to call “Loom.” We've got beginning, purchases, COGS and ending on both sides.

Let's use easy numbers. We've already tied out that beginning is 100 in both, so we're good. We've already recorded COGS of 50 in both, which is a subtraction from inventory. We've validated those. Now let's say in Luminous, purchases are 100, and in QuickBooks, they're only 50.

You might ask yourself, how the heck is that possible? Again, where is the purchases number in Luminous? It's Received Value. So think about that for a second and ask yourself, as the accountant: do all of the bills I've posted to the inventory account in QuickBooks throughout the month match, dollar for dollar, the value that was received at my warehouse? Most months, that's not the case.

Why might that happen? Well, there's this sneaky little thing I call accrued accounts payable. Some accountants call it inventory received but not billed. Let's say Luminous is actually correct: it's reconciled the value of your ASNs via the connection to your 3PL and the landed cost value placed on those items.

If you didn't think critically about what each of these purchase numbers was in Luminous and in QuickBooks, you'd book an adjusting journal entry adding 50 back to QuickBooks inventory, which actually reduces your cost of goods sold for the month.

The Correct Adjusting Journal Entry

Jon Blair: But if you dig deeper in this hypothetical example, it turns out that 50 of the 100 received in Luminous hasn't been billed yet. So you do need to add 50 to your QuickBooks inventory via an adjusting journal entry — but the other side of that journal entry is not cost of goods sold. It's an account you need to create called accrued accounts payable, or, as some accountants call it so it's very clear, inventory received but not billed.

The entry would be a debit of 50 to inventory, increasing inventory and getting your ending to tie. On the Luminous side, we'd have 100 plus 100 minus 50, which is 150. On the QuickBooks side, we'd have 100 plus 50, plus the 50 adjustment, which is 200, minus 50, which is 150. And we tie. The trick is that your adjusting journal entry is a debit to inventory and a credit to a liability account called inventory received but not billed, or accrued accounts payable.

Then, when you get that bill for 50 later, you don't post it directly against inventory. You've already picked it up in this adjustment. Instead, you post that bill directly against accrued accounts payable, which wipes out that 50.

Why This Matters for Your Gross Margin

Jon Blair: This is a key distinction. If you didn't reconcile this way, and didn't investigate the difference between 100 of purchases received in Luminous and 50 in QuickBooks, you may have made the wrong journal entry and reduced your cost of goods sold for the month. That would throw your QuickBooks P&L off from the Luminous P&L, and you'd be asking yourself, why is my gross margin so high? I'm sure there are a lot of brands making this mistake.

To recap: Received Value on the Stock Snapshot report in Luminous is the value of everything that was received in the warehouse. It's not the value of every bill you got in the month. So you do need to check whether you've been billed for everything received in the month, and you may need to accrue some accounts payable. You may also need to offset the previous month's accrued accounts payable with bills you got in the current month.

Prepaid and In-Transit Inventory

Jon Blair: There's one more sneaky thing: prepaid and in-transit inventory. This is the last thing we'll talk about. One other thing that can throw off your inventory reconciliation is having just one inventory account on your balance sheet in QuickBooks instead of two. You can go to three if you want, but let me explain why.

Right now, the reports — namely the Stock Snapshot report in Luminous — reconcile on-hand inventory. They don't reconcile in-transit inventory, or what we at Free to Grow call prepaid inventory. So what I recommend, and what we do at Free to Grow CFO, is to have two inventory accounts. One is called Prepaid and In Transit. That's where you post any bills or payments you make for inventory you haven't received yet. You put bills for inventory you've already received in your On-Hand Inventory account.

Furthermore, there's one more step, and I'd be curious whether there are any reports in Luminous that might be able to help us with this. What I recommend is that you post every bill you get for inventory directly to Prepaid and In Transit — just assume it's prepaid and in transit. Then at the end of every month, validate the value of everything that has moved from prepaid and in transit to on hand, and you can do that by gathering the right data from Luminous.

Join the Luminous community today.

Subscribe for updates from Luminous—fresh insight delivered to your inbox twice a month.
Get started today