📅 Published by Subham Ghadge  |  Category: Facility Management, Real Estate Analytics, Excel Systems  |  Free Download Excel Arrays Utility Billing Commercial Real Estate

Commercial Electricity & Diesel Generator (DG) Billing System in Excel

Managing electricity billing in a commercial building, IT park, or multi-tenant property can get tricky quickly. When the main power grid goes out during peak working hours, heavy-duty diesel generators (DG) kick in automatically to keep tenant offices and server rooms running.

At the end of the month, the property manager receives two separate invoices—one from the state electricity board (like MSEB) and another from the commercial diesel supplier. Dividing these huge costs fairly among 20 or 30 different tenants using manual math often leads to calculation errors or lost revenue for the building owner.

To solve this, I designed the Towal Tower Electricity & DG Billing System in Excel. It applies statutory adjustment multipliers automatically, keeps main grid and generator fuel costs separate using smart IF logic, and generates visual charts to resolve tenant billing questions instantly.

Why Basic Subtraction Fails in Commercial Sub-Metering

Simply subtracting sub-meter readings isn't enough in commercial real estate. If a tenant's sub-meter reads 100 units of consumption over a month, billing them for exactly 100 units means the property management company ends up losing money.

State electricity boards (like MSEB, BESCOM, or Tata Power) apply transmission loss percentages and line resistance corrections to the building's main primary meter before issuing the final bill. If building management doesn't apply these adjustment multipliers downstream to tenant sub-meters, total collected payments won't cover the main utility bill.

This Excel system embeds these statutory adjustment variables directly into the billing calculations. You set up rates once, and the spreadsheet applies the correct formulas automatically.

Applying Statutory Adjustment Factors

The system applies hardcoded statutory multipliers to raw sub-meter readings. For main grid electricity, background formulas multiply raw consumption numbers by the official state adjustment factor (typically 1.0068). For Diesel Generator power, which has different line loss and fuel efficiency metrics, the sheet applies a separate factor (such as 1.064).

You can set up local per-unit rates for grid power and fluctuating market rates for diesel fuel in a protected Validation tab. Updating rates on this tab updates calculations across all tenant accounts automatically.

The Billing Calculation Sheet. Multiplies raw unit usage by statutory adjustment factors before generating final billing totals.

Separating Main Grid & DG Power using IF Logic

State grid power and diesel generator power represent very different costs. Grid electricity might cost ₹18 per unit on a commercial tariff, while diesel generator power can cost ₹30 to ₹35 per unit due to fuel usage and maintenance overhead. Mixing these consumption logs together distorts billing accuracy.

The entry sheet separates daily readings into distinct 'Grid' and 'DG' columns. The system uses nested IF statements to check the column source and apply the matching financial multiplier:

=IF(Energy_Source="Grid", (Closing - Opening) * Grid_Adjustment * Grid_Rate, (Closing - Opening) * DG_Adjustment * DG_Rate)

This prevents costly mistakes like billing expensive diesel generator power at the cheaper grid electricity rate.

The Meter Reading Entry Sheet. Staff enter daily opening and closing numbers, and the sheet routes calculations automatically.

Continuous Multi-Month Logging Setup

Some older billing setups require making a new blank file on the 1st of every month, forcing staff to re-enter ending readings from the previous month. This takes extra time and increases the chance of typing errors.

This tracker uses a continuous logging structure. The closing reading on May 31st automatically serves as the opening reading for June 1st. To generate monthly invoices, background SUMIFS array formulas filter data by date range:

=SUMIFS(Consumption_Array, Date_Array, ">="&Start_Date, Date_Array, "<="&End_Date)

Selecting "June" on the dashboard isolates entries logged between June 1st and June 30th. Your historical data stays intact in one file for multi-year record-keeping.

Visual Dashboard for Resolving Tenant Disputes

Commercial tenants sometimes ask for clarification when utility bills jump during hot months. Resolving these questions quickly requires clear, visual proof. Similar to how our Panchwa Rent Income System resolves rent questions with clear tenant ledgers, this tool uses visual graphs to explain power usage clearly.

The main dashboard lets you select any office number from a dropdown list. The sheet uses VLOOKUP and MATCH formulas to fetch that tenant's 30-day consumption history and displays a clear bar chart comparing daily grid usage versus DG usage.

If a tenant asks about a spike in their monthly bill, you can show them a daily chart explaining how high-load AC units ran on generator power during a 3-day grid outage.

The Dispute Resolution Dashboard. Visual charts show daily power usage patterns to resolve tenant billing questions quickly.

Protecting Sheet Logic & Calculation Security

To protect formula accuracy, keep calculation rules and rate tables password-protected. Staff assigned to read physical sub-meters only need access to unlocked data entry columns on the daily meter sheet.

Setting cell protection properties to unlocked only for entry fields keeps your main rates and statutory multipliers safe from accidental changes.

Frequently Asked Questions

How does the file handle mid-month meter replacements?
If a sub-meter is replaced and resets to zero mid-month, you can enter a "Reset Flag" in an adjacent column. The IF formula calculates consumption from the old meter up to the swap date and adds consumption from the new meter starting from zero, combining both into a clean monthly total.

Can the spreadsheet spot broken meters early?
Yes. You can use Conditional Formatting to highlight daily readings that vary by more than 20% from a tenant's usual average. If an active office suddenly shows near-zero usage, the cell highlights red so technicians can check the meter right away.

Connect For Custom Property Tools

If your real estate group needs custom rent-roll trackers, utility dashboards, or Power BI reporting tools for multiple commercial properties, feel free to reach out.