Britannia B2B Supply Chain Analytics

Comprehensive Project Documentation

1. Britannia Trade & Distribution Ltd — Business Model & Supply Chain Framework

▼

1.1 Company Profile & Value Proposition

Britannia Trade & Distribution Ltd operates in the critical middle echelon of the British supply chain. As a non-manufacturing stockist and wholesale distributor, Britannia creates economic value by:

Aggregating Upstream Supply: Procuring large multi-container or pallet volumes from domestic manufacturers and international importers, breaking bulk into trade-sized orders.
Holding Regional Inventory: Operating regional distribution centres to guarantee 1–3 day delivery windows across England, insulating downstream buyers from factory lead times.
Extending Commercial Credit: Buffering customer working capital through tiered payment terms (Immediate, Net 15, Net 30, Net 60, Net 90).
Catalog Consolidation: Enabling B2B clients to satisfy procurement needs across 14 diverse product lines through a single master vendor relationship.

60 Suppliers

Domestic & Overseas

Inbound Freight

5 Delivery Modes

4 Regional DCs

Perpetual Stock Buffer

Outbound Shipping

SLA-Monitored Freight

4,000 B2B Clients

4 Commercial Segments

1.2 Multi-DC Regional Logistics Network

Britannia routes customer purchases through 4 strategic distribution centres across England:

Distribution Centre	Warehouse Region	Primary Geography Served	Operational Role & Routing Logic
Coventry Central Fulfilment Hub	Midlands	Midlands, Yorkshire, Northwest England	The network workhorse: handles 64.3% of company volume (£952M). Also fulfills overflow for the unserviced Southwest region.
Bristol Portbury Logistics Park	West	Wales, Somerset, Devon, Cornwall	Western hub: processes £263.7M. Manages western international maritime export shipments.
Lutterworth National DC	Southeast	Home Counties, South England corridor	Specialist high-velocity facility adjacent to the M1/M6 Golden Triangle. Ships £154.9M.
Dagenham East Distribution Centre	Northeast	London, Essex, East Anglia, Southeast corridor	Metropolitan & export gateway hub handling £113M, near deep-sea shipping channels.

1.3 Four Distinct Commercial Customer Segments

Every account is categorized into one of four immutable customer segments, governing commercial credit eligibility, ordering frequency, and basket sizes:

Wholesale (Bulk Distributors): Accounts purchasing full pallet lots for sub-distribution. Accounts for ~£1.20Bn (81%) of revenue. Average order value: £29,525. Highest on-time fulfillment priority.
Business (Corporate & Institutional): Hotels, construction contractors, NHS hospital trusts, schools, and professional service firms purchasing consumables and operational goods. Generates £235.9M across 42,836 orders. Average order value: £5,809.
Retail (Independent Merchants): Independent hardware shops, community pharmacies, specialty sports outlets, and stationers stocking storefronts. High transaction frequency (54,434 orders) totaling £29.2M. Average order value: £562.
Online (E-Commerce Sellers): Pure-play e-retailers and marketplace sellers. Rapid order turnover (35,500 orders) generating £13.7M. Fastest-growing segment (+98.3% annual expansion), but subject to highest return rates (9.4%). Average order value: £392.

1.4 The 14 Product Categories & Seasonality Profiles

Britannia’s catalog encompasses 340 distinct commercial SKUs grouped into 14 categories with distinct seasonal demand cycles:

Automotive · Baby · Books · Clothing · Electronics · Grocery · Health · Home · Industrial · Movies · Office · Sports · Tools · Toys

Key seasonal trends include: Electronics & Toys peaking heavily during Q4 holiday trade (November–December); Tools & Industrial accelerating in March–May during commercial infrastructure ramp-ups; and Sports & Health spiking in January around fitness procurement cycles.

2. Data Engineering & Quality Audit — The 39-Point Master Issue Register

▼

Prior to dashboard development, the underlying sales and procurement data underwent a rigorous 4-phase audit. Rather than accepting raw synthetic outputs, 39 systemic defects were cataloged, diagnosed, and resolved to ensure enterprise-grade analytical validity.

Flaw D1: Denormalized Same-Day Rows

Originally, sales and purchases existed in a single flat file sharing identical transaction dates. Goods were recorded as "sold" before or on the exact day they were received from suppliers. The dataset was split into independent sales.csv (178,272 rows) and purchases.csv (37,707 rows), joined strictly via conforming product and warehouse dimensions with verified lag constraints.

Flaw R3-VI: Complete Price-Cost Collapse

In audit Pass 3, unit prices across all 14 categories were discovered to have collapsed into an unrealistic £66–£71 band (e.g. televisions costing £34 identical to bulk bags of rice). The pricing engine was completely reconstructed from ground truth reference points, establishing authentic price tiers from £8 (Grocery/Office) to £850+ (Electronics/Industrial machinery).

Flaw R2-D: 91.4% Negative Warehouse Stock

Tracking cumulative inventory per product-DC pair revealed that 91.4% of SKUs dipped into negative perpetual stock balances due to unhedged outbound surges. A dynamic safety-stock replenishment algorithm was built, generating 7,636 top-up purchase orders to eliminate stockout violations across all 4 hubs.

Critical DAX Fix: Rebuilt [Total COGS]

The initial Power BI [Total COGS] DAX formula utilized a cross-fact SUMX with context transition that silently dropped 78% of inventory cost, creating wildly inflated margin reports. The measure was re-engineered to iterate over the product dimension (VALUES(dim_products[product_id])), achieving exact penny reconciliation against company totals.

2.1 Master Issue Register (39 Tracked Defects & Resolutions)

Unified ID	Issue Name & Description	Severity	Tables Affected	Root Cause & Remediation	Status
D1	Sales & Purchases on same row / same date	Critical	Sales & Purchases	Denormalized shortcut. Split into dual fact tables; enforced sale date ≥ delivery date.	Fixed
D2	Single-month data limitation (Jan only)	Critical	Sales & Purchases	Synthetic scope limitation. Expanded generation to full 12 calendar months with seasonal multipliers.	Fixed
D3	Zero price / cost variance across catalog	Critical	Sales & Purchases	Static default values. Applied Gaussian distributions around industry standard category price seeds.	Fixed
D4	Single warehouse & region topology	High	Sales & Purchases	Monolithic model. Expanded to 4 regional DCs and 6 customer trade regions.	Fixed
R1-01	Selling price below landed cost in 42.6% of rows	Critical	Sales & Purchases	Unbounded discounting. Enforced minimum gross margin floors by category (min 15% on non-clearance).	Fixed
R1-02	Uniform delivery lead times across ship modes	Critical	Sales & Purchases	Shared duration generator. Calibrated realistic durations (Overnight: 1-2d, Sea: 35-45d).	Fixed
R1-05	Uniform category purchasing (no B2B affinity)	Critical	Sales	Pure random SKU selection. Assigned commercial category clusters (e.g. pharmacies buy Health/Baby).	Fixed
R2-B	11,041 backorders flagged as Early/On-Time	Critical	Sales	Decoupled status logic. Enforced rule: any backordered shipment is automatically classified as "Late".	Fixed
R2-D	91.4% negative perpetual stock balances	Critical	Both	Unsynchronized sales vs. PO pacing. Injected 7,636 automated safety-stock replenishment POs.	Fixed
R3-I	Non-UK geography label ("Midwest") in schema	High	Both	US terminology carry-over. Standardized to "Midlands" aligned with UK administrative zones.	Fixed
R3-VI	Category price collapse (£66–71 across all 14)	Critical	Both	Formulaic normalization bug. Re-seeded catalog with authentic category price anchors.	Fixed
R4-01	Warehouse name / region ID mismatch	Critical	Both	Key mapping transposition. Reconciled dimensional warehouse surrogate keys to geographic locations.	Fixed
R4-02	Partial shipment flag inverted in 39.5% of POs	Critical	Purchases	Boolean evaluation threshold error. Verified received_qty < ordered_qty logic.	Fixed

3. Star-Schema Semantic Data Model & Core DAX Measure Library

▼

3.1 Semantic Data Model Architecture

The Power BI solution is structured around an enterprise star-schema consisting of two independent fact tables joined exclusively through shared, conforming dimensional hierarchies:

fact_sales_orders (178,272 rows): Outbound commercial order lines, capturing transaction dates, customer references, warehouse fulfillment nodes, shipped/returned quantities, discounts, transport costs, and service level achievements.
fact_purchase_orders (37,707 rows): Inbound procurement orders, tracking vendor commitments, DC receipts, delivery methods, lead times, purchase unit costs, freight expenses, and inspection quality flags.
Conforming Dimensions: dim_customers (4,000 accounts), dim_products (340 SKUs across 14 categories), dim_warehouses (4 regional hubs), dim_suppliers (60 panel vendors), dim_date (continuous date dimension), and MapPoints (geocoded coordinates for Azure Map layers).

Architectural Cardinality Note

Sales orders and purchase orders share no direct foreign key. Cross-fact calculations (such as matching inventory purchase costs against sales revenues to derive gross profit margins) must be resolved at the shared dim_products grain via context transition in DAX.

3.2 Core DAX Measure Library

Total Revenue

Total Revenue = SUM(fact_sales_orders[sales_revenue])

Total Purchase Cost

Total Purchase Cost = 
SUMX(
    fact_purchase_orders, 
    fact_purchase_orders[ordered_quantity] * fact_purchase_orders[purchase_unit_cost]
)

Corrected Total COGS (Cross-Fact Dimension Iteration)

Total COGS = 
SUMX(
    VALUES(dim_products[product_id]),
    CALCULATE(SUM(fact_sales_orders[net_quantity]))
    *
    CALCULATE(
        DIVIDE(
            SUMX(fact_purchase_orders, fact_purchase_orders[ordered_quantity] * fact_purchase_orders[purchase_unit_cost]),
            SUM(fact_purchase_orders[ordered_quantity])
        )
    )
)

Gross Margin Proxy & Gross Margin %

Gross Margin Proxy = [Total Revenue] - [Total COGS]

Gross Margin % = DIVIDE([Gross Margin Proxy], [Total Revenue], 0)

On-Time Delivery Rate & Inbound Fill Rate

On-Time Rate = 
DIVIDE(
    COUNTROWS(FILTER(fact_sales_orders, fact_sales_orders[service_level_status] = "On-Time")), 
    COUNTROWS(fact_sales_orders)
)

Inbound Fill Rate = 
DIVIDE(
    SUM(fact_purchase_orders[received_quantity]), 
    SUM(fact_purchase_orders[ordered_quantity])
)

Supplier Quality Rate & Partial Shipment Rate

Supplier Quality Rate = 
DIVIDE(
    COUNTROWS(FILTER(fact_purchase_orders, fact_purchase_orders[quality_flag] = "Issue")), 
    COUNTROWS(fact_purchase_orders)
)

Partial Shipment Rate = 
DIVIDE(
    COUNTROWS(FILTER(fact_purchase_orders, fact_purchase_orders[partial_shipment_flag] = TRUE())), 
    COUNTROWS(fact_purchase_orders)
)

Month-over-Month Revenue Waterfall Display

Waterfall MoM Revenue Display = 
VAR CurrentRevenue = [Total Revenue]
VAR MinSelectedDate = CALCULATE(MIN(dim_date[date]), ALLSELECTED(dim_date))
VAR CurrentMonthStart = MIN(dim_date[date])
VAR IsFirstMonth = CurrentMonthStart = MinSelectedDate
VAR PreviousRevenue = 
    CALCULATE([Total Revenue], DATEADD(dim_date[date], -1, MONTH))
RETURN
IF(
    IsFirstMonth,
    CurrentRevenue,
    IF(ISBLANK(PreviousRevenue) || ISBLANK(CurrentRevenue), BLANK(), CurrentRevenue - PreviousRevenue)
)

Dynamic Map Tooltip On-Time Measure (TREATAS Context)

On-Time Rate (Map Tooltip) = 
VAR CurrentPointType = SELECTEDVALUE(MapPoints[PointType])
RETURN
SWITCH(
    TRUE(),
    CurrentPointType = "Customer Region",
        CALCULATE(
            [On-Time Rate],
            TREATAS(VALUES(MapPoints[PointName]), dim_customers[customer_region])
        ),
    CurrentPointType = "Distribution Centre",
        CALCULATE(
            [On-Time Rate],
            TREATAS(VALUES(MapPoints[PointName]), dim_warehouses[warehouse_name])
        )
)

4. The 7-Page Enterprise Dashboard Suite & Deep-Dives

▼

The Power BI suite features a standardized 1280×720 widescreen canvas with a 180px persistent left navigation rail. It delivers targeted, persona-driven analytics across seven specialized report pages:

Page 1: Executive Overview (C-Suite Health Verdict)

▼

Page 1: Executive Overview Power BI Dashboard Canvas

Figure 4.1: Executive Overview Report Canvas (1280×720) View in Showcase Hub

Audience: Chief Executive Officer, Managing Director, Operating Board.
Design Philosophy: A 5-second operational health scan. Top row cards provide the binary verdict, middle visuals reveal segment/channel distribution, and the right-hand Issue Trend Trio tracks emerging operational risks.

KPI Row (6 Cards with RAG Conditional Formatting):
- Total Revenue: £1,483.84M
- Total Orders: 178,272 orders
- On-Time Rate: 72.7% RED (<85%)
- Return Rate (Units): 6.7% AMBER (5-10%)
- Avg Lead Time: 15.5 days GREEN (<20d)
- Supplier Quality Rate: 6.3% RED (>5%)
Revenue Trend (Line): Monthly revenue plotted alongside the 3-Month Rolling Average to smooth seasonal buying waves.
Service Level Donut: Order distribution across On-Time, Late, Very Late, and Cancelled, with a dynamic central On-Time callout.
Segment × Region Stacked Bar: Wholesale dominance highlighted across all UK regions.
Top 5 Product Categories: Industrial (£539.5M) and Electronics (£238.9M) commanding the top rankings.
Issue Trend Trio: Full-height vertical stack pairing rate KPI sparklines with 1-decimal volume cards for Cancellations (11.8K), Backorders (15.9K), and Returns (18.8K).

Page 2: Sales Performance & Revenue Analytics

▼

Figure 4.2: Sales Performance & Revenue Analytics Report Canvas View in Showcase Hub

Audience: Commercial Director, Sales Managers, Corporate Finance.
Core Inquiries: Where is revenue originating, and what discount margin concessions are being surrendered?

Revenue by Month & Segment (Stacked Area): Illustrates seasonal acceleration into Q4, driven primarily by Wholesale volume.
Revenue by Category (Clustered Bar with % Share): Clear visual weighting showing Industrial commanding 36.4% and Electronics 16.1% of company turnover.
Discount Elasticity Analysis: Analyzes Revenue per Order across discount buckets (0–5%, 5–10%, 10–15%, 15–20%, 20–30%), confirming the 5–10% bracket maximizes revenue without diluting margins.
Custom TopoJSON Shape Map (britannia_5regions_final.topojson): Saturated choropleth of UK regions (Midlands, Northeast, Northwest/West, Southeast, Southwest) with International revenue filtered for separate handling.
Ship Mode Economics (Dual Axis): Outbound volume bars against Transport Cost per Order line, contrasting economical Rail/Sea with premium Overnight modes.
MoM Waterfall Visual: Tracks month-by-month revenue expansion and contraction with dynamic delta tooltips and direction indicators.

Page 3: Fulfillment & Order Quality

▼

Figure 4.3: Fulfillment & Order Quality Report Canvas View in Showcase Hub

Audience: Operations Director, Distribution Centre Managers, Quality Assurance.
Focus: Service level agreements, delivery lead times, backorders, and return rates.

Service Level by Warehouse (100% Stacked Bar): Compares On-Time, Late, Very Late, and Cancelled proportions across all 4 DCs, revealing consistent ~73% on-time performance across sites.
On-Time Trend by DC (Multi-Line): Uncovers the steep Q4 network-wide drop from 74% down to 63% during November–December peak volumes.
Backorder Matrix Heatmap: 14 categories × 4 quarters cross-tabulation, demonstrating that backorders are uniformly spread (8.5%–9.6%) rather than isolated to specific categories.
Return Rate by Category: Highlights the massive Clothing return anomaly (21.0%), more than double the next category (Electronics at 9.1%).
Full vs. Partial Returns: 100% stacked bar confirming all customer segments exhibit an identical 73% full / 27% partial return split.
Category Fill Rate Scatter: Plots total ordered units against fulfillment percentage, showing tight clustering between 93.5% and 97.0%.

Page 4: Procurement & Supplier Intelligence

▼

Figure 4.4: Procurement & Supplier Intelligence Report Canvas View in Showcase Hub

Audience: Procurement Director, Strategic Sourcing, Inbound Logistics.
Core Inquiries: Vendor reliability, lead-time predictability, supplier quality, and spend concentration.

KPI Row: Total Spend (£1.24Bn), Inbound Fill Rate (98.4%), Supplier Quality Issue Rate (6.3%), Partial Shipment Rate (42.4%), Active Suppliers (60).
Top 10 Suppliers by Spend: Identifies high vendor concentration: top 10 suppliers account for 53.1% of total spend, led by Supplier 354 with 19% (£235M).
Lead Time vs. Cost by Delivery Method: Proves inbound Express Overnight is economically dominated by Air Express (slower by 1 day and costs £137 more per order).
Supplier Defect League Table: Top 15 suppliers by quality defect rate, tracking vendors exceeding 10% issue rates.
Partial Shipment Analysis: Identifies the 8 suppliers running >90% partial shipments, driving operational instability.
Purchase Cost Trend Small Multiples: 14 mini line charts tracking monthly purchase unit costs per category, revealing Industrial costs rising steadily from £83.84 to £95.30.

Page 5: Warehouse & Logistics Management

▼

Figure 4.5: Warehouse & Logistics Management Report Canvas View in Showcase Hub

Audience: Head of Logistics, Transport Managers, Facility Supervisors.
Focus: Throughput balance, material flow equilibrium, freight cost ratios, and geographic reach.

Throughput Summary Table: Highlights Coventry Central's dominance (£952M revenue, 64.3% share) compared to Dagenham East (£113M, 7.6%).
Inbound vs. Outbound Material Flow: Surfaces major inventory surpluses at Dagenham East (+1.3M units) and Bristol Portbury (+1.1M units), contrasting with Coventry’s balanced flow (+0.1M units).
Transport Efficiency Index: Quantifies freight spend relative to revenue: Coventry is highly cost-efficient (0.81% freight cost ratio), while Dagenham East over-indexes at 1.64%.
Fulfillment Distance Distribution: Sourcing mileage categorized into Local (<580km), Regional (580–2150km), National (2150–4000km), and Distant (4000+km).
Azure Map Visualization: Sized and shaded bubbles representing distribution centres and customer delivery clusters across the UK.

Page 6: Product & Category Intelligence

▼

Figure 4.6: Product & Category Intelligence Report Canvas View in Showcase Hub

Audience: Commercial Category Managers, Merchandising, Pricing Teams.
Focus: Product profitability, catalog rationalization, margin proxy evaluation, and quality risk.

Category Revenue vs. Margin Proxy (Scatter):
- Industrial: Massive profit anchor (£539.5M revenue, 31.1% margin, £167.6M gross contribution).
- Clothing: Highest margin rate in the business (39.8%, £50.1M revenue).
- Electronics: High revenue (£238.9M) but thin margin rate (12.4%).
- Grocery: Structurally unprofitable (−2.5% margin on £15.8M sales).
Product Quality Risk Matrix: Triangulates Supplier Quality Rate, Return Rate, and Backorder Rate across all 14 categories.
Top 20 Products by Sales Volume: Ranks top-performing individual SKUs by gross revenue contribution.

Page 7: Customer Intelligence & Key Accounts

▼

Figure 4.7: Customer Intelligence & Key Accounts Report Canvas View in Showcase Hub

Audience: Commercial Director, Key Account Directors, Sales Operations.
Focus: Account revenue concentration, service level equity, and segment health.

Top 20 Revenue Concentration (Pareto Combo): Displays cumulative revenue curve reaching ~13% at rank 20, paired with an "All Others" callout card representing £1.29Bn (87%).
Segment Performance Matrix: Cross-tabulates revenue, order counts, revenue per order, return rates, and on-time percentages across Wholesale, Business, Retail, and Online.
Segment Revenue Trend with Dedicated Tile Slicer: Solves small-segment visibility by allowing users to toggle Wholesale/Business off so Retail and Online trends become clearly legible without distorting the Y-axis.
Top 20 Customers Table: Granular account rankings equipped with inline conditional data bars for revenue and color scales for return and on-time performance.

Underlying Navigation Layer: Drillthroughs & Custom Tooltip Pages

▼

Beyond the 7 primary canvases, the architecture includes dedicated contextual inspection layers:

Customer Details Drillthrough: Navigates from any customer visual into a full account dossier: benchmarked on-time rate, monthly spend trend, category mix, return history, payment terms, and granular order transaction log.
Supplier Details Drillthrough: Provides vendor audit capabilities: lead time distribution, delivery method breakdown, monthly receipt quality trends, and purchase order history.
Custom Report Page Tooltips:
- Transport Cost Detail Tooltip: Pops up over Page 5 matrix cells to reveal warehouse ID, ship mode, and cost per order.
- Customer Region Tooltip: Displays regional order counts and revenue over the Page 2 shape map.
- Regional Coverage Map Tooltip: Evaluates dynamic TREATAS DAX measures over Page 5 Azure Map points.

5. Strategic Stakeholder Analysis — 25 Executive Questions Answered

▼

The dashboard suite was validated against 25 specific operational and financial questions posed by Britannia’s senior leadership team. Key evidence and strategic verdicts are documented below:

Commercial Director / Sales Inquiries

Q1 Is revenue growth broad-based, or is one segment carrying the entire trend?

Comparing January to December revenue reveals balanced expansion across all three core business channels: Wholesale grew +38.6% (£90.9M to £126.0M), Business grew +36.0% (£20.0M to £27.2M), and Retail rose +25.0% (£2.4M to £3.0M). Online expanded by +98.3% (£847K to £1.68M), representing an emerging high-growth segment.

Verdict: Growth is healthy and broad-based across all commercial divisions. December peaks reflect seasonal gifting/year-end surges rather than structural volatility.

Q2 Are we dangerously dependent on a small handful of key accounts?

The top 20 customers (representing just 0.5% of the 3,999-account base) account for exactly 13.0% of gross turnover (£193M of £1.48Bn). The remaining 3,979 clients generate 87.0% of revenue.

Verdict: Zero account concentration vulnerability. Britannia exhibits exceptional commercial revenue diversification.

Q3 Is our discounting strategy generating incremental revenue or eroding margin?

Analyzing revenue per order across discount tiers confirms the 5–10% discount band generates the highest aggregate revenue across Wholesale (£361.0M), Business (£73.9M), and Online (£5.3M). Deeper discounts (20–30%) fail to generate higher basket values and result in severe margin erosion.

Crucially, Grocery is discounted at an average of 8.1% while producing a −2.5% gross margin. Every discounted grocery order accelerates cash losses. Books (7.8% discount, 1.4% margin) operates dangerously close to break-even.

Verdict: Eliminate discounting on Grocery immediately; enforce strict gross margin hurdle rates before commercial sales teams can apply discounts.

Q4 Which geographic regions are underperforming relative to distribution coverage?

Regional sales totals: Southwest (£286.57M), Northeast (£258.55M), International (£255.83M), West (£253.58M), Southeast (£220.94M), and Midlands (£208.36M).

Verdict: The Southwest is Britannia's #1 revenue generator despite having no dedicated DC, relying on long-haul fulfillment from Bristol and Coventry. Conversely, the Midlands lags in last place despite housing the main distribution hub.

Procurement Director Inquiries

Q5 Is the 42.4% partial shipment rate isolated to a few bad vendors or systemic?

Of 60 approved suppliers, 27 operate above the 42.4% company average. 8 suppliers exceed a 90% partial shipment rate, and an additional 5 sit between 80–90%.

Verdict: The defect is systemic across the vendor panel, though the top 8 offenders represent the most critical targets for immediate contractual SLA renegotiation.

Q6 Are we excessively concentrated in our supplier base?

The top 10 suppliers capture 53.1% of total procurement expenditure (£658.1M of £1,239.2M). Supplier 354 alone commands 19.0% (£235M) of all purchases.

Verdict: High supplier concentration presents substantial supply continuity risk. Multi-sourcing strategies are required for high-volume SKUs.

Q7 Do our premium inbound delivery methods justify their cost?

Inbound delivery metrics:

Sea Economy: 43 days lead time | £30.72 avg freight
Rail: 22 days lead time | £72.62 avg freight
Ground Standard: 15 days lead time | £224.59 avg freight
Air Express: 9 days lead time | £258.72 avg freight
Express Overnight: 10 days lead time | £396.05 avg freight

Verdict: Express Overnight is completely dominated by Air Express—it is 1 day slower yet £137.33 more expensive per PO. Shifting all 6,046 inbound Overnight POs to Air Express saves £830,300 annually with faster transit times.

Operations & Logistics Inquiries

Q9 Is the late-delivery problem localized to one warehouse, one carrier, or network-wide?

Full-year on-time delivery rates are uniform across all 4 hubs: Coventry Central (72.8%), Bristol Portbury (72.4%), Lutterworth National (73.1%), and Dagenham East (72.1%). However, during November and December, on-time rates plunged across all DCs from 74% down to 63%.

Verdict: Late deliveries stem from network-wide Q4 peak congestion and carrier capacity constraints, not single-site operational failures.

Q11 Are high return rates in Clothing and cancellations in Toys explainable?

Clothing (21.0% return rate): Traces directly to inbound supplier quality, where Clothing tops the catalog with a 7.7% defect rate. Despite this, Clothing yields Britannia’s highest gross margin rate (39.8%).
Toys (9.8% cancellation rate): Driven by stockouts during severe Q4 holiday demand spikes (£399K/mo rising to £1,695K in December), compounded by zero inventory allocation from Coventry Central.

Verdict: Both represent operational bottlenecks rather than random consumer behavior: supplier fabrication quality for Clothing, and peak seasonal inventory allocation for Toys.

Q14 Do any distribution centres sit on excess dormant inventory?

Comparing annual received units against net outbound shipments:

Dagenham East: +1.3 Million units surplus (~31% of outbound volume)
Bristol Portbury: +1.1 Million units surplus
Lutterworth National: +0.7 Million units surplus
Coventry Central: +0.1 Million units surplus (near-perfect balance)

Verdict: Dagenham East and Bristol Portbury are heavily over-stocked, tying up working capital. Inbound purchase allocations should be redirected toward Coventry Central.

Category Management & Financial Cross-Functional Inquiries

Q15 Which product categories are generating true commercial profit?

Following the DAX [Total COGS] correction, full-year gross margin reconciliation reveals:

Industrial: £539.5M revenue | 31.1% margin | £167.6M gross contribution (largest dollar generator)
Clothing: £50.1M revenue | 39.8% margin | £19.9M contribution (highest percentage margin)
Electronics: £238.9M revenue | 12.4% margin | £29.6M contribution (high volume, thin margin)
Books: £9.0M revenue | 1.4% margin (barely breakeven)
Grocery: £15.8M revenue | −2.5% margin | −£395K contribution (margin negative)

Verdict: Industrial and Clothing are the commercial profit engines. Grocery and Books require urgent price adjustments to prevent margin erosion.

Q22 If Britannia needs to cut operational costs immediately, where are the least disruptive levers?

Three immediate levers were identified:

Inbound Freight Mode Shift: Migrate 6,046 inbound POs from Express Overnight to Air Express, capturing £830,300 in immediate cash savings while improving delivery speed.
Plug the Grocery Margin Bleed: Eliminate the 8.1% discount and raise prices by 5% to restore Grocery to a positive +2.5% margin.
Working Capital Optimization: Stop safety-stock PO allocation to Dagenham East and Bristol Portbury until their 2.4M unit combined surplus is absorbed.

Verdict: The inbound freight shift alone achieves nearly £1M in annual savings without disrupting customer fulfillment.

Q25 Which single operational fix moves the largest number of KPIs simultaneously?

Enforcing strict inbound Supplier SLAs and QA Gateways targets the 42.4% Partial Shipment Rate and 6.3% Supplier Defect Rate.

Verdict: Improving inbound supplier compliance cascades through the entire value chain: reducing warehouse stockouts, lowering backorders (currently 8.9%), eliminating returns in high-defect categories like Clothing (21%), and elevating customer on-time fulfillment from 72.7% toward the 85%+ SLA target.

6. Strategic Action Plan & Implementation Roadmap

▼

Based on the 25 stakeholder findings, a phased implementation plan is outlined for executive execution:

Phase 1: Immediate Quick Wins (0–30 Days)

Inbound Freight Mode Shift: Mandate procurement re-route all inbound Express Overnight POs to Air Express, capturing £830,300 in annualized freight savings.
Grocery Margin Rescue: Freeze all sales discounting on Grocery SKUs and apply a 4.5% wholesale price index increase to return the category to positive contribution margins.
Inventory Rebalancing: Halt speculative PO replenishment into Dagenham East (+1.3M surplus) and Bristol Portbury (+1.1M surplus), routing inventory to Coventry Central.

Phase 2: Operational Stabilization (30–90 Days)

Supplier SLA Enforcement: Institute contractual financial penalties for the 8 suppliers operating above 90% partial shipment rates.
Clothing Supplier QA Audits: Commission third-party factory quality inspections on the top 3 Clothing vendors to address the root causes of the 21% return rate.
Q4 Peak Capacity Planning: Pre-book dedicated third-party logistics (3PL) linehaul capacity between October and December to prevent the seasonal on-time drop (74% → 63%).

Phase 3: Strategic Network Transformation (90–180 Days)

Southwest Logistics Feasibility Study: Assess commercial viability of opening a 5th regional fulfillment hub in Exeter/Plymouth to directly support the network's highest-revenue territory (£286.6M).
Payment Terms Migration: Transition high-credit-rating Net 30 suppliers toward Net 60 terms, freeing an estimated £15M–£25M in working capital liquidity.
Long-Tail Catalog Rationalization: Re-evaluate commercial viability of near-breakeven categories (Books at 1.4% margin and Movies at 10.6%) to optimize pallet space.

7. Technical Specifications & Data Dictionary

▼

7.1 Core Schema Entities

Table Name	Type	Row Count	Primary Key	Business Description
`fact_sales_orders`	Fact Table	178,272	`sale_id`	Transactional order lines fulfilling B2B customer purchases.
`fact_purchase_orders`	Fact Table	37,707	`purchase_id`	Inbound supplier purchase orders and warehouse receipts.
`dim_customers`	Dimension	4,000	`customer_id`	Customer master: business accounts, commercial segment, geographic territory.
`dim_products`	Dimension	340	`product_id`	Product catalog: category, subcategory, base unit cost, standard trade price.
`dim_warehouses`	Dimension	4	`warehouse_id`	Regional distribution centres: hub name, region, square footage, throughput capacity.
`dim_suppliers`	Dimension	60	`supplier_id`	Approved vendor panel: category specialisms, payment terms, geographical origin.
`dim_date`	Dimension	365	`date`	Standard fiscal calendar table supporting Time Intelligence DAX expressions.
`MapPoints`	Dimension / GIS	10	`PointName`	Geocoded latitude/longitude coordinates powering the Azure Map visualization.

7.2 Key Field Specifications

service_level_status Categorical metric (On-Time, Late, Very Late, Cancelled) evaluating delivery completion against ship mode SLA windows.

net_quantity Net revenue-generating units: shipped_quantity - return_quantity.

quality_flag Inbound receipt quality inspection outcome: binary indicator (Clean vs. Issue).

fulfillment_distance_km Inbound sourcing mileage: used to compute the 4 operational distance buckets.

8. Core Skills & Technical Competencies

▼

This enterprise project demonstrates multidisciplinary competencies across business intelligence, data engineering, supply chain modeling, and cartography:

Power BI & Service

Advanced DAX

SQL & Data Warehousing

Supply Chain & Logistics

TopoJSON & Azure Maps

Data Quality Auditing

Custom UI/UX Theme JSON

Executive Decision Support

9. Project Deliverables, Models & Source Code Downloads

▼

Access the complete suite of open-source deliverables, including the compiled Power BI workbook, enterprise SQL database schema, custom visual theme, TopoJSON cartographic shape files, and detailed audit logs:

Power BI Analytical Suite

Full 7-page interactive .pbix file with DAX measures, drillthroughs, and custom tooltips.

Get Power BI File (.pbix)

Enterprise SQL Database

Complete SQL DDL and relational schema script for sales, purchase orders, and dimensions.

Get SQL Database (.sql)

Power BI Theme File

Custom corporate theme configuration (.json) standardizing typography, colors, and RAG rules.

Get Theme (.json)

UK Regional TopoJSON Map

Custom TopoJSON cartographic boundary file engineered for Britannia's 5 UK customer sales regions.

Get TopoJSON (.topojson)

39-Point Data Quality Audit

Consolidated 800+ line technical audit log detailing all 39 synthetic and structural defect fixes.

View Audit Log on GitHub (.md)

25 Executive Insights Memo

Full executive analytical dossier answering all 25 stakeholder inquiries with verified data citations.

View Insights on GitHub (.md)

Complete open-source repository containing all datasets, DAX scripts, TopoJSON files, and documentation:

View Britannia-B2B-Supply-Chain-Analytics on GitHub