Subscribe

Oil Pump Price Model – Excel Spreadsheet

September 14th, 2012 Posted in Oil Pricing Formula

Oil Pump Price Model – Excel Spreadsheet

Calculation model for the following products:

  98 Octane – Super Premium
  95 Octane – Premium
  93 Octane – Unleaded
  87 Octane – Regular / Naphtha
  Avgas
  Kerosene
  Avturbo
  Low Sulfur Diesel
  Regular Diesel
  Low Sulfur Fuel Oil
  Bunker C Fuel Oil
  Lubricating Oils
  LPG – Cooking
  LPG – Power / Automotive

 

Click the link below to order, remit payment via PayPal, then download in one easy step.

http://energydataexpert.com/product_info.php?products_id=184?osCsid=df4ccf5d82c62463a57b8655a7eaafb5

Use the model to estimate absolute pump price, and if you have two period 1 (previous) and period 2 (current) events, the model will calculate the predicted oil pump price adjustments.

Following are sample inputs and outputs:

Oil Pump Price Model – Gasoline      
1) DPLC Calculator – Gasoline        
As of Jan-Jun 2012 average Period 1 Period 2   Delta (2 – 1)
Parcel Size, Bbl 50,000 50,000    
Liters per Bbl 158.9868 158.9868    
Total Volume, L 7,949,340 7,949,340    
Density, kg/L 0.75 0.75    
Total Metric Tons (1000 kg) 5,962 5,962    
MOPS, $/Bbl 145.000 145.000    
FOB, $ 7,250,000 7,250,000    
Freight, % FOB 2.00% 2.00%    
Insurance, % FOB 4.00% 4.00%    
CIF, $ 7,685,000 7,685,000    
Exchange Rate, PhP/$ 41.00 41.00    
CIF, PhP 315,085,000 315,085,000    
Customs Duty, % of CIF 0.00% 0.00%    
Customs Duty, PhP 0 0    
Special Duty, PhP/L 0.00 0.00 5,300  
Special Duty, PhP 0 0 200,000  
Brokerage Fee, PhP 398,906 398,906 0.00125  
Bank Charge, PhP 393,856 393,856 0.00125  
Arrastre Charge, PhP 727,365 727,365 122.00  
Wharfage Charge, PhP 218,507 218,507 36.65  
Import Processing Fee, PhP 1,000 1,000    
Customs Doc Stamps, PhP 256 256    
Excise Tax, PhP/L 4.35 4.35    
Excise Tax, PhP 34,579,629 34,579,629    
Landed Cost (LC), PhP 351,404,520 351,404,520    
VAT1 on Importation, % LC 12% 12%    
VAT1 on Importation, PhP 42,168,542 42,168,542    
Duty Paid Landed Cost, PhP 393,573,062 393,573,062    
Duty Paid Landed Cost, PhP/L 49.5102 49.5102   0.0000
         
2) Pump Price Model –   Gasoline        
As of Jan-Jun 2012 average Period 1 Period 2   Delta (2 – 1)
  % Pure Oil 90.00% 90.00%    
DPLC, PhP/L 44.5591 44.5591    
Oil Company   Gross Margin, % of DPLC 17.00% 17.00%    
Oil Company   Gross Margin, PhP/L 7.5751 7.5751    
Refining   Cost, PhP/L 0.0000 0.0000 0.0000  
Transshipment,   PhP/L 0.4707 0.4707 0.5230  
Pipeline   Cost, PhP/L 0.0000 0.0000 0.0000  
Depot   Operation, PhP/L 0.2805 0.2805 0.3117  
Pure Biofuel   Cost, PhP/L biofuel 37.7897 37.7897 37.7897  
  % Biofuel in Blend 10.00% 10.00%    
Biofuel Cost,   PhP/L 3.7790 3.7790    
Hauler’s Fee 0.3599 0.3599    
Dealer’s   Margin 1.8260 1.8260    
  Sub-Total Local Costs, PhP/L 14.2911 14.2911    
VAT2 on Local   Costs , % 12% 12%    
VAT2 on Local   Costs , PhP/L 1.7149 1.7149    
OPSF 0.0000 0.0000    
Pump Price,   PhP/L 60.5652 60.5652   0.0000
         
3) Pump Price   Build-Up Summary Period 1 Period 2
GASOLINE PhP/L % PhP/L %
FOB 37.3930 75.53% 37.3930 75.53%
FRT 0.7479 1.51% 0.7479 1.51%
INS 1.4957 3.02% 1.4957 3.02%
CIF 39.6366 80.06% 39.6366 80.06%
DUT 0.0000 0.00% 0.0000 0.00%
SD 0.00 0.00% 0.00 0.00%
BF 0.0502 0.10% 0.0502 0.10%
BC 0.0495 0.10% 0.0495 0.10%
AC 0.0915 0.18% 0.0915 0.18%
WF 0.0275 0.06% 0.0275 0.06%
IPF 0.0001 0.00% 0.0001 0.00%
CDS 0.0000 0.00% 0.0000 0.00%
ET 4.3500 8.79% 4.3500 8.79%
VAT1 5.3047 10.71% 5.3047 10.71%
DPLC 49.5102 100.00% 49.5102 100.00%
DPLC 44.5591 73.57% 44.5591 73.57%
OCGM 7.5751 12.51% 7.5751 12.51%
RC 0.0000 0.00% 0.0000 0.00%
TS 0.4707 0.78% 0.4707 0.78%
PC 0.0000 0.00% 0.0000 0.00%
DEP 0.2805 0.46% 0.2805 0.46%
BIO 3.7790 6.24% 3.7790 6.24%
HF 0.3599 0.59% 0.3599 0.59%
DM 1.8260 3.01% 1.8260 3.01%
VAT2 1.7149 2.83% 1.7149 2.83%
OPSF 0.0000 0.00% 0.0000 0.00%
PP 60.5652 100.00% 60.5652 100.00%
DUT 0.0000 0.00% 0.0000 0.00%
SD 0.0000 0.00% 0.0000 0.00%
WF 0.0247 0.04% 0.0247 0.04%
IPF 0.0001 0.00% 0.0001 0.00%
CDS 0.0000 0.00% 0.0000 0.00%
ET 3.9150 6.46% 3.9150 6.46%
VAT1 4.7742 7.88% 4.7742 7.88%
VAT2 1.7149 2.83% 1.7149 2.83%
Govt Imposts 10.4290 17.22% 10.4290 17.22%
Delta (Period   2 – Period 1)     0.0000 0.00%

Oil Pump Price Model – Diesel      
1) DPLC Calculator – Diesel        
As of Jan-Jun 2012 average Period 1 Period 2   Delta (2 – 1)
Parcel Size, Bbl 100,000 100,000    
Liters per Bbl 158.9868 158.9868    
Total Volume, L 15,898,680 15,898,680    
Density, kg/L 0.80 0.80    
Total Metric Tons (1000 kg) 12,719 12,719    
MOPS, $/Bbl 135.000 135.000    
FOB, $ 13,500,000 13,500,000    
Freight, % FOB 2.00% 2.00%    
Insurance, % FOB 4.00% 4.00%    
CIF, $ 14,310,000 14,310,000    
Exchange Rate, PhP/$ 41.00 41.00    
CIF, PhP 586,710,000 586,710,000    
Customs Duty, % of CIF 0.00% 0.00%    
Customs Duty, PhP 0 0    
Special Duty, PhP/L 0.00 0.00 5,300  
Special Duty, PhP 0 0 200,000  
Brokerage Fee, PhP 738,438 738,438 0.00125  
Bank Charge, PhP 733,388 733,388 0.00125  
Arrastre Charge, PhP 1,551,711 1,551,711 122.00  
Wharfage Charge, PhP 466,149 466,149 36.65  
Import Processing Fee, PhP 1,000 1,000    
Customs Doc Stamps, PhP 256 256    
Excise Tax, PhP/L 0.00 0.00    
Excise Tax, PhP 0 0    
Landed Cost (LC), PhP 590,200,941 590,200,941    
VAT1 on Importation, % LC 12% 12%    
VAT1 on Importation, PhP 70,824,113 70,824,113    
Duty Paid Landed Cost, PhP 661,025,054 661,025,054    
Duty Paid Landed Cost, PhP/L 41.5774 41.5774   0.0000
         
2) Pump Price Model – Diesel        
As of Jan-Jun 2012 average Diesel Period 2   Delta (2 – 1)
  % Pure Oil 98.00% 98.00%    
DPLC, PhP/L 40.7458 40.7458    
Oil Company   Gross Margin, % of DPLC 2.00% 2.00%    
Oil Company   Gross Margin, PhP/L 0.8149 0.8149    
Refining   Cost, PhP/L 0.0000 0.0000 0.0000  
Transshipment,   PhP/L 0.5125 0.5125 0.5230  
Pipeline   Cost, PhP/L 0.0000 0.0000 0.0000  
Depot   Operation, PhP/L 0.3052 0.3052 0.3114  
Pure Biofuel   Cost, PhP/L biofuel 61.6786 61.6786    
  % Biofuel in Blend 2.00% 2.00%    
Biofuel Cost,   PhP/L 1.2336 1.2336    
Hauler’s Fee 0.1970 0.1970    
Dealer’s   Margin 1.4717 1.4717    
  Sub-Total Local Costs, PhP/L 4.5349 4.5349    
VAT2 on Local   Costs , % 12% 12%    
VAT2 on Local   Costs , PhP/L 0.5442 0.5442    
OPSF 0.0000 0.0000    
Pump Price,   PhP/L 45.8249 45.8249   0.0000
         
3) Pump Price   Build-Up Summary Period 1 Period 2
DIESEL PhP/L % PhP/L %
FOB 34.8142 83.73% 34.8142 83.73%
FRT 0.6963 1.67% 0.6963 1.67%
INS 1.3926 3.35% 1.3926 3.35%
CIF 36.9031 88.76% 36.9031 88.76%
DUT 0.0000 0.00% 0.0000 0.00%
SD 0.00 0.00% 0.00 0.00%
BF 0.0464 0.11% 0.0464 0.11%
BC 0.0461 0.11% 0.0461 0.11%
AC 0.0976 0.23% 0.0976 0.23%
WF 0.0293 0.07% 0.0293 0.07%
IPF 0.0001 0.00% 0.0001 0.00%
CDS 0.0000 0.00% 0.0000 0.00%
ET 0.0000 0.00% 0.0000 0.00%
VAT1 4.4547 10.71% 4.4547 10.71%
DPLC 41.5774 100.00% 41.5774 100.00%
DPLC 40.7458 88.92% 40.7458 88.92%
OCGM 0.8149 1.78% 0.8149 1.78%
RC 0.0000 0.00% 0.0000 0.00%
TS 0.5125 1.12% 0.5125 1.12%
PC 0.0000 0.00% 0.0000 0.00%
DEP 0.3052 0.67% 0.3052 0.67%
BIO 1.2336 2.69% 1.2336 2.69%
HF 0.1970 0.43% 0.1970 0.43%
DM 1.4717 3.21% 1.4717 3.21%
VAT2 0.5442 1.19% 0.5442 1.19%
OPSF 0.0000 0.00% 0.0000 0.00%
PP 45.8249 100.00% 45.8249 100.00%
DUT 0.0000 0.00% 0.0000 0.00%
SD 0.0000 0.00% 0.0000 0.00%
WF 0.0287 0.06% 0.0287 0.06%
IPF 0.0001 0.00% 0.0001 0.00%
CDS 0.0000 0.00% 0.0000 0.00%
ET 0.0000 0.00% 0.0000 0.00%
VAT1 4.3656 9.53% 4.3656 9.53%
VAT2 0.5442 1.19% 0.5442 1.19%
Govt Imposts 4.9386 10.78% 4.9386 10.78%
Delta (Period   2 – Period 1)     0.0000 0.00%

KEROSENE   (3000 ppm Sulfur)   0.30 % Sulfur     Jun-08 May-08 Apr-08
   FOB, $/bbl ( MOPS, Singapore ) $ / bbl       2.50% 159.170 159.580 138.580
   Exchange Rate, P/$ PhP / $         42.902 42.902 41.820
   Freight, $/bbl $ / bbl 1.0530 $ / bbl   2.50% 1.0530 1.0530 1.0530
C&F, $/bbl $ / bbl         160.2230 160.6330 139.6330
   Insurance, $/bbl $ / bbl 0.0500 Insurance, % C&F   0.0801 0.0803 0.0698
CIF, $/bbl $ / bbl         160.3032 160.7134 139.7029
   Wharfage, $/bbl $ / bbl 0.0886 Wharfage, $/bbl 5.00% 0.0886 0.0886 0.0886
   BOE Fee, $/bbl $ / bbl 0.1000 BOE Fee, % CIF   0.1603 0.1607 0.1397
   Ocean Loss, $/bbl $ / bbl 0.5000 Ocean Loss, % CIF   0.8015 0.8036 0.6985
   Doc Stamp, $/bbl $ / bbl 0.1500 Doc Stamp, % CIF   0.2405 0.2411 0.2096
   Demurrage, $/bbl $ / bbl 0.0000 Demurrage, % Freight   0.0000 0.0000 0.0000
   Duty on Imported Oil, $/bbl $ / bbl 3.0000 Duty, % CIF   Excise Tax 4.8091 4.8214 4.1911
   Specific Tax on Imported Oil (new) $ / bbl 0.0000 PhP / L 1   0.0000 0.0000 0.0000
   VAT on Imported Oil (new) $ / bbl 12.0000 %   of CIF + BOE + OL + DS + DEM + ST 19.9684 20.0194 17.4036
DPLC, $/bbl $ / bbl         186.3715 186.8482 162.4340
DPLC, P/liter PhP / L 158.9868 L / bbl     50.2916 50.4202 42.7263
  % margin 0.7900 kg / L     0.31% 0.08% 6.89%
   Specific Tax (old) PhP / L 0.0000 PhP / L 0   0.0000 0.0000 0.0000
   Oil Company Gross Margin (old) PhP / L 0.6850 as of July 1997 5.5306 2.13% 0.1542 0.0394 2.9447
   Dealers’ Margin PhP / L 1.2000 PhP / L 0.4857 5.00% 1.2000 1.2000 1.2000
   Refillers’ Margin (LPG) PhP / L              
   Haulers’ Fee PhP / L 0.1140 PhP / L 0.1140 5.00% 0.1140 0.1140 0.1140
   Transhipment Rate PhP / L 0.2000 PhP / L 0.0633 5.00% 0.2000 0.2000 0.2000
   Reheating cost PhP / L              
   Pipeline thruput fee PhP / L              
   VAT on other costs (new) PhP / L 12.0000 %   Margin and Delivery costs 0.2002 0.1864 0.5350
Sub-Total Margin, Delivery   & VAT on others PhP / L         1.8684 1.7398 4.9937
Estimated Pump Price  or Delivered Price PhP / L         52.1600 52.1600 47.7200
Prevailing Pump Price PhP / L         52.16 52.16 47.72
Variance PhP / L         0.00 0.00 0.00

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>