Simulation de Monte Carlo dans Excel : Un guide complet

Pourquoi utiliser Excel pour la simulation de Monte Carlo ?

Une fois que vous avez choisi de mettre en œuvre une simulation de Monte Carlo, vous disposez de multiples outils, tels qu'Excel, Python, R, SAS et MATLAB, pour vous aider dans les simulations.

Le facteur le plus important à prendre en compte, en particulier lorsque vous mettez en œuvre la simulation de Monte Carlo pour la première fois, est votre familiarité globale avec l'outil. Excel est l'un des outils les plus utilisés dans le monde des affaires, ce qui signifie que de nombreuses personnes sont déjà familiarisées avec ses opérations de base. Cela réduit le temps de formation et élimine la nécessité d'apprendre un nouveau logiciel à partir de zéro.

Excel fournit également des outils faciles à utiliser pour créer des diagrammes et des graphiques, qui peuvent être utiles pour visualiser les résultats des simulations. En outre, plusieurs compléments puissants sont disponibles pour Excel, améliorant sa capacité à effectuer des simulations Monte Carlo complexes.

Toutefois, il convient également de noter que pour des simulations plus avancées, en particulier celles qui nécessitent la manipulation de grands ensembles de données ou l'exécution d'un très grand nombre de simulations, des outils plus spécialisés, autres qu'Excel, peuvent être plus appropriés.

Aperçu des fonctions Excel pertinentes

Ensuite, nous explorerons deux fonctions essentielles d'Excel : RAND et NORM.INV, couvrant leur syntaxe, leurs paramètres et les cas d'utilisation typiques. Ces fonctions permettent de générer des nombres aléatoires et de définir des distributions de probabilités, qui sont des aspects fondamentaux de toute simulation.

La syntaxe et les paramètres de la fonction RAND

RAND génère un nombre aléatoire supérieur ou égal à 0 et inférieur à 1. Les nombres sont uniformément distribués, ce qui signifie que tout nombre compris dans l'intervalle spécifié a la même probabilité de se produire.

La syntaxe de RAND est la suivante :

RAND()

La fonction RAND ne nécessite aucun argument. Il est utilisé simplement comme RAND().

Dans le contexte de la simulation de Monte Carlo, RAND() peut être utilisé pour simuler l'apparition d'événements aléatoires ou pour faire varier les entrées dans votre modèle.

La syntaxe et les paramètres de la fonction NORM.INV

Alors que RAND génère des nombres aléatoires uniformes, NORM.INV est utilisé pour générer des nombres aléatoires à partir d'une distribution normale, ce qui est une exigence courante dans une simulation de Monte Carlo. Cette fonction renvoie l'inverse de la distribution cumulative normale pour une moyenne et un écart type spécifiés.

La syntaxe de la fonction NORM.INV est la suivante :

NORM.INV(probability, mean, standard_deviation)

Les paramètres sont les suivants :

• probabilité : Une probabilité correspondant à la distribution normale, qui doit être une valeur comprise entre 0 et 1. Elle est généralement générée par la fonction RAND().
• moyenne : La moyenne arithmétique de la distribution normale.
• standard_deviation: L'écart-type de la distribution normale, une mesure de la dispersion des nombres autour de la moyenne.

La fonction NORM.INV est utilisée pour transformer des nombres aléatoires uniformément distribués à partir de la fonction RAND en nombres qui suivent une distribution normale spécifiée. Cela s'avère utile pour modéliser des variables qui devraient présenter une variabilité naturelle suivant une courbe normale.

Maintenant que nous connaissons tous les éléments constitutifs, les fonctions et les concepts d'une simulation de Monte Carlo, nous allons en mettre une en œuvre dans Microsoft Excel.

Mise en œuvre de la simulation Monte Carlo dans Microsoft Excel : Un exemple

Imaginez un scénario dans lequel vous êtes un analyste de données travaillant dans une entreprise dynamique d'électronique grand public et où vous êtes chargé d'évaluer la viabilité financière du lancement d'un nouveau dispositif de suivi de la condition physique.

Le marché de ces appareils est concurrentiel et la demande des consommateurs peut être très variable, influencée par les tendances saisonnières, l'efficacité du marketing et les actions des concurrents. En outre, les coûts associés à la fabrication de ces dispositifs sont sujets à des fluctuations en raison des changements dans les coûts des matériaux et des incertitudes de la chaîne d'approvisionnement.

Vous avez décidé d'utiliser la simulation de Monte Carlo dans Excel pour relever ces défis. Vous pensez que cette approche vous aidera à estimer la rentabilité potentielle selon différents scénarios, ce qui permettra à l'entreprise de prendre des décisions éclairées sur les stratégies de prix, les volumes de production et les investissements en marketing.

Vous avez également analysé des données antérieures provenant de lancements de produits similaires et d'études de marché dans le secteur de l'électronique grand public. Cette analyse vous a permis de déterminer certains paramètres qui serviront de base à votre simulation :

• Une demande moyenne de 10 000 unités pour les nouveaux appareils au cours de la première année de lancement, avec un écart type de 2 000 unités, reflétant l'incertitude quant à l'adoption par les consommateurs.
• Le prix de vente unitaire se situe généralement entre 50 et 70 dollars, en fonction des prix pratiqués par la concurrence et de la saturation du marché.
• Le coût unitaire, influencé par la volatilité des prix des matériaux et l'efficacité de la fabrication, s'élève en moyenne à 30 dollars par unité, avec un écart-type de 5 dollars.

Ces données historiques constituent les hypothèses sous-jacentes de vos paramètres de simulation, ce qui permet de créer une simulation reflétant plus précisément les conditions actuelles du marché.

Les étapes à suivre pour mettre en œuvre la simulation de Monte Carlo pour cet exemple particulier sont les suivantes :

Étape 1 : Configurez votre feuille Excel

Tout d'abord, préparez votre feuille de calcul Excel de manière à inclure des colonnes pour chaque variable et une colonne pour le bénéfice calculé.

Voici à quoi cela ressemblerait initialement :

Mise en place de la feuille Excel.

Étape 2 : Formules d'entrée pour les variables

Dans chaque ligne, vous saisirez des formules pour générer des valeurs aléatoires pour la demande, le prix de vente et le coût sur la base des distributions que vous avez identifiées :

• Demande : Distribution normale (moyenne = 10 000 unités, écart-type = 2 000 unités)
• Prix de vente : Distribution d'uniformes (50 à 70 $)
• Coût : Distribution normale (moyenne = 30 $, écart-type = 5 $)

Pour saisir ces formules une à une, sélectionnez la cellule A2 et tapez ce qui suit :

=NORM.INV(RAND(), 10000, 2000)

L'équation ci-dessus crée une distribution normale avec une moyenne et un écart-type donnés comme ci-dessous :

Créer la distribution de la demande.

Ensuite, sélectionnez la cellule B2 et tapez ce qui suit :

=50 + (70-50) * RAND()

L'équation ci-dessus crée une distribution uniforme entre 50 et 70 dollars pour le prix de vente, comme indiqué ci-dessous :

Création de la distribution pour le prix de vente.

Sélectionnez la cellule C2 et tapez ce qui suit :

=NORM.INV(RAND(), 30, 5)

L'équation ci-dessus, similaire à l'équation de la demande, crée une distribution normale avec une moyenne et un écart-type donnés comme ci-dessous :

Création de la distribution pour le coût.

Étape 3 : Calculer la variable dépendante

Calculez maintenant le bénéfice, qui est la variable dépendante, pour chaque simulation à l'aide de la formule de la colonne D :

=(B2 - C2) * A2

Calcul du bénéfice.

Étape 4 : Remplir pour simuler plusieurs scénarios

Jusqu'à présent, nous avons créé une simulation unique. Étendons-le à de multiples simulations, disons un millier.

Sélectionnez les cellules A2 à D2 et faites glisser la poignée de remplissage (un petit carré en bas à droite de la sélection) vers le bas pour remplir les formules sur autant de lignes que vous souhaitez simuler (par exemple, 1000 lignes pour 1000 simulations).

Il se présentera comme suit :

Créer les simulations.

Étape 5 : Analyser les résultats

Après avoir effectué les simulations, vous pouvez analyser les résultats à l'aide de fonctions statistiques telles que min, max, moyenne et écart-type. N'hésitez pas à vous référer rapidement à l 'antisèche Excel pour vous rafraîchir la mémoire sur les fonctions Excel intégrées que nous utiliserons par la suite.

Pour trouver le bénéfice moyen attendu chaque mois, tapez ce qui suit dans une cellule, disons G6 :

=AVERAGE(D2:D1001)

Pour trouver le bénéfice minimum attendu chaque mois, tapez ce qui suit dans une cellule, disons G7 :

=MIN(D2:D1001)

Pour trouver le bénéfice maximum attendu chaque mois, tapez ce qui suit dans une cellule, disons G8 :

=MAX(D2:D1001)

Pour trouver l'écart-type du bénéfice, tapez ce qui suit dans une cellule, disons G9 :

=STDEV.P(D2:D1001)

Une fois exécutée, la feuille Excel devrait ressembler à ceci :

Analyse des résultats de la simulation.

Nous pouvons interpréter les résultats estimés et les implications pour le lancement du produit comme suit :

• Le chiffre du bénéfice moyen représente le bénéfice attendu du lancement du nouveau tracker de fitness. Il suggère qu'en moyenne, chaque simulation prédit que nous pouvons nous attendre à réaliser un bénéfice d'environ 298 278,67 $. Cette valeur est utile en tant qu'estimation centrale de la rentabilité dans les hypothèses données.
• Le profit minimum de 67 598,78 $ est le profit le plus faible observé dans toutes nos simulations. Il indique le scénario le plus défavorable selon les hypothèses de votre modèle, qui reste rentable mais nettement moins que la moyenne. Cela peut être dû à une demande particulièrement faible ou à des conditions de coût défavorables dans cette simulation spécifique.
• Un bénéfice maximal de 641 955,42 $ représente le scénario le plus favorable, dans lequel la demande et le prix étaient probablement les plus élevés et les coûts les plus bas dans toutes les simulations. Cela montre le potentiel de hausse si les conditions s'avèrent très favorables.

Compte tenu de l'écart important entre les bénéfices minimum et maximum et de l'écart-type substantiel, le lancement du nouveau produit comporte un risque financier considérable.

Les décideurs doivent se demander si l'entreprise est à l'aise avec ce niveau d'incertitude et le risque de bénéfices inférieurs à la moyenne.

En outre, bien que cela soit facultatif, nous vous encourageons à créer des visualisations telles que des histogrammes afin d'avoir une compréhension visuelle des résultats des simulations.

Meilleures pratiques et techniques avancées pour améliorer les simulations

Lorsque vous refaites la même simulation que ci-dessus, vous pouvez observer une légère différence dans les calculs, comme indiqué ci-dessous :

Des résultats de simulation variables.

En effet, les valeurs de la simulation originale peuvent changer entre les itérations, ce qui influence les estimations qui en résultent. Bien que la variation soit faible, lorsque la valeur estimée change, les décideurs s'interrogent sur la précision et la fiabilité de la simulation.

Explorons quelques techniques avancées que nous pourrions utiliser pour améliorer la précision et la fiabilité des simulations.

Augmenter le nombre de simulations

L'exécution d'un plus grand nombre de simulations permet d'atténuer les fluctuations aléatoires et d'obtenir une estimation plus stable et plus précise des résultats.

Pour l'exemple ci-dessus, nous pouvons augmenter le nombre de simulations (par exemple, de 1 000 à 10 000 ou plus), en particulier lorsqu'il s'agit de paramètres très variables.

La détermination du "bon" nombre de simulations dépend de plusieurs facteurs.

Plus le modèle est complexe (c'est-à-dire plus il y a de variables et plus l'éventail de leurs interactions est large), plus il faut de simulations pour saisir tous les résultats possibles et s'assurer que les résultats ne sont pas dus au hasard.

Si les données d'entrée présentent une grande variabilité ou sont fortement asymétriques, davantage de simulations seront nécessaires pour estimer avec précision les queues (valeurs extrêmes) des distributions des résultats.

Pour des analyses plus détaillées, notamment dans le domaine de la finance ou de la gestion des risques, il n'est pas rare d'effectuer de 10 000 à 100 000 simulations. Cette fourchette est généralement utilisée pour garantir des résultats solides pour différents scénarios et données d'entrée. Bien sûr, comme nous l'avons mentionné précédemment, pour des analyses à si grande échelle, Excel n'est pas toujours le meilleur choix d'outil, plutôt R ou Python.

Affiner les distributions d'entrée

La précision des simulations dépend en grande partie de la manière dont les distributions de probabilités d'entrée reflètent l'incertitude et le comportement réels des variables sous-jacentes. Dans notre exemple ci-dessus, nous avons supposé une distribution normale pour la demande et le coût et une distribution uniforme pour le prix de vente.

En outre, nous pourrions analyser des données historiques plus complètes afin de mieux paramétrer les distributions. Nous pouvons mieux comprendre les comportements des coûts, des ventes et de la demande en fonction de facteurs externes, sur la base des informations fournies par les experts du domaine. Nous pouvons également envisager d'utiliser des distributions telles que log-normale, bêta ou gamma ou de créer des distributions personnalisées basées sur des données empiriques.

Réalisation d'une analyse de sensibilité

Cette analyse est effectuée pour comprendre quelles variables d'entrée ont l'impact le plus significatif sur la production en faisant varier systématiquement chaque entrée tout en maintenant les autres constantes.

Dans notre exemple ci-dessus, nous pouvons maintenir deux variables constantes et modifier la distribution de l'une d'entre elles pour comprendre les changements dans les estimations. Ensuite, répétez le même processus pour les deux variables restantes, une par une. Enfin, cette technique permet de comprendre sur quelle variable il faut concentrer les efforts pour améliorer la précision.

L'utilisation itérative des techniques ci-dessus et l'analyse des résultats peuvent conduire à des résultats plus précis et plus fiables.

Conclusion

Ce tutoriel vous a présenté la simulation de Monte Carlo et les concepts statistiques pertinents. Après avoir présenté les fonctions Excel pertinentes, le tutoriel a fourni un guide étape par étape pour mettre en œuvre la simulation Monte Carlo dans Excel en utilisant un exemple du monde réel.

Enfin, vous avez pris connaissance de certaines bonnes pratiques et techniques avancées pour garantir la précision et la fiabilité de vos résultats.

Si vous êtes particulièrement intéressé par la mise en œuvre de la simulation Monte Carlo ci-dessus à l'aide d'autres outils tels que Python ou R, ces deux ressources vous seront utiles :

• Simulations de Monte Carlo en R
• Simulations de Monte Carlo en Python

Par ailleurs, si vous souhaitez vous en tenir à l'outil familier qu'est Microsoft Excel et maîtriser vos compétences à l'aide de cet outil largement adopté, vous devriez consulter le cursus "Excel Fundamentals".