Créer un modèle de simulation dans Excel

Ce tutoriel explique comment calculer et interpréter un modèle de simulation avec Excel en utilisant XLSTAT

Les modèles de simulation

Les modèles de simulation permettent d'obtenir des informations, telles que la moyenne ou la médiane, pour des variables qui n'ont pas une valeur exacte, mais pour lesquelles nous pouvons connaître, supposer ou calculer une distribution. Si des variables « résultat » dépendent de ces variables « distribution » au travers d’une formule établie, elles auront par conséquence aussi une distribution et non une valeur fixe. Les simulations de Monte-Carlo dans XLSTAT vous permettent de définir les distributions, puis d’obtenir, par le biais de simulations itératives et après convergence du modèle, une distribution empirique pour les variables d’entrée et de sortie ainsi que les statistiques correspondantes.
Les modèles de simulation sont utilisés dans de nombreux domaines tels que la finance et l’assurance, la médecine, la prospection pétrolière et minière, ou la prévision des ventes.

Jeu de données pour la création d'un modèle de simulation et le lancement des calculs

Dans ce tutoriel, un modèle de simulation simple est construit avec deux distributions (deux variables aléatoires) et une variable résultat, en vue d'expliquer les bases des modèles de simulation.
D'autres didacticiels utilisant les quatre objets mentionnés plus hauts sont accessibles ici.

Notre modèle de simulation est basé sur les ventes et les coûts d'un magasin. Dans ce cas simple, le bénéfice est tout simplement la différence entre les ventes et les coûts. Sur la base de données historiques pour les coûts et les ventes qui ont été analysées avec l'outil "Ajustement d'une distribution" nous avons constaté que les coûts suivent une distribution normale (mu = 120, sigma = 10) et que les ventes suivent une distribution normale (mu=80, sigma=20) (voir notre tutoriel sur ce sujet pour plus d'informations).

Sur la base de ce modèle, différentes variables sont créées :

Le modèle peut être trouvé sur la feuille "Modèle".

Créer un modèle de simulation

Comment définir les variables aléatoires dans XLSTAT ?

• Ouvrir XLSTAT

• Sélectionner la cellule B2 qui correspond au montant des ventes.

• Lancer la commande XLSTAT > Simulations Monte Carlo > Définir une distribution. La boîte de dialogue Définir une distribution apparaît.

• Sélectionner le Nom de la variable disponible dans la cellule qui contient "Ventes". Choisir une distribution normale avec mu = 120 et sigma = 10. Une formule avec un appel à une fonction XLSTAT_Sim est générée dans la cellule B2.

• Renouveler la procédure dans la cellule en dessous pour générer une distribution normale avec mu = 80 et sigma = 20.

Comment définir la variable résultat dans XLSTAT ?

• Sélectionner la cellule qui contient 40 comme résultat de la formule =B2-B3. Attention : afin qu’il y ait une évolution de la variable résultat, celle-ci doit dépendre des variables aléatoires.

• Lancer XLSTAT > Simulations Monte Carlo > Définir une distribution. La boîte de dialogue Définir une variable résultat apparaît.

• Sélectionner le Nom de la variable disponible dans la cellule A4. Une formule avec un appel à XLSTAT_SimRes est générée dans la cellule B4.

Lancer les simulations

• Cliquer sur la commande XLSTAT > Simulations Monte-Carlo > Lancer les calculs. La boîte de dialogue apparaît. Définir le nombre de simulations à réaliser à 1000.

• Configurer l’onglet Graphiques > Sensibilité comme sur la capture d’écran ci-dessous.

• Lancer l’analyse en cliquant sur OK.

Interpréter les résultats des simulations

Le premier résultat affiché est une description du modèle contenant les valeurs par défaut des cellules et les distributions des différentes variables. Il rappelle également la formule qui explique comment calculer la variable résultat.
Plusieurs indicateurs statistiques tels que la moyenne, la médiane, les quartiles, la variance, l’écart-type et des coefficients d’asymétrie des distributions des deux variables aléatoires (Ventes et Coûts) sont affichés dans le tableau suivant.
L’histogramme ci-dessous permet de visualiser la distribution de la variable Coûts. Il en est de même pour la variable Ventes.

Comment interpréter l’analyse de sensibilité  ?

Les mêmes tableaux et graphiques sont présents pour étudier la variable résultat suite à la simulation. Ils sont suivis d’une analyse de sensibilité qui se fonde sur les résultats des simulations. Le tableau ci-dessous présente les résultats de cette analyse pour la variable résultat (Bénéfices).
Par exemple, nous pouvons constater que l’évolution des coûts contribue à quasiment 80 % à celle des bénéfices et que la corrélation entre ces deux variables est négative. Plus les coûts augmentent, plus les bénéfices diminuent. Ces résultats sont illustrés par le graphique ci-dessous.

Comment interpréter l’analyse tornado ?

Contrairement à l'analyse de sensibilité, l'analyse tornado ne dépend pas des simulations, mais d'une analyse point par point de toutes les variables d'entrée (les variables aléatoires et les variables scénario, non utilisées dans ce tutoriel).
Lors de l'analyse tornado, pour chaque variable résultat, les variables aléatoires d'entrée et les variables scénario sont analysées une par une. On fait varier leur valeur entre deux bornes puis on enregistre la valeur de la variable résultat, afin de savoir comment chaque variable influence la variable résultat. Pour une variable aléatoire, les valeurs explorées peuvent l'être autour de la médiane ou autour de la valeur par défaut de la variable, avec des limites définies par les percentiles ou par la déviation. Pour une variable scénario, l'analyse est effectuée entre les deux limites spécifiées lors de la définition de la variable.
Dans le tableau et sur le diagramme, nous pouvons voir que les coûts ont l'impact le plus fort sur le bénéfice. Ceci est lié à la largeur de l'intervalle exploré.
Enfin, la matrice de corrélation entre les variables d'entrée et les variables résultats est affichée. Nous voyons que les coûts et les ventes ne sont pas corrélés. Mais le bénéfice, lui, est bien entendu lié aux ventes et aux coûts.