Dear Cathy,

Our company discovered that we can order a report from the U.S. Census Bureau with information about our export filings through the Automated Export System (AES) by visiting the Census website and downloading and completing the template letter and required certification.

We sent the letter to Census listing all our primary addresses and tax IDs (EIN) and those of our subsidiaries. Just last week we received a notice that the data was available to us to download; we downloaded the many Excel spreadsheets. Just three words: WHAT A MESS! We are faced with extracting the data from these reports, segregating the several lines of shipment data, then aggregating the data into reports for each subsidiary. 

We have learned three hard lessons from examining the data:

1. We must provide clear, complete instructions to the forwarders completing the Electronic Export Information (EEI) in AES.

2. We must be proactive in auditing the EEIs they send us to ensure correct data is presented to Customs and Border Protection (CBP).

3. We may need to complete the filings in-house if the forwarders are unable to meet our accuracy standards and measures that we have adopted and submitted to them in either our

Standard Operating Procedure or our Shipper's Letter of Instruction.

We also discovered that some forwarders have indicated some shipments as routed, when they weren’t, and other forwarders have indicate some shipments as not routed, when they were.

Back to those reports; what do we do with all of this data?

Dorothy

Overwhelmed in Kansas City

Dear Dorothy,

What would I do? If you have an internal Excel or Access database expert that is willing to work with you to create formulas that can extract and merge data, go to them to seek time on their busy schedules to help you tackle the databases that were provided. 

Here are some hints and tips that I have used to manage the data:

* Save the files with a unique name, but have that name include the original Census file name.

* Add a column to each Census spreadsheet that allows you to input a unique identifier for each line of data that allows you to cross reference any new spreadsheet; this allows you to verify that the data was moved with integrity to the new spreadsheet.

* Forwarders sometimes file a firm's subsidiary's EEIs using the corporate tax identification number instead of the subsidiaries. If the subsidiary's address was used (instead of the corporate address), then the first sort will be to apply a sort by address to identify filings. Then it is possible to move those EEIs to two spreadsheets for each subsidiary:

-Routed

-Non-routed

* Once the allocation of EEI data has occurred (a big task), it is time to take the next step; it is time to review the data against the shipment detail and data that you have.

-Ensure buy-in from compliance, management and corporate legal counsel (internal or external) to place all results under attorney-client privilege in the event there is a need to make a disclosure of findings to Census. A disclosure is an official presentation of your finding of errors to the appropriate government agency, such as Census.

-Select a sample of shipments to review based on destination, volume, value, classification and export controls.

-Create an audit checklist for each EEI filing.

-Develop an audit team that includes representatives from your subsidiaries and corporate office. (I call this sharing the opportunity for learning.)

-Work together to review the documentation that is on file at your offices and those of your subsidiaries for the EEIs selected for review.

-If the findings warrant a 100% review, then proceed with the 100% review.

-Upon completion of the review, instruct the EEI filer, typically a forwarder, to correct the errors that you discovered by providing them with an Excel spreadsheet listing the errors that you found along with the corrections to be made.

-Instruct the forwarder to provide you with a copy of the corrected EEI.

I am an optimistic person! It is my belief that compliance is possible, but it takes effort to establish processes, procedures and policies that internal and external partners will follow and embrace. Luckily, it doesn't take a magician pulling levers behind the curtain—just some good old fashioned hard work and thoughtful coordination of your internal resources!

All the best!

Cathy