The MacGyver-esque Approach to Inventory Valuation Adjustment in QuickBooks Online
As many of us know, Intuit® has been packing more and more functionality into QuickBooks® Online (QBO) over time in various areas, including the area of inventory management.
Some time back, one of those improvements was the ability to perform inventory quantity adjustments. That was nice, I thought, but I was waiting for the day when inventory valuation adjustments would make an appearance. As I’m still waiting – and some of my QBO clients have required this feature to track obsolescence – I decided to conjure up my own “MacGyver-esque” approach to the challenge.
I’d been creating my own QuickBooks Desktop & QBO hacks forever, so I figured I was up for it. And, so it began. Before long, I had a working concept!
Let’s start off with our scenario. A year ago, our test drive landscaping company owner, Craig, had foolishly diversified into office supplies and renamed his company, “Craig’s Office Supplies & Landscaping.” I know, right? What was he thinking?
He knew nothing about office supplies. Not realizing that floppy diskettes were about to be discontinued, Craig purchased 60 cases of them in 2008 at $35 a case, and he thought he could sell them for $100 a case. Not surprisingly, those 60 cases are still in his inventory. But, he can’t get $100 for a case. He can’t even get the $35 he paid for each one. He’d like to write down his inventory of floppies to reflect their obsolescence. He’s not getting rid of the quantity of diskettes; he wants to reduce their value.
Craig runs an Inventory Valuation Summary today and sees that he has 100 cases of floppies worth $2,100:
He wants to revalue them down to $300 total for the 60 cases, or $5 each, down from the $2,100 total or $35 each. Therefore, Craig wants to write down the 60 cases by $30 each (for a total write down of $1,800).
We’ll do this Inventory Valuation Adjustment in two steps.
Step one is to create an Inventory Quantity Adjustment down to zero, temporarily, affecting a new COGS account we created called Obsolescence.
The impact of this is to reduce the inventory – both quantity and value – to zero. Remember, though, that Craig still has these cases of diskettes on hand; they’re merely less valuable than they once were. We need to add them back into inventory at the revalued (i.e., lower than original) amount by creating an Expense transaction.
Step two is the point at which we account for the revaluation of the diskettes by creating a new Expense transaction.
Open an Expense transaction and date the transaction the same date as the Inventory Quantity Adjustment. (an advantage of using an Expense transaction is that no vendor name is required.)
You need to identify a bank account for this transaction. The best practice, here, is to use a bank type of account called Clearing for this Expense transaction. In the Item Details grid, buy back the 60 cases of diskettes at $5 each for a total of $300 (put simply, this is ‘virtually’ buying 60 cases at $5 each, for a total of $300, but next, we also have to show that no money has changed hands).
In the Account Details grid, select the Obsolescence account and affect it by a negative $300, so that the total Expense transaction is zeroed out (remember: in recognizing the reduced value of the diskettes, Craig is not actually spending any real money here).
In effect, although we’re creating a purchase, we’re reducing the amount of the Expense payment to $0 by using the negative amount for the Obsolescence account in the Account Details grid to offset the amount in the Item Details grid.
The net effect of these two steps is that the Obsolescence COGS account has been increased by $2,100, and then reduced by $300, for a net increase of $1,800, which is the total write down Craig wanted to capture.
Now, when Craig runs the Inventory Valuation Summary report after the two-step adjustment, there are 60 cases of diskettes back in inventory, this time worth $300, rather than the original $2,100.
Note that after the Expense transaction is recorded, the Clearing “bank” account balance should always be zero. Do not use a real bank account because even with $0 transactions, it will unnecessarily complicate the bank reconciliation process.
There you have it: MIVA, the MacGyver-esque Inventory Valuation Adjustment.
Note: teasing out wacky ideas and turning them into a reality sometimes takes a village. In this case, the village consisted of myself and one other resident: my QBO Advanced Certification partner, MB Raimondi. Thank you, MB, for helping me turn this fuzzy idea into a reality. I seriously could not have done this without you!