Format and integrate multiple excel spreadsheets and create reports

PREFERENCE WILL BE GIVEN TO THOSE WHO CAN BEGIN WORK IMMEDIATELY PLEASE READ THE BRIEF IN DETAIL AND LOOK AT THE FILES. YOU MUST INCLUDE A TIME ESTIMATE AND ANY NEEDED DETAILS IN YOUR BID GENERIC BIDS WILL BE DELETED! I need to integrate 16 different excel reports into one excel file. Each sheet will require some formatting so that the data analysis run on the master reporting tab changes appropriately as the data changes. Goal: Consolidate the sheets into one excel file in the most efficient manner (Can be done in 2 tabs or separately Current setup: Facility Score Card (FSC) Reports are separated into separate excel files by month. Various issues are separated into 12 categories and then are used to generate reports. Each month has 3 tabs 1) Score Card Info Monthly Tracker: Number of issues each day separated by type and facility the issue occured at. 2) Score Card Incident Report: Notes more detail on the incident but is not used in the Summary sheet. This column contains the time spent fixing each issue which is needed in the master results. 3) Score Card Summary: Currently copies data from the score card monthly info tracker into a hidden columns AI to AT. Then creates graphs of the number of incidents per facility and the number of incidents per type per facility. Step 1 Integrate 15 months of separate FSC Score card data reports into one excel sheet, format the time sheets so that hours are a number and not a number/text combo (currently sheet has '4.0 HOURS' should just be hours. Step 2 Instead of just reporting the number of issues per month per facility (current reporting), The master sheet should report number of hours spent on each problem per facility and per month. Step 3 Need to normalize the data by integrating the number of shipments per day and per month into the data. The data needed is contained in the sheet 'All inbounds'. This sheet needs some formatting: 3A) If the PO number in column F contains any words or letters then Remove the row entirely 3B) If the Carrier, column B; says the words 'in-house' then remove the row entirely 3C) Once formatted this data can be used to create new reports that show the number of incidents per facility per month divided by the total number of shipments per day and per month Step 4 Create a new reporting tab that normalizes the incident reporting using the number of shipments per day pulled from the formatted 'all inbounds' sheet. The end results should be taking the 4 monthly reporting indicators and creating a new reporting tab that shows 4A) The total number of incidents per month divided by the total number of shipments 4B) The total hours spent per month divided by the total number of shipments Step 5 Need to normalize the data by the number of shipments per facility 5A) new columns H & I need to be created which pulls data from column E - the last two items of each entry are the city and the state. Column H should be the City and Column I should be the state. For example MS MONSATO - WATERMAN, IL should be Separted so that column H is Waterman and Column I should be IL. 5B) The end Result would then integrate into the overall report to create the total number of of incidents by facility by month - divided by the total number of shipments that came from that facility that month.