Setting Up an FBA Reconciliation Spreadsheet That Actually Works

Most people building their first Amazon FBA tracking sheet end up with something that looks fine on day one and falls apart by month three. The problem isn't the initial setup. It's that Amazon's settlement reports change format quietly, columns shift without announcement, and after a few months of manually copying data you realize you've been tracking the wrong metric the whole time. I spent roughly six months refining my own workbook before I settled on a structure that could handle returns, reimbursements, and fee recalculations without requiring a complete rebuild every quarter. The approach below is what I ended up using. It's not glamorous but it handles the actual edge cases that come up when you're managing anywhere from 50 to 500 SKUs.

How To Create Worksheet For Amazon Fba That Won't Break on You in Six Months

Start with four tabs. That's it. Any more and you're overcomplicating something that doesn't need complexity. Tab one is your inbound inventory log. Column A through I should track shipment ID, ASIN, SKU, units shipped, units received, receiving date, condition flagged, damaged count, and a notes field. Amazon sends you a reports file every time a shipment is received at a fulfillment center. Download that, paste it under your headers, and let the sheet do the work of flagging discrepancies between what you sent and what Amazon actually logged. Tab two is your transaction detail feed. This one pulls from Amazon's Settlement Report, which you export monthly from Seller Central under Reports > Payments > View Report > Download. The columns here need to be precise. Transaction type, date, ASIN, order ID, item quantity, revenue, FBA fees, referral fees, storage fees, refunds, and a running balance. I use a pivot table at the bottom grouped by month and ASIN so I can see net profit per product without manually adding everything up. Tab three handles reimbursements and claim tracking. This is where most sellers lose money because they never file the claims or they lose track of which ones Amazon already paid. I keep a running list of shipment damages, lost inventory, and overweight fee errors with the claim amount, filing date, status, and resolution date. When Amazon does issue a reimbursement, you match it against this tab so it doesn't get buried in your general transactions. Tab four is your monthly summary. I calculate sell-through rate, total fees as a percentage of revenue, average net profit per unit, and storage cost trends. If any metric jumps more than 15 percent month over month, I dig into the transaction detail tab and find the cause before it becomes a problem.

The specific workaround I ended up using for a problem that nearly ruined my Q4 numbers: Amazon occasionally reclassifies items as "oversize" after they've already been stored for weeks. The storage fees jump dramatically and the item stays under its old size tier in your records. I built a simple check into my inbound tab that compares the dimensions I originally entered against the size tier Amazon assigned, and flags any mismatches automatically. It saved me roughly $2,300 in a single quarter that I would have otherwise written off as unexplained margin compression. A few things beginners consistently get wrong with these worksheets. First, don't rely on the Revenue column alone. Amazon's definition of revenue includes refunds that haven't been issued yet, which makes your numbers look healthier than they are. Always cross-check against actual disbursements from your payment account. Second, the standard settlement report merges orders and fees together in ways that make it nearly impossible to calculate true per-unit profitability without a second pass. I separate line items by pulling the Child ASIN report from the same Downloads section, which gives you clean per-item data that you can VLOOKUP into your main tab. There's a real limitation here that nobody mentions. These sheets work fine if you're under about 3,000 transactions per month. Beyond that, Excel starts choking on the calculation cycles, especially when you're running pivot tables and multiple VLOOKUPs across large datasets. I ran into this during peak season when a single product launch generated over 4,000 line items in one settlement period. I switched to using a Google Sheets version with a script that pulls the data directly from the Settlement Report API instead of manual downloads. If you're doing high volume, the manual approach stops being sustainable around that threshold and you'll need to automate the data extraction part at minimum.

Another edge case worth noting: Amazon sometimes merges two different SKUs into a single fulfillment line item when they're bundled or repackaged at the warehouse. Your worksheet will show fewer units received than you shipped, which looks like a loss until you verify whether it's actually a merged SKU situation. I keep a separate reconciliation log for these instances so I don't file false reimbursement claims.