Inventory/Shipping management problem - dealing with 2 data sets

Hi there,

I'll try to articulate the issue I'm facing as clearly as possible, and have attached an example data set, so hopefully what I'm asking will be clear.

Basically, I have 2 data sets. The first comes from a shipping company detailing various shipments of the product, it contains the date of shipment, the name of the ship, amount of the good shipped and the % that are broken based on a sample taken at the loading dock. The second is a data set generated at the plant level, it contains the date of manufacture, quantity, name of the plant and the % broken as measured at the plant.

What I need to do is formulate a rough comparison of the % broken as they arrive at the loading dock vs. at the plant. I would like to see this by plant and overall by month. I don't have real data on which ship each batch produced at the plant was shipped on, but I can reasonably estimate this by comparing the ship capacities/batch sizes and dates.

E.g. The first batch of the good is 40,000 units on January 1, looking at the shipping schedule I can see that the next shipment after that date had 8000 units, so I can assume that 8000 of my original 40,000 were on board (so I want these 8000 to be associated with this shipment so I can compare the broken % of this batch with the broken % of that shipment) I also have 32,000 units remaining that I can assume were on board the next shipment of 40,000 units and so forth.

Is tracking this sort of thing manageable in Excel, and if so what is my best approach - or do I need to use Access or do things more manually in order to get the information I'm looking for? Any guidance would be great.