Loss prevention analytics from inventory and sales data

Loss prevention analytics reconciles physical inventory with recorded stock movements, then shows which store and SKU differences need investigation. Start with quantities, value the differences at inventory cost, and keep shortages separate from overages. The result is an exception list, with supporting rows to review.

Start with a common count cutoff

Export opening inventory, receipts, sales quantities, customer returns, transfers, approved adjustments and the closing count. Keep store, SKU, transaction ID, movement type, effective timestamp, quantity and unit of measure. Preserve the original exports so a later correction does not erase the evidence for the first result.

The opening balance and count must describe the same stock population. Include saleable stock, returns awaiting inspection and goods in transit consistently. A count taken after a store closes cannot be compared with a sales export taken before its last transactions posted.

The IRS inventory guidance says a perpetual inventory requires physical inventories at reasonable intervals and adjustment of the book amount to actual inventory. Here, calculate the difference before posting that adjustment; otherwise the correction removes the exception you are trying to measure.

Use the stock movement report for the underlying movement layout. This article adds the count reconciliation.

Build the expected quantity in Excel or Google Sheets

Expected closing units = Opening units + Receipts + Customer returns + Transfers in - Sales units - Transfers out + Signed adjustments

Count difference = Counted units - Expected closing units

Store sales and transfers out as positive quantities that the formula subtracts. Store adjustments as signed quantities. A recorded write-down in units is negative; an approved correction upward is positive.

Suppose a US retailer reconciles September 2026. Columns A:J contain the following inputs. Every quantity is in individual units, rather than cases.

Add unit cost in K, expected units in L, difference in M and signed cost difference in N. Enter these formulas in row 2 and fill down through row 5:

L2 = C2+D2+E2+F2-G2-H2+I2
M2 = J2-L2
N2 = M2*K2

To build receipts from transaction rows, let Movements contain store in A, SKU in B, movement type in C, units in D and effective timestamp in E. Put the opening timestamp in Q1 and the closing timestamp in Q2. The closing boundary is exclusive:

=SUMIFS(Movements!$D$2:$D$5000,Movements!$A$2:$A$5000,$A2,Movements!$B$2:$B$5000,$B2,Movements!$C$2:$C$5000,"Receipt",Movements!$E$2:$E$5000,">="&$Q$1,Movements!$E$2:$E$5000,"<"&$Q$2)

Repeat with the appropriate movement label for each column. Google's SUMIFS documentation defines the multiple-criteria sum used here. Reject missing timestamps and quantities before summing; an omitted row must not quietly become no activity. Covirage is not affiliated with Microsoft or Google.

Value the exceptions and prove the total

For this example, inventory costs stay constant during the period. If costs change, use the inventory valuation supporting each closing balance, rather than averaging selling prices; see weighted average inventory cost per unit.

The quantity proof is 390 + 185 + 7 + 20 - 405 - 20 - 6 = 171 expected units. Counted units are 166, leaving a signed difference of -5. Transfers in and out both total 20, so these transfers have no net company quantity effect.

The value proof is equally important. Expected inventory is $3,261 and counted inventory is $3,110, using the unit costs above. Their difference is -$151. These spreadsheet checks must agree:

=SUM(J2:J5)-SUM(L2:L5)
=SUMPRODUCT(J2:J5,K2:K5)-SUMPRODUCT(L2:L5,K2:K5)
=SUM(N2:N5)

The first returns -5; the last two return -151. Separately, negative cost differences total $175 of shortages and positive differences total $24 of overages. Their absolute sum is $199, so reporting only the $151 net understates the amount requiring review.

Investigate patterns before changing records

Recount East / A and West / A, then check transaction IDs, receipt documents and transfer pairs around the cutoff. Review East / B's overage too: it could identify a recording issue related to a shortage elsewhere, but that relationship needs evidence.

Keep a history with one finalized row per count. Compare the same store and SKU over successive counts, separating corrected records from unresolved exceptions. Use inventory aging by site for slow stock and inventory turnover for movement speed; neither substitutes for this reconciliation.

Where it goes wrong

• Posting the count adjustment first. The adjusted balance matches the count by construction. Preserve the balance before adjustment.
• Counting cases against units. Apply a documented conversion before comparing quantities.
• Recording only one transfer leg. Match dispatch and receipt documents and identify stock still in transit.
• Treating blanks as zero. A missing count is incomplete evidence, not an empty shelf.
• Netting unrelated exceptions. Keep the signed total for the proof and separate shortage and overage totals for review.

Reconcile your own inventory file in Covirage

Bring a sample of your own count and stock movement exports to Covirage; its tools map the columns, compute expected quantities and check the differences against the supplied rows. The external AI model explains those 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 Excel analysis with Covirage for the supporting workflow.