Consolidated Commodity Pricing Trends

The Consolidated Commodity Pricing Trends report allows users to compare product costs and prices in one reference market with product costs and prices in other markets. Click HERE to see Related and Recommended setup.

The summary report displays data at the Location level, which can be used to compare and rank locations, markets, and concepts based on aggregated consumption, purchases, pricing variances, and total net sales. This view will also help identify locations for which the cost fluctuation is significant compared to sales.

The detailed "drill-down" report displays data at the Product level, which can be used to compare and rank products based on aggregated consumption, purchases, pricing variances, and total net sales. This view can also be used to rank products by how much their individual costs and purchase prices have fluctuated over the selected period.

 

Menu Paths: Net-Chef > Purchasing: Reports > Consolidated Commodity Pricing Trends
Menu Paths: Net-Chef > Reports: Purchasing > Consolidated Commodity Pricing Trends

Retrieval Note: Unlike other consolidated reports, the user does not need to select a hierarchy; the report will include all locations within the highest hierarchy level associated with the user.

 

Report Controls

Reference Data: When "Calculation Type" is 'Variance', retrieve by Market or Location, select a specific option and reference date.  This will provide a set of reference prices against which a location’s actual prices can be compared.  (When a location is selected, the report will look at bid prices in the location’s market; the report is NOT using Location Product prices.)

Objective Locations: Select a Concept, Hierarchy, Market, Single Location or User Defined Category to display data for only the related locations/products; defaults to 'All'.

Objective Date Range: Select either the 'Financial' or 'Post' Calendar and a Begin Date & End Date.

Data Type: Select whether the report should display Consumption or Purchase data.

• Consumption – Calculated as [ Beginning Inventory + Purchases – Ending Inventory ]
• Purchase – Includes reconciled Vendor Orders (including Vendor Returns), Commissary Orders, and Location Transfers.

Calculation Type: Select whether the report should display Variance or Actual data.

• Variance – Compares actual prices against reference prices and displays variances.
• Actual – Displays actual prices for the locations over the periods retrieved.

Group Locations By: Concept, Market, or State

Filter Included Products By: Active, Category, Subcategory, Microcategory, GL, Product Type, Template, User Defined Category.

Show Period Detail: Check this to display period-specific columns.

Ascending Order: When "Show Period Detail" is checked, check this box to display the period-specific columns in ascending order by date. If not checked, these columns will appear in descending order by date.

Missing Reference Bids: Downloads a PDF report that shows all products with no valid bid in the reference market.

Funnel (Filters): Any report column

Printer (Export): PDF, Excel, CSV formats

-><- , <-->: Click to minimize/maximize the report header

Retrieval Combinations (Data Type/Calculation Type):

• 'Purchase' / 'Actual' – Shows pricing trends based on actual purchases.
• 'Purchase' / 'Variance' – Shows the difference between actual purchases and what the purchase values would be if the costs were based on those in a reference market.
• 'Consumption' / 'Actual' – Shows consumption based on actual inventory costs .
• 'Consumption' / 'Variance' – Shows the difference between actual consumption and what the consumption values would be if the costs were based on those in a reference market.

Note: For “Variance” calculations, a negative value represents a better actual value than the reference market and a positive value represents a worse actual value than the reference market.

Note: When Calculation Type = 'Variance', the values in this report are expressed in the Reference Market’s base currency (if selected); otherwise the values are displayed in the Company base currency.

Report Columns:

The columns displayed differ based on the "Data Type" and "Calculation Type" selected. To see the relevant columns and definitions, click on the appropriate link below.  Click any column header to sort by the data in that column.

Columns: Data = 'Consumption'; Calculation = 'Variance' 

• Location Code, Location Name – The code and name for the location.
• Total Net Sales = Sum of [ (POS Sales – Comps & Discounts) + value of Reconciled Commissary and Customer Orders ] for all days across all periods retrieved.
• Consumption Value = Sum of [ (Beginning Inventory Qty in the first retrieved period + Purchased Qty in all retrieved periods – Ending Inventory Qty in the last retrieved period) x Inventory Cost ] for all products consumed across all periods.
• Consumption Value % of Sales = [ (Consumption Value ÷ Total Net Sales) x 100 ]
• Reference Consumption Value = Sum of [ (Beginning Inventory Qty in the first retrieved period + Purchased Qty in all retrieved periods – Ending Inventory Qty in the last retrieved period) x Reference Price ] for all products consumed across all periods.  (Reference Price = the contract or lowest bid price; if a product has no valid bid, will use the Avg. Unit Price.)
• Consumption Value Variance = [ Consumption Value – Reference Consumption Value ]
• Consumption Value Variance % of Sales = [ ((Consumption Value – Reference Consumption Value) ÷ Total Net Sales) x 100 ]
• [Date] - [Date] Consumption Value Variance = [ Period Consumption Value – Period Reference Consumption Value ]
• [Date] - [Date] Consumption Value Variance % of Sales = [ (( Period Consumption Value – Period Reference Consumption Value) ÷ Period Total Net Sales) x 100 ].

Columns: Data = 'Consumption'; Calculation = 'Actual'  

• Location Code, Location Name – The code and name for the location.
• Total Net Sales = Sum of [ (POS Sales – Comps & Discounts) + value of Reconciled Commissary and Customer Orders ] for all days across all periods retrieved.
• Consumption Value = Sum of [ (Beginning Inventory Qty in the first retrieved period + Purchased Qty in all retrieved periods – Ending Inventory Qty in the last retrieved period) x Inventory Cost ] for all products consumed across all periods.
• Consumption Value % of Sales = [ (Consumption Value ÷ Total Net Sales) x 100 ]
• [Date] - [Date] Consumption Value = Sum of [ (Beginning Inventory Qty in the first retrieved period + Purchased Qty in all retrieved periods – Ending Inventory Qty in the last retrieved period) x Inventory Cost ] for the period.
• [Date] - [Date] Consumption Value % of Sales = [ (Period Consumption Value ÷ Period Total Net Sales) x 100 ].

Columns: Data = 'Purchase'; Calculation = 'Variance' 

• Location Code, Location Name – The code and name for the location.
• Total Net Sales = Sum of [ (POS Sales – Comps & Discounts) + value of Reconciled Commissary and Customer Orders ] for all days across all periods retrieved.
• Purchase Value = Sum of [ Quantity Received x Unit Price ] for all products received across all retrieved periods.
• Purchase Value % of Sales = [ (Purchase Value ÷ Total Net Sales) x 100 ].
• Reference Purchase Value = Sum of [ Quantity Received x Reference Price ] for all products received across all retrieved periods.  (Reference Price = the contract or lowest bid price; if a product has no valid bid, will use the Avg. Unit Price.)
  
• Purchase Value Variance = [ Purchase Value – Reference Purchase Value ]
• Purchase Value Variance % of Sales = [ ((Purchase Value – Reference Purchase Value) ÷ Total Net Sales) x 100 ]
• [Date] - [Date] Purchase Value Variance = [ Period Purchase Value – Period Reference Purchase Value ]
• [Date] - [Date] Purchase Value Variance % of Sales = [ ((Period Purchase Value – Period Reference Purchase Value) ÷ Period Total Net Sales) x 100 ]

Columns: Data = 'Purchase'; Calculation = 'Actual'  

• Location Code, Location Name – The code and name for the location.
• Total Net Sales = Sum of [ (POS Sales – Comps & Discounts) + value of Reconciled Commissary and Customer Orders ] for all days across all periods retrieved.
• Purchase Value = Sum of [ Quantity Received x Unit Price ] for all products received across all retrieved periods.
• Purchase Value % of Sales = [ (Total Purchase Value ÷ Total Net Sales) x 100 ]
• [Date] - [Date] Purchase Value = Sum of [ Quantity Received x Unit Price ] for all products received during the period.
• [Date] - [Date] Purchase Value % of Sales = [ (Period Purchase Value ÷ Period Total Net Sales) x 100 ]

“Drill Down” Links:

• Location Name – Opens a Product Detail sub-report for the location.
• Grand Total (label) – Opens a Product Detail sub-report for all locations.

 

Note: Some columns have the same name as columns in other reports.  Columns are specific to each individual report and therefore the calculations may not be equivalent.  As a result, columns with the same name may not contain the same value across reports.

 

 

Related Topics