Ce tableur Excel d’analyse des écarts quantité / coût met en regard, ligne par ligne, la Quantité prévue et le Coût unitaire prévu avec la Quantité réalisée et le Coût unitaire réalisé. Il sert à expliquer un écart de chantier entre effet de volume et effet de prix.
Destiné aux personnes qui préparent ou contrôlent une situation de travaux, il alimente une synthèse par Lot et signale les écarts dépassant les seuils définis. Les valeurs déjà présentes sont fictives et doivent être remplacées avant utilisation ; le fichier reste un support interne de décision.
Ce que ce tableau aide à suivre
- Distinguer la part d’un écart total liée aux quantités de celle liée au coût unitaire.
- Repérer les lots à examiner selon un seuil absolu ou relatif défini pour le chantier.
- Consolider le Budget prévu, le Coût réalisé et les deux impacts dans une synthèse commune.
- Détecter les lignes incomplètes ou fondées sur un budget prévu nul avant de conclure.
Contenu du classeur
Commencer à l’utiliser
1. Remplacer les paramètres de démonstration
Dans Paramètres, renseignez Nom du chantier, Date d’arrêté, Seuil d’alerte absolu, Seuil d’alerte relatif, Tolérance de contrôle et Première date autorisée. Adaptez ensuite les listes Lots autorisés et Unités autorisées. Les valeurs initiales sont des hypothèses de démonstration.
2. Poser la base prévue
Dans Analyse des écarts, renseignez les cellules jaunes des champs Identifiant, Lot, Désignation, Unité, Date de situation, Quantité prévue et Coût unitaire prévu. L’Identifiant doit rester unique. Cette saisie constitue le prévu servant de base aux écarts.
3. Saisir le réalisé
Complétez Quantité réalisée, Coût unitaire réalisé et, si nécessaire, Commentaire / action. Cette saisie constitue le réalisé. Préservez les formules de Budget prévu, Coût réalisé, Écart de quantité, Impact volume, Écart de coût unitaire, Écart de coût unitaire %, Impact coût unitaire, Écart total et Statut.
4. Contrôler avant de décider
Vérifiez le Statut de chaque ligne, puis ouvrez Synthèse pour comparer les lots. Contrôlez que Écart total correspond à la somme de Impact volume et Impact coût unitaire, et que Contrôle de décomposition reste proche de zéro selon la Tolérance de contrôle choisie.
Contenu des trois feuilles de calcul
La feuille Analyse des écarts contient 100 lignes de suivi. Chaque ligne associe un Identifiant, un Lot, une Désignation, une Unité et une Date de situation aux quantités et coûts unitaires prévus puis réalisés. Les colonnes suivantes calculent les écarts et laissent un champ Commentaire / action pour consigner la vérification à mener.
Synthèse totalise Budget prévu, Coût réalisé, Écart total, Impact volume et Impact coût unitaire. Elle regroupe aussi ces montants par Lot, calcule Écart % et attribue une Priorité. Paramètres centralise l’identité du chantier, la date d’arrêté, les seuils, la tolérance et les listes utilisées par les contrôles de saisie.
Logique des calculs et traitement des cellules vides
Budget prévu correspond à Quantité prévue multipliée par Coût unitaire prévu. Coût réalisé correspond à Quantité réalisée multipliée par Coût unitaire réalisé. Écart de quantité est la différence entre les deux quantités ; Impact volume valorise cette différence au Coût unitaire prévu.
Écart de coût unitaire est la différence entre le coût réalisé et le coût prévu. Impact coût unitaire multiplie cette différence par Quantité réalisée. Cette convention affecte donc l’effet croisé quantité-prix à Impact coût unitaire. Écart total correspond à Coût réalisé moins Budget prévu et doit aussi être égal à Impact volume plus Impact coût unitaire. Dans le fichier, un écart positif signifie un surcoût et un écart négatif une économie arithmétique.
Si l’une des quatre données Quantité prévue, Coût unitaire prévu, Quantité réalisée ou Coût unitaire réalisé manque, les calculs concernés restent vides et Statut affiche À compléter. Pour Écart de coût unitaire %, IFERROR((J6-G6)/G6,"") renvoie une cellule vide lorsque la division ne peut pas être effectuée, notamment si Coût unitaire prévu vaut zéro. Dans Synthèse, IFERROR(C11/B11,"") laisse également Écart % vide si le Budget prévu du lot est nul. Une cellule vide ne doit donc pas être lue comme un écart de 0,00 %.
Les regroupements par Lot utilisent SUMIF. Une ligne complète passe en Alerte dès que la valeur absolue de Écart total atteint le Seuil d’alerte absolu ou que son rapport au Budget prévu atteint le Seuil d’alerte relatif. Sinon, elle reste Sous seuil. Un budget prévu nul produit À vérifier.
Cas pratique interprété : un surcoût sous le seuil absolu
Exemple fondé sur la ligne de démonstration P001, à remplacer par vos données : au 31/08/2026, la Quantité prévue est de 1 200 m² à 2,40 €, tandis que la Quantité réalisée est de 1 280 m² à 2,55 €. Le Budget prévu calculé est de 2 880,00 € et le Coût réalisé de 3 264,00 €.
Le supplément de 80 m² produit un Impact volume de 192,00 €. La hausse de 0,15 € appliquée aux 1 280 m² réalisés produit un Impact coût unitaire de 192,00 €. Le résultat calculé est donc un Écart total de 384,00 €, soit 13,33 % du Budget prévu.
Avec les paramètres fictifs fournis, soit 1 000,00 € en valeur absolue et 10,00 % en valeur relative, la ligne prend le statut Alerte. Le montant reste inférieur à 1 000,00 €, mais le taux dépasse 10,00 %. La décision raisonnable consiste à vérifier séparément le métrage réalisé et le coût unitaire, puis à consigner l’action dans Commentaire / action. Ce calcul illustre la logique du fichier ; il ne décrit pas un chantier réel.
Erreurs concrètes et corrections sûres
Symptôme : Statut affiche À compléter. Cause plausible : au moins une valeur manque dans Quantité prévue, Coût unitaire prévu, Quantité réalisée ou Coût unitaire réalisé. Vérification ou correction : comparez la ligne avec la situation de travaux et renseignez uniquement la donnée confirmée, sans saisir zéro pour remplacer une inconnue. Effet attendu : les calculs se remplissent et le statut devient Sous seuil, Alerte ou À vérifier.
Symptôme : Statut affiche À vérifier et Écart de coût unitaire % reste vide. Cause plausible : Budget prévu vaut zéro parce que Quantité prévue ou Coût unitaire prévu vaut zéro. Vérification ou correction : contrôlez la base prévue et corrigez la saisie si le zéro est accidentel. S’il est justifié, conservez-le et expliquez la situation dans Commentaire / action. Effet attendu : avec une base non nulle, le pourcentage et le test relatif redeviennent interprétables.
Symptôme : une nouvelle ligne est signalée lors de la saisie de Identifiant. Cause plausible : la même référence existe déjà entre les lignes 6 et 105. Vérification ou correction : recherchez l’identifiant existant et attribuez une référence distincte, ou mettez à jour la ligne déjà créée. Effet attendu : la validation accepte la référence unique et le risque de double comptage diminue.
Symptôme : une Date de situation est signalée ou refusée. Cause plausible : elle est antérieure à Première date autorisée ou postérieure à Date d’arrêté. Vérification ou correction : contrôlez la date de la pièce source, puis corrigez la date ou adaptez les bornes dans Paramètres si la période de suivi a réellement changé. Effet attendu : la date entre dans l’intervalle autorisé.
Symptôme : Contrôle de décomposition n’est pas proche de zéro. Cause plausible : une formule de Impact volume, Impact coût unitaire ou Écart total a été remplacée. Vérification ou correction : comparez la ligne avec une ligne intacte et restaurez la formule par copie, après sauvegarde du fichier. Effet attendu : Écart total redevient égal à la somme des deux impacts, sous réserve des arrondis.
Comparaison avec une feuille vide ou une liste manuelle
Le choix dépend du niveau d’analyse attendu. Ce classeur fournit une méthode de décomposition déjà posée ; une feuille vide laisse davantage de liberté, tandis qu’une liste manuelle suffit parfois pour quelques contrôles sans calcul détaillé.
| Critère | Ce classeur Excel | Feuille vide | Liste manuelle |
|---|---|---|---|
| Préparation | Champs prévus, réalisés et paramètres déjà structurés. | Champs, formats et formules à construire selon le besoin. | Rubriques libres, adaptées à un relevé ponctuel. |
| Décomposition de l’écart | Impact volume et Impact coût unitaire calculés séparément. | Possible après création et vérification des formules. | Calcul à effectuer séparément ou à reporter à la main. |
| Contrôles de saisie | Identifiant unique, listes de Lot et d’Unité, dates bornées et nombres positifs. | Aucun contrôle initial tant qu’il n’est pas configuré. | Contrôle fondé sur la relecture de l’utilisateur. |
| Synthèse et priorisation | Totaux et regroupements par Lot avec statuts selon deux seuils. | Présentation entièrement personnalisable, mais à créer. | Récapitulatif à recalculer lors de chaque mise à jour. |
Choisissez ce classeur si vous suivez jusqu’à 100 lignes avec une structure stable de quantités et coûts. Une feuille vide est préférable si votre décomposition, vos catégories ou votre volume de données diffèrent fortement. Une liste manuelle peut convenir à un contrôle unique de quelques postes ne nécessitant ni ventilation ni synthèse recalculée.
Adapter les lots, unités, seuils et capacités
Les valeurs de Paramètres sont modifiables. Les seuils initiaux de 1 000,00 € et 10,00 %, la Tolérance de contrôle de 1,00 %, la Première date autorisée du 01/01/2026 et la Date d’arrêté du 07/09/2026 sont des hypothèses de démonstration. Remplacez-les selon votre règle interne avant de lire les statuts.
Les listes comportent huit Lots autorisés et sept Unités autorisées. Vous pouvez remplacer leurs libellés en conservant les plages existantes. Ajouter davantage de catégories demande d’étendre les plages utilisées par les validations et, pour les lots, les lignes correspondantes de Synthèse.
Le tableau t_Ecarts et les formules de consolidation s’arrêtent à la ligne 105. Pour dépasser 100 lignes, étendez ensemble le tableau, les formules ligne à ligne et les plages de Synthèse ; une extension partielle produirait des lignes non consolidées. Le classeur ne contient pas de macro pour effectuer cette adaptation.
Luc Martin est associé à la structure du classeur et à la logique des calculs. Claire Bernard est associée aux guides d’utilisation et à la cohérence éditoriale. Ces attributions ne constituent ni une validation des données saisies ni une certification.
Limites à connaître avant le suivi
Le fichier est conçu pour un suivi périodique. Il ne synchronise pas les données, n’émet pas de notification et ne récupère aucune situation de travaux automatiquement. Il ne contient ni graphique ni macro ; les mises en forme conditionnelles sont uniquement des repères visuels.
Le classeur n’est ni un ERP, ni un document officiel, ni une preuve de conformité. Il ne vérifie pas les métrés, les factures, le périmètre contractuel ou la qualité des sources. Les montants sont formatés en euros, mais aucun champ ne précise s’ils sont hors taxes ou toutes taxes comprises : retenez une convention unique et documentez-la.
La synthèse additionne toutes les lignes présentes. Si plusieurs situations cumulatives du même poste sont empilées, le réalisé peut être compté plusieurs fois. Définissez donc avant la saisie si les lignes représentent des mouvements de période ou un état cumulé à remplacer.
Questions fréquentes
Faut-il saisir des quantités cumulées ou celles de la période ?
Le classeur n’impose pas cette convention. Vous devez en choisir une pour tout le suivi. Si vous saisissez des situations cumulées successives sur plusieurs lignes, Synthèse les additionnera et pourra doubler le réalisé. Pour un suivi cumulé, remplacez l’état précédent ou archivez une copie séparée avant la nouvelle situation.
Les montants doivent-ils être saisis hors taxes ou toutes taxes comprises ?
Aucun champ ne distingue ces deux bases. Utilisez la même convention pour Coût unitaire prévu et Coût unitaire réalisé, puis indiquez-la dans vos règles internes ou dans Commentaire / action. Mélanger les bases créerait un écart de coût unitaire sans rapport avec la performance du chantier.
Que faire si le coût unitaire prévu est réellement nul ?
La formule utilisant IFERROR laisse Écart de coût unitaire % vide, car aucun taux pertinent ne peut être calculé avec une base nulle. Le Statut passe à À vérifier lorsque Budget prévu vaut zéro. Ne remplacez pas un zéro justifié par une valeur artificielle : examinez l’écart absolu et documentez la raison de cette absence de base.
Comment utiliser la Tolérance de contrôle ?
Contrôle de décomposition calcule la différence entre Écart total, Impact volume et Impact coût unitaire. Avec des formules intactes, le résultat doit être nul ou très proche de zéro. Comparez cette valeur à Tolérance de contrôle ; un dépassement invite à rechercher une formule remplacée ou un effet d’arrondi.
Comment éviter l’écrasement des formules ?
Conservez une copie vierge du fichier et limitez la saisie aux cellules jaunes ainsi qu’au champ Commentaire / action. Avant une correction de structure, sauvegardez une version datée. Si une formule est effacée, recopiez-la depuis une ligne intacte en vérifiant que les références de ligne se sont adaptées.
À propos des auteurs
Luc MartinStructure des classeurs et logique des calculs
Claire BernardGuides d’utilisation et cohérence éditoriale
