Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 17 juin 2018

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.

 

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

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, Finance corporative et Compléments avancés. 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.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire