Comment intégrer des vérifications d'erreurs dans votre modèle financier Excel


Posted on 2017-09-08 11:20:54 category:Logiciel


Même si vous venez tout juste de commencer la modélisation, vous savez probablement à quel point il est facile de se tromper dans un modèle financier!Il existe trois façons d'éviter les erreurs dans un modèle financier:

  • Évitez de commettre des erreurs en premier lieu. Dans ce livre, je décris plusieurs techniques que vous pouvez utiliser pour éviter de commettre des erreurs en premier lieu, comme être cohérent avec vos formules.
  • Vérifiez le modèle pour les erreurs. Malgré tous vos efforts, des erreurs peuvent inévitablement passer. Vérifiez, revérifiez et demandez à quelqu'un d'autre de vérifier votre modèle une fois celui-ci terminé.
  • Incluez des vérifications d'erreurs. Lorsque vous créez le modèle, incluez des vérifications d'erreurs qui empêchent les erreurs involontaires de glisser dans le modèle en raison d'entrées incorrectes ou d'une erreur de l'utilisateur.

Les informations ici se concentrent sur les deux premiers points: les techniques de création de modèles pour réduire les erreurs, ainsi que les moyens de vérifier les erreurs du modèle.

Les vérifications d'erreurs sont un élément essentiel d'un modèle financier bien construit afin que l'utilisateur ou le modélisateur puisse voir d'un coup d'œil si les formules calculent correctement.Par exemple, lors de la création de rapports de gestion, vérifiez que la somme des rapports de chaque service individuel s'ajoute au total de l'entreprise.Cela peut être fait en insérant une simple fonction IF, entre autres méthodes.

Dans l'exemple présenté ici, un budget d'investissement a été créé avec des dates de dépenses estimées dans la colonne E. Dans le calendrier des dépenses d'investissement présenté dans les colonnes F à Q, les dépenses sont réparties sur l'année complète.Le modélisateur sait que le montant total des dépenses en capital de 124700 $ indiqué dans la cellule D17 doit être le même que le montant total du calendrier d'immobilisations indiqué dans la cellule R17, et si les deux montants ne sont pas égaux, le modèle ne calcule pas correctement.La cellule de vérification des erreurs E1 contient donc la formule très simple= R17-D17.

modèle financier de vérification d'erreur simple
Un simple contrôle d'erreur.

Ci-dessous, vous pouvez voir qu'un utilisateur a entré une valeur incorrecte dans la cellule E4.Le 20 février n'est pas une entrée valide car le calendrier des dépenses en capital n'autorise que des dates en 2019. Cela signifie que le modèle financier ci-dessous est incorrect - l'utilisateur a saisi 124700 USD de dépenses en capital dans le modèle, mais seulement 115700 USD ont été répartis entrel'année.Le nombre affiché dans la cellule E1 (9 000) alerte l'utilisateur sur le fait qu'il y a un problème.Téléchargez le fichier 0401.xlsx et sélectionnez les onglets étiquetés 4-9 et 4-10 pour essayer de déclencher cette vérification d'erreur par vous-même.

déclenchement d'un modèle financier de vérification d'erreur
Une erreurvérification déclenchée.

Ce contrôle d'erreur est très simple et assez discret.Pour rendre cela plus évident, vous pouvez préférer inclure la description «vérification d'erreur» à côté de la cellule de vérification d'erreur dans la cellule D1, ce qui rendrait plus évident à l'utilisateur ce qui s'est passé lorsque la vérification d'erreur est déclenchée.

La vérification des erreurs illustrée ci-dessus est une méthode préférée de vérification des erreurs, car elle est si simple et rapide à créer.Puisqu'il renvoie une valeur en cas d'erreur, il peut être un peu trop discret à votre goût - il n'alerte pas nécessairement immédiatement l'utilisateur qu'une erreur a été commise.Cependant, il est certainement rapide et facile à suivre et, pour cette raison, un contrôle d'erreur assez courant favorisé par de nombreux modélisateurs.

Si vous utilisez ce type de vérification des erreurs, formatez-la en utilisant le style Virgule (qui se trouve sur l'onglet Accueil dans le groupe Nombres) et supprimez la décimale et formatez-la avec une police rouge.De cette façon, le zéro ne montrera pas s'il n'y a pas d'erreur, et un nombre rouge indiquera s'il y a une erreur.

Vous pouvez également préférer l'autre formule de vérification des erreurs telle que= D17= R17, qui renverra la valeur TRUE s'ils sont identiques ou FALSE s'ils ne le sont pas.Cependant, cette méthode est également sujette à une fausse erreur.

Autoriser la tolérance d'erreur

= IF (D17R17, "error", 0) est un contrôle d'erreur supérieur, mais de temps en temps, il peut renvoyer un faux résultat d'erreur, même si les valeurs sont les mêmes.Ce «bogue» dans un contrôle d'erreur est causé par le fait qu'Excel porte les calculs à 14 décimales.Après cela, il tronque la valeur et peut provoquer un écart d'une minute, ce qui signalera une erreur lorsqu'il n'est que 0,0000000000000001 désactivé.

Pour éviter ce problème potentiel, vous pouvez utiliser une formule de valeur absolue, ce qui permettrait une tolérance d'erreur.= IF (ABS (D17-R17) 1, "error", 0) permettra aux valeurs d'être décalées de 1 $ avant de signaler une erreur.Si vous utilisez la fonction ABS dans Excel, cela prendra la valeur absolue du résultat, donc peu importe qu'il s'agisse d'un nombre positif ou négatif.

Il existe de nombreuses variantes de cette formule.Certains modélisateurs préfèrent afficher le mot OK si les chiffres sont corrects, et Vérifier s'ils ne le sont pas.

Application d'une mise en forme conditionnelle à un contrôle d'erreur

Pour rendre la vérification des erreurs encore plus visible pour l'utilisateur, envisagez d'utiliser la mise en forme conditionnelle pour ajouter une règle qui rend toute la cellule rouge si la vérification des erreurs a été déclenchée.Dans l'onglet Accueil du ruban, dans le groupe Styles, cliquez sur le bouton Mise en forme conditionnelle.Passez ensuite la souris sur Règle de mise en surbrillance des cellules et sélectionnez Égal à.

application de la mise en forme conditionnelle à un contrôle d'erreur de modélisation financière
Application d'un formatage conditionnel à un contrôle d'erreur.

Lorsque la boîte de dialogue Égal à apparaît, saisissez le mot erreur dans la zone Format des cellules qui sont égales à et cliquez sur OK.Par défaut, il transformera la cellule en Remplissage rouge clair avec texte rouge foncé, mais vous pouvez changer cela dans la liste déroulante.

modification du formatage conditionnel couleur modèle financier
Modification ducouleur de mise en forme conditionnelle.

Le formatage conditionnel est populaire dans les vérifications d'erreurs car il fait ressortir davantage la vérification des erreurs lorsqu'elle a été déclenchée.Et, avec un peu de chance, l'utilisateur se rendra compte qu'il y a une erreur dans le modèle financier.La mise en forme conditionnelle ne se limite pas aux vérifications d'erreurs - elle peut être utile pour d'autres modèles pour lesquels vous souhaitez attirer l'attention de l'utilisateur sur des anomalies ou des points saillants dans les calculs.

À propos de l'auteur du livre

Danielle Stein Fairhurst est la principale modélisatrice financière de Plum Solutions avec de nombreuses années d'expérience dans le domaine.Son groupe LinkedIn «Modélisation financière dans Excel» compte plus de 40 000 abonnés.Elle est également l'auteur de plusieurs articles et autres livres ainsi que d'un bulletin d'information sur la modélisation financière.

82236