Background/Context: 

• The objective of this analysis is to determine whether transitioning to an in-house delivery model is commercially and operationally viable compared to the current outsourced logistics services. 
• Key analytic questions: 
  • What are the comparative costs and operational efficiencies of outsourced versus in-house logistics? 
  • What combination of vehicles will optimise in-house delivery efficiency while minimising operational costs and staffing needs? 
  • What route planning strategies can further reduce costs and environmental impact? 
  • What would be the associated capital investment and potential tax savings?

Project Development Process:

1.Data Cleaning Process 

The data cleaning process involved steps for checking missing values, duplicates and addressing incomplete data issues. Ways to merge the three sheets were evaluated. Unnecessary columns were removed from the DataFrame, and columns were renamed to ensure consistency. 

• Missing Values: missing values were handled with targeted approaches. Missing despatch dates were filled with random values based on delivery proportions, while missing delivery costs were excluded and weights calculated. A random forest model was used to address the high volume of missing values in ‘CubicMPerUnit’ to preserve data variance.
• Incomplete Postcode Data: to address incomplete postcode data, we generated a new DataFrame from Ordnance Survey Code-Point Open data and validated the provided Postal Sectors against this official dataset. This allowed us to calculate each postal sector centroid’s geographical coordinates. 
• DataFrame Merge: The order detail variables were aggregated based on their OrderID. This approach ensured that the merging process did not generate duplicates. After evaluating various merging methods, a left join was selected to maintain flexibility for future analysis. This choice was particularly beneficial for preserving data with unmatched rows that could have become relevant later.

2. Exploratory Data Analysis 

• Initial data exploration involved descriptive and basic visualisations. Outlier analysis was conducted using boxplots and descriptive statistics. However, following a thorough examination of the data and discussions with Altura, many identified outliers were determined to be valid and were subsequently included in the final analysis. 
• Excluded data points: weekend dispatch days, cancelled/ deleted orders, samples, direct orders dispatched by suppliers directly and orders above 15 pallets. This resulted in 5% of data points unsuitable for route planning. However, the costs of these points were manually added to the final monthly costs. 
• A cumulative analysis revealed that only 18.5% of orders fell within a 50-mile radius, prompting an extension to 75 miles, covering 33.7% of orders. Beyond this, our analysis highlighted that returns diminish. See our long-term recommendations below for potential strategic in-house delivery expansion options. 
• See Appendix 3 for detailed insights into the data cleaning and analysis.

3. Data Visualisation

• Visualisations were designed to convey insights to technical and business stakeholders, balancing functionality, accessibility and aesthetics. Types of Visualisations 
  • Bar graphs: used for categorical data to highlight magnitudes.
  • Line graphs: Depict trends, such as the cumulative percentage of orders by distance.
  • Maps: Intuitively display geographic data such as delivery routes and charging station locations. 
  • Tables: Present multiple data fields concisely where visualisation isn’t effective.
• Colour Scheme: 
  • Green, aligned with Altura branding, is the primary colour, paired with purple for contrast and readability. 
  • A green-purple gradient minimises visual clutter, except in maps where distinct colours are required to easily identify categories. 
  • Grey replaces purple in dual-category bar graphs to emphasise green, highlighting key data.
• Accessibility 
  • n the graph below, the colour palette was chosen for suitability with colour blindness, as colour is a key element of the map.
  • In other graphs, alternative methods such as varying bar sizes or adding labels ensured colour wasn’t the sole differentiator.
• Size & Layout 
  • Horizontal or vertical bars were selected to optimise slide layout in the presentation, with data labels replacing x-axes for clarity.