Blog · Territory, capacity and quota planning · Finance and FP&A teams
Build a storage operating workbook from supplied capacity, utilization, prices and costs. Compare the plan with actual revenue and cash, with checks that keep operating assumptions separate from settlement evidence.
An energy storage financial model in Excel compares supplied operating assumptions with actual revenue, costs and cash. Build it around a project-period table, preserve the source of every input, and make the cash bridge reconcile before using the result in a planning meeting.
The workbook below is an operating comparison for a US storage project. It takes capacity, utilization and pricing assumptions as inputs; it does not choose dispatch schedules or trading positions.
Use an Assumptions tab for the approved plan, an Actuals tab for settlement and expense records, and a Comparison tab for the calculation. Keep project ID, period, version, unit, source reference and owner alongside the amounts. A changed assumption should create a clearly labeled version rather than overwrite the approved baseline.
Keep power and energy units distinct. The EIA distinguishes power capacity in MW from energy capacity in MWh. The example uses an energy-capacity input, so multiplying it by equivalent full cycles produces an energy quantity. Multiplying a power rating directly by cycles would leave out duration.
For a broader generation project, use the neighboring renewable energy financial model workflow. Here the question is narrower: did supplied storage activity and prices produce the expected operating contribution and cash?
Put the following lines in column A, plan in B and actual in C. Column D is actual minus plan. All amounts are USD unless a different unit is shown. Costs are entered as positive amounts and subtracted in the formulas.
| Row | Line | Plan, column B | Actual, column C | Plan formula or source |
|---|---|---|---|---|
| 2 | Energy capacity (MWh) | 40 | 40 | Approved capacity schedule |
| 3 | Equivalent full cycles | 250 | 240 | Operating schedule |
| 4 | Usable-capacity factor | 95% | 94% | Supplied capacity assumption or observation |
| 5 | Discharged energy (MWh) | 9,500 | 9,024 | =B2*B3*B4 |
| 6 | Realized energy price (USD/MWh) | 100 | 95 | Supplied settlement price |
| 7 | Energy revenue (USD) | 950,000 | 857,280 | =B5*B6 |
| 8 | Other contracted revenue (USD) | 180,000 | 180,000 | Contract or settlement schedule |
| 9 | Total revenue (USD) | 1,130,000 | 1,037,280 | =B7+B8 |
| 10 | Charging cost (USD) | 300,000 | 315,000 | Purchased MWh × charging price |
| 11 | Operating expenses (USD) | 240,000 | 252,000 | Expense schedule |
| 12 | Operating contribution (USD) | 590,000 | 470,280 | =B9-B10-B11 |
| 13 | Opening cash (USD) | 500,000 | 500,000 | Cash schedule |
| 14 | Capital spending (USD) | 100,000 | 120,000 | Capital spending schedule |
| 15 | Increase in working capital (USD) | 20,000 | 25,000 | Working capital schedule |
| 16 | Closing cash (USD) | 970,000 | 825,280 | =B13+B12-B14-B15 |
The actual discharge quantity comes from the supplied operating records. In this illustrative case, capacity times observed cycles times the usable-capacity factor also equals that quantity. Do not force an actual meter total to equal an assumption-based estimate when the records differ.
The usable-capacity factor is distinct from charging efficiency. Use purchased electricity for charging cost, not discharged electricity. The supplied plan assumes 76% delivered-to-purchased efficiency; actual records show 80.21%, rounded to two decimals. Both periods assume unchanged stored energy between opening and closing, with matching measurement boundaries.
| Charging input | Plan | Actual |
|---|---|---|
| Purchased electricity (MWh) | 12,500 | 11,250 |
| Charging price (USD/MWh) | 24 | 28 |
| Delivered / purchased electricity | 76.00% | 80.21% |
| Charging cost (USD) | 300,000 | 315,000 |
Charging cost is 12,500 × $24 = $300,000 in plan and 11,250 × $28 = $315,000 actual. The efficiency check divides the discharge quantities above by purchased MWh. Do not deduct these losses again from the usable-capacity factor.
Keep one Actuals row per project, reporting period and settlement or expense line. For example, use A for project ID, B for period, C for line type and D for amount. A total-revenue formula for the selected project in F2 and period in F3 is:
=SUMIFS(Actuals!$D$2:$D$5000,Actuals!$A$2:$A$5000,$F$2,Actuals!$B$2:$B$5000,$F$3,Actuals!$C$2:$C$5000,"Revenue")
Microsoft documents SUMIFS as adding cells that meet multiple criteria. Use explicit line labels so a charging expense cannot enter revenue. Expand the ranges when the export grows, and check that every imported row falls within them. Covirage is not affiliated with Microsoft.
If the workbook spans several related tables, the existing Power Pivot guide explains that separate spreadsheet approach. Whatever aggregation method you use, its result must tie back to the supplied files.
The plan discharge calculation is 40 × 250 × 95% = 9,500 MWh. At $100 per MWh, energy revenue is $950,000. Add $180,000 of contracted revenue and subtract $300,000 of charging cost and $240,000 of operating expenses to reach $590,000 of operating contribution.
Actual contribution is $470,280, or $119,720 below plan. The reconciliation is visible in the table: lower total revenue, higher charging cost and higher operating expense all flow through the same subtraction. Put =C12-B12 in D12; do not type the variance manually.
For this simplified cash bridge, there are no financing, tax or other cash movements. The working capital schedule captures the difference between operating accruals and operating cash. If those excluded movements exist in your records, add separate lines before comparing closing cash with the bank reconciliation.
Actual cash checks as $500,000 + $470,280 − $120,000 − $25,000 = $825,280. The cash schedule must independently confirm that amount. Dollar figures are exact in this example; retain full precision when prices or quantities include decimals.
Bring a sample of your own storage operating workbook and settlement or expense files: Covirage maps the columns, and its tools compute and check the supplied figures. The external AI model explains the 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 finance teams for the supporting finance context.
Use supplied energy capacity, equivalent cycles, usable-capacity assumptions, realized prices, contracted revenue, charging costs and operating expenses. Add capital spending, working capital movements and opening cash if the workbook includes a cash bridge. Keep the source and approval date beside each assumption.
This workbook compares business assumptions and actual results. It does not determine when to charge or discharge. Use operating schedules and settlement files supplied by the responsible team, and keep any operational optimization outside the finance comparison.
Use the approved usable-capacity assumption for each forecast period and record what it includes. Do not apply a second degradation deduction to a capacity figure already reduced for degradation. Actual delivered energy should come from the supplied operating or settlement records.