An Excel-based utility management system for commercial buildings to track electricity and diesel generator (DG) consumption across multiple offices, calculate accurate bills, and generate monthly reports.
Solves the problem: How to accurately track and bill electricity consumption for multiple offices/tenants in a commercial building, including backup generator usage during power cuts.
Key Benefits:
- Track daily electricity consumption for each office
- Monitor diesel generator usage during power outages
- Calculate accurate bills with Indian electricity factors (MSEB)
- Generate monthly consumption reports with charts
- Maintain a complete audit trail for the electricity board
Access Electricity & DG Billing System
Password for settings sheet:
123
Select month, office, and type (Electricity/DG) to view consumption charts and cost breakdowns
All offices' consumption data with MSEB adjustment factors and cost calculations
Simple entry form for daily opening and closing meter readings
Document why consumption increased or decreased each month
- Select Month: Choose any month (Jan-24 to Jul-24)
- Select Office: Pick specific office (102, 309, 310, 311, etc.)
- Select Type: Electricity or DG (Diesel Generator)
- View Charts: Daily consumption bars, cost trends, comparisons
- See Totals: Monthly consumption with adjustment factors applied
Security guards simply record:
- Opening meter reading (start of day)
- Closing meter reading (end of day)
- System automatically calculates consumption and cost
- Track 20+ offices separately (102, 309, 310, 311, 312, 313, 314, 315, etc.)
- Each office gets individual bill
- Compare consumption across offices
- Adjustment Factor for Electricity: 0.987937707 or 1.0068
- Adjustment Factor for DG: 1.064232000
- Ensures accurate billing per MSEB requirements
- Track diesel consumption during power cuts
- Separate rates for diesel (₹29.32, ₹31.26 per liter)
- Calculate DG backup costs separately
Track separately:
- Electricity: General office power
- HVAC: Air conditioning/heating
- AC: Separate AC units
- DG: Diesel generator backup
- Consumption = Closing Reading - Opening Reading
- Billable Units = Consumption × Adjustment Factor
- Cost = Billable Units × Rate
- Monthly totals automatic
Document reasons for consumption changes:
- "Increased due to 3 extra working days"
- "Summer month - higher AC usage"
- "Rate increased from ₹20.58 to ₹23.08"
- "More employees joined"
Step 1: Set Up (One-Time)
- Download the file from the link above
- Go to "Validations" sheet (Password: 123)
- Add your office numbers and current electricity rates
Step 2: Daily Entry (Security Guard - 5 Minutes)
- Go to "Daily Readings" sheet
- Enter date, office number, opening reading, closing reading
- System calculates consumption and cost automatically
Step 3: View Reports (Anytime)
- Go to "Supporting" (Dashboard) sheet
- Select month, office number, and type
- View charts and totals instantly
End of Each Month:
- Verify all daily readings are entered
- Check Raw Data sheet for calculation accuracy
- Add consumption feedback explaining any variations
- Generate bills using dashboard totals
- Backup the file for audit records
- Daily consumption table (all 31 days)
- Bar charts showing consumption by day
- Cost comparison charts (month-over-month)
- Total consumption with adjustment factors
- Monthly bill amounts for electricity and HVAC
Office: 319
Month: May-24
Type: Electricity
Total Consumption: 2,764.20 units
Adjustment Factor: 1.0068
Billable Units: 2,782.99 units
Rate: ₹20.58 per unit
Total Cost: ₹57,273.91
Month Comparison:
March-24: ₹2,697.63
April-24: ₹2,782.99
May-24: ₹2,966.94
✅ Accurate Billing: MSEB-compliant adjustment factors
✅ Time Saving: Automatic calculations reduce manual work
✅ Audit Ready: Complete daily reading history
✅ Transparency: Tenants can see daily consumption breakdown
✅ Easy Entry: Security guards just enter 2 numbers daily
✅ Multi-Tenant: Track 20+ offices separately
✅ DG Backup: Track diesel costs during power cuts
✅ Cost Analysis: Visual charts show consumption patterns
- "Validations" sheet → Add office number
- Start entering daily readings for that office
- "Validations" sheet (Password: 123)
- Change rate value
- All calculations update automatically
- "Validations" sheet → Update factor value
- New factor applies to all new calculations
Property Managers:
- Bill each tenant based on actual consumption
- Track which offices use most electricity
- Justify costs with detailed reports
Facility Managers:
- Monitor daily consumption patterns
- Identify unusual spikes (investigate issues)
- Plan for seasonal variations (summer AC costs)
Finance Teams:
- Accurate cost allocation per department
- Budget forecasting with historical data
- Variance analysis month-over-month
Audit Compliance:
- Complete daily reading records
- Documented consumption feedback
- MSEB billing factor compliance
- Excel advanced formulas (VLOOKUP, SUMIFS)
- Data validation and dropdown menus
- Dynamic dashboards with charts
- Multi-sheet data management
- Automated calculations and reporting
- Indian utility billing knowledge (MSEB)
- Audit trail documentation
- User-friendly interface design
For customization or questions:
MIT License - Free to use and modify
⚡ Simplify Your Electricity Billing - Accurate, Automated, Audit-Ready
⭐ If this helps your facility management, please star this repository!
💬 Questions? Open an issue and I'll help you get started!