Dropshipping Profit Margin: A Spreadsheet AI Can Audit

Take the selling price, subtract product cost, shipping, payment fees, an allowance for refunds and chargebacks, and the ad cost per order, then divide by the price. The figure before ads is your contribution margin, and it sets your break-even ROAS. AI can audit the sheet's formulas, but you must verify its findings.

A "40% margin" quoted from price minus supplier cost is not a margin at all. It leaves out the three costs that most often turn a promising product into a loss: the ad money to find the buyer, the refunds you pay when parcels are late or wrong and the processing fee you do not get back. This tutorial builds the real number for one product, to the cent.

Follow me on Instagram@sagnikteaches

The product and the assumptions

All figures here are illustrative assumptions, chosen to be plausible and easy to follow. Substitute your own. Say a store sells a compact desk lamp for $39.00 an order. The supplier charges $14.50 for the lamp and $4.80 for shipping to the buyer. Payments run through a card processor at 2.9% plus 30 cents per order, the standard published domestic card rate for Shopify's Basic plan and for Stripe. Out of every 100 orders, 8 are refunded in full and 1 is lost to a chargeback with a $15 fee (Stripe's published fee for each dispute received; it charges another $15 if you counter a dispute, returned only if you win, and other processors differ). Ads cost $12.00 per order placed.

Connect on LinkedInSagnik Bhattacharya

Three modelling choices drive everything below, so state them in the sheet:

Subscribe on YouTube@sagnikteaches

1. Refunded orders still cost you the product and shipping, because the supplier shipped. If a supplier cancels before dispatch, adjust.
2. Processing fees are not returned on refunds. Stripe's refund documentation says it does not return the original processing fees; check yours.
3. Taxes you collect and pass on are not revenue. Strip them out of the price before you start, or your margin is overstated.

The naive margin everyone starts with

Start with what most calculators show. Price $39.00 minus product $14.50 minus shipping $4.80 leaves $19.70, which is 50.5% of the price. Subtract the card fee of $1.43 per order (2.9% of $39.00 is $1.131, plus $0.30) and you have $18.27, or 46.8%. A margin of nearly 47% sounds like a business.

It is not yet a business, because nothing in those figures paid for the ad, the refund or the dispute.

The real calculation, per 100 orders

Working per 100 orders placed avoids fractions of an order. Every line below has a formula you can drop into a sheet.

Per order, contribution before ads is $14.61 (that is $1,460.90 divided by 100) and after ads it is $2.61. As a share of gross sales of $3,900, that is 37.5% before ads and 6.7% after. A product the naive sheet showed at 46.8% before ads makes 6.7% after real costs, or $2.61 on each order. Pay a part-time helper, a subscription or the platform plan out of that, and the product is close to break-even before you have paid yourself.

"Contribution" means what each order contributes towards fixed costs (your platform plan, apps, your own time) and profit. It is the figure to manage, because fixed costs do not change with each sale.

Break-even ROAS: the number to put on your ads dashboard

ROAS (return on ad spend) is revenue divided by ad spend. Your break-even ROAS is the price divided by contribution before ads per order: $39.00 / $14.61 = 2.67. That means every dollar of ads must bring back at least $2.67 of sales just to cover the costs of making those sales. Your actual ROAS here is $39.00 / $12.00 = 3.25, which clears break-even and produces the $2.61 per order.

The break-even cost per order (break-even CPA) is the other way to say it: $14.61. Pay more than that to win an order and you lose money on it.

Compare this with the naive break-even. Using the naive $18.27 contribution, break-even ROAS looks like $39.00 / $18.27 = 2.13. An ad account running at ROAS 2.4 would look comfortably profitable in a dashboard built on the naive sheet, and would be losing about $1.64 per order in reality (a ROAS of 2.4 implies an ad cost of $16.25 per order, against $14.61 of contribution before ads). That gap is why people "scale a winning product" straight into losses.

What moves the answer: three sensitivity checks

Margins are fragile in dropshipping, so test the sheet by changing one input at a time.

Refunds double. Raise the refund rate from 8% to 15% (15 refunds, 1 chargeback). Net sales kept become $3,900 - $585 - $39 = $3,276. Subtracting the same costs ($1,450 + $480 + $143.10 + $15 = $2,088.10) leaves $1,187.90 before ads, or $11.88 an order. With ads at $12.00 an order, contribution after ads is -$12.10 for the 100 orders, a small loss. Break-even ROAS has risen to $39.00 / $11.88 = 3.28. A seven-point rise in refunds, perhaps from poor delivery times or pictures that do not match the product, moved a profitable product to a loss.

Ads get 25% dearer. At $15.00 an order, base-case contribution before ads of $14.61 is below the ad cost: a loss of $0.39 an order. Ad costs rise when audiences saturate, so check weekly.

Supplier raises shipping by $1.50. Contribution before ads falls by $1.50 per order to $13.11, break-even ROAS rises to $39.00 / $13.11 = 2.97, and the $12.00 ad cost leaves only $1.11 an order. Small supplier changes matter when your profit is $2.61 an order.

Keep these three as scenarios in a column beside the base case. The point of the exercise is not to predict the future, but to see how much room you have before the product turns.

Setting the sheet up

Build it in two blocks: inputs at the top, calculations below. Never type a number inside a formula; refer to an input cell. That single habit makes the sheet auditable.

Then the calculations, per order, in cells below. Revenue kept: =B2*(1-B7-B8). Fees: =B2*B5+B6. Chargeback fees: =B8*B9. Contribution before ads: =B2*(1-B7-B8)-B3-B4-(B2*B5+B6)-B8*B9. After ads: subtract B10. Break-even ROAS: =B2/(contribution before ads). Check the base case against the table above: it should return 14.609, 2.609 and 2.67.

Add a column for each scenario (higher refunds, dearer ads, dearer shipping) that copies the inputs and changes one. Add a final row that tells you in words: "Profitable at current ads" or "Below break-even by $X". A formula that returns text is a quicker read than a number at the end of a long day.

Handing the sheet to AI for an audit

Most margin-sheet errors are structural, not arithmetic: a cell referring to the wrong row, a percentage typed as 2.9 instead of 0.029, a refund taken off revenue and off product cost when the supplier does not refund you. These are exactly what a reviewer, human or AI, can catch from the formulas alone. Give the assistant the formulas as text, not a picture of the sheet, and tell it which assumptions you made.

Audit this dropshipping margin sheet. I will paste each cell with its label, value and formula.
My assumptions: refunded orders still incur product cost and shipping; card fees are not
returned on refunds; chargebacks lose the sale plus a fixed fee; ads are charged per order placed.
Task: (1) list any formula that contradicts these assumptions, (2) list any hard-coded
number inside a formula, (3) list any percentage that looks entered as a whole number,
(4) check that break-even ROAS = price / contribution before ads, (5) say which cells you are
unsure about. Do not rewrite the sheet. For each finding give the cell and one sentence.

[paste cells]

Now consider an illustrative flawed sheet. Contribution before ads is written as =B2*(1-B7-B8)-B3*(1-B7)-B4-(B2*B5+B6). The audit comes back with two findings: "Product cost is multiplied by (1-B7), which assumes the supplier refunds refunded orders, contradicting your assumption" and "The chargeback fee B9 is never used". Both are true. The first flattered the margin by roughly $1.16 per order (8% of $14.50) and the second omitted $0.15. Together they turned a $2.61 profit into $3.92, which is a 50% overstatement.

Verify each finding yourself by looking at the cells. An assistant can misread a long formula, invent a cell that does not exist or declare something wrong because it assumed a different rule. Treat the audit as a checklist of places to look, and fix only what you have confirmed. If you want a refresher on working with spreadsheets and chat assistants, our guide to Google Sheets AI prompts covers how to give a sheet's contents to an assistant safely.

Testing the sheet with known answers

Before trusting any sheet, feed it cases where you know the answer. Set price to $100, costs to zero and refunds and chargebacks to zero: contribution before ads should be $100 minus the card fee ($2.90 + $0.30 = $3.20), so $96.80. Set refunds to 100%: contribution should be negative by costs and fees. Set price equal to total cost: break-even ROAS should be undefined or very large, and your formula should not return a division error. These three quick runs find most real errors in a few minutes.

Also compare the sheet with real data. Take the last month's orders from your store admin and payment dashboard, add up gross sales, refunds, fees and ad spend, and see whether the sheet's per-order figures match the actual month. The first reconciliation usually uncovers something: a fee line you forgot, an app subscription charged per order or refunds recorded at the wrong date.

Counting refunds and chargebacks honestly

Choose the allowance from your own data, not from hope. If you have fewer than 100 orders, use a pessimistic placeholder and review it as orders accumulate. Count refunds by the order date, not the refund date, so a bad week of deliveries shows up against the week that caused it, and keep a column for the reason: late delivery, not as described, changed mind, damaged, other. The mix tells you what to fix. A product with mainly "late delivery" reasons has a supplier problem, one with "not as described" has a page or image problem (see replacing supplier images safely), and "changed mind" suggests a returns policy worth tightening, as refund policy wording and AI replies explains.

Chargebacks need a longer lens. A buyer can dispute a payment some weeks after the order, so recent weeks always look better than they will end up. Keep a rolling figure over at least 60 to 90 days if your volume allows, and apply the processor's own fee rather than a guess. When a dispute arrives, a prepared evidence pack is worth the minutes; a chargeback response template drafted with AI shows what to include.

Choosing the refund allowance when you have no data yet

A brand-new product has no refund history, and the temptation is to type a hopeful 2%. Resist it. Start from a pessimistic placeholder, say 10%, and run the sheet on it. If the product only works at 2% refunds, it is too fragile to test with real ad money, because a single slow shipment or a mismatched colour will move the number past your margin. If it still clears break-even at 10%, you have a cushion, and every point of refund rate you beat becomes profit.

Then replace the placeholder as orders arrive. After 30 orders the figure is still noisy (one refund is 3.3 percentage points); after 100 it starts to mean something. Mark the sheet with the number of orders behind the rate, so nobody, including you in three months, mistakes a guess for a measurement.

A second product, walked through quickly

To see how different the answers can be, try a cheaper item. A phone stand sells at $19.00, costs $3.20 from the supplier with $2.10 shipping. Card fee: 2.9% of $19.00 is $0.551, plus $0.30, giving $0.85 an order. With 6% refunds and 0.5% chargebacks, revenue kept per order is $19.00 x (1 - 0.06 - 0.005) = $17.77. Add the expected chargeback fee of 0.005 x $15 = $0.08. Contribution before ads: $17.77 - $3.20 - $2.10 - $0.85 - $0.08 = $11.54. Break-even ROAS: $19.00 / $11.54 = 1.65.

The cheaper product has a much lower break-even ROAS, because a smaller share of its price goes to cost, even though each order earns less in dollars. It also has less room for error in dollars: one refund wipes out the contribution of more than one and a half sales. Low-ticket items need low ad costs and low refund rates; higher-ticket items can afford dearer ads but suffer more when a refund lands. Neither is better by default, and only the sheet can tell you which suits your channel.

From one product to a store-wide view

Once you have a sheet you trust for one product, copy it for each product and add a summary tab: units, net sales, contribution before ads, ad spend and contribution after ads, with one row per product. Add a line for fixed costs (platform plan, apps, domain, any subscription) and your own time at a rate you choose. The last line is your real profit for the month, and the rows tell you which products to scale, repair or drop.

For more on running the same check across a whole catalogue, see checking your margins product by product with AI, and Google Ads for dropshipping with AI for keeping ad cost inside the break-even you have just worked out.

Mistakes that make the sheet lie

• Using ad platform revenue as truth. Dashboards often count revenue from orders that were later refunded. Use your store's net sales figure, not the ad platform's.
• Spreading ad cost by sales rather than orders. Ads are paid whether or not the buyer later refunds. Charge them against orders placed.
• Using the headline card rate for every order. Premium and international cards cost more. Shopify lists premium cards at 3.5% + 30 cents on its Basic plan and adds 1% for international cards; Stripe adds 1.5% for international cards and 1% when currency conversion is needed. Take your blended rate from last month's payouts, not from the pricing page.
• Forgetting app and subscription charges that scale with orders. Some apps bill per order, which makes them variable costs.
• Treating a good week as the average. One lucky ROAS of 5 does not make a product. Use at least a couple of weeks and a few hundred dollars of ad spend before deciding.

When a sheet looks too good, assume a mistake and look for it. When it looks bad, assume it may be right.

The Monday margin check, product by product

1. Update ad spend, orders, refunds and fees for the week from the store and processor dashboards.
2. Recompute CPA, refund rate and contribution after ads for each product.
3. Compare each product's real ROAS with its break-even ROAS. Flag any within 15% of break-even.
4. Run the AI audit prompt on any sheet you changed during the week.
5. Decide: scale, hold, repair (supplier, page, policy) or stop. Write one line why.

If you would like help turning this into a sheet that updates itself from your store and ads accounts, that is the kind of job a 1:1 AI implementation call is for, but the manual version above is already enough to stop the most expensive mistake in this business: scaling ads on a margin that was never real.

Questions about dropshipping margin sheets

What is a good dropshipping profit margin?

There is no honest universal figure, because it depends on ad cost, refunds and fees for your product. Instead of aiming at a percentage, find your break-even ROAS and check that your real ad results beat it with room to spare. Then judge the margin after ads, not before.

Should refunded orders reduce my cost of goods too?

Only if the supplier actually refunds you or the order never shipped. In dropshipping the supplier often keeps your payment once the parcel leaves, so a refund to the buyer can be a full loss of revenue with the product cost still paid. Check each supplier's terms and enter what really happens.

Do payment fees come back when I refund?

Often not. Stripe's published refund guidance says it does not return the original processing fees on refunds, and other processors have their own rules. Look up your own processor's current terms and put the fee on refunded orders into the sheet as a cost.

How often should I update the numbers?

Refresh the inputs weekly while you are testing a product and monthly once it is stable. Ad cost and refund rate move fastest. Keep a dated copy each time so you can see the trend and notice when a once-profitable product slides below break-even.

Can I trust AI to do the sums?

Let it check the structure, not replace the sums. A chat assistant is good at spotting a formula that double-counts or divides by the wrong cell, and poor at long arithmetic in its head. Give it the formulas as text, ask it to list suspect cells and verify each finding yourself.

Further reads

• Shopify Dropshipping Automation: What AI Runs and What Breaks — See what AI runs in a Shopify dropshipping store.
• AI for a Dropshipping Business: Where It Helps and Where It Fails — Learn where AI helps and fails in a running store.
• Dropshipping Customer Service With AI: Can It Handle Slow Delivery? — Cut refunds caused by slow delivery.
• AI Dropshipping Product Finder: Check Demand and Supplier Risk First — Screen products for margin before you test them.
• How to Find AI Savings Line by Line in Your Profit and Loss — Read the whole profit and loss line by line with AI.
• How to Use AI to Understand Your Profit and Loss Statement — Understand the statement your sheet feeds into.
• Free Shipping Threshold: Set It From Your Average Order Value — A worked order-export example: find your average order value, price the shipping you give away, and test two thresholds with ChatGPT or Claude.
• Shopify Profit Report With Claude: Add Ad Spend, COGS and Returns — Join four exports in Claude to see profit per order after ads, costs, shipping and returns, with a full worked month.
• How Does AI Dropshipping Work? One Store's AI Jobs and Approval Steps — One dropshipping store's jobs sorted into AI-does and owner-approves, with the approval step for supplier vetting, listings, support and ads.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Stripe pricing and refund documentation (standard 2.9% + 30c domestic card rate, international and conversion surcharges, $15 dispute fee; processing fees not returned on refunds), Shopify pricing page for Basic plan card rates, read November 2026. All other figures in the worked example are illustrative assumptions, not benchmarks.