Cette feuille de calcul Excel organise les frais généraux d’un chantier, puis les répartit entre des lots ou des périodes selon des coefficients saisis. Elle s’adresse à la personne qui prépare ou contrôle une analyse interne des coûts indirects.
Le classeur rapproche le montant retenu et le montant affecté, signale les lignes incohérentes et calcule un écart global. La décision affichée reste un contrôle arithmétique : elle ne valide ni la pertinence de la clé choisie ni la conformité comptable de l’analyse.
Ce que ce tableau aide à suivre
- Rattacher chaque frais indirect à un axe et à une clé de ventilation explicitement renseignés.
- Repérer les frais non ventilés, exclus ou affectés avec un écart supérieur à la tolérance.
- Lire la répartition cumulée des montants par lot et par période dans une synthèse commune.
- Rapprocher le total retenu du total affecté avant de clôturer une analyse périodique.
Contenu du classeur
Commencer à l’utiliser
1. Remplacez les paramètres de démonstration
Dans Paramètres, renseignez Tolérance d’écart, Date de début et Date de fin. Adaptez ensuite Catégories, Lots, Axes, Clés de ventilation, Inclusion et Périodes mensuelles. Vérifiez que les dates utilisées restent comprises dans la période d’analyse.
2. Enregistrez les frais à répartir
Dans Frais généraux, saisissez dans l’ordre ID frais, Date, Libellé, Catégorie, Montant saisi (€), Inclure, Axe choisi, Clé choisie et, si nécessaire, Commentaire. Chaque ID frais doit être unique. Préservez les colonnes de calcul Montant retenu (€), Total affecté (€), Écart (€) et Statut.
3. Affectez les coefficients
Dans Ventilation, sélectionnez un ID frais, puis renseignez soit Lot cible, soit Période cible selon Axe. Saisissez un Coefficient numérique positif pour chaque destination. Ne remplacez pas les formules des champs Axe, Montant source (€), Total coefficients, Part, Anomalie et Montant affecté (€).
4. Contrôlez la synthèse
Dans Synthèse, comparez Total retenu, Total affecté et Écart global. Vérifiez aussi Anomalies de ventilation, Taux ventilé et Décision de contrôle. Ne clôturez l’analyse qu’après examen des statuts Écart ou Non ventilé dans Frais généraux.
Ce que contient le classeur
Le fichier est organisé en quatre feuilles qui séparent les réglages, les données sources, les affectations et le contrôle final.
- Paramètres contient Tolérance d’écart, Date de début, Date de fin ainsi que les listes Catégories, Lots, Axes, Clés de ventilation, Inclusion et Périodes mensuelles.
- Frais généraux reçoit ID frais, Date, Libellé, Catégorie, Montant saisi (€), Inclure, Axe choisi, Clé choisie et Commentaire. Montant retenu (€), Total affecté (€), Écart (€) et Statut sont calculés.
- Ventilation reçoit ID frais, Lot cible ou Période cible et Coefficient. Les champs Axe, Montant source (€), Total coefficients, Part, Anomalie et Montant affecté (€) sont alimentés par les formules.
- Synthèse présente les totaux, le nombre de frais soldés, les anomalies, le taux ventilé, la décision de contrôle et les montants regroupés par destination.
Les lignes déjà renseignées concernent un chantier fictif. Elles servent à comprendre l’enchaînement des calculs et doivent être remplacées par vos propres saisies.
Comment la répartition est calculée
Dans Frais généraux, Montant retenu (€) reprend Montant saisi (€) lorsque Inclure vaut « Oui » ; il vaut 0 lorsque Inclure vaut « Non ». La fonction SUMIFS additionne ensuite les Montant affecté (€) associés au même ID frais. Écart (€) correspond à Montant retenu (€) moins Total affecté (€).
Dans Ventilation, XLOOKUP retrouve Axe et Montant source (€) à partir de l’ID frais. SUMIFS additionne les coefficients du même ID, puis la part de chaque destination est calculée avec IFERROR(E2/G2,0). Le comportement des champs vides est volontaire : si ID frais est vide, les cellules calculées restent vides. Si une recherche échoue, IFERROR renvoie une cellule vide ; l’Anomalie doit alors être contrôlée.
Montant affecté (€) multiplie Montant source (€) par Part, mais reste vide lorsqu’une anomalie est présente. Dans Synthèse, le taux utilise IFERROR(B4/B3,0) : si Total retenu est nul ou provoque une erreur de division, le résultat affiché est 0 %. Ce zéro ne signifie pas que la ventilation est achevée ; vérifiez d’abord que Total retenu contient bien les frais attendus.
Les statuts reposent notamment sur IF, ABS, COUNTIFS et la Tolérance d’écart. Les noms de fonctions sont en anglais et les arguments sont séparés par des virgules, comme dans IFERROR(E2/G2,0).
Cas pratique : répartir une base vie entre deux lots
Le classeur contient un exemple fictif identifié FG-DEMO-001. Dans Frais généraux, son Montant saisi (€) est de 1 200,00 €, Inclure vaut « Oui », Axe choisi vaut « Lot » et Clé choisie vaut « Surface prévue ». Le Montant retenu (€) calculé est donc de 1 200,00 €.
Dans Ventilation, deux saisies affectent cet ID : un coefficient de 60 à L01 Démo et un coefficient de 40 à L02 Démo. Total coefficients vaut 100. Les Parts calculées sont respectivement 60,0 % et 40,0 %, ce qui produit des Montant affecté (€) de 720,00 € et 480,00 €.
Le total affecté à cet ID atteint ainsi 1 200,00 € et son Écart (€) est nul. Avec la Tolérance d’écart de démonstration fixée à 0,01 €, le Statut devient Soldé. La décision opérationnelle consiste alors à accepter l’équilibre arithmétique de cette ligne, tout en vérifiant séparément que la surface est bien la clé adaptée au chantier réel.
Contrôles à lire avant de clôturer
Commencez par rapprocher Total retenu et Total affecté dans Synthèse. Écart global doit rester dans la Tolérance modifiable. Anomalies de ventilation compte les lignes de Ventilation pour lesquelles ID frais est renseigné et Anomalie n’est pas vide.
- Exclu indique que Inclure vaut « Non » ; le Montant retenu (€) correspondant est alors nul.
- Non ventilé indique qu’un montant retenu n’a encore reçu aucune affectation calculée.
- Écart indique que le montant affecté ne rejoint pas le montant retenu dans la tolérance définie.
- Soldé indique que l’écart de la ligne est compris dans cette tolérance.
Décision de contrôle affiche « À corriger » si l’écart global dépasse la tolérance ou si une anomalie subsiste. « Ventilation acceptable » signifie uniquement que ces tests internes sont satisfaits. Cette mention ne contrôle pas les pièces justificatives, le choix économique de la clé ou la destination comptable des frais.
Erreurs courantes et corrections sûres
- Symptôme : Statut affiche « Non ventilé ». Cause plausible : aucune ligne valide de Ventilation ne reprend l’ID frais, ou une anomalie empêche le calcul du montant. Vérification ou correction sûre : filtrez Ventilation sur cet ID, renseignez une cible cohérente et un Coefficient positif, puis vérifiez que Anomalie est vide. Effet attendu : Total affecté (€) augmente et Écart (€) diminue.
- Symptôme : Anomalie affiche « ID inconnu ». Cause plausible : l’identifiant saisi n’existe pas exactement dans Frais généraux. Vérification ou correction sûre : choisissez l’ID depuis la liste proposée et contrôlez les espaces ou caractères ajoutés. Effet attendu : Axe et Montant source (€) sont retrouvés, puis l’anomalie disparaît si les autres champs sont valides.
- Symptôme : Anomalie affiche « Coefficient invalide ». Cause plausible : Coefficient est vide, non numérique, nul ou négatif. Vérification ou correction sûre : saisissez une valeur numérique strictement positive sans modifier les formules voisines. Effet attendu : Part et Montant affecté (€) peuvent être recalculés.
- Symptôme : Anomalie affiche « Cible incohérente ». Cause plausible : les deux cibles sont remplies, ou la cible ne correspond pas à Axe. Vérification ou correction sûre : pour Axe égal à « Lot », renseignez seulement Lot cible ; pour « Période », renseignez seulement Période cible. Effet attendu : la ligne rejoint la destination prévue et le montant devient calculable.
- Symptôme : Excel affiche une erreur de fonction ou le préfixe _xlfn.XLOOKUP. Cause plausible : l’environnement utilisé ne reconnaît pas XLOOKUP. Vérification ou correction sûre : ouvrez le fichier dans une version d’Excel prenant cette fonction en charge et évitez de remplacer les formules par des valeurs fixes. Effet attendu : Axe et Montant source (€) se renseignent à nouveau à partir de l’ID frais.
Comparaison avec une feuille vide et une liste manuelle
Ce classeur convient lorsque plusieurs frais doivent être affectés selon des coefficients et rapprochés des montants sources. Une feuille vide ou une liste manuelle peut toutefois être plus adaptée lorsque la méthode est inhabituelle ou que le volume reste très limité.
| Critère | Classeur Excel proposé | Feuille vide | Liste manuelle |
|---|---|---|---|
| Structure de saisie | Quatre feuilles reliées, champs préparés et listes issues de Paramètres. | Structure à concevoir selon vos besoins avant la première affectation. | Présentation libre, adaptée à quelques écritures simples. |
| Calcul de répartition | Parts et montants calculés à partir des coefficients saisis. | Dépend des formules que vous créez et vérifiez. | Calcul effectué séparément ou reporté ligne par ligne. |
| Contrôles | Écart (€), Statut, Anomalie et décision globale forment un contrôle structuré. | Les contrôles dépendent entièrement des règles ajoutées. | Contrôle principalement fondé sur la relecture et le rapprochement manuel. |
| Souplesse | Axes Lot ou Période, avec capacités et plages déjà préparées. | Convient à une logique spécifique qui doit être construite sur mesure. | Pratique pour un besoin ponctuel sans ventilation pondérée complexe. |
Choisissez ce classeur pour un suivi périodique nécessitant des affectations pondérées et un rapprochement chiffré. Préférez une feuille vide si votre méthode sort des axes prévus, ou une liste manuelle pour quelques montants isolés dont la répartition peut être contrôlée directement.
Adaptations possibles et limites
Vous pouvez remplacer les catégories, lots, clés et périodes de démonstration dans Paramètres. Les plages préparées vont notamment jusqu’à la ligne 20 pour Catégories, 30 pour Lots, 10 pour Clés de ventilation et 25 pour Périodes mensuelles. Vérifiez que toute nouvelle valeur reste dans ces plages afin qu’elle soit disponible dans les listes de saisie.
Le fichier prépare 150 lignes dans Frais généraux et 500 lignes dans Ventilation. Si votre chantier dépasse ces volumes, étendez ensemble les tables, les formules et les validations, puis testez les totaux sur une copie. Un simple ajout de lignes hors des plages existantes ne doit pas être supposé couvert par tous les contrôles.
Chaque ID frais utilise un seul Axe choisi : Lot ou Période. Le classeur ne produit pas une analyse simultanée croisant les deux dimensions. Il ne contient ni macro, ni graphique, ni synchronisation externe, ni notification, et ne constitue pas un outil en temps réel.
Ce support sert à une analyse interne. Il n’est ni un document comptable officiel, ni une certification, ni une preuve de conformité. La qualité du résultat dépend des montants saisis, du caractère complet des affectations et de la justification des clés retenues.
Questions fréquentes
Peut-on ventiler un même ID frais à la fois par lot et par période ?
Non, chaque ID frais reprend un seul Axe choisi, soit « Lot », soit « Période ». Les lignes de Ventilation doivent renseigner uniquement la cible correspondant à cet axe. Pour analyser deux dimensions, il faut préparer une méthode distincte plutôt que remplir simultanément Lot cible et Période cible.
Combien de lignes le classeur prévoit-il ?
Frais généraux contient 150 lignes préparées, de la ligne 2 à la ligne 151. Ventilation en contient 500, de la ligne 2 à la ligne 501. Une extension demande de contrôler les formules, les validations et les plages de synthèse.
Faut-il conserver les données de démonstration ?
Non. Les montants, libellés, lots et périodes de démonstration sont fictifs et doivent être remplacés. Conservez en revanche les en-têtes, les tables et les cellules de formule, puis vérifiez les résultats avec quelques lignes avant de saisir l’ensemble du chantier.
À quoi sert Tolérance d’écart ?
Tolérance d’écart fixe le seuil monétaire accepté entre Montant retenu (€) et Total affecté (€). La valeur de démonstration est de 0,01 €. Adaptez-la à votre règle interne, puis vérifiez son incidence sur Statut et Décision de contrôle.
La mention « Ventilation acceptable » garantit-elle l’analyse ?
Non. Elle indique seulement que l’écart global ne dépasse pas la tolérance et qu’aucune anomalie de ventilation n’est comptée. Vérifiez encore les pièces sources, les exclusions, les coefficients et la pertinence des destinations avant de clôturer l’analyse.
À propos des auteurs
Luc MartinStructure des classeurs et logique des calculs
Claire BernardGuides d’utilisation et cohérence éditoriale
