The product-research spreadsheet I use before trusting a supplier price

A supplier price can look profitable until the rest of the order economics are added. A $12 item sold for $40 appears to leave $28, but that gap is not margin. Shipping, discounts, payment fees, refunds and customer acquisition all claim part of it.

The spreadsheet I use before trusting a supplier price turns every listing into the same question: how much contribution profit remains per order under realistic assumptions?

Start with the price customers actually pay

Do not calculate margin from the compare-at price or the undiscounted product price if most customers use an offer.

Use:

Net selling price = Listed price × (1 - Average discount rate)

If a product is listed at $44 and the expected average discount is 10%:

$44 × (1 - 0.10) = $39.60

“Average discount” should reflect the blended effect of welcome codes, automatic discounts, bundles and other promotions. If you do not have store data yet, test several scenarios rather than choosing one optimistic number.

For example:

• Base case: 10% average discount
• Better case: 5%
• Stress case: 15%

This makes it easier to see whether the product works only when customers pay full price.

Keep product cost and shipping separate

Supplier listings often emphasize the lowest product price while showing shipping only after a destination, variant or quantity is selected.

Give product cost and supplier shipping their own spreadsheet columns:

• Unit product cost
• Shipping cost to the target country
• Packaging or handling fees
• Duties or import charges, if applicable

Keeping them separate helps identify what changed. A supplier may reduce the unit price while increasing shipping, leaving the actual landed cost unchanged.

For basic product research:

Landed supplier cost = Product cost + Shipping + Handling + Duties

Use the price for the exact variant you intend to advertise. The smallest size or cheapest color is not useful if customers are likely to choose a more expensive variant.

If shipping varies by destination, calculate separate rows for your main markets. A product can have acceptable economics in one country and poor economics in another.

Calculate payment fees after discounts

Percentage-based payment fees apply to the amount the customer pays, not the original listed price.

A common spreadsheet structure is:

Payment fee = Net selling price × Percentage fee + Fixed fee

For a $39.60 payment, using an illustrative fee of 2.9% plus $0.30:

$39.60 × 0.029 + $0.30 = $1.45

Use the fees that apply to your actual Shopify and payment setup. Also check how currency conversion, international cards, alternative payment methods and refunded transactions are handled.

The point is not to predict every fee perfectly. It is to avoid treating payment processing as zero.

Treat refunds as an expected cost

Refunds are irregular at the order level but predictable enough to include as an allowance when comparing products.

A simple conservative method is:

Refund allowance = Net selling price × Expected refund rate

At a 6% expected refund rate:

$39.60 × 0.06 = $2.38

This assumes refunded revenue is lost while the original product and shipping costs have already been incurred. That may be conservative if products are returned and resold, but dropshipped returns are not always economically recoverable.

You can build a more detailed model once you have data. For initial research, consistency is more important than false precision.

Refund assumptions should also vary by product type. Sizing-sensitive apparel, fragile goods and products with ambiguous compatibility may deserve a higher stress-case assumption than straightforward accessories.

Include target acquisition cost before calling it profitable

Margin before advertising is useful, but it does not tell you whether paid acquisition is viable.

Add a target customer acquisition cost, or CAC, to every row. This is the maximum or expected advertising cost assigned to one order—not click cost and not total campaign spend.

The core formula is:

Contribution profit = Net selling price - Product cost - Shipping - Payment fees - Refund allowance - Target CAC

You can add other variable costs, such as packaging, apps charged per order or support allowances, where relevant.

The corresponding contribution margin is:

Contribution margin % = Contribution profit ÷ Net selling price

I also track a research-stage ROI:

Order ROI = Contribution profit ÷ (Product cost + Shipping + Payment fees + Target CAC)

ROI definitions vary, so label the denominator in your sheet. Otherwise, two sellers can report different “ROI” figures from identical economics.

Worked example

Assume the following inputs:

| Input | Amount | |---|---:| | Listed price | $44.00 | | Average discount | 10% | | Net selling price | $39.60 | | Product cost | $11.20 | | Supplier shipping | $4.80 | | Payment fee | 2.9% + $0.30 | | Expected refund rate | 6% | | Target CAC | $12.00 |

First calculate the payment fee:

$39.60 × 2.9% + $0.30 = $1.45

Then calculate the refund allowance:

$39.60 × 6% = $2.38

Now calculate contribution profit:

$39.60 - $11.20 - $4.80 - $1.45 - $2.38 - $12.00 = $7.77

Contribution margin is:

$7.77 ÷ $39.60 = 19.6%

Using product cost, shipping, payment fees and CAC as the ROI denominator:

$7.77 ÷ ($11.20 + $4.80 + $1.45 + $12.00) = 26.4%

The product has not “made” $7.77 yet. This is a planning estimate based on assumptions. It does, however, show exactly what must remain true for the listing to meet the model.

Solve for the CAC you can afford

You can also rearrange the formula to find break-even CAC:

Break-even CAC = Net selling price - Product cost - Shipping - Payment fees - Refund allowance

In the example:

$39.60 - $11.20 - $4.80 - $1.45 - $2.38 = $19.77

Spending $19.77 to acquire an order would leave approximately zero contribution profit under these assumptions.

If you require $8 contribution profit per order:

Target CAC = $19.77 - $8.00 = $11.77

That is more useful than asking whether a supplier price “looks cheap.” It connects the listing directly to an acquisition constraint.

Turn the sheet into a supplier filter

My minimum columns are:

• Supplier and listing URL
• Variant
• Destination country
• Listed selling price
• Expected discount
• Product cost
• Shipping
• Payment fee
• Refund allowance
• Target CAC
• Contribution profit
• Contribution margin
• ROI
• Delivery time
• Stock status

Then I add rejection rules. For example:

IF(ContributionMargin < 0.20, "Reject", IF(ShippingDays > 12, "Review", "Pass"))

A spreadsheet is enough for a short list. When comparing many live supplier listings, the same logic can be written as a custom scoring formula in Drop-IQ, combining margin and ROI with shipping time, stock, trends and if/then rules.

The honest next step is to take one supplier listing you are considering, enter the real variant and destination costs into this model, and stress-test it at a higher discount, refund rate and CAC before creating the product page.