Kition
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.

12-Month Cash Flow Forecast — Sheet sample rendered by Kition

Each month opens with the previous month's closing balance, adds counter sales and catering, and takes away payroll, rent, ingredients (a fixed 31 % of sales, rounded with ROUND), utilities, marketing, the equipment loan and the quarterly tax bill. Closing balances under the $25,000 buffer turn red, a three-colour scale shades the net cash flow of each month, and the notes below find the lowest balance and its month with MIN, MATCH and INDEX.

Steps

48 commands
showcase.yaml76 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Cash Flow Forecast 2027"], ["Juniper Lane Bakery · all figures in dollars"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - ["Cash flow", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Year"]
        - ["Opening balance", 24000, "=B20", "=C20", "=D20", "=E20", "=F20", "=G20", "=H20", "=I20", "=J20", "=K20", "=L20", "=B5"]
        - ["Cash in"]
        - ["Counter sales", 38200, 35400, 41800, 44100, 47600, 51200, 53800, 52400, 46900, 45300, 49800, 61500, "=SUM(B7:M7)"]
        - ["Catering orders", 4200, 3800, 5100, 6300, 8800, 9400, 7200, 6900, 7700, 6100, 8200, 12400, "=SUM(B8:M8)"]
        - ["Total cash in", "=SUM(B7:B8)", "=SUM(C7:C8)", "=SUM(D7:D8)", "=SUM(E7:E8)", "=SUM(F7:F8)", "=SUM(G7:G8)", "=SUM(H7:H8)", "=SUM(I7:I8)", "=SUM(J7:J8)", "=SUM(K7:K8)", "=SUM(L7:L8)", "=SUM(M7:M8)", "=SUM(N7:N8)"]
        - ["Cash out"]
        - ["Payroll", 18500, 18500, 18500, 18500, 18500, 19800, 19800, 19800, 18500, 18500, 18500, 21400, "=SUM(B11:M11)"]
        - ["Rent", 6200, 6200, 6200, 6200, 6200, 6200, 6200, 6200, 6200, 6200, 6200, 6200, "=SUM(B12:M12)"]
        - ["Ingredients (31% of sales)", "=ROUND(B7*31%,0)", "=ROUND(C7*31%,0)", "=ROUND(D7*31%,0)", "=ROUND(E7*31%,0)", "=ROUND(F7*31%,0)", "=ROUND(G7*31%,0)", "=ROUND(H7*31%,0)", "=ROUND(I7*31%,0)", "=ROUND(J7*31%,0)", "=ROUND(K7*31%,0)", "=ROUND(L7*31%,0)", "=ROUND(M7*31%,0)", "=SUM(B13:M13)"]
        - ["Utilities", 1450, 1520, 1380, 1210, 1150, 1290, 1420, 1460, 1240, 1300, 1480, 1610, "=SUM(B14:M14)"]
        - ["Marketing", 900, 900, 1500, 1200, 1200, 2400, 900, 900, 1500, 1200, 2600, 3200, "=SUM(B15:M15)"]
        - ["Equipment loan", 1850, 1850, 1850, 1850, 1850, 1850, 1850, 1850, 1850, 1850, 1850, 1850, "=SUM(B16:M16)"]
        - ["Quarterly tax", 0, 0, 6800, 0, 0, 7400, 0, 0, 7900, 0, 0, 8600, "=SUM(B17:M17)"]
        - ["Total cash out", "=SUM(B11:B17)", "=SUM(C11:C17)", "=SUM(D11:D17)", "=SUM(E11:E17)", "=SUM(F11:F17)", "=SUM(G11:G17)", "=SUM(H11:H17)", "=SUM(I11:I17)", "=SUM(J11:J17)", "=SUM(K11:K17)", "=SUM(L11:L17)", "=SUM(M11:M17)", "=SUM(N11:N17)"]
        - ["Net cash flow", "=B9-B18", "=C9-C18", "=D9-D18", "=E9-E18", "=F9-F18", "=G9-G18", "=H9-H18", "=I9-I18", "=J9-J18", "=K9-K18", "=L9-L18", "=M9-M18", "=N9-N18"]
        - ["Closing balance", "=B5+B19", "=C5+C19", "=D5+D19", "=E5+E19", "=F5+F19", "=G5+G19", "=H5+H19", "=I5+I19", "=J5+J19", "=K5+K19", "=L5+L19", "=M5+M19", "=M20"]
  - command: range.setValues
    params:
      range: A22
      values:
        - [Minimum cash buffer, 25000]
        - [Months under the buffer, "=COUNTIF(B20:M20,\"<\"&B22)"]
        - [Lowest closing balance, "=MIN(B20:M20)"]
        - [Lowest month, "=INDEX(B4:M4,MATCH(B24,B20:M20,0))"]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#7c2d12" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:N4", color: "#7c2d12" } }
  - { command: home.fontColor, params: { range: "A4:N4", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:N4" } }
  - { command: home.alignRight, params: { range: "B4:N4" } }
  - { command: home.numberFormat, params: { range: "B5:N20", format: "#,##0;[Red]-#,##0" } }
  - { command: home.fontColor, params: { range: "B5", color: "#1d4ed8" } }
  - { command: home.fontColor, params: { range: "B7:M8", color: "#1d4ed8" } }
  - { command: home.fontColor, params: { range: "B11:M12", color: "#1d4ed8" } }
  - { command: home.fontColor, params: { range: "B14:M17", color: "#1d4ed8" } }
  - { command: home.italic, params: { range: "A5:N5" } }
  - { command: home.bold, params: { range: "A6" } }
  - { command: home.bold, params: { range: "A10" } }
  - { command: home.fontColor, params: { range: "A6", color: "#7c2d12" } }
  - { command: home.fontColor, params: { range: "A10", color: "#7c2d12" } }
  - { command: home.increaseIndent, params: { range: "A7:A8" } }
  - { command: home.increaseIndent, params: { range: "A11:A17" } }
  - { command: home.bold, params: { range: "A9:N9" } }
  - { command: home.bold, params: { range: "A18:N18" } }
  - { command: home.borders, params: { range: "A9:N9", preset: top, color: "#9ca3af" } }
  - { command: home.borders, params: { range: "A18:N18", preset: top, color: "#9ca3af" } }
  - { command: home.fillColor, params: { range: "A19:N19", color: "#fef3c7" } }
  - { command: home.bold, params: { range: "A19:N20" } }
  - { command: home.borders, params: { range: "A20:N20", preset: topDoubleBottom } }
  - { command: home.fillColor, params: { range: "N4:N20", color: "#f3f4f6" } }
  - { command: home.fillColor, params: { range: "N4", color: "#57200b" } }
  - { command: home.fillColor, params: { range: "N19", color: "#fde68a" } }
  - { command: home.conditionalFormat, params: { range: "B19:M19", rule: { type: colorScale, colors: ["#f87171", "#fef3c7", "#4ade80"] } } }
  - { command: home.conditionalFormat, params: { range: "B20:M20", rule: { type: expression, formula: "=B20<$B$22", preset: lightRedFillDarkRedText } } }
  - { command: home.numberFormat, params: { range: "B22:B24", format: "#,##0" } }
  - { command: home.fontColor, params: { range: "B22", color: "#1d4ed8" } }
  - { command: home.fillColor, params: { range: "A22:B25", color: "#fff7ed" } }
  - { command: home.borders, params: { range: "A22:B25", preset: outside, color: "#fdba74" } }
  - { command: home.alignRight, params: { range: "B22:B25" } }
  - { command: home.bold, params: { range: "B23:B25" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 150 } }
  - { command: home.columnWidth, params: { cols: "B:M", width: 50 } }
  - { command: home.columnWidth, params: { cols: "N:N", width: 62 } }
  - { command: view.freezePanes, params: { cell: B5 } }
  - { command: sheet.read, params: { range: "B13:M13" }, as: ingredients }
  - { command: sheet.read, params: { range: "B19:N20" }, as: totals }
  - { command: sheet.read, params: { range: "B23:B25" }, as: notes }

Checks

7 checks on every build
  • results.ingredients.values.0 = [11842,10974,12958,13671,14756,15872,16678,16244,14539,14043,15438,19065]
  • results.totals.values.0 = [1658,-744,-2288,7769,12744,5788,14152,12846,2871,8307,11932,11975,87010]
  • results.totals.values.1 = [25658,24914,22626,30395,43139,48927,63079,75925,78796,87103,99035,111010,111010]
  • results.notes.values = [[2],[22626],["Mar"]]
  • inspect.used = "A1:N25"
  • 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

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.

1 sheet; used range A1:F20
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

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