LibreOffice Calc · Practical projects · Intermediate
Forecast a Running Cash Balance in LibreOffice Calc
Track dated cash movements, expose a temporary shortfall and audit the opening balance instead of trusting a positive ending total.
Official help reviewed; independent arithmetic checked; native verification pending · Editorial standards
A positive closing balance can hide a shortfall halfway through a plan. Build a fictional workshop cash schedule that shows the balance after every movement, then delay a receipt to see why timing matters.
This is an arithmetic planning exercise, not accounting, investment or financing advice. Amounts use one unspecified currency. No accounts, bank connections or paid tools are needed.
Scope and starting assumptions
Use free desktop LibreOffice Calc with English formulas and semicolon separators. The official online Help reviewed on 21 October 2026 identifies itself as LibreOffice 26.8 Help; that documentation version is not a claim that a stable 26.8 release or this example was tested. The workflow is desktop-oriented, with Linux-style menu labels. No native execution or screenshots are claimed for this page.
The balance is cash available immediately before the first listed movement. Each receipt and payment appears once, on its assumed availability date. This example excludes taxes, fees, interest and transfers between accounts. A real plan needs its own complete inputs.
Enter the chronological movements
Create a blank workbook. Put Opening balance in G2 and 600 in H2. Enter these headers in A1:F1: Date, Movement, Money in, Money out, Balance, Input check.
Enter A2:D7 as follows. DATE formulas avoid ambiguous day/month text parsing; format A2:A7 as dates with Format → Cells → Numbers → Date.
| Row | A: Date formula | B: Movement | C: Money in | D: Money out |
|---|---|---|---|---|
| 2 | =DATE(2026;11;2) |
Room deposit | 0 | 400 |
| 3 | =DATE(2026;11;3) |
Advance receipts | 250 | 0 |
| 4 | =DATE(2026;11;4) |
Materials | 0 | 500 |
| 5 | =DATE(2026;11;5) |
Final receipts | 800 | 0 |
| 6 | =DATE(2026;11;6) |
Remaining room charge | 0 | 300 |
| 7 | =DATE(2026;11;7) |
Travel reimbursement | 0 | 100 |
Use explicit numeric zero for the unused side, not a blank or dash. Money out is positive because the balance formula subtracts it. Do not enter a payment as both negative money in and positive money out.
Check inputs before calculating balances
In F2 enter this formula, then copy it down through F7:
=IF(COUNT(A2;C2:D2)<>3;"CHECK INPUT";IF(OR(C2<0;D2<0);"CHECK SIGN";"OK"))
Every row should show OK. COUNT checks that the date and both amounts are numeric. It does not prove a date is plausible or a receipt is certain: manually check those assumptions and the chronological order.
In E2 enter the following and copy it down through E7:
=IF(COUNT($H$2)<>1;"CHECK OPENING";IF(COUNTIF($F$2:F2;"<>OK")>0;"CHECK INPUT";$H$2+SUM($C$2:C2)-SUM($D$2:D2)))
The opening cell and first range endpoints remain fixed. The final endpoint advances with each row. E4, for example, includes all movements in rows 2 through 4. See fixed and changing references if the dollar signs are unfamiliar.
Expected E2:E7: 200, 450, −50, 750, 450, 350. The first shortfall is 50 on 4 November, even though the schedule ends at 350. A negative amount is a planning warning, not evidence that a real payment can proceed.
Add independent summary checks
Put the following labels in G4:G7 and formulas in H4:H7:
| Label | Formula | Expected |
|---|---|---|
| Closing balance | =E7 |
350 |
| Lowest balance, including opening | =IF(COUNT(E2:E7)<>6;"CHECK INPUT";MIN($H$2;E2:E7)) |
−50 |
| Movement rows below zero | =IF(COUNT(E2:E7)<>6;"CHECK INPUT";COUNTIF(E2:E7;"<0")) |
1 |
| Independent closing check | =IF(COUNT(E2:E7)<>6;"CHECK INPUT";$H$2+SUM(C2:C7)-SUM(D2:D7)-E7) |
0 |
Total inflows are 1,050 and outflows are 1,300. Independently, 600 + 1,050 − 1,300 = 350. H7 being zero checks internal consistency, not completeness against an external bank statement. H6 counts rows, not calendar days spent below zero.
Delay a receipt without changing the final total
Save a checkpoint first. Change A5 to =DATE(2026;11;8). The rows are now out of date order, so do not interpret the balance column yet.
Select A1:D7 only, choose Data → Sort, indicate that the first row contains column labels, and sort by Date ascending. Here A:D is the complete input table; E:F are position-based calculations deliberately left in place. Check that each movement stays with its date and amounts. Because sort-related reference updating can affect formulas outside the selected input range, re-enter the documented F2 formula and fill F2:F7, then re-enter the documented E2 formula and fill E2:E7. This deliberately rebuilds the position-based checks and cumulative ranges in the new order; merely leaving E:F outside the sort is not a guarantee that references stay unchanged.
Expected balances in the newly sorted order: 200, 450, −50, −350, −450, 350. Closing cash is unchanged, but the lowest balance becomes −450 and three movement rows are below zero. The final receipt now arrives after the last two payments. Restore the checkpoint before further tests.
For same-date movements, specify an explicit transaction sequence or aggregate to an end-of-day plan. An arbitrary within-day sort can change the reported intermediate low point.
Test failure cases and preserve the plan
- Clear C3. F3 must say CHECK INPUT and E3:E7 must stop producing numeric balances. Restore numeric 250.
- Enter −500 in D4. F4 must say CHECK SIGN. Restore positive 500.
- Clear H2. Every balance must say CHECK OPENING. Restore 600.
- Change H2 to 650. The lowest balance becomes 0, the closing balance 400, and the below-zero count 0. Restore 600.
Save as a new .ods workbook, reopen it and repeat the opening-balance change yourself. Keep assumptions and original inputs alongside the plan. Adding rows requires extending the formulas, summary ranges and the expected numeric count of six together.
If totals look too low, investigate numeric text rather than overwriting a formula with the expected answer. SUM ignores text in referenced ranges. If balances no longer match row order, rebuild E2:F7 from the documented formulas after verifying the input table. Sorting safely explains the general table-selection risks.
Sources and evidence limits
Official LibreOffice Help supports SUM and its text handling, relative/absolute references, logical tests and Data Sort. The fictional schedule, delayed-receipt scenario and boundary expectations were calculated independently. Native formula evaluation, sorting, saving/reopening and other locales/platforms remain unverified for this new exercise.