by Calculated Risk on 5/20/2005 01:01:00 AM

UPDATE: Error correction (thanks to fatbear), I wrote millions instead of billions in several places.

The April trade balance will be reported on June 10th. Over the next couple of weeks, I'm going to try to develop a simple model to predict the trade deficit. I'll post my methodology and hopefully others will offer suggestions and improvements. Of course, it takes more than one month's data to make a trend and these predictions are just for fun.

My general approach will be to divide the deficit into two components: petroleum energy related products and everything else. This is a mixed model, by "goods" for petroleum, by country or region for everything else. We have to be careful: there can be overlap between a country approach and petroleum approach: Canada and Mexico are good examples.

Part I: Energy-Related Petroleum Products (ERPP).

Each month, on exhibit 17, the Census Bureau and the Bureau of Economic Analysis present the imports for ERPP. These numbers are not seasonally adjusted (NSA).

To forecast the Petroleum number we need:
1) Price per barrel of crude oil.
2) Quantity of crude oil imported.
3) Price per barrel of "Other" ERPP
4) Quantity of "Other" ERPP.

Total NSA ERPP ($) = P (crude) * Q (crude) + P (other) * Q (other).

CRUDE OIL

To estimate the Crude price and quantity, I'm going to use the reported numbers from the DOE. On sheet "2-TWIP Crude World & US Prices", the DOE provides the weekly contract price back to 1997. On sheet "5-TWIP Crude Imports" the DOE provides the average daily imports, not including SPR. NOTE: Sheet 2 is contract prices, even though it is labeled "spot" prices "United States Spot Price FOB Weighted by Estimated Import Volume (Dollars per Barrel)".

It appears that the contract lengths vary over time, but an 8 week volume weighted average, provides a close approximation of the price. Using this data since Jan '97, this approach usually (2/3 of the time) comes within +/-2% of the Census reported price, although there were two months were it missed by 5%. Still the correlation is very good. Here is the DOE data since Feb 1:

Week	Contract Price ($/BBL)	BBL (1000s/day)
Feb 04, 2005	37.915	9914
Feb 11, 2005	36.575	10434
Feb 18, 2005	38.076	9643
Feb 25, 2005	40.567	10065
Mar 04, 2005	43.377	10099
Mar 11, 2005	45.463	10041
Mar 18, 2005	46.42	10270
Mar 25, 2005	46.969	10568
Apr 01, 2005	45.324	9890
Apr 08, 2005	47.688	9863
Apr 15, 2005	44.23	9718
Apr 22, 2005	43.957	10863
Apr 29, 2005	45.902	10259

NOTE: I didn't try to adjust the week end to match the month end.

Using this approach, the predicted average March price per BBL/crude would be $41.20. This is almost 2% more than the reported price of $41.14. Pretty close. For April, the predicted P(crude) is $45.70.

For the quantity of crude, I used a 4 week average of the DOE's average daily crude imports. Since '97, using this method has resulted in a fairly consistent prediction that is about 4% low per month (a systemic error). So I adjust this number accordingly (divide by .96). The March prediction of Q (crude) would be: 330,812 BBLs. The actual number was 325,979 or about 1.5% less.

For March, the estimate of NSA crude = 330,812 * $41.20/BBL = $13.63 Billion compared to the actual of $13.41 Billion.

For April, the NSA prediction for Crude is: 328,592 * $45.70 = $15.0 Billion.

OTHER ERPP

According to the Census Bureau, Other ERPP includes "the following SITC commodity groupings; crude oil, petroleum preparations, and liquefied propane and butane gas". Since I'm not sure how to build up a price from all these products, I compared the price for Other ERPP to the price for crude (the 8 week moving volume weighted average). Although the relationship varies, using the following is relatively close: P (Other) = P (crude) * 1.15 (or 15% more). Most months fall within a +/- 5% of this price estimate. It probably depends on the mix of products and I couldn't determine any seasonal pattern to the price.

For Q (Other) I plotted the quantity of "other" since Jan '97. There has been a slight steady increase with a seasonal pattern. The best estimate for Other ERRP Quantity is probably the same month for the previous year. Sometimes this lead to a substantial error (20%), and I welcome a better approach.

For March, estimated Q (Other) would have been 100,000. Actual was 94,281. The estimated NSA Other was $41.20 * 1.15 * 100,000 BBL = $4.7 Billion. Actual was $4.5 Billion. The good news is that the larger percentage errors for "Other" are not very important for the overall ERPP.

For April, the NSA Other estimate = $45.70 * 1.15 * 82,000 BBL = $4.3 Billion.

NSA vs. SA

I haven't worked up a Seasonally Adjusted (SA) number yet for April ERPP. However, here are the numbers for Feb and Mar (see Exhibit 9 for SA numbers). It is interesting that NSA for March was $3 Billion more (or 20% more) than February, but SA March was only $0.75 Billion more (4% more) than February.

NOTE: These are IMPORTS only of ERPP. The US also exports ERPP that will have to evaluated too to estimate the total number.

Table numbers in Billions $

MONTH	NSA	Seasonally Adjusted
February	$14.947	$18.163
March	$17.955	$18.913
April	$19.300(est)	To Come

Here is the NSA estimate for April imports:

TOTAL NSA ERPP APRIL IMPORTS = $15.0 Billion (Crude) + $4.3 Billion (Other) = $19.3 Billion

SA to come. Part II will be about China.