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 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 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 = 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 = 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.

  • Thank You !!!
  • Prepared by:
  • Marcial T. Ocampo
  • TWG Member, IOPRC 2012

 

Estimating impact of transport fuel price increase on passenger tariff fares

March 9th, 2012 No Comments   Posted in public transport fare

Estimating impact of transport fuel price increase on passenger tariff fares

It is possible to develop a business model for each mode of transport and determine the fare (Peso per passenger km) that will allow the public transport investor a fair return on his investment given his vehicle size/capacity (passengers per trip), capacity factor (% seats occupied), travel distance per round trip, time for a round trip (hours per round trip), number of trips in a day (week days and week ends), number of operating days per year, fuel mileage (km per liter), cost of fuel (Pesos per liter), fixed operating costs (boundary, permits, fees, licenses), variable maintenance costs other than fuel (tire, radiator, brake, engine, suspension, body works), other variable operating costs (toll fees per round trip). More »