The MacGyver-esque approach to tracking fully landed costs for profitability in QuickBooks Online

A few months back, I conjured up a hack to create inventory valuation adjustments in QuickBooks® Online because the feature didn’t natively exist at the time. And, I used the U.S. QuickBooks Online test drive company, Craig’s Design and Landscaping Services, to illustrate the scenario.

Poor Craig. In that example, he decided to diversify into the area of office supplies, and he knew nothing about them. In fact, he purchased floppy diskettes!

This month, Craig has decided to rebrand: he is now calling his business “Craig’s Used Cars and Landscaping.” He knows about as much about used cars as he does office supplies. He will not listen to anyone who tries to talk him out of it. This cannot end well. All anyone can do is help make QuickBooks Online work for him in tracking his inventory and his cost of goods sold.

Here’s what we know about buying and selling used cars, at least as far as Craig’s business model is concerned:

• Craig is using QuickBooks Online Plus.
• He will buy each used vehicle at an auction or privately from a seller, pay for some cleanup and repairs for the vehicle, and then advertise and resell the vehicle at a higher price (hopefully).
• For illustration purposes, each vehicle will be assigned a unique product/service inventory item name, consisting of the vehicle year and then the color, followed by the make and model, and then the 17 digits of the VIN (Vehicle Identification Number). Here’s an example: “2010 Yellow Invincible Warrior SUV 1HGBH41JXMN567890.” This is a very long name, and I’ll address this later on in this article. The optional SKU can consist of just the VIN, and the description can be more complete, if desired. Categories can also be used if you want to split vehicles.
• Since VIN’s are unique, there will only ever be a quantity of ONE of any vehicle, until it is sold, and then the item will be made inactive.
• After purchasing each vehicle, there are usually some outlays required to get the item ready for resale: repairs, maintenance, detailing or perhaps even advertising.
• The challenge here is to assign these costs to the cost of the vehicle, rather than general overhead expenses, so that the cost of goods sold are properly impacted when the vehicle is sold and the item profitability is correct. In other words, Craig needs to know if he’s selling his used cars at a profit after spending the after-purchase money on them: he needs QuickBooks Online to track the fully landed costs of the cars.

In the interest of transparency, I have seen suggestions that used car dealers not bother with different items, and instead assign a different class for each different vehicle, and then run a Profit & Loss by class. The problem with this approach is twofold:

• Classes may already be in use for another purpose, and it’s not a good idea to use classes for more than one purpose.
• With the launch of QuickBooks Online Advanced in the U.S., QuickBooks Online Plus has a maximum number of 40 total classes and locations. With a new class for each vehicle Craig buys and sells, that maximum can be reached very quickly, and Craig would have to upgrade to QuickBooks Online Advanced at a much higher subscription cost.

And, yes, it is true that Craig might want to turn to a third-party app that handles the heavy lifting of inventory and landed costs and integrates with QuickBooks Online. But, Craig is unsure that he wants to stay in the area of used cars, and he’s also price-sensitive so he wants to use QuickBooks Online with no external app for now.

Believe it or not, we can design QuickBooks Online Plus to absorb fully landed costs, and it’s not too different from the model we used for inventory valuation adjustments.

Let’s get started:

I’m setting up a separate “Inventory of Vehicles” asset account for the vehicles that Craig is going to buy and sell. I’m doing this to segregate the vehicles from any other inventory that Craig is tracking on the balance sheet in the test drive company:

I’ve also set up a separate “Vehicle Sales” income account and a “COGS – Vehicles” account for cost of goods sold related to vehicles. (It’s true that I could do without these new accounts and use a class for vehicles on these vehicle product/service inventory parts I’m about to set up instead, but I want you to see the cars separately on the balance sheet for the purposes of this article.)

Next, let’s set up the first vehicle as a product/service inventory part that Craig is buying from an auction in order to track in inventory.

The “2010 Yellow Invincible Warrior SUV 1HGBH41JXMN567890” is linked to Inventory of Vehicles as the inventory asset account, Vehicle Sales as the income account and COGS – Vehicles as the expense account.

Next, Craig bought this vehicle (yes, he could have added the inventory item while creating the transaction) and paid $10,000 for it:

And, low and behold, the balance sheet reflected one vehicle in stock worth $10,000:

And, the inventory valuation summary (which won’t let me filter for the one inventory account) reflects that $10,000 as well:

Very nice. You can imagine that as time went on, Craig started buying up a bunch of other used vehicles: coupes, vans, compacts, sports cars and so on, adding to the balance sheet value of the Inventory of Vehicles account and the inventory valuation summary report. But, I digress. Let’s back up and focus on this one vehicle and pretend that there are no other vehicles in this business for now.

What happens after Craig buys each used car and he incurs expenses that he wants to allocate to the value of the vehicle?

For this, we’re going to create two more accounts, and we’ll call the first “Landed Cost Clearing” and it will be an “Other Current Asset” account. As it’s a clearing account, it should be zeroed out whenever we’re finished with it.

Our second new account will be a bank type, and will be called “Clearing Account (s.b. 0),” whose balance should also always be zero. Believe me, you’ll be glad it’s there:

A few days after the purchase, Craig lays out $5,000 to detail and repair the Invincible Warrior SUV, but instead of putting it to repairs & maintenance, let’s book it to Landed Cost Clearing:

At this point, Craig has one SUV worth $10,000 at the original purchase price, and $5,000 in repairs and detailing expenses sitting in Landed Cost Clearing.

Bear in mind that in a real life situation, the business owner might have several expenditures related to getting a used car ready for sale. In that case, each transaction would be booked to Landed Cost Clearing, and the memo field at the bottom of the transaction, or the description line in each row, might be of use to tag the vehicle, especially if the transaction is related to several vehicles.

To allocate the $5,000 to the cost of the vehicle, we have to take two steps.

Step one is to create an inventory quantity adjustment to bring our quantity of that one vehicle down to zero, temporarily, affecting that new Other Current Asset account we created called Landed Cost Clearing. I’m going to do it on the same day as the $5,000 expenditure, but I’ll show you the sequence of reports after each step I take.

Now, the inventory of vehicles is zeroed out and the Landed Cost Clearing is temporarily pumped up to $15,000, consisting of the original purchase price of the Invincible Warrior SUV and the follow-up repairs and detailing:

Furthermore, the inventory valuation summary shows you that on this same day, the quantity and value of the Invincible Warrior SUV have both now decreased to zero:

Now, we’re ready for step two: a zero-dollar expense, moving the $15,000 balance out of Landed Cost Clearing and back into the inventory where it belongs. We are going to purchase that same Invincible Warrior SUV again.

Although we’re creating a purchase, we’re reducing the amount of the expense payment to $0 by using the negative amount for the Landed Cost Clearing account in the account details grid to offset the amount in the item details grid.

Note that after the expense transaction is recorded, the clearing “bank” account balance should always be zero. Do not use a real bank account, even with $0 transactions, as it will unnecessarily complicate the bank reconciliation process, and someone who is not in the know will inadvertently delete them.

Let’s say that later in the month, Craig sells this car for $14,500 (plus tax).

Craig is happy because the $14,500 sale price is much higher than the $10,000 he originally paid, so he thinks he’s made a profit. But, we’ve been allocating the fully landed cost to this vehicle. Let’s run a Profit & Loss for this month and display columns by products/services:

(Note: if we had we been tracking classes, we could have filtered this report for the Vehicle class.)

Now, we can show Craig that he had a $500 loss. And, if he is buying and selling a number of vehicles over the course of the month, we can pinpoint exactly which vehicle(s) caused the loss.

Even better, you could have run an inventory valuation summary report for Craig, as I did, and prevented the loss. Before he sold the car, he might have looked at the report and decided to hold out for a price of at least $15,000.

I realize that the item name is very lengthy and makes the column overly wide and unwieldy on the P&L. In this example, there’s only one vehicle in inventory and it’s fine for illustration purposes. But, if Craig wants to make a go of this used car business, he’ll want to create a different, shorter naming convention for his vehicles. Who knows? Maybe this time he’ll be able to diversify successfully!

There you have it: the MacGyver-esque approach to tracking fully landed costs in QuickBooks Online.