Monsieur Excel
Pour tout savoir faire sur Excel !

26 novembre 2015

Macro pour table d’hypothèse

Remarque 1 – Même si je prends dans cet article l’exemple du modèle de location analysé dans les articles précédents, ce que je vais dire concerne l’utilisation des tables de façon totalement générale. Vous avez donc intérêt à lire l'article de ce jour même si l’exemple de locations d’appartements ne vous intéresse pas…

Dans l’article du 4 novembre, nous avons vu comment affiner notre analyse du problème de location d’appartements en réalisant deux tables d’hypothèse, une table « à une dimension » dans laquelle nous analysions les valeurs de deux variables, la marge brute et la rémunération et une table « à deux dimensions » pour la seule rémunération.

Comme je l’indiquais dans cet article, la coexistence des deux tables multiplie par 877 le temps de calcul du modèle, ce qui peut rapidement devenir insupportable pour un modèle plus lourd.

Quand je réalise des tables pour les modèles de mes clients en conseil, je m’efforce d’éviter de tomber dans cette situation. En fait, quand je crée une table, je lui associe en général un bouton macro permettant de lancer la création et le calcul de la table. Cela ne pose en général pas de problème car, le plus souvent, on n’a pas besoin qu’une table reste « dynamique », c’est-à-dire se calcule en permanence : on a simplement envie de la consulter à la demande.

J’enregistre donc une macro dans laquelle je réalise les opérations suivantes :
  • création de la première table ;
  • sélection de l’intérieur de la table puis copie ;
  • collage spécial des valeurs ;
  • [Esc], pour ôter le liseré du copier/coller ;
  • clic dans le coin supérieur gauche.
J’obtiens alors la macro ci-dessous, que j’ai appelée « Table_enreg ».

Comme vous le constaterez aussitôt si vous connaissez un tant soit peu le VBA, cette macro fonctionne très bien mais n’est pas particulièrement efficace.

Après quelques retouches, j’obtiens alors la macro « Table_1 », qui est bien plus courte et performante.


Remarque 2 – Un pro du VBA remplacerait immédiatement les coordonnées utilisées dans la macro par des noms, afin que la macro continue à fonctionner si l’utilisateur insère ou détruit des lignes ou des colonnes.

Remarque 3 – Il ne reste plus qu’à créer un bouton macro en utilisant la commande « Insérer » du bloc « Contrôle » de l’onglet « Développeur », en prenant le bouton macro du bloc « Formulaires » et en l’associant à la macro « Table_1 »…

Remarque 4 – Notez comment nous avons évité de copier/coller les valeurs, ce qui nous a permis en prime d'éliminer la commande (CutCopyMode) ôtant le liseré du copier/coller ainsi que la sélection finale de H2.


20 novembre 2015

Location d’appartements (e)

Notre modèle est maintenant prêt à tourner. Dans la barre d’outils de Crystal Ball, nous voyons qu’il est prévu par défaut de faire 5.000 itérations. Pourquoi un tel nombre ? Parce que cela donne, dans les situations les plus courantes, une précision d’estimation suffisante.

Supposons par exemple qu’avec votre simulation, vous cherciez à estimer un pourcentage p proche de 50%. Dans ce cas, si vous avez quelques connaissances en statistiques, vous savez que l’écart-type s est de 1/2*racine(n). Un intervalle d’estimation avec un degré de confiance de 95% est donc de (p-2s;p+2s) soit, pour notre estimation d’un pourcentage de 50%, un intervalle du type (48,6% ; 51,4%).

Je lance donc la simulation ; les 5.000 itérations se font en 3,2 secondes, infiniment plus rapides que tout ce que nous aurions pu faire nous-mêmes avec du VBA. Et voici quelques-uns des résultats engendrés de façon automatique par Crystal Ball : le graphe de prévision qui affiche la distribution des valeurs obtenues pour la rémunération brute, les statistiques associées, et une analyse de sensibilité de cette marge brute.



Remarque – Pour le graphe de prévision, nous avons utilisé une option permettant d’identifier la loi statistique la mieux ajustée avec  les résultats obtenus ! Ici, Crystal Ball a trouvé une loi bêta dont on peut afficher les paramètres si nécessaire, ainsi que ceux obtenus avec chacune des lois possibles.

14 novembre 2015

Location d’appartements (d)

Remarque 1 – Je vous prie, chères lectrices et chers lecteurs, de m'excuser pour le délai entre l'article précédent et celui-ci. J'étais en effet très chargé ces derniers jours... J'essaierai dans les articles suivants d'aller un peu plus vite pour compenser :)

Dans l’article du 23 octobre, nous avions dit qu’il y avait quatre façons d’analyser des issues aléatoires : le plus probable (a), quelques scénarios (b), une série de scénarios (c) et enfin la simulation probabiliste (d). Nous voyons donc aujourd’hui comment analyser notre problème en simulation probabiliste.

Nous voyons dans la copie d’écran ci-dessous comment se présente la barre d’outils de Crystal Ball, ainsi que le dialogue que nous avons utilisé pour définir la loi de l’hypothèse en C3.

Dans le langage de Crystal Ball, une hypothèse est une cellule contenant une constante que l’on va faire varier aléatoirement. De même, une prévision est une cellule contenant un résultat dont on souhaite analyser les valeurs après la simulation.

Dans notre cas, les cellules C3, C4, C7 et C9 seront nos hypothèses et la cellule C16, la rémunération mensuelle, est la prévision unique de ce modèle.


Nous avons défini la cellule C3 comme première hypothèse, en choisissant une loi triangulaire, ce qui est bien adapté vu que la valeur probable n’est pas la moyenne entre la valeur pessimiste et la valeur optimiste : comme ce n’est pas symétrique, on ne peut pas prendre une loi telle que par exemple la loi normale.

Si vous regardez bien la copie d’écran, vous verrez que les trois paramètres de cette loi sont en fond jaune clair, ce qui est la façon de Crystal Ball de vous dire que l’on n’a pas rentré ces trois paramètres à la main mais qu’ils font référence à la valeur initiale de trois cellules, ici respectivement F3, C3 et E3.

Pour la cellule C4, nous avons défini une loi normale avec C4 comme moyenne et 5 comme écart-type. En effet, en prenant m+3s, on obtient bien 100 comme maximum et 70 comme minimum.

Remarque – Certes, en prenant une loi normale, nous obtiendrons des valeurs continues ce qui, au fond, ne devrait pas être trop grave pour notre modèle. Si cela vous dérange, vous pouvez toujours tirer cette variable continue, dans une cellule voisine comme D4 et mettre alors en C4 la formule =arrondi(D4;0) ou la formule =ent(D4+0,5;0).

Enfin, pour C7 et C9, nous avons pris des lois uniformes entre les valeurs de la colonne F et celles de la colonne E.

Il ne reste plus qu’à définir la cellule C16 comme prévision et à lancer la simulation...

04 novembre 2015

Location d’appartements (c)

Dans l’article précédent, nous avons découvert le gestionnaire de scénarios. Si vous vous rappelez ce que nous avons décrit dans l’article du 23 octobre, nous avions dit qu’il y avait quatre façons d’analyser des issues aléatoires : le plus probable (a), quelques scénarios (b), une série de scénarios (c) et enfin la simulation probabiliste (d). Nous voyons donc aujourd’hui comment analyser notre problème avec l’aide d’une série de scérnarios.

Table à une dimension

Nous avons donc créé une table à une dimension, en H3:J14. Nous avons entré la formule =C12 en I3 et la formule =C16 en J3. Nous avons alors créé la table en prenant la cellule C3 comme critère d’entrée en colonne. Nous avons maintenant dans cette table non plus 3 mais 11 scénarios.

Remarque – Si les valeurs sont toutes les mêmes, c’est que vous êtes en mode de calcul « Manuel » ou « Automatique sauf les tables ». Faites alors [F9] pour lancer le calcul de la table.

Table à deux dimensions

Nous avons ensuite créé une table à deux dimensions, en L3:S14. Nous avons entré la formule =C16 en L3. Nous avons alors créé la table en prenant la cellule C4 comme critère d’entrée en ligne et la cellule C3 comme critère d’entrée en colonne. Nous avons maintenant dans cette table non plus 3 mais 77 scénarios.


En guise de conclusion

Avec une table à une dimension, on peut décliner le résultat pour autant de formules que l'on veut. Ici, nous nous sommes contentés de la marge brute et de la rémunération.

Avec une table à deux dimensions, il y a une seule formule, dans le coin supérieur gauche, mais on peut alors décliner le résultat pour les valeurs de deux paramètres.

Faites attention cependant ! En effet, si vous gardez les deux tables telles quelles, vous calculez 877 fois votre modèle : 11 * 77 ! Ainsi, si votre modèle se calculait sans table en une seconde, le calcul avec les deux tables actives prendrait un quart d'heure...

Avant de créer la seconde table, il est donc prudent – pour éviter ce problème – de copier le bloc I4:J14 et de coller les valeurs par-dessus pour annihiler la première table…