Cette page s'adresse aux étudiants de première année de l'Ecole de Management de l'Institut National des Télécommunications dans le cadre de l'enseignement des probabilités et des statistiques. Néanmoins, elle peut être consultée par toute personne souhaitant acquérir quelques rudiments sur les fonctions probabilistes offertes par le tableur "Excel". La connaissance des outils de base d'Excel est requise. Une présentation sommaire de ce logiciel avec exercices d'application (en anglais) est disponible ici. En outre, si vos connaissances en probabilités doivent être 'rafraîchies', vous pouvez vous rendre sur le site "Defense Acquisition University" (en anglais) : sa présentation originale est très réussie.

La première partie de la séance est consacrée à la construction d'un modèle élémentaire de gestion de stock. La seconde concerne l'optimisation de la gestion d'un stock en tenant compte des contraintes financières liées au stockage.

Afin de vous ménager une part active lors de cette séance, vous devrez rechercher vous-mêmes les fonctions demandées. Néanmoins, pour certaines d'entre elles, des explications vous seront fournies pour vous permettre d'y accéder plus facilement. Les mots ou groupes de mots apparaissant en vert sur cette page correspondent à des fonctions décrites dans l'aide d'Excel.
Certaines questions, dont la résolution peut poser quelques difficultés, comportent des indications sous forme de liens. Ne les consulter qu'après avoir consacré un minimum de temps à la recherche de la solution : c'est à cette condition que vous rentabiliserez votre travail.

CONFIGURATION DU LOGICIEL

Avant de commencer le travail à effectuer, vous devez vérifier que le module "utilitaire d'analyse" est installé. Pour ce faire, ouvrir "Excel"et cliquer sur "outils" dans la barre des menus: si la mention "utilitaire d'analyse" apparaît (en général en bas du menu déroulant), vous n'avez aucune manipulation à effectuer car le module est installé. Dans le cas contraire, cliquer sur "outils" dans la barre des menus, puis sur "macros complémentaires". Ensuite cocher la case "utilitaire d'analyse" et cliquer sur "OK". La mention "utilitaire d'analyse" doit maintenant apparaître dans le menu "outils".

MODELE ELEMENTAIRE DE GESTION DE STOCK

MODELISATION DE LA DEMANDE D'UN PRODUIT

Un article est l'objet d'une demande journalière qui a été observée sur une période de 218 jours. Les résultats ont été consignés sur une feuille de calcul Excel (à télécharger).

On a des raisons de penser que cette demande obéit à une loi de Poisson. Afin de confirmer ces soupçons, on se propose de représenter sur un même graphique l'histogramme des résultats observés et celui correspondant à une loi de Poisson de paramètre égal à la moyenne empirique observée sur l'échantillon (le paramètre d'une loi de Poisson coïncide avec son espérance mathématique).

Compte tenu des valeurs relevées sur l'échantillon de 218 jours, on constituera des classes de longueur égale à 5 de 20 à 80, soit douze classes (20 à 25, 25 à 30, 30 à 35, ..., 75 à 80).
 

Tracer le double histogramme. (indications)

Peut-on conclure que les lois théorique et empirique sont assez proches l'une de l'autre?

Quelle doit être la valeur minimum du stock en début de journée pour que la probabilité d'être en rupture de stock à la fin de la journée soit inférieure à 0,1? (indication)

MODELISATION DE LA GESTION D'UN STOCK

Soient S_n le niveau du stock au début de la journée n, X_n la demande d'un produit au cours de la journée n
et Y_n = S_n - X_n le stock intermédiaire (cette quantité peut être négative). On suppose que les variables X_n sont indépendantes et suivent une loi de Poisson de paramètre 50,5.

si Y_n < 60, on procède à une remise à niveau du stock de telle sorte que S_n+1 = 60

sinon, on ne réapprovisionne pas et on a alors S_n+1 = Y_n .

Effectuer la simulation de ce modèle sur une période de 150 jours: on construira trois colonnes adjacentes contenant respectivement X_n , Y_n et S_n+1 (on initialisera le stock à la valeur 60). (indication)

Estimer, à partir de cette simulation, la probabilité d'être en rupture de stock. Cette valeur est-elle en accord avec les résultats de la partie précédente? (indication)

OPTIMISATION D'UN BENEFICE

Le problème consiste à déterminer les stocks s_x et s_y pour optimiser le bénéfice moyen lié à la vente des produits A et B compte-tenu des coûts inhérents au stockage. Sa résolution s'effectuera grâce au solveur d'Excel.

La méthode de résolution choisie nécessite la mise en place d'une simulation des variables X et Y et des bénéfices correspondant B_x et B_y . On effectuera 100 simulations (choisir "1" pour l'entier générateur).

 
Utiliser le solveur pour résoudre le problème posé (penser à fixer la contrainte "nombre entier" pour s_x). Les valeurs initiales pour s_x et s_y pourront être choisies égales à 10 et 12. (indications)

L'exemple choisi plus haut fonctionne bien car les fonctions mathématiques en jeu sont simples. Toutefois, l'efficacité du solveur peut être mise en défaut lorsque les formules sont complexes et (ou) les valeurs initiales choisies pour les variables sont éloignées des valeurs optimales recherchées. Une illustration de ces limitations est donnée par le problème suivant:
 

déterminer à l'aide du solveur le plus grand entier m tel que la probabilité pour qu'une variable gaussienne de moyenne m et d'écart-type 5 dépasse 60 soit inférieure ou égale à 1%. Prendre comme valeurs initiales de m: 0, 10, 20, ...

Afin de contrôler vos résultats, vous pouvez consulter le corrigé.

Une autre page, similaire à celle-ci, est disponible en statistiques.

J'espère que cette séance aura contribué à susciter votre intérêt pour formaliser et résoudre des problèmes de nature probabiliste. N'hésitez pas à m'envoyer un  pour me faire part de vos remarques et suggestions.