How to build an Amazon wholesale sourcing spreadsheet

Most wholesale sellers start with a spreadsheet: the supplier's price list on one side, Amazon's numbers on the other. Here is how to build an Amazon wholesale sourcing spreadsheet that gives an honest answer, the columns and Excel formulas to use, and where a spreadsheet stops being enough.

Want it ready-made? Download the free template with every column and formula below already in place.

1. Keep your targets on their own sheet

Add a sheet called Settings with your targets in fixed cells, so you change them in one place instead of in every formula:

• Settings!B1: target ROI, for example 30%
• Settings!B2: minimum profit per unit, for example 3.00
• Settings!B3: minimum sales per month, for example 30
• Settings!B4: prep, labels and shipping per unit, for example 1.00

2. The supplier columns

Paste the supplier's list into a sheet called Products, one product per row, starting at row 2:

• A: UPC or EAN, formatted as text so leading zeros survive
• B: Title as the supplier writes it
• C: Case pack: units per case
• D: Cost: the supplier's price per unit

3. The Amazon columns

For each product, look up the listing and fill in:

• E: ASIN, and check that the listing's pack size matches what the supplier sells
• F: Buy Box price
• G: Referral fee and H: FBA fee, from Amazon's revenue calculator
• I: Sales per month, estimated from the Best Sellers Rank
• J: Sellers: the number of new offers

4. The formulas

Enter these in row 2 and copy them down:

• K: Landed cost: =D2+Settings!$B$4
• L: Profit: =F2-G2-H2-K2
• M: ROI: =IF(K2>0,L2/K2,""), formatted as a percentage
• N: Margin: =IF(F2>0,L2/F2,"")
• O: Max buy cost: =MIN((F2-G2-H2)/(1+Settings!$B$1),F2-G2-H2-Settings!$B$2)-Settings!$B$4
• P: Decision: =IF(AND(M2>=Settings!$B$1,L2>=Settings!$B$2,I2>=Settings!$B$3),"BUY",IF(L2>0,"REVIEW","AVOID"))

The max buy cost column is the most useful one in the sheet: it is the highest price you can pay the supplier and still meet both targets. Where the cost in column D is above it, you know exactly how much discount to ask for. The method is explained in how to find the highest cost to pay for a wholesale product.

5. Make the answers stand out

• Turn on Filter on the header row and filter column P to BUY to build your order.
• Add conditional formatting: green for BUY, amber for REVIEW, red for AVOID.
• Highlight rows where column J is 1 (usually the brand selling its own product) or above 20 (crowded listings where price wars start).

Where a spreadsheet stops being enough

The formulas are the easy part. The hard part is the data in columns E to J:

• Time: looking up a listing, its fees and its rank takes a minute or two per product. A list of 2,000 products is days of work.
• Freshness: prices, sellers and fees change every day. By the time the last row is filled in, the first rows are out of date.
• What it cannot see: a snapshot hides a falling price, a rising seller count, whether Amazon is on the listing and whether you are allowed to sell the product at all. See how to avoid price wars and how to check if you can sell a product.

Let the spreadsheet fill itself

Wholesale Analyzer builds this spreadsheet for you. Upload the supplier's Excel or CSV file, and it matches every product to Amazon and fills in the live Buy Box price, the fees, the sales, the sellers and your selling eligibility. It then calculates landed profit, ROI, max buy cost, price war risk and a BUY, REVIEW or AVOID decision with your own targets. The results come back as an Excel workbook with every column, a dashboard and a list of products to ask your supplier a better price for. The first 50 products of a list are free every month.