spreadsheet to analyze raw material locations

I am looking for a spreadsheet/s to analyze raw material location to be used in production.

Explaination:

I work in a chemical manufacturing plant. We blend liquid raw materials into finished products.
Are raw materials are in 55 gallon drums, and are in the "production area" of the warehouse.
There are two rows with 14 sections in each row and 4 shelves per section (total of 112 seperate locations, not all locations are currently used). Each location fits one pallet which can hold 4 drums (not all pallets are full).
I need a spreadsheet/s to help me determine which drums should go where. This would be based on how often a raw is used, what finished product it is used in. The idea is that all the raws for a finished product should be on the same pallet and/or next to each other in location, as well as the raws that are used the most should be in the locations easiest to get to.
Hopfully that makes some kind of sence.

I have a spreadsheet that represents the warehouse locations and what is in them. I just made a spreadsheet for entering the importent information: finished product, number of times made, raws used, and location which they came from.
The information I am trying to get is:
1) How many times a raw is used in all finished products (to determine which raws are used the most).
2) How many times a location is picked from (to determine where to move the heavily pick locations to).
3) Which finished products use the same raws (to determine which raws should be near each other).
4) To be able to have the spreadsheet/list sorted by which finished product is made the most to teh least.
I have attached the spreadsheets that I have made.