Le bug de la valeur actuelle nette

Merci à Aigle34 qui, dans le premier commentaire lié à mon message du 24 mai (il y a quatre jours), nous avertissait du bug lié au calcul de la VAN. Microsoft nous a en effet sorti là un bug sérieux, qui mérite quelques commentaires.

Reprenons – dans les lignes 1 à 7 – notre modèle antérieur, à une différence près : nous avons modifié l’investissement initial et détruit le contenu de la cellule E2.

Nous constatons alors que les deux formules en C6 et C7 ne donnent plus le même résultat :

C’est la cellule C7 qui affiche le bon résultat. En effet, quand un montant de l’échéancier est vide – au lieu d’afficher la valeur 0 – Excel « saute » l’année correspondante !

On le vérifie aisément dans le bloc A13:F15 qui engendre en C15 le même résultat qu’en C6 ! Alors que, si le montant à 0 € est entré tel quel – comme en E10 – on obtient bien en C11 le même résultat qu’en C7…

Eh oui, encore un bug !

Calcul de la valeur actuelle nette

La valeur actuelle nette d’un projet est la valeur de ce projet aujourd’hui, sachant qu’il y aura des dépenses et des recettes étalées dans le temps – en général plusieurs années – et en respectant un taux d’actualisation donné.

La syntaxe de la fonction est : =van(taux;cash-flows)

…où cash-flows est la liste des cash-flows dans laquelle on peut utiliser des références individuelles de cellule et/ou des références de blocs de cellules.

Voici un exemple simple dans lequel on investit 900.000 € en 2008, avec des recettes de 250.000 € par an de 2009 à 2013. Si le taux d’actualisation est de 11,5 %, cela donne une VAN (Valeur Actuelle Nette) de 12.469 € :

Nous avons montré en bleu les formules utilisées en C6 et C7.

Le taux d’actualisation joue un double rôle. D’une part, il intègre le fait que de disposer d’un euro aujourd’hui a plus de valeur que d’en disposer dans un an. D’autre part, il sert à jouer un rôle de protection : on prendra ainsi un taux d’actualisation plus élevé pour un projet dans un pays « à risque » que pour le même projet en France ou aux Etats-Unis.

Remarque 1 – On peut regretter que Microsoft ait programmé la fonction VAN de telle façon qu’elle ne prenne pas en compte l’année 0 mais débute en année 1, ce qui oblige à ajouter le cash-flow de l’année 0 (ici, B2).

Remarque 2 – Nous pouvons remplacer cette fonction par la formule utilisée en C7 : il suffit de diviser le cash-flow de l’année n par (1+i)^n et de faire la somme des cash-flows actualisés ainsi obtenus.

Remarque 3 – Bien que notre formule avec sommeprod() soit plus lourde, elle est toutefois plus « propre » dans la mesure où elle prend en compte l’ensemble des cash-flows, sans traiter de façon particulière celui de l’année 0.

Inversion prénom(s) et nom

Dans une liste en colonne A, nous disposons du nom de famille, suivi par une virgule et enfin le(s) prénom(s) de la personne concernée. L'objectif est d’obtenir une liste avec le(s) prénom(s) puis – séparé par un simple espace – le nom de famille.

La formule utilisée en B2, puis recopiée vers le bas, est :
=stxt(A2;cherche(",";A2)+1;100)&" "&gauche(A2;cherche(",";A2)-1)

Remarque 1 – Nous avons vu grand pour le troisième argument de stxt() mais cela ne gêne en rien le résultat…

Remarque 2 – Nous sommes obligés de réaliser deux fois la même recherche de la position de la virgule, mais c’est le prix à payer pour tout calculer en une seule formule.

Remarque 3 – Ceux qui n’apprécient pas la fonction stxt() peuvent utiliser une formule un peu plus longue : =droite(A4;nbcar(A4)-cherche(",";A4))&" "&gauche(A4;cherche(",";A4)-1)

Un partenariat avec EuroDécision

Ma société de conseil – Logma SA – vient de signer un partenariat avec la société EuroDécision, une société de conseil basée elle aussi à Versailles et dont l’activité principale est l’optimisation de ressources.

Comme pour ma part je suis spécialisé depuis toujours dans la modélisation et – depuis une quinzaine d’années – dans la simulation probabiliste, nos activités sont complémentaires. Ensemble, nous pouvons proposer toute solution faisant appel à l’optimisation ou à la simulation.

http://www.eurodecision.com/

Nous avons décidé de proposer ensemble les deux formations que j’ai déjà annoncées sur ce blog depuis quelque temps, ainsi que deux autres formations – sur la modélisation du risque – que j’ai déjà fréquemment animées. Voici donc les dates de ces formations :

Modélisation avec Excel (2 jours) : mardi 27 mai et mardi 3 juin 2008.

Découverte du Visual Basic (2 jours) : mercredi 18 et jeudi 19 juin 2008.

La modélisation du risque : vendredi 27 juin 2008.

Utilisation de Crystal Ball Pro : lundi 30 juin 2008.

Les deux premières formations ont lieu au centre de Paris, les deux dernières à Versailles dans les locaux d’EuroDécision.

Si vous souhaitez vous inscrire ou me poser des questions au sujet de ces formations, vous pouvez me contacter à : thiriez@hec.fr

La somme d’une valeur sur trois

Dans le tableau ci-dessous, on souhaite calculer – en D1 et D2 – la somme d’une cellule sur trois de la colonne A soit – dans cet exemple – obtenir la somme des trois cellules à fond coloré.

La solution la plus simple est celle proposée en D1, qui fait appel à la fonction SommeProd() et utilise aussi les fonctions Ligne() et Mod() :

=sommeprod(A1:A9*(mod(ligne(A1:A9);3)=0))

Le principe en est que l’on effectue la somme des valeurs situées dans les lignes où le reste de la division du numéro de la ligne par 3 est nul.

La cellule D2 représente une solution un peu plus compacte, mais qui exige en revanche une validation matricielle avec [Ctrl]-[Maj]-[Entrée] :

=somme(A1:A9*(mod(ligne(A1:A9);3)=0))

Cela mis à part, cette seconde solution est basée sur exactement le même principe.

Ce n’est pas la première fois que nous proposons des solutions alternatives entre SommeProd() et une formule matricielle…

Nom de fichier : une autre solution

Nous l’avons souvent déclaré : dans Excel – comme d’ailleurs avec toute modélisation – il y a souvent plusieurs façons d’atteindre un objectif fixé.

Il y a quatre jours, nous vous avons présenté une formule de validation de données qui servait à vérifier que – pour un nom de classeur – aucun caractère interdit n’était utilisé.

Voici deux autres formules de validation que vous auriez aussi pu utiliser…

Avec la fonction EstErreur()…

La solution suivante est plus simple à imaginer et plus compréhensible que la solution proposée il y a quatre jours, mais elle est plus lourde avec 257 caractères au lieu des 77 caractères de notre solution originale :

=et(esterreur(trouve("/";$B$1));esterreur(trouve("\";$B$1));esterreur(trouve(":";
$B$1));esterreur(trouve("*";$B$1));esterreur(trouve("?";$B$1));esterreur(trouve(
"";$B$1));esterreur(trouve("<";$B$1));esterreur(trouve(">";$B$1));esterreur(
trouve("""";$B$1)))

Avec la fonction SommeProd()…

En revanche, voici une solution qui non seulement se révèle presque aussi compacte (80 caractères) que notre solution initiale mais aussi utilise le SommeProd() avec « -- » que nous avions découvert dans le message du 27 mars 2008 :

=sommeprod(--(non(esterreur(trouve(stxt("\:/*?<>""";ligne($1:$9);1);$B$1)))))=0

Cette formule renvoie VRAI quand le nombre total de fois où l’on a trouvé un des caractères interdits est nul.

Remarque – Un avantage notable de cette dernière solution est qu’il n’est plus nécessaire d’indiquer dans la formule le nombre de caractères autorisé pour le nom du classeur : le seul paramètre à régler est le nombre (ici, 9) des caractères prohibés.

Contrôle du nom du classeur

Dans une cellule de votre modèle, un utilisateur doit saisir le nom à donner à un classeur. Le classeur en question sera ensuite enregistré sous ce nom par une macro. Le problème est que tous les caractères ne sont pas autorisés dans un nom de classeur. Supposons par exemple que vous souhaitiez interdire les caractères suivants :

\ : / * ? < > "

La solution consiste à effectuer le contrôle par la commande « Données … Validation », en sélectionnant l’option « Personnalisé » dans laquelle vous entrez la formule suivante, si le nom du classeur est en B1 :

=sommeprod(nbcar(substitue("\:/*?<>""";stxt(B1;ligne($1:$15);1);"")))=15*9

En fait, si le nom du classeur est « Ventes 2007 », la formule stxt(B1;ligne($1:$15);1) sera évaluée comme le vecteur :

{"V";"e";"n";"t";"e";"s";" ";"2";"0";"0";"7";"";"";"";""}

La fonction substitue() élimine de ce vecteur tous les caractères interdits : s’il y a au moins un caractère interdit dans le nom proposé, le total du nombre de caractères ne sera plus de 15 fois 9.

Remarque 1 – Les guillemets doublés représentent le caractère « " ». On aurait aussi pu utiliser la formulation "\:/*?<>" & car(34) comme premier argument.

Remarque 2 – La formule proposée ici suppose que le nom du classeur comporte au maximum 15 caractères et qu’il y a exactement 9 caractères interdits. Il faut le cas échéant adapter les valeurs de 15 et 9 en fonction des circonstances.

Remarque 3 – On peut noter l’utilisation tout à fait originale de la fonction stxt() qui, avec un second argument vectoriel, engendre du coup un résultat lui aussi vectoriel.