# NWRB Reporting — Implemented and Deferred

Status of this system against the National Water Resources Board (NWRB) reporting
obligations for CPC / CPCN grantees.

Last updated: 2026-08-03

---

## 1. What NWRB requires

| Obligation | Detail |
|---|---|
| **Annual Report** | Financial condition + operational data for the previous year. Due **on or before May 31**, in at least two copies. Must be **audited by an external auditor and sworn before a notary public**. Filed on NWRB's MS Excel template with the Audit Certificate and Affidavit of Manager. |
| **Section 18 — Financial and Technical Data Sheet** | Prepared **monthly** (or quarterly) to accumulate year-to-date figures; only the year-end sheet is attached to the Annual Report. It is the basis for NWRB's **KPIs and industry benchmarking**, and feeds tariff formulation. |
| **Supervision and Regulation Fee (SRF)** | Billed annually 1 June – 30 September, due **30 September**. Based on actual property and equipment **or** cost of capital stock, whichever is higher. 2%/month surcharge on unpaid balances; non-payment blocks CPC renewal. |
| **Rate increase / CPC renewal** | Latest audited FS, actual balance sheet and income statement for water operations, list of assets in service, 5-year projected income statement / funds flow / balance sheet, latest annual report, comparative revenue at present vs. proposed rates, potability certificate. |

Section 18 requires **active service connections** ("connections presently or
regularly billed, whether metered or flat rate") classified into **residential,
commercial/industrial and public taps**, with **monthly and year-to-date columns**
for every figure, plus water production data.

---

## 2. Implemented (2026-08-03)

### Reports → NWRB Data Sheet (`/reports/nwrb`)
Line items down, 12 months across, YTD column. Roles: **super admin, company admin**.
CSV export and print. Produces:

- Active service connections at each month end, split residential /
  commercial-industrial / public tap / unclassified, plus total
- New connections, disconnections and reconnections during each month
- Water produced, water purchased (bulk), total supply
- Water billed / sold
- **Non-revenue water** and **NRW rate**
- Bills issued, amount billed, amount collected, **collection efficiency**
- Accounts receivable at month end
- Average consumption per connection

### Water Production capture (`/water-productions`)
New `water_productions` table (`company_id`, `source_name`, `period`,
`production_volume`, `purchased_volume`, `remarks`, `recorded_by`) with full CRUD.
One row per company + source + month, enforced by a unique index. The index screen
shows NRW per month as production is entered. Roles: **super admin, company admin**.

### NWRB customer classification
`tier_groups.nwrb_classification` (`residential`, `commercial_industrial`,
`public_tap`, or null = unclassified), settable on the tier group create/edit forms.
Clients inherit it from the tier group they are already assigned to. Existing groups
were auto-classified from their names by the migration.

### Operational reports (2026-08-02)
Monthly financial, usage per account, payments per account, unpaid + aging, and
monthly disconnection — see `/reports`. Roles: super admin, company admin, cashier.

**Migration:** `2026_08_03_000001_add_nwrb_reporting_support.php`

### Honest limits of what is produced
- NRW is only computed for months where production was actually recorded. Months
  without it are left **blank**, never zero or negative, and are excluded from the
  YTD NRW figure — so the YTD rate is never a full year of billing divided by a
  partial year of production. The report warns when months are missing.
- **Collection efficiency** is collected ÷ billed *within the same month*.
  Collections often settle earlier months' bills, so it is an indicator, not a
  per-bill settlement figure.
- **Accounts receivable** is reconstructed as billed-to-date minus collected-to-date.
  No historical month-end snapshot is stored, so restating a closed month after
  back-dated edits will change the figure.

---

## 3. Deferred — future implementation

Ordered by how much they block an actual NWRB filing.

### 3.1 Accounting data (blocks the Annual Report; largest gap)
The Annual Report needs an audited **income statement** and **balance sheet** for
water operations. This system records revenue only — there is no general ledger,
no chart of accounts, no expense capture, no depreciation.

- **Recommendation:** do *not* build a GL here. Export billed/collected figures to
  the company's accountant and let the audited statements come back from them.
- **If it must be in-system:** minimum viable scope is an expense table with an
  NWRB-aligned chart of accounts (source of supply, pumping, treatment,
  transmission & distribution, customer accounts, administrative & general,
  depreciation, taxes), plus a period-close so figures stop moving after filing.

### 3.2 Assets in service / capital stock (blocks SRF and rate cases)
The SRF is assessed on property and equipment **or** cost of capital stock,
whichever is higher, and rate applications need a "list of existing assets in
service subject to return". Nothing in this system tracks fixed assets.

- **Scope:** an asset register (description, acquisition date, cost, useful life,
  accumulated depreciation, in-service flag) and a report totalling net book value
  at year end.

### 3.3 Metered vs. flat-rate connection flag
Section 18 counts active connections "whether metered or flat rate", and NWRB
benchmarks the split. There is no flag on `clients`; every connection is currently
assumed metered because bills derive from meter readings.

- **Scope:** `clients.is_metered` boolean (default true), exposed on the client
  form, and a split row on the NWRB data sheet. Small change — do this next.

### 3.4 Connection-count accuracy
Active connections are currently counted from `clients.registered_at` (falling back
to `created_at`) and are not reduced when an account is permanently closed —
`is_disconnected` marks a service cut-off, not a terminated account.

- **Scope:** distinguish *temporarily disconnected* from *permanently closed*
  (e.g. a `closed_at` date), and count active connections as
  registered ≤ month end AND (not closed OR closed after month end).

### 3.5 Water quality / potability
Rate and renewal applications require a current certificate of potability with
bacteriological, physical and chemical test results. Not tracked.

- **Scope:** a small document/test-result record per company with an expiry date
  and a dashboard reminder.

### 3.6 Five-year tariff model
Rate increase applications need projected income statements, funds flow and
balance sheets for five years plus comparative revenue at present vs. proposed
rates. NWRB publishes its own Excel tariff model.

- **Recommendation:** keep using NWRB's model. The useful addition here is an
  export of historical billed volume and revenue **by tariff class and consumption
  bracket**, which is the input the model needs and which this system *can*
  produce from `billings` + `tiers`.

### 3.7 Filing workflow
No tracking of whether the Annual Report was filed, when, by whom, or when the SRF
was paid. Given the 2%/month surcharge and the CPC-renewal block, a simple
compliance calendar with reminders (Annual Report → May 31, SRF → Sept 30) would
be cheap and valuable.

---

## 4. Verification note

The exact field list of NWRB's Annual Report Excel template and the MED-22-r3 Water
Utility Operation Report form could **not** be confirmed — nwrb.gov.ph returns HTTP
403 to this server, so the template was never opened. The line items above come
from NWRB's published descriptions of those forms. **Before the first filing,
download the current template from nwrb.gov.ph and reconcile it against the
`/reports/nwrb` output**; column names and any additional line items should be
matched exactly.

### Sources
- CPC Annual Report — https://nwrb.gov.ph/index.php/products-and-services/cpc-annual-report
- Water Utilities Division FAQ — https://nwrb.gov.ph/water-utilities-division-frequently-ask-questions/
- Downloadable Forms — https://nwrb.gov.ph/downloadable-forms/
- NWRB MED-22-r3 Water Utility Operation Report Form
- Increase in Rates Application Form — https://formsphilippines.com/viewform/1771/increase-in-rates-application-form