Vérificateur d'audit de formule
Quand l'utiliser
Utilisez ce skill lorsque vous révisez un modèle Excel créé par quelqu'un d'autre ou pour vérifier votre propre modèle avant de le partager avec un client ou un responsable. Déclenchez-le lorsqu'un modèle a été signalé pour un contrôle qualité, lors d'une remise de version, ou avant qu'un modèle ne soit utilisé pour une décision financière importante. Il est particulièrement utile comme barrière de qualité avant soumission : exécutez cet audit avant qu'un modèle ne soit présenté à un conseil d'administration, un comité d'investissement ou un client.
Ce qu'il fait
Fournit un processus d'audit de modèle systématique et étape par étape couvrant : les outils d'audit de formule natifs d'Excel (Trace Precedents, Trace Dependents, Error Checking), la détection et la correction des valeurs codées en dur dans les formules, l'identification des références circulaires, l'encapsulation cohérente avec IFERROR, et la documentation des résultats d'audit dans un journal de révision. Le résultat est un journal d'audit complété et une liste priorisée des problèmes à corriger.
Méthode
-
Enregistrez une copie de travail avant d'auditer. Avant toute modification, enregistrez le modèle sous un nouveau fichier en ajoutant "_Audit_AAAA-MM-JJ" au nom. Toutes les modifications durant l'audit doivent être effectuées sur cette copie. Cela préserve l'état d'origine pour comparaison.
-
Exécutez l'outil Error Checking. Allez dans Formules > Error Checking. Excel parcourt toutes les cellules contenant des erreurs (#N/A, #REF!, #DIV/0!, #VALUE!, #NAME?) et affiche l'erreur ainsi que la formule. Pour chaque erreur : diagnostiquez la cause (clé de recherche manquante, cellule source supprimée, mauvais type de données, plage nommée manquante), corrigez-la et documentez-la dans le journal d'audit avec : référence de cellule, type d'erreur, cause, correction appliquée, sévérité (Critique, Majeur, Mineur).
-
Trouvez et résolvez les références circulaires. Allez dans Formules > Error Checking > Circular References. Excel liste toutes les cellules ayant des dépendances circulaires. Les références circulaires sont presque toujours une erreur dans un modèle financier (sauf dans les scénarios de calcul itératif, qui sont rares et doivent être explicitement intentionnels). Pour chaque référence circulaire : tracez la chaîne de dépendance, identifiez la boucle et brisez-la en restructurant la logique de la formule. Documentez chaque référence circulaire trouvée.
-
Utilisez Trace Precedents pour vérifier la logique des formules. Pour chaque ligne de calcul importante (revenus, EBITDA, FCF, valeur des capitaux propres, IRR), cliquez sur la cellule et appuyez sur Ctrl+[ ou allez dans Formules > Trace Precedents. Cela dessine des flèches montrant quelles cellules alimentent cette formule. Vérifiez : (a) la formule provient des bons onglets (onglet Inputs pour les hypothèses, onglet Data pour les références, bon onglet Calc pour les résultats intermédiaires), (b) aucune cellule inattendue n'est référencée, (c) la formule ne référence pas des cellules qui devraient se trouver sur un onglet différent.
-
Utilisez Trace Dependents pour vérifier ce qu'une cellule alimente. Pour les cellules de sortie clés sur les onglets Calc, utilisez Formules > Trace Dependents pour voir quelles cellules en aval référencent cette sortie. Cela confirme : (a) que les cellules de sortie sont bien reprises dans l'onglet Outputs, (b) qu'aucune formule en aval inattendue ne référence une cellule qui devrait être un intermédiaire local.
-
Trouvez tous les nombres codés en dur dans les cellules de formule. C'est l'étape d'audit la plus importante pour l'intégrité du modèle. Tous les nombres codés en dur (sauf dans l'onglet Inputs) sont des erreurs de modèle car ils ne peuvent pas être facilement modifiés sans ouvrir chaque formule.
Étape : Appuyez sur Ctrl+H (Rechercher et remplacer). Cliquez sur Options. Définissez "Regarder dans" sur Formules. Recherchez des nombres spécifiques à vérifier (essayez "0.2", "0.3", "0.15", "1000", "12"). Toute formule contenant ces nombres en dehors de l'onglet Inputs doit être examinée.
Meilleure approche : Utilisez la fonctionnalité Atteindre d'Excel (Ctrl+G > Atteindre > Constantes > Nombres). Cela sélectionne toutes les cellules contenant des nombres codés en dur. Examinez : toute cellule de ce type sur un onglet Calc qui n'est pas sur l'onglet Inputs doit être signalée.
Pour chaque nombre codé en dur trouvé dans une formule Calc : déplacez l'hypothèse vers l'onglet Inputs, donnez-lui une étiquette, appliquez un remplissage bleu, et remplacez la valeur codée par une référence de cellule vers l'onglet Inputs.
-
Auditez la cohérence des IFERROR. Examinez toutes les formules de recherche (VLOOKUP, INDEX-MATCH, XLOOKUP, MATCH) pour vérifier qu'elles sont encapsulées dans IFERROR. Une recherche non protégée qui retourne #N/A peut se propager silencieusement dans les formules en aval, produisant des résultats incorrects.
Pour trouver les recherches non protégées : Ctrl+F > recherchez "=VLOOKUP", "=INDEX(", "=XLOOKUP" sans le préfixe "IFERROR(". Pour chaque recherche non protégée : encapsulez-la dans IFERROR(formule, "Vérifier : [description] introuvable") ou IFERROR(formule, 0) selon le contexte. Documentez chaque recherche qui a été encapsulée.
-
Vérifiez l'intégrité des plages nommées. Allez dans Formules > Name Manager. Pour chaque plage nommée : vérifiez qu'elle fait référence à la bonne cellule ou plage (pas de #REF! dans la colonne "Fait référence à"), vérifiez que le nom est toujours utilisé dans le modèle (recherchez le nom dans les formules avec Ctrl+F), et supprimez les plages nommées orphelines (noms qui n'ont plus de référence valide ou qui ne sont plus utilisés).
-
Vérifiez la convention de signe. Une erreur courante dans les modèles est une convention de signe incohérente -- certains éléments positifs, d'autres négatifs, sans règle énoncée. Auditez : tous les éléments du compte de résultat (revenus, bénéfice brut, EBITDA) doivent être des nombres positifs. Les coûts peuvent être positifs (avec soustraction dans la formule) ou négatifs (avec addition). Choisissez une convention et appliquez-la de manière cohérente. Signalez toute ligne où le signe semble incohérent avec les lignes environnantes.
-
Vérifiez les tableaux de sensibilité pour les références de cellules d'entrée correctes. Dans tout tableau de données Excel, la cellule d'entrée de ligne et la cellule d'entrée de colonne doivent référencer les cellules exactes sur l'onglet Inputs qui sont modifiées. Une erreur courante : les cellules d'entrée référencent une cellule différente de celle réellement utilisée dans les calculs du modèle. Pour vérifier : modifiez manuellement la valeur de la cellule d'entrée de ligne et vérifiez que le résultat du modèle change comme prévu. Ensuite, exécutez le tableau de données et vérifiez que les résultats changent en conséquence.
-
Auditez la complexité des formules. Les longues formules IF imbriquées (plus de 2 niveaux) sont un risque d'audit car elles sont difficiles à lire, difficiles à déboguer et sujettes aux erreurs de logique. Signalez toute formule de plus de 150 caractères ou contenant plus de 2 niveaux d'imbrication. Pour chaque formule signalée : divisez-la en lignes de calcul intermédiaires, nommez chaque étape intermédiaire clairement, et remplacez la formule imbriquée par une séquence propre étape par étape.
-
Complétez le journal d'audit. Enregistrez tous les résultats dans un tableau structuré de journal d'audit sur l'onglet Documentation ou dans un onglet séparé Audit Log :
ID Résultat | Onglet | Cellule | Type de problème | Sévérité | Description | Statut (Ouvert/Corrigé) | Corrigé par | Date de correction.
Types de problèmes : Valeur codée en dur, Référence circulaire, Erreur de formule, IFERROR manquant, Incohérence de signe, Plage nommée cassée, Erreur de tableau de sensibilité, Complexité de formule.
Sévérité : Critique (produira des résultats erronés dans l'état actuel), Majeur (produira des résultats erronés dans certaines conditions), Mineur (n'affecte pas les résultats mais viole les conventions).
Résumez en haut du journal : nombre total de résultats, nombre de critiques, nombre de majeurs, nombre de mineurs, et statut global de l'audit (Réussite si zéro Critique, Réussite conditionnelle si seulement Mineur, Échec si un Critique ou Majeur non résolu).
Entrées
- Le modèle Excel à auditer (noms des onglets, description de l'objectif et où se trouvent les sorties clés)
- Tout problème ou préoccupation connu signalé par le constructeur du modèle
- Le public cible du modèle (détermine le niveau de rigueur de l'audit)
- La date limite pour terminer l'audit
Format de sortie
Une spécification d'audit complète avec :
- Checklist d'audit : Les 12 étapes d'audit sous forme de checklist vérifiable avec le chemin de navigation Excel pour chacune.
- Instructions de détection des valeurs codées : Approche étape par étape Ctrl+H et Atteindre.
- Instructions d'audit IFERROR : La chaîne de recherche pour trouver les lookups non protégés.