Magazine Informatique

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

Publié le 18 juin 2018 par Sopmar01 @mon_cher_watson

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.

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

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, 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


Retour à La Une de Logo Paperblog

A propos de l’auteur


Sopmar01 3934 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte