Oil and gas financial model in Excel

An oil and gas financial model XLS workbook should connect sales volumes and realized prices to operating contribution and cash. Start with a monthly operating layout, preserve the units for each commodity and put each scenario in a separate column. This template is for a company finance team reviewing operations and cash needs.

Set up the source tabs first

Keep source exports separate from assumptions and calculated results. Use a sales tab with period, property or operating area, commodity, quantity, unit, recognized revenue and adjustments. Keep the operating expense ledger, approved capital spending schedule and working capital balances alongside it.

For each commodity, calculate the actual realized price as recognized revenue divided by matched sales volume. Keep benchmark assumptions and contractual adjustments visible when planning prices. A market quote alone does not establish what your company earned after its own pricing terms.

Name units in the headers. The EIA defines a barrel as 42 US gallons and Mcf as one thousand cubic feet. Oil barrels and gas Mcf therefore cannot simply be added. Price units must match volume units before any multiplication.

Use a production-to-sales reconciliation when produced quantities differ from deliveries or recognized sales. Keep ownership and royalty treatment consistent with the company's revenue ledger. Otherwise, a gross-volume input and a net-revenue price can produce a result that looks plausible but has no accounting tie.

Copy this operating worksheet layout

Place row labels in column A, base assumptions in B and a price downside in C. Row 1 contains headers. The example covers one month at a US operator and uses sales quantities on the same revenue basis throughout. Production costs include the example's royalties and production taxes.

Microsoft's Excel formula guidance explains references and arithmetic operators. Copy the calculated cells from B to C; relative references then use the downside inputs. Covirage is not affiliated with Microsoft.

The cash schedule is deliberately limited: there are no income tax payments, interest payments, borrowings, debt repayments or owner distributions in this example. Add those rows for a business that has them. The cash cost inputs are accrued period expenses excluding noncash charges; working capital adjustments convert them and revenue to cash. Operating contribution excludes depreciation and is a management subtotal, not net income or a complete statutory cash flow measure.

Fill the assumptions without losing the audit trail

Enter approved base prices and volume assumptions, along with an owner, approval date and source in adjacent notes. Link actual volumes and revenue to the sales export. Link production costs and overhead to the ledger, and capital cash payments to the spending schedule.

Calculate working capital movement from opening and closing operating balances. An increase in receivables or inventory uses cash; an increase in operating payables supplies cash. Keep debt and cash balances outside that operating working capital calculation.

Do not enter a positive working capital increase in one scenario and treat it as a cash inflow in another. Here the $45,000 increase reduces cash in both columns. For the distinction between accounting earnings and receipts, see cash flow versus profit.

Keep an actual column when closing the month. Replace assumptions with matched, reconciled export totals while preserving the approved base. Overwriting the original plan removes the comparison the operating review needs.

Prove the results and the scenario difference

The base revenue check is 18,000 × 68.00 + 90,000 × 2.80 = $1,476,000. Subtracting $510,000 and $140,000 leaves $826,000 of contribution. Subtract capital spending of $120,000 and the working capital increase of $45,000 to reach $661,000 of net cash movement.

Opening cash of $300,000 plus that movement equals $961,000. The following Excel check in B17 must return zero; copy it across:

=B16-B10-B15

Only the oil price changes in the downside. Its effect is 18,000 × (63.00 - 68.00) = -$90,000. Revenue, operating contribution, net cash movement and closing cash each decline by exactly that amount because all costs and other inputs stay fixed. Actual costs may respond to price, so a later scenario should change them explicitly rather than implying this assumption always holds.

All displayed dollar results are whole dollars; the calculations use the unrounded inputs. Tie actual revenue to the ledger and actual closing cash to the relevant cash accounts. A formula that balances is necessary, but it does not prove the inputs came from the correct month.

The SEC's financial statement guide explains why profit and cash differ and separates operating, investing and financing cash movements. Keep that separation when extending this operating template.

Where it goes wrong

• Mixing units. Mcf and other gas measures need matching price units; a label change does not convert a quantity.
• Using production as sales. Inventory and delivery timing can break the revenue tie even when production figures are correct.
• Double counting royalties. A price already net of a charge must not have that same charge deducted again without reconciliation.
• Treating capex as a monthly operating expense. Capital payments belong in the cash bridge; depreciation belongs in an accounting profit schedule.

For broader operating sensitivities, use energy financial modeling. The mining workbook and gas station workbook cover neighboring businesses with different volume and cost assumptions.

Compare your workbook in Covirage

Bring your prepared scenario workbook and matching sales and expense exports; Covirage maps the columns, and its tools compute and check the figures. The external AI model explains the scenario differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for oil and gas businesses for the supporting industry context.