Oil Pump Price Calculation (OPPC) Model – an Excel Model

December 17th, 2017 No Comments   Posted in Oil Pricing Formula

Oil Pump Price Calculation (OPPC) Model – an Excel Model

  • Calibrate Model by Calculating % Gross Margin (%GM) from Pump Price Less All Costs:
  • %GM = {[PP – OPSF – TPLC * (1 – % biofuel)] / (1 + VAT2) – [(TS + PL + DE) * (1 – % biofuel) + BF + HF + DM]} / {TPLC * (1 – % biofuel)}
  • Calculate Pump Price (PP) using the % Gross Margin and Other Cost Inputs:
  • PP = TPLC * (1 – % biofuel) + [TPLC * (1 – % biofuel) * %GM + (TS + PL + DE) * (1 – % biofuel) + BF + HF + DM] * (1 + %VAT2) + OPSF

To download excel model and IOPRC 2012 report, click the following link of DOE:

  • Calculation of TPLC and PP
  • MOPS$ = Mean of Platts Singapore (imported cost of fuel)
  • FOB$ = Freight on Board in US$ = MOPS * 300,000
  • FRT$ = Ocean Freight in US$ = FOB$ * 2.00%
  • INS$ = Ocean Insurance in US$ = FOB$ * 4.00%
  • CIF$ = Cargo, Insurance & Freight in US$ = FOB$ + FRT$ + INS$
  • CIF = CIF in Pesos = CIF$ * (FOREX, P/$)
  • CD = Customs Duty = CIF * 3.00% (now zero due to ASEAN AFTA)
  • BF= Brokerage Fee = 5,300 + (CIF – 200,000) * 0.00125
  • BC = Bank Charges = CIF * 0.00125
  • AC = Arrastre Charge (gasoline) = 122 * (0.75 * 158.9868 / 1000) * 300,000
  • AC = Arrastre Charge (diesel) = 122 * (0.80 * 158.9868 / 1000) * 300,000
  • WC = Wharfage Charge (gasoline) = 36.65 * (0.75 * 158.9868 / 1000) * 300,000
  • WC = Wharfage Charge (diesel) = 36.65 * (0.80 * 158.9868 / 1000) * 300,000
  • IPF = Import Processing Fee = 1,000 per import entry
  • CDS = Customs Documentary Stamp = 256 per import entry
  • ET = Excise Tax (gasoline) = 4.35 * 158.9868 * 300,000
  • ET = Excise Tax (diesel) = 1.63 * 158.9868 * 300,000
  • LC = Landed Cost = CIF + CD + BF + BC + AC + WC + IPF + CDS + ET
  • VAT1 (on import) = 10% * Landed Cost (Nov 2005 – Jan 2000)
  •                             = 12% * Landed Cost (Feb 2006 – present)
  • TPLC (Tax Paid Landed Cost) = LC + VAT1 (imports) = LC * (1 + %VAT1)
  • TPLC (P/L) = TPLC / (300,000 * 158.9868)
  • Summary to BOC = CD + IPF + CDS + ET + VAT1
  • Summary to BOC (P/L) = Summary to BOC / (300,000 * 158.9868)
  • OCGM = Oil Company Gross Margin (P/L) = TPLC * (1 – % biofuel) * % gross margin
  • OOCC = Other Oil Company Costs (P/L) = (TS + PL + DE) * (1 – % biofuel) + BF
  • TS = Transshipment = 0.38 P/L (for oil tanker ships and barges)
  • PL = Pipeline = 0.000 P/L (for FPIC)
  • DE = depot = 0.27 P/L (gasoline)
  •                    = 0.28 P/L (diesel)
  • BF = Biofuels = 10% * (P/L of ETHANOL) = 2.63 P/L (gasoline)
  •                        =  2% * (P/L of CME Biodiesel) = 1.28 P/L (diesel)
  • HF = Hauler’s Fee (P/L) = 0.21 P/L (gasoline and diesel)
  • DM = Dealer’s Margin (P/L) = 1.72 (gasoline)
  •                                                 = 1.47 (diesel)
  • TLC = Total Local Costs (P/L) = OCGM + OOCC + HF + DM
  • VAT2 (local costs) = 10% * Total Local Cost (Nov 2005 – Jan 2006)
  •                                = 12% * Total Local Cost (Feb 2006 – present)
  • PP = TPLC * (1 – % biofuel) + [TPLC * (1 – % biofuel) * %GM + (TS + PL + DE) * (1 – % biofuel) + BF + HF + DM] * (1 + %VAT2) + OPSF

The pump price (PP) component called the oil company gross margin is given by:

  • OCGM (oil company gross margin) = TPLC * (1 – % biofuel) * %GM
  •  = fixed O&M + variable O&M + marketing expense + depreciation + profit margin

The OCGM is used to cover the fixed and variable costs of the oil company plus the marketing expenses and depreciation cost of its invested capital assets and provide profit margin that recovers its capital investments and thus determine the IRR of the investment made by the oil company:

  • (Capital Investment) = sum ( profit margin(t) * sales volume(t) / (1 + IRR)^t )
  • Thank You !!!
  • Prepared by:
  • Marcial T. Ocampo
  • TWG Member, IOPRC 2012