Ce tableur Excel de suivi des engagements enregistre les commandes et accords internes d’un chantier, leur imputation par lot et les montants facturés cumulés. Il s’adresse à la personne qui prépare un arrêté de coûts ou contrôle les dépenses restant à engager.
La feuille Synthèse présente l’enveloppe totale, l’engagé net, le reste à engager et l’exposition avant facturation. Cette dernière reste un repère interne : elle ne constitue ni un suivi individuel des factures et paiements, ni un montant comptable officiel.
Ce que ce tableau aide à suivre
- Connaître le montant engagé net et le reste à engager par rapport aux enveloppes de pilotage.
- Repérer les engagements incomplets, les incohérences de facturation et les expositions anciennes.
- Comparer les lots selon leur enveloppe, leur taux engagé et leur état budgétaire.
- Préparer un arrêté périodique en séparant les montants à valider des engagements déjà pris en compte.
Contenu du classeur
Commencer à l’utiliser
1. Renseignez le contexte du chantier
Dans Paramètres, remplacez les données fictives par le Nom du chantier, le Code chantier et la Date d’arrêté. Ajustez ensuite le Seuil de consommation, l’Ancienneté d’exposition et le Seuil d’exposition unitaire. Les cellules jaunes signalent les zones modifiables.
2. Préparez les enveloppes par lot
Dans Budgets par lot, sélectionnez un Lot puis saisissez son Enveloppe de pilotage HT. Conservez une seule ligne budgétaire par lot afin d’éviter de compter deux fois une enveloppe dans Synthèse.
3. Enregistrez chaque engagement
Dans Engagements, renseignez d’abord ID engagement et Date d’engagement, puis Statut, Nature, Contrepartie ou porteur, Objet, Lot, Centre de coût et les montants. Pour une Nature égale à Commande, renseignez aussi Référence commande/accord. Montant annulé HT et Facturé cumulé HT peuvent être laissés vides lorsqu’ils sont réellement nuls.
4. Vérifiez les résultats calculés
Préservez les colonnes calculées Engagé net HT, Exposition non facturée HT, Âge en jours et Alerte. Contrôlez ensuite les alertes dans Engagements, l’État dans Budgets par lot et les totaux de Synthèse avant de clôturer l’arrêté.
Contenu du classeur de suivi
Le fichier répartit le travail entre cinq feuilles complémentaires afin de distinguer les saisies, les calculs et la lecture de l’arrêté.
- Synthèse regroupe six indicateurs, le contexte du pilotage, un tableau par lot et un graphique.
- Paramètres contient le Nom du chantier, le Code chantier, la Date d’arrêté et les trois seuils utilisés dans les contrôles.
- Engagements comporte 17 champs, de ID engagement à Commentaire, avec une ligne par commande ou accord interne.
- Budgets par lot rapproche Enveloppe de pilotage HT, Engagé net HT, Reste à engager HT, Taux engagé et État.
- Listes fournit les valeurs proposées pour Statuts, Natures, Lots et Centres de coût.
Les lignes de démonstration sont fictives. Elles doivent être remplacées par vos saisies avant d’utiliser les totaux pour un chantier réel.
Calcul de l’engagé net et du reste à engager
Dans Engagements, Engagé net HT applique la logique =MAX(0,N(J2)-N(K2)) lorsque Statut vaut Engagé ou Soldé. Un statut À valider ou Annulé produit un engagé net nul. La fonction N traite un Montant annulé HT vide comme zéro, tandis que MAX empêche l’affichage d’un montant négatif.
Exposition non facturée HT est calculée par =MAX(0,N(L2)-N(M2)). Un Facturé cumulé HT vide est donc interprété comme zéro. Dans Budgets par lot, SUMIFS additionne les Engagé net HT dont le Lot correspond exactement à la ligne budgétaire, puis Reste à engager HT soustrait cet engagé de l’enveloppe. Un résultat négatif conduit à l’état Dépassement.
Le Taux engagé utilise =IFERROR(C2/B2,""). La même logique est appliquée au taux global de Synthèse. Si l’enveloppe est nulle ou si la division produit une erreur, la formule renvoie une chaîne vide : une cellule vide n’est pas un zéro affiché et ne doit pas être lue comme un taux de 0 %. Les lignes sans Lot renvoient également un affichage vide grâce à une formule de type =IF(A2="","",...).
Lecture des seuils et des alertes
La colonne Alerte examine les contrôles dans un ordre précis. Elle affiche À compléter si Date d’engagement, Statut, Nature, Objet, Lot, Centre de coût ou Montant initial HT manque, ou si une Commande ne possède pas de Référence commande/accord. Elle affiche ensuite Facturé > engagé lorsque Facturé cumulé HT dépasse Engagé net HT.
L’alerte Exposition ancienne apparaît seulement si deux conditions sont réunies : Exposition non facturée HT atteint au moins le Seuil d’exposition unitaire et Âge en jours dépasse l’Ancienneté d’exposition. L’âge correspond à la différence entre Date d’arrêté et Date d’engagement, avec un minimum de zéro.
Dans Budgets par lot, État devient Dépassement lorsque le reste est négatif, Seuil atteint lorsque le Taux engagé atteint le Seuil de consommation, et Disponible dans les autres cas prévus par la formule. Les mises en forme conditionnelles facilitent le repérage visuel, mais chaque signal doit être rapproché des données de la ligne.
Cas pratique : examiner une exposition ancienne
Les valeurs suivantes proviennent d’une ligne de démonstration fictive. L’engagement E-002 est daté du 15/06/2026, avec le statut Engagé. Montant initial HT vaut 120 000,00 €, Montant annulé HT 5 000,00 € et Facturé cumulé HT 50 000,00 €. La Date d’arrêté est fixée au 07/09/2026.
- Engagé net HT est calculé à 115 000,00 € : 120 000,00 € moins 5 000,00 €.
- Exposition non facturée HT est calculée à 65 000,00 € : 115 000,00 € moins 50 000,00 €.
- Âge en jours est calculé à 84 jours.
- Avec un Seuil d’exposition unitaire de 10 000,00 € et une Ancienneté d’exposition de 30 jours, les deux conditions sont remplies.
Le résultat calculé de démonstration est donc l’alerte Exposition ancienne. La décision utile consiste à vérifier si Facturé cumulé HT est à jour et si l’engagement reste actif. Les 65 000,00 € ne prouvent pas qu’une facture est en retard : ils représentent la part engagée non couverte par le cumul facturé saisi.
Erreurs fréquentes et corrections sûres
- Symptôme : Alerte affiche À compléter. Cause plausible : un champ obligatoire est vide ou une Commande n’a pas de Référence commande/accord. Vérification ou correction : contrôlez Date d’engagement, Statut, Nature, Objet, Lot, Centre de coût, Montant initial HT et, pour une commande, sa référence. Effet attendu : l’alerte disparaît ou laisse place à un autre contrôle justifié.
- Symptôme : Excel refuse une date, un statut ou un montant. Cause plausible : la date est antérieure au 01/01/2020 ou postérieure à la Date d’arrêté, la valeur ne figure pas dans Listes, ou le montant est négatif. Vérification ou correction : choisissez une valeur proposée et vérifiez les paramètres sans désactiver la validation. Effet attendu : la saisie est acceptée dans la plage préparée.
- Symptôme : Facturé > engagé apparaît. Cause plausible : Facturé cumulé HT dépasse Montant initial HT diminué de Montant annulé HT, éventuellement après un collage ayant contourné la validation. Vérification ou correction : rapprochez les trois montants de vos pièces de travail et corrigez la donnée erronée ; ne réduisez pas artificiellement le facturé pour supprimer l’alerte. Effet attendu : Facturé cumulé HT redevient cohérent avec Engagé net HT.
- Symptôme : Taux engagé reste vide. Cause plausible : l’enveloppe est nulle ou absente et IFERROR masque l’erreur de division. Vérification ou correction : renseignez une Enveloppe de pilotage HT positive uniquement si une enveloppe existe réellement. Effet attendu : le taux apparaît ; sinon, laissez-le vide et ne l’interprétez pas comme 0 %.
- Symptôme : une ligne ajoutée après la ligne 101 n’entre pas dans les totaux. Cause plausible : les validations, formules et plages de consolidation sont préparées pour 100 lignes. Vérification ou correction : utilisez une ligne libre comprise entre 2 et 101 ou étendez ensemble les tableaux, formules, validations et plages de Synthèse. Effet attendu : la nouvelle ligne est prise en compte sans rupture de calcul.
Comparaison avec une feuille vide ou une liste manuelle
Le classeur convient à un suivi structuré et répétitif. Une feuille vide laisse davantage de liberté, tandis qu’une liste manuelle non calculée peut suffire pour quelques engagements à noter sans consolidation budgétaire.
| Critère | Classeur Excel préparé | Feuille vide | Liste manuelle non calculée |
|---|---|---|---|
| Structure des données | Cinq feuilles et champs déjà organisés | Structure à concevoir selon le chantier | Format libre, adapté à des notes courtes |
| Calculs | Engagé net, exposition, reste et taux calculés par formules | Formules à créer et à vérifier | Calculs à effectuer séparément |
| Contrôles de saisie | Validations sur dates, listes, identifiants et montants | Contrôles possibles mais à configurer | Relecture visuelle principalement |
| Capacité préparée | 100 engagements et 100 lignes budgétaires ; graphique limité aux huit premiers lots affichés | Capacité définie lors de la construction | Souple pour un faible volume, moins lisible lorsque la liste s’allonge |
| Usage pertinent | Arrêté périodique avec comparaison aux enveloppes | Modèle très spécifique ou analyse ponctuelle | Collecte rapide de quelques informations qualitatives |
Le classeur est un bon choix sous conditions si les champs proposés et la capacité de 100 lignes correspondent à votre suivi. Préférez une feuille conçue sur mesure si vos règles de calcul diffèrent fortement, et une liste manuelle si vous n’avez ni budget par lot ni besoin de consolidation.
Adaptations possibles et limites du fichier
Vous pouvez remplacer les valeurs existantes dans Listes, mais les plages de validation sont fixes : Statuts utilise A2:A5, Natures B2:B3, Lots C2:C9 et Centres de coût D2:D6. Un lot ajouté au-delà de ces plages ne sera pas proposé automatiquement. Utilisez exactement le même libellé dans Engagements et Budgets par lot pour que SUMIFS réalise le rapprochement.
Synthèse reprend les huit premières lignes de Budgets par lot dans son tableau et son graphique. Les totaux généraux couvrent les lignes 2 à 101. Toute extension doit donc porter à la fois sur les formules, les validations, les tableaux et les références de synthèse. Le classeur fonctionne sans macro et ne produit ni notification, ni synchronisation, ni mise à jour en temps réel.
Il s’agit d’un support de pilotage interne périodique. Il ne remplace pas le détail des factures et paiements, un budget contractuel, un document comptable officiel, un ERP ou une vérification de conformité. La structure du classeur et la logique des calculs sont associées à Luc Martin ; les guides d’utilisation et la cohérence éditoriale sont associés à Claire Bernard.
Questions fréquentes
Un engagement Soldé reste-t-il compté dans l’engagé net ?
Oui. La formule calcule Engagé net HT pour les statuts Engagé et Soldé. Les statuts À valider et Annulé produisent un montant engagé net nul. Si votre règle interne exige qu’un élément soldé soit retiré, ne modifiez pas simplement son statut : vérifiez d’abord la signification attendue et adaptez la logique de calcul de manière cohérente.
Que se passe-t-il lorsque des montants sont laissés vides ?
Dans les calculs, Montant annulé HT et Facturé cumulé HT sont traités comme zéro par la fonction N. En revanche, un Montant initial HT vide déclenche À compléter. Un dénominateur nul dans le calcul du Taux engagé conduit IFERROR à afficher une cellule vide, qui ne doit pas être confondue avec 0 %.
Comment ajouter un nouveau lot à la liste ?
La liste actuelle des lots occupe Listes de C2 à C9. Vous pouvez remplacer un libellé existant si vous corrigez également les lignes correspondantes dans Budgets par lot et Engagements. Pour ajouter un neuvième choix, il faut étendre les validations qui utilisent cette plage et, si nécessaire, les références du tableau et du graphique de Synthèse.
Le fichier permet-il de suivre chaque facture et chaque paiement ?
Non. Engagements contient seulement Facturé cumulé HT pour chaque engagement. Il n’existe pas de registre distinct des factures, échéances, règlements ou soldes fournisseurs. L’Exposition non facturée HT est donc un indicateur d’examen, pas un suivi détaillé des pièces comptables.
Quel est l’effet d’une modification de la Date d’arrêté ?
La Date d’arrêté modifie le calcul de Âge en jours et peut faire apparaître ou disparaître une alerte Exposition ancienne. Elle limite aussi les Date d’engagement autorisées : une date postérieure à l’arrêté est refusée par la validation. Les montants engagés et facturés ne sont pas modifiés par ce changement.
À propos des auteurs
Luc MartinStructure des classeurs et logique des calculs
Claire BernardGuides d’utilisation et cohérence éditoriale
