Kition
sheetFinance & Budgets

Break-Even Analysis

How many coffee drippers a small pottery has to sell each month to cover its costs, with the revenue and cost lines crossing on a chart.

Break-Even Analysis — Sheet sample rendered by Kition

Price, unit cost, fixed costs and a planned volume give the contribution margin, the break-even volume and revenue, the margin of safety and the profit at plan. The volume table below steps from 0 to 1,600 units, losses show in red and the first profitable row is highlighted. The volumes are typed as text labels (a leading apostrophe) so the line chart takes them as its categories, while the formulas still multiply them as numbers; revenue and total cost cross at the break-even volume.

Steps

44 commands
showcase.yaml67 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Break-Even Analysis"], ["Stoneware pour-over dripper · monthly view"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - [Selling price, 42, null, Contribution per unit, "=B4-B5"]
        - [Variable cost per unit, 17.5, null, Contribution margin, "=E4/B4"]
        - [Fixed costs per month, 18400, null, Break-even units, "=B6/E4"]
        - [Planned units, 1100, null, Break-even revenue, "=E6*B4"]
        - [null, null, null, Margin of safety, "=(B7-E6)/B7"]
        - [null, null, null, Profit at plan, "=B7*E4-B6"]
  - command: range.setValues
    params:
      range: A11
      values:
        - [Units sold, Revenue, Total cost, Fixed cost, Variable cost, Profit]
        - ["'0", "=A12*$B$4", "=D12+E12", "=$B$6", "=A12*$B$5", "=B12-C12"]
        - ["'200", "=A13*$B$4", "=D13+E13", "=$B$6", "=A13*$B$5", "=B13-C13"]
        - ["'400", "=A14*$B$4", "=D14+E14", "=$B$6", "=A14*$B$5", "=B14-C14"]
        - ["'600", "=A15*$B$4", "=D15+E15", "=$B$6", "=A15*$B$5", "=B15-C15"]
        - ["'800", "=A16*$B$4", "=D16+E16", "=$B$6", "=A16*$B$5", "=B16-C16"]
        - ["'1,000", "=A17*$B$4", "=D17+E17", "=$B$6", "=A17*$B$5", "=B17-C17"]
        - ["'1,200", "=A18*$B$4", "=D18+E18", "=$B$6", "=A18*$B$5", "=B18-C18"]
        - ["'1,400", "=A19*$B$4", "=D19+E19", "=$B$6", "=A19*$B$5", "=B19-C19"]
        - ["'1,600", "=A20*$B$4", "=D20+E20", "=$B$6", "=A20*$B$5", "=B20-C20"]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#0c4a6e" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fontColor, params: { range: "B4:B7", color: "#1d4ed8" } }
  - { command: home.fillColor, params: { range: "A4:B7", color: "#f0f9ff" } }
  - { command: home.borders, params: { range: "A4:B7", preset: outside, color: "#7dd3fc" } }
  - { command: home.numberFormat, params: { range: "B4:B5", format: "$$#,##0.00" } }
  - { command: home.numberFormat, params: { range: "B6", format: "$$#,##0" } }
  - { command: home.numberFormat, params: { range: "B7", format: "#,##0" } }
  - { command: home.fillColor, params: { range: "D4:E9", color: "#0c4a6e" } }
  - { command: home.fontColor, params: { range: "D4:E9", color: "#ffffff" } }
  - { command: home.bold, params: { range: "E4:E9" } }
  - { command: home.numberFormat, params: { range: "E4", format: "$$#,##0.00" } }
  - { command: home.numberFormat, params: { range: "E5", format: "0.0%" } }
  - { command: home.numberFormat, params: { range: "E6", format: "#,##0.0" } }
  - { command: home.numberFormat, params: { range: "E7", format: "$$#,##0" } }
  - { command: home.numberFormat, params: { range: "E8", format: "0.0%" } }
  - { command: home.numberFormat, params: { range: "E9", format: "$$#,##0" } }
  - { command: home.fillColor, params: { range: "D6:E6", color: "#0369a1" } }
  - { command: home.fontSize, params: { range: "D6:E6", size: 13 } }
  - { command: home.bold, params: { range: "A11:F11" } }
  - { command: home.fillColor, params: { range: "A11:F11", color: "#e0f2fe" } }
  - { command: home.fontColor, params: { range: "A11:F11", color: "#0c4a6e" } }
  - { command: home.borders, params: { range: "A11:F11", preset: bottom, style: medium, color: "#0c4a6e" } }
  - { command: home.alignRight, params: { range: "A11:F11" } }
  - { command: home.alignRight, params: { range: "A12:A20" } }
  - { command: home.numberFormat, params: { range: "B12:F20", format: "#,##0;[Red](#,##0)" } }
  - { command: home.borders, params: { range: "A12:F20", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.conditionalFormat, params: { range: "A12:F20", rule: { type: expression, formula: "=AND($F12>=0,$F11<0)", style: { font: { bold: true }, fill: { fg: { Rgb: 16707722 }, pattern: Solid } } } } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 130 } }
  - { command: home.columnWidth, params: { cols: "B:C", width: 76 } }
  - { command: home.columnWidth, params: { cols: "D:D", width: 132 } }
  - { command: home.columnWidth, params: { cols: "E:F", width: 80 } }
  - { command: insert.chart, params: { range: "A11:C20", type: line }, as: chart }
  - { command: chart.set, params: { title: Revenue meets cost, legend: bottom, xTitle: Units sold per month, at: H4, width: 440, height: 320 } }
  - { command: chart.formatSelection, params: { series: 0, color: "#0284c7" } }
  - { command: chart.formatSelection, params: { series: 1, color: "#f97316" } }
  - { command: sheet.read, params: { range: "E4:E9" }, as: results }
  - { command: sheet.read, params: { range: "A16:F16" }, as: row800 }
  - { command: sheet.read, params: { range: "B20:F20" }, as: row1600 }

Checks

5 checks on every build
  • results.results.values = [[24.5],[{"approx":0.5833333,"tol":0.000001}],[{"approx":751.0204082,"tol":0.000001}],[{"approx":31542.8571429,"tol":0.000001}],[{"approx":0.3172542,"tol":0.…
  • results.row1600.values.0 = [67200,46400,18400,28000,20800]
  • results.row800.values.0 = ["800",33600,32400,18400,14000,1200]
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetFinance & Budgets

Household Budget

A monthly budget with planned and actual spending, variance formulas, a total row and a column chart.

1 sheet; used range A1:E12
sheetFinance & Budgets

12-Month Cash Flow Forecast

A bakery's year of cash in and cash out, month by month, with running balances, a colour scale and a warning when cash dips under the buffer.

1 sheet; used range A1:N25
sheetFinance & Budgets

Depreciation Schedule

One machine depreciated four ways, with SLN, SYD, DDB and VDB side by side and the book value each leaves behind.

1 sheet; used range A1:H19
sheetFinance & Budgets

Investment Growth Projection

Twenty-five years of monthly saving projected with FV, split into money paid in and growth earned, with a line chart of the gap opening up.

1 sheet; used range A1:F36