Monsieur Excel
Pour tout savoir faire sur Excel !

26 décembre 2011

Remarques sur l'audit de modèles

Je vais être dans l’impossibilité d’alimenter ce blog durant 15 jours. A titre de compensation, je vous propose aujourd’hui un article sensiblement plus long que mon format habituel d’une à deux pages.

Pour que vous ne perdiez pas votre temps avec des redites, je vous propose de commencer par (re)lire les articles suivants puisque ce que nous allons voir aujourd’hui a trait à l’audit de modèles Excel, et que cela est lié aux soucis de taille (premier article), de re-engineering (second article) et de modélisation en général (troisième article).

« Attention à la taille de vos fichiers ! » du 23 octobre 2005

« Le re-engineering de vos modèles » du 3 mars 2008

« Réflexions générales sur la modélisation » du 28 octobre 2010

Comme nous venons – dans les deux derniers articles publiés sur ce blog – de passer en revue l’add-in Spreadsheet Detective, je vais vous parler aujourd’hui de l’audit/amélioration de modèles, en présentant trois expériences de conseil que j’ai vécues, la première en 2006, et les deux dernières durant les six derniers mois.

Un projet de 300 millions d’euros

Le plus grave problème d’audit auquel j’ai été confronté correspondait à un projet d’investissement de 300 millions d’euros, lancé en collaboration entre une grande banque française, une banque allemande et une banque italienne. Dans le cadre de ce projet, une vingtaine de projets industriels devaient être financés par le consortium.

Chacun des trois partenaires était actionnaire du projet global, mais pouvait aussi financer en plus telle ou telle partie de tel ou tel projet, ou apporter d’autres concours financiers. Un contrat de 70 pages spécifiait les différentes façons dont chacun des trois partenaires pouvait intervenir et le mode de rémunération lié à cette intervention.

Le classeur de base du projet comportait une cinquantaine d’onglets. Le problème était que – à force d’avoir été « tripoté » par de trop nombreuses personnes, il était devenu ingérable. Quand je l’ai reçu, il comportait des références circulaires non voulues, et de nombreux liens vers d’autres classeurs.

Ma mission – si je l’acceptais… – consistait à nettoyer tout cela afin d’avoir un classeur propre et facile à utiliser. Je devais en outre, et là cela dépassait de loin ce que l’on peut demander à un simple expert d’Excel, garantir que toutes les formules de tous les onglets étaient cohérentes avec toutes les stipulations des 70 pages de contrat.

C’est un projet qui a été mené en 2006, sur une durée de 3 mois, et dans lequel j’ai travaillé – pour la partie financière – en collaboration avec un de mes collègues d’HEC, le professeur de finance David Thesmar.

Une société de placement financier

J’ai commencé à travailler cet été avec une petite entreprise – moins de 10 personnes – spécialisée dans les placements financiers. Cette entreprise a une vingtaine d’agents en France qui vendent leurs produits à leurs propres clients ; les produits de son catalogue sont sélectionnés par mon client parmi des produits financiers et/ou d’assurance vendus par des compagnies financières et des sociétés d’assurance.

Il faut donc gérer la prise des commandes par les clients finaux : quel produit ils ont acheté, provenant de quel fournisseur, en passant par quel agent,…

Puis il faut gérer la facturation de tout cela, avec le calcul des commissions aux deux niveaux : la commission versée par le fournisseur du produit à mon client, la commission versée par le client à ses agents.

Quand je suis arrivé, il y avait un certain nombre de classeurs Excel qui étaient utilisés selon que la commande avait été passée avec tel ou tel agent, ou avec tel ou tel fournisseur. J’ai mis en place un classeur de saisie généralisé, et un outil de préparation de la facture.

Voici ce que dit mon client de cette intervention : « En cinq jours de travail de Monsieur Thiriez, nous avons divisé par cinq le temps de saisie d’une affaire. Dans l’étape du calcul des commissions et du contrôle, nous pouvons aujourd’hui nous passer des services du salarié que nous avions employé expressément pour cela. »

Certes, la plupart de mes clients sont de grosses entreprises car – assez logiquement – plus une entreprise est importante, plus je peux lui faire gagner de temps et d’argent. Mais il est intéressant de noter que, même pour une petite entreprise comme celle-ci, le rendement sur investissement de l’audit & amélioration des modèles a pu être extrêmement intéressant, avec un payback nettement inférieur à l’année, moins de six mois dans cet exemple précis.

Un cabinet d’avocats

Durant les six derniers mois, j’ai été appelé à assister un cabinet d’une trentaine d’avocats où il y a une trentaine de fichiers Excel de reporting, avec de multiples liens entre classeurs, et où la construction du rapport de fin d’année était devenue – de fil en aiguille, année après année – de plus en plus lourde. A telle enseigne qu’en décembre 2011, le rapport de l’année 2010 était encore loin d’être prêt !

Là, mon intervention a consisté principalement à rendre tous les classeurs plus flexibles, de façon que le passage d’une année à l’autre se révèle le plus léger et rapide possible. J’ai eu là l’occasion de réduire de façon très significative le nombre d’onglets de chaque classeur, avec des réductions parfois supérieures à 50%. Les graphes, qui étaient tous construits au rythme d’un ou plusieurs par an, ont été rendu flexibles, avec la possibilité de représenter en un seul graphe ce qui auparavant en demandait une dizaine.

A titre indicatif, il y avait dans un classeur un plan glissant sut 4 ans, avec une dizaine d’onglets : un onglet 2006-2009, un onglet 2005-2008, un onglet 2004-2007,… J’ai remplacé tout cela par un onglet unique où l’on indique l’année de départ et l’année de fin, et qui calcule le plan glissant sur toutes les années concernées. Résultat : un seul onglet, et en prime la possibilité nouvelle de consulter les plans sur un nombre variable d’années, pas forcément quatre.

Les simplifications ont été drastiques, et elles auraient même pu être plus significatives encore si je n’avais pas reçu pour instruction fondamentale, dès le départ, de changer le moins possible les habitudes de travail et processus antérieurs.

Dans ce cas, il s’est agi d’une intervention d’une quinzaine de jours, suite à laquelle le reporting est devenu à la fois beaucoup plus simple et nettement plus flexible.

En guise de conclusion...

Ces exemples montrent qu’il est possible – avec l’audit et l’amélioration de modèles existants – d’améliorer de façon très significative la qualité des modèles Excel.

Que cela peut même être très rentable pour une entreprise de petite taille (10 salariés dans le second exemple, moins de 50 dans le troisième).

Et enfin que, même avec des honoraires par journée correspondant à ceux de consultants seniors dans les meilleurs cabinets de conseil, on peut amortir très rapidement, le plus souvent en moins d’un an, l’investissement que ces améliorations requièrent.

Je vous souhaite à tous une Excellente Année 2012 !

23 décembre 2011

Spreasheet Detective (suite et fin)

Dans le dernier message, nous avons commencé à voir comment fonctionne l'add-in Spreadsheet Detective, mon outil préféré pour l’audit de modèles Excel. Voici son ruban complet en Excel 2007 :

Nous avons vu dans l’article précédent des copies d’écran des effets de la commande « Shade » et de la commande « Report ».

La commande « Annotate » crée – sur l’ensemble de la feuille active – des sortes d’annotations liées à chaque formule originale. C’est donc en quelque sorte un croisement entre ce que font les deux commandes « Shade » et « Report ». Pour ma part, je trouve cela moins lisible que les résultats de ces deux autres commandes.

La commande « One Cell » fait la même chose que la commande « Annotate », mais seulement pour la cellule active. Dans la copie d’écran ci-dessus, nous voyons ce qui se passe avec cette commande quand la cellule active est la cellule D7.

La commande « Map Report » crée une carte de la feuille active avec, pour chaque cellule dont la formule est recopiée depuis une autre cellule, un symbole permettant de voir si la recopie vient de la gauche, du haut, ou même d’ailleurs.

La commande « Workbook Report » crée une feuille de statistiques relatives à chaque feuille du classeur.

Notons encore la présence de commandes pour l’analyse de sensibilité, l’analyse des précédents et des successeurs, la comparaison de deux classeurs, et enfin la gestion des noms.

La gestion des noms est un domaine où Excel était très pauvre jusqu’à la version 2003 incluse. Depuis Excel 2007, il y a du progrès – relisez à ce titre l’article « Le gestionnaire des noms » du 17 juillet 2011 et les deux articles suivants – , mais on est encore loin de ce que l’add-in Name Manager permet de réaliser.

En conclusion, le Spreadsheet Detective est pour moi un utilitaire réellement utile…

17 décembre 2011

Un add-in d’audit de modèle

Une partie non négligeable de mon activité de consultant consiste à auditer des modèles existants afin d’atteindre un ou plusieurs des objectifs suivants :

nettoyer et/ou réparer des modèles devenus ingérables ou erronés ;

réduire la taille de modèles devenus énormes (plus de 20 Mo) ;

accélérer le temps de calcul, parfois jusqu’à un facteur 100 ou plus ;

rendre le modèle beaucoup plus flexible.

Pour en savoir plus sur ce que l’on peut gagner avec ce type d’audit, lisez l’article « Le re-engineering de vos modèles » du 3 mars 2008.

Voici des endroits où vous trouverez des add-ins d’audit :

Spreadsheet Detective : www.spreadsheetdetective.com/

RefTree Analyzer : www.jkp-ads.com/RefTreeAnalyser.asp

Reference Browser : www.mang.canterbury.ac.nz/people/jfraffen/sscom/refbrowser.htm

Pour faciliter l’audit de modèles, j’utilise pour ma part l’add-in « Spreadsheet Detective », que je vous avais présenté une première fois le 22 avril 2006. Nous allons voir aujourd’hui à quoi il ressemble dans sa version Excel 2007.

J’ai choisi un tableau où, dans trois blocs de cellules (D9:D10, D14:D16 et G6:G8), les formules avaient été détruites et remplacées par des valeurs. J’ai effacé tous les encadrements et toutes les couleurs de cellules pour mieux voir le résultat et j’ai lancé la commande « Shade ».

Voici le résultat : toutes les cellules ayant une formule originale ont reçu un quadrillage bleu, les formules recopiées depuis le haut ont des traits verticaux, et celles recopiées à droite (il n’y en a pas ici) ont des traits horizontaux. On constate ainsi qu’en I12, il y a une seconde formule originale pour la colonne I. En outre, chaque cellule avec une formule originale a reçu un commentaire explicitant la formule.

Ensuite, avec la commande « Report », j’ai créé un rapport détaillant toutes les formules originales de la feuille active. Les formules sont écrites en bleu, avec en vert l’identification de ce à quoi correspond chaque référence.

Faites un double clic sur cette image pour la voir à son échelle originale...

Nous venons de voir seulement deux des vingt et quelques commandes de ce logiciel. Nous verrons dans le prochain article certaines des autres commandes.



12 décembre 2011

VBA Excel : liste de mots réservés

La liste ci-dessous est une liste que j’ai construite de mots réservés du VBA Excel (cf. article précédent).

Dernière minute...

En fait, la liste complète est bien plus longue, comme vous pourrez le constater en lisant les deux commentaires ci-après.

En fouillant dans mes archives, j'ai découvert que j'avais déjà diffusé cette liste en 2002 avec le n°74 de la lettre d'Excel (que j'ai publiée de 1990 à 2005).

Cette liste est conséquente : elle contient 1.913 mots réservés soit à peu de choses près autant de mot que dans le vocabulaire d'un humain lambda...

Je ne peux donc pas la reproduire ici, mais vous savez à présent où la trouver...

08 décembre 2011

Les mots réservés du VBA Excel

Le langage VBA d’Excel comporte de nombreux mots réservés. Si on les utilise par erreur, on obtient dans le meilleur des cas une erreur de syntaxe. Là, ce n’est pas trop grave car on voit qu’il y a un problème et on peut y remédier :)

Il peut en revanche y avoir un problème quand cela ne provoque pas d’erreur de syntaxe et que la variable ne fait pas exactement ce que l’on en attend.

En me baladant sur Google dans l’espoir de trouver une liste de ces mots réservés, je n’ai quasiment trouvé que des gens déplorant que cette liste n’existe apparemment pas, en tout cas dans ce qui est diffusé par Microsoft ou par d’autres spécialistes Excel.

Comment s’aperçoit-on qu’un mot est réservé ?

Une bonne méthode consiste à taper ce mot dans une instruction. Si l’on aboutit à une erreur de syntaxe, il est clair qu’il est réservé.

Sinon, si la syntaxe passe mais que le mot se met à afficher une ou plusieurs majuscules, c’est aussi qu’il est réservé.

Nous le voyons dans les deux copies d'écran ci-dessous. Dans le premier cas,
« friend » engendre une erreur de syntaxe. Dans le second cas, l’instruction sera acceptée, mais elle affichera « Description = 3 », la majuscule indiquant que le mot était réservé.

Remarque – C’est pour cette raison que, chaque fois que je définis une variable, je m’arrange pour lui donner au moins une majuscule. Je peux ainsi vérifier, quand je l’utilise, que les bonnes majuscules apparaissent au bon endroit.

Dans le prochain article, je vous donnerai ma liste des mots réservés du VBA Excel.

04 décembre 2011

Des graphes en waterfall gratuits !

Les graphes en waterfall – aussi appelés bridges – sont régulièrement utilisés en finance. Ils servent à mettre en relief l’impact d’un certain nombre de recettes et de dépenses sur une situation donnée, les dépenses étant représentées en rouge et les recettes en vert.

Dans mon article du 15 août 2011, je vous ai montré comment construire un waterfall dans Excel. Tout cela prend quand même du temps à construire, même quand vous savez exactement ce que vous devez faire.

Dans les articles « UpSlide pour Excel » des 6 et 10 mai 2011, je vous ai montré comment un bouton de l’add-in UpSlide, créé par la société Finance 3.1, permettait de créer un waterfall en un seul clic.

Finance 3.1 vient de mettre au point un service gratuit de réalisation de waterfalls : vous entrez vos données sur le site www.waterfall-chart.com, vous entrez vos coordonnées et vous cliquez sur la flèche de validation. Voici ce que j’ai entré dans la zone de saisie à gauche :

Voici enfin le graphe obtenu sur un fichier Excel que j’ai reçu en PJ dans un mail –en quelques minutes ! – de la part de Finance 3.1.

Bien entendu, le tracé de waterfalls n’est que l’une des nombreuses fonctionnalités offertes par les barres d’outils UpSlide (www.upslide.fr)...