Description
How much of one battery's measured oil, gas and water did each of its ten wells produce this month? You enter the month's metered volumes, the factor bands, and each well's test oil rate, GOR, water cut and hours on. You read off each phase's allocation factor, every well's allocated oil, gas and water, the largest producer and the battery's uptime, with checks that the factors sit inside their bands and that each phase closes to its meter.
How to use it: On the Allocation sheet enter the days in the month, the measured oil (STB), gas (Mscf) and water (STB), and each well's test oil rate (STB/d), GOR (scf/STB), water cut and hours. Review the factors, the per-well volumes and the checks; the Summary gathers every result and verdict. The defaults are the allocation blueprint's synthetic month-30 type-well set, not field measurements.
Method: Theoretical oil is test oil rate times hours divided by 24. Theoretical gas is oil times test GOR divided by 1,000; water is oil times water cut divided by one minus water cut. Each phase's factor is its measured volume divided by its theoretical sum; each well's allocated phase is its theoretical volume times that factor. Each phase closes independently to its meter.
Example: For 30 days the wells' theoretical oil, gas and water are 49,510.5 STB, 79,983.96 Mscf and 12,509.74 STB. The measured volumes are 47,050 STB, 82,400 Mscf and 11,700 STB; the factors are approximately 0.950303, 1.03021 and 0.935271. B-4 is the largest oil producer at approximately 7,610.5 STB and 16.1754% of the total; uptime is 96.25%.
Validity
A test is assumed representative of all producing hours. Changes in choke or line conditions, stale tests and meter errors can shift volume between wells. Independent phase factors change allocated well GOR and water cut from the test, and a factor does not identify the cause of a mismatch. Enter meter volumes already corrected for inventory, fuel, flare and lease use; this workbook does not reconcile meters, account for those corrections or combine multiple tests. Invalid input and zero theoretical phase totals fail the input check; review every failing or warning check before use.
References
- American Petroleum Institute, Manual of Petroleum Measurement Standards, Chapter 20.1, Allocation Measurement.
- PetroWiki: Production allocation (allocation practice).
- The worked example uses the synthetic well tests and meter inputs of the allocation blueprint
po.bp.fpp.allocation, checked well by well with independent arithmetic. These are illustrative inputs, not published field measurements.
Inputs
| Input | Unit | Default | Name |
|---|---|---|---|
| Days in month | days | 30 | PAA_Days |
| Measured oil, measured gas, measured water | STB, Mscf, STB | 47050, 82400, 11700 | PAA_OilMeas, PAA_GasMeas, PAA_WatMeas |
| Factor warning band, factor failure band | fraction | 0.1, 0.2 | PAA_TolWarn, PAA_TolFail |
| Well tests: test oil, GOR, water cut and hours for ten wells | STB/d, scf/STB, fraction, hours | synthetic month 30 | PAA_TestOil, PAA_TestGor, PAA_TestWC, PAA_Hours |
Sheets
Guide— the workflow and how to interpret the factors and the allocated volumes.Allocation— the measured month and factor bands, the theoretical volumes, factors and uptime, the allocation results and checks, the well table of tests, theoretical and allocated volumes, and a chart of theoretical against allocated oil by well.Summary— the measured, theoretical and allocated phase totals, the factors, the largest producer, uptime, and every check under one verdict.Test Cases— the worked example and other cases, with expected and computed results.
Functions used
None: the workbook calculates with Excel's own functions.
Names (73)
| Name | Refers to | Comment |
|---|---|---|
PAA_Battery |
Allocation!$A$43:$L$52 |
Well tests, theoretical and allocated phase volumes |
PAA_CloseCheck |
Allocation!$B$33 |
Oil closure verdict |
PAA_CloseCheckNote |
Allocation!$C$33 |
Oil closure comment |
PAA_CloseTol |
Allocation!$B$38 |
Closure tolerance |
PAA_Days |
Allocation!$B$4 |
Days in month, days |
PAA_GasA |
Allocation!$J$43:$J$52 |
GasA |
PAA_GasAF |
Allocation!$B$16 |
Gas allocation factor |
PAA_GasAlloc |
Allocation!$B$20 |
Allocated gas, Mscf |
PAA_GasCheck |
Allocation!$B$31 |
gas factor verdict |
PAA_GasCheckNote |
Allocation!$C$31 |
gas factor comment |
PAA_GasCloseCheck |
Allocation!$B$34 |
gas closure verdict |
PAA_GasCloseCheckNote |
Allocation!$C$34 |
gas closure comment |
PAA_GasMeas |
Allocation!$B$6 |
Measured gas, Mscf |
PAA_GasT |
Allocation!$G$43:$G$52 |
GasT |
PAA_GasTheo |
Allocation!$B$12 |
Theoretical gas, Mscf |
PAA_Gor |
Allocation!$B$24 |
Measured GOR, scf/STB |
PAA_Hours |
Allocation!$E$43:$E$52 |
Hours |
PAA_HoursCheck |
Allocation!$B$29 |
Hours verdict |
PAA_HoursCheckNote |
Allocation!$C$29 |
Hours comment |
PAA_HrDay |
Allocation!$B$37 |
Hours per day, hr/day |
PAA_InputCheck |
Allocation!$B$28 |
Valid inputs verdict |
PAA_InputCheckNote |
Allocation!$C$28 |
Valid inputs comment |
PAA_Inputs |
Allocation!$B$4:$B$9 |
Measured volumes and factor bands |
PAA_OilA |
Allocation!$I$43:$I$52 |
OilA |
PAA_OilAF |
Allocation!$B$15 |
Oil allocation factor |
PAA_OilAlloc |
Allocation!$B$19 |
Allocated oil, STB |
PAA_OilCheck |
Allocation!$B$30 |
oil factor verdict |
PAA_OilCheckNote |
Allocation!$C$30 |
oil factor comment |
PAA_OilMeas |
Allocation!$B$5 |
Measured oil, STB |
PAA_OilT |
Allocation!$F$43:$F$52 |
OilT |
PAA_OilTheo |
Allocation!$B$11 |
Theoretical oil, STB |
PAA_Results |
Allocation!$B$11:$B$25 |
Theoretical and allocated phase results |
PAA_Share |
Allocation!$L$43:$L$52 |
Share |
PAA_TestGor |
Allocation!$C$43:$C$52 |
TestGor |
PAA_TestOil |
Allocation!$B$43:$B$52 |
TestOil |
PAA_TestWC |
Allocation!$D$43:$D$52 |
TestWC |
PAA_TolFail |
Allocation!$B$9 |
Factor failure band, % |
PAA_TolWarn |
Allocation!$B$8 |
Factor warning band, % |
PAA_Top |
Allocation!$B$22 |
Largest oil producer |
PAA_TopShare |
Allocation!$B$23 |
Largest oil share, % |
PAA_Uptime |
Allocation!$B$14 |
Battery uptime, % |
PAA_WatA |
Allocation!$K$43:$K$52 |
WatA |
PAA_WatAF |
Allocation!$B$17 |
Wat allocation factor |
PAA_WatAlloc |
Allocation!$B$21 |
Allocated water, STB |
PAA_WatCheck |
Allocation!$B$32 |
water factor verdict |
PAA_WatCheckNote |
Allocation!$C$32 |
water factor comment |
PAA_WatCloseCheck |
Allocation!$B$35 |
water closure verdict |
PAA_WatCloseCheckNote |
Allocation!$C$35 |
water closure comment |
PAA_WatMeas |
Allocation!$B$7 |
Measured water, STB |
PAA_WatT |
Allocation!$H$43:$H$52 |
WatT |
PAA_WatTheo |
Allocation!$B$13 |
Theoretical water, STB |
PAA_WaterCut |
Allocation!$B$25 |
Measured water cut, % |
PAA_Wells |
Allocation!$A$43:$A$52 |
Wells |
PAX_Checks |
Summary!$B$25:$B$32 |
Every allocation check |
PAX_GasAF |
Summary!$B$14 |
Gas factor |
PAX_GasAlloc |
Summary!$B$17 |
Allocated gas, Mscf |
PAX_GasMeas |
Summary!$B$8 |
Measured gas, Mscf |
PAX_GasTheo |
Summary!$B$11 |
Theoretical gas, Mscf |
PAX_Gor |
Summary!$B$21 |
Measured GOR, scf/STB |
PAX_OilAF |
Summary!$B$13 |
Oil factor |
PAX_OilAlloc |
Summary!$B$16 |
Allocated oil, STB |
PAX_OilMeas |
Summary!$B$7 |
Measured oil, STB |
PAX_OilTheo |
Summary!$B$10 |
Theoretical oil, STB |
PAX_Overall |
Summary!$B$4 |
Overall verdict |
PAX_OverallNote |
Summary!$C$4 |
Overall comment |
PAX_Top |
Summary!$B$23 |
Largest producer |
PAX_TopShare |
Summary!$B$19 |
Largest oil share, % |
PAX_Uptime |
Summary!$B$20 |
Uptime, % |
PAX_WatAF |
Summary!$B$15 |
Water factor |
PAX_WatAlloc |
Summary!$B$18 |
Allocated water, STB |
PAX_WatMeas |
Summary!$B$9 |
Measured water, STB |
PAX_WatTheo |
Summary!$B$12 |
Theoretical water, STB |
PAX_WaterCut |
Summary!$B$22 |
Measured water cut, % |
Details
| Opens in | Works in Excel 2016 and later: plain formulas, no add-in needed. |
| File | ProductionAllocation.xlsx, 23 KB |
| Category | Production |
| For agents and tools | Descriptor (.md) |
More Production Workbooks
Artificial Lift Selection
PO Add-in + Microsoft 365Which artificial-lift method suits a well, from a six-method screen against editable limits to first-pass ESP, gas-lift, rod-pump and plunger sizing.
Continuous Gas-Lift Design
PO Add-inHow much lift gas one well needs, how deep to inject it and where the unloading valves go, checked against port throughput and the nodal rate.
Decline Curve Analysis
PO Add-in + Microsoft 365How much oil one well will recover, from its monthly rate history through four decline fits to an EUR, a booked model and its sensitivity.
ESP System Design
PO Add-in + Microsoft 365How many stages and which motor one well's ESP needs, where it operates, and whether intake gas, cable voltage drop and drive speed are acceptable.
Field Production Profile
PO Add-in + Microsoft 365What a 24-well development will produce year by year, from a P50 type well through a drilling schedule and a buildup, plateau and decline profile.
Flow Assurance Screening
PO Add-in + Microsoft 365Whether a subsea tieback will hydrate, scale, corrode or erode, the methanol that clears its hydrates, and how high each threat rates.
Gas Well Deliverability
PO Add-in + Microsoft 365How much gas one well delivers and when it will load up, from a four-point test fit through the tubing to critical rates along a decline.
Gas-Lift Field Optimisation
PO Add-inHow to split a limited lift-gas supply across four wells for the most oil, and where the first well then injects.
Gathering System Diagram
Excel 2016+How a field's wells, chokes, manifold and separators connect, drawn from a network table and labelled with typed rates and pressures.
Inverted ESP for a Downhole Water Sink
PO Add-in + Microsoft 365Which pump, stages and drive frequency an inverted ESP needs to drain water below the contact and re-inject it, and whether that water is coning.
Nodal Analysis
PO Add-in + Microsoft 365At what rate one oil well flows, from its inflow and best-matching tubing curve, and how tubing size, skin, wellhead pressure and water cut move it.
Pipeline Design and Route Integrity
PO Add-in + Microsoft 365What size one route's gas, liquid and multiphase lines must be, and whether the bore holds against erosion, hydrates and CO2 corrosion.
Production Surveillance
PO Add-in + Microsoft 365Whether an oil well's rate, GOR, WOR or productivity index has moved off trend enough to review, beside decline, WOR, inflow and loading references.
Rod Pump System Design
PO Add-in + Microsoft 365Whether a tapered rod string, pump and pumping unit lift one well's target rate within rod stress, unit ratings and counterbalance.
Surface Facility Sizing
PO Add-in + Microsoft 365How big a separator, how much compression and which wellhead choke one well stream needs, from its gas, oil and water rates and pressures.
Type Well Builder
PO Add-in + Microsoft 365What a new well in a play will produce, as P90, P50 and P10 type curves and EURs from ten offset wells normalised by lateral length and screened for outliers.
Unconventional Rate-Transient Analysis
PO Add-in + Microsoft 365Whether a tight-oil well has reached boundary-dominated flow, how much oil it contacts and what it will recover, from its monthly rates and flowing pressures.
VFP Production and Injection Tables
PO Add-in + Microsoft 365What bottomhole pressure one vertical well needs across rate, pressure, water cut and GOR, written as VFPPROD and VFPINJ tables for a simulator.
Well Productivity Review
PO Add-in + Microsoft 365Which wells are worth stimulating, ranked by PI, skin and damage ratio, with each one's IPR uplift valued against a type curve.
Need a workbook we don't have? Suggest a Workbook




