Modèle de simulation avec variables scénario dans Excel

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

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 voie de conséquence aussi une distribution et non une valeur fixe. Les simulations Monte-Carlo 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.
Quatre types d’objets sont nécessaires pour la construction d’un modèle de simulation :

• Distributions : cet objet correspond à une variable aléatoire dont on choisit la distribution parmi un choix de plus de 20 distributions proposées par XLSTAT, afin d’exprimer l’incertitude quant aux valeurs que peut prendre la variable aléatoire. Par exemple, on choisira une distribution triangulaire lorsque l’on a une quantité que l’on sait pouvoir varier entre deux bornes mais avec une valeur qui semble plus probable. À chaque itération du calcul du modèle de simulation, un tirage aléatoire est effectué dans chaque distribution.

• Variables scénario : elles permettent d’introduire dans le modèle de simulation une quantité fixe pour un modèle de simulation donné, mais que l’on fait varier entre deux bornes avec un pas donné, afin d’étudier la sensibilité des variables résultats à ces variables. Autrement dit, on recalcule le modèle de simulation pour chaque valeur des variables scénario. Facultatives, les variables de décision sont néanmoins nécessaires pour les graphiques tornado.

• Variables résultat : les variables résultat sont des quantités qui dépendent directement ou indirectement, au travers de formules Excel, des variables aléatoires auxquelles ont été affectées des distributions, et éventuellement des variables de décision. Le but des calculs d’un modèle de simulation est justement de connaître la distribution des variables résultats.

• Statistiques : on peut définir une statistique associée à une distribution, à une variable résultat, ou à une autre statistique. Elle est calculée à chaque itération du calcul du modèle de simulation. Le rapport de simulation inclut alors des résultats concernant la statistique définie. Un grand nombre de statistiques est proposé par XLSTAT.

Un modèle doit comprendre au moins une distribution et une variable résultat, et autant des quatre objets définis ci-dessus que vous le souhaitez. Un modèle peut être limité à une unique feuille Excel ou peut utiliser un classeur entier.

Jeu de données pour utiliser les variables scénario et les statistiques dans un modèle de simulation

Dans ce tutoriel est utilisé un modèle identique à celui du premier tutoriel. Nous ajoutons ici une variable scénario et une statistique. Notre modèle de simulation est établi sur les ventes et les coûts d'un magasin. Dans ce cas élémentaire, 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 analysées avec l'outil Ajustement à une loi de probabilité, nous avons constaté que les coûts suivent une distribution normale (mu = 120, sigma = 10). Les ventes suivent également une distribution normale (mu=80, sigma=20) - Voir le tutoriel sur ce sujet pour plus d'informations.
Par ailleurs, nous supposons que 80 % des coûts et 30 % des ventes dépendent des variations d'un taux de change. Le taux est fixé à 1 au départ. Pendant l'analyse tornado, XLSTAT fera varier le taux de change de 0.8 à 1.2 pour étudier son impact sur le résultat.
Les variables "Ventes" et "Coûts" sont définies comme dans le tutoriel Créer un modèle de simulation dans Excel. La formule de calcul du "Résultat" est cependant plus compliquée en raison de l'intervention du taux de change : = (0.3 * E2 * B2 + 0.7 B2) -( 0.8 * E2 B3+ 0.2 * B3).Sur la base de ce modèle, les différentes variables sont insérées sur la feuille Excel "Modèle".

Créer une variable scénario pour la simulation

• Ouvrir XLSTAT.

• Cliquer sur la commande Définir une variable scénario du menu XLSTAT / Simulations Monte-Carlo. La boîte de dialogue Définir une variable scénario apparaît alors. Sélectionner la cellule Excel qui contient le nom de la variable, "taux d'échange".

• Sélectionner les cellules correspondant au minimum et maximum des valeurs entre lesquelles XLSTAT fera varier le taux de change.

• Choisir le type de données Continues, cette variable pouvant prendre toutes les valeurs dans l'intervalle défini.

• Cliquer sur OK. L'appel à la fonction XLSTAT_SimSVar est ajouté dans la cellule active (E2).

Définir une statistique pour la simulation

• Sélectionner la cellule E6 qui se trouve juste à droite de “Écart-type résultat” pour y définir une statistique qui sera calculée pendant le processus de simulation. Ici, l'écart-type de la variable résultat "Résultat" est choisi de manière à vérifier que le nombre de simulations est suffisant. Il est réévalué à chaque itération du processus de simulation.

• Cliquer sur la commande Simulations Monte Carlo / Définir une statistique du menu XLSTAT.

• La boîte de dialogue Définir une statistique apparaît alors. Sélectionner la cellule Excel qui contient le nom soit D6. Activer l'option Statistique descriptive puis choisir l'écart-type (n-1) dans la liste.

• Cliquer sur OK. L'appel à la fonction XLSTAT_SimStat est ajouté dans la cellule active (E6).

Lancer les simulations

Si vous le souhaitez, les Simulations Monte-Carlo dans XLSTAT vous permettent de lancer les simulations en mode pas à pas. Cela permet de vérifier que votre modèle se comporte comme prévu. Dans cet exemple, on peut ainsi suivre l'évolution de la statistique à chaque itération.
Pour initialiser le modèle de simulation et pour effacer l'information correspondant à de précédentes simulations, cliquez sur la commande Simulations Monte-Carlo / Réinitialisation du modèle de simulation du menu XLSTAT.
Vous pouvez alors voir les résultats de la première itération de simulation dans les cellules correspondant aux "Coûts", "Ventes" et "Résultat". La variable scénario étant utilisée uniquement pendant l'analyse tornado, elle ne change pas dans l’immédiat. La statistique n'est quant à elle pas encore évaluée, puisqu’il faut au moins deux valeurs pour calculer l'écart-type (n-1).
Pour faire d'autres pas de simulation, cliquez sur la commande  Simulations Monte-Carlo/ Faire un pas de simulation du menu XLSTAT.
Vous pouvez alors constater que la statistique "Écart-type résultat" converge rapidement.

Comment lancer les simulations ?

• Cliquer sur la commande  Simulations Monte-Carlo/ Lancer les calculs. La boîte de dialogue Lancer les calculs apparaît alors.

• Fixer le nombre de simulations à 1000.

• Dans l'onglet Options, entrer les paramètres pour l'analyse tornado et pour le diagramme araignée.

• Cliquer sur OK pour lancer l’analyse.

Interprétation des résultats de simulation sur un modèle incluant une variable scénario et une statistique

Les premiers résultats sont identiques à ceux présentés dans le premier tutoriel.
L'analyse tornado et le calcul du diagramme araignée ne sont pas fondés sur les itérations des simulations, mais sur une analyse point par point de toutes les variables d'entrée du modèle (variables d'entrée et variables scénario).
Au cours de l’analyse tornado, pour chaque variable résultat, les variables aléatoires et les variables scénario sont étudiées une par une. On fait varier leur valeur entre deux bornes et on enregistre la valeur des variables résultat afin de savoir comment chaque variable aléatoire et chaque variable scénario influent sur la valeur des variables résultat. Pour une variable aléatoire, les valeurs explorées sont soit autour de la médiane, soit autour de la valeur par défaut de la cellule, avec des limites définies par des percentiles ou des % de déviation. Pour une variable scénario, l'analyse est effectuée entre les deux bornes spécifiées lors de la définition de la variable. Le nombre de points étudiés pour les variables est une option qui peut être modifiée par l'utilisateur avant de lancer les simulations.
Dans notre exemple, l'analyse tornado permet de mesurer l'impact du taux de change. On voit que son influence est aussi importante que les "Coûts" et les "Ventes".
Plus de détails sont fournis avec le diagramme araignée. Le taux de change est négativement corrélé avec le résultat.
Enfin, les détails des simulations sont affichés. Pour chacune des variables du modèle, "Coûts", "Ventes", "Taux de change", et "Ecart-type résultat", sont affichées les valeurs à chaque itération. Nous voyons explicitement l'évolution de la statistique qui converge rapidement vers les 22 avant d'osciller autour de cette valeur.