Most Amazon sellers I talk to are flying blind. They know their revenue — Seller Central shows that front and center — but when you ask them what their actual profit is after FBA fees, ad spend, COGS, and returns, the room goes quiet. That gap between revenue and real profit is exactly why I spent a weekend building a proper Amazon sales and profits dashboard in Google Sheets, and why I now recommend this approach to every client we onboard at Helion 360.
This isn't a theoretical exercise. I'm going to walk you through how I actually structured it, what data I pull, and the formulas that make it functional rather than decorative.
Why Google Sheets (Not a Paid Tool)
Before anyone asks: yes, there are tools like Sellerboard, InventoryLab, and Shopkeeper that do some of this automatically. I've used them. But for most small to mid-sized Amazon sellers, a well-built Google Sheets dashboard offers something those tools don't — complete control over your logic, zero recurring SaaS cost, and the ability to blend Amazon data with your own COGS spreadsheet, your ad platform exports, and even your wholesale supplier pricing.
When a client can open a tab and immediately see margin by SKU, ad TACOS trend, and net profit by month — all in one place they own — that's when the dashboard actually gets used. That's the goal.
The Data Sources You Need
Before building anything, I always map out the inputs. For an Amazon profits dashboard, you need data from at least three places:
- Amazon Seller Central reports: Specifically the Business Report (units and revenue), the FBA Fee Preview report, and the Payments report for settlement data.
- Amazon Advertising console: Export your Sponsored Products, Sponsored Brands, and Sponsored Display spend at the campaign or ASIN level.
- Your own COGS file: This is the piece most sellers are missing. You need a tab that lists each ASIN with its landed cost — product cost plus freight, duty, and prep fees per unit.
Once these three data sets are available, everything else in the dashboard is just math.
How I Structure the Spreadsheet
I use a tab-based architecture with a strict separation between raw data and calculated outputs. Here's the exact tab layout I use:
- Raw_Sales — Paste your Seller Central Business Report here. Date, ASIN, SKU, units ordered, ordered product sales.
- Raw_Fees — FBA fulfillment fee and referral fee per ASIN, pulled from the FBA Fee Preview or the detailed transaction-level Payments report.
- Raw_Ads — Ad spend by ASIN or SKU per date range. I typically work in monthly buckets.
- COGS_Table — Your internal cost table. ASIN, unit cost, freight per unit, prep per unit, total landed cost.
- Calc_Profit — This is where the magic happens. A single calculated table that pulls from all raw tabs and produces gross profit and net profit per ASIN per period.
- Dashboard — Charts, KPI cards, and summary tables that reference Calc_Profit. This is the only tab most users ever need to look at.
The Core Profit Formula
The calculation I use in the Calc_Profit tab looks like this at its simplest:
Net Profit = Revenue − FBA Fees − Referral Fees − COGS − Ad Spend − Returns Estimate
In Sheets, I build this as a series of named columns using VLOOKUP or INDEX/MATCH to pull values from each raw tab by ASIN. Here's a simplified version of what each column looks like:
- Column B (Revenue): =SUMIF(Raw_Sales!B:B, A2, Raw_Sales!F:F) — sum revenue for this ASIN
- Column C (Units): Similar SUMIF pulling unit count
- Column D (FBA Fees Total): Units × per-unit FBA fee from Raw_Fees via VLOOKUP
- Column E (COGS Total): Units × landed cost from COGS_Table via VLOOKUP
- Column F (Ad Spend): SUMIF from Raw_Ads by ASIN
- Column G (Gross Profit): =B2−D2−E2
- Column H (Net Profit): =G2−F2
- Column I (Net Margin %): =H2/B2
I also add a returns adjustment column — typically 2–4% of revenue as a blended estimate unless you have granular return data — because ignoring returns consistently overstates profit.
Building the Dashboard Tab
The dashboard is where the work pays off. I keep it clean: five KPI cards at the top (Total Revenue, Total Net Profit, Blended Net Margin %, Total Ad Spend, TACOS), followed by two charts and one sortable table.
The charts I always include:
- Monthly trend line: Revenue vs. Net Profit over the trailing 12 months. This is the chart that stops conversations cold when someone sees profit flatline while revenue climbs — a classic sign of rising ad costs or fee increases eating margin.
- ASIN-level margin bar chart: Sort by net margin descending. The losers are always visible here, usually SKUs that looked fine on revenue alone.
For the KPI cards, I use simple bordered cells with large font sizes and conditional formatting — green if net margin is above your target, yellow for warning, red for below. No pivot tables, no complex formulas at the dashboard level. Everything hard is done in Calc_Profit.
The TACOS Calculation
Total Advertising Cost of Sale (TACOS) is one of the most important metrics for Amazon sellers and one of the least understood. I add it to every dashboard:
TACOS = Total Ad Spend ÷ Total Revenue
A healthy TACOS for most categories sits between 8–15%. I've seen sellers with 30%+ TACOS who thought their business was profitable because their ACOS looked fine. ACOS only measures ad revenue against ad spend; TACOS measures ad spend against all your revenue, including organic. When TACOS climbs, it usually means organic rank is weakening and you're becoming more dependent on paid traffic to sustain sales.
Common Mistakes I See
After building versions of this dashboard for a dozen different sellers, the mistakes I see repeatedly are:
- Using sell price from Seller Central as revenue instead of the actual settlement amount (after Amazon's cut)
- Forgetting storage fees, especially for slow-moving inventory in Q1 and Q2
- Using average COGS instead of ASIN-specific COGS, which masks which products are actually profitable
- Not accounting for inbound shipping to FBA in landed cost
Final Thought
The goal of this dashboard isn't sophistication — it's visibility. When a seller can open one tab and answer the question "did I make money this month and on which products," they make better decisions faster. That's what we build for at Helion 360. If you're running ads, growing revenue, and still not sure whether you're profitable, this is the project to prioritize this week.