Grocery store analytics: sales, waste and margin

Grocery store analytics connects what the register sold with what the store discounted, consumed and discarded. Compare net sales and margin after waste for the same stores, categories and dates, then reconcile the figures to the source reports before changing an order or a markdown rule.

A sales ranking alone misses perishability. A produce department can sell strongly while throwing away expensive inventory, and a markdown can reduce the selling price while still recovering more than disposal would.

Start with exports that describe the same week

Take the closed week's POS sales, product cost records and inventory adjustment log. Keep store, SKU, category, business date and transaction identifiers. The sales file needs selling value before markdowns, markdown amounts and returns; the inventory file needs the quantity and cost of each waste adjustment and its reason.

Use a single category mapping for both files. If the register calls an item prepared food while the stock system calls it produce, resolve that difference before adding either category. Assign late adjustments to the review period using the same written cutoff each week.

For the example below, customer returns are absent, sales tax is excluded, and markdowns are the only sales reduction. For a real export, deduct returns separately. The broader retail analytics review covers other store measures; this review stays with the economics of perishables.

Keep the original files intact. Build a summary with one row per store and category, and retain a way to find the sales and waste records behind each summary row.

Define the cost boundary before calculating margin

Use these operating measures:

Net sales = Selling value before markdowns − Markdowns − Returns

Contribution after waste = Net sales − Cost of units sold − Waste cost

Contribution margin after waste = Contribution after waste / Net sales

This contribution excludes store labor, rent and other overhead. It is a management comparison, not a complete store profit figure. The gross profit margin method explains the broader revenue and cost boundary.

The crucial choice is whether the supplied goods cost already includes waste. In this example, cost of units sold excludes discarded inventory, so waste is deducted separately. If the ledger's category cost already includes the waste adjustment, split it out for display or leave it included; never subtract it again.

The IRS explanation of cost of goods sold uses beginning inventory, purchases and ending inventory to establish goods cost. Use the company's approved inventory costing policy for this operating report and have finance reconcile its treatment of waste to the ledger.

Build the comparison with exact formulas

An illustrative US grocery operator has the following weekly inputs. Columns C through F come from its reconciled summaries. All amounts are USD.

Enter these formulas in the first data row and fill down through row 5:

G2 = C2-D2
H2 = G2-E2-F2
I2 = IF(G2=0,"",H2/G2)

Format column I as a percentage. A blank marks a zero-sales denominator for review rather than pretending its margin is zero. For totals, sum the dollar columns and divide total contribution by total net sales:

G6 = SUM(G2:G5)
H6 = SUM(H2:H5)
I6 = IF(G6=0,"",H6/G6)

Percentages are rounded to one decimal place after calculation. Retain full precision in the sheet. The total is $18,400 / $61,000 = 30.2%, rather than an average of the category percentages.

Prove the totals, then choose the investigation

The first reconciliation is $65,000 − $4,000 = $61,000 of net sales. That must match the POS sales report on the same tax and return basis. The next is $61,000 − $39,900 − $2,700 = $18,400. Waste adjustments must also sum to $2,700 in the inventory report.

Add a balance check that must return zero:

=SUM(G2:G5)-SUM(E2:E5)-SUM(F2:F5)-SUM(H2:H5)

North contributes $9,100 on $32,500 of net sales, or 28.0%. South contributes $9,300 on $28,500, or 32.6%. Compute those store totals with SUMIFS, using a store name in K2:

L2 = SUMIFS($G$2:$G$5,$A$2:$A$5,K2)
M2 = SUMIFS($H$2:$H$5,$A$2:$A$5,K2)
N2 = IF(L2=0,"",M2/L2)

North's produce waste is the first investigation, not proof of poor ordering. Check receipts, shelf life, handling and the timing of discounts. The EPA's waste tracking guidance recommends recording waste amounts and reasons; that distinction turns a dollar variance into an operating question.

The store manager owns reason accuracy, merchandising owns markdown rules, and finance owns cost consistency. Review inventory aging by site for stock exposure and retail demand forecasting when deciding future purchase quantities.

Where it goes wrong

• Markdowns deducted twice. POS net sales may already contain them. Preserve the original basis and subtract each adjustment once.
• Waste valued at shelf price. Lost selling opportunity and inventory expense answer different questions. Keep their amounts and labels separate.
• Cost adjustments mixed across weeks. A late supplier credit can move category contribution without any change in store execution. Show the adjustment distinctly.
• Shrink treated entirely as food waste. Theft, count errors and spoilage need different investigations. Preserve the reason codes and flag unknown reasons.
• Department margin read as store profit. Labor, occupancy and other expenses remain outside this calculation. Bring them into a separate operating profit review.

Do this on your own grocery files in Covirage

Bring a sample of your own POS sales, product cost and inventory adjustment exports to Covirage. Covirage maps the columns, and its tools compute and check the store and category comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your files.

See revenue driver analysis for the supporting business use case.