Theoretical vs Actual Variance Report

In Beta

This feature is in beta for select customers. Contact your CSM for more information.

The Theoretical vs Actual Variance report compares the theoretical usage of certain inventory items or categories against actual data, in terms of both quantities and dollar amounts and their percent of net sales. It also shows and compares the variance between them.

The Theoretical vs Actual Variance flags variances that are in excess. This means that the location used more product than it ideally should have. The inclusion of positive and negative variance columns allow users to get a snapshot of how an item category is contributing to the variance.

In addition to theoretical vs actual values and variances, this report allows users to expand/collapse the ‘actual’ usage column. This function opens columns that show what is calculated into the usage, including:

Beginning inventory count
Transfers
Ending inventory count
Purchases (Items on AP invoices/credit memos)

This report provides a deeper dive into the quantity, cost, and use of each purchased item to find solutions to limit the biggest food cost variances.

Users can drill down into the items that make up the item categories and their transactions. Users can also view a full list of all the items.

This report calculates theoretical usage using historical POS (point of sale) and PMIX (menu item) data.

Navigation

The My Reports search bar can be used to search R365's entire catalog of reports.

Open the Reports app.
Navigate to My Reports.
Enter all or part of the report name in the search bar.
The Results tab will open with the list of search results.
From beneath the report name, click Run to run the report with the selected report view.
-OR-
Click Customize to adjust the report parameters and run the report.

Report Parameters

Field	Description
Location	The location that the report is run for. The location selection will determine the counts that are available in From Count and To Count.
From Count	Starting inventory count from which to pull data. The From Count and To Count selections create a date range for the generated report to display data.
To Count	Ending inventory count from which to pull data. The From Count and To Count selections create a date range for the generated report to display data.
Key Items Only	If Yes is selected, the report includes key items only. If No is selected, the report includes all items.
Show Unapproved	If Yes is selected, the report includes data from unapproved transactions. If No is selected, the report only accounts for approved transactions. Yes is selected by default. This allows accounting staff to close the period at their discretion while enabling operations to run vital reports in real time without waiting for all transactions to be approved.
Count Time	Time of day from which transactions are pulled. Options include: Beginning of Day - Uses transactions from the DAY OF the From Count to the day BEFORE the To Count.. End of Day - Uses data from the day AFTER the From Count through the DAY OF the To Count. Example: If Count From is set to 02/10/YY and Count To is set to 02/17/YY, the report uses transactions from 02/11/YY to 02/17/YY.
Item Category 1, 2, 3	Filters the items that appear in the report by the selected item categories.
Show % of Sales	If Yes is selected, the Actual %, Theo %, Variance %, Total Positive Variance, and Total Negative Variance columns appear in the report. If No is selected, the report does not display these three columns.
Sales Account	Filters the report to show data for selected sales accounts.
Efficiency Calculation (Waste)	Include or exclude waste in percentage calculations.
Efficiency Calculation (Donations)	Include or exclude donations in percentage calculations.
Actual Usage Dollar	Determines whether item costs are based on the: Transaction Cost - Pulls the actual item's cost as it was at the time of each transaction that occurred between and including the beginning and ending count. Unit Cost - Pulls the item's current inventory cost from the Item record as of the time the report was generated. If using item locations, this option pulls cost data from the store's item location. End Count Cost - Pulls the cost of all items from the ending inventory count used for this report.
Waste Calculations	Shows or hides inventory that was documented in the waste count.
Donation Calculations	Shows or hides inventory that was documented in the donation count.
Items to Display	If Counted Only is selected, the report shows only inventory count items. If All Items is selected, all purchased items are included in the report.
Calculate Based On	Determines whether the report is calculated based on the default Inventory UofM 1 or the item's Reporting U of M.
View options	Provides the user with options to select, save, edit, or delete a report view. Learn more about using my reports.
Run	Generates the report. The green arrow button opens a drop-down from which users can Export, Email, or Print the report.

Report Columns

The header displays the following information:

Begin Inventory - First inventory count included in the report.
End Inventory - Final inventory count included in the report.
Report Parameters - The report parameter selections for the data shown.
Net Sales - Total net sales for all items in the report.

Column	Description
Item Categories	Item categories can be clicked to drill down into the items that make up the category.
Count UofM	Unit of measure for each item as used when conducting an inventory count. This UofM is either the Inventory UofM1 of the Reporting UofM. This is dependent on what is selected in the report parameters. This column is only visible when drilled into an item category.
Avg Cost	Average cost per unit. This column is only visible when drilled into an item category.
Quantity Usage
Theo	Theoretical number of items used.
Begin	Starting quantity of each item as shown in the first inventory count that was included in the report. This column is only visible when the Actl column is expanded using the + icon.
Purch	Quantity of items purchased for the location. This column is only visible when the Actl column is expanded using the + icon.
Xfer	Quantity of items transferred to or from the location. This column is only visible when the Actl column is expanded using the + icon.
End	Final quantity of each item as shown in the last inventory count that was included in the report. This column is only visible when the Actl column is expanded using the + icon.
Actual	Actual number of items used. When expanded, the Beginning, Purchased, Transfer, and End columns appear.
Var	Difference between the theoretical amount and the actual amount. This is theoretical minus actual usage.
Waste	Number of items that were not used and therefore went to waste. These appear as positive values. This column appears only if Show is selected on the Waste Calculations parameter.
Donation	Number of items that were donated. This column appears only if Show is selected on the Donation Calculations parameter.
UnExpl Var	Difference between variance and waste. This is theoretical minus actual plus waste. This column can help users better understand why certain items have larger variances than others.
Effcy	The theoretical quantity over the actual quantity used. This shows how efficiently this item is being used.
Dollar Usage
Theo	Total theoretical cost of the item. Actual Usage Dollar - Transaction Cost When Actual Usage Dollar is set to Transaction Cost, the theoretical cost shown here is the total theoretical cost of all the transactions that occurred between counts, including the day of the beginning and ending count. The item costs used to calculate this value are the the costs of the item at the time of each different transaction, which are based on the most recent selling price of the item relative to the various transaction(s).
Actual	Total actual cost of the item. When expanded, the Beginning Cost, Purchased Cost, Transfer Cost, and Ending Cost appear. Actual Usage Dollar Parameter Setting When Actual Usage Dollar is set to Transaction Cost, the actual cost shown here is the total cost of all the transactions that occurred between counts, including the day of the beginning and ending count. The item costs used to calculate this value are the various item costs as they were at the point in time of each different transaction. When this parameter is set to Unit Cost, the actual cost shown here is the total actual cost based on the average cost of the item across all the transactions between and including the beginning and ending counts. When this parameter is set to Ending Count Cost, the actual cost shown here is based on the cost of the item at the time of the ending count.
Var	Difference between the actual cost and the theoretical cost. This is theoretical minus actual cost.
Waste	Cost items that were not used and therefore went to waste. This column appears only if Show is selected on the Waste Calculations parameter.
Donation	Cost of items that were donated. This column appears only if Show is selected on the Donations Calculations parameter.
UnExpl Var	Difference between variance and waste. This column can help users better understand why certain items have larger variances than others.
Effcy	The theoretical cost over the actual cost of items used. This shows how efficiently this item is being used.
% of Sales The following columns appears only if the Show % of Sales parameter is set to Yes.
Theo %	Percentage of theoretical sales.
Actl %	Percentage of actual sales.
Total Pos Var	The item category’s total variance if the variance is a positive value.
Total Neg Var	The item category’s total variance if the variance is a negative value.
Var %	Difference between actual percent and theoretical percent of sales.

The totals in the column all add up to the row labeled Grand Total, which shows the grand total of all values in the column.

R365 Tip
Clicking the Grand Total label will open a new tab with a full list of every item considered for this report.
This opens a list view of all items compared to one another.

Email, Export, or Print the Report

This report can be emailed, exported, or printed in custom formatting directly from the reporting window. Click here to learn how to send, export, or print this report.