Petroleum Logistics Data Analytics Dashboard
Budget: $250 – $750 USD
End-to-End Petroleum Logistics Visibility Dashboard (Excel + Power Query, Power BI-ready) 1. Background & Objective We operate a bulk petroleum logistics and distribution business involving: • Import of petroleum products at Dar es Salaam • Storage of petroleum at depot in Dar es Salaam • Dispatch through petroleum tanker fleet (owned and 3PL) • Cross-border transportation through Tanzania → Zambia → DRC • Delivery into owned depots and client depots in DRC • After offloading, trucks return empty from DRC → Zambia → Tanzania • In Dar es Salaam, trucks go to workshop for maintenance, and then go to depot for loading • In DRC, Last-mile distribution from our owned DRC depot to end customers in owned-local truck fleet Currently, most operational tracking is done manually using Excel. The objective of this project is to create a structured, refreshable operational dashboard that provides end-to-end visibility of product volumes, Bill of Lading (BL) linked petroleum product, fleet movement, depot capacities, and downstream deliveries, with the ability to highlight risk situations and mismatches early.
2. Scope of Data Inputs (Excel-based) 2.1 BL & Hospitality Register (Origin Depot – Dar es Salaam) An Excel register containing one row per Bill of Lading, including: • BL Number (unique) • Product • Vessel Name • Consignee Name • Volume in MT and M³ • Date product is available for loading • Hospitality start date • Hospitality end date • Origin depot • Remarks Typically, 10–15 active BLs exist at any time. 2.2 Dispatch & Loading Register (Long-Haul Fleet) An Excel register maintained by the dispatch team, containing: • Load / Trip ID • Date & time of loading • Truck number • BL number (link to BL register) • Loaded volume • Destination depot • Customer name • Release status and reason (if delayed) 2.3 Fleet Tracking Register – Long Haul (4-hourly updates) An Excel register updated approximately every 4 hours, covering ~200 trucks, including: • Truck number • Date & time of last update • Current location / journey status.
Reason for delay (if any) • Destination depot • Customer name • Loaded / empty indicator This register reflects the full fleet journey cycle, including: • Available for loading • Waiting at origin depot • Loaded but pending release • On journey (segmented by corridor) • Border waiting (Tunduma, Sakania) • Destination depot waiting • Offloaded, pending release • Empty return • Workshop • Available for next loading 2.4 Destination Depot Stock Register (DRC) A daily Excel register for DRC destination depot (Lubumbashi), capturing: • Opening stock • Receipts from long-haul fleet • Deliveries • Closing stock • Total capacity (e.g., 4600 m³) • Available ullage For client or hospitality depots, estimated planning figures may be used where exact data is unavailable. 2.5 Customer Orders & Last-Mile Delivery Register An additional Excel register capturing end-customer demand and last-mile deliveries from our owned DRC depot.
This includes: • Customer name • Customer type (Mining / Reseller / Retail / Other) • Order reference • Order type (Monthly / Spot) • Order quantity (M³ / MT) • Required delivery date or delivery window • Assigned last-mile truck number (from 18-truck fleet) • Loaded date from DRC depot • Delivery status • Delivered quantity • Remarks / delays This register represents the final leg of the supply chain, linking inbound product at the DRC depot to actual customer consumption. 3. Expected Processing Logic (Power Query) The solution should: • Use Power Query to ingest all source Excel files from a shared OneDrive folder • Clean, standardize, and validate data (dates, truck numbers, depot names) • Link datasets using: o BL Number o Truck Number o Trip / Load ID o Customer / Order reference • Produce clean fact and dimension tables suitable for later migration to Power BI • Avoid VBA and Excel-only logic All transformation logic should be documented and reusable. 4. Dashboards & Outputs 4.1 BL & Hospitality Overview • Active BL list
Hospitality expiry countdown • Volume lifted vs remaining per BL • Vessel and consignee visibility 4.2 Fleet Journey Status (Long Haul) • Truck count by journey segment: o Dar–Iringa o Iringa–Mbeya o Mbeya–Tunduma o Tunduma border o Nakonde–Sakania o Sakania border o Destination depot • Trucks waiting due to documents, customs, or operational reasons 4.3 Depot Volume Visibility • Origin depot available volume • In-transit volume • Destination depot stock, ullage, and delivery rate 4.4 Fleet Availability & Workshop Cycle • Available trucks • In transit • Empty return • In workshop • Ready for next loading 4.5 Last-Mile Distribution (DRC) • Orders received vs fulfilled • Volume available in depot vs committed orders • Last-mile fleet utilization (18 trucks) • Pending deliveries and delivery delays
5. Risk, Constraint & Early-Warning Indicators The solution should be capable of building rule-based early warning flags to highlight operational risks arising from mismatches across the supply chain. Examples include (not exhaustive): • Inbound volume vs destination depot capacity (Loaded trucks en route exceeding available ullage) • Ready-to-load volume vs empty-truck availability at origin (Risk of hospitality expiry or demurrage) • Empty-truck return capacity exceeding available product (Idle fleet risk) • Customer orders vs depot stock availability (DRC) (Last-mile service risk) • Border congestion or prolonged dwell times (Journey imbalance risk) At this stage, the requirement is to: • Design the data model and dashboard framework such that these triggers can be implemented • Visually highlight risk / warning / normal states on dashboards • Allow future extension to alerts or notifications (e.g., Power BI alerts) Detailed thresholds and business rules will be finalized later. 6. Technology & Design Expectations • Primary build: Excel + Power Query • Source files: Excel (OneDrive) • Dashboard file: Excel (read-only for management) • Data model must be Power BI compatible • Naming conventions, mappings, and transformations must be documented • The solution should allow future migration to Power BI with minimal rework
7. Deliverables 1. Power Query–enabled Excel file(s) 2. Clean data model (facts & dimensions) 3. Operational dashboards (Excel); that can later migrate to Power BI 4. Avoid VBA and Excel-only hacks 5. Give the Power Query logic (and not lock it) 6. Brief documentation covering: o Data sources o Refresh process o Table relationships o Assumptions
2. Scope of Data Inputs (Excel-based) 2.1 BL & Hospitality Register (Origin Depot – Dar es Salaam) An Excel register containing one row per Bill of Lading, including: • BL Number (unique) • Product • Vessel Name • Consignee Name • Volume in MT and M³ • Date product is available for loading • Hospitality start date • Hospitality end date • Origin depot • Remarks Typically, 10–15 active BLs exist at any time. 2.2 Dispatch & Loading Register (Long-Haul Fleet) An Excel register maintained by the dispatch team, containing: • Load / Trip ID • Date & time of loading • Truck number • BL number (link to BL register) • Loaded volume • Destination depot • Customer name • Release status and reason (if delayed) 2.3 Fleet Tracking Register – Long Haul (4-hourly updates) An Excel register updated approximately every 4 hours, covering ~200 trucks, including: • Truck number • Date & time of last update • Current location / journey status.
Reason for delay (if any) • Destination depot • Customer name • Loaded / empty indicator This register reflects the full fleet journey cycle, including: • Available for loading • Waiting at origin depot • Loaded but pending release • On journey (segmented by corridor) • Border waiting (Tunduma, Sakania) • Destination depot waiting • Offloaded, pending release • Empty return • Workshop • Available for next loading 2.4 Destination Depot Stock Register (DRC) A daily Excel register for DRC destination depot (Lubumbashi), capturing: • Opening stock • Receipts from long-haul fleet • Deliveries • Closing stock • Total capacity (e.g., 4600 m³) • Available ullage For client or hospitality depots, estimated planning figures may be used where exact data is unavailable. 2.5 Customer Orders & Last-Mile Delivery Register An additional Excel register capturing end-customer demand and last-mile deliveries from our owned DRC depot.
This includes: • Customer name • Customer type (Mining / Reseller / Retail / Other) • Order reference • Order type (Monthly / Spot) • Order quantity (M³ / MT) • Required delivery date or delivery window • Assigned last-mile truck number (from 18-truck fleet) • Loaded date from DRC depot • Delivery status • Delivered quantity • Remarks / delays This register represents the final leg of the supply chain, linking inbound product at the DRC depot to actual customer consumption. 3. Expected Processing Logic (Power Query) The solution should: • Use Power Query to ingest all source Excel files from a shared OneDrive folder • Clean, standardize, and validate data (dates, truck numbers, depot names) • Link datasets using: o BL Number o Truck Number o Trip / Load ID o Customer / Order reference • Produce clean fact and dimension tables suitable for later migration to Power BI • Avoid VBA and Excel-only logic All transformation logic should be documented and reusable. 4. Dashboards & Outputs 4.1 BL & Hospitality Overview • Active BL list
Hospitality expiry countdown • Volume lifted vs remaining per BL • Vessel and consignee visibility 4.2 Fleet Journey Status (Long Haul) • Truck count by journey segment: o Dar–Iringa o Iringa–Mbeya o Mbeya–Tunduma o Tunduma border o Nakonde–Sakania o Sakania border o Destination depot • Trucks waiting due to documents, customs, or operational reasons 4.3 Depot Volume Visibility • Origin depot available volume • In-transit volume • Destination depot stock, ullage, and delivery rate 4.4 Fleet Availability & Workshop Cycle • Available trucks • In transit • Empty return • In workshop • Ready for next loading 4.5 Last-Mile Distribution (DRC) • Orders received vs fulfilled • Volume available in depot vs committed orders • Last-mile fleet utilization (18 trucks) • Pending deliveries and delivery delays
5. Risk, Constraint & Early-Warning Indicators The solution should be capable of building rule-based early warning flags to highlight operational risks arising from mismatches across the supply chain. Examples include (not exhaustive): • Inbound volume vs destination depot capacity (Loaded trucks en route exceeding available ullage) • Ready-to-load volume vs empty-truck availability at origin (Risk of hospitality expiry or demurrage) • Empty-truck return capacity exceeding available product (Idle fleet risk) • Customer orders vs depot stock availability (DRC) (Last-mile service risk) • Border congestion or prolonged dwell times (Journey imbalance risk) At this stage, the requirement is to: • Design the data model and dashboard framework such that these triggers can be implemented • Visually highlight risk / warning / normal states on dashboards • Allow future extension to alerts or notifications (e.g., Power BI alerts) Detailed thresholds and business rules will be finalized later. 6. Technology & Design Expectations • Primary build: Excel + Power Query • Source files: Excel (OneDrive) • Dashboard file: Excel (read-only for management) • Data model must be Power BI compatible • Naming conventions, mappings, and transformations must be documented • The solution should allow future migration to Power BI with minimal rework
7. Deliverables 1. Power Query–enabled Excel file(s) 2. Clean data model (facts & dimensions) 3. Operational dashboards (Excel); that can later migrate to Power BI 4. Avoid VBA and Excel-only hacks 5. Give the Power Query logic (and not lock it) 6. Brief documentation covering: o Data sources o Refresh process o Table relationships o Assumptions