📅 Published by Subham Ghadge  |  Category: Corporate Operations, Cost Optimization, Facility Auditing  |  Free Download Business Intelligence Facility Management Excel Analytics

Corporate Water Consumption & Vendor Billing System in Excel

When companies audit operational costs, everyday utilities like office drinking water deliveries often get overlooked. In large corporate offices, BPO hubs, or coworking spaces, daily water jar deliveries are usually logged by reception or security guards on paper slips.

Without a way to cross-reference delivery numbers with actual office attendance, overbilling or phantom delivery invoices can easily slip through unnoticed.

To solve this, I designed the Water Supporting & Consumption Analytics System in Excel. It compares daily water jar deliveries against employee attendance metrics (ACR), handles multi-tenant vendor pricing tiers, and compiles clean, auditor-ready monthly vendor invoices using SUMIFS formulas.

Attendance-to-Consumption Ratio (ACR) Auditing

Verifying high-volume water bills requires looking at actual staff attendance. Water consumption naturally follows human headcount on the floor.

This Excel system tracks the Attendance-to-Consumption Ratio (ACR). You log daily employee attendance alongside water jar drops. The formula divides total litres consumed by present staff count to calculate a 'Per-Employee Cost' baseline.

If your baseline averages ₹8.50 per employee per day during regular workweeks, a sudden jump to ₹15.00 during a low-attendance holiday week highlights a clear data anomaly.

If office attendance drops by 60% during a holiday period but the vendor bill claims full delivery numbers, you have exact attendance metrics to dispute the invoice—using audit principles similar to our Vendors Manpower Attendance System.

The Daily Audit Matrix. Pairs physical delivery counts with daily attendance numbers to calculate per-employee usage metrics.

Central Vendor & Statutory Tax Setup

Multi-tenant facilities often manage different water vendors at different contract rates. A coworking space might hire Vendor A for floor 2 at ₹35 per jar, while hiring Vendor B for executive areas at ₹100 per jar, while needing to apply GST taxes correctly on cross-charges.

The system manages rates on a central control sheet named 'IMP'. Here, you define company names, assigned suppliers, contracted unit rates, and statutory tax percentages (CGST/SGST).

If a vendor rate changes or GST rules update, updating the rate once in the IMP tab updates calculations across your entire historical database automatically.

The IMP Master Sheet. Controls vendor contract rates and tax rates across all tenant billing views.

Preventing Common Utility Delivery Errors

Unmonitored supply chains can run into common billing discrepancies that this workbook helps spot:

1. **Unverified Delivery Claims:** Receiving staff enter exact opening and closing jar counts daily, preventing suppliers from billing for more jars than were physically delivered. 2. **Container Deposit Disputes:** Water vendors often charge penalties for unreturned empty jars. The sheet tracks empty jar returns, giving you clear records if a vendor charges missing jar fees. 3. **Weekend Billing Errors:** Visual trend graphs make it easy to spot delivery charges entered on weekends or public holidays when the office was closed.

Continuous Inventory Carry-Forward Setup

Water deliveries involve continuous stock carry-over. Water jars delivered on Tuesday evening are often opened on Wednesday morning.

The sheet uses a continuous carry-forward setup. It takes the ending jar count from yesterday and sets it as the starting jar count for today: Closing Stock = Opening Stock + New Deliveries - Daily Usage

This automatic roll-over prevents double-counting inventory delivered across overnight shift changes.

Automated Tenant Invoice Generation with SUMIFS

Cross-charging individual tenant companies for water usage in a shared building manually takes time.

The invoice compiler tab uses `SUMIFS` formulas linked to a company selector dropdown menu on the dashboard. Selecting a tenant company name scans the daily ledger for that tenant's monthly usage totals:

=SUMIFS(Delivery_Range, Company_Range, Selected_Company, Date_Range, Active_Month)

A secondary `VLOOKUP` formula fetches the vendor rate from the IMP sheet and calculates total subtotal costs automatically.

Applying GST & Generating Printable Bills

After calculating base material costs, the sheet applies CGST and SGST percentages set in the IMP tab (such as 9% CGST and 9% SGST).

The system calculates exact tax amounts and adds them to the subtotal, creating a clean, printable invoice ready for tenant billing.

The Tenant Invoice Dashboard. Uses SUMIFS formulas to aggregate tenant consumption and calculate monthly bills automatically.

Team Workflow & Data Roles

To maintain clean data, different teams handle specific steps: - **Facility Team (9:00 AM):** Logs physical jar drop-offs and starting stock numbers into the entry sheet. - **HR Team (5:00 PM):** Inputs daily biometric staff attendance numbers. - **Finance Team (Month-End):** Reviews dashboard totals, checks the ACR metric for anomalies, and prints monthly invoices.

Protecting Sheet Logic & Cell Security

To protect calculation accuracy across team entries, background calculation sheets are password-protected.

Only designated entry cells (like 'New Deliveries' and 'Employee Count') remain unlocked for daily logging. This keeps underlying formulas safe from accidental changes while staff log daily entries.

Frequently Asked Questions

How do I handle mid-month vendor price changes?
If a vendor updates prices mid-month, close out the current billing invoice up to the price change date. Then add a new vendor entry (e.g., "VendorName_New") in the IMP tab with the updated rate for the rest of the month to keep historical numbers accurate.

Can this sheet track bulk water tanker deliveries?
Yes. Change the rate type in the IMP tab from a per-jar rate (e.g., ₹35) to a per-tanker rate (e.g., ₹1,500). Staff then enter tanker counts instead of jar counts, and all cost calculations will work normally.

Connect For Custom Business Tools

If your organization needs custom utility trackers, automated vendor billing tools, or Power BI dashboards to track operations across multiple facilities, feel free to reach out.