Why every bid needs a ceiling
Pay-per-click advertising looks simple from the outside. You choose keywords or products, you say how much a click may cost, and visitors arrive. The difficult part is the number in the middle. Bid too little and competitors take the clicks. Bid too much and every order you win costs more than it earns, while the dashboards still show healthy traffic and growing revenue.
The way out is to stop asking what does a click cost? and to ask what is a click worth? Once you know what one click is worth to your business on average, you have a ceiling for every bid: the break-even CPC. Below it a click adds profit. Above it a click destroys profit, however good the click-through rate looks.
This free lesson shows how to calculate that ceiling in Excel from three numbers you already have, how to compare it with what you really pay and how to flag the ad groups that need a lower bid. The example company is Fernwick Cycle Supply, a fictional online shop for bicycle parts. All ad groups, people, campaigns and numbers are invented for learning, and every benchmark in this course is illustrative.
The three numbers behind a break-even bid
A break-even bid needs no forecast and no special tool. It is built from three inputs that sit in your shop system and your ad account.
- Average order value (AOV). Revenue ÷ orders for the ad group. A brake-pad order at Fernwick is small, €38 on average. An order from the bike-computer ad group is large, €240.
- Gross margin. The share of the order value that is left after product cost, shipping, packaging and payment fees, before advertising. This is the money that has to pay for the ads and still leave a profit. Margins differ a lot: 48% on bike lights, only 22% on bike computers.
- Conversion rate. Orders ÷ clicks. It tells you how many clicks you have to buy for one order. At 4.6% you need about 22 clicks per order, at 1.2% about 83.
Notice what is not on the list: impressions, click-through rate, average position, quality score. They all influence how many clicks you get and what you pay for them. None of them changes what a click is worth once the visitor is on your site.
The formula in one line
Start with one order. Margin per order = average order value × gross margin. For the Road tyres ad group that is €92 × 31% = €28.52. If you could buy orders directly, €28.52 is the most you could pay for one. That figure has a name of its own, the break-even CPA, and lesson 2 of the course builds on it.
But you buy clicks, and most clicks do not become orders. With a conversion rate of 2.9%, one click carries only 2.9% of an order. So one click is worth, on average, €28.52 × 2.9% = €0.83. That is the break-even CPC. In words: break-even CPC = average order value × gross margin × conversion rate.
The last step is a comparison. Fernwick pays €0.88 per click on Road tyres. Margin per click after CPC = break-even CPC − average CPC = €0.83 − €0.88 = −€0.05. The minus sign is the whole message: each click loses about 5 cents, which is €53 per 1,000 clicks. The ad group may show a respectable ROAS in the ad platform and still shrink the bank balance.
A fully worked example: ten ad groups at Fernwick Cycle Supply
Here is the data from the lab above: ten search ad groups with their figures for the last 90 days. Columns B to E are given; you calculate the rest.
| Ad group | Average order value | Gross margin | Conversion rate | Average CPC |
|---|---|---|---|---|
| Brake pads | €38 | 42% | 4.6% | €0.52 |
| Chains & cassettes | €64 | 38% | 3.4% | €0.71 |
| Road tyres | €92 | 31% | 2.9% | €0.88 |
| MTB tyres | €104 | 33% | 2.7% | €0.83 |
| Helmets | €118 | 44% | 2.1% | €0.96 |
| Bike lights | €46 | 48% | 3.8% | €0.58 |
| Pedals | €72 | 36% | 2.5% | €0.74 |
| Saddles | €86 | 41% | 1.9% | €0.79 |
| Bike computers | €240 | 22% | 1.2% | €0.92 |
| Workshop tools | €54 | 45% | 3.1% | €0.49 |
Look at the raw data first and try to guess which ad groups have a problem. Most people pick the ones with the highest CPC, Helmets at €0.96 and Bike computers at €0.92. One of those guesses is right and one is wrong, and the reason is the point of the lesson: a CPC is only high or low relative to what the click earns.
Step by step in Excel
The lab asks for four columns and one summary cell. You type each formula once in row 4 and fill it down as far as row 13.
- Margin per order (F4):
=B4*C4. Order value times margin. For Brake pads: €38 × 42% = €15.96. - Break-even CPC (G4):
=F4*D4, or in one step=B4*C4*D4. For Brake pads: €15.96 × 4.6% = €0.73. - Margin per click after CPC (H4):
=G4-E4. For Brake pads: €0.73 − €0.52 = €0.21. Positive, so each click adds about 21 cents. - Verdict (I4):
=IF(E4>G4,"Lower bid","OK"). IF tests whether the CPC is above the break-even CPC and returns one of two texts. - Count the problem ad groups (E15):
=COUNTIF(I4:I13,"Lower bid"), or straight from the data without helper columns:=SUMPRODUCT((E4:E13>B4:B13*C4:C13*D4:D13)*1).
The SUMPRODUCT version deserves a second look, because the same pattern returns throughout the course. The comparison E4:E13>B4:B13*C4:C13*D4:D13 produces ten TRUE or FALSE values, one per ad group. Multiplying by 1 turns them into ones and zeros, and SUMPRODUCT adds them up. The result does not depend on any helper column, so it stays correct even if someone deletes column I.
Reading the result
With all formulas in place the sheet looks like this.
| Ad group | Margin per order (F) | Break-even CPC (G) | Average CPC (E) | Margin per click after CPC (H) | Verdict (I) |
|---|---|---|---|---|---|
| Brake pads | €15.96 | €0.73 | €0.52 | €0.21 | OK |
| Chains & cassettes | €24.32 | €0.83 | €0.71 | €0.12 | OK |
| Road tyres | €28.52 | €0.83 | €0.88 | −€0.05 | Lower bid |
| MTB tyres | €34.32 | €0.93 | €0.83 | €0.10 | OK |
| Helmets | €51.92 | €1.09 | €0.96 | €0.13 | OK |
| Bike lights | €22.08 | €0.84 | €0.58 | €0.26 | OK |
| Pedals | €25.92 | €0.65 | €0.74 | −€0.09 | Lower bid |
| Saddles | €35.26 | €0.67 | €0.79 | −€0.12 | Lower bid |
| Bike computers | €52.80 | €0.63 | €0.92 | −€0.29 | Lower bid |
| Workshop tools | €24.30 | €0.75 | €0.49 | €0.26 | OK |
Four of the ten ad groups pay more for a click than the click earns: Road tyres, Pedals, Saddles, Bike computers. The Helmets guess from above was wrong. Helmets has the highest CPC in the table, €0.96, but also the highest break-even CPC, €1.09, because a €118 order at 44% margin leaves €51.92 per order. It is one of the healthiest ad groups in the account.
Bike computers is the opposite case. The order value of €240 is by far the largest, which makes the ad group look attractive. But the margin is only 22% and only 1.2% of clicks convert, so a click is worth €0.63 while it costs €0.92. With −€0.29 per click it is the most expensive mistake on the sheet: €286 lost per 1,000 clicks.
The chart makes the pattern visible at a glance. Wherever the black marker lies to the right of the bar, the bid is above the ceiling. The best ad group by margin per click is Workshop tools with €0.26 per click; Brake pads and Bike lights follow closely. These are the ad groups where a higher bid could be tested, because there is room between what a click costs and what it earns.
What to do with an ad group above break-even
"Lower bid" is the obvious verdict, and often the right first move. It is not the only lever. The break-even CPC is a product of three numbers, and each of them can be improved.
- Lower the bid to below the break-even CPC. You will lose some clicks, mostly the most expensive ones. Lesson 3 shows how to move bids in capped steps so that one noisy period cannot wreck a keyword.
- Raise the conversion rate. A better landing page, clearer delivery information or tighter keywords lift the value of every click. For Road tyres the conversion rate would have to rise from 2.9% to about 3.09% to break even at today's CPC.
- Raise the order value. Bundles, accessories and free-shipping thresholds increase the margin per order without touching the bid.
- Cut waste inside the ad group. An average hides a mix. A few search terms with many clicks and no orders can pull a good ad group below break-even. Negative keywords repair the average.
- Check the margin. Sometimes the honest answer is that the product cannot carry paid clicks at current prices. That is useful to know before the budget is spent.
How sensitive is the break-even CPC?
Because the three inputs are multiplied, a change of 10% in any one of them moves the break-even CPC by 10%. The conversion rate is usually the least certain of the three, so it pays to see how much rests on it. For Road tyres, with a margin per order of €28.52:
| Conversion rate | Break-even CPC | Against a CPC of €0.88 |
|---|---|---|
| 2.5% | €0.71 | loses money |
| 2.9% | €0.83 | loses money |
| 3.2% | €0.91 | profitable |
| 3.5% | €1.00 | profitable |
A shift of three tenths of a percentage point in the conversion rate turns a losing ad group into a profitable one. That is a small difference, well within the normal variation of a month with a few dozen orders. Two conclusions follow. First, use enough data: 90 days are better than 14, and an ad group with eight orders does not have a reliable conversion rate at all. Second, do not treat a verdict as final when the margin per click is within a few cents of zero. Pedals at −€0.09 and Saddles at −€0.12 are clear cases; an ad group at −€0.01 is not.
Break-even is a ceiling, not a target
A common misreading is to set every bid exactly at its break-even CPC. At that bid the ad group earns nothing: all of the margin goes to the ad platform. Break-even marks the point where you stop losing money, not the point where you make the most.
The profit-maximising bid lies below the ceiling, and how far below depends on how quickly clicks get more expensive as you raise the bid. Lesson 15 of the course works through a bid landscape and finds that point with MAX, INDEX and MATCH. As a working rule until then: decide which share of the margin you want to keep, for example 30%, and bid at most the remaining 70% of the break-even CPC. For Helmets that would be €1.09 × 70% = €0.76.
Common mistakes and how to spot them
The arithmetic is short, which makes the typical errors easy to recognise.
- Using revenue instead of margin.
=B4*D4gives revenue per click. For Road tyres that is €2.67, more than three times the true ceiling of €0.83. If your break-even CPCs look generous, check whether column C is in the formula. - Dividing by the conversion rate.
=B4*C4/D4returns hundreds of euros per click. A break-even CPC is always smaller than the margin per order. - Percentages typed as whole numbers. A conversion rate entered as 2.9 instead of 2.9% makes every result a hundred times too large. Format the cells as percentages before typing.
- A reversed comparison.
=IF(E4<G4,"Lower bid","OK")flags exactly the healthy ad groups. Read the formula aloud: lower the bid if the CPC is greater than break-even. - Mixing time periods. Order value from last year, conversion rate from last week and CPC from yesterday do not belong in one row. Use the same period for all columns.
- Account-wide averages. One conversion rate and one margin for the whole account produce one break-even CPC for everything. The table shows why that fails: the true ceilings range from €0.63 to €1.09.
From break-even CPC to target CPA and target ROAS
Manual bidding works with a CPC. Automated bid strategies ask for a different input, a target CPA or a target ROAS, but they rest on the same three numbers.
The margin per order in column F is the break-even CPA: Road tyres can pay at most €28.52 in ads per order. And the gross margin alone gives the break-even ROAS, 1 ÷ margin: at 31% margin Road tyres needs €3.23 of revenue per euro of ad spend, Bike computers at 22% needs €4.55. Whichever strategy you use, the platform optimises towards the number you give it. If that number is on the wrong side of break-even, automation simply loses money more efficiently.
Quick reference: formulas in this lesson
| Quantity | Formula | In the sheet |
|---|---|---|
| Margin per order | order value × gross margin | =B4*C4 |
| Break-even CPC | order value × gross margin × conversion rate | =B4*C4*D4 |
| Margin per click after CPC | break-even CPC − average CPC | =G4-E4 |
| Verdict | CPC above break-even? | =IF(E4>G4,"Lower bid","OK") |
| Count above break-even | number of ad groups with CPC above break-even | =COUNTIF(I4:I13,"Lower bid") |
| Break-even CPA | order value × gross margin | same as margin per order |
| Break-even ROAS | 1 ÷ gross margin | =1/C4 |
Where the numbers come from in practice
To build this sheet for your own account, export a report by ad group (or by campaign, product group or keyword) for a period long enough to contain at least a few dozen orders per row. You need clicks, cost, conversions and conversion value. Average CPC is cost ÷ clicks, conversion rate is conversions ÷ clicks, and average order value is conversion value ÷ conversions.
The gross margin is the one figure the ad platform does not know. Take it from your shop system or your accountant, per product category if you can. Be strict about what counts as cost: purchase price, inbound freight, packaging, outbound shipping you do not charge for, payment fees and the cost of returns. A margin that is five points too optimistic raises every break-even CPC by the same proportion.
What you will build in the rest of this course
This first lesson is open to everyone. The nineteen labs that follow carry the break-even logic through bid strategies, bid modifiers and budget pacing to a complete review dashboard.
- Break-even CPA and ROAS, manual bids and strategy comparison (lessons 2 to 5). You calculate break-even CPA and ROAS with profit built into the target, turn a target CPA into keyword bids with capped steps, roll campaigns up by bid strategy with SUMIFS and derive the max CPC that a tiered ROAS target implies.
- Bid modifiers (lessons 6 to 9). You build day-parting modifiers from hourly conversion rates, device modifiers from value per click and geo modifiers with a minimum-data rule, then see how the three multiply into one effective bid and why a ceiling is needed.
- Pacing and forecasts (lessons 10 to 12). You track cumulative spend against an ideal line, calculate the daily spend left, forecast month-end spend from the run rate and from the last seven days with DAY and EOMONTH, and replace the even plan with weekday weights.
- Budget decisions (lessons 13 to 16). You separate impression share lost to budget from share lost to rank, estimate marginal CPA to decide where the next euro goes, find the profit-maximising bid on a bid landscape and check which campaigns have enough data and budget for automated bidding.
- Planning, testing and the final project (lessons 17 to 20). You build a target ROAS what-if grid with mixed references and a heat map, split an annual budget by a seasonal index, evaluate a bid strategy experiment with a paired t-test and finish with a bidding and pacing dashboard that assigns an action to every campaign.
Summary
A break-even bid answers the most basic question in paid search: what may a click cost? Multiply the average order value by the gross margin to get the margin per order, multiply by the conversion rate to get the break-even CPC, and subtract the CPC you pay. In Excel that is =B4*C4, =B4*C4*D4 and =G4-E4, with =IF(E4>G4,"Lower bid","OK") for the verdict.
At Fernwick Cycle Supply the calculation shows that four of ten ad groups bid above their ceiling, that the ad group with the highest CPC is one of the most profitable and that the one with the largest orders loses the most per click. None of that is visible in the CPC column alone.
The lab on this page lets you work through the ten ad groups yourself in roughly a quarter of an hour. Every formula is checked as you enter it, and the finished sheet doubles as a template for your own ad groups.
Frequently asked questions
What is a break-even CPC?
It is the highest cost per click at which an ad neither makes nor loses money. Break-even CPC = average order value × gross margin × conversion rate. Above it every click costs more than the margin it brings in.
How do I calculate a break-even bid in Excel?
With the order value in B4, the gross margin in C4 and the conversion rate in D4, type =B4*C4*D4. Compare it with the average CPC in E4 using =IF(E4>G4,"Lower bid","OK") when the break-even CPC is in G4.
Should I bid exactly at the break-even CPC?
No. At break-even the whole margin is spent on ads and nothing is left as profit. Treat it as a ceiling and bid below it, for example at 70% of the break-even CPC if you want to keep 30% of the margin.
Which margin should I use for break-even calculations?
Use the gross margin before advertising: order value minus product cost, shipping, packaging, payment fees and the cost of returns. Do not subtract ad cost, otherwise you count the ads twice.
What is the difference between break-even CPC, break-even CPA and break-even ROAS?
They are the same limit seen from three sides. Break-even CPA is the margin of one order (order value × margin). Break-even CPC is that margin × conversion rate. Break-even ROAS is 1 ÷ gross margin, for example 4.00 at a 25% margin.
How much data do I need for a reliable break-even CPC?
Enough for a stable conversion rate. A few dozen orders per ad group is a sensible minimum; with fewer, lengthen the period or combine similar ad groups. Small changes in the conversion rate move the break-even CPC in the same proportion.
Keep going: PPC Bidding & Budget Pacing: Break-Even Bids, Manual CPC vs Target CPA vs Target ROAS, Bid Modifiers, Monthly Pacing & Spend Forecasts
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Break-Even CPA and ROAS: What an Order May Cost, the ROAS You Need and a Target with Profit Built In
- Manual CPC: Turning a Target CPA into Keyword Bids with Conversion Rates, Capped Steps and ROUND
- Manual CPC vs Target CPA vs Target ROAS: Spend, CPA, ROAS and Share of Spend per Bid Strategy with SUMIFS
- Target ROAS: Value per Click, Tiered ROAS Targets with XLOOKUP and the Max CPC a Target Implies
- Day-Parting: Conversion Rate by Hour Block, an Index Against the Account and Capped Bid Modifiers
- Device Modifiers: Value per Click by Device, the Campaign Baseline with SUMIFS and Bids in 5 % Steps
- Geo Modifiers: CPA by Region, a Minimum-Data Rule, Capped Location Modifiers and a Rank
- Stacked Modifiers: How Device, Region and Time Adjustments Multiply, the Effective Bid and a Bid Ceiling
- Monthly Pacing: Cumulative Spend, the Ideal Line, Pacing in Per Cent and the Daily Spend Left
- Month-End Forecasts: Run Rate vs Recent Trend with DAY and EOMONTH, Variance to Budget and a Status with IFS
- Weekday-Weighted Pacing: Weekday Weights, WEEKDAY and XLOOKUP on Dates and a Daily Plan for the Rest of the Month
- Impression Share: Lost to Budget or Lost to Rank, Missed Clicks, the Spend to Capture Them and a Diagnosis
- Marginal CPA: What the Last Euro Buys, a Ranking of Campaigns and Where to Move Budget
- Bid Landscapes: Marginal CPC, Profit After Ads at Every Bid Level and the Profit-Maximising Bid with INDEX/MATCH
- Ready for Automated Bidding? Conversion Volume, Budget-to-Target Ratio, Days to Enough Data and a Verdict
- What-If Grid: Profit After Ads for Every Target ROAS and Margin with Mixed References, the Best Target and a Heat Map
- Seasonal Budget Plan: A Seasonal Index, the Annual Budget Split by Month with ROUND and Expected Conversions
- Testing a Bid Strategy: A Split Experiment, the Change in CPA, a Paired T.TEST and a Verdict
- Final Project: Bidding & Pacing Dashboard with Month-End Projection, Profit Index, an Action per Campaign and Profit by Strategy