Solution d'analyse de données

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

20/10/2017

Modèles de simulation

Les modèles de simulation permettent d'obtenir des informations, telles que la moyenne ou 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. Sim vous permet de définir les distributions, puis d’obtenir, par le biais de simulations itératives 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. A chaque itération du calcul du modèle de simulation, un tirage aléatoire est effectué dans chacune des distributions.
  • 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 chacune des valeurs 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 tout un classeur.

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

Une feuille Excel contenant les données et les résultats de cet exemple peut être téléchargée en cliquant ici.

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 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 à une loi de probabilité 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 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 variation 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 Sim1. 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" :

sim201f.gif

Créer une variable scénario pour la simulation

Sélectionnez ensuite la cellule pour y définir la variable scénario correspondant au taux de change.

Une fois XLSTAT lancé, cliquez sur la commande Définir une variable scénario du menu XLSTAT / Sim, ou cliquez sur le bouton correspondant de la barre Sim (voir ci-dessous).

barsim2f.gif

La boîte de dialogue Définir une variable scénario apparaît alors. Sélectionnez la cellule Excel qui contient le nom de la variable, "taux d'échange", puis les cellules correspondant au minimum et maximum des valeurs entre lesquelles XLSTAT fera varier le taux de change. Choisissez le type de données Continues, cette variable pouvant prendre toute les valeurs dans l'intervalle défini.

sim202f.gif

Une fois que vous avez cliquez sur OK, l'appel à la fonction XLSTAT_SimSVar est ajouté dans la cellule active (E2).

Définir une statistique pour la simulation

Sélectionnez la cellule E6 qui se trouve juste à droite de “Ecart-type résultat” pour y définir une statistique qui sera calculé pendant le processus de simulation.

Nous allons ici prendre l'écart-type de la variable résultat "Résultat" de manière à vérifier que le nombre de simulations est suffisant.

La statistique est réévaluée à chaque itération du processus de simulation.

Cliquez sur la commande the Sim / Définir une statistique du menu XLSTAT, ou sur le bouton correspondant de la barre d'outils Sim.

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

sim203f.gif

Une fois que vous avez cliquez sur OK, l'appel à la fonction XLSTAT_SimStat est ajouté dans la cellule active (E6).

Lancer les simulations

Si vous le souhaitez, Sim vous permet 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 Sim / Réinitialisation du modèle de simulation du menu XLSTAT, ou cliquez sur le bouton correspondant de la barre d'outils Sim.

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 pour l'instant. La statistique n'est quant à elle pas encore évaluée car il faut au moins deux valeurs pour calculer l'écart-type (n-1).

Pour faire d'autres pas de simulation, cliquez sur la commande Sim / Faire un pas de simulation du menu XLSTAT, ou cliquez sur le bouton correspondant de la barre d'outils Sim.

Vous pouvez alors constater que la statistique "Écart-type résultat" converge rapidement.

Pour lancer les simulations en mode continu, cliquez sur la commande Sim / Lancer les calculs du menu XLSTAT, ou cliquez sur le bouton correspondant de la barre d'outils Sim.

La boîte de dialogue Lancer les calculs apparaît alors. Fixez le nombre de simulations à 1000.

sim105f.gif

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

Choisissez la valeur par défaut de la cellule comme valeur centrale.

Pour définir l'intervalle de variation autour de chaque variable distribution et les pas auxquels les calculs doivent être effectués, choisissez 10 points et des intervalles allant de -10% à 10% de la valeur centrale:

sim106f.gif

Dans l'onglet Sorties, l'option Détails des simulations est activée.

sim204f.gif

Les calculs commencent lorsque vous avez cliqué sur OK.

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 tu 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 clairement négativement corrélé avec le résultat.

sim205f.gif

Enfin, les détails des simulation 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 clairement l'évolution de la statistique qui converge rapidement vers les 22 avant d'osciller autour de cette valeur.

sim206f.gif

Nous contacter

Envoyer un email au service commercial

Contacter notre équipe de support technique : support@xlstat.com

https://cdn.desk.com/
false
desk
Chargement
il y a quelques secondes
il y a une minute
il y a quelques minutes
il y a une heure
il y a quelques heures
il y a un jour
il y a quelques jours
à propos de
false
Caractères non valides trouvés
/customer/portal/articles/autocomplete
9283