Tableau Excel de suivi des coûts logistiques de chantier

Ce que ce tableau aide à suivre

• Comparer, pour chaque lot, l’enveloppe HT à la somme des coûts réalisés et confirmés.
• Ventiler les dépenses entre transport, manutention, stockage et coût exceptionnel.
• Repérer les opérations planifiées ou confirmées dont la date prévue est dépassée.
• Identifier les informations manquantes avant la revue périodique du journal.

Contenu du classeur

Commencer à l’utiliser

1. Réglez les paramètres du chantier

Dans Paramètres, renseignez Date de référence, Seuil coût exceptionnel, Délai d’anticipation, puis adaptez les listes Statuts, Flux, Natures logistiques, Unités et Lots. Affectez une Enveloppe logistique HT à chaque lot utilisé.

2. Remplacez les lignes fictives

Conservez d’abord une copie du fichier, puis remplacez les saisies de démonstration dans Journal. Renseignez Identifiant, Date prévue, Statut, Flux, Lot et Nature logistique avant les informations de trajet, de quantité et de coût.

3. Qualifiez le coût et l’avancement

Saisissez les montants dans Transport HT, Manutention HT, Stockage HT ou Exceptionnel HT. Ajoutez Motif exceptionnel si nécessaire et Date réalisée lorsque Statut vaut Réalisé. Les montants acceptés sont positifs ou nuls.

4. Contrôlez les résultats calculés

Préservez les cellules grises, notamment Total HT et Contrôle dans Journal ainsi que les formules de Synthèse. Corrigez les messages de Contrôle, actualisez Date de référence, puis comparez Exposition HT, Solde HT et Taux consommé par lot.

Contenu des feuilles de suivi

Le classeur est organisé en trois feuilles qui séparent la saisie, les réglages et la lecture des résultats.

• Synthèse présente, par Lot, Enveloppe HT, Réalisé HT, Confirmé HT, Exposition HT, Solde HT et Taux consommé. Elle affiche aussi Total réalisé HT, Confirmé à venir HT, Exceptionnel réalisé HT et Opérations en retard.
• Journal contient une ligne par opération. Les champs couvrent l’identification, les dates, le Statut, le Flux, le Lot, la Nature logistique, l’itinéraire, le Prestataire, la Quantité, l’Unité, la Distance (km), les quatre postes de coût et le Commentaire.
• Paramètres regroupe Date de référence, Seuil coût exceptionnel, Délai d’anticipation, les listes de choix et les Enveloppe logistique HT des lots.

Les en-têtes du Journal restent visibles pendant le défilement et un filtre est disponible sur ses 21 colonnes.

Calculs, statuts et traitement des cellules vides

Dans Journal, Total HT additionne Transport HT, Manutention HT, Stockage HT et Exceptionnel HT avec une logique équivalente à IF(COUNTA(A5:R5,T5)=0,"",IF(D5="Annulé",0,SUM(N5:Q5))). Une ligne entièrement vide reste donc vide. Sur une ligne renseignée, un poste de coût vide est traité comme nul par SUM. Si Statut vaut Annulé, le total devient 0, même si des montants restent saisis.

Dans Synthèse, SUMIFS ventile les montants selon Lot et Statut. Exposition HT correspond à Réalisé HT plus Confirmé HT, puis Solde HT soustrait cette exposition de Enveloppe HT. Taux consommé repose sur IFERROR(E5/B5,"") : une division impossible renvoie une cellule vide, et non 0 %. De même, les recherches de lot emploient INDEX, MATCH et IFERROR ; une correspondance absente peut donc apparaître vide.

Une Enveloppe HT vide laisse Solde HT et Taux consommé vides. Cette absence ne signifie ni qu’aucun coût n’existe, ni que le lot est soldé : renseignez l’enveloppe ou vérifiez l’affectation du lot avant de conclure.

Cas pratique : interpréter un transport réalisé

Le cas de démonstration LOG-0001 comporte une Date prévue et une Date réalisée au 01/09/2026, le Statut Réalisé, le Flux Entrant et le Lot Gros œuvre. Transport HT est renseigné à 860,00 €, tandis que les autres postes de coût de cette ligne sont vides.

Le résultat calculé pour cette ligne est donc un Total HT de 860,00 €. Elle contribue à hauteur de 860,00 € au Réalisé HT du lot Gros œuvre et au réalisé du flux Entrant. Elle ne suffit pas à déterminer le total global, qui dépend des autres lignes conservées dans Journal.

Exemple hypothétique : si cette opération était la seule affectée au lot Gros œuvre, dont l’Enveloppe logistique HT de démonstration est de 32 000,00 €, l’Exposition HT serait de 860,00 €, le Solde HT de 31 140,00 € et le Taux consommé de 2,7 %. La décision consiste alors à confirmer l’imputation au bon lot avant de comparer ce solde avec les opérations confirmées à venir.

Erreurs courantes et corrections sûres

N’écrasez pas les formules lors du remplacement des données fictives. En cas d’anomalie, suivez le parcours symptôme, cause plausible, vérification ou correction, puis effet attendu.

• Total HT reste vide sur une ligne renseignée : la formule de la colonne S a pu être supprimée. Vérifiez-la par comparaison avec une ligne voisine et restaurez-la depuis une copie intacte ou par recopie relative. Le total doit ensuite additionner N à Q.
• Contrôle affiche « Date réalisée manquante » : Statut vaut Réalisé alors que Date réalisée est vide. Vérifiez la réalisation effective, puis renseignez la date au format jj/mm/aaaa ou corrigez le statut. Le message correspondant doit disparaître.
• La saisie d’un Identifiant est refusée : la valeur est vide ou existe déjà entre A5 et A204. Recherchez l’identifiant avec le filtre et attribuez une référence unique. La ligne pourra alors être validée sans doublon.
• Opérations en retard semble trop élevé : Date de référence peut être ancienne ou des opérations Planifié et Confirmé ont une Date prévue antérieure sans Date réalisée. Contrôlez Paramètres et chaque statut avant d’actualiser la date. L’indicateur sera recalculé sur la situation retenue.
• Contrôle affiche _xlfn ou une erreur de nom : la fonction TEXTJOIN peut ne pas être évaluée par l’environnement Excel utilisé. Ouvrez le fichier dans une version prenant cette fonction en charge sans remplacer la formule. Les messages de contrôle concaténés doivent redevenir lisibles.

Comparaison avec une feuille vide ou une liste manuelle

Ce classeur convient à un support périodique lorsque les coûts doivent être ventilés de façon constante. Une feuille vide ou une liste manuelle peut toutefois être plus adaptée à un besoin très court ou à une structure entièrement différente.

Choisissez ce fichier si vous conservez les catégories proposées et avez besoin d’une synthèse par lot. Préférez une feuille construite sur mesure si vos flux exigent d’autres relations ou plus de 200 lignes ; une liste manuelle reste pertinente pour quelques mouvements sans consolidation chiffrée.

Adapter les listes et les enveloppes

Les listes utilisées par Journal occupent des plages définies dans Paramètres : quatre Statuts, trois Flux, quatre Natures logistiques, cinq Unités et jusqu’à vingt Lots. Vous pouvez remplacer leurs libellés, mais les anciennes lignes du Journal doivent employer exactement les mêmes valeurs pour rester correctement regroupées par SUMIFS.

Pour ajouter des valeurs au-delà des plages prévues, il faut étendre avec précaution les validations, les formules de Synthèse et, pour les lots, les zones de recherche INDEX et MATCH. Une simple saisie sous la dernière cellule ne l’intègre pas automatiquement aux listes existantes.

Date de référence intervient dans Confirmé à venir HT et Opérations en retard. Seuil coût exceptionnel et Délai d’anticipation sont également disponibles dans Paramètres, mais ils ne produisent ni notification ni mise à jour autonome : contrôlez l’affichage du classeur après toute modification.

Périmètre et limites avant utilisation

Le Journal présente une limite de 200 opérations, de la ligne 5 à la ligne 204. Les calculs de Synthèse utilisent également ces bornes. Ajouter des lignes sans étendre le tableau, les validations et les plages SUMIFS peut laisser de nouvelles opérations hors des résultats.

Le fichier suit des montants hors taxes et ne comporte ni graphique, ni macro, ni synchronisation, ni notification. Il ne remplace pas un ERP, une pièce comptable, un document officiel ou une procédure de contrôle. Il ne garantit pas la conformité des transports, des factures ou du chantier.

La structure du classeur et la logique des calculs sont associées à Luc Martin. Le guide d’utilisation et la cohérence éditoriale sont associés à Claire Bernard.

Questions fréquentes