Energy Technology Expert

How to predict oil price adjustments from changes in MOPS and FOREX – a simplified formula

Yes, your favorite energy technology and pricing expert has simplified the calculation of oil price adjustments given changes in product MOPS and FOREX since not all the minor cost items change.

The duty paid landed cost inclusive of 12% VAT on imported oil, in $/bbl is:

DPLC $/bbl = (CIF + WHARFAGE + BOE FEE + OCEAN LOSS + DOC STAMPS + DEMURRAGE + CUSTOMS DUTY + SPECIFIC TAX) x 1.12

where FOB = MOPS + PREMIUM (where PREMIUM is usually zero)

FRT = 1.1049 $/bbl gasoline, 1.1089 $/bbl naphtha, 1.0530 $/bbl kerosene, 1.0329 $/bbl diesel, 1.2984 $/bbl fuel oil, 14.9810 $/MT LPG (ocean freight)

INS = (FOB + FRT) x 0.05% (insurance for ocean voyage)

CIF = FOB + FRT + INS

= FOB + FRT + (FOB + FRT) x 0.05%

= (FOB + FRT) x (1 + 0.05%) = (FOB + FRT) x 1.0005

WHARFAGE = 0.0823 $/bbl gasoline, 0.0886 $/bbl kerosene, 0.0946 $/bbl diesel, 0.1077 $/bbl fuel oil, 0.6999 $/MT LPG (port authority)

BOE FEE = CIF x 0.10% (Board of Energy Fee)

OCEAN LOSS =CIF x 0.50% (ocean loss allowance)

DOC STAMPS = CIF x 0.15% (documentary stamps)

DUMURRAGE = usually zero unless there are delays at the discharge port

CUSTOMS DUTY = CIF x 3.00% (on CRUDE and PRODUCTS)

SPECIFIC TAX = 4.36 x 158.9868 / FOREX for gasoline

= 3.67 x 158.9868 / FOREX for avturbo

= zero for diesel and other products

FOREX = foreign exchange rate, Php/$ = say 48.00 PhP/$

The duty paid landed cost is then converted to equivalent Pesos per liter using the exchange rate and conversion from barrels to liters (1 barrel = 42 gallons, 1 gallon = 3.7854 Liters):

DPLC PhP/Liter= (DPLC  $/bbl) x (48.00 PhP/$) x (bbl / 158.9868 Liters)

= DPLC x FOREX / 158.9868

The next step is to add the oil company margin (the only portion that actually goes to the oil company since the other cost factors are simply pass-thru charges that go to the supplier, shipper, insurer, port authority, government tax and VAT) and the other local costs and local VAT.

Based on the author’s monitoring of 2007 annual average MOPS, exchange rate and pump price, the oil company margin as % of DPLC is shown below:

% OIL COMPANY MARGIN = 14.77% 95 RON, 13.17% 93 RON, 15.29% 87 RON, 30.90% kerosene, 1.34% avturbo, 9.07% diesel, 19.79% fuel oil, 29.28% LPG

OIL COMPANY MARGIN = (DPLC PhP/Liter) x (% OIL COMPANY MARGIN)

BIOFUELS = 0.200 PhP/liter for 1% CME in diesel (I have no idea for 10% ETHANOL in gasoline)

DEPOT COST = 0.250 PhP/liter (depends on bulk plant location and type of products carried)

DEALER’S MARGIN = 1.200 PhP/liter for gasoline, kerosene, avturbo and diesel, 1.3640 PhP/kg for LPG

REFILLER’S MARGIN = 0.500 PhP/kg for refillers of LPG

TRANSSHIPMENT = 0.200 PhP/liter for gasoline, kerosene, avturbo and diesel, 0.0897 PhP/liter for fuel oil, 0.3226 PhP/kg for LPG (tankers and barges)

HAULER’S FEE = 0.1140 PhP/liter for gasoline, kerosene, avturbo and diesel, 0.1254 PhP/liter for fuel oil, 0.3059 PhP/kg for LPG

The local costs inclusive of 12% VAT on local value adding activities is:

LOCAL COSTS PhP/Liter = (OIL COMPANY MARGIN + BIOFUELS + DEPOT COST + DEALER’S MARGIN  + TRANSSHIPMENT + HAULER’S FEE) x 1.12

Adding the imported costs and local costs we get the final pump price:

PUMP PRICE PhP/Liter = (DPLC PhP/Liter) + (LOCAL COSTS PhP/Liter)

The oil price adjustment as a result in changes in product price MOPS and FOREX is then calculated by calculating the PUMP PRICE in the previous week and the current week.

This is readily done in an Excel spreadsheet and could be done quite accurately since the minor oil cost items that we really don’t know the exact value will simply cancel out.  I have such an spreadsheet as shown in my oil price bulletin wherein I copy pasted the values to the document file.

We could derive an approximation of the oil price adjustment based mainly in the changes in MOPS and FOREX rate since the other minor cost items will not significantly change between two week intervals (1) and (2).

The upward (positive) or downward (negative) adjustment is calculated as follows:

ADJUSTMENT = PUMP PRICE(2) – PUMP PRICE(1) = change in each cost item

The changes in each cost item inclusive of 12% VAT are as follows

Change in CIF = [FOB(2) x FOREX(2) – FOB(1) x FOREX(1)]x (1 + 0.05%) x 1.12 / 158.9868

Change in Wharfage = zero

Change in BOE FEE = [FOB(2) x FOREX(2) – FOB(1) x FOREX(1)] x (1 + 0.05%) x 0.10% x 1.12 / 158.986

Change in OCEAN LOSS = [FOB(2) x FOREX(2) – FOB(1) x FOREX(1)] x (1 + 0.05%) x 0.50% x 1.12 / 158.9868

Change in DOC STAMPS = [FOB(2) x FOREX(2) – FOB(1) x FOREX(1)] x (1 + 0.05%) x 0.15% x 1.12 / 158.9868

Change in DEMURRAGE = zero

Change in CUSTOMS DUTY = [FOB(2) x FOREX(2) – FOB(1) x FOREX(1)] x (1 + 0.05%) x 3.00% x 1.12 / 158.9868 for crude and products

Change in SPECIFIC TAX = zero (no change)

Summarizing, the change in the duty paid landed cost is:

Change in DPLC = { [ FOB(2) x FOREX(2) – FOB(1) x FOREX(1) + (FOREX(2) - FOREX(1)) x FRT ] x (1 + 0.05%) x (1+ 0.10% + 0.50% + 0.15% + 3.00%) + ((FOREX(2) – FOREX(1)) x WHARFAGE } 1.12 / 158.9868

The next step is to calculate changes in local costs inclusive of 12% VAT on local value adding activities:

Change in oil company margin = (Change in DPLC) x (% OIL COMPANY MARGIN)

= (Change in DPLC) x 14.77% for gasoline (95 RON)

= (change in DPLC) x 9.07% for diesel

Change in BIOFUELS = zero

Change in DEPOT COST = zero

Change in DEALER’S MARGIN = zero

Change in REFILLER’S MARGIN = zero (for LPG only)

Change in TRANSSHIPMENT = zero

Change in HAULER’S FEE = zero

The total change in local costs is thus:

Change in LOCAL COSTS = (Change in DPLC) x (% OIL COMPANY MARGIN) x 1.12

Adding up the changes in DPLC and LOCAL COSTS we get the oil price adjustment:

ADJUSTMENT = Change in DPLC + Change in LOCAL COSTS

= { [ FOB(2) x FOREX(2) – FOB(1) x FOREX(1) + (FOREX(2) - FOREX(1)) x FRT ] x (1 + 0.05%) x (1+ 0.10% + 0.50% + 0.15% + 3.00%) + ((FOREX(2) – FOREX(1)) x WHARFAGE } 1.12 / 158.9868 x (1 + % OIL COMPANY MARGIN x 1.12)

As you can see, it is rather a complicated equation that tracks the changes in both the oil product MOPS or FOB and FOREX between two periods (1) and (2).

The above formula tracks the change in FOB/MOPS and FOREX and its impact on CIF, insurance (0.05%), BOE FEE (0.10%), OCEAN LOSS (0.50%), DOC STAMPS (0.15%) and CUSTOMS DUTY (3.00%).  The other cost items simply cancels out between two periods.

If we disregard the freight and wharfage terms, then the main determinants of oil price change are FOB/MOPS, FOREX and % OIL COMPANY MARGIN:

ADJUSTMENT = { [ FOB(2) x FOREX(2) – FOB(1) x FOREX(1) ] x (1 + 0.05%) x (1+ 0.10% + 0.50% + 0.15% + 3.00%) } 1.12 / 158.9868 x (1 + % OIL COMPANY MARGIN x 1.12)

It assumes, however, that the % OIL COMPANY MARGIN remains the same which is a reasonable assumption since it is percentile, not an absolute value, and is acceptable to the oil companies. The oil companies are averse to an absolute Peso per liter absolute oil company margin because it does not value the rise and fall of the amount of capital they put to bring the product to the local market, which I think is reasonable since it is akin to a % margin on your cost of goods.  It works both ways in favor of both customer and supplier of oil products.

To avoid errors, I suggest that we design a standard spreadsheet showing the itemized calculations from the supply cost (MOPS) to the PUMP PRICE at the customer given the MOPS and FOREX between the two periods and calculating the absolute difference (adjustment).

Please leave your comments and suggestions.

Marcial T. Ocampo

Energy Technology & Pricing Expert

Business Development Consultant

(Friendly note: All content written by Engr. Marcial T. Ocampo are copyrighted and may not be redistributed in any way or form.)