Solution d'analyse de données

Créer un modèle de simulation 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 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 4 objets mentionnés plus hauts sont accessibles en cliquant sur les liens suivants : scenario-variables-simulation-model.

Une feuille Excel contenant les données et les résultats de cet exemple peut être téléchargée en cliquant 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 le tutoriel sur ce sujet pour plus d'informations).

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

sim101f.gif

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

Créer un modèle de simulation

Pour créer la première variable aléatoire, sélectionnez la cellule B2 qui correspond au montant des ventes.

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

barsim1f.gif

La boîte de dialogue Définir une distribution est alors affichée. Sélectionnez ensuite le Nom de la variable disponible dans la cellule qui contient "Ventes". Choisissez ensuite une distribution normale avec mu = 120 et sigma = 10.

sim102f.gif

Lorsque vous cliquez sur le bouton OK, une formule avec un appel à une fonction de Sim est générée dans la cellule B2.

La deuxième variable aléatoire (ou distribution) est générée de la même manière. Sélectionnez dans ce cas une distribution normale avec mu = 80 et sigma = 20. Voici la boîte de dialogue correspondante :

sim103f.gif

Sélectionnez ensuite la cellule qui contient 40 comme résultat de la formule =B2-B3.

Cliquez alors sur XLSTAT / Sim / Définir une variable résultat ou sur le bouton correspondant de la barre Sim.

La boîte de dialogue Définir une variable résultat est alors affichée. Sélectionnez ensuite le Nom de la variable disponible dans la cellule A4.

sim104f.gif

Lorsque vous cliquez sur le bouton OK, une formule avec un appel à XLSTAT_SimRes est générée dans la cellule B4.

Lancer les simulations

Pour lancer les simulations, cliquez sur la commande XLSTAT / Sim / Lancer les simulations ou sur le bouton correspondant de la barre d'outils Sim.

Une boîte de dialogue apparaît. Elle vous permet de définir un certain nombre de paramètres, parmi lesquels le nombre de simulations à réaliser.

Dans cet exemple, 1000 simulations sont réalisées. Cela signifie que 1000 valeurs seront échantillonnées dans chaque distribution et que pour chaque couple de valeur, la variable résultat sera calculée. On obtiendra alors une distribution empirique de la variable résultat basée sur 1000 simulations.

sim105f.gif

Dans l'onglet Options, les paramètres pour les diagrammes tornado et araignée ont été modifiés.

sim106f.gif

Les calculs commencent lorsque vous avez cliqué sur OK.

Interpréter les résultats des simulations

Le premier résultat affiché est une description du modèle.

sim107f.gif

Ensuite, des détails sur les deux distributions et sur la variable résultat sont affichés.

Les tableaux suivants présentent les détails pour les deux variables aléatoires (Ventes et Coûts). Des statistiques descriptives, un histogramme et des statistiques sur les intervalles sont affichés.

sim108f.gifsim109f.gif

Les tableaux ci-dessous fournissent des détails pour la variable résultat. Des statistiques descriptives, un histogramme et des statistiques sur les intervalles sont affichés, suivis de l'étude de sensibilité. L'analyse de sensibilité se fondent sur les résultats des simulations.

sim110f.gif

La section suivante correspond aux résultats de l'analyse tornado.

Contrairement à l'analyse de sensibilité, l'analyse tornado ne dépend pas des simulations mais d'une analyse point par point analysis de toutes les variables d'entrée (les variables aléatoires et les variables scénario, non utilisées dans ce tutoriel).

Au cours 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 et 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. Le nombre de points est une option qui peut être modifiée par l'utilisateur avant d'exécuter le modèle de simulation, dans les options de Sim.

Sur le diagramme on voit que les coûts ont l'impact le plus fort sur le bénéfice. Cela est lié à la largeur de l'intervalle exploré.

sim111f.gif

Enfin, la matrice de corrélation entre les variables d'entrée et les variables résultat est affichée. Nous voyons que les coûts et les ventes ne sont pas corrélés. Mais le bénéfice est bien entendu lié aux ventes et aux coûts.

sim112f.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