Inventory Count Variance

The Inventory Count Variance report generates a list of theoretical quantities compared to actual inventory counts for a specified period.

The theoretical ending inventory count is calculated by taking the actual ending inventory count from the preceding period as the beginning inventory, then adding all the purchases (from AP invoices and manual checks, or from portion cut sheets; or for prep items from the prep item creation posting), and subtracting all of the usages, the sales of the items (from the recipes in the menu items, the POS sales data, the ingredients in a prep items, and the whole cuts when portioning) and any inter-store transfers of inventory or wasted/donated inventory (inventory adjustments, which are not broken out in this report).

The actual inventory count used is the count you select by its posting date, after first entering a date range and getting prompted by Compeat to select a posting date.

The theoretical usage in dollars is calculated by taking a weighted average of the actual dollars per unit (i.e., the sum of dollars divided by the sum of units) during the specified time period, and then multiplying the weighted average dollars per unit times the number of theoretical usage units. If the start date of the time period (e.g., 1/1) is immediately following an inventory count date (e.g., 12/31), the inventory quantity and unit cost of that date will be included in the calculation.

Note: An asterisk is displayed on the report in the Beginning Inventory column for uncounted items— that is, items for which no count was entered so the theoretical count was used instead. Variance is, by definition, zero for uncounted items.

It is expected that there will be some variance between the theoretical and the actual inventory. This report provides a numeric and percentage variance so you can determine which are normal variances and which indicate a problem of some sort.

Note: A negative variance indicates you have a lower actual count than theoretical count; a positive variance indicates you have a higher actual count than theoretical count; three asterisks (***) in the Variance % column indicate there was a division by zero, which is undefined.

The report has drill-down capabilities for the Item Description, Receiving, Xfer In, Prod/Portioning, Xfers Out, Waste, Theo Usage, and Theo End Inv fields. Most drill-down fields will open the Inventory Transaction History Report, which shows details of the history of the selected item.

This report also has an ‘Export to Excel’ option that sends all the data in the report to excel with the data for each item flattened to one row, which makes it easy to sort and sub-total rows without the need to cut and paste information.

Report Options

Click to view/hide picture.

The following options are available to customize your report:

Note: Because the purpose of this report is to compare actual counts to theoretical quantities, it is important to run the report on days when the actual numbers are most accurate, that being the day after a count to the day-of a count. Although the report will accept dates that fall in between counts, the variances returned will be less reliable.
This report has an alternative use as a way to view theoretical quantities. If you enter the beginning date as the day after a count, and the ending date as, say the current date, the system will show theoretical quantities for the current date and allow you to spot-check inventory between counts.

If you are using the ‘Last Invoice Valuation’ Method, the variance and theoretical ending inventory dollar amounts on your report will likely be different depending on the selection made here. Visit the Compeat Support Website for additional information on this topic and more. Customers using a ‘Weighted Average’ valuation method will not see a difference with this setting adjusted.

Note: In order to view costs on this report, your group must have "Show Costs on Inventory Count Variance Report" checked under Inventory > Inventory Counts of the Security > Groups > Screen Access tab.

Note: When you select the Expand non-produced option, there is a chance that the ending inventory value will not match the beginning value on the next reporting period. To see the exact ending/beginning values that will be written to your General Ledger, turn this expansion option off. This is a result prep items being turned back into inventory products for the sake of report display and does not affect your cost of goods calculations or your P&L.

Report Data

The Inventory Count Variance report appears similar to the following:

Note: All items created through portioning and prep productions are reported in the column (+/- Prod/Portioning). Items depleted through portioning and prep production display as part of the Theo Usage column, alongside depletion from sales mix, commissary shipments, and outside order shipments.

 

Description of report columns

Each item is identified by its Description and/or Item # at the beginning of the row. Most fields can be displayed alternatively in dollars or quantity, depending on the report options selection. The following descriptions assume a quantity selection, but the description is much the same if Costs were selected.

Note: the math used to calculate the Theoretical Ending Inventory is shown in the column headers.

Note: Both the Inventory Theoretical Cost report and the Inventory Count Variance report have columns representing theoretical usage. In the Inventory Theoretical Cost report this is the Qty Used, Theo column; in the Inventory Count Variance report, it is the Theo Usage column. The calculations for each of these are slightly different between the two reports, so in some cases the numbers may not match.

In the Inventory Theoretical Cost report, the Qty Used, Theo amount is calculated as sales + waste quantities.

In the Inventory Count Variance report, the Theo Usage amount is simply the sales amount. Thus, Theo Usage = Qty Used, Theo - Waste.