Water Utility Rate Setting: Revenue Requirement, Tiered Tariffs & Affordability

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Add up the utility's cash needs (O&M, debt service, cash-funded capital), subtract non-rate revenue to find what rates must recover, and check the debt service coverage.
• Forecast billed volume by customer class, then use a water balance to find the non-revenue water, its share of production and the real losses from leaks.
• Convert accounts into equivalent meters, split the revenue requirement into a fixed and a volumetric part, and set a monthly service charge for each meter size plus a uniform rate per m³.
• Split each customer's monthly use into four blocks with MIN and MAX, price the blocks, add the service charge and compare the average price per m³ of small and large users.
• Price the bill tabulation at current rates, compare the revenue with next year's requirement and solve directly for the volumetric increase that Goal Seek would find, then set the new block rates.
• Calculate five typical monthly bills at current and proposed rates with SUMPRODUCT, the percentage change for each profile, and chart the bills side by side.
• Calculate the monthly bill and its share of income for five income groups, count the households above the affordability target and cost a credit that brings the lowest group down to the target.
• Model four drought stages: billed volume, volumetric revenue, the net revenue loss after saved production cost, the surcharge per m³ that restores it and how the fixed charges stabilise revenue.