Monsieur Excel
Pour tout savoir faire sur Excel !

28 septembre 2015

Modèle de gestion de locations (b)

Dans cet article, nous continuons à travailler sur le modèle présenté dans l’article précédent, le modèle d’analyse de la rentabilité de la location d’une série d’appartements.

En fait, à la fin d’une simulation, Crystal Ball en fournit pas qu’un graphe de prévision et des statistiques pour chaque prévision. Il est possible d’utiliser d’autres outils d’analyse.

On peut ainsi demander à Crystal Ball d’effectuer une analyse de sensibilité. Dans notre cas, nous souhaitons par exemple savoir quels sont les facteurs clefs de la rémunération, le « Take home pay ». Voici ce que cela donne : 


Nous constatons, non sans surprise, que le facteur le plus important est le nombre d’appartements loués, et que le second facteur le plus important est le loyer. Ces deux facteurs ont un impact positif sur la rémunération. En revanche, les variables de coût ont – ce qui n’est pas surprenant – un impact négatif, celui du coût fixe étant quasiment négligeable.

Remarque – Les statisticiens seront peut-être surpris de voir ici des valeurs négatives pour une contribution à la variance. Qu’ils se rassurent, ces contributions sont bien positives, ce que l’on vérifie en constatant que 50,7% + 36,2% + 12,9% + 0,1% = 100% (aux arrondis près). Crystal Ball a simplement ajouté un signe indiquant le type d’impact de chaque variable.

Crystal Ball permet, en direct, deux analyses de sensibilité : la contribution à la variance et la corrélation des rangs.

La contribution à la variance montre quelle part chaque variable joue dans la variabilité de y (ici, la rémunération). Le total de ces impacts fait donc 100%. C’est l’analyse de la sensibilité de y par rapport à l’ensemble des x.

Pour calculer la corrélation des rangs, on prend une à une chaque variable et l’on calcule la corrélation entre les rangs des valeurs de y et les rangs des valeurs de la variable. C’est donc l’analyse de la sensibilité de y par rapport à chacun des x pris séparément.

Bien entendu, les deux analyses de sensibilité aboutissent en général au même classement des x, mais il peut y avoir des différences dans les sensibilités relatives.


De façon un peu plus indirecte, Crystal Ball permet de produire deux autres analyses de sensibilité encore, le Tornado Chart et le Spider Graph.Mais cela nous entraînerait trop loin de vous les montrer ici.

22 septembre 2015

Modèle de gestion de locations (a)

Dans le dernier article, nous avons vu comment créer un modèle de simulation bien plus rapidement, et aussi de façon nettement plus fiable, en utilisant l’add-in Crystal Ball. Ceci dit, notre exemple de lancement de dés était un exemple très simple. Aujourd’hui, nous verrons comment utiliser Crystal Ball pour la création d’un modèle plus compliqué.

Dans le cas qui nous intéresse aujourd’hui, nous supposons que nous avons 100 appartements à louer. Il y a quatre variables aléatoires, le nombre d’appartements que l’on parvient à louer (entre 70 et 100), le loyer moyen, les coûts fixes et les coûts variables. Les colonnes E et F fournissent les paramètres correspondant aux cas le plus favorable (Best Case) et le pire (Worst Case). On se demande quelle est la probabilité de gagner au moins 4.300 € avec les locations.

Nous avons choisi des lois triangulaires pour les deux premières variables, en C3 et C4, et des lois uniformes pour les deux variables de coût, en C7 et C9. Vous voyez dans la copie d’écran l’écran de définition de l’hypothèse « Rental fee per unit ».


Remarque – Dans Crystal Ball, par défaut, les hypothèses sont en fond vert avec un commentaire et les prévisions en fond bleu.

Nous lançons la simulation, qui est toujours de 5.000 itérations par défaut. Cette simulation dure moins de 25 secondes, ce qui est bien plus rapide que ce que nous pourrions faire sans Crystal Ball. Et, surtout, nous obtenons aussitôt un graphe de prévision (cf. image ci-dessous) ainsi que des statistiques détaillées.


En entrant 4.300 dans la partie inférieure gauche du graphe de prévision, nous obtenons immédiatement la réponse à notre question : la probabilité de gagner au moins 4.300 € est de 32,26%.

17 septembre 2015

Les dés avec Crystal Ball

Une autre façon de résoudre le problème du lancement de dés de nos conq derniers articles est d’utiliser un add-in de simulation probabiliste. Il en existe plusieurs en freeware ou shareware, mais de qualité non professionnelle. Il y a aussi deux produits professionnels de qualité, Crystal Ball et @risk.

Je me suis intéressé à ces deux produits dès 1992. A l’époque, Crystal Ball avait 35% du marché, le reste étant pris par son concurrent. A près avoir bien analysé les deux produits, j’ai choisi Crystal Ball, que je trouvais beaucoup plus convivial, ce qui est encore sans conteste le cas aujourd’hui. J’ai contacté la société Decisioneering qui en était l’auteur, je suis devenu le premier distributeur de cet add-in en France, et j’ai depuis traduit plusieurs versions de ce produit génial.

Le vocabulaire de Crystal Ball est simple : on définit comme hypothèses les cellules constantes appelées à devenir des variables aléatoires, et comme prévisions les résultats importants.

Avec Crystal Ball, notre modèle de lancement de dés se construit en moins d’une minute ! On entre 0 en A1, la formule =A1 en A2 ; on définit A1 comme une hypothèse (variable aléatoire) discrète de 1 à 6 (cf. la copie d’écran encadrée de bleu) et A2 comme prévision de nom «  ».


Il ne reste plus qu’à lancer la simulation… Comme nous en avons facilement les moyens avec Crystal Ball, nous lançons la simulation pour les 5.000 itérations proposées par défaut.

La simulation totale a pris moins de 10 secondes pour 5.000 itérations, et nous obtenons aussitôt l’histogramme du résultat et un tableau de statistiques.


Certes, cet exemple est simpliste, mais il est intéressant de noter que la création du modèle total a pris moins d’une minute, rien à voir avec nos modèles antérieurs. 

De plus, nous n’avons pas eu à gérer la façon dont Excel recalcule ou pas, ni les conséquences éventuelles de l’ordre des calculs dans Excel !


11 septembre 2015

Mes formations en novembre 2015

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de novembre 2015. Chaque formation est limitée à huit participants.

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision. Elles auront lieu à Paris, au 14 avenue de l’Opéra.

La formation « Création de tableau de bord sous Excel » vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article « Notre premier tableau de bord » du 22 mai 2013.

La formation « Modélisation avec Excel » est mon best-seller intégral : c’est une formation que j’ai animée plus de 70 fois, en inter ou en intra, avec un taux de satisfaction supérieur à 97%. Avec cette formation, n’importe quel utilisateur d’Excel parvient en deux jours à atteindre le niveau du top 5% des utilisateurs d’Excel.

Avec la formation « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore.

Toutes mes formations (cf. catalogue sur le site) peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.
Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.


06 septembre 2015

Les dés sur (dans) la table

Un message que j’ai toujours transmis, aussi bien dans « La lettre d’Excel » que j’ai publiée de 1990 à 2005 que dans ce blog que je rédige depuis octobre 2005 – vous vous rendez compte, 25 ans de publications sur Excel ! – c’est qu’il y a toujours plusieurs façons d’atteindre le même but dans Excel.

C’est encore vrai avec notre exemple de tirage de 10 dés dans Excel, développé dans les quatre articles précédents…

Une solution totalement différente revient à créer une table (cf. image encadrée de bleu) dans laquelle je calcule en série la formule de B2 : =ent(1+6*alea()).

La formule =nb.si($B$2:$B$11;A14)/10 en B14, recopiée vers le bas, calcule le pourcentage avec lequel chaque dé est obtenu.

Chaque fois que l’on appuie sur [F9], la table est recalculée…

Et voilà le travail… Brillant, n’est-ce pas ?


Remarque – Vous vous demandez peut-être pourquoi je vous ai montré, dans les articles précédents, une façon plus compliquée d’organiser la simulation du lancement de 10 dés. En fait, c’était intéressant, non seulement pour vous montrer comment gérer le calcul itératif, mais aussi pour prouver qu’il est important de savoir comment Excel gère ses calculs…

01 septembre 2015

Une macro pour les dés

Les trois articles précédents ont prouvé que, même si la fonction alea() permettait de faire de la simulation probabiliste dans Excel, ce n’était pas une sinécure.  En effet, les résultats obtenus sont faux si l’on ignore l’un des deux éléments suivants.

Le premier est qu’Excel ne calcule à chaque itération que les cellules qu’il lui semble nécessaire de calculer et cela n’est pas forcément ce que vous voudriez. C’est ce que nous avons constaté dans le premier article.

Le second est qu’Excel effectue ses calculs dans un certain ordre : ligne par ligne puis, dans chaque ligne, colonne par colonne. Cela peut engendrer des erreurs dans des simulations si vous ne respectez pas cet ordre de calcul dans la structure de votre modèle. C’est ce que nous avons constaté dans le second article.

Pour faire toutes nos simulations, nous avons dû à chaque fois modifier le drapeau et lancer le calcul, sans parler de la nécessité a priori de passer par la commande « Options » pour modifier le mode de calcul.

Ces dernières opérations sont bien simplifiées avec deux boutons macro, comme nous le voyons dans la copie d’écran ci-dessous.