Here are two tables. The first shows the concepts behind Asset Protection and Shrink Management, the second shows this by Excel formulas, and DAX and OracleSQL Queries.
| Category | Subcategory | Concept | Description | Shortcode Equation | Use Case | Keywords |
|---|---|---|---|---|---|---|
| Shrink Measurement | Core Metrics | Shrink Rate | Percentage of inventory lost compared to sales or inventory value | Shrink$ / Sales$ * 100 | Calculate overall store shrink performance; benchmark against industry standards | shrink percentage, loss rate, inventory variance |
| Shrink Measurement | Core Metrics | Shrink Dollars | Absolute dollar value of inventory loss | Book Inventory – Physical Inventory | Quantify financial impact of shrink; budget for loss prevention investments | inventory loss, dollar shrink, financial impact |
| Shrink Measurement | Core Metrics | Unknown Shrink | Shrink that cannot be attributed to a specific cause after investigation | Total Shrink – (Known Theft + Admin Errors + Vendor + Damage) | Identify gaps in tracking systems; prioritize investigation areas | unaccounted loss, mystery shrink, unexplained variance |
| Shrink Measurement | Core Metrics | Known Loss | Shrink attributed to documented incidents (apprehensions, case files, damages) | Sum of all documented loss events | Track effectiveness of detection and documentation; build case histories | documented theft, recorded incidents, case files |
| Shrink Measurement | Department Analysis | Department Shrink Rate | Shrink percentage by specific department or category | (Dept Book Inv – Dept Physical Inv) / Dept Sales * 100 | Identify high-risk departments; allocate AP resources strategically | category shrink, departmental loss, segment analysis |
| Shrink Measurement | Department Analysis | SKU-Level Shrink | Shrink calculated for individual products or SKUs | SKU Book Qty – SKU Physical Qty * Unit Cost | Identify high-theft items; implement targeted protection strategies | item-level loss, product shrink, SKU variance |
| Shrink Measurement | Trending | Shrink Trend Analysis | Period-over-period comparison of shrink rates | (Current Period Shrink – Prior Period Shrink) / Prior Period Shrink | Monitor improvement or deterioration; assess intervention effectiveness | trend analysis, period comparison, shrink trajectory |
| Shrink Measurement | Trending | Rolling Shrink Average | Moving average of shrink over specified time window | AVG(Shrink Rate for last N periods) | Smooth out seasonal variations; identify long-term patterns | moving average, smoothed trend, rolling calculation |
| External Theft | Shoplifting | Apprehension Rate | Number of shoplifting incidents caught per time period | Count of apprehensions / Time period | Measure AP team effectiveness; justify staffing levels | catch rate, detention rate, theft incidents |
| External Theft | Shoplifting | Average Theft Value | Average dollar value per shoplifting incident | Total Recovery Value / Number of Apprehensions | Understand theft severity; set case priorities | average loss, theft amount, incident value |
| External Theft | Shoplifting | Recovery Rate | Percentage of stolen merchandise recovered | Recovered Merchandise$ / Total Theft$ * 100 | Assess merchandise recovery effectiveness; evaluate response time | merchandise recovery, recovery percentage, retrieval rate |
| External Theft | ORC | Organized Retail Crime (ORC) Loss | Theft loss attributed to organized crime rings | Sum of ORC case values | Track sophisticated theft operations; coordinate with law enforcement | organized crime, professional theft, crime rings |
| External Theft | ORC | ORC Incident Frequency | Number of suspected ORC incidents per period | Count of ORC-flagged transactions or incidents | Identify ORC patterns; allocate investigative resources | ORC frequency, organized theft rate, crime patterns |
| External Theft | Detection | High-Risk Transaction Count | Transactions flagged by exception reporting systems | Count of transactions meeting risk criteria | Prioritize investigation targets; refine detection algorithms | exception count, flagged transactions, alert volume |
| Internal Theft | Employee Theft | Internal Theft Rate | Shrink attributed to employee theft as percentage | Internal Theft$ / Total Shrink$ * 100 | Quantify employee theft impact; justify internal controls investment | employee theft, insider loss, associate dishonesty |
| Internal Theft | Employee Theft | Internal Case Count | Number of internal theft investigations or cases | Count of employee theft cases | Track internal threat level; measure investigative workload | employee cases, internal investigations, associate theft incidents |
| Internal Theft | Policy Violations | Cash Handling Violations | Incidents of improper cash handling procedures | Count of cash policy violations | Identify training gaps; enforce cash handling protocols | cash violations, till errors, register policy |
| Internal Theft | Policy Violations | Discount Abuse Rate | Excessive or unauthorized discounts by employees | Suspicious Discounts / Total Discounts * 100 | Detect employee discount fraud; monitor pricing integrity | discount fraud, pricing abuse, unauthorized markdowns |
| Internal Theft | Transaction Analysis | Void Rate | Percentage of transactions voided | Voided Transactions / Total Transactions * 100 | Identify potential theft concealment; monitor unusual void patterns | void percentage, transaction cancellations, void abuse |
| Internal Theft | Transaction Analysis | Refund Rate | Percentage of transactions that are refunds | Refund Transactions / Total Transactions * 100 | Detect refund fraud schemes; monitor return abuse | return rate, refund percentage, return fraud |
| Internal Theft | Transaction Analysis | No-Sale Rate | Frequency of register opens without sales | No-Sale Events / Total Transactions * 100 | Detect cash skimming; monitor register access patterns | no-sale frequency, drawer opens, register access |
| Administrative Errors | Pricing | Price Change Accuracy | Percentage of price changes executed correctly | Correct Price Changes / Total Price Changes * 100 | Reduce pricing shrink; improve price integrity | pricing accuracy, markdown errors, price integrity |
| Administrative Errors | Pricing | Scan Accuracy | Percentage match between scanned price and shelf price | Matching Prices / Total Price Verifications * 100 | Ensure pricing compliance; reduce customer disputes | scan errors, price verification, checkout accuracy |
| Administrative Errors | Inventory | Receiving Error Rate | Percentage of receiving discrepancies | Receiving Errors / Total Receipts * 100 | Improve receiving accuracy; reduce inventory variance | receiving mistakes, inbound errors, receipt accuracy |
| Administrative Errors | Inventory | Inventory Adjustment Frequency | Rate of manual inventory adjustments | Manual Adjustments / Total SKU Count | Monitor inventory control; identify process weaknesses | adjustment rate, inventory corrections, manual changes |
| Administrative Errors | Accounting | Book-to-Physical Variance | Difference between book inventory and physical count | Book Inventory$ – Physical Count$ / Book Inventory$ * 100 | Measure inventory accuracy; identify systemic issues | inventory variance, count discrepancy, book vs physical |
| Vendor Fraud | Delivery | Short Delivery Rate | Percentage of deliveries with quantity discrepancies | Short Deliveries / Total Deliveries * 100 | Detect vendor fraud; strengthen receiving controls | delivery shortages, vendor shorts, receipt discrepancies |
| Vendor Fraud | Billing | Invoice Discrepancy Rate | Percentage of invoices with billing errors | Discrepant Invoices / Total Invoices * 100 | Identify vendor overbilling; improve AP accuracy | billing errors, invoice mistakes, vendor disputes |
| Vendor Fraud | Quality | Damaged Goods Rate | Percentage of received goods damaged or defective | Damaged Units / Total Units Received * 100 | Monitor vendor quality; claim damaged merchandise credits | product damage, vendor quality, receipt damage |
| Operational Losses | Perishables | Spoilage Rate | Percentage of perishable inventory spoiled or expired | Spoiled Units / Total Perishable Units * 100 | Optimize ordering; reduce waste in perishables | expiration, food waste, perishable loss |
| Operational Losses | Perishables | Markdown Rate | Percentage of inventory sold at reduced price | Markdown$ / Regular Price$ * 100 | Manage markdown timing; optimize pricing strategy | clearance, price reduction, promotional markdown |
| Operational Losses | Damage | Damage Rate | Percentage of inventory damaged in-store | Damaged Units / Total Inventory Units * 100 | Improve handling procedures; reduce operational damage | product damage, handling loss, breakage |
| Operational Losses | Waste | Waste Rate | Percentage of inventory designated as waste | Waste Units / Total Units * 100 | Identify waste sources; implement waste reduction programs | disposal, unusable inventory, waste management |
| Detection & Prevention | Technology | EAS Tag Rate | Percentage of high-risk items with EAS tags | Tagged Items / Total High-Risk Items * 100 | Ensure protection coverage; optimize tagging strategy | electronic article surveillance, security tags, tag compliance |
| Detection & Prevention | Technology | Camera Coverage Rate | Percentage of sales floor covered by cameras | Camera Coverage Area / Total Sales Floor Area * 100 | Assess surveillance effectiveness; identify blind spots | CCTV coverage, video surveillance, camera monitoring |
| Detection & Prevention | Technology | POS Exception Alert Rate | Percentage of transactions triggering exception alerts | Alert Transactions / Total Transactions * 100 | Calibrate detection sensitivity; reduce false positives | exception reporting, alert system, transaction monitoring |
| Detection & Prevention | Staffing | AP Coverage Hours | Hours of AP personnel coverage per week | Sum of AP staff hours / Total store hours * 100 | Optimize AP scheduling; maximize deterrent presence | AP staffing, coverage ratio, personnel allocation |
| Detection & Prevention | Auditing | Compliance Audit Score | Store performance on AP policy compliance audits | Passed Audit Items / Total Audit Items * 100 | Measure policy adherence; identify training needs | audit compliance, policy adherence, control effectiveness |
| Financial Impact | Profitability | Shrink Impact on Margin | Reduction in profit margin due to shrink | Shrink$ / (Sales – COGS) * 100 | Quantify shrink effect on profitability; justify AP investments | margin erosion, profit impact, financial loss |
| Financial Impact | Profitability | AP ROI | Return on investment for asset protection programs | (Shrink Reduction$ – AP Program Cost$) / AP Program Cost$ * 100 | Evaluate AP program effectiveness; justify budget requests | return on investment, program effectiveness, cost benefit |
| Financial Impact | Benchmarking | Industry Shrink Comparison | Store shrink rate vs industry benchmark | Store Shrink Rate – Industry Average Rate | Assess competitive performance; set realistic targets | industry benchmark, competitive comparison, peer analysis |
| Financial Impact | Forecasting | Shrink Forecast | Projected shrink for future period based on trends | Historical Avg Shrink * Seasonal Factor | Budget planning; set prevention goals | shrink projection, loss forecast, future estimate |
| Investigative | Case Management | Case Closure Rate | Percentage of investigations completed | Closed Cases / Total Cases * 100 | Track investigative productivity; manage caseload | closure rate, case resolution, investigation completion |
| Investigative | Case Management | Average Case Value | Average dollar value per investigation | Total Case Value$ / Number of Cases | Prioritize high-value cases; allocate investigative time | case value, investigation worth, case sizing |
| Investigative | Case Management | Investigation Time | Average days to close an investigation | Sum of (Close Date – Open Date) / Number of Cases | Improve investigation efficiency; reduce time to resolution | case duration, investigation length, resolution time |
| Investigative | Prosecution | Prosecution Rate | Percentage of cases referred for prosecution | Prosecuted Cases / Total Apprehensions * 100 | Track legal outcomes; maintain law enforcement relationships | prosecution ratio, legal referral, criminal cases |
| Investigative | Recovery | Civil Recovery Amount | Dollars recovered through civil demand | Sum of civil recovery payments | Offset shrink losses; deter repeat offenders | civil demand, restitution, recovery collections |
| Category | Subcategory | Query | Description | Use Case | Excel Formula | DAX Query | Oracle SQL Query | Keywords |
|---|---|---|---|---|---|---|---|---|
| Shrink Analysis | Core Metrics | Calculate Store Shrink Rate | Calculate the shrink percentage for each store location | Monthly shrink reporting by store; identify high-shrink locations for intervention | #NAME? | Shrink Rate = DIVIDE(SUM(Inventory[BookValue]) – SUM(Inventory[PhysicalValue]), SUM(Sales[SalesAmount])) * 100 | SELECT store_id, store_name, ((SUM(book_inventory) – SUM(physical_inventory)) / NULLIF(SUM(sales_amount), 0)) * 100 AS shrink_rate FROM inventory i JOIN sales s ON i.store_id = s.store_id WHERE inventory_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’ GROUP BY store_id, store_name ORDER BY shrink_rate DESC | shrink rate, store performance, location analysis |
| Shrink Analysis | Core Metrics | Department Shrink Breakdown | Analyze shrink by department to identify high-loss categories | Target AP resources to high-shrink departments; adjust merchandising strategies | !ERROR! F3 -> Must specify range of cells, not any kind of literal | Dept Shrink = CALCULATE(SUM(Inventory[BookValue]) – SUM(Inventory[PhysicalValue]), ALLEXCEPT(Product, Product[Department])) | SELECT d.department_name, SUM(i.book_inventory – i.physical_inventory) AS shrink_dollars, (SUM(i.book_inventory – i.physical_inventory) / NULLIF(SUM(s.sales_amount), 0)) * 100 AS shrink_pct FROM inventory i JOIN products p ON i.product_id = p.product_id JOIN departments d ON p.department_id = d.department_id JOIN sales s ON p.product_id = s.product_id AND i.store_id = s.store_id WHERE i.inventory_date = TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’) GROUP BY d.department_name ORDER BY shrink_dollars DESC | department shrink, category loss, segment analysis |
| Shrink Analysis | Core Metrics | Top 20 Shrink SKUs | Identify the top 20 products by shrink dollars | Focus protection efforts on highest-loss items; add EAS tags or security measures | Use pivot table with Product as rows, shrink dollars as values, sorted descending, top 20 | Top Shrink SKUs = TOPN(20, SUMMARIZE(Inventory, Product[SKU], Product[Description], “Shrink”, SUM(Inventory[BookValue]) – SUM(Inventory[PhysicalValue])), [Shrink], DESC) | SELECT * FROM (SELECT p.sku, p.product_name, SUM(i.book_quantity – i.physical_quantity) * p.unit_cost AS shrink_dollars, RANK() OVER (ORDER BY SUM(i.book_quantity – i.physical_quantity) * p.unit_cost DESC) AS shrink_rank FROM inventory i JOIN products p ON i.product_id = p.product_id WHERE i.inventory_date BETWEEN ADD_MONTHS(SYSDATE, -3) AND SYSDATE GROUP BY p.sku, p.product_name, p.unit_cost) WHERE shrink_rank <= 20 | high shrink items, top loss products, SKU analysis |
| Shrink Analysis | Trending | Month-over-Month Shrink Trend | Compare current month shrink to previous month by store | Track shrink improvement or deterioration; assess intervention effectiveness | #NAME? | MoM Shrink Change = VAR CurrentShrink = CALCULATE([Shrink Rate], DATESMTD(Calendar[Date])) VAR PriorShrink = CALCULATE([Shrink Rate], DATEADD(Calendar[Date], -1, MONTH)) RETURN DIVIDE(CurrentShrink – PriorShrink, PriorShrink) * 100 | SELECT s.store_id, s.store_name, current.shrink_rate AS current_month_shrink, prior.shrink_rate AS prior_month_shrink, ((current.shrink_rate – prior.shrink_rate) / NULLIF(prior.shrink_rate, 0)) * 100 AS pct_change FROM stores s JOIN (SELECT store_id, shrink_rate FROM monthly_shrink WHERE month_year = TO_DATE(‘2024-12-01’, ‘YYYY-MM-DD’)) current ON s.store_id = current.store_id JOIN (SELECT store_id, shrink_rate FROM monthly_shrink WHERE month_year = TO_DATE(‘2024-11-01’, ‘YYYY-MM-DD’)) prior ON s.store_id = prior.store_id ORDER BY pct_change DESC | shrink trend, period comparison, month over month |
| Shrink Analysis | Trending | Rolling 12-Month Shrink Average | Calculate 12-month moving average shrink rate by store | Smooth seasonal variations; identify long-term trends | !ERROR! F6 -> Invalid cell coordinate #NAME? | Rolling 12M Shrink = CALCULATE([Shrink Rate], DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -12, MONTH)) | SELECT store_id, inventory_month, shrink_rate, AVG(shrink_rate) OVER (PARTITION BY store_id ORDER BY inventory_month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS rolling_12m_avg FROM monthly_shrink_summary WHERE inventory_month >= ADD_MONTHS(SYSDATE, -24) ORDER BY store_id, inventory_month | moving average, rolling average, trend smoothing |
| External Theft | Incidents | Apprehension Count by Store | Count of shoplifting apprehensions by store and time period | Measure AP team productivity; justify staffing levels | 0 | Apprehensions = CALCULATE(COUNTROWS(Incidents), Incidents[IncidentType] = “Shoplifting”) | SELECT s.store_id, s.store_name, COUNT(i.incident_id) AS apprehension_count, SUM(i.recovered_value) AS total_recovery FROM stores s LEFT JOIN incidents i ON s.store_id = i.store_id WHERE i.incident_type = ‘SHOPLIFTING’ AND i.incident_date BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’) GROUP BY s.store_id, s.store_name ORDER BY apprehension_count DESC | shoplifting incidents, apprehension rate, theft count |
| External Theft | Incidents | Average Theft Value by Time Period | Calculate average dollar value per theft incident over time | Understand theft severity trends; identify escalating theft patterns | #DIV/0! | Avg Theft Value = AVERAGEX(FILTER(Incidents, Incidents[IncidentType] = “Shoplifting”), Incidents[RecoveredValue]) | SELECT TRUNC(incident_date, ‘MM’) AS incident_month, COUNT(incident_id) AS incident_count, AVG(recovered_value) AS avg_theft_value, MEDIAN(recovered_value) AS median_theft_value FROM incidents WHERE incident_type = ‘SHOPLIFTING’ AND incident_date >= ADD_MONTHS(SYSDATE, -12) GROUP BY TRUNC(incident_date, ‘MM’) ORDER BY incident_month | average theft, incident value, theft amount |
| External Theft | ORC | Suspected ORC Transactions | Identify transactions matching ORC patterns (multiple high-value items, suspicious returns) | Flag potential organized retail crime for investigation | 0 | ORC Flags = CALCULATE(COUNTROWS(Transactions), Transactions[Quantity] >= 5, Transactions[TransactionAmount] >= 500, Transactions[PaymentMethod] IN {“Cash”, “Gift Card”}) | SELECT t.transaction_id, t.transaction_date, t.store_id, COUNT(DISTINCT td.product_id) AS unique_items, SUM(td.quantity) AS total_quantity, SUM(td.extended_price) AS transaction_total FROM transactions t JOIN transaction_details td ON t.transaction_id = td.transaction_id WHERE td.product_id IN (SELECT product_id FROM high_theft_items) GROUP BY t.transaction_id, t.transaction_date, t.store_id HAVING COUNT(DISTINCT td.product_id) >= 5 AND SUM(td.extended_price) >= 500 ORDER BY transaction_total DESC | organized retail crime, ORC detection, suspicious transactions |
| External Theft | Detection | High-Risk Transaction Alerts | Count of transactions triggering exception rules | Monitor detection system effectiveness; prioritize investigations | !ERROR! F10 -> Must specify range of cells, not any kind of literal | Exception Alerts = CALCULATE(COUNTROWS(Transactions), Transactions[ExceptionFlag] = TRUE()) | SELECT alert_type, COUNT(*) AS alert_count, COUNT(DISTINCT transaction_id) AS unique_transactions, COUNT(DISTINCT store_id) AS stores_affected FROM transaction_exceptions WHERE alert_date >= TRUNC(SYSDATE) – 7 GROUP BY alert_type ORDER BY alert_count DESC | exception reporting, alert count, fraud detection |
| Internal Theft | Employee Activity | Excessive Void Transactions by Employee | Identify employees with abnormally high void rates | Detect potential theft concealment through voided transactions | !ERROR! F11 -> Must specify range of cells, not any kind of literal | Void Rate = DIVIDE(CALCULATE(COUNTROWS(Transactions), Transactions[TransactionType] = “Void”), COUNTROWS(Transactions)) | SELECT e.employee_id, e.employee_name, e.store_id, COUNT(CASE WHEN t.transaction_type = ‘VOID’ THEN 1 END) AS void_count, COUNT(t.transaction_id) AS total_transactions, (COUNT(CASE WHEN t.transaction_type = ‘VOID’ THEN 1 END) / NULLIF(COUNT(t.transaction_id), 0)) * 100 AS void_rate FROM employees e JOIN transactions t ON e.employee_id = t.cashier_id WHERE t.transaction_date >= TRUNC(SYSDATE) – 30 GROUP BY e.employee_id, e.employee_name, e.store_id HAVING (COUNT(CASE WHEN t.transaction_type = ‘VOID’ THEN 1 END) / NULLIF(COUNT(t.transaction_id), 0)) > 0.05 ORDER BY void_rate DESC | void rate, employee voids, transaction cancellations |
| Internal Theft | Employee Activity | Excessive Discount Usage | Track employees giving discounts above normal thresholds | Identify potential discount abuse or unauthorized markdowns | !ERROR! F12 -> Must specify range of cells, not any kind of literal | Discount Rate = DIVIDE(SUM(Transactions[DiscountAmount]), SUM(Transactions[SalesAmount])) * 100 | SELECT e.employee_id, e.employee_name, COUNT(t.transaction_id) AS transactions_with_discounts, SUM(t.discount_amount) AS total_discounts, AVG(t.discount_amount) AS avg_discount, (SUM(t.discount_amount) / NULLIF(SUM(t.sales_amount), 0)) * 100 AS discount_rate FROM employees e JOIN transactions t ON e.employee_id = t.cashier_id WHERE t.discount_amount > 0 AND t.transaction_date >= TRUNC(SYSDATE) – 30 GROUP BY e.employee_id, e.employee_name HAVING (SUM(t.discount_amount) / NULLIF(SUM(t.sales_amount), 0)) > 0.15 ORDER BY discount_rate DESC | discount abuse, employee discounts, markdown fraud |
| Internal Theft | Employee Activity | No-Sale Frequency Analysis | Monitor register opens without sales by employee | Detect potential cash skimming or theft | 0 | No Sales = CALCULATE(COUNTROWS(RegisterEvents), RegisterEvents[EventType] = “No Sale”) | SELECT e.employee_id, e.employee_name, COUNT(re.event_id) AS no_sale_count, COUNT(DISTINCT TRUNC(re.event_timestamp)) AS days_worked, COUNT(re.event_id) / NULLIF(COUNT(DISTINCT TRUNC(re.event_timestamp)), 0) AS no_sales_per_day FROM employees e JOIN register_events re ON e.employee_id = re.employee_id WHERE re.event_type = ‘NO_SALE’ AND re.event_timestamp >= TRUNC(SYSDATE) – 30 GROUP BY e.employee_id, e.employee_name HAVING COUNT(re.event_id) / NULLIF(COUNT(DISTINCT TRUNC(re.event_timestamp)), 0) > 3 ORDER BY no_sales_per_day DESC | no sale, register opens, cash access |
| Internal Theft | Refunds | Suspicious Refund Patterns | Identify refunds without original receipts or to same employee | Detect refund fraud schemes | 0 | No Receipt Refunds = CALCULATE(COUNTROWS(Refunds), Refunds[ReceiptProvided] = FALSE()) | SELECT e.employee_id, e.employee_name, COUNT(r.refund_id) AS no_receipt_refunds, SUM(r.refund_amount) AS total_no_receipt_value, COUNT(CASE WHEN r.refund_to_employee_id = e.employee_id THEN 1 END) AS refunds_to_self FROM employees e JOIN refunds r ON e.employee_id = r.processed_by_employee_id WHERE (r.original_receipt_id IS NULL OR r.refund_to_employee_id = e.employee_id) AND r.refund_date >= TRUNC(SYSDATE) – 30 GROUP BY e.employee_id, e.employee_name HAVING COUNT(r.refund_id) > 5 ORDER BY no_receipt_refunds DESC | refund fraud, no receipt returns, suspicious refunds |
| Administrative Errors | Pricing | Price Change Accuracy Rate | Calculate percentage of price changes executed correctly | Identify training needs; reduce pricing shrink from errors | !ERROR! F15 -> Must specify range of cells, not any kind of literal | Price Accuracy = DIVIDE(COUNTROWS(FILTER(PriceChanges, PriceChanges[Accurate] = TRUE())), COUNTROWS(PriceChanges)) * 100 | SELECT pc.store_id, s.store_name, COUNT(pc.price_change_id) AS total_changes, COUNT(CASE WHEN pc.system_price = pc.shelf_price THEN 1 END) AS accurate_changes, (COUNT(CASE WHEN pc.system_price = pc.shelf_price THEN 1 END) / NULLIF(COUNT(pc.price_change_id), 0)) * 100 AS accuracy_rate FROM price_changes pc JOIN stores s ON pc.store_id = s.store_id WHERE pc.verification_date >= TRUNC(SYSDATE) – 7 GROUP BY pc.store_id, s.store_name ORDER BY accuracy_rate ASC | price accuracy, pricing errors, price integrity |
| Administrative Errors | Receiving | Receiving Discrepancy Report | Track differences between ordered and received quantities | Improve receiving accuracy; identify vendor or process issues | !ERROR! F16 -> Must specify range of cells, not any kind of literal | Receiving Variance = SUM(Receiving[OrderedQty]) – SUM(Receiving[ReceivedQty]) | SELECT po.vendor_id, v.vendor_name, COUNT(DISTINCT po.po_number) AS total_pos, SUM(pod.ordered_quantity – pod.received_quantity) AS quantity_variance, SUM((pod.ordered_quantity – pod.received_quantity) * pod.unit_cost) AS dollar_variance FROM purchase_orders po JOIN po_details pod ON po.po_number = pod.po_number JOIN vendors v ON po.vendor_id = v.vendor_id WHERE po.received_date >= ADD_MONTHS(SYSDATE, -3) AND pod.ordered_quantity != pod.received_quantity GROUP BY po.vendor_id, v.vendor_name ORDER BY dollar_variance DESC | receiving errors, delivery discrepancy, vendor shorts |
| Administrative Errors | Inventory | Inventory Adjustment Frequency | Monitor rate of manual inventory adjustments by location | Identify stores with inventory control issues | 0 | Manual Adjustments = CALCULATE(COUNTROWS(InventoryAdjustments), InventoryAdjustments[AdjustmentType] = “Manual”) | SELECT ia.store_id, s.store_name, COUNT(ia.adjustment_id) AS adjustment_count, SUM(ABS(ia.adjustment_quantity)) AS total_units_adjusted, SUM(ABS(ia.adjustment_value)) AS total_dollar_adjusted, COUNT(DISTINCT ia.employee_id) AS employees_making_adjustments FROM inventory_adjustments ia JOIN stores s ON ia.store_id = s.store_id WHERE ia.adjustment_date >= TRUNC(SYSDATE) – 30 AND ia.adjustment_type = ‘MANUAL’ GROUP BY ia.store_id, s.store_name ORDER BY adjustment_count DESC | inventory adjustments, manual corrections, inventory integrity |
| Vendor Analysis | Delivery | Vendor Short Delivery Rate | Calculate percentage of deliveries short by vendor | Identify problematic vendors; strengthen receiving controls | !ERROR! F18 -> Must specify range of cells, not any kind of literal | Short Rate = DIVIDE(CALCULATE(COUNTROWS(Deliveries), Deliveries[IsShort] = TRUE()), COUNTROWS(Deliveries)) * 100 | SELECT v.vendor_id, v.vendor_name, COUNT(d.delivery_id) AS total_deliveries, COUNT(CASE WHEN d.ordered_qty > d.received_qty THEN 1 END) AS short_deliveries, (COUNT(CASE WHEN d.ordered_qty > d.received_qty THEN 1 END) / NULLIF(COUNT(d.delivery_id), 0)) * 100 AS short_rate, SUM(CASE WHEN d.ordered_qty > d.received_qty THEN (d.ordered_qty – d.received_qty) * d.unit_cost ELSE 0 END) AS short_dollar_value FROM vendors v JOIN deliveries d ON v.vendor_id = d.vendor_id WHERE d.delivery_date >= ADD_MONTHS(SYSDATE, -6) GROUP BY v.vendor_id, v.vendor_name HAVING COUNT(d.delivery_id) >= 10 ORDER BY short_rate DESC | vendor shorts, delivery accuracy, vendor performance |
| Vendor Analysis | Quality | Damaged Goods by Vendor | Track percentage of received inventory damaged by vendor | Monitor vendor quality; claim credits for damaged goods | !ERROR! F19 -> Must specify range of cells, not any kind of literal | Damage Rate = DIVIDE(SUM(Receiving[DamagedQty]), SUM(Receiving[ReceivedQty])) * 100 | SELECT v.vendor_id, v.vendor_name, COUNT(ri.inspection_id) AS inspections, SUM(ri.damaged_quantity) AS total_damaged_units, SUM(ri.received_quantity) AS total_received_units, (SUM(ri.damaged_quantity) / NULLIF(SUM(ri.received_quantity), 0)) * 100 AS damage_rate, SUM(ri.damaged_quantity * ri.unit_cost) AS damage_dollar_value FROM vendors v JOIN receiving_inspections ri ON v.vendor_id = ri.vendor_id WHERE ri.inspection_date >= ADD_MONTHS(SYSDATE, -3) GROUP BY v.vendor_id, v.vendor_name HAVING SUM(ri.received_quantity) > 0 ORDER BY damage_rate DESC | damaged goods, vendor quality, product damage |
| Operational Losses | Perishables | Spoilage Rate by Department | Calculate percentage of perishable inventory spoiled | Optimize ordering for perishables; reduce waste | !ERROR! F20 -> Must specify range of cells, not any kind of literal | Spoilage = DIVIDE(SUM(Perishables[SpoiledQty]), SUM(Perishables[ReceivedQty])) * 100 | SELECT d.department_name, SUM(p.spoiled_quantity) AS spoiled_units, SUM(p.received_quantity) AS received_units, (SUM(p.spoiled_quantity) / NULLIF(SUM(p.received_quantity), 0)) * 100 AS spoilage_rate, SUM(p.spoiled_quantity * p.unit_cost) AS spoilage_cost FROM perishables p JOIN products pr ON p.product_id = pr.product_id JOIN departments d ON pr.department_id = d.department_id WHERE p.disposition_date >= TRUNC(SYSDATE) – 30 AND p.disposition_reason = ‘SPOILAGE’ GROUP BY d.department_name ORDER BY spoilage_cost DESC | spoilage, food waste, perishable loss |
| Operational Losses | Markdown | Markdown Analysis by Category | Track markdown dollars and percentage by category | Optimize pricing strategy; improve markdown timing | !ERROR! F21 -> Must specify range of cells, not any kind of literal | Markdown % = DIVIDE(SUM(Sales[MarkdownAmount]), SUM(Sales[RegularPrice])) * 100 | SELECT c.category_name, COUNT(DISTINCT m.product_id) AS items_marked_down, SUM(m.markdown_quantity * (m.original_price – m.markdown_price)) AS total_markdown_dollars, SUM(m.markdown_quantity * m.original_price) AS original_value, (SUM(m.markdown_quantity * (m.original_price – m.markdown_price)) / NULLIF(SUM(m.markdown_quantity * m.original_price), 0)) * 100 AS markdown_pct FROM markdowns m JOIN products p ON m.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id WHERE m.markdown_date >= TRUNC(SYSDATE) – 30 GROUP BY c.category_name ORDER BY total_markdown_dollars DESC | markdown rate, clearance, price reduction |
| Detection & Prevention | Technology | EAS Tag Compliance Rate | Percentage of high-theft items properly tagged | Ensure protection coverage on vulnerable merchandise | !ERROR! F22 -> Must specify range of cells, not any kind of literal | Tag Compliance = DIVIDE(CALCULATE(COUNTROWS(Inventory), Inventory[EASTagged] = TRUE()), COUNTROWS(Inventory)) * 100 | SELECT s.store_id, s.store_name, COUNT(i.item_id) AS high_theft_items, COUNT(CASE WHEN i.eas_tagged = ‘Y’ THEN 1 END) AS tagged_items, (COUNT(CASE WHEN i.eas_tagged = ‘Y’ THEN 1 END) / NULLIF(COUNT(i.item_id), 0)) * 100 AS tag_compliance_rate FROM stores s JOIN inventory i ON s.store_id = i.store_id JOIN products p ON i.product_id = p.product_id WHERE p.high_theft_indicator = ‘Y’ AND i.inventory_date = TRUNC(SYSDATE) GROUP BY s.store_id, s.store_name ORDER BY tag_compliance_rate ASC | EAS tags, security tags, tag compliance |
| Detection & Prevention | Auditing | AP Audit Compliance Score | Store performance on asset protection policy audits | Measure policy adherence; identify training gaps | !ERROR! F23 -> Must specify range of cells, not any kind of literal | Audit Score = DIVIDE(SUM(Audits[PassedItems]), SUM(Audits[TotalItems])) * 100 | SELECT a.store_id, s.store_name, a.audit_date, COUNT(ai.audit_item_id) AS total_items, COUNT(CASE WHEN ai.compliant = ‘Y’ THEN 1 END) AS passed_items, (COUNT(CASE WHEN ai.compliant = ‘Y’ THEN 1 END) / NULLIF(COUNT(ai.audit_item_id), 0)) * 100 AS compliance_score FROM audits a JOIN audit_items ai ON a.audit_id = ai.audit_id JOIN stores s ON a.store_id = s.store_id WHERE a.audit_date >= ADD_MONTHS(SYSDATE, -3) GROUP BY a.store_id, s.store_name, a.audit_date ORDER BY a.store_id, a.audit_date DESC | audit compliance, policy adherence, control effectiveness |
| Financial Impact | ROI | Asset Protection Program ROI | Calculate return on investment for AP initiatives | Justify AP program spending; demonstrate value | #NAME? | AP ROI = DIVIDE([Prior Year Shrink] – [Current Year Shrink] – [AP Program Cost], [AP Program Cost]) * 100 | SELECT ap.program_name, ap.program_cost, (prior.total_shrink – current.total_shrink) AS shrink_reduction, ((prior.total_shrink – current.total_shrink) – ap.program_cost) AS net_savings, (((prior.total_shrink – current.total_shrink) – ap.program_cost) / NULLIF(ap.program_cost, 0)) * 100 AS roi_pct FROM ap_programs ap, (SELECT SUM(shrink_dollars) AS total_shrink FROM shrink_summary WHERE fiscal_year = 2023) prior, (SELECT SUM(shrink_dollars) AS total_shrink FROM shrink_summary WHERE fiscal_year = 2024) current WHERE ap.fiscal_year = 2024 | ROI, return on investment, program effectiveness |
| Financial Impact | Benchmarking | Store Shrink vs District Average | Compare individual store shrink to district benchmark | Identify underperforming locations; set realistic targets | !ERROR! F25 -> Must specify range of cells, not any kind of literal | vs District = [Store Shrink Rate] – CALCULATE(AVERAGE(Stores[ShrinkRate]), ALLEXCEPT(Stores, Stores[District])) | SELECT s.store_id, s.store_name, s.district_id, ss.shrink_rate AS store_shrink_rate, AVG(ss2.shrink_rate) OVER (PARTITION BY s.district_id) AS district_avg_shrink, ss.shrink_rate – AVG(ss2.shrink_rate) OVER (PARTITION BY s.district_id) AS variance_from_district FROM stores s JOIN shrink_summary ss ON s.store_id = ss.store_id JOIN shrink_summary ss2 ON s.district_id = ss2.district_id WHERE ss.period = ‘Q4-2024’ ORDER BY variance_from_district DESC | benchmark comparison, district average, peer analysis |
| Investigative | Case Management | Average Case Resolution Time | Calculate average days to close investigations | Improve investigation efficiency; identify bottlenecks | #VALUE! | Avg Resolution = AVERAGE(Cases[ClosedDate] – Cases[OpenedDate]) | SELECT c.case_type, COUNT(c.case_id) AS total_cases, AVG(c.closed_date – c.opened_date) AS avg_days_to_close, MEDIAN(c.closed_date – c.opened_date) AS median_days_to_close, MAX(c.closed_date – c.opened_date) AS max_days_to_close FROM cases c WHERE c.case_status = ‘CLOSED’ AND c.closed_date >= ADD_MONTHS(SYSDATE, -6) GROUP BY c.case_type ORDER BY avg_days_to_close DESC | case duration, investigation time, resolution speed |
| Investigative | Recovery | Civil Recovery Success Rate | Percentage of civil demands resulting in payment | Track recovery effectiveness; adjust recovery strategy | !ERROR! F27 -> Must specify range of cells, not any kind of literal | Recovery Rate = DIVIDE(COUNTROWS(FILTER(CivilDemands, CivilDemands[PaymentStatus] = “Paid”)), COUNTROWS(CivilDemands)) * 100 | SELECT cd.demand_year, COUNT(cd.demand_id) AS total_demands, SUM(cd.demand_amount) AS total_demanded, COUNT(CASE WHEN cd.payment_status = ‘PAID’ THEN 1 END) AS paid_count, SUM(CASE WHEN cd.payment_status = ‘PAID’ THEN cd.payment_amount ELSE 0 END) AS total_collected, (COUNT(CASE WHEN cd.payment_status = ‘PAID’ THEN 1 END) / NULLIF(COUNT(cd.demand_id), 0)) * 100 AS collection_rate FROM civil_demands cd WHERE cd.demand_date >= ADD_MONTHS(SYSDATE, -12) GROUP BY cd.demand_year ORDER BY cd.demand_year | civil recovery, restitution, collection rate |
| Exception Reporting | Transaction Patterns | After-Hours Transactions | Identify transactions occurring outside normal business hours | Detect unauthorized access or suspicious activity | !ERROR! F28 -> Must specify range of cells, not any kind of literal | After Hours = CALCULATE(COUNTROWS(Transactions), OR(Transactions[TransTime] < TIME(7,0,0), Transactions[TransTime] > TIME(22,0,0))) | SELECT t.store_id, s.store_name, TO_CHAR(t.transaction_timestamp, ‘HH24:MI’) AS transaction_time, t.transaction_id, t.cashier_id, e.employee_name, t.transaction_amount FROM transactions t JOIN stores s ON t.store_id = s.store_id JOIN employees e ON t.cashier_id = e.employee_id WHERE (EXTRACT(HOUR FROM t.transaction_timestamp) < 7 OR EXTRACT(HOUR FROM t.transaction_timestamp) > 22) AND t.transaction_date >= TRUNC(SYSDATE) – 7 ORDER BY t.store_id, t.transaction_timestamp | after hours, off-hours transactions, unauthorized access |
| Exception Reporting | Transaction Patterns | Large Cash Transactions | Flag cash transactions over threshold amount | Identify potential suspicious activity or fraud | 0 | Large Cash = CALCULATE(COUNTROWS(Transactions), Transactions[PaymentMethod] = “Cash”, Transactions[Amount] >= 1000) | SELECT t.transaction_id, t.transaction_date, t.store_id, s.store_name, t.cashier_id, e.employee_name, t.transaction_amount, t.payment_method FROM transactions t JOIN stores s ON t.store_id = s.store_id JOIN employees e ON t.cashier_id = e.employee_id WHERE t.payment_method = ‘CASH’ AND t.transaction_amount >= 1000 AND t.transaction_date >= TRUNC(SYSDATE) – 30 ORDER BY t.transaction_amount DESC | large cash, cash transactions, high value sales |
| Exception Reporting | Employee Behavior | Same-Day Hire and Refund | Identify employees processing refunds on same day as hiring | Detect potential collusion or fraud | 0 | Same Day Events = CALCULATE(COUNTROWS(Refunds), Refunds[RefundDate] = RELATED(Employees[HireDate])) | SELECT e.employee_id, e.employee_name, e.hire_date, r.refund_id, r.refund_date, r.refund_amount, r.transaction_id FROM employees e JOIN refunds r ON e.employee_id = r.processed_by_employee_id WHERE TRUNC(r.refund_date) = TRUNC(e.hire_date) AND r.refund_date >= ADD_MONTHS(SYSDATE, -6) ORDER BY r.refund_amount DESC | new hire fraud, same day refund, employee collusion |