The Free Retail Deduction Recovery Workbook: An Excel Sheet for Deduction Diagnostics

A free Excel workbook with 2,000+ formulas that auto-calculates dispute deadlines, shortage gaps, priority scores, and recovery rates. Sheet-by-sheet walkthrough.

Hand-drawn black ink whiteboard sketch of a spreadsheet grid with labeled column headers (Case ID, Retailer, Amount, Days Left) and a formula bar showing an IF function, arrows pointing to colored cells marked overdue, due soon, and OK
Short answer

The Retail Deduction Recovery Workbook is a free Excel file with eight sheets and 2,000+ formulas. The Dispute Tracker auto-calculates days remaining and a priority score for each case. Shortage Reconciliation computes quantity gaps across four handoff points and flags the first divergence. The Dashboard pulls live totals by retailer, deduction type, and root cause with no manual entry. Every formula works in Google Sheets.

Pixels and Clicks free resource, November 2026. Retailer deadlines compiled from independent vendor sources and Pixels and Clicks articles. Verify against the live retailer portal.

HubPart of the Deduction Management Hub→
2,003
formulas across eight sheets, zero manual calculations
0
logins, email gates, or forms required to download
14
functions used, all compatible with Google Sheets
200
data rows per tracker sheet, expandable by dragging formulas down

A $4,200 shortage claim from Walmart hits your remittance on a Tuesday. Your team pulls the POD, confirms the truck arrived, and opens a dispute, but Walmart denies it. Your ASN quantity didn’t match your pick ticket, and your team didn’t check before filing.

That claim is now a write-off. The evidence existed in four systems. Unfortunately, your team didn’t assemble it before the deadline closed.

This workbook exists so you don’t lose that claim again. It’s a single Excel file with eight sheets and 2,003 formulas. You enter a deduction. The workbook calculates your deadline countdown, flags your evidence gaps, scores the case by urgency, and rolls everything into a dashboard you review monthly.

There are no macros, no VBA, and nothing to log into. Download the workbook and open it.

Download Workbook (.xlsx)

What’s inside

There are eight sheets, each with a specific job. The first five are where you enter data. The Dashboard reads from them automatically. The Deadline Reference is a static lookup.

2,003
formulas in the workbook. Gap columns calculate your numbers. Deadline countdowns automatically update when you open the file. Every cell that needs math has a formula behind it.
DATA-ENTRY SHEETS — YOU TYPE HEREDispute TrackerCases & deadlinesEvidence16 docs per caseShortage ReconQuantity gapsOTIF TimestampMilestone timelineRoot CauseOperational causesAUTO-CALCULATED · DON’T TYPE HEREDashboardRecovery rate, aging, exposure by retailer,root-cause frequency, outcome summarySTATIC LOOKUPDeadline RefWalmart, Target,Amazon, Kroger windowsYou type in the five sheets up top. The Dashboard reads from all of them automatically.
SheetWhat you enterWhat it calculates
Dispute TrackerCase ID, retailer, amount, clock-start date, deadline daysPortal deadline, days remaining, priority score (0-10), recovery %
Evidence ChecklistWhich of 16 documents you’ve located and verified per caseCompleteness % per case
Shortage ReconciliationOrdered, picked, ASN’d, received, and invoiced quantitiesFour gap columns, first point of divergence
OTIF Timestamp LogSource timestamps for each shipment milestoneDelta hours between consecutive milestones
Root Cause RegisterOperational cause, corrective action, ownerThe Dashboard reads from it
DashboardNothing. Don’t type in this sheet.Everything: exposure, recovery rate, aging, breakdowns by retailer, type, root cause
Deadline ReferenceNothing. Pre-filled.Static lookup with hyperlinks to source articles

Dispute Tracker

YOU ENTER THE FACTS · FOUR COLUMNS DO THE MATHYOU ENTERRetailerAmount ($)Clock-start dateDeadline (days)Evidence ready?FOUR COLUMNS AUTO-CALCULATEPortal Deadlineclock-start + deadline (days)Days Remainingdeadline − TODAY()cell fills by urgencyPriority Score 0–10amount + urgency + evidenceRecovery %recovered ÷ amount
Enter the facts once. Portal deadline, days remaining, priority score, and recovery rate calculate themselves.
Dispute Tracker sheet with three deduction cases. The Days Remaining column fills each cell with a color and white text: dark red for an overdue case, red for 3 days left, green for a far-off deadline. The Priority Score column shows high-urgency cases in red text.
Dispute Tracker with three sample cases across Walmart and Amazon. The Days Remaining cell color flags urgency at a glance.

This is the master log. You have one row per deduction.

You enter the case ID, retailer (dropdown), deduction code, type (dropdown: Shortage, OTIF, ASN Accuracy, Pricing, Compliance, Returns), amount, invoice number, PO number, clock-start date, and deadline in days.

Four columns auto-calculate from your entries.

4
columns auto-calculate: Portal Deadline, Days Remaining, Priority Score, and Recovery %. You fill in the facts, and the sheet does the math.

Portal Deadline

Formula: Clock-Start Date + Deadline (days).

If you enter 2026-11-01 as the clock-start date and 30 as the deadline, the portal deadline shows 2026-12-01. This is a working estimate. Always verify against the live portal.

Days Remaining

Formula: Portal Deadline - TODAY().

This number updates every time you open the file. Negative numbers mean the deadline has passed.

The cell fills with a color based on urgency, with white text for contrast. The further past the deadline, the darker the red:

Cell colorMeaning
Green30 or more days remaining
Orange7 to 29 days remaining
RedFewer than 7 days remaining
Dark redDeadline already passed (negative number)

Priority Score

A 0-to-10 urgency score. Three weighted factors:

FactorWeightHow it scores
Amount30%Scales from 0 to 3 based on dollar value (caps at $3,000+)
Days remaining40%4.0 if overdue. 3.5 if under 7 days. 3.0 if under 14. 2.0 if under 30. 1.0 if under 60. 0.5 beyond.
Evidence readiness30%0 if complete. 1.5 if partial. 3.0 if not started.

A $5,000 deduction due in 3 days with no evidence gathered scores 10. A $200 deduction due in 90 days with complete evidence scores 0.7. Work the high numbers first.

Scores above 7 show in red text, and scores above 4 in amber.

Other columns

Evidence Ready, Decision, Date Submitted, Current Status, Outcome, Recovered Amount, Root Cause, Owner, and Notes are manual entry. Columns with a fixed set of values use dropdown lists.

Recovery % auto-calculates: Recovered Amount / Deduction Amount.

ColumnOptions
RetailerWalmart, Target, Amazon, Kroger, Home Depot, Costco, Other
Deduction TypeShortage, OTIF, ASN Accuracy, Pricing, Compliance, Returns, Other
Evidence ReadyYes, No, Partial
DecisionDispute, Investigate, Accept, Write Off
Current StatusOpen, Submitted, Awaiting Response, Additional Docs Requested, Won, Lost, Partial, Written Off
OutcomeRecovered, Partially Recovered, Denied, Written Off, Pending

How to use it

  1. Open the Dispute Tracker when a new deduction posts on your remittance or shows up in the retailer portal.
  2. Enter one row per deduction line. Fill in Case ID through Deadline (days).
  3. Check the Days Remaining column. Work anything red or yellow first.
  4. Sort by Priority Score to triage your open cases.
  5. Update Status and Outcome as the dispute progresses.
  6. Enter the Recovered Amount when it resolves. Recovery % fills in automatically.

Evidence Checklist

16 DOCUMENTS PER CASE · ONE COMPLETENESS SCORELOCATED + VERIFIED → RESULTPurchase orderYesEDI 856 payloadNoSigned PODNoEDI 824 responseN/AInvoice / EDI 810Yes+ 11 more items per caseCOMPLETENESS58%7 of 12 required items complete4 items marked N/A are excluded from the count
Mark each document located and verified. The sheet scores completeness and ignores the items that don’t apply.
Evidence Checklist sheet showing 16 document rows per case with Located, Verified, and Complete columns. A completeness summary row reads 58% for a case with 7 of 12 required items verified.
Evidence Checklist for a Walmart shortage case at 58% completeness. Three items still outstanding before the dispute can file.

Each case needs up to 16 documents before you file a dispute. This sheet tracks which ones you’ve gathered and verified.

The sheet is organized in blocks. Each block covers one case: a case header that carries the Case ID, 16 rows (one per evidence item), and a completeness row. Every row in a block shares that block’s Case ID, so one block is always one case.

16
standard evidence items per case: PO, change orders, pick ticket, EDI 856 payload, 997, 824, BOL, POD, carrier confirmation, retailer receiving record, invoice, trade agreement, promo agreement, item master, pallet/carton detail, and appointment confirmation.

Columns

ColumnWhat to enter
Case IDSame case ID from the Dispute Tracker
RetailerDropdown
Evidence ItemPre-filled. Don’t change these.
Required?Yes, No, or N/A. Mark N/A for items that don’t apply to this deduction type.
Located?Yes or No. Have you found the document?
Verified?Yes or No. Have you checked the document’s content against the claim?
File / LinkWhere the document lives (filename, path, or portal link)
OwnerWho is responsible for gathering this document

Auto-calculated columns

Complete? shows Yes if both Located and Verified are Yes. Shows N/A if the item was marked N/A. Shows No otherwise.

Completeness (the row below each case block) shows a percentage and count. “58% (7 of 12 required items complete)” means 7 of the 12 items that apply to this case are both located and verified. Items you marked N/A drop out of the count, so the denominator is only the items that actually apply.

How to use it

  1. Copy the first case block for each new case. The formulas copy with it.
  2. Set Required to N/A for anything that doesn’t apply. A pricing dispute doesn’t need a POD. An OTIF dispute doesn’t need a trade agreement.
  3. Update Located and Verified as documents come in.
  4. Don’t file a dispute until the completeness row reads 100%.

The workbook ships with three pre-formatted case blocks. If you need more, copy a block and paste it below.

Shortage Reconciliation

Shortage Reconciliation sheet with three SKU rows. The ASN-to-Received gap column shows red text reading -20 for one Walmart SKU. The First Divergence column reads ASN to Received: check carrier/delivery/receiving.
Shortage Reconciliation with three line items. Red text pinpoints where quantities diverge in the supply chain.

Shortage claims are the most common deduction type. The root cause hides in the quantity gap between two adjacent handoff points. This sheet finds it.

1,005
formulas on this sheet alone. Four gap calculations, a first-divergence auto-diagnosis, and conditional formatting that turns non-zero gaps red, per line item, per case. You enter quantities. The sheet tells you where to look.
SHORTAGE QUANTITY CHAINOrderedPO / ERPPickedWMS / 3PLASN’dEDI 856ReceivedRetailerInvoicedEDI 810GAP 1Order to PickGAP 2Pick to ASNGAP 3ASN to ReceivedGAP 4Received to InvoiceCheck warehouse,inventory, short picksCheck 856 generation,mapping, manual editsCheck carrier, delivery,receiving scansCheck billing logic,UOM, pack conversionEXAMPLEFirst divergence: Gap 3, investigate carrier/deliveryOrdered: 1,000 | Picked: 1,000 | ASN’d: 1,000 | Received: 960 | Invoiced: 1,000

Columns you fill in

Case ID, Retailer, Invoice, PO, SKU/GTIN, UOM, and five quantities: Ordered, Picked, ASN’d, Received, Invoiced.

All quantities must use the same unit of measure. If your PO is in cases and your ASN is in eaches, convert before entering.

Auto-calculated columns

ColumnFormulaWhat it means
Order to Pick GapPicked minus OrderedNegative means short pick. Positive means over pick.
Pick to ASN GapASN’d minus PickedNon-zero means the ASN doesn’t match what left your dock.
ASN to Received GapReceived minus ASN’dNegative means the retailer says fewer units arrived.
Received to Invoice GapInvoiced minus ReceivedPositive means you billed more than the retailer counted.
First DivergenceFirst non-zero gapTells you where to investigate and what to check.
40
units. That's the gap between ASN'd (1,000) and Received (960) in the example above. The workbook flags it in red and points you to the carrier handoff and retailer receiving record. Without the formula, someone on your team subtracts four columns by hand, per line item, per deduction.

Any non-zero gap turns red text. The First Divergence column gives you a plain-language instruction:

  • “Order to Pick: check warehouse/inventory”
  • “Pick to ASN: check 856 generation/mapping”
  • “ASN to Received: check carrier/delivery/receiving”
  • “Received to Invoice: check billing/UOM”
  • “No discrepancy found” if all gaps are zero

How to use it

  1. Pull quantities from each source system. Use the actual EDI 856 payload, not your ERP shipment screen. Use the retailer’s receiving record, not your delivery confirmation.
  2. Enter all five quantities on one row.
  3. Read the gap columns left to right. The first red cell is your investigation starting point.
  4. Use the First Divergence column to decide who to talk to and what evidence to pull.

OTIF Timestamp Log

ONE SHIPMENT → A TIMELINE THAT FLAGS LATE MILESTONES4.0h48.5h18.8h ⚠0.1h2.5hApptconfirmedCarrierpickupEDI 856transmittedEDI 997receivedDeliveryarrivalRetailerreceipt856 sent 18.8h after pickup — Amazon needs < 4hwithin windowmissed
Each milestone gets a timestamp. The sheet computes the gap between them and flags the one that blew the window.
OTIF Timestamp Log sheet with one shipment block for an Amazon ASN case. The Delta hours column shows the gap between milestones. The Within Window column fills each cell green for compliant milestones and red for the late 856, with white text.
Amazon ASN shipment timeline. Green cells mark compliant milestones. A red cell flags the 856 sent 19 hours late.

OTIF disputes need a timeline, not a general explanation. This sheet builds one.

Each shipment gets a block of eight rows, one per milestone:

  1. Appointment requested
  2. Appointment confirmed
  3. Carrier pickup / gate-out
  4. EDI 856 transmitted
  5. EDI 997 received
  6. EDI 824 received (if used)
  7. Delivery arrival / check-in
  8. Retailer receipt / unload complete
8
milestone timestamps per shipment. The sheet computes the hours between each consecutive pair. A 36-hour gap between carrier pickup and delivery arrival on a 200-mile route tells you something went wrong in transit.

Columns you fill in

Case/Shipment ID, Retailer, PO, Shipment/BOL. Then for each milestone: Date, Time, Time Zone, Source System, Retailer Requirement, Within Window (dropdown: Yes, No, N/A), and Evidence File.

Auto-calculated column

Delta (hours) shows the time elapsed between consecutive milestones. Formula: (current milestone date + time) minus (previous milestone date + time) times 24. Displays hours with one decimal place.

How to use it

  1. Pull timestamps from the source systems, not from your ERP’s summary view. The timestamp in NetSuite or Business Central is often when the record was created in your system. That’s different from when the EDI file transmitted.
  2. Record the time zone. A delivery at 2:00 PM ET is different from 2:00 PM CT if the retailer’s appointment window is in local time.
  3. Compare each milestone against the retailer’s specific requirement for that shipment type and program.

Root Cause Register

CLOSE THE LOOP · SO THE DEDUCTION DOESN’T COME BACKDeductionresolvedTag rootcauseCorrective +preventive30/60/90-daycheckRecurring pattern?NoClosed — process fixedYes · redo the fix
A recovered deduction is done. A recurring one is a process you haven’t fixed — the 30/60/90-day checks tell you which.
Root Cause Register sheet with one entry: a retailer receiving discrepancy marked Verified with 30, 60, and 90-day checks all passed.
Root Cause Register for a resolved Walmart shortage. All three recurrence checks passed.

A recovered deduction is yesterday’s problem. A recurring deduction is a process failure you haven’t fixed yet. This sheet connects the two.

11
root cause categories in the dropdown: warehouse pick error, ASN timing/transmission, UOM/item master mismatch, PO version mismatch, carrier appointment miss, invoice mismatch, retailer receiving discrepancy, pricing agreement stale, pack hierarchy error, label/barcode error, and Other.

Columns

Case ID, Retailer, Deduction Code, Amount, SKU/Facility/Lane, Root Cause Category (dropdown), Evidence Supporting Cause, Corrective Action, Preventive Control, Owner, Date Identified, Due Date, Status (dropdown: Open, In Progress, Implemented, Verified, Closed), Recurrence (dropdown: Yes - recurring, Yes - new instance, No, Monitoring), and 30/60/90-Day Check columns.

Recurrence values that start with “Yes” show in red text.

How to use it

  1. After resolving a dispute (won, lost, or written off), add one row with the root cause.
  2. Assign an owner and a due date for the corrective action.
  3. Check the 30/60/90-day columns monthly. If the same root cause keeps appearing, the corrective action didn’t work.
  4. The Dashboard reads from this sheet to show root-cause frequency and total value by cause category.

Dashboard

DASHBOARD · EVERYTHING AUTO-CALCULATED FROM YOUR DATATOTAL EXPOSURE$8,730RECOVERY RATE19.2%OVERDUE CASES1AVG PRIORITY5.7ALSO SUMMARIZED BYBy retailerBy deduction typeRoot-cause frequencyOutcome summaryThree cases produce a full portfolio view. You never type in this sheet.
Every tile is a formula reading from the Dispute Tracker and Root Cause Register. Review it once a month.
Dashboard sheet showing Portfolio Summary with $8,730 total exposure and 19.2% recovery rate, Recovery by Retailer table with Walmart and Amazon rows, and Root Cause Frequency section.
Dashboard auto-calculates from your data. Three cases produce a complete portfolio view with no manual entry.

Don’t type in this sheet. Every cell is a formula that reads from the Dispute Tracker and Root Cause Register.

122
formulas on the Dashboard. All auto-calculated from the Dispute Tracker and Root Cause Register. You review this sheet on the first Monday of each month.

Portfolio summary

MetricWhat it shows
Total Deductions LoggedCount of non-empty Case IDs in the Dispute Tracker
Total ExposureSum of all deduction amounts
Total RecoveredSum of all recovered amounts
Recovery RateTotal Recovered divided by Total Exposure
Open CasesCases not yet marked Won, Lost, Written Off, or Partial
Overdue CasesCases where Days Remaining is negative. Turns red if any exist.
Cases Due in 7 DaysCases with 0 to 7 days remaining
Cases Due in 30 DaysCases with 0 to 30 days remaining
Average Priority ScoreMean of all calculated priority scores

Recovery by retailer

One row per retailer: cases, exposure, recovered, recovery rate, open cases, and average priority score. Walmart, Target, Amazon, Kroger, Home Depot, Costco, and Other.

Exposure by deduction type

One row per type: cases, exposure, recovered, recovery rate. Shortage, OTIF, ASN Accuracy, Pricing, Compliance, Returns, and Other.

Root cause frequency

One row per root cause category: occurrences, total value, and percentage of all tagged causes. If “ASN timing/transmission” accounts for 40% of your deductions by value, that’s where your process improvement budget goes.

Outcome summary

Cases and amounts grouped by outcome: Won, Partial, Lost, Written Off, and Pending.

Deadline Reference

A static reference table compiled from Pixels and Clicks articles. Ten rows covering Walmart (four claim types), Target (three), Amazon (one), and Kroger (two).

Each row shows the reported dispute window, what date starts the clock, the portal or route to file through, a confidence note, and a hyperlink to the source article.

10
retailer-claim-type combinations covered. These windows change. Always verify the current window in the live retailer portal or your supplier agreement before building a dispute plan around it.

For the most current version, use the Deduction Dispute Deadline Lookup on the Pixels and Clicks site.

Google Sheets compatibility

Every formula uses standard spreadsheet functions. No Excel-only features.

FeatureExcelGoogle Sheets
Formulas (IF, COUNTIFS, SUMIFS, SUMPRODUCT, TODAY, etc.)WorksWorks, no changes needed
Data validation dropdownsWorksCarries over from .xlsx upload
Conditional formatting (color-filled cells and colored text)WorksMay need manual re-application
Cross-sheet referencesWorksWorks
Number formats (currency, date, percentage)WorksWorks
14
functions used: IF, AND, OR, TODAY, ROUND, MIN, COUNTA, COUNTIF, COUNTIFS, SUMIFS, SUMPRODUCT, AVERAGEIF, AVERAGEIFS, IFERROR. All available in Google Sheets.

To use in Google Sheets:

  1. Upload the .xlsx file to Google Drive.
  2. Right-click the file. Select Open with, then Google Sheets.
  3. Check that the conditional formatting on the Dispute Tracker’s Days Remaining column (K) and Priority Score column (L) looks right. If the colors are missing, re-apply them manually: select the column, go to Format then Conditional formatting, and set the rules described in the Dispute Tracker section above.

Monthly review process

Open the Dashboard on the first Monday of each month and run five checks.

5
checks, once a month. A 20-minute review that tells you where your deduction recovery process is strong and where it's leaking.
  1. Overdue cases. If Overdue Cases shows anything other than zero, those cases need immediate attention or a write-off decision.
  2. Recovery rate by retailer. A 20% recovery rate at one retailer and 70% at another means your evidence process for that retailer is broken. Check whether the Evidence Checklist completeness for that retailer’s cases was consistently low.
  3. Root cause concentration. If one root cause accounts for more than 30% of total deduction value, it’s a process failure worth a dedicated fix. A warehouse pick error that causes $50,000 in annual shortage claims justifies a pre-ASN quantity validation check.
  4. Type distribution. If shortage claims dominate your exposure, your operational focus is warehouse execution, carrier handoff, and receiving. If OTIF dominates, focus on appointment scheduling and ASN timing. The workbook tells you where the money is leaking.
  5. Aging. If Cases Due in 7 Days stays consistently above zero, your team is working too close to deadlines. Move dispute preparation earlier in your process.

Download

The workbook is free and ungated. Hit download.

Download Workbook (.xlsx)

Compatible with Excel 2016+ and Google Sheets. Upload the .xlsx to Google Drive and open with Google Sheets. See compatibility notes.

200 data rows per sheet. Need more? Select the last formula row, drag down.

If you want to understand the evidence chain behind each document type, read The Six Documents You Need Before Disputing a Deduction. For retailer-specific dispute windows, use the Deduction Dispute Deadline Lookup. To size your annual deduction leakage, try the Deduction-Leakage Calculator.

Three hubs cover the system this workbook plugs into. The Deduction Management hub walks through the full recovery cycle, from decoding a code to preventing the next one. The Supplier Scorecard hub explains the OTIF and ASN-accuracy metrics the Timestamp Log feeds. The Vendor Compliance hub covers the routing-guide rules behind most compliance chargebacks.

This workbook is an operational aid. Retailer programs, portal workflows, deadlines, and required evidence vary and change. Confirm current requirements in your retailer portal and supplier agreement before submitting a dispute.

Frequently Asked Questions

Does the workbook work in Google Sheets?
Yes. Every formula uses functions available in Google Sheets: IF, AND, OR, TODAY, COUNTIFS, SUMIFS, AVERAGEIFS, SUMPRODUCT, IFERROR, and ROUND. Upload the .xlsx file to Google Drive and open it with Google Sheets. Data validation dropdowns carry over. Conditional formatting colors may need manual re-application in some cases.
What does the priority score measure?
The priority score is a 0-to-10 scale combining three factors: deduction amount (weighted 30%), days remaining until deadline (weighted 40%), and evidence readiness (weighted 30%). A $5,000 deduction due in 3 days with incomplete evidence scores higher than a $500 deduction due in 60 days with all documents gathered. Use it to decide which cases to work first.
How does the shortage reconciliation sheet work?
Enter quantities at four handoff points: ordered (from the PO), picked (from the WMS), ASN'd (from the actual EDI 856), and received (from the retailer's record). The sheet auto-calculates the gap between each consecutive pair. A non-zero gap turns red. The First Divergence column tells you where to investigate: warehouse, ASN generation, carrier and delivery, or billing.
What is the Dashboard sheet?
The Dashboard auto-calculates from the Dispute Tracker and Root Cause Register. It shows total exposure, total recovered, recovery rate, overdue and upcoming cases, and breakdowns by retailer, deduction type, root-cause category, and outcome. Every cell is a formula. No manual entry required.
← Back to all articles

Find out what your deductions are actually costing you.

A 30-minute call with the founder, who will tell you which of the three scans fits your problem, or that none of them do.

No account manager and no sales rep, on a fixed scope at a fixed price.