Excel: Auditez vos modèles financiers avec OAK et Inquire

Publié le 17 juin 2018
par Sophie Marchand M.Sc., CPA, CGA, MVP
Inquire dépendants

Excel: Auditez vos modèles financiers avec OAK et Inquire

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.

Menu Inquire

 

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.

Inquire - Analyse du classeur

 

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.

Inquire Sujets Audité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.

Inquire Numeric Constant Formulas

 

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.

Inquire Validation Criteria

 

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.

Inquire Aperçu du fichier

 

Vous pouvez cliquer sur un onglet pour voir ses antécédents (ci-dessous, nous avons cliqué sur l’onglet Dem).

Inquire Antécédents

 

Vous pouvez aussi repérer les dépendants du même onglet, en bleu, ci-dessous.

Inquire dépendants

 

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.

Dépendances d'une cellule

 

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.

Oak Review menu

 

Et un menu qui servira à corriger votre fichier: OAK Development.

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:

Oak Map Options

 

Ainsi, votre copie de fichier sera en quelques sortes, codée. Les codes utilisés seront ceux de la légende ci-dessous.

Oak Map Legend

 

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.

Oak Review Workbook

 

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.

Oak ce que l'on recherche

 

Analyse sommaire

Oak vous offre également une analyse sommaire de votre fichier, un peu comme le fait Inquire.

Oak Summarize

 

Par exemple, vous pourrez étudier la liste de toutes les formules différentes utilisées dans votre fichier.

Oak Distinct Fomulas

 

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é.

OAK risk analysis

 

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.

Poids des risques

 

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.

 


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top