Tableau de Bord Logistique Excel : Construction Optimale Sans Template

Votre entreprise logistique croît. Les emails WhatsApp de statut colis ne suffisent plus. Vous avez besoin d’une vue globale en temps réel.

Solution classique : acheter Tableau, Power BI, ou un logiciel SaaS coûteux (500-2000€/mois).

Mais il existe une 3e voie : construire votre propre tableau de bord logistique dans Excel, optimisé, scalable, sans payer.

Ce guide montre comment le construire correctement (pas juste « un spreadsheet »), en évitant les pièges qui rendent les dashboards Excel cassants et inutiles.

Pourquoi un Tableau de Bord Logistique ?

Cas réel : Entreprise e-commerce Casablanca, 500 colis/jour, sans tableau bord. Résultats :

❌ Responsable logistique estime « 90% à temps » (vraiment 75%)
❌ 2 transporteurs non fiables, personne ne le sait
❌ Retards fréquents, cause inconnue (entrepôt ? transporteur ?)
❌ Clients mécontents (pas de tracking)
❌ Réunions management débouchent sur rien (pas de data)

Après tableau bord Excel :

✅ Taux livraison à temps prouvé : 75% réel
✅ Identifiés 2 transporteurs = remplacement rapide
✅ Découvert : entrepôt traîne 8h/jour (action : nouveau dispatcher)
✅ Taux livraison monté : 75% → 94% en 6 semaines
✅ Clients satisfaits (taux retour -3 points)
✅ ROI : identifié 500K MAD coûts inutiles/an

Clé du succès : Tableau bord bien construit force honnêteté. Data n’a pas d’opinion.

Architecture Optimale : 4 Niveaux

Niveau 1 : Données Atomiques (Raw Data)

Définition : Chaque ligne = 1 colis expédié. Jamais résumé.

Pourquoi : Vous pouvez ensuite agréger n’importe comment (par jour, transporteur, zone, client).

Colonnes minimales :

Colonne	Type	Validation
ID Colis	Unique	Pas duplicates
Date Expédition	Date	MM/JJ/AAAA
Heure Expédition	Heure	08:30
Client	Texte	Dropdown list
Destination (Région)	Texte	Casa/Rabat/Fes/Marrakech
Poids (kg)	Nombre	>0
Transporteur	Texte	Dropdown (Express Pro, etc.)
Coût Transport	Devise	MAD
Date Livraison Prévue	Date	MM/JJ/AAAA
Date Livraison Réelle	Date	MM/JJ/AAAA (si livré)
Statut Livraison	Dropdown	À Temps / Retard 1j / Retard 2j+ / En Transit / Retour / Perte
Raison Retard (si)	Texte	Rupture routière / Mauvais adresse / Client absent
Notes	Texte libre	Observations terrain

Important : N’ajoutez PAS de formules ici. Données brutes uniquement.

Niveau 2 : Couche Transformation (Calculs)

Ici vivent vos formules. Jamais dans Raw Data.

Colonne calculée #1 : Délai (jours)

=IF(ISBLANK(F2),"",F2-E2)

Résultat : nombre jours entre prévue et réelle. Vide si pas livré encore.

Colonne calculée #2 : Statut Automatique (if manquant)

=IF(ISBLANK(F2),"En Transit",IF(F2<=E2,"À Temps",IF(F2<=E2+1,"Retard 1j","Retard 2j+")))

Colonne calculée #3 : Coût/kg

=C2/D2

Permet identifier clients/zones trop coûteux à livrer.

Niveau 3 : KPI Résumés (Summary Stats)

Sheet séparée « KPI Summary »

A. KPI Globaux (Tous colis, tous temps)

Taux À Temps % = COUNTIF(Statut,"À Temps") / COUNTA(Statut) * 100 Délai Moyen = AVERAGE(Délai) Coût Moyen/kg = AVERAGE(Coût/kg) Total Colis = COUNTA(ID) Total Poids = SUM(Poids) Total Chiffre HT = SUM(Coût Transport)

B. KPI par Période (Jour/Semaine/Mois)

Utiliser SUMIFS pour filtrer par date :

Colis Aujourd'hui = COUNTIF(Date,TODAY()) Colis Cette Semaine = COUNTIFS(Date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-7), Date,"<="&TODAY()) Colis Ce Mois = COUNTIFS(Date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), Date,"<="&TODAY())

C. KPI par Transporteur

Scorecard :

Express Pro: - Colis = COUNTIF(Transporteur,"Express Pro") - À Temps % = COUNTIFS(Transporteur,"Express Pro", Statut,"À Temps") / COUNTIF(Transporteur,"Express Pro") * 100 - Coût Moyen = AVERAGEIF(Transporteur,"Express Pro", Coût/kg) - Score = IF(À Temps %>=95,"⭐⭐⭐⭐⭐",IF(À Temps %>=85,"⭐⭐⭐⭐","⭐⭐⭐"))

Répéter pour chaque transporteur.

D. KPI par Région/Zone

Casablanca: - À Temps % = COUNTIFS(Région,"Casa", Statut,"À Temps") / COUNTIF(Région,"Casa") * 100 - Délai Moyen = AVERAGEIF(Région,"Casa", Délai)

Heatmap visuelle : Casa 92%, Rabat 88%, Fes 85% = identifier zone faible.

Niveau 4 : Dashboard Visuel (Presentation)

C'est ce que voient les managers.

Layout recommandé :

┌─────────────────────────────────────────────┐ │ TABLEAU DE BORD LOGISTIQUE │ │ Période : Sept 2027 | Mise à jour : Auj │ ├────────────┬────────────┬────────────────────┤ │ À Temps % │ Délai Moy │ Coût/kg │ │ 94% 🟢 │ 1.2 jours │ 2.15€ 📉 │ ├────────────┴────────────┴────────────────────┤ │ TRANSPORTEURS (Scorecard) │ │ Express Pro | 97% ⭐⭐⭐⭐⭐ | 2.50€ | ✅ │ │ Eco Transp | 92% ⭐⭐⭐⭐ | 2.15€ | ✅ │ │ Budget Deliv | 78% ⭐⭐⭐ | 2.80€ | ⚠️ │ ├─────────────────────────────────────────────┤ │ ZONES (Heatmap Performance) │ │ Casa: 94% | Rabat: 91% | Fes: 87% | Marr:89% ├─────────────────────────────────────────────┤ │ TENDANCE DÉLAI (7 derniers jours) │ │ Graphique sparkline montrant trend │ └─────────────────────────────────────────────┘

Construction Étape par Étape

Étape 1 : Créer Structure Excel (30 min)

Sheet 1 : "Raw Data"

Colonnes A-M (voir table ci-dessus). Headers en gras, fond bleu.

Sheet 2 : "KPI Calcul"

Formules de synthèse (voir Niveau 3).

Sheet 3 : "Dashboard"

Layout visuel avec graphiques.

Sheet 4 : "Transporteurs"

Scorecard détaillé par prestataire.

Étape 2 : Ajouter Data Validation (15 min)

Sélectionnez colonne "Transporteur" → Données → Validation → Liste = "Express Pro, Eco Transp, Budget Deliv"

Même pour "Région" et "Statut Livraison".

Bénéfice : Pas d'erreurs typo = data propre.

Étape 3 : Formules Intelligentes (30 min)

Copier les formules KPI du Niveau 3.

Attention : Utiliser références entières ($A:$A) pas limites (A2:A100) = dynamique.

Étape 4 : Visualisations (30 min)

Insérer → Graphique pour :

📊 Jauge % À Temps (Kpi #1)
📈 Ligne Délai Moyen (Trend 7j)
📋 Table Transporteurs (Scorecard)
🗺️ Heatmap Zones (si data)

Étape 5 : Conditional Formatting (15 min)

Mettez en surbrillance :

🟢 Vert : KPI OK (>95%)
🟡 Orange : KPI Moyen (85-95%)
🔴 Rouge : KPI Mauvais (<85%)

Résultat : Directeur voit rouge = action urgente (sans lire chiffres).

Maintenance Quotidienne

Morning (5 min) :

Ouvrir Excel
Raw Data → Ajouter colis expédiés hier
Remplir colonnes
Sauvegarder

Soir (2 min) :

Raw Data → Mettre à jour statuts livraisons (de transporteur API ou SMS)
Sauvegarder

Résultat : KPI se recalculent AUTOMATIQUEMENT.

Lundi matin (10 min) :

Ouvrir Dashboard
Prendre screenshot
Envoyer à management / clients
Identifier 1-2 actions de la semaine

Optimisations Avancées

Slicer par Période

Insérer → Slicer → Date expédition.

Cliquez "This Week" = Dashboard recalcule pour semaine courante seulement.

Cliquex "Last Month" = données mois passé. Comparer trend.

Pivot Table (Pour Deep Dives)

Sélectionnez Raw Data → Insertion → Pivot Table.

Rows: Transporteur, Columns: Statut Livraison, Values: Count.

Résultat : Matrice montrant exactement performance chaque transporteur (5 min).

Alertes Automatiques

Créer colonne "Alert" :

=IF(Délai>2,"RETARD CRITIQUE",IF(Délai>1,"RETARD","OK"))

Puis Conditional Formatting rouge si "RETARD CRITIQUE".

Chaque retard 48h+ est surligné rouge = action immédiate.

Cas d'Étude Complet : Implementation 2 Semaines

Entreprise : Logistique Fes, 200 colis/jour, 3 transporteurs.

Semaine 1 :

Jour 1-2 : Structure Excel créée
Jour 3 : Data validation + formules
Jour 4-5 : Data historique 4 semaines importée (10h travail assistant)

Semaine 2 :

Jour 1 : Dashboard visuels created
Jour 2 : Formation équipe (30 min)
Jour 3-5 : Maintenance quotidienne (validation processus)

Découvertes Immédiates :

✅ Transporteur A : 96% à temps (champion) → augmenter volume
✅ Transporteur B : 82% à temps, 3.20€/kg (trop cher) → réduire
✅ Retards concentrés Wednesday = pic de charge, ajouter transporteur 4e jour
✅ Fes-Meknes route prend moyenne 2.5 jours (vs 1.5 attendu) = enquête

Actions :

Réassignés 30% volume de B vers A
Recherche 3e transporteur mercredi
Changé itinéraire Fes-Meknes (gain 8h)

Résultats (1 mois) :

✅ Taux à temps : 85% → 94%
✅ Coût/kg : 2.60€ → 2.35€ (-10%)
✅ Satisfaction client : +35%
✅ Économies : 80,000 MAD/an identifiées

Pièges Courants (Et Solutions)

❌ "Statut" tape main à chaque fois → Solution : Dropdown validation
❌ Formules cassent si données manquantes → Solution : =IFERROR(formule,0)
❌ Dashboard mélange calculs + visuels → Solution : 3 sheets séparées
❌ Données historique perdue → Solution : Backup auto Google Drive
❌ Gère manuellement retour mails → Solution : Export API transporteur direct

Scaling : Quand Passer à Power BI ?

Excel suffisant si <10,000 lignes/mois (300/jour).

Au-delà : considérez Power BI (mais coûteux, apprentissage long).

Conseil : Maîtrisez Excel d'abord, pivot vers BI plus tard.

Conclusion

Un tableau de bord logistique Excel bien construit = plus puissant qu'un logiciel coûteux mal utilisé.

À vous : Construction = 3-4 heures. Maintenance = 5 min/jour. Valeur = 100,000+ MAD économisées/an.

Investissez le temps maintenant. Les retours seront énormes.

Templates Excel logistique prêts-à-utiliser ? Consultez logique.ma.