Auditer un modèle financier n’est certainement pas une tâche facile. Je ne compte plus le nombre de modèles financiers que j’ai audités dans ma carrière et vous seriez surpris d’apprendre qu’environ 80-90% des modèles financiers audités comportent des erreurs importantes. Quand je dis “importantes”, je veux dire qu’elles ont le potentiel d’entraîner de mauvaises décisions d’affaires, ayant des conséquences graves. Les erreurs sont de toutes sortes, erreurs dans les formules, références aux mauvaises cellules, insertion de valeurs dans des formules qui ne sont pas mises à jour, etc. Pour vous aider à auditer vos modèles financiers, il existe plusieurs solutions dont notamment le add-in pour Excel OAK Operis Analysis Kit et Inquire (inclus dans la version 2016 d’Excel), qui feront l’objet de cet article. Il existe également d’autres solutions, comme Spreasheet Studio, mais celles-ci ne seront pas couvertes dans cet article.
Inquire
Inquire est un menu que vous pouvez ajouter à votre barre de menus si vous utilisez Excel 2016. Il permet notamment de comparer des fichiers entre eux, sujet que j’ai déjà couvert dans l’article Spreadsheet Compare: Comparer deux fichiers Excel aisément. Il permet également d’analyser en profondeur n’importe quel fichier Excel.
Analyse du classeur
Vous pouvez analyser votre fichier simplement en cochant les items que vous désirez analyser et en cliquant sur Exportation Excel. Inquire créera un fichier avec le résultat de l’analyse.
Voici la liste de tous les items qui peuvent être analysés. J’ai analysé tous ces items à partir d’un modèle d’un client. Voici le résultat. À noter qu’il ne s’agit pas à ce stade-ci “d’erreurs” mais plutôt d’une liste d’éléments qui correspondent aux critères en libelés.
Par exemple, il peut s’avérer utile d’étudier en profondeur la section des formules qui utilisent des constantes. Selon les meilleures pratiques d’affaires en modélisation financière, on ne devrait jamais insérer de constantes dans une formule.
De même, il peut s’avérer utile de vérifier les validations de données (ci-dessous, on voit qu’il y a des #REF dans certaines validations de données), les fonctions SI imbriquées (qui comportent souvent des erreurs et qui nuisent à la performance), les fonctions volatiles (DECALER et INDIRECT), etc.
Dépendances du classeur et des onglets
Vous pouvez observer les dépendances du classeur avec d’autres classeurs (tout à fait à proscrire donc s’il y a des dépendances, mieux vaut les remplacer avec Power Query). De même, vous pouvez étudier les dépendances entre les onglets du fichier. Inquire produit un shéma comme celui sur l’image ci-dessous.
Vous pouvez cliquer sur un onglet pour voir ses antécédents (ci-dessous, nous avons cliqué sur l’onglet Dem).
Vous pouvez aussi repérer les dépendants du même onglet, en bleu, ci-dessous.
Ces options sont intéressantes pour l’audit du fichier mais pourraient aussi servir de documentation au modèle financier.
Dépendances des cellules
Pour une cellule particulière, il est également possible de trouver tous les antécédents et tous les dépendants, tel qu’illustré ci-dessous.
OAK Operis Analysis Kit
Operis vous offre d’essayer sa solution OAK gratuitement pendant 30 jours sans limite. En installant OAK, vous oberverez 2 nouveaux menus dans Excel.
Un menu qui servira à auditer votre fichier: OAK Review.
Et un menu qui servira à corriger votre fichier: OAK Development.
Map du fichier
À mon avis, l’option la plus intéressante est sans doute la map du fichier, ce qui fait défaut à Inquire. Vous pouvez générer une copie de votre fichier sous forme de map, avec les options suivantes:
Ainsi, votre copie de fichier sera en quelques sortes, codée. Les codes utilisés seront ceux de la légende ci-dessous.
Lorsque vous regarderez les onglets de votre fichier, vous verrez donc un aperçu tel que celui ci-dessous.Toutes les valeurs (entrées de données et formules) seront remplacées par ces symboles.
Il vous faudra être attentif aux éléments comme le suivant. On y indique que les cellules avec des astérisques rouges comprennent des formules qui ne sont pas identiques à celles des cellules adjacentes. Il faudra valider leur contenu et le corriger, au besion.
Analyse sommaire
Oak vous offre également une analyse sommaire de votre fichier, un peu comme le fait Inquire.
Par exemple, vous pourrez étudier la liste de toutes les formules différentes utilisées dans votre fichier.
L’analyse la plus intéressante est sans doute l’analyse de risque, qui fait aussi défaut dans Inquire. Oak vérifie des éléments susceptibles de poser problème et leur accordent un poids selon la gravité des problèmes potentiels. Ce faisant, vous pouvez filtrer ces données de façon à auditer d’abord les erreurs potentielles avec un risque élevé.
Voici les poids accordés, en terme de risque, à chacune des composantes. Sans surprise, on retrouve en tête de liste les fonctions OFFSET (DECALER) et INDIRECT, deux fonctions volatiles dans Excel. Tout de suite après, on retrouve les fonctions imbriquées avec plus de 7 niveaux d’imbrication.
Les meilleures pratiques d’affaires en modélisation financière
La meilleure façon de réduire les erreurs dans vos fichiers demeure de vous former adéquatement en ce qui a trait aux meilleures pratiques d’affaires en modélisation financière dans Excel.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Analytique d’affaires et Finance corporative. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel. De plus, nos formations peuvent mener à l’obtention d’une accréditation.
Découvrez quelles formations vous conviennent
Pour info: 514-605-7112 ou info@lecfomasque.com