Water balance reconciliation workbook
A quarterly water account can close even when two monthly accounts contain equal and opposite unexplained differences. This workbook keeps each period visible, checks the storage carried from one period to the next, and separates the net residual from the gross absolute residual. It gives a reporting team a bounded record to investigate rather than a balancing entry to invent.
The editable XLSX contains a blank 12-period Water balance sheet, a separate Fictional example and a detailed Read me. The matching PDF is a static, zoomable reference with one complete sheet per page. Use one copy for one unchanged physical water system. All volumes are in cubic metres, and all case records and quantities are invented.
Download the water balance reconciliation workbook (XLSX).
Download the static water balance reconciliation workbook (PDF).
Fix the system and the sequence
Start with the map that identifies the selected system, its incoming routes, outgoing routes and storage components. A treatment-and-process loop, a building and a whole facility can have different boundaries. Record which one this copy covers. Ask the authorised custodian to confirm the physical relationships using existing records. This workbook does not instruct anyone to access equipment, change routing or take new measurements.
Retain an approved schedule of the periods being reconciled. Enter its reference, the expected count, time zone and responsible reporting role. Confirm the schedule only after comparing every entered period with that external list. A count cannot show that the right periods were entered. Use consecutive whole-day windows in chronological order, with no unused row between two active rows.
US EPA’s WaterSense at Work, Section 1.2, discusses facility water balances and unaccounted water. Its November 2023 guidance provides context; this workbook’s period and stock checks are original.
Read each flow at the chosen boundary
For each period, enter opening storage, external incoming water, external discharge, other separately supported departures and closing storage. Other departures need their own evidence or an explicitly supported estimate; examples might include evaporation or water leaving in a product. The formula does not derive those quantities from equipment capacity or identify them by subtracting the other terms. An unexplained residual remains unexplained.
Use the same storage components and measurement basis at both endpoints. If a tank is added, a stock estimate is revised or the selected system changes, preserve the earlier account and route the change to the appropriate owner. Do not make an old closing number agree with a new opening number merely by overwriting one of them. The relationship has to be explained before the periods are combined.
Keep internal-circulation observations in their separate optional column. Water moving between two points inside the system has not become a new external supply. The workbook excludes that column from the physical balance. A blank there means the observation was not supplied. It does not prevent the boundary balance from being calculated when the required terms are otherwise complete, but the aggregate internal-observation total is withheld.
Give every row a period ID and an evidence-pack reference with locators for its terms. Use the method and action field to identify estimates, unresolved questions, owners and review references. The evidence pack should let a second reader find the opening and closing observations as well as the period flows. A single invoice reference rarely describes all five required quantities.
Understand both the row and carryforward checks
The row residual is opening storage plus incoming water, less discharge, other departures and closing storage. The storage-change column is closing less opening. A positive residual means the supplied terms leave more water on the incoming side; a negative residual means the outgoing and closing terms are larger. Neither sign identifies a physical route or establishes that a measured quantity is wrong.
For every later row, its start date must match the previous row’s exclusive end date. Its opening storage must exactly match the previous closing storage. A date or stock mismatch stops the aggregate calculation while otherwise valid individual row residuals remain available. This prevents a quarter from silently bridging a missing period or an unexplained stock revision.
Choose an absolute residual tolerance and explain the working precision behind it. Equality is within tolerance. That tolerance does not waive a stock carryforward mismatch, establish an acceptable leak or determine regulatory materiality. Keep the residual amount visible even when it is within tolerance. The person interpreting the evidence must decide what investigation or correction is appropriate.
Replay three months that cancel in the net
The fictional Cedar system W1 has an unchanged map MAP1 and schedule CAL1 for January through March 2026, using UTC+05:30. Each period begins at local midnight on the first day of its month and ends at local midnight on the first day of the next month. The supplied evidence packs EP1, EP2 and EP3 cover those same windows and use cubic metres throughout.
January starts with 120 m³ in storage. Incoming water is 900, discharge 650, other supported departures 80 and closing storage 250. Its residual is 120 + 900 − 650 − 80 − 250 = +40 m³. February starts with that same 250, receives 700, discharges 800, has other departures of 90 and closes at 100. Its residual is −40 m³. March starts at 100, receives 600, discharges 450, has other departures of 70 and closes at 180, leaving zero.
The complete quarter therefore has 2,200 m³ incoming, 1,900 discharged, 240 in other departures and a 60-m³ increase in storage. The endpoint check is 120 + 2,200 = 1,900 + 240 + 180. The net residual is zero, but the gross absolute residual is 40 + 40 + 0 = 80 m³. At the fictional tolerance of 1 m³, two periods still need attention.
The separate internal observations are 300, 280 and 250 m³, totalling 830. They are context for those records, not an addition to the 2,200 incoming total. The case supplies no evidence that the two residuals have a common cause. A matched quarter total cannot close actions W1 and W2 or establish that the January records are corrected by the February difference.
Change one record and observe what stops
In a copy of the example, change February opening storage from 250 to 260. Its row residual becomes −30 m³, but the carryforward check identifies the ten-cubic-metre disagreement with January’s closing storage. Aggregate outputs disappear. Ask whether the endpoint record, scope or version changed; do not distribute that ten across discharge or another term to make the sheet appear complete.
Restore the opening value, then clear February discharge. That period no longer has a complete balance and the aggregate remains withheld. A supported zero is different: it would assert that no discharge occurred and produce a very different residual. Finally, restore discharge and clear only February’s internal observation. The physical aggregate remains available, while the separate internal total disappears. These tests show which evidence each result actually depends on.
Use the supported date and quantity contract
Rows 17–28 are the full capacity. Dates are literal whole numeric spreadsheet serials between 1 January 2000 and 1 January 2100; formulas and text dates are rejected. The display shows year-month-day so the reader can verify the intended dates. The windows include their start and exclude their end, at midnight in the recorded time zone. The file does not support sub-day periods or calculate daylight-saving elapsed hours.
Volumes are nonnegative literal numbers up to 1,000,000,000 m³, with at most three decimal places. Tolerance is limited to 1,000,000 m³ at the same precision. Derived volume results are rounded to three places. Keep General base input formats and paste values rather than source formatting. Numeric formulas, booleans, text numbers, errors, negative quantities and excess precision are rejected. A date serial also looks numeric in a volume cell. A number can still carry a wrong unit or meaning; entry checks do not establish measurement validity.
Do not insert rows, change formulas or extend the capacity without redesigning and testing the dependent ranges. There are no macros, automatic unit conversions or external data connections. Preserve a clean master and verify the saved copy in your intended spreadsheet application. Cache-cleared headless LibreOffice tests cover the supplied files and mutations; desktop interaction and Microsoft Excel behavior have not been tested.
Hand over the open account
Keep the map, complete period list, endpoint stock records, flow evidence, estimate methods and action log together. Send the signed period residuals as well as the aggregate. A summary saying only “quarter balanced” would omit the most important finding in the fictional case. Any measurement investigation or physical intervention belongs with authorised technical personnel and their procedures.
The water-source inventory helps establish the routes and measurement identities before this calculation. The water-balance guide explains the wider evidence workflow, while the water-metric guide addresses the separate task of translating records into withdrawal, discharge and consumption disclosures. This workbook does not automatically supply any of those reporting metrics.
For help preparing a bounded water-record handover, contact Nitium Advisory with a non-confidential description of the selected system and missing evidence.
Version 1.0. Prepared 15 October 2026. Original Nitium Advisory workbook, diagram and fictional case. Source context checked on that date. This optional working aid provides no engineering design, water-quality finding, permit conclusion, verified saving, source endorsement or independent assurance.
Comments