Simulation de Monte-Carlo sur Excel, avec ou sans complément
Une simulation de Monte-Carlo sur un modèle Excel se fait de trois façons : avec des formules RAND() et une table de données, avec un complément, ou avec un outil qui exécute le modèle en dehors d’Excel. Dans les trois cas, le modèle est recalculé des milliers de fois, les entrées incertaines étant tirées dans des fourchettes, et les résultats se lisent comme des probabilités : la probabilité de dépasser le budget, le coût que l’on ne dépasse pas avec un niveau de confiance de 80 %, les entrées qui comptent le plus.
Pourquoi simuler plutôt que se contenter d’un seul nombre
Une feuille de calcul donne un seul résultat pour un seul jeu d’entrées. Quand les entrées sont des estimations, ce résultat cache l’écart possible, et il est souvent optimiste : additionner les coûts les plus probables oublie que les coûts dérapent plus facilement qu’ils ne se réduisent. Dans notre exemple de coût de projet, l’estimation de base, somme des coûts les plus probables, est dépassée dans 94,2 % des résultats simulés. Les cas optimiste, de référence et pessimiste n’y remédient pas : ils donnent trois résultats, sans dire avec quelle probabilité chacun se produit.
1. Excel seul, sans complément : RAND() et une table de données
Excel n’a pas de commande de simulation, mais ses fonctions suffisent pour un petit modèle :
- Remplacez chaque entrée incertaine par un tirage aléatoire.
RAND()renvoie un nombre uniforme entre 0 et 1 ; une fonction de distribution inverse le transforme en une valeur de la fourchette voulue (formules ci-dessous). - Répétez le calcul. Placez les numéros d’itération de 1 à 5 000 dans une colonne et, dans la colonne suivante, une ligne au-dessus du premier numéro, une référence à la cellule de sortie. Sélectionnez les deux colonnes à partir de cette ligne, choisissez Données › Analyse de scénarios › Table de données, et indiquez une cellule vide quelconque de la même feuille comme cellule d’entrée en colonne. Chaque ligne recalcule le modèle et reçoit donc de nouveaux tirages aléatoires.
- Synthétisez la colonne.
AVERAGE,PERCENTILE.INCetCOUNTIF(divisé par le nombre d’itérations) donnent la moyenne, le P80 et la probabilité de dépasser un objectif ; un histogramme montre la forme de la distribution.
| Entrée | Formule |
|---|---|
| Normale (moyenne, écart type) | =NORM.INV(RAND(), mean, sd) |
| PERT (min, plus probable, max) | =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max) |
| Triangulaire (min, plus probable, max), avec le tirage en U | =IF(U<(ml-min)/(max-min), min+SQRT(U*(max-min)*(ml-min)), max-SQRT((1-U)*(max-min)*(max-ml))) |
| Événement de risque (probabilité p, coût c) | =IF(RAND()<p, c, 0) |
Cela fonctionne, et c’est une bonne façon d’apprendre. Les limites se voient sur les modèles réels :
- Une table de données recalcule tout le modèle à chaque itération : les gros modèles deviennent lents. Le mode de calcul « Automatique sauf tables de données » garde le fichier utilisable entre-temps.
RAND()tire de nouvelles valeurs à chaque recalcul : les résultats changent donc à chaque recalcul du fichier, sauf si vous les collez en valeurs.- Pour les entrées corrélées, l’analyse de sensibilité et les rapports, il faut tout construire avec vos propres formules et graphiques.
2. Un complément Excel
Des compléments comme @RISK, Crystal Ball, Analytic Solver et ModelRisk ajoutent des fonctions de distribution à Excel (par exemple une fonction PERT que vous saisissez dans une cellule), exécutent les itérations à votre place, et produisent des graphiques, une analyse de sensibilité et des rapports. Ils s’exécutent dans Excel : chaque utilisateur doit donc avoir Excel et le complément installés, ce qui, dans beaucoup d’entreprises, passe par une demande au service informatique.
3. Un outil qui exécute le fichier hors d’Excel
Troisième option : un outil doté de son propre moteur de calcul, qui lit le fichier .xlsx et exécute lui-même les itérations. xellstorm fonctionne ainsi, dans le navigateur :
- Ouvrez le fichier Excel que vous avez déjà : votre navigateur le calcule dans son onglet, sans jamais l’envoyer. Un contrôle de compatibilité compare ses formules aux valeurs enregistrées par Excel et signale toute cellule que le moteur ne sait pas calculer.
- Sur la carte du modèle, cliquez sur les entrées incertaines pour leur donner des fourchettes, puis sur les résultats à suivre. Si le fichier utilise déjà
NORM.INV(RAND(), …)ou les fonctions de distribution courantes de @RISK, ModelRisk ou Analytic Solver, les formules prises en charge deviennent des entrées dont les paramètres restent liés à vos cellules. La formuleBETA.INVde la PERT ci-dessus est elle aussi convertie : ses arguments de forme arithmétiques restent liés et sont évalués avant chaque exécution (voir la distribution PERT dans Excel). - Chaque exécution part d’une graine : le même modèle redonne donc les mêmes nombres, et dans la plupart des fichiers, seules les cellules touchées par une itération sont recalculées.
Les trois méthodes côte à côte
| Excel seul | Complément Excel | xellstorm | |
|---|---|---|---|
| Installation | Rien | Un complément dans Excel | Rien : s’exécute dans le navigateur |
| Distributions | À construire avec des formules | Nombreuses, sous forme de fonctions | 48, dont vos propres tables |
| Répétition des itérations | Table de données, qui recalcule tout le fichier | Intégrée | Intégrée ; dans la plupart des fichiers, seul ce qui change est recalculé |
| Mêmes nombres d’une fois à l’autre | Non, sauf collage en valeurs | Avec une graine fixe | Avec une graine, quel que soit le nombre de cœurs du processeur |
| Corrélation, sensibilité, rapports | Vos propres formules et graphiques | Intégrée | Intégrée |
Comment lire les résultats
- Moyenne
- La moyenne de tous les résultats simulés. Avec des entrées asymétriques, elle diffère du résultat obtenu avec les entrées les plus probables.
- Centiles (P10, P50, P80, P90)
- La valeur que 10 %, 50 %, 80 % ou 90 % des résultats ne dépassent pas. Le P50 est la médiane ; P80 et P90 sont des niveaux de budget courants (en savoir plus sur les niveaux P).
- Probabilité d’un objectif
- La part des résultats qui remplissent une condition, par exemple un coût total supérieur au budget.
- Courbe en S
- La probabilité cumulée tracée en fonction du résultat : on y lit n’importe quel centile, ou la probabilité de rester sous une valeur quelconque.
- Diagramme en tornade
- De combien le résultat bouge quand chaque entrée passe d’un centile bas à un centile haut, les autres restant à leur médiane ; les barres les plus larges désignent les entrées qui comptent le plus (diagrammes en tornade).
- CVaR (moyenne des 5 % les plus défavorables)
- La moyenne des 5 % de résultats les plus défavorables : la gravité des cas défavorables, et pas seulement leur fréquence (dimensionner une réserve d’après les pires résultats).
Combien d’itérations ?
Assez pour que les chiffres que vous présentez cessent de bouger. Le bruit d’une moyenne simulée diminue comme la racine carrée du nombre d’itérations : multiplier ce nombre par quatre le divise par deux. Quelques milliers d’itérations font un point de départ courant ; les centiles de queue comme le P95 en demandent plus que la moyenne. xellstorm peut tourner jusqu’à ce que la moyenne et les centiles se stabilisent dans la tolérance que vous fixez.
Questions
Simulation de Monte-Carlo et analyse de scénarios, est-ce la même chose ?
Non. L’analyse de scénarios calcule quelques cas choisis, comme le meilleur cas, le cas de référence et le pire, sans dire avec quelle probabilité chacun se produit. Une simulation tire des milliers de combinaisons dans les fourchettes et donne la probabilité de chaque résultat.
Quelle distribution utiliser pour une estimation ?
Pour des estimations d’expert données en min, plus probable et max : PERT ou triangulaire. Pour des grandeurs qui ne peuvent pas descendre sous zéro et ont une longue queue à droite : log-normale. Pour un événement qui survient ou non : une probabilité, avec un coût s’il survient. Avec des données, ajustez une distribution à ces données.
Dois-je modifier mon fichier ?
Pas avec xellstorm : les distributions sont gardées à côté du fichier, pas dedans, et peuvent lire leurs nombres dans des cellules que vous avez déjà, par exemple des colonnes Min / Probable / Max ; vos formules restent telles que vous les avez écrites. Avec Excel seul ou un complément, les fourchettes sont gardées dans le fichier lui-même, sous forme de formules ou de définitions propres au complément.
Comment créer une distribution PERT dans Excel ?
Excel n’a pas de fonction PERT, mais une distribution PERT est une distribution bêta mise à l’échelle entre le min et le max, et BETA.INV permet donc d’y tirer des valeurs : =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max), en remplaçant min, plus probable (ml) et max par des références de cellules. Sa moyenne est (min + 4 × plus probable + max) / 6. La distribution PERT dans Excel la compare à la distribution triangulaire.
Peut-on faire une simulation de Monte-Carlo dans Excel sans complément ?
Oui. Tirez chaque entrée incertaine avec une formule comme =NORM.INV(RAND(), mean, sd), répétez le modèle avec une table de données, et résumez les résultats avec AVERAGE, PERCENTILE.INC et COUNTIF. Ou ouvrez le fichier dans xellstorm, qui exécute les itérations dans le navigateur, sans complément et sans Excel.
À lire aussi
- Guide
Distribution PERT dans Excel : formule, moyenne et quand l’utiliser
Une PERT est une distribution bêta de min à max, de moyenne (min + 4 × probable + max)/6. La formule Excel BETA.INV, et PERT contre triangulaire. - Guide
P50, P80 et P90 : ce qu’ils signifient et à quel niveau budgétiser
Le P80 est le coût que 80 % des résultats simulés ne dépassent pas. Six postes de coût d’un bâtiment : leurs P80 totalisent 2 843, mais le P80 de leur somme est de 2 756 (milliers de dollars US). - Guide
Diagrammes en tornade et analyse de sensibilité : explications
Un diagramme en tornade fait varier chaque entrée seule, du P10 au P90 par défaut dans xellstorm. Dans une estimation de bâtiment, un seul risque fait varier le coût de 150 (milliers de dollars US). - Coût et planning du projet
Quelle provision pour aléas faut-il pour un projet de construction ?
Exemple traité : dans 94,2 % des résultats simulés, une estimation de bâtiment coûte plus que la somme de ses coûts les plus probables. Dimensionnez la provision pour aléas d’un projet de construction par simulation de Monte-Carlo.
xellstorm est un outil de simulation de Monte-Carlo qui fonctionne dans le navigateur, pour les modèles Excel : aucun complément, et votre fichier ne quitte jamais votre ordinateur.