Excel / Power Query Expert Needed to Build Automated Freight Rate Database & Rate Finder

Excel / Power Query Expert Needed to Build Automated Air Freight Rate Database & Rate Finder ⚠️ IMPORTANT — PLEASE READ BEFORE APPLYING DO NOT APPLY FOR THIS PROJECT WITHOUT READING THE FULL JOB DESCRIPTION AND REVIEWING THE ATTACHED FILES. This is NOT a basic Excel data-entry, copy/paste, formatting or simple spreadsheet project. I have multiple air freight rate files with different tabs, layouts, weight breaks, charges, surcharges, inland rates and conditions. I need someone who can understand the data, structure it properly and build an automated Air Freight Rate Finder. Before applying, please understand: You will be working with 3 different source files The files have different structures and layouts The project covers Air Import, Air Export and Air Freight Tariff/Charges Weight-break and minimum-charge logic must be handled correctly. Additional charges and surcharges need to be preserved Inland/cartage rates where applicable need to be retained Source references and tariff conditions need to be preserved. The final system must be maintainable by me. I work primarily on a Mac, so Mac compatibility is important I need to be able to add new monthly tariffs myself I do not want to depend on the freelancer every month for updates. If you have not read the complete requirements and reviewed the attached files, please DO NOT APPLY. Generic proposals such as: "I am an Excel expert with 10 years of experience and can complete this quickly." will be ignored. Your proposal should demonstrate that you understand this specific project and explain how you would approach these files. ****Project Overview***** I run an Australian freight forwarding business, Navtrex Pty Ltd, and I need an experienced Excel / Power Query / data automation specialist to consolidate my existing air freight rate and tariff information into one professional, searchable Air Freight Rate Database and Rate Finder. The objective is simple: Enter the shipment details → find the applicable air freight rates and charges quickly. I currently need to manually search through multiple Excel files and tabs to find rates. I want to replace this with one centralised system. This is a data-structuring and automation project, not simply a spreadsheet-cleaning exercise. Source Files I have three source files that need to be incorporated. 1. Air Export Freight Tariff — Excel The Export workbook contains multiple tabs for Australian origins, including: Adelaide Brisbane Melbourne Perth Sydney Auckland The file contains different destinations, airlines/routings, weight breaks, minimums and validity information. The structure varies between tabs, so the data needs to be properly standardised. 2. Air Import Freight Tariff — Excel The Import workbook contains multiple tabs covering international origins/destinations and supporting information. The data includes items such as: Origin Destination Country City Airport codes Airline Routing Cut-off times Transit times On-forwarding Inland rates Zone information Weight-based charges Country-specific conditions The freelancer must review the actual file and determine the best way to structure this information. 3. Air Freight Tariff & Charges — PDF The tariff/charges file contains separate Import and Export charges, including items such as: Breakbulk Cargo Automation Fee Administration Fee Airline Terminal Fee Import Document Fee Handover Fee Storage Dangerous Goods Transfer SAC Entry International Tranship Fee Quarantine charges Palletisation Shrink wrap Labelling Cartage Export Documentation Export AWB Fee Security Maintenance Fee CTO Security Fee Airport Transfer Screening charges X-Ray Repack Undeclared Dangerous Goods Other applicable charges The tariff contains fixed charges, minimum charges, per-KG charges and conditional charges, so these need to be structured correctly rather than simply copied into one generic "charges" field. What I Want Built I want one central system where I can search for air freight rates without manually opening multiple files and tabs. Ideally, I should be able to select: Direction Import Export Australian Origin Adelaide Brisbane Melbourne Perth Sydney etc. Destination Country City Airport Airline Airline/carrier Shipment Details Chargeable weight The system should then return the relevant rate options and applicable charges. Example: EXPORT → SYDNEY → INDIA → MUMBAI → 150 KG The system should be able to identify the applicable: Airline Routing Weight break Rate/KG Minimum charge Fuel/FSC where applicable Security charges Terminal/CTO charges Documentation charges Other applicable charges Transit time Validity Source/reference Relevant tariff conditions I want the results displayed clearly so I can compare available airline options. Weight Break Logic The system must correctly handle different weight structures. Examples may include: Minimum +45 KG +100 KG +300 KG +500 KG +1000 KG The applicable rate should be determined from the actual chargeable weight. For example, if the shipment is 150 KG, the system should correctly identify the applicable weight break according to that specific airline's tariff. Different airlines may have different weight structures, so the system must not assume every tariff uses the same weight breaks. Minimum Charge Logic Where a tariff has both: Minimum charge Per-KG rate the system should correctly determine the applicable freight charge according to the source tariff. For example: Chargeable Weight × Applicable KG Rate versus Minimum Charge The correct tariff logic should be applied. Charges & Surcharges I don't want the system to simply return a raw airfreight rate. Where the source data allows it, applicable charges should be identified separately. However, charges should only be automatically calculated when the source tariff provides sufficient information to do so correctly. If a charge is: On Application On Request Conditional Not available Dependent on a specific circumstance it should be clearly identified rather than incorrectly calculated. Inland / Zone Rates Where the Import tariff contains inland or zone information, this should be retained and structured appropriately. This may include: Inland locations Postal-code zones Weight ranges Minimum charges Inland transport rates On-forwarding These rates should not be lost simply because they are located on separate tabs. Source References This is important. I want to be able to identify where a rate came from. The database should ideally retain: Source file Source worksheet/tab Airline Tariff reference Validity date Original conditions/notes This allows me to trace a rate back to the original tariff when checking a customer quotation. Monthly Rate Updates This is one of the most important requirements. I receive new tariff files periodically. I need to be able to update the system myself. The ideal workflow would be: Receive new tariff → add/import file → Refresh → updated rates available I do not want to pay a developer every month to update my rates. The finished system should allow me to add/update: Import tariffs Export tariffs Airline rates Countries Cities Airports Airlines Weight breaks Tariff/charge information without rebuilding the entire system. Technology I am primarily looking for an Excel-based solution. Preferred technologies include: Microsoft Excel Power Query XLOOKUP FILTER LET Dynamic arrays Structured Excel Tables Data validation VBA where genuinely useful Python can be used during development if necessary, but I do not want Python knowledge to be required for my normal monthly updates. The finished system needs to be maintainable by me. Mac Compatibility I primarily work on a Mac. This is important. The final solution must be usable with Microsoft Excel for Mac. Please clearly identify if any proposed functionality is Windows-only. I do not want to discover after the project is completed that I need a Windows computer to update my rates. Rate Finder Interface I want the Rate Finder to be simple and professional. For example: Direction: Export ▼ Australian Airport: Sydney ▼ Country: India ▼ City: Mumbai ▼ Airline: All ▼ Chargeable KG: 150 [ FIND RATES ] The system should then display the matching options. Ideally, I would like to see multiple airline options side-by-side where available. Desired Database / Workbook Structure I am open to the freelancer recommending the best structure. However, I expect something broadly similar to: Rate Finder Air Export Rates Air Import Rates Tariff / Charges Inland / Zone Rates Master Database Source Data Conditions / Notes Supporting Lists Instructions The exact structure can be determined after reviewing the files. Accuracy Is More Important Than Appearance The priority is accuracy and reliability. I don't want the freelancer to simply make the files look neat. The system needs to preserve: Rates Minimums Weight breaks Surcharges Validity Routing Airlines Inland rates Zones Conditions On-request rates Special charges Source references If something cannot safely be automated, I would rather have it clearly flagged than have the system produce an incorrect rate. Deliverables The successful freelancer will deliver: 1. Master Air Freight Rate Database Consolidated and standardised data from the three supplied files. 2. Automated Rate Finder Search interface with dropdowns and shipment inputs. 3. Air Export Rate Logic Including weight breaks, minimums, airlines, routing and applicable charges. 4. Air Import Rate Logic Including weight breaks, minimums, airlines, routing, inland/zone rates and applicable charges. 5. Tariff / Charges Database Structured fixed, minimum, per-KG and conditional charges. 6. Source References Ability to trace rates back to their original source. 7. Monthly Update Process I must be able to add new tariffs myself. 8. Mac Compatibility The system must work on Microsoft Excel for Mac. 9. User Guide A simple guide explaining how I update the system when new tariffs arrive. 10. Testing The final system should be tested against examples from the supplied tariff files to ensure the correct rate/weight break/charge is being returned. ************************************************ Freelancer Requirements You should have strong experience with: Advanced Microsoft Excel Power Query Data transformation Data consolidation XLOOKUP FILTER Dynamic arrays Excel Tables Data validation VBA where appropriate Experience with the following is a major advantage: Freight forwarding Logistics Airline tariffs Air freight pricing Freight quotation systems Freight rate databases ************************************************ Before Applying Please review ALL THREE attached source files before submitting your proposal. I don't want someone to simply copy/paste the data. I need someone who can look at the different structures and design a proper, scalable air freight-rate database and search system.