The Hidden Cost of "Successful" Promotions
Your Black Friday sale was a massive success. You sold 500 units at 40% off. Traffic was up 300%. Conversion rate was through the roof.
Then you run the numbers. After platform fees (2%), payment processing (3%), and the 40% discount, you made $2 per unit. But your COGS is $15 and shipping is $4.
You just lost $17 per unit. On 500 units. You lost $8,500 while celebrating "record sales."
This is more common than you think. Discounting feels like free marketing, but it's actually spending money. The question isn't "Should I run promotions?" It's "Which promotions make financial sense?"
Let's build a system to answer that.
The True Profit Formula (After Discounts)
Most sellers calculate profit wrong. They think:
Profit = Price - COGS
But that ignores half your costs. The real formula is:
Net Profit = List Price - Discount Amount - COGS - Platform Fee - Processing Fee - Shipping Cost - Return Reserve
Let's break it down with a real example:
Example: $100 Product with 30% Discount
- List Price: $100.00
- Discount (30%): -$30.00
- Sale Price: $70.00
- COGS: -$35.00
- Platform Fee (2% of $70): -$1.40
- Processing Fee (2.9% + $0.30): -$2.33
- Shipping (you pay): -$6.00
- Net Profit: $25.27
That's a 36% net margin on the discounted price. Healthy.
Same Product with 50% Discount
- List Price: $100.00
- Discount (50%): -$50.00
- Sale Price: $50.00
- COGS: -$35.00
- Platform Fee (2% of $50): -$1.00
- Processing Fee (2.9% + $0.30): -$1.75
- Shipping: -$6.00
- Net Profit: $6.25
Now you're at 12.5% margin. One return and you lose money.
Same Product with 70% Discount (Liquidation)
- List Price: $100.00
- Discount (70%): -$70.00
- Sale Price: $30.00
- COGS: -$35.00
- Platform Fee (2% of $30): -$0.60
- Processing Fee (2.9% + $0.30): -$1.17
- Shipping: -$6.00
- Net Profit: -$12.77
You're paying $12.77 per unit to clear inventory. Is that worth it?
Building Your Discount Profitability Calculator in Excel
-
3
Set Up Your Product Cost Table
Create a table with columns: SKU, Product Name, List Price, COGS, Average Shipping Cost. This is your foundation. If you don't track COGS, use rough category averages (e.g., 40% of retail for fashion, 25% for digital goods).
-
3
Build Discount Scenarios
Create columns for 10%, 20%, 30%, 40%, 50% discount scenarios. Formula: =ListPrice*(1-DiscountPercent). This lets you see how each discount level affects profitability for each SKU.
-
3
Calculate Net Profit for Each Scenario
For each discount level, calculate: =DiscountedPrice - COGS - PlatformFee - ProcessingFee - ShippingCost. Use conditional formatting: green if profit >15%, yellow if 5-15%, red if <5%.
-
3
Find Your Break-Even Discount
Use Goal Seek to find the maximum discount where net profit = $0. Select net profit cell, go to Data > What-If Analysis > Goal Seek. Set cell to 0 by changing discount percent. This is your danger zone.
-
3
Analyze Actual Discount Performance
Import your sales data with discount codes. Use SUMIFS to calculate total revenue and profit per discount code. Formula: =SUMIFS(profit_column, discount_code_column, "SAVE30"). Some codes will surprise you.
-
3
Create Your Discount Safety Matrix
Build a visual matrix showing safe discount ranges by product category. High-margin products (>50%) can afford 30-40% discounts. Low-margin products (<25%) should max out at 15-20%.
The Stack-Attack Problem: Multiple Discounts
The worst profit killers are stacked discounts:
- 20% off site-wide code
- + 10% email signup discount
- + Free shipping
- + 5% loyalty points redemption
That's 35% in discounts plus $7 shipping. On a $50 product with 35% gross margin, you just lost money.
In Excel, create a Stack Calculator:
```excel
Discounted Price = List Price * (1 - Discount1) * (1 - Discount2) - Free Shipping
```
Most platforms apply discounts sequentially, not additively. 20% then 10% = 28% total discount, not 30%.
Stop guessing. Get automated profit analysis that shows exactly which discounts are profitable at the SKU level.
Analyze My DiscountsIncludes discount impact calculator
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Calculating discount profitability on revenue instead of actual sale price
Solution: Fees are charged on the discounted price, not list price. A $100 item at 50% off generates $50 in revenue, and fees are calculated on that $50.
Not accounting for increased return rates on discounted items
Solution: Deep discounts attract bargain hunters who return more. If your category has 15% return rate on full price but 30% on 50% off, factor that into profitability.
Running the same discount on all products regardless of margin
Solution: Create discount tiers by product margin. High-margin items can go 40% off, low-margin items max 15% off.
Celebrating 'record sales' without checking if you made money
Solution: Revenue means nothing if you're unprofitable. Always run the profitability analysis after major promotions.
Offering discounts to drive traffic instead of to clear inventory
Solution: Discounts are for liquidation, not acquisition. If you need traffic, invest in ads. You'll know exactly what you're spending.
How to Verify Your Numbers
Ensure accuracy with these verification steps
You know the exact break-even discount for each product category
You can predict profitability before launching a promotion
Your post-promotion analysis shows net profit per discount code
You have a written discount policy based on margin thresholds
You've stopped at least one planned promotion because the math didn't work
Frequently Asked Questions
What's a good profit margin after discounts?
Should I ever run promotions that lose money?
How do I calculate profitability for bundle deals?
What if I don't know my exact COGS?
How often should I review discount profitability?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.