Blog · Territory, capacity and quota planning · Oil and gas
Build an operating workbook around oil and gas sales volumes, realized prices, cash costs and capital spending. Separate price and volume sensitivities, then prove the change in planned cash with a reconciliation.
Oil & gas financial modeling connects sales volume, realized price and operating costs to planned cash. Keep oil and gas in separate unit calculations, combine their dollar results, and show capital spending below operating costs. A useful workbook explains which driver changed and how that change reached cash.
This example is an illustrative US producer's monthly operating plan in USD. It uses sales volumes attributable to the company's net revenue interest after the royalty share has been removed. It assumes produced and sold volumes are equal, with no inventory movement.
Use production reports to explain operational activity and sales statements to establish billed volumes and realized prices. If production and sales differ, add an inventory or timing reconciliation. Do not multiply gross operated production by a price and compare the result with revenue attributable only to the company's retained share.
The workbook measures a cash proxy after listed operating costs, production taxes and capital spending. It excludes working capital changes, debt service, interest, income taxes, hedging cash flows and financing. These require separate schedules when the planning question includes them.
For the wider workbook structure, see financial modeling. Broader company performance belongs in financial analysis of oil and gas companies, rather than this operating sensitivity.
Bring sales statement rows with month, field or asset, product, quantity, unit, realized price, revenue and ownership basis. Add operating cost records by asset and month, plus approved capital spending assumptions. Identify whether deductions are already reflected in the realized price before subtracting them anywhere else.
Keep the unit beside every quantity. The EIA glossary defines Mcf as one thousand cubic feet. It is a volume unit, so a gas price quoted per energy unit cannot be applied to Mcf without the appropriate supplied conversion.
Treat scenario prices as planning assumptions. The EIA explanation of crude oil prices identifies global supply and demand as price drivers. That does not establish what a particular asset will realize or justify presenting a planning price as a prediction.
For each assumption, record who supplied it, the period it covers and whether it is approved. Finance should not infer a production profile from a revenue total when the operating team can provide the underlying volume schedule.
The oil and gas prices below are illustrative realized prices after marketing deductions. Variable costs exclude the separately shown production taxes. Royalty share is already excluded from the volumes, so there is no additional royalty deduction in this example.
Put these product rows on a sheet named Drivers. Columns A through G are product, sales volume, unit, realized price, revenue, variable cash cost per unit and variable cash cost.
| Product | Sales volume | Unit | Realized price (USD/unit) | Revenue (USD) | Variable cost (USD/unit) | Variable cost (USD) |
|---|---|---|---|---|---|---|
| Oil | 30,000 | bbl | 70.00 | 2,100,000 | 14.00 | 420,000 |
| Gas | 120,000 | Mcf | 2.50 | 300,000 | 0.60 | 72,000 |
| Total | 2,400,000 | 492,000 |
With oil on row 2 and gas on row 3, enter these spreadsheet formulas and fill down:
E2 = B2*D2
G2 = B2*F2
E4 = SUM(E2:E3)
G4 = SUM(G2:G3)
Do not total the volume column. Its rows have different units. The dollar columns are the comparable amounts.
On a sheet named Summary, put the following labels in column A and values or formulas in column B, starting at row 2. The tax rate is a simplified supplied planning assumption, not a statement about any jurisdiction's actual tax liability.
| Row | Item | Amount (USD), except percentage |
|---|---|---|
| 2 | Revenue | 2,400,000 |
| 3 | Variable operating cash costs | 492,000 |
| 4 | Production tax assumption | 6.0% |
| 5 | Production taxes | 144,000 |
| 6 | Fixed field operating costs | 180,000 |
| 7 | General and administrative cash costs | 90,000 |
| 8 | Capital spending | 700,000 |
| 9 | Planned cash proxy | 794,000 |
B2 = Drivers!E4
B3 = Drivers!G4
B5 = B2*B4
B9 = B2-B3-B5-B6-B7-B8
Planned cash proxy = Revenue - Variable operating costs - Production taxes - Fixed field costs - Administrative costs - Capital spending
The result is $794,000 before the excluded cash items. It is not net income or a complete cash flow statement. Display dollar results to whole dollars and assumptions to the shown precision; retain unrounded inputs for calculations.
First reduce only the oil price from $70.00 to $65.00 per barrel. Then restore the base price and reduce only oil volume from 30,000 to 27,000 barrels. Gas assumptions, unit costs, fixed costs and capital spending remain unchanged.
| Measure | Base (USD) | Lower oil price (USD) | Lower oil volume (USD) |
|---|---|---|---|
| Revenue | 2,400,000 | 2,250,000 | 2,190,000 |
| Variable operating costs | 492,000 | 492,000 | 450,000 |
| Production taxes | 144,000 | 135,000 | 131,400 |
| Fixed field costs | 180,000 | 180,000 | 180,000 |
| Administrative costs | 90,000 | 90,000 | 90,000 |
| Capital spending | 700,000 | 700,000 | 700,000 |
| Planned cash proxy | 794,000 | 653,000 | 638,600 |
| Change from base | 0 | -141,000 | -155,400 |
The lower-price case loses $150,000 of revenue but saves $9,000 of production taxes, leaving a $141,000 cash reduction. The volume case loses $210,000 of revenue, saves $42,000 of variable costs and saves $12,600 of taxes, leaving a $155,400 reduction.
Those bridges follow directly from the differences between table rows. They explain why equal revenue changes would not necessarily have equal cash effects. For a walkthrough of the spreadsheet layout, use the oil and gas financial workbook guide; oil and gas financial ratios covers measures used after the schedules are built.
Tie product revenue to the sales statements and the recorded revenue schedule. Tie operating costs to asset-level ledger totals and capital spending to the project payment schedule. Confirm that the production and sales reports use the same ownership basis before comparing their quantities.
For each scenario, revenue less every displayed deduction must equal the cash proxy. The change in that cash result must also equal the sum of the revenue change and all cost savings or increases. A bridge with a residual needs investigation before the operating review.
Bring your prepared volume, price, cost and capital spending workbook to Covirage; its tools map the columns, compute and check the supplied figures, and 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; account creation takes about a minute.
See Covirage for oil and gas for the supporting industry context.
Calculate revenue in each product's native units and add the dollar results. Barrels and thousand cubic feet cannot be summed directly. If management also needs an equivalent-volume measure, document its conversion basis and keep it separate from the revenue calculation.
Use the price that matches the sales contract and volume basis in your workbook. A benchmark can be an assumption input, but location, quality or contract deductions may require a separate bridge before it becomes the realized price used for revenue.
No. This workbook uses production and sales assumptions supplied by the responsible operating teams. It compares their financial effects without estimating reserves, valuing securities or recommending tax structures. Reserve estimates and specialist technical judgments remain separate inputs.