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.
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 commandssteps:
- { 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 buildresults.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.pngwithin the render tolerance
Related
More Sheet samplesBreak-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.
Household Budget
A monthly budget with planned and actual spending, variance formulas, a total row and a column chart.
Depreciation Schedule
One machine depreciated four ways, with SLN, SYD, DDB and VDB side by side and the book value each leaves behind.
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.