Monsieur Excel
Pour tout savoir faire sur Excel !

28 octobre 2015

Location d’appartements (b)

Pour analyser des scénarios, nous avons la chance de disposer dans Excel d’un add-in (complément, en français), le Gestionnaire de scénarios.

Certains compléments livrés avec Excel ne sont pas installés par défaut. C’est en particulier le cas du Solveur, particulièrement précieux pour tous ceux qui ont recours à l’optimisation ou à la résolution de problèmes, par exemple celle de systèmes d’équations. D’autres, comme le Gestionnaire de scénarios ou l’Analysis Toolpak, sont installés par défaut.

Création du premier scénario

Sélectionnons – en maintenant la touche [Ctrl] enfoncée – les quatre cellules encadrées de la colonne C (le cas probable) puis appelons la commande « Analyse scénarios » du bloc « Outils de données » de l’onglet « Données ». Cliquons ensuite sur « Ajouter » et donnons le nolm « Probable » à ce premier scénario et validons par « OK ». Nous constatons que les valeurs actuelles sont les bonnes.

Création des autres scénarios

Il ne reste plus qu’à cliquer de nouveau sur « Ajouter », puis à créer le scénario « Pessimiste », cette fois en entrant les valeurs correspondantes, puis à faire de même pour le scénario « Optimiste » et terminer en validant par « OK ».

On obtient alors l’écran reproduit ci-dessous si l’on sélectionne « Optimiste » puis que l’on clique dans le bouton « Afficher ».


Remarque – Nous avons utilisé ci-dessus trois scénarios, mais nous pouvions en fait en créer autant que nous le voulions.

Cet outils est donc extrêmement pratique pour – avant une réunion – préparer une série de scénarios et les afficher ensuite à la demande sans perdre du temps – et risquer de se tromper – à saisir les valeurs en temps réel pendant la réunion.

Avec le bouton « Synthèse », on peut alors créer d’un clic une feuille dans laquelle un tableau affiche les valeurs courantes, plus celles des trois scénarios pour les variables sélectionnées. On pourra par exemple afficher les valeurs de la marge brute et de la rénumération mensuelle pour chacun de quatre cas : les valeurs actuelles des quatre cellules paramétrées ainsi que celles correspondant aux trois scénarios.


23 octobre 2015

Location d’appartements (a)

Avec les articles de ces dernières semaines, nous vous avons familiarisés avec la simulation probabiliste. Nous en profitons à présent pour analyser un cas concret et voir comment – avec les différents outils disponibles sur Excel – vous avez la possibilité d’affiner votre analyse et de prendre de meilleurs décisions.

Le cas « Location d’appartements »

Vous cherchez un job et vous avez reçu deux propositions. Dans le premier cas, on vous propose un job salarié avec un fixe de 4.300 € par mois. Dans le second cas, vous êtes appelé à travailler pour une société de gestion immobilitère qui vous propose 3.000 €/mois en rémunération fixe plus une commission de 1% sur la marge brute.

Dans cette seconde hypothèse, vous avez un parc de 100 appartements à louer, sachant que le nombre d’appartements loués a toujours fluctué entre 70 et 100, avec la valeur la plus probable à 85. Il y a des coûts fixes mensuels de 15.500 €/mois en moyenne, pouvant aller de 15.000 € à 15.600 €. Plus des coûts variables de 600 €/mois par appartement loué, ces coûts fixes pouvant aller de 500 €/mois à 700 €/mois. Enfin, le loyer moyen a une valeur la plus probable à 2.200 €/mois, avec au pire 2.000 €/mois et au mieux 2.500 €/mois.

Le modèle Excel représenté ci-dessous résume la situation en trois colonnes : le cas le plus probable, le cas optimiste et le cas pessimiste. Nous vous laissons retrouver les formules : elles sont simples et il n’y en a que 6 par colonne, toutes les mêmes…


Comment gérer l’incertitude ?

Nous nous trouvions dans une situation que les experts en modélisation appelent la « prise de décision en avenir incertain ».

Face à une telle situation, nous pouvons envisager quatre approches :
  • ne considérer que le résultat le plus probable ;
  • analyser les trois scénarios classiques : pessimiste, probable, optimiste ;
  • étudier toute une série de scénarios (scenarii pour les puristes) possibles ;
  • réaliser une simulation probabiliste.
Il semblerait logique que l’on trouve ces quatre solutions de plus en plus fines. Vous seriez cependant supris de voir combien d’entreprises – aujourd’hui encore ! – se satisfont de la seule analyse en trois scénarios…

Le modèle reproduit ci-dessus permet d’utiliser la première approche et nous fournit les bases de la seconde approche. Nous verrons dans les articles suivants comment utiliser Excel pour les trois dernières approches.


18 octobre 2015

Un modèle de pharmacie (c)

Nous avons annoncé dans les articles précédents que Crystal Ball, outre les résultats graphiques et statistiques, vous permet d’effectuer une analyse de sensibilité, ce qui est particulièrement précieux.

Dans l’exemple ci-dessous, nous avons demandé une analyse de sensibilité de la valeur actuelle nette. Nous constatons, sans surprise d’ailleurs, qu’il y a deux facteurs déterminants, le rang d’arrivée sur le marché et l’année de sortie.


Crystal Ball fournit en standard deux types d’analyse de sensibilité : la contribution à la variance – ce qui est le cas du graphe ci-dessus – et la corrélation des rangs.

Pour la contribution à la variance, Crystal Ball calcule la part qu’a joué chaque variable dans la variabilité du résultat final. C’est donc une analyse de la sensibilité du résultat y par rapport à l’ensemble des xi. La somme des sensibilités (en valeur absolue car Crystal Ball ajoute un signe pour indiquer le sens de la sensibilité) est alors égale à 1. 

Pour la corrélation des rangs, Crystal Ball analyse tour à tour la corrélation entre le résultat y par rapport à chacun des xi pris séparément. Mais, au lieu de calculer la corrélation entre les valeurs, il calcule la corrélation entre les rangs des valeurs : 1 pour la plus grande, 2 pour la seconde,…


Nous voyons ci-dessous ce que donne cette seconde analyse de sensibilité :


11 octobre 2015

Un modèle de pharmacie (b)

Reprenons le modèle de pharmacie présenté dans le dernier article. Nous avions défini 12 hypothèses, les cellules en fond vert de l’article précédent, et une prévision, la cellule B16, en fond bleu.

Ces fonds sont mis en couleurs automatiquement par Crystal Ball, et vous pouvez en modifier la couleur par défaut. Nous ne vous le conseillons pas : en effet, quand on échange avec d’autres utilisateurs des modèles Excel utilisant Crystal Ball, il est bien pratique de pouvoir identifier d’un coup d’œil les hypothèses et les prévisions.

Par défaut, Crystal Ball propose l’exécution de 5.000 itérations, nombre que vous pouvez aisément modifier avant le lancement de la simulation.

A titre indicatif, si vous essayez d’évaluer par simulation un pourcentage de l’ordre de 50%, l’écart-type est alors de 1/(2*racine(5000)). On a donc 95% de chances que le pourcentage p réel soit dans la fourchette (p – 1,43% ; p + 1,43%). En effet, avec la loi normale, qui s’applique à ce genre de situation, l’intervalle de probabilité à 95% se situe entre entre p-2s et p+2s).

Remarque – La plupart des journalistes vous racontent donc des craques quand il font quasiment des thèses de doctorat sur le fait qu’un politicien a baissé ou monté de 2 points dans les sondages. La plupart des sondages politiques sont basés sur 1.000 personnes, auquel car la fourchette d’évaluation à 95% d’un pourcentage p est (p-3,16% ; p+3,16%). La fourchette d’évaluation étant donc de plus de 6 points de pourcentage, on ne peut pas dire grand-chose de significatif sur une hausse ou une baisse de deux points !

La simulation entière, pour les 5.000 itérations, prend 5 secondes sur mon ordinateur. Autant dire que si j’avais développé un modèle sans Crystal Ball, avec ou sans VBA, j’en serais loin.  En outre, Crystal Ball vous fournit un graphe de prévision (cf. ci-dessous) ainsi que des statistiques de toutes sortes.


Avant qu’il n’existe des outils tels que Crystal Ball, de nombreuses entreprises, pour mieux appréhender des résultats aléatoires, se contentaient – il y en a encore qui ne font pas mieux aujourdh’ui ! – d’évaluer trois résultats : hypothèse basse – hypothèse probable – hypothèse haute.

Le graphe ci-dessus nous en apprend beaucoup plus ! Nous découvrons ainsi de façon très claire qu’il y a trois groupes de résultats. Le groupe le plus à droite (avec lui-même trois sous-groupes correspondant à l’année de sortie), celui qui se situe à droite de la moyenne, correspond au rang d’arrivée n°1 sur le marché.

Autre indication intéressante : il est de cas, comme celui-ci, où le résultat moyen a la particularité de ne correspondre à aucune réalité. Ici, la moyenne de 2,2 M€ n’est jamais atteinte par une observation… Sans simulation, il aurait été très difficile de percevoir la distribution des résultats affichée par le graphe de prévision !

05 octobre 2015

Un modèle de pharmacie (a)

L’exemple traité dans les articles précédents, le lancement de dé, était un cas simpliste de simulation stochastique (faisant intervenir le hasard). Montrons à présent ce que l’on peut faire avec un modèle nettement plus réaliste.

Il s’agit cette fois-ci d’un modèle de marketing, une simplification d’un modèle que j’ai créé comme consultant pour une société pharmaceutique. On veut étudier les résultats sur 10 ans du lancement d’un nouveau médicament. Le produit peut être lancé (cellule B1) en année, 1, 2 ou 3, avec des probabilités différenciées. Quand il sort sur le marché, il peut être (cf. B2) le premier, le second ou le troisième sur le marché (cf. copie d’écran ci-dessous).


Pour les cellules B11:K11, nous avons défini une loi normale avec une moyenne de 2% et un écart-type de 0,5%.

Remarque – Nous avons aussi corrélé chacune de ces hypothèses avec ses deux voisines, partant du principe que les bonnes années, ainsi que les mauvaises, ont tendance à se suivre.


Nous avons aussi défini une corrélation entre les hypothèses B1 et B2. En effet, plus on sort le produit tôt, plus on a de chances d’être le premier sur le marché. La configuration de la copie d’écran (B1=3 et B2=1) a donc peu de chances de se réaliser, mais nous l’avons mise là pour illustrer l’impact de ces deux cellules sur les résultats de la ligne 10.

Le tableau des lignes 4 à 7 montre quelles seront làes parts de marché, à partir de la première année de production selon que le produit sort le premier, le second ou le troisième sur le marché.

On voit en ligne 10 quelle est la part de marché obtenue selon l’année et le rang de sortie. Tout cela aboutit en B16 au calcul de la valeur actuelle nette.

La formule de B10 est =si(B9<$B$1;0;index($B$5:$Z$7;$B$2;B9-$B$1+1)).

La formule de B16 est =van(G1;B15:K15)-B14.