Extracting Rate Tiers From a Tiered Utility Bill

Recording the total on a utility bill is data entry. The reason to extract the tier rows is that they let you recompute the total, and a recomputation that disagrees is either a bad extraction or a bad bill — both of which are worth knowing about, and only one of which you can fix yourself.

Tiers are marginal, and the boundary moves

A tiered tariff prices successive blocks of consumption at different rates. The first block is cheapest; each subsequent block is dearer. The rates are marginal, not selective: crossing into tier 2 does not reprice the units in tier 1. Anyone who has misread an income tax table has made the other mistake, and an extraction that stores a single effective rate per bill has made it structurally.

The part that surprises people writing the schema is that the tier boundary is not a constant. In many tariffs the first block is a baseline allowance expressed per day and multiplied by the number of days in the billing period, with the daily quantity varying by climate zone, season and whether the household has electric heating. Higher tiers are then defined as percentages of that baseline. So the boundary between tier 1 and tier 2 differs between two bills for the same address in the same year, and a schema that stores tier boundaries as fixed numbers on the tariff instead of as computed quantities on the bill will fail to reproduce the arithmetic.

Extract the tier rows as they are printed: a quantity, a unit, a rate at full printed precision, and an amount, in order, with the boundary quantities they imply. The tariff is context; the bill is evidence.

Rebuilding a bill from its tier rows

Here is a complete synthetic bill. Every rate below is invented for this example and is not any real utility’s tariff; the point is the arithmetic, not the numbers. Assume a 30-day period, usage of 905 kilowatt-hours, a baseline allowance of 11.2 kilowatt-hours per day, a tier 1 rate of $0.14000, a tier 2 rate of $0.21000, a fixed customer charge of $10.00, a per-unit public purpose rider of $0.00400 and a local utility tax of 3%.

baseline      = 11.2 kWh/day * 30 days        =  336 kWh
tier 1        = 336 kWh   * 0.14000            =  $47.04
tier 2        = (905 - 336) = 569 kWh * 0.21   = $119.49
energy charge = 47.04 + 119.49                 = $166.53
customer chg  =                                  $10.00
rider         = 905 kWh * 0.00400              =   $3.62
subtotal      = 166.53 + 10.00 + 3.62          = $180.15
tax           = 180.15 * 0.03 = 5.4045 -> round   $5.40
total         = 180.15 + 5.40                  = $185.55

That derivation gives you four independent tests against the printed document, and they fail in informative ways.

• Tier quantities sum to consumption. 336 + 569 = 905, which is the figure the meter block already validated in extracting meter readings. A shortfall here usually means a tier row was missed entirely, most often a third tier printed after a page break — see table column misalignment across pages.
• Tier amounts sum to the printed energy charge. A failure here with correct quantities means a rate was misread.
• Each tier row is internally consistent. Quantity times rate equals amount, per row. This localises the error to one line instead of the block.
• The components sum to the total. A failure here with a correct energy charge means there is a charge line you did not model at all.

Rounding, precision and the wrong tolerance

Two mistakes make a correct extraction look wrong. The first is truncating the rate. Utility rates are commonly printed to five decimal places, and $0.21000 stored as $0.21 is fine while $0.21437 stored as $0.21 is off by more than two dollars across 569 units. Capture the rate exactly as printed, as a decimal string, and do the arithmetic in a decimal type. Binary floating point on currency will also produce cent-level disagreements that look like billing errors and are not; the general treatment is in the currency amount validation rule.

The second is comparing sums with a fixed tolerance. Each printed line is rounded to the cent independently, so the sum of the rounded lines can differ from the rounded sum by up to half a cent per line. A tolerance of one cent fails spuriously on a bill with six charge lines; a tolerance of one dollar hides a real error on a small bill. Scale the tolerance with the number of lines being summed — half a cent per line, plus a cent — and the false positives disappear without the check going blind.

When one bill contains two tariffs

The structural failure specific to this document is a rate change in the middle of a billing period. When it happens, the bill splits into two rate periods, each with its own dated range, its own tier structure, its own prorated baseline and its own set of tier rows — and the same meter reading pair covering both. Consumption is allocated between them, usually by days.

A schema with a flat list of tiers on the bill cannot represent this. The extraction then does one of two things, both bad: it concatenates the two tier sets into one list, so tier 1 appears twice with different rates and the quantities sum to twice the consumption; or it takes the last block it saw and silently drops the first. Nest the tiers under a rate period with a start date, an end date and its own allocated consumption, and let a single-tariff bill be the case where there is one rate period. Seasonal tariffs make this the normal case twice a year rather than an exotic one.

Credits behave similarly. Net metering exports, a budget-billing true-up, a regulatory refund and a levelised-payment adjustment all appear as negative lines, often in parentheses rather than with a minus sign, and sometimes in a separate section that a reader skips. A reconstruction that ignores them will be short by exactly the credit, and a schema that stores amounts as unsigned will get the sign from nowhere.

Three reasons a total does not foot

When the recomputed total disagrees with the printed one, there are exactly three explanations and they are worth working in this order, because the cheapest to test is also the most likely.

1. You misread a figure. Test it by localising: which of the four checks failed, and which single line would have to change to make it pass. If one plausible digit substitution reconciles everything, that is your answer.
2. You did not model a line. Test it by comparing the residual against the printed charge lines you ignored — a minimum bill adjustment, a franchise fee, a late payment charge carried forward, a prior balance, a deposit. A residual that matches a printed line exactly is not a billing error, it is a modelling gap. This is where most first-run failures land.
3. The bill is wrong. Only after the first two. A genuine billing error is real and finding them is often the entire business reason for the pipeline, but it is the residual explanation, not the first hypothesis. The evidence you need to make the claim is the same evidence that ruled the other two out: the tier rows, the rates at printed precision, and the meter arithmetic underneath them.