If you are using QuickBooks to manage your inventory, you need to understand how QuickBooks deals with the cost of inventory items. Unfortunately, the term “cost” is used in several different ways, and it can get confusing. Here is a quick rundown of how QuickBooks handles things.
I’ll focus on Inventory Part items, which are the true “inventory” items in QuickBooks, with a little detour to talk about Inventory Assembly items.
Cost Fields in QuickBooks
If you look at an Inventory Part item, you will see that there are two cost fields.
Cost, on the left, is a “reference” field. That is, it doesn’t have any direct bearing on the valuation of your inventory, the cost of your inventory in your inventory asset account. I wish they had another name, because it is confusing to talk about it. I refer to this as the “last purchased cost”, although that isn’t always exactly right.
If you purchase an item and receive a bill for it, the cost that you receive the item at will usually be stored here (but not always, that depends on how your company file is set up). You can edit this cost directly in this window. This does not have a direct effect on your inventory valuation.
The avg cost field, bottom center, is the field that is used in the calculation of the value of your inventory. This is calculated by QuickBooks based on the cost of receipt (and adjustment) transactions. You cannot directly edit this in this window.
If you look at the edit item window for an Inventory Assembly item you will see a third cost, the total bill of materials cost, which is another “reference” cost (not directly affecting inventory valuation). I’ll discuss that in more detail later.
Inventory Valuation
QuickBooks values your inventory using an average costing calculation, as opposed to other types you may be familiar with, such as LIFO, FIFO, or specific costing. This can be a complicated subject – I am only going to go into this lightly. Let’s look at a simple example.
- If start with an item with no quantity, no value, and receive a quantity of 10 at $1.00 each
- 10 items and a value of $10.00, we added another 10 items at a value of $20.00, so, you will see that the cost is $1.00, and the avg cost is also $1.00. You have $10.00 of inventory in your inventory asset account.
- If I then receive another 10 items, but at a unit cost of $2.00, you will usually see the cost value set to be $2.00. However, the avg cost of your inventory will show as $1.50. We started with we have 20 items with a value of $30.00. That gives us an average cost of $1.50.
If you sell one of these items in an invoice, the Cost of Goods Sold (COGS) account is incremented by the average cost of the item at the time of the sale.
One thing that I will note, briefly – if you sell all of your inventory, and then continue to sell the item so that you go to a negative quantity, the costing calculation runs into problems. QuickBooks can’t accurately account for a negative balance, and you can see some very odd figures show up in the average cost field, and your inventory valuation reports. Once you bring the balances back to positive these figures should resolve themselves, but it is always a good idea to not allow inventory balances to go negative.
Editing the cost field in an inventory record will have no bearing on the avg cost, or your inventory valuation. The only way to directly change the avg cost or valuation is to use the inventory adjustment function and do a “value” adjustment.
Manufacturing Cost
Let’s take a look at an inventory assembly item. The WHAS wheel assembly has two components, a screw (two of them) and a roller. Note that there are three costs shown in this window.
The Cost field (15.00) has no real bearing on valuation of this item as I have discussed above.
The Avg Cost field (32.00) is the cost that QuickBooks uses to calculate the value of this item.
The Total Bill of Materials Cost field (32.00) is not directly tied to the cost or avg cost values. This is the sum of the cost values of the components in the BOM. In our starting example it matches the avg cost, but they are not connected.
If I build this assembly item, the avg cost of the assembly will NOT be adjusted by this total bill of materials cost. Instead, QuickBooks will take the avg cost of the component items and roll that into the received cost of the assembly. You can’t look at this screen and tell what the exact cost of the build will be. Remember, the total bill of materials cost shown here is based on the cost field of the components, not the avg cost value. But avg cost is what is used in valuation.
For a more detailed explanation of costs in inventory assembly items, see my article on Understanding Total Bill of Materials Cost in QuickBooks.
There are two possible ways to track retainage (retention) that you owe to your subcontractors; one method utilizes a Sub-Account of Accounts Payable, called Retainage Payable, and the other method utilizes an Other Liability Account, called Retainage Due to Subcontractors. Please review the setup and use of both methods, and choose whichever one seems more appropriate for your use.
You will need to inform your accountant of this at year end, so he or she, may make the necessary Journal Entries.
Method 1: Retainage as an Accounts Payable Sub-Account
Accountants tend to really like this method, but, it is a two-part process for the person actually doing the billing, which means that it’s error prone, simply due to normal day-to-day distractions.
From the Lists menu, choose Chart of Accounts
- From the Chart of Accounts window, click the Accounts button (lower left), and choose New
- Choose Accounts Payable as the Account type
- Enter an Account Number
- In the Name Box, enter Retainage/Retention Payable
- Click on the Subaccount of box, and choose Accounts Payable
- In the Description box, enter Retainage/Retention Payable on Contracts
- Click OK
Deducting Retainage/Retention Payable on a Vendor/Subcontractor Bill
If you have created a Purchase Order for this subcontractor and are now receiving his first progress billing, choose Vendors, and Receive Items with Bill, and if you have not created a Purchase Order, simply choose Enter Bill.
Select the Subcontractor from the drop down list; change the date, enter a Reference Number, the total amount of the bill, select terms, due date, and enter a memo if applicable – select either the Expenses or Item tab, and pull in the appropriate Item Code or Expense account associated with the vendor bill, and select the Customer: job. In the next blank line, again, pull in either the Item Code or Expense Account, enter the retention as a negative amount, in the memo field type in less retainage held, choose the Customer: job from the dropdown list.
Sample 1 below shows a sample bill created from a Purchase Order using the Item tab:
Sample 2 below shows a sample bill created using Enter Bills and the Expenses tab:
Recording Retainage Payable
Select Enter Bills; change A/P Account from Accounts Payable to Retainage/Retention Payable. Select your subcontractor; enter date of original subcontractor invoice, in the Ref. No., input the invoice number followed by, –R to indicate Retainage/Retention, enter amount due, change your terms to reflect when you will pay the retainage, select either the Item Code or the Expense account, and select the job.
Sample 1 shows a bill for retainage entered using the Items tab:
Sample 2 shows a bill for retainage entered using the Expenses tab:
Method 2: Retainage Payable as an Other Liability Account
This is a simple one-step process for the person actually doing the billing, and at the end of the year, the accountant will need to do a Journal entry to move the dollars for tax return purposes.
From the Lists menu, choose Chart of Accounts
- From the Chart of Accounts window, click the Accounts button (lower left), and choose New
- Choose Other Liability as the Account type
- Enter an Account Number
- In the Name Box, enter Subcontractor Retainage/Retention Payable
- In the Description box, enter Retainage Payable on Contracts
- Click OK
Setting up Items to Deduct Retainage Payable
- From the Lists menu, choose Item List
- From within the items List window, click the Item button (lower left), choose New
- In Type box, select Other Charge
- In the Item Name/Number box, type in 92 Less Sub Ret
- In the Description box, type in Less Subcontractor Retainage/Retention
- In the rate box, leave the amount set to 0 (you cannot use percentages in the detail of the bill) in the
- Account box, select the account used for Retainage Payable on Contracts
- Click OK to create the new item
NOTE: If you have different flat rates of retainage (retention) that you use, a separate item can be created for each of them using the rate in the Item Name|Number.
Deducting Retainage Payable on a Vendor/Subcontractor Bill
If you have created a Purchase Order for this subcontractor and are now receiving his first progress billing, choose Vendors and Receive Items with Bill, and if you have not created a Purchase Order, simply choose Enter Bill.
Select the Subcontractor from the drop down list; change the date, enter a Reference Number, the total amount of the bill, select terms, due date, and enter a memo if applicable – select either the Expenses or Item tab, and pull in the appropriate Item Code or Expense account associated with the vendor bill, and select the Customer: job.
Sample 1 shows a deduction for retainage using the Items tab:
Sample 2 shows a deduction for retainage using the Expenses tab:
While this is a much more simple process than Method 1, it will not reduce total expenses or Cost of Goods Sold, on a Profit and Loss Report. You will need to inform your accountant of this at year end, so he or she, may make the necessary Journal entries.
Recommendations:
Create a copy of your actual QuickBooks company data file and experiment with each of these methods to determine which is the right method for your company –and discuss this with your accountant so that they are aware of what you are doing!
Did you find this article helpful? If so please take the time to leave a comment – or share it with your friends.
QuickBooks Pro & Premier 2011 and Enterprise Solutions 11.0 offer new and improved Purchase Order Reporting.
The Open Purchase Orders Detail report is new in QuickBooks 2011 versions (Pro, Premier and Enterprise 11.0). As the name implies, the report displays items (cost codes) that haven’t been received in full.
I’m excited to see that after many years, Intuit has finally fixed the Purchase Order reports to ACCURATELY display open balances, without a lot of additional manipulation by the user.
To find the new report, from the Report menu in QuickBooks Pro and Premier 2011 and Enterprise 11.0 -> Purchases -> Open Purchase Order Detail.
Right click on the image to enlarge it.
This report lists Purchase Orders by Items or Cost Codes that haven’t been received in full. By default, QuickBooks displays the following information for each Item or Cost Code:
- Date – the date of issue
- Num – the Number
- Name – the Customer and/or Job that the item ordered is for
- Source Name – the Vendor or Subcontractor who you placed the order with
- Memo – the Item or Cost Code description used
- Delivery Date – if you utilize the Delivery Date field on the form, the delivery date you entered will display
- Qty – the original Quantity, Units, or Hours that was ordered
- Rcv’d – what portion of the original quantity ordered has been received
- Backordered – what portion of the original quantity ordered has not yet been received
- Amount – the original dollar value
- Open Balance – the dollar value that has yet to be received
Right click on the image to enlarge it.
QuickBooks groups the same items/cost codes together.
Right click on the image to enlarge it.
Personally, I would change the way the report displayed information. Rather than to display the information based on Items or Cost Codes, I would like the information to be grouped by Customer and then by individual Job. To accomplish this, I would click the Total By drop down menu and select Customer.
Right click on the image to enlarge it.
The Open Purchase Order report has been improved, as it now displays an accurate Open Balance, however, you cannot modify the report to pull in Customer, Job, or Item (Cost Code) Information, however, it only provides a brief summary.
Right click on the image to enlarge it.
The Open Purchase-Orders by Job report has also been improved, as it now displays an accurate Open Balance.
Right click on the image to enlarge it.
In order for any of these reports to work correctly you will have to enable the use of Purchase Orders (Edit menu -> Preferences -> Items & Inventory -> Company Preferences -> Inventory and purchase orders are active option must be checked); and you MUST use Quantities when you create a Purchase Order, when you receive the item, or enter a bill against it.
Right click on the image to enlarge it.
My overall opinion is that for any company that issues Purchase Orders, this is a marked improvement in the overall functionality of QuickBooks.
——————————————-
 |
Request our FREE 142-page “What’s New in QuickBooks 2011? eBook, by completing a simple request form.This eBook will provide you with with all the information I’ve posted here in our blog, plus MORE!
Once you’ve completed our simple request form, you’ll have instant access to this 142-page .pdf eBook, designed to be duplex printed and put in a binder for future reference. |
A reader asked the following question:
We are a small contractor in San Diego and we are wondering how we get around having to always do an inventory adjustment to get our purchases to the correct COGS account? We have (2) departments but they are both doing construction projects: Service dept does smaller installs and Contracts dept does the bigger jobs so I have been doing a JE to move the material from the Contracts material COGS account to the service COGS account.
Is there an add-on for QB Enterprise 8 that we could use?
Thank you so much for your website/blog. I’ve been reading it faithfully every day!
____________________________________________________________________
There are several inventory add-ons for QuickBooks Pro, Premier and Enterprise; check out these 3rd party add-ons at the Intuit Marketplace.
Recording costs and income with an item
Based on your comment about “always having to do an inventory adjustment in order to get your Purchases to the correct COGS account”, makes me think that perhaps your items are not correctly set up.
Make sure that your QuickBooks Inventory Items are set up so that they capture both the cost/purchase account as well as a sales account; this method is called a “double-sided” item.
When any type of QuickBooks Service, Inventory, Non-Inventory, Other Charge Item is set up this way you are able to capture the purchase price as well as the sales price.
When you enter a bill from a vendor for the inventory item (or write a check) you should be using the ITEM tab and not the Expense tab.
I’m confident that if you aren’t currently using the QuickBooks Purchase Order function that you would find that beneficial also.
Additionally, rather than using Journal Entries to classify whether it was the Service or Contracts Department – consider using “classes” to handle that.
