Energy financial modeling: prices, costs and cash

Energy financial modeling links operating volume, realized commodity prices and costs to the cash an energy business can generate. Start with the sales, production and expense files the finance team already uses. Keep the drivers separate so a change in revenue has an explanation beyond a single percentage variance.

Define the operating business and the units

An upstream producer, fuel distributor and energy service company have different revenue drivers. Choose a business unit and a reporting period before setting assumptions. A producer may start with barrels sold, a distributor with gallons delivered and a service company with billable days; do not combine those quantities in one volume total.

This guide uses a simplified US oil-producing business and barrels sold. It addresses operating cash, while renewable energy project modeling has a separate generation, capital and debt-service job. For the general workbook structure, start with financial modeling.

The EIA explanation of oil prices describes supply, demand and disruptions as price drivers and emphasizes uncertainty in price projections. Treat a commodity price case as an assumption to test. It is not evidence that the business will receive that price.

Build an input register with product, business unit, month, scenario, quantity, unit, realized price and source reference. For actuals, retain invoice IDs and the mapping to ledger accounts. Separate quantities produced, sold and held in inventory so revenue uses the quantity that was sold.

Map the expense files before calculating

Split operating expenses into volume-sensitive and fixed amounts. Assign each cost once, with a clear rule for where corporate allocations belong. EIA defines lifting costs as costs associated with extracting a mineral reserve from a producing property; that label alone does not cover every cash requirement of the business.

For this example, the variable operating rate includes the costs management expects to move with barrels sold. Fixed operating costs are separate. Cash taxes, working capital, maintenance capital spending and interest sit in the cash bridge. No owner distributions, debt principal payments, new borrowing or asset disposals occur in the illustrative period.

Do not count a transportation deduction in both realized price and operating costs. Preserve the commercial team's price definition and write the treatment beside the input. The controller should approve the ledger mapping before the first comparison is circulated.

Build the price and cash worksheet

Put row labels in column A, the approved monthly plan in B and a lower-price case in C. Enter these assumptions in rows 2–10:

Only the selling price changes. These are illustrative company assumptions, not current commodity prices. Holding taxes and working capital constant isolates the price effect; a full forecast should replace that simplification with approved tax and collection assumptions.

Operating contribution = Sales volume × Realized price − Sales volume × Variable cost per unit − Fixed operating costs

Cash movement = Operating contribution − Cash taxes − Increase in operating working capital − Maintenance capital spending − Cash interest

Calculate the outputs in B11–B15 and copy across to C:

B11 = B2*B3
B12 = B2*B4
B13 = B11-B12-B5
B14 = B13-SUM(B6:B9)
B15 = B10+B14

Each label before the equals sign is the destination cell. The working capital input is positive when operating balances absorb cash and negative when they release it.

All amounts are exact whole dollars. The plan's cash movement is 330,000 − 30,000 − 20,000 − 60,000 − 15,000 = $205,000. A $5 reduction per barrel across 10,000 barrels reduces revenue and cash movement by $50,000 under the stated assumptions.

Explain actual revenue without an unexplained residual

Now suppose the actual sales export shows 9,500 barrels at a realized price of $68 per barrel. Actual revenue is 9,500 × 68 = $646,000. Build a separate comparison with planned volume in B2, planned price in B3, actual volume in C2 and actual price in C3.

Volume variance = (C2-B2)*B3
Price variance = C2*(C3-B3)
Revenue check = C2*C3-B2*B3-(C2-B2)*B3-C2*(C3-B3)

The proof is 646,000 − 700,000 = -$54,000, and the revenue check returns zero. Repeat the expense bridge using actual quantities and approved cost classifications before attributing the whole cash difference to prices.

Reconcile cash and assign the review

The worksheet identity is opening cash plus cash movement equals closing cash. In B16, =B15-B10-B14 must return zero. Then reconcile the closing amount to the actual bank and ledger balances, adding separately identified financing, distributions or other omitted movements where relevant.

Commercial finance owns realized prices and sales volumes. Operations owns production and cost explanations. Treasury owns payment timing and financing rows. Use a cash flow forecast for the collection calendar; infrastructure financial modeling covers long-lived asset cash and debt scenarios.

Where it goes wrong

• A benchmark substitutes for realized price. Preserve delivery terms and commercial adjustments.
• Production becomes sales automatically. Reconcile inventory and the revenue recognition period.
• All costs move with volume. Contracted labor and fixed leases need separate assumptions.
• A scenario becomes a prediction. A sensitivity holds specified inputs constant; predictive analytics answers a different question.

Compare your energy workbook in Covirage

Upload the scenario workbook and actual volume, price and expense exports, or bring a Google Sheet with the same business-unit and period fields. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the variance with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See oil and gas analytics for the supporting industry workflow.