Blog · Board and management reporting · Manufacturing
Build an energy workbook from supplied meter readings, utility invoices and production records. Compare site usage, total utility cost and cost per unit of output, with checks that expose missing periods and duplicated production.
An energy dashboard in Excel compares supplied meter readings, utility invoices and production output by site. Its most useful comparison is often cost per unit of output, supported by usage per unit and the total bill, so a lower invoice is not mistaken for better efficiency.
This layout is a monthly workbook for a US manufacturer. It uses files provided by the business, with a separate check for each source total.
Choose the sites, utilities and reporting period before selecting charts. This example covers purchased electricity at two plants for September. Electricity consumed and production output must refer to the same dates and the same operating boundary.
The EIA explains that electricity energy is measured in kilowatthours, while watts measure power. Keep kWh usage separate from any kW demand field on an invoice. They answer different questions and cannot be added.
Use four tabs: Usage, Output, Plan and Dashboard. Store supplied readings and bills in Usage, production quantities in Output, approved targets in Plan, and formulas in Dashboard. Keep the source filename and reference beside every imported record so a reviewer can find the supporting evidence.
The neighboring energy dashboard examples page covers presentation choices. This workbook supplies the reconciled site figures those charts should display.
In Usage, use A for reporting month, B for site, C for meter ID, D for opening reading, E for closing reading, F for multiplier, G for usage and H for allocated invoice cost. A meter replacement or reset needs its own reconciled adjustment rather than a negative consumption figure.
| Site | Meter | Opening reading | Closing reading | Multiplier | Usage (kWh) | Invoice cost (USD) |
|---|---|---|---|---|---|---|
| Ohio | OH-01 | 100,000 | 220,000 | 1 | 120,000 | 15,600 |
| Texas | TX-01 | 500,000 | 580,000 | 1 | 80,000 | 9,600 |
| Total | 200,000 | 25,200 |
The usage formula in G2 is:
=(E2-D2)*F2
Only use that formula when the supplied readings and multiplier describe the meter correctly. Where the utility supplies billed kWh, reconcile the calculated usage to that figure and investigate a difference before publishing.
Put production in a separate Output table with one row per site and period. Repeating site production on each meter row multiplies the denominator when a plant has several meters. In this example, Ohio made 30,000 units and Texas made 20,000 units. Both sites produce a comparable finished unit; otherwise show their intensities separately or use an approved equivalent-output definition.
The approved Plan contains these matching quantities:
| Site | Planned usage (kWh) | Planned invoice cost (USD) | Planned output (units) |
|---|---|---|---|
| Ohio | 110,000 | 13,200 | 30,000 |
| Texas | 90,000 | 10,800 | 25,000 |
| Total | 200,000 | 24,000 | 55,000 |
On Dashboard, put site in A, actual usage in B, actual cost in C and actual output in D. Put the selected reporting month in L1. For the site in A2, usage and cost are:
=SUMIFS(Usage!$G$2:$G$5000,Usage!$B$2:$B$5000,$A2,Usage!$A$2:$A$5000,$L$1)
=SUMIFS(Usage!$H$2:$H$5000,Usage!$B$2:$B$5000,$A2,Usage!$A$2:$A$5000,$L$1)
Microsoft documents SUMIFS as summing cells that satisfy multiple criteria. Use the same site and month criteria against Output and Plan. Covirage is not affiliated with Microsoft.
Put kWh per unit in E and cost per unit in F:
=IF(D2=0,NA(),B2/D2)
=IF(D2=0,NA(),C2/D2)
A zero-output period should stay visible as an exception. Hiding the error with a zero would imply production used no energy, even when the plant incurred standby usage or fixed charges.
| Site | Actual usage (kWh) | Actual cost (USD) | Actual output (units) | Actual kWh/unit | Actual cost/unit (USD) | Planned cost/unit (USD) |
|---|---|---|---|---|---|---|
| Ohio | 120,000 | 15,600 | 30,000 | 4.000 | 0.520 | 0.440 |
| Texas | 80,000 | 9,600 | 20,000 | 4.000 | 0.480 | 0.432 |
| Total | 200,000 | 25,200 | 50,000 | 4.000 | 0.504 | 0.436 |
Totals divide total usage or cost by total output. For example, total cost per unit is =SUM(C2:C3)/SUM(D2:D3). The displayed ratios are rounded to three decimal places; calculate variances from the unrounded figures.
Total usage is exactly on the 200,000 kWh plan. Actual cost is $25,200 against a $24,000 budget, a 5.0% increase. Yet output is only 50,000 units against 55,000 planned, so usage per unit rises from 3.636 to 4.000 kWh, or 10.0% using unrounded values.
Cost per unit rises from $0.436 to $0.504, a 15.5% increase before display rounding. Ohio's blended invoice cost is $15,600 / 120,000 = $0.13 per kWh; Texas is $9,600 / 80,000 = $0.12. These are whole-invoice ratios, so inspect charge components before calling the difference a tariff change.
Operations owns the output definition and unusual usage explanations. Finance owns invoice reconciliation and the planned cost baseline. Together they should check whether lower production, product mix, operating hours or billing components explain the movement. The neighboring energy analytics dashboard covers the broader review context.
Before release, confirm that site usage sums to the supplied meter or utility report, costs sum to the selected invoices and output sums to the production report. Record unmatched invoices or partial billing periods visibly. Use the business spreadsheet data quality checks before interpreting the chart.
Bring a sample of your own meter, utility invoice and production workbook: Covirage maps the columns, and its tools compute and check the supplied figures. The external AI model explains the site comparison with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.
See Covirage for industrial manufacturers for the supporting operating context.
Start with electricity usage, utility cost, production output, usage per unit and cost per unit. Show actual and planned values for the same site and period. Add the billing-period dates so readers can see whether the comparison uses matching coverage.
It is a blended cost per kWh when the numerator includes the whole invoice. Demand charges, fixed charges and other items can affect it. Label the measure accordingly, and separate invoice components when the team needs to explain the bill.
Keep the original utility units separate in this workbook unless you have an approved conversion basis. You can add their dollar costs when periods and boundaries match. A combined physical energy measure needs consistent conversions and a clear definition.