3 Tips For Navigating QuickBooks Inventory Valuation And Balance Problems

QuickBooks Inventory Valuation And Balance Problems

QuickBooks inventory management is a feature that allows for the tracking of the inventory ‘ledger’ — specifically items, quantity on-hand and value – in the system. Ideally, this ledger provides the detailed information that supports the Balance Sheet balance for the Inventory Asset account.

But, there are several scenarios and uses of QuickBooks that will cause the Balance Sheet balance for Inventory Asset account to not be in-line with the Inventory Valuation reports from QuickBooks. We’ll be addressing the following questions in this article:

  1. Why does this happen?

  2. How do we fix this?

  3. How do we prevent this in the future?

1. Why does this happen?

There are 4 reasons why the Inventory Asset Balance Sheet account will not align with the Inventory Valuation report.

  1. Corrupt Data File

In our experience, this is the least common reason for the variance, but it can happen. However, it is also the easiest to correct (see next section) so we recommend you start there.

2. Posting to Inventory Asset without an Item

This is a very common reason for the misalignment. Take a look at our examples below to see different occurrences of this error.

Example A: Inventory Asset account used on “Expenses” tab.
If a user enters a vendor bill, and uses the “Expense” tab (vs. the “Item” tab) and the Inventory Asset account, this will cause a discrepancy.


Example B: Adjustment Account on Inventory Adjustment
When making an inventory adjustment, users often think that they should post to Inventory Asset account.

Adjustment Account on Inventory Adjustment example vector business solutions.jpg

Example C: Journal Entry that posts to Inventory Asset
Similar to the above scenarios, a journal entry posting to the Inventory Asset account will cause misalignment.

Journal Entry that posts to Inventory Asset vector business solutions.jpg

3. Inactive Parts

This is also a very common reason for the misalignment. There are two unique occurrences of this problem, but both are due to the same core reason – items are made inactive.

Example A: Item Inactivated while quantity on-hand has balance
The first occurrence is when an item is deactivated, yet it still has quantity on-hand. When this happens, the quantity and value stay in the system, but are not included in the Inventory Valuation report.

Item Inactivated while quantity on-hand has balance vector business solutions example.jpg

Example B: Item is inactivated properly, but back-dated reporting is affected (a QuickBooks reporting limitation)
The second scenario is when an item is deactivated, because it has 0 on-hand and won’t be used in the future.   This is a valid time to make an item inactive. Yet, when you run a back-dated Inventory Valuation report and Balance Sheet, for a point in history when this item did have quantity on-hand, the reports won’t align. Unfortunately the Inventory Valuation report will not display this item, because it is currently (and validly) deactivated.

4. Rounding

This is also a very common reason for the misalignment, but is typically of very minor (immaterial) consequence. We see minor rounding (pennies and small dollar amounts) when inventory is very active or when multiple units-of-measure are used. Once the prior problems are eliminated, if you have a small residual discrepancy, you can safely assume it is a rounding error. And, if it is small, it is not material to your financial statements or operations, so you may opt to ignore.

2. How do we fix this?

For each reason, there is a unique solution.

1. Corrupt Data File

To resolve: We recommend you perform a QuickBooks File “Rebuild”, as a first step, whenever there is a possible corrupt data file. See attached procedures provided by Intuit for this Rebuild process.

2. Posting to Inventory Asset without an Item

To resolve: Note, it is important that you only change historical transactions, if you have confirmed with Finance, the CPA, and the business owner that it is OK to affect prior period financial statements. Therefore, this solution assumes that you will not be able to correct the historical transactions, but instead want to re-align the Balance Sheet by reversing the effect of these historical errors.

Step 1

Run a Custom Detail Report to identify the total $ associated with these historical direct postings to Inventory Asset

  • Menu: Reports / Custom Reports / Transaction Detail

  • Display Tab:

  1. Include ITEM field

  2. Sort by ITEM

  3. Date Range = All

  • Filters Tab:

  • Account = Inventory Asset

Step 2

On the report, determine the adjustment needed. This is the total balance of all postings without an item #, see below for example.


Step 3

Create a journal entry to reverse the effects of this balance created without items. For the example above the journal entry would Credit Inventory Asset by $21,200.95, and Debit Inventory Adjustment (cost of goods sold account) by $21,200.95.

3. Inactive Parts

Example A: Item Inactivated while quantity on-hand has balance

Step 1 

Run a Customized Item Price List Report to identify the inactive items with quantity on-hand

  • Menu: Reports / Lists / Item Price List

  • Display Tab:

  1. Include Quantity on Hand field

  • Filters Tab:

  1. Active Status = Not Active

  2. Qty on Hand >= .01 (greater than or equal to one penny – note that this will report both positive and negative quantities on-hand)

Step 2

On the report, determine the items that need to have their on-hand quantity zeroed out.


Step 3

Double-click on each item on this report to edit. Uncheck the Inactive check box.


Step 4

Zero out the quantity on-hand for each of these items via an inventory adjustment


Step 5

Edit the items to make them inactive again

Example B: Item is inactivated properly, but back-dated reporting is affected
There is actually no correction needed for this discrepancy. It is essentially a ‘reporting limitation’ in QuickBooks –inventory reports are usually automatically filtered to only show active parts. If you require inventory reporting to support each Balance Sheet account, and need to do so historically. We recommend that
Option 1: Run the Inventory Stock Status and Inventory Valuations at each month-end, before any closed-out products are inactivated. Export these reports to Excel in case they ever need to reproduce the supporting reports for history.
Option 2: In case of audit or other retroactive review, they can also opt to re-activate all items, run the reports, and then make the items inactive again.
Of course, neither solution (Excel stored reports, or re-activating old parts) is ideal. Both are workarounds for this reporting limitation.

4. Rounding

Any impacts of rounding should be immaterial, and therefore we don’t recommend any specific resolutions steps for these discrepancies. The effects of rounding are typically a few dollars or less.

3. How do we prevent this in the future?

Finally, let’s talk about how to keep these things from happening in the future.

1. Corrupt Data File

This isn’t possible to prevent, but you may want to do a monthly “file maintenance” process as part of your month-end close. Perform the Rebuild steps in the link below:http://support.quickbooks.intuit.com/support/articles/HOW12158

2. Posting to Inventory Asset without an Item

To prevent this problem, all QuickBooks users need to be educated to:

  • Never post directly to the Inventory Asset account (on a credit card charge, check, vendor bill, etc.). Anything that affects inventory should use the “Item” tab of these transactions. Below is an example of a correct bill for inventory purchases:

  • Inventory Adjustments should not have Adjustment Account = Inventory Asset. Instead, use an expense or cost-of-goods-sold account as the Adjustment Account. Below is a correct Inventory Adjustment:

  • If item value and/or quantity changes are needed, it is recommended to use inventory adjustment transactions, not journal entries.As a general rule, journal entries should not be posted directly to the Inventory Asset account, because this account balance should be supported by the inventory details. (Note that an exception to this rule is in correcting historical erroneous postings, as noted in solutions in this article).However, there may be occasion where the business’ CFO or CPA requires a journal entry to Inventory Asset for historical balance adjustments. In this case, we recommend that this journal entry be reversed in the following period. And, for the period in which the adjustment was originally made, be aware that the Balance Sheet and Inventory Valuation reports will not align.An alternative to making journal entries to Inventory Asset (for CPA and CFO requested adjustments) is to create a separate Balance Sheet account for these “non-item” adjustments. This Balance Sheet account would be managed via other close/reconciliation processes, and would not be supported by the inventory tracking features of QuickBooks.

3. Inactive Parts

To prevent this problem, all QuickBooks users need to be educated to

  • Before making an item inactive, adjust quantity on-hand to 0And, to avoid historical reporting challenges, see the notes in the prior section about the QuickBooks reporting limitations and options for handling.

4. Rounding

As mentioned in the prior section, this is of immaterial impact and cannot be prevented.

Please let us know if you have any questions about the material above. If you have found it helpful, please feel free to share it with any colleagues who may benefit from this information.

4 Common Questions Regarding Financial Reporting, Taxes, GAAP and QuickBooks® Compliancy for Public Companies

  1. What is the Difference between Cash and Accrual Accounting?

Cash and Accrual Accounting are the two principle methods of reporting financial statements. The basic differences are related to the timing of when the income and expenses are accounted.

Here is a simple explanation of Cash Accounting:
Cash Accounting accounts for income at the time payments are received. Accounts for expenses at the time the bills are paid.

Here is a simple explanation of Accrual Accounting:
Accrual Accounting accounts for income at the time the service was performed (or product is shipped). It accounts for expenses at the time the expense was incurred for operational expenses, as well as accounting for cost-of-goods at the time the service was performed (or product was delivered).

Cash Accounting is the simplest method of accounting, as it only requires knowing when the money leaves the bank or is deposited into the bank. However, Cash Accounting tells you less little about the actual business performance/profitability, as pent-up unpaid bills are not apparent on a cash P&L.

2. Since my taxes are done on a cash basis, do I need to do accrual accounting?

Your CPA may opt to submit your business taxes on a cash accounting basis, which is completely fine and may be in the best interest and most appropriate for your business. Usually, we recommend that your financials still be captured and managed on an accrual accounting basis. This gives the business more flexibility to report on either an accrual or cash basis. And, accrual accounting gives a more accurate picture of true business profitability by period. Also, as businesses grow, investors, banks, and auditors will typically want to see accrual based financial statements, for current as well as historical periods.

It is pretty straight-forward to switch from accrual to cash for tax return purposes. This is a reporting ‘toggle’ in QuickBooks, along with some known typical adjustments (e.g. Inventory AR isn’t completely reversed with this toggle). VBS can help you implement accrual accounting principles as well as ensuring your CPA will be comfortable with the cash-basis reporting at end of year.

3. How important is being GAAP compliant to our business?

Initially, a business may feel like GAAP (Generally Accepted Accounting Principles) financials are too much work (and cost) and have little value for their business. Initially, you may be right. But, we would recommend that a business consistently ‘grow toward GAAP’ as a financial philosophy.

For example, payroll accruals may be ‘overkill’ for a very small firm with a few days of month-end payroll overlap, and not outside financial reporting requirements. But, as the firm grows, this month-end pay overlap might become more material to the monthly profitability. And, investors, banks, or auditors may want to see true profitability considering the payroll liability outstanding.

So, in anticipation of this, we would encourage the small business to consider adding GAAP analysis and adjustments, one at a time, as their business grows.

4. Is QuickBooks® sufficient for a public company and/or is it Sarbanes Oxley compliant?

We often hear “we want to go public so we need to move off of QuickBooks”. Yes, it is true that most public companies use very extensive, complex (and expensive) systems to support their businesses. But, the rules around Sarbanes Oxley and public companies are not specific to systems or applications, they are specific to controls and processes.

Vector Business Solutions has public clients that are running on QuickBooks Enterprise. These companies have simpler financial needs that can be currently met with QuickBooks Enterprise and external controls and processes. Undoubtedly these companies will be looking at more robust systems in the near future, but they were able to go public using their current systems. VBS doesn’t provide public accounting services, but we do provide the QuickBooks knowledge and support to help the public company’s Finance department use QuickBooks to support their needs.

9 Frequently Asked Questions Regarding QuickBooks® Functionalities and Capabilities

We’ve all been there: An urgent need arises regarding your financials or accounting processes. So, you head straight to Google for the answer, but then what happens? Ads, opinions and spam get in the way. Each option vying for your attention, but more so, your wallet. So before you cold call each company on the first page of the search results to see if they can sell you some application or process as a solution, let’s dive into 9 FAQ’s (frequently asked questions) about QuickBooks® functionalities to try and save you time and money. Hopefully one of these answers helps solve your “Can QuickBooks® do (insert any function)” questions.

Below is a table of contents in case you want to save time and jump straight to a certain section. Just click the question you desire to have answered to automatically scroll to that point in the article.

  1. Is QuickBooks® just a financial system?

  2. What types of information or reporting does QuickBooks® have beyond a P&L and Balance Sheet?

  3. What is QuickBooks® Hosting, and how does it differ from QuickBooks® Online (QBO)?

  4. Does using QuickBooks® require extra ongoing work?

  5. When is QuickBooks® not the right choice?

  6. Can QuickBooks® manage inventory?

  7. Does QuickBooks® support tracking inventory at multiple inventory sites and bin locations within those sites? (3pls, multi-warehouse operations, etc.)

  8. Does QuickBooks® provide Material Requirements Planning (MRP) capabilities?

  9. When does it make sense to use QuickBooks® Payroll solutions?

1. Is QuickBooks® just a financial system?

Actually, no. QuickBooks can be used to record ‘straight financials’, but it can also be used for much more.

If you are a product company, QuickBooks can help you with order management and inventory. It can print your pick-lists and pack-slips, and track your Accounts Receivable. If your business does ‘lite’ manufacturing, QuickBooks can help you track your component inventory and consume those components in an Assembly Build process.

If you are a services company or general contractor, QuickBooks can help you see profitability by customer engagement via job-costing features. Time-tracking and rebillable expenses can be managed by QuickBooks, to ensure all costs are captured and rebilled as needed.

Although QuickBooks has its limitations, as do all systems, it is a low-cost of entry system with quite a bit of flexibility for businesses. And many businesses aren’t aware of all of the features that QuickBooks can provide, if implemented and managed properly.

2. What types of information or reporting does QuickBooks® have beyond a P&L and Balance Sheet?

If implemented fully and used consistently, QuickBooks can provide businesses with some helpful information for managing your business. Some examples are below:

Inventory Information

  • On-hand by inventory location, reorders needed

  • Total valuation and valuation by location

Customer Order Information

  • Open Orders by Item or Customer

  • Fulfilled Orders by Item or Customer

  • Customer Sales Rankings

Vendor Order Information

  • Open Purchase Orders (including options to view by Customer, if orders are for specific customers)

Job Cost Information

  • Estimates vs. Actuals – Summary and Details – by Customer/Job

Employee/Payroll Information

  • Sales by Representative and Profitability by Representative (for commission / bonus reporting)

  • Salary History and Pay by Customer

  • PTO Available

3. What is QuickBooks® Hosting and how does it differ from QuickBooks® Online (QBO)?

QuickBooks Online is a unique Intuit product, it is accessed via a browser-interface, and all functionality is contained within the browser.

QuickBooks Pro, Premier and Enterprise are the ‘desktop/server’ software products. These were designed for use with desktops and a server, and they operate via a traditional “windows” style interface.

Currently the Pro/Premier/Enterprise products have much more robust functionality than the QuickBooks Online product, including inventory management features. They are also often preferred due to their inherent ‘usability’ in a Windows environment.
However, despite the product limitations, QuickBooks Online has gained business interest for two big reasons:

  • Access from anywhere/anytime as long as you have internet capability

  • No need for special servers, hardware or network capabilities

Due to these reasons, QuickBooks Hosting of the Pro/Premier/Enterprise has become a new option. QuickBooks Hosting is a concept where another company, called a Hosting Provider, provides the server/desktop environment, remotely and ‘hosts’ the Pro/Premier/Enterprise product for you. They also provide access anywhere, anytime as long as the user has an internet connection. QuickBooks is not installed on the local PC, but is instead installed and managed by the hosting provider. Many hosting providers also provide additional services or software, and they usually guarantee data backups and recovery, as well as specific availability. Hosting Providers charge a monthly fee for this service, which is usually a reasonable fee considering the hardware/software/services they are providing.

Several of these Hosting Providers are linked below:

Trapp Online www.trapponline.com

RightNetworks www.rightnetworks.com

InsynQ www.insynq.com

4. Does using QuickBooks® require extra ongoing work?

The truth is, it depends.

If you are currently managing your business using spreadsheets, and creating Purchase Orders, Sales Orders and Invoices using Microsoft Word, you will probably find that QuickBooks, when implemented properly (and with appropriate training and procedures), can decrease your work efforts and increase the quality and accuracy of the information you have about your business.

If you don’t currently manage inventory, payables, receivables, etc., at all, you will find that QuickBooks requires extra business process vigor and information management discipline. We would challenge you to consider that your business may not manage very effectively without this information, so you will probably need to invest the extra energy to gather and maintain this information, whether or not QuickBooks is your system of choice.

5. When is QuickBooks® not the right choice?

For most start-up businesses, QuickBooks is a low cost-of-entry system that can provide the base functions needed for Finance and Operations. Also, add-on integrated systems can provide additional functionality not inherent to QuickBooks. (Note: Many integrated solutions can be found at at https://desktop.apps.com/home

Some business types find that they outgrow QuickBooks more quickly—or opt to find a more unique/specialized solution right out of the gate. Manufacturing businesses that require complex bill-of-materials management and materials requirements planning (MRP) sometimes find QuickBooks capabilities insufficient or just too simplistic for their needs.

Other businesses have very unique and specific business flows, such as agency royalty management (for publishers, entertainment or sports agents), or medical insurance management (for doctors, dentists), and find that industry-specific software is needed to manage their business.

Some of these businesses use industry-specific solutions for a portion of their business (e.g. MRP, bill of materials, or insurance/patient accounts receivable) while still using QuickBooks for full financial reporting and vendor/bill management. This does require integrating two systems, or maintaining data in two places. But for smaller business, this is sometimes an acceptable approach to keeping their overall systems costs low.

We have found that the most important factor to consider when determining if QuickBooks is a good fit is complexity/uniqueness of your business model, and not the size of your business. QuickBooks is a somewhat “generic” system, so it becomes more difficult to manage as business processes/flows and data is more unique. But, several of our clients with straight-forward business models are in the $20M-$50M gross revenue range, and successfully manage their operations and financials with QuickBooks.

6. Can QuickBooks® manage inventory?

Yes, QuickBooks desktop products (Pro, Premier and Enterprise) provide the ability to track both the quantity and the value of products/items. Inventory is ‘increased’ by using the Purchasing/Receiving features, or by using the Assembly Build to create to assemblies. Inventory is ‘consumed’ by using the Sales Order/Invoice features, or by using the Assembly Build to consume components. Key factors in successfully managing inventory in QuickBooks include:

A Good Starting Point: An accurate accounting for inventory on-hand quantity and value at a specific point in time. Often, a physical inventory is also a good time to implement QuickBooks inventory.

Process Mapping: Identify all business processes that affect inventory, and determine the QuickBooks transactions that will be used to record these business activities in QuickBooks on a real-time basis.

Real-Time Process Integration: Integrate QuickBooks with your business process, as tightly as possible. For example, use QuickBooks to create the packing slip at time of product shipment. By doing this, inventory is decremented in QuickBooks at the same time it actually leaves your warehouse.

Ownership: Identify the inventory ‘owner’ within your business. The person who will be ultimately responsible for valid inventory tracking.

7. Does QuickBooks® support tracking inventory at multiple inventory sites and bin locations within those sites? (3pls, multi-warehouse operations, etc.)

QuickBooks Enterprise (but not Premier or Pro) does have an Advanced Inventory feature that can be purchased for an additional annual fee.

This Advanced Inventory feature includes the ability to track inventory at multiple warehouses, or “inventory sites”. Some companies use this feature to track multiple locations within a single warehouse and will have inventory sites for Components, WIP, Finished Goods, and Quarantine/Inspection.

This Advanced Inventory feature also provides the ability to track BINs or Shelf locations (or any other sub-location distinction), if so desired.

We typically recommend tracking Inventory Sites when there is a physical segregation to your inventory. Or, even when there is a logical segregation – such as inventory that you would not want to ship from (such as Inspection or Quarantine inventory). Some of our clients segregate inventory in order to reserve inventory for special categories of customers – such as wholesale inventory versus online inventory.

We typically recommend using QuickBooks BIN tracking feature only when this information will be helpful for picking/locating inventory for assembling or shipping. There is significant effort in consistently recording BIN activity and BIN to BIN transfers within QuickBooks, so we encourage our clients to look at the value this tracking will bring to their business before diving into this feature.

8. Does QuickBooks® provide Material Requirements Planing (MRP) capabilities?

The short answer is ‘no’, QuickBooks does not inherently provide MRP capabilities, such as production scheduling and purchase planning/scheduling. QuickBooks does not look at the sales plan, in order to back into the production requirements, and it does not drive a component’s purchasing schedule based on this information. There are many add-on products that may assist with some of these features that can be found at https://desktop.apps.com/home.

Meanwhile, we find that smaller companies that need these features will use QuickBooks reports and some off-line analysis to support these needs until they can afford the costs of a more full-functioning MRP system.

9. When does it make sense to use QuickBooks® Payroll solutions?

Intuit has payroll offerings that are fully integrated and launched from within QuickBooks. When set up properly (with the help of Intuit and your QuickBooks ProAdvisor, together) the accurate Financial tracking of the paychecks is automatic, so no journal entries are needed. Intuit has offerings that provide full-service (tax returns, tax deposits) just like many of the payroll companies provide. The difference is that integrated payroll solutions are ‘launched’ from within QuickBooks. You can take a look at their payroll service here: https://payroll.intuit.com/

We usually recommend that Intuit integrated payroll solutions be considered when any of the following are true:

  • Employee time-tracking is in QuickBooks (so time tracking can/will feed their paychecks)

  • Job-costing of payroll is required (service companies are typically a candidate for this)

  • Class-tracking of payroll is required

Otherwise, an existing outsourced Payroll solution (such as ADP, Paychex) may be working just fine for a business, and changing this is not recommended.