Optimisation de la livraison

Formulation du problème

Supposons que l'entreprise dans laquelle vous travaillez dispose de trois entrepôts, d'où les marchandises sont acheminées vers cinq de vos magasins disséminés dans Moscou.

Chaque magasin est en mesure de vendre une certaine quantité de marchandises que nous connaissons. Chacun des entrepôts a une capacité limitée. La tâche consiste à choisir rationnellement à partir de quel entrepôt vers quels magasins livrer les marchandises afin de minimiser les coûts de transport totaux.

Avant de commencer l'optimisation, il faudra compiler un simple tableau sur une feuille Excel – notre modèle mathématique décrivant la situation :

Il est bien compris que:

• Le tableau jaune clair (C4:G6) décrit le coût d'expédition d'un article de chaque entrepôt à chaque magasin.
• Les cellules violettes (C15 : G14) décrivent la quantité de marchandises nécessaires à la vente de chaque magasin.
• Les cellules rouges (J10:J13) affichent la capacité de chaque entrepôt - la quantité maximale de marchandises que l'entrepôt peut contenir.
• Les cellules jaunes (C13:G13) et bleues (H10:H13) sont respectivement les sommes des lignes et des colonnes pour les cellules vertes.
• Le coût total d'expédition (J18) est calculé comme la somme des produits du nombre de marchandises et de leurs frais d'expédition correspondants - pour le calcul, la fonction est utilisée ici SUMPRODUCT (SOMMEPROD).
  

Ainsi, notre tâche se réduit à la sélection des valeurs optimales des cellules vertes. Et pour que le montant total de la ligne (cellules bleues) ne dépasse pas la capacité de l'entrepôt (cellules rouges), et en même temps chaque magasin reçoit la quantité de marchandises qu'il doit vendre (le montant pour chaque magasin dans le les cellules jaunes doivent être aussi proches que possible des exigences (cellules violettes).

Solution

En mathématiques, de tels problèmes de choix de la répartition optimale des ressources sont formulés et décrits depuis longtemps. Et, bien sûr, les moyens de les résoudre ont longtemps été développés non pas par une énumération directe (ce qui est très long), mais en un très petit nombre d'itérations. Excel fournit à l'utilisateur une telle fonctionnalité à l'aide d'un complément. Solutions de recherche (Solveur) de l'onglet Données (Date):

Si sur l'onglet Données votre Excel n'a pas une telle commande - ça va - cela signifie que le complément n'est tout simplement pas encore connecté. Pour l'activer, ouvrez Déposez votre dernière attestation , Puis sélectionnez Paramètres - Ajouts - Qui sommes-nous (Options - Compléments - Aller à). Dans la fenêtre qui s'ouvre, cochez la case à côté de la ligne dont nous avons besoin Solutions de recherche (Solveur).

Exécutons le module complémentaire :

Dans cette fenêtre, vous devez définir les paramètres suivants :

• Optimiser la fonction cible (Réglez tde l'argent cellule) – ici, il faut indiquer l'objectif principal final de notre optimisation, c'est-à-dire la boîte rose avec le coût total d'expédition (J18). La cellule cible peut être minimisée (s'il s'agit de dépenses, comme dans notre cas), maximisée (s'il s'agit, par exemple, d'un profit) ou essayer de la ramener à une valeur donnée (par exemple, s'inscrire exactement dans le budget alloué).
• Modification des cellules variables (By en changeant cellules) – ici nous indiquons les cellules vertes (C10 : G12), en faisant varier les valeurs dont nous voulons atteindre notre résultat – le coût minimum de livraison.
• Conforme aux restrictions (Sujet à le Contraintes) – une liste de restrictions à prendre en compte lors de l'optimisation. Pour ajouter des restrictions à la liste, cliquez sur le bouton Ajouter (Ajouter) et entrez la condition dans la fenêtre qui apparaît. Dans notre cas, ce sera la contrainte de demande :

   

  et limitation du volume maximum des entrepôts :

Outre les limitations évidentes liées aux facteurs physiques (capacité des entrepôts et des moyens de transport, contraintes de budget et de temps, etc.), il est parfois nécessaire d'ajouter des restrictions "spéciales pour Excel". Ainsi, par exemple, Excel peut facilement s'arranger pour que vous "optimisiez" le coût de la livraison en vous proposant de transporter les marchandises des magasins jusqu'à l'entrepôt - les coûts deviendront négatifs, c'est-à-dire que nous ferons un profit ! 🙂

Pour éviter que cela ne se produise, il est préférable de laisser la case cochée. Rendre des variables illimitées non négatives ou même parfois enregistrer explicitement de tels moments dans la liste des restrictions.

Après avoir défini tous les paramètres nécessaires, la fenêtre devrait ressembler à ceci :

Dans la liste déroulante Sélectionner une méthode de résolution, vous devez en outre sélectionner la méthode mathématique appropriée pour résoudre un choix de trois options :

• Méthode simplexe est une méthode simple et rapide pour résoudre des problèmes linéaires, c'est-à-dire des problèmes où la sortie dépend linéairement de l'entrée.
• Méthode générale du gradient dégradé (OGG) – pour les problèmes non linéaires, où il existe des dépendances non linéaires complexes entre les données d'entrée et de sortie (par exemple, la dépendance des ventes aux coûts publicitaires).
• Recherche évolutive d'une solution – une méthode d'optimisation relativement nouvelle basée sur les principes de l'évolution biologique (bonjour Darwin). Cette méthode fonctionne plusieurs fois plus longtemps que les deux premières, mais peut résoudre presque tous les problèmes (non linéaires, discrets).
  

Notre tâche est clairement linéaire : livré 1 pièce – dépensé 40 roubles, livré 2 pièces – dépensé 80 roubles. etc., la méthode du simplexe est donc le meilleur choix.

Maintenant que les données pour le calcul sont saisies, appuyez sur le bouton Trouver une solution (Résoudre)pour commencer l'optimisation. Dans les cas graves avec beaucoup de cellules changeantes et de contraintes, trouver une solution peut prendre beaucoup de temps (surtout avec la méthode évolutive), mais notre tâche pour Excel ne sera pas un problème - dans quelques instants, nous obtiendrons les résultats suivants :

Faites attention à la manière intéressante dont les volumes d'approvisionnement ont été répartis entre les magasins, sans dépasser la capacité de nos entrepôts et en satisfaisant toutes les demandes du nombre requis de marchandises pour chaque magasin.

Si la solution trouvée nous convient, nous pouvons la sauvegarder ou revenir aux valeurs d'origine et réessayer avec d'autres paramètres. Vous pouvez également enregistrer la combinaison de paramètres sélectionnée sous Scénario. A la demande de l'utilisateur, Excel peut construire trois types Rapports sur le problème à résoudre sur des feuilles séparées : un rapport sur les résultats, un rapport sur la stabilité mathématique de la solution et un rapport sur les limites (restrictions) de la solution, cependant, dans la plupart des cas, ils n'intéressent que les spécialistes .

Il existe cependant des situations où Excel ne trouve pas de solution appropriée. Il est possible de simuler un tel cas si nous indiquons dans notre exemple les besoins des magasins d'un montant supérieur à la capacité totale des entrepôts. Ensuite, lors de l'exécution d'une optimisation, Excel essaiera de se rapprocher le plus possible de la solution, puis affichera un message indiquant que la solution est introuvable. Néanmoins, même dans ce cas, nous avons beaucoup d'informations utiles – en particulier, nous pouvons voir les « maillons faibles » de nos processus métier et comprendre les axes d'amélioration.

L'exemple considéré, bien sûr, est relativement simple, mais s'adapte facilement pour résoudre des problèmes beaucoup plus complexes. Par exemple:

• Optimisation de la répartition des ressources financières par poste de dépense dans le plan d'affaires ou le budget du projet. Les restrictions, dans ce cas, seront le montant du financement et le calendrier du projet, et l'objectif de l'optimisation est de maximiser les profits et de minimiser les coûts du projet.
• Optimisation des horaires des employés afin de minimiser le fonds salarial de l'entreprise. Les restrictions, dans ce cas, seront les souhaits de chaque employé en fonction du calendrier d'emploi et des exigences du tableau des effectifs.
• Optimisation des investissements d'investissement – la nécessité de répartir correctement les fonds entre plusieurs banques, titres ou actions d'entreprises afin, là encore, de maximiser les profits ou (si plus important) de minimiser les risques.

Dans tous les cas, un complément Solutions de recherche (Solveur) est un outil Excel très puissant et magnifique et digne de votre attention, car il peut vous aider dans de nombreuses situations difficiles auxquelles vous devez faire face dans les affaires modernes.