Distribution PERT dans Excel : formule, moyenne et quand l’utiliser
Une distribution PERT fait d’une estimation à trois points (min, plus probable, max) une distribution bêta étirée de min à max, de moyenne (min + 4 × plus probable + max) / 6 : pour la structure d’un bâtiment estimée à 760, 820 et 1 010 (milliers de dollars US), cette moyenne vaut 841,7. Excel n’a pas de fonction PERT, mais =BETA.INV(RAND(), α, β, min, max) tire des valeurs dans une telle distribution.
Ses paramètres de forme sont α = 1 + 4 × (plus probable − min) / (max − min) et β = 1 + 4 × (max − plus probable) / (max − min). Une distribution triangulaire construite sur les mêmes trois nombres a pour moyenne 863,3 et s’étale davantage.
Ce qu’est une distribution PERT
Une estimation à trois points donne la valeur la plus basse que vous jugez possible, la plus probable et la plus haute. Une distribution PERT en tire un éventail complet de résultats : rien en dessous du min ni au-dessus du max, un pic à la valeur la plus probable et une décroissance douce vers les deux extrémités, de sorte que les valeurs proches des limites sont possibles mais rares.
Mathématiquement, c’est une distribution bêta : une famille de distributions sur un intervalle fixe, dont la forme dépend de deux nombres positifs, α (alpha) et β (bêta), ici étirée de l’intervalle 0 à 1 sur l’intervalle min à max. La PERT choisit α et β pour que le pic tombe sur la valeur la plus probable et que la moyenne soit (min + 4 × plus probable + max) / 6 : la valeur la plus probable compte quatre fois, chaque limite une fois. Le poids 4 vient de PERT (Program Evaluation and Review Technique), la méthode de planning de projet mise au point pour le programme de missiles Polaris de la marine américaine à la fin des années 1950, qui estimait la durée attendue de chaque activité par (optimiste + 4 × plus probable + pessimiste) / 6. Les chefs de projet appellent encore cette expression l’estimation PERT ou à trois points ; c’est la moyenne de cette distribution.
| Grandeur | Formule et exemple |
|---|---|
| Forme α | 1 + 4 × (ml − min) / (max − min) = 1 + 4 × (820 − 760) / 250 = 1,96 |
| Forme β | 1 + 4 × (max − ml) / (max − min) = 1 + 4 × (1 010 − 820) / 250 = 4,04 |
| Moyenne | (min + 4 × ml + max) / 6 = 5 050 / 6 = 841,7 |
| Écart type | √((moyenne − min) × (max − moyenne) / 7) = 44,3 |
| Pic (mode) | ml = 820 |
La moyenne, 841,7, est supérieure à la valeur la plus probable parce que la fourchette va plus loin au-dessus (820 à 1 010) qu’en dessous (760 à 820). Les estimations de coût et de durée sont généralement asymétriques de cette façon, ce qui explique qu’un total de coûts les plus probables soit optimiste, comme le montre l’exemple de provision pour aléas d’un projet. L’ancien raccourci PERT pour l’écart type, (max − min) / 6, n’est qu’une approximation : 41,7 ici, contre 44,3 avec la formule ci-dessus.
La formule PERT dans Excel
Excel n’a pas de fonction PERT, mais BETA.INV(probability, alpha, beta, A, B) renvoie des valeurs d’une distribution bêta étirée sur un intervalle quelconque de A à B. Avec le min, le plus probable et le max d’une estimation dans les cellules B2, C2 et D2 :
| Élément | Formule |
|---|---|
| Tirage aléatoire | =BETA.INV(RAND(), 1+4*(C2-B2)/(D2-B2), 1+4*(D2-C2)/(D2-B2), B2, D2) |
| Moyenne (en E2) | =(B2+4*C2+D2)/6 |
| Écart type | =SQRT((E2-B2)*(D2-E2)/7) |
| P90 | =BETA.INV(0.9, 1+4*(C2-B2)/(D2-B2), 1+4*(D2-C2)/(D2-B2), B2, D2) |
Avec RAND() comme probabilité, chaque recalcul tire une nouvelle valeur. Avec une probabilité fixe, BETA.INV renvoie directement ce centile, sans simulation : 0.9 donne 904,1 pour le poste Structure et 0.1 donne 786,9. BETAINV, le nom dans Excel 2007 et les versions antérieures, prend les mêmes arguments. Le min et le max doivent différer, puisque les deux formules de forme divisent par max − min.
La somme des P90 de plusieurs postes n’est pas le P90 de leur somme (voir P50, P80 et P90) : le centile d’un poste n’est pas celui d’un total. Pour un total, il faut une simulation, et dans Excel seul, cela revient à collecter des milliers de recalculs, par exemple avec une table de données, comme l’explique Simulation de Monte-Carlo dans Excel.
PERT ou triangulaire : mêmes trois nombres, résultats différents
Une distribution triangulaire prend les mêmes min, plus probable et max, mais sa densité monte en ligne droite du min à la valeur la plus probable puis redescend en ligne droite jusqu’au max. Sa moyenne est (min + plus probable + max) / 3 : la valeur la plus probable compte une fois au lieu de quatre, si bien que la limite éloignée tire davantage la moyenne. Pour le poste Structure, cela donne 2 590 / 3 = 863,3, soit environ 22 de plus que les 841,7 de la PERT. Son écart type, √((min² + ml² + max² − min × ml − min × max − ml × max) / 18), est de 53,3 contre 44,3 pour la PERT.
Pour voir ce que cela donne dans un modèle, nous avons lancé deux fois, avec la même graine, les 5 000 itérations de l’exemple du bâtiment : une fois telles que publiées, avec Structure en PERT, et une fois avec Structure remplacée par une distribution triangulaire aux mêmes trois nombres. Toutes les autres entrées tirent exactement les mêmes valeurs dans les deux exécutions, et chaque itération tire le même nombre aléatoire pour Structure : les exécutions ne diffèrent donc que par la forme de cette seule distribution.
| Poste Structure | PERT | Triangulaire |
|---|---|---|
| Moyenne par la formule | 841,7 | 863,3 |
| Moyenne des tirages | 841,7 | 863,3 |
| Écart type par la formule | 44,3 | 53,3 |
| Écart type des tirages | 44,3 | 53,3 |
| P10 des tirages | 786,9 | 798,7 |
| P90 des tirages | 904,1 | 941,1 |
| Tirages au-dessus du P90 de la PERT | 10,0 % | 23,6 % |
Les tirages concordent avec les formules, à la décimale affichée : leurs moyennes sont 841,7 et 863,3. L’échantillonnage par hypercube latin, valeur par défaut de xellstorm, y contribue : il donne à chaque entrée exactement un tirage dans chacune de 5 000 tranches équiprobables de sa fourchette. La triangulaire est plus large, avec un écart de P10 à P90 de 142,4 contre 117,2, et 23,6 % de ses tirages dépassent le P90 de la PERT, 904,1, alors que les tirages de la PERT ne le font que dans 10 % des cas. Elle n’est pourtant pas plus large des deux côtés : près du min, la PERT a plus de tirages, et le P10 de la triangulaire est plus élevé, 798,7 contre 786,9. Avec la valeur la plus probable près de l’extrémité basse, la triangulaire déplace du poids vers la longue queue supérieure ; pour une estimation symétrique, elle en donnerait davantage aux deux extrémités.
Un poste sur six suffit à déplacer le total. Avec Structure en distribution triangulaire, le coût total moyen passe de 2 785 à 2 806, soit le même écart que la moyenne du poste (tous les autres tirages sont identiques) ; le P90 augmente de 27, et la probabilité de dépasser le budget de 2 900 passe de 16,1 % à 20,8 %.
| Coût total | PERT | Triangulaire |
|---|---|---|
| Moyenne | 2 785 | 2 806 |
| P90 | 2 937 | 2 964 |
| Probabilité de dépasser le budget de 2 900 | 16,1 % | 20,8 % |
Aucune des deux formes n’a raison ou tort : ce sont deux lectures des mêmes trois nombres. Choisissez-en une en connaissance de cause, et dites sur laquelle repose un budget.
Quand utiliser la PERT, la triangulaire ou la log-normale
- PERT pour les estimations d’experts où la valeur la plus probable est celle en laquelle vous avez le plus confiance : coûts, durées et quantités dont le plancher et le plafond sont connus. Sa moyenne reste proche de la valeur la plus probable et les limites sont rarement atteintes, ce qui convient à un min et un max conçus comme des bornes que vous ne comptez pas atteindre.
- Triangulaire quand les valeurs proches des limites sont réalistes, ou quand vous voulez une dispersion plus prudente à partir des mêmes trois nombres : sur une estimation asymétrique, elle donne plus de poids au côté long, de sorte que sa moyenne et ses centiles supérieurs sont plus élevés. Elle est aussi facile à expliquer, puisque sa densité est faite de deux droites.
- Log-normale quand il n’y a pas de maximum ferme : coûts, pertes ou durées qui peuvent dépasser d’un multiple, et autres grandeurs qui ne peuvent pas descendre sous zéro mais ont une longue queue à droite. Elle est définie par une valeur typique et une dispersion plutôt que par des limites.
- Une distribution ajustée quand les trois nombres ne sont pas du tout des limites. Si le « bas » et le « haut » d’un expert sont des cas à un sur dix (P10 et P90), une PERT qui les prend pour min et max laisse de côté les 20 % de résultats au-delà ; ajustez plutôt une distribution aux centiles. Avec des données historiques, ajustez-la aux données.
Une variation symétrique autour d’une cible, comme une cote usinée, est généralement normale, comme dans l’exemple de cumul de tolérances.
Saisir une distribution PERT dans xellstorm
xellstorm calcule votre fichier dans le navigateur et garde les distributions à part : le fichier n’a pas besoin de formules BETA.INV :
- Ouvrez le fichier, puis, sur la carte du modèle, cliquez sur une cellule dont vous n’êtes pas sûr et choisissez Make input. Les nouvelles entrées démarrent comme une PERT allant de 90 % à 110 % de la valeur enregistrée de la cellule.
- À l’étape Distributions, gardez PERT dans la colonne Distribution et saisissez le min, le plus probable (likely) et le max. Les colonnes Shape, Mean et P10 – P90 se mettent à jour pendant la saisie : vous voyez ce que les trois nombres impliquent avant de lancer la simulation.
- Si les estimations sont déjà dans le fichier, dans des cellules étiquetées Min, Likely et Max (ou Low, Base et High) à côté de l’entrée, le panneau latéral propose « Link to these cells » : la PERT les lit alors avant chaque exécution, de sorte que les modifications de la feuille se reportent quand vous ouvrez le fichier modifié et appliquez votre projet enregistré. L’exemple de registre des risques fonctionne ainsi.
- Si votre bas et votre haut sont le P10 et le P90 plutôt que des limites, saisissez-les avec la valeur typique comme P50 dans « Fit from estimates » du panneau latéral et choisissez PERT, Normal, Lognormal ou Triangular comme forme ; xellstorm trouve la distribution la plus proche de cette forme et affiche son erreur d’ajustement. L’onglet « From data » ajuste plutôt une distribution à des valeurs historiques.
- Pour comparer les formes, ajoutez un scénario qui change la distribution de l’entrée en Triangular et saisissez les mêmes trois nombres. Les scénarios s’exécutent sur les mêmes tirages aléatoires, et l’étape Results compare chacun avec le cas de référence.
Les fichiers conçus pour @RISK, ModelRisk ou Analytic Solver contiennent souvent des fonctions PERT (RiskPert, VosePERT, PsiPert). « Import from workbook » transforme en entrée une fonction isolée, ou une fonction avec un seul multiplicateur positif suivi d’au plus un décalage ; VosePERT(E7,1,F7)*D7 en est un exemple. Les arguments et multiplicateurs qui utilisent des cellules restent liés, y compris leur arithmétique, et sont relus après application des cellules fixes de chaque scénario. La formule BETA.INV ci-dessus est aussi convertie en une entrée bêta équivalente : ses deux calculs de forme, son min et son max restent liés aux cellules. Ses divisions explicites exigent toujours un min et un max différents. Une entrée PERT dont le min, le plus probable et le max sont tous égaux tire cette seule valeur.
Questions
Quelle est la formule de la moyenne d’une distribution PERT ?
La moyenne d’une distribution PERT est (min + 4 × plus probable + max) / 6 : la valeur la plus probable pondérée quatre fois, chaque limite une fois. Pour une estimation de 760, 820 et 1 010, elle est de 5 050 / 6 = 841,7. En gestion de projet, la même expression s’appelle l’estimation PERT ou à trois points.
Quel est l’écart type d’une distribution PERT ?
L’écart type d’une distribution PERT est √((moyenne − min) × (max − moyenne) / 7). La règle rapide (max − min) / 6 n’en donne qu’une approximation : pour une estimation de 760, 820 et 1 010, la règle donne 41,7, alors que la valeur exacte est 44,3.
Une distribution PERT est-elle la même chose qu’une distribution bêta ?
Une distribution PERT est une distribution bêta particulière : étirée de min à max, avec des paramètres de forme fixés par la valeur la plus probable, α = 1 + 4 × (plus probable − min) / (max − min) et β = 1 + 4 × (max − plus probable) / (max − min). Une distribution bêta avec d’autres paramètres de forme n’est pas une PERT. Comme c’est une bêta, on peut y tirer avec la fonction BETA.INV d’Excel.
Faut-il utiliser PERT ou triangulaire ?
Les distributions PERT et triangulaire prennent les mêmes min, plus probable et max, mais la triangulaire s’étale davantage et, sur une estimation asymétrique, penche vers le côté long : pour une structure estimée à 760, 820 et 1 010 (milliers de dollars US), la moyenne de la triangulaire est de 863,3 contre 841,7 pour la PERT. Utilisez la PERT quand la valeur la plus probable est celle en laquelle vous avez le plus confiance et que les limites sont rarement atteintes ; utilisez la triangulaire pour une dispersion plus prudente, ou quand les valeurs proches des limites sont réalistes.
Comment obtenir le P90 d’une distribution PERT dans Excel ?
Le P90 d’une entrée PERT isolée s’obtient directement avec BETA.INV et 0.9 comme probabilité : =BETA.INV(0.9, 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max), ce qui donne 904,1 pour une estimation de 760, 820 et 1 010 ; les 5 000 tirages simulés de cette page le reproduisent avec 904,1, grâce à l’échantillonnage par hypercube latin (un tirage dans chaque tranche équiprobable de la fourchette). Pour le P90 d’un total de plusieurs postes incertains, lancez une simulation : les centiles ne s’additionnent pas.
À lire aussi
- Guide
Simulation de Monte-Carlo sur Excel, avec ou sans complément
Trois façons de lancer une simulation de Monte-Carlo sur un modèle Excel : RAND() et une table de données, un complément, ou un outil dans le navigateur sans complément. Formule PERT incluse. - 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. - 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).
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.