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

Managing utility billing across a commercial building, IT business park or multi-tenant property presents complex accounting challenges. When public grid power fails during high-load business hours, heavy-duty diesel generators (DG) engage automatically to support tenant lighting, server infrastructure and air-conditioning plants.

At the end of each billing cycle, property managers receive two separate utility bills: one from the regional electricity utility board and another from bulk commercial fuel suppliers. Dividing these massive energy costs across twenty or thirty independent commercial tenants using manual arithmetic often produces calculation errors, tenant disputes and unrecovered utility expenses for the landlord.

To eliminate these financial leaks, we built the Commercial Electricity & DG Billing System in Microsoft Excel. This model applies statutory line-loss adjustment factors automatically, routes grid and generator consumption through structured conditional logic and builds visual variance charts to resolve tenant billing inquiries instantly.

Why Basic Subtraction Fails in Commercial Sub-Metering

In commercial properties, calculating utility bills by simply subtracting an opening meter reading from a closing reading causes steady financial losses. If a tenant's internal sub-meter logs 1,000 kilowatt-hours (kWh) of consumption, invoicing them for exactly 1,000 units leaves the building management company paying for utility shortfalls out of pocket.

Public electricity boards (such as state distribution companies and private utility grids) apply transmission loss percentages, power factor surcharges and transformer step-down corrections to the building's bulk primary meter before issuing monthly invoices. If property managers fail to distribute these statutory adjustment multipliers across tenant sub-meters, collected revenues will never match the master utility invoice.

Dual-Tariff Utility Billing & Statutory Loss Matrix

The system embeds statutory transmission multipliers and distinct unit rates directly into daily calculations. Grid electricity and diesel generator power represent drastically different cost profiles that must be calculated under separate formulas.

Table 1: Dual-tariff structure and statutory loss multipliers applied across tenant utility sub-meters.

You manage these base rates and regulatory factors inside a protected Validation sheet. When commercial electricity tariffs or diesel market rates fluctuate, updating numbers on this single tab recalculates invoices across every tenant ledger automatically.

The Billing Calculation Sheet. Multiplies raw sub-meter units by statutory adjustment factors before generating final billing totals.

Separating Main Grid & Generator Power using IF Logic

Grid power costs significantly less than diesel generation. While municipal electricity might cost ₹14 to ₹18 per unit, running diesel generators during long grid cuts can cost ₹28 to ₹35 per unit due to fuel burn rates and engine servicing intervals. Mixing these two consumption streams into a single average rate distorts tenant billing.

The daily recording sheet separates meter readings into dedicated Grid and DG columns. The workbook routes calculations using conditional logic:

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

This logic prevents costly operational errors, guaranteeing that energy consumed during power cuts is recovered at true generator operational costs.

The Meter Reading Entry Sheet. Operations staff enter daily opening and closing meter units and the sheet routes calculations automatically.

Automated Anomaly Detection & Broken Meter Alerts

In large facilities, sub-meters occasionally suffer mechanical failures, digital drift or loose current transformers (CTs). Identifying faulty hardware before month-end prevents prolonged tenant billing disputes.

Table 2: Sub-meter variance thresholds used to flag hardware faults and after-hours energy waste.

Continuous Multi-Month Logging using SUMIFS

Older property management setups require creating a new spreadsheet on the first of every month, forcing administrative staff to copy ending readings from the previous month manually. This redundant step takes hours and introduces transcription typos.

This system uses a continuous timeline architecture. The closing meter number from the final day of May serves as the opening reading for June first automatically. To produce monthly tenant statements, the workbook uses the official Microsoft Support SUMIFS function:

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

Choosing "June" on the summary dashboard isolates entries recorded between June 1st and June 30th instantly, keeping your multi-year utility archive preserved in a single searchable file.

Visual Dashboard for Resolving Tenant Inquiries

Corporate tenants frequently request clarification when monthly utility bills rise during summer months. Resolving these questions quickly requires transparent, visual evidence.

The executive dashboard allows you to select any suite number from a dropdown menu. The model pulls that tenant's thirty-day history and renders an automated comparison chart showing daily grid consumption alongside diesel generator run hours.

When an office manager questions an increase in their bill, you can present a daily chart showing that high-demand air handling units operated on diesel generator power during an extended public grid outage.

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

Connecting Energy Billing with Facility & Water Operations

Commercial utility management works best when power sub-metering connects with water logistics and workplace consumption records. To apportion water tanker shipments and plumbing sub-meters across tenants, pair this electricity model with our Water Consumption Tracking & Billing System. For facility pantry supplies and outsourced property personnel, explore our operational frameworks for Corporate Office Consumption Reporting and Vendors Manpower Attendance & Salary Tracking to keep your total property overhead completely reconciled.

Frequently Asked Questions

How does the system calculate mid-month meter replacements?
If a sub-meter fails and is replaced mid-month, enter a "Reset Flag" in the adjacent notes column. The underlying formula calculates units consumed on the old meter up to the replacement date, adds units logged on the new meter from zero and combines both into an accurate monthly total.

Can we adjust the statutory line-loss factor for different utilities?
Yes. You can edit the regulatory factor inside the protected Validation tab. All tenant ledgers will update historical calculations based on the revised utility percentage.

Does this workbook work in Google Sheets?
Yes. Because the model uses standard formulas like SUMIFS, INDEX/MATCH and IF logic without Windows-only VBA macros, it operates smoothly across Microsoft Excel and Google Sheets.

If your building uses peak-hour time-of-day tariffs or multi-generator load sharing in Excel, leave a comment below and I will help you adjust the conditional billing rules.