How to Build a Cost-Volume-Profit Chart in Excel (Break-Even Point on the Graph)

How-to · Updated July 2026 · ~14 min read

A break-even figure in a cell is a fact. A cost-volume-profit chart is an argument. Tell a board "we break even at 3,000 units" and you get a nod; show them the revenue line cutting the total-cost line at 3,000 units, with this month's volume sitting 1,800 units to the right of it, and you get a conversation about which side of that gap the next hire lands on.

This post is the chart, not the theory essay: the assumptions block, the plotting grid, the graph as a scatter with straight lines, a labelled break-even intersection, margin of safety read off the picture, and a one-variable Data Table that answers "what if we price at 42 instead of 45" without redrawing anything.

The five numbers the chart is built from

Everything on the chart derives from a selling price, a variable cost per unit and a fixed-cost total. Put them in a labelled block at the top of the sheet — never buried inside formulas — because the Data Table later needs real input cells to point at.

Contribution per unit is what one extra sale leaves behind after its own variable cost — the money that pays down fixed costs, so break-even is fixed costs divided by it. Break-even revenue works from either direction: =B7*B2 (units × price) or =B6/B5 (fixed costs ÷ contribution margin ratio), both 135,000 here. The second is what you need when you sell a mix and "units" is meaningless. Format B5 and B11 with Ctrl+Shift+% — the stored value is 0.40, not 40.

The arithmetic is trivial; the judgement upstream is not. Materials, packaging, transaction fees and per-unit freight belong in B3; rent, salaried staff, software and insurance in B6. Semi-variable costs — a warehouse team on overtime above a threshold, a tiered delivery contract — need splitting by high-low or regression first. If your cost of sales is still one ledger balance, the COGS formula in Excel is where to start unpicking it.

The plotting grid: three straight lines need seven rows

All three CVP lines are linear, so two points per line would be mathematically exact — but a small grid at round intervals reads better and gives sensible gridlines. Seven rows from zero to roughly twice break-even keeps both the loss triangle and the profit wedge visible. Headers in row 13, data in rows 14 to 20.

Enter these in row 14 and fill down:

A15:  =A14+1000
B14:  =$B$2*A14
C14:  =$B$6
D14:  =$B$6+$B$3*A14
E14:  =B14-D14

Absolute references on every assumption, relative on the units column — press F4 after clicking an assumption cell, because forgetting one makes the total-cost line drift on fill-down. Row 17 shows revenue and total costs both at 135,000, profit exactly nil; break-even landing on a grid row is tidy but not required, since the marker below is driven by cell B7, not by the grid.

Build the chart: Scatter with Straight Lines

1. Select A13:D20 — headers included, column E excluded (that belongs to a different chart, below).
2. Insert tab > Charts group > Insert Scatter (X, Y) or Bubble Chart.
3. Pick the thumbnail whose tooltip reads Scatter with Straight Lines — the one without markers. Hover to confirm rather than counting positions: the smooth-line and with-markers variants sit alongside it and the order has moved between Excel versions.
4. Excel takes the leftmost selected column as X, so Units become the horizontal axis and Revenue, Fixed costs and Total costs become three series. That is the classic CVP graph, already drawn.

Two sub-type choices matter more than they look. Not Scatter with Smooth Lines — it bows the cost line between points, a picture of something that is not happening. And not a Line chart: that treats the units column as text categories on evenly spaced ticks, so the moment your steps stop being uniform (0, 1,000, 2,500, 6,000) the axis lies about the spacing and the intersection lands in the wrong place. Scatter puts units on a true numeric axis, and that one substitution is behind most CVP charts that look right but break even in the wrong spot.

Now make it readable:

1. Chart Design tab > Add Chart Element > Axis Titles > Primary Horizontal, type Units sold; repeat for Primary Vertical with Revenue and cost. Set the chart title to the product name.
2. Right-click the horizontal axis > Format Axis > Axis Options, set Bounds: Minimum 0, Maximum 6000. Same on the vertical axis with Minimum 0 — Excel's automatic bounds often start above zero and visually amputate the loss area.
3. Still in Format Axis, open Number and apply the custom format #,##0,"k" to the vertical axis. The single trailing comma divides the display by a thousand, so 135,000 reads 135k and the labels stop crowding.
4. Right-click the Fixed costs series > Format Data Series > Fill & Line > Line, set Dash type to a dash. Fixed costs are a reference level, not a driver; dashing stops it competing with the two lines that actually cross. Give Revenue and Total costs contrasting solid colours at about 2.25 pt.

Read it in this order. Fixed costs is flat. Total costs starts at 54,000 on the vertical axis — not at the origin — and climbs by 27 per unit. Revenue starts at the origin and climbs by 45, so it eventually catches total costs, and that crossing is break-even. Left of it the vertical gap between the two is loss; right of it the same gap is profit. The gap between total costs and the fixed-cost line, at any volume, is total variable cost. Three lines, four readings.

Label the break-even intersection

Excel will not annotate the intersection, and a floating text box drifts the moment an assumption changes. Add it as a one-point series driven by the cells instead.

1. Chart Design > Select Data > Add (under Legend Entries). Series name: Break-even. Series X values: =Sheet1!$B$7. Series Y values: =Sheet1!$B$8. Clear whatever Excel pre-fills, including any ={1} placeholder, before clicking OK.
2. Right-click the resulting point > Format Data Series > Fill & Line > Line > No line, then Marker > Marker Options > Built-in, circle, Size 9, strong fill.
3. In a spare cell — say G8 — build the caption: =TEXT(B7,"#,##0")&" units / "&TEXT(B8,"#,##0"), giving 3,000 units / 135,000.
4. Right-click the point > Add Data Label, then right-click the label > Format Data Labels. Under Label Options, tick Value From Cells, select G8, untick X Value and Y Value, and set Label Position to Above.

Value From Cells is a desktop-Excel feature from the 2013 generation onwards; on anything older, and in some other surfaces, you are back to a text box — check the Label Options pane in your own build. The payoff: change the price in B2 and the marker moves, the crossing moves, and the caption rewrites itself in one recalculation.

For the vertical drop line to the axis — the detail that turns a marker into a readable annotation — add a two-point helper: =B7 in G2 and G3, 0 in H2 and =B8 in H3. Add it via Select Data > Add with X values =Sheet1!$G$2:$G$3 and Y values =Sheet1!$H$2:$H$3, then format as a thin grey dashed line with no markers. Two points sharing an X value draw a perfect vertical.

Reading margin of safety off the chart

Margin of safety is how far budgeted volume sits above break-even — the cushion before the business starts losing money. At 4,800 budgeted units against a 3,000 break-even that is 1,800 units, 37.5% of budget (cells B10 and B11), or 81,000 of revenue.

Repeat the drop-line trick at budgeted volume: put =B9 in G5 and G6, 0 in H5 and =B9$B$2 in H6, and add that pair as another dashed series in a second colour. The horizontal distance between the two verticals is* the margin of safety, drawn to scale. Label it with a Value From Cells caption reading ="MOS "&TEXT(B10,"#,##0")&" units ("&TEXT(B11,"0.0%")&")".

Two judgements belong in the commentary rather than on the chart. A margin of safety below roughly 20% means a modest sales miss puts you under water, and it should be read against the fixed-cost line: a high fixed base means a steeper drop into loss for the same percentage volume fall. And use budgeted volume, not the best month of the year — this is a downside measure. Tracking that gap monthly is ordinary variance analysis, and this chart explains why a 10% volume miss produced a 40% profit miss.

The profit-volume variant (one line, crosses zero)

Some boards read three lines fluently; others want the answer without the geometry. The profit-volume graph gives them one line: profit against units, crossing the horizontal axis at break-even.

Select A13:A20, hold Ctrl, add E13:E20, then Insert > Insert Scatter (X, Y) > Scatter with Straight Lines. The line starts at −54,000 at zero units, crosses zero at 3,000 and reaches +54,000 at 6,000: the vertical intercept is fixed costs negated, the slope is contribution per unit.

One fix is essential. By default the horizontal axis labels sit at zero on the vertical scale — exactly where this chart's crossing is — so they land on the line. Format Axis > Labels > Label Position: Low drops them to the bottom of the plot area.

Price sensitivity with a one-variable Data Table

This is the step most CVP write-ups name-drop and never demonstrate. A Data Table re-runs the whole model against a list of inputs and writes the answers into a grid. For a column-oriented table, the output formulas go in the row above the first input value, one column to the right of the input column, and the corner cell stays empty.

1. In H10 enter =B7 (break-even units); in I10 enter =B8 (break-even revenue). One-variable tables accept several outputs across that row.
2. In G11:G17 type the prices to test: 40, 42, 44, 45, 46, 48, 50.
3. Select the whole block G10:I17, empty corner included.
4. Data tab > Forecast group > What-If Analysis > Data Table.
5. Leave Row input cell empty; in Column input cell enter $B$2. Click OK.

Read the asymmetry, because it is the point. Cutting the price by 5 pushes break-even up 1,154 units — 38% more volume needed — while raising it by 5 only pulls break-even down 652. Near a thin contribution margin, discounts cost far more volume than price rises save, which settles the "take 10% off to shift stock" conversation faster than any argument.

Four things reliably go wrong:

• The input cell must be on the same sheet as the table. Excel rejects a cross-sheet input cell outright; mirror the assumption onto this sheet and point at the mirror.
• The results are one array formula, {=TABLE(,B2)}. You cannot edit or delete a single result cell — clear the whole range or nothing.
• Frozen values are usually a calculation setting. Check File > Options > Formulas > Calculation options: "Automatic except for data tables" is legitimate in a big model, but then you must press F9.
• The output formula must depend on the input cell. =B7 works because B7 traces back to B2 via B4; a hard-typed 3,000 gives seven identical rows and no error.

For a two-variable version — price down the side, variable cost across the top — put =B7 in the corner cell G20, variable costs in H20:J20, prices in G21:G23, select G20:J23 and set Row input cell $B$3, Column input cell $B$2. Two-variable tables allow exactly one output.

Goal Seek (Data > What-If Analysis > Goal Seek) is what people reach for first: set the profit cell to 0 by changing units and Excel iterates to break-even. It answers one question, overwrites your input, and is unnecessary when =B6/B4 is exact. Save it for models where profit is not linear — tiered commissions, volume rebates, stepped freight.

How HISAB 360 helps

The slow parts of this job are not the arithmetic. They are getting a defensible fixed/variable split out of the ledger, and rebuilding the chart every time someone changes the question.

HISAB 360 is a paid Excel add-in with an AI chat panel docked inside the workbook, and its chart engine builds a styled break-even chart from a plain-English request — "plot revenue, fixed costs and total costs against units from this grid and mark the break-even point" — writing a real Excel chart into the sheet, correctly typed and positioned rather than dumped over your data. Asking for the profit-volume variant, or the sensitivity grid rebuilt at different price points, is another sentence rather than another twenty clicks.

For the cost classification, it connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, pulling GL activity, bills and invoices into sheets so the fixed-cost total is this period's actual overhead rather than last quarter's estimate. And because totals support drill-down, double-clicking the 54,000 lists the transactions behind it — which is how you catch the delivery contract sitting quietly in fixed costs while behaving like a variable one.

Honest limits: Windows desktop Excel only (Microsoft 365 or 2016+), not Mac and not Excel on the web, and it is paid — the trial runs 15 days with 50 AI credits and no card. Give a generated chart the same check as a junior's: confirm the axis bounds and verify the marked intersection against =B6/B4 by hand.

The assumptions that break the chart

CVP draws straight lines because it assumes straight lines. Three places that fails, and one thing to add:

• Multiple products. The chart holds only for one product or a constant sales mix. With a mix, swap contribution per unit for a weighted-average contribution margin ratio and plot revenue on the horizontal axis: break-even revenue is fixed costs ÷ that ratio. A shift in mix invalidates the chart even if nothing else moved.
• Step-fixed costs. Rent is fixed until you need a second unit, supervision until you need a second shift. Past that step the flat line jumps — keep the grid inside the relevant range and say so on the chart.
• Volume discounts on inputs. If variable cost per unit falls at higher volumes the total-cost line bends and true break-even sits left of the drawn one: conservative, but wrong.
• Target profit. Most decisions are "what volume delivers 90,000 of profit", not "where do we stop losing". Put the target in the numerator — =(B6+90000)/B4 gives 8,000 units — and add a second labelled marker.

Past a single-product snapshot these belong in a driver-based model — see building a financial model in Excel — with the profit line tying back to your actual P&L in Excel.

The formulas on one page

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.