How to Analyze Inventory Costs in QuickBooks
As is typical at the end of the year, we recently received several messages from readers asking how to analyze QuickBooks inventory costs. All of our correspondents said they knew that QuickBooks does not track costs by FIFO or LIFO, but they were having problems rectifying their year-end inventory values (for tax returns) with a meaningful analysis of those costs. Many of the messages included the statement, "This doesn't add up correctly". Several accountants said they were trying to evaluate client businesses that were for sale, and couldn't present accurate monthly, quarterly, or yearly inventory analysis figures to potential buyers.
Here's the problem: QuickBooks computes the average cost over the life of the inventory item, not as a weighted average. Weighted average (which is the generally acceptable paradigm for accounting) means measuring and tracking costs for an accounting period (fiscal year for most small businesses).
To get a weighted average, you must customize QuickBooks reports that show your purchases and sales for the accounting period in question. Then export the reports to Excel in order to perform the calculations you need.
Businesses that have sophisticated inventory needs should not be using QuickBooks unless they can find a third party application that will help them track inventory the way they have to. QuickBooks was originally designed for service businesses, and even though Intuit added the ability to track inventory, the software does not track or report on inventory the way that accounting applications that are designed for inventory-based businesses do.