Plan d'approvisionnement équitable

[kan3pz]

Bonjour à tous,
Ne sachant pas programmer en VBA je recherche une solution par formule dans Excel pour réaliser un plan d'approvisionnement équitable entre mes fournisseurs. Mes données d'entrée :
1 - Disponibilités par fournisseur
2 - Contrat par fournisseur
3 - Besoins (global)

Le but est de répondre à mes besoins en prenant les dispos par fournisseur pour les équilibrer je calcul un taux cible Besoin/CT pour équilibrer mes producteurs par contre je ne sais pas comment faire une itération pour équilibrer les manques des fournisseurs qui ont leurs dispos < aux contrats sur les fournisseurs qui ont du plus.

Cf PJ, merci d'avance !

 

[eriiic]

Dans ton exemple aucun fournisseur n'a de qté dispo > contrat et le total dispo = besoin, pas vraiment besoin d'excel...
Un essai avec le solveur.
eric

 

[mapomme]

Bon jour kan3pz, eriiiic,

J’avais commencé une solution à base de solveur mais en utilisant des valeurs entières pour les appros à faire. Ça ressemble à la solution d'eriiiic . On indique le besoin puis on lance le solveur.

 

[eriiic]

Salut mapomme,

Je préfère ton tableau au mien mais pour '% conso de chaque contrat avant appro' est-ce que =C2/$C$6 n'est pas plus logique ?
er

 

[kan3pz]

Bonjour mapomme et eriiic,
Merci pour votre aide je ne connaissais pas cette solution solveur, je vais creuser ça mais je pense que ça va répondre à ma problématique!

 

[mapomme]

Bonjour Eric,

(...) pour '% conso de chaque contrat avant appro' est-ce que =C2/$C$6 n'est pas plus logique ? (...)

Ce que je calcule dans cette colonne est la conso pour chaque contrat individuellement (=1-B2/C2) et non la conso par rapport au total des contrats. Le but (du moins c'est ce que j'en ai compris) est, qu'au fur et à mesure des commandes, le pourcentage de consommation de chaque contrat tende vers la même valeur (aux arrondis près).

Quand on saisit le besoin, on peut calculer le pourcentage moyen cible (déjà consommé + besoin) mais, cette fois-ci, pour l'ensemble des contrats (cellule [F6] = =1-(B6-E6)/C6). Idéalement, à la fin de la résolution, le pourcentage de consommation de chaque contrat devrait être égal à [F6] qui a été calculé.

A l'aide du solver, on minimise donc la différence des taux de consommation de chaque contrat avec la valeur cible théorique en [F6] en colonne G.

Les colonnes C et F permettent de voir l'évolution de la convergence des taux de consommation de chaque contrat vers une valeur unique (aux arrondis près).

Je ne sais pas si c'est beaucoup plus clair

 

[eriiic]

Moi j'ai compris qu'il voulait s'approcher de la répartition contractuelle si tous les contrats peuvent être honorés.
Par exemple Fournisseur1: 50%, Fournisseur2: 25%, Fournisseur1: 25% pour toute commande.
Ton 0 chez le fournisseur qui contractuellement peut lui fournir le plus me fait bizarre. Mais c'est peut-être moi qui ai la mauvaise lecture.
Pour le solveur je suis d'accord avec toi, j'ai utilisé le même principe sauf une pondération et surtout sauf que le mien bugue parfois ;-)
On en saura plus à son retour.

 

[mapomme]

Re Eriiiic,

(...) Ton 0 chez le fournisseur qui contractuellement peut lui fournir le plus me fait bizarre (...)

En fait, j'ai considéré que je devais faire des commandes à partir d'une situation donnée pour qu'au fur et à mesure de mes commandes les taux de consommation de chaque contrat soit les mêmes. Je prends une situation en cours où le gars avant moi a fait n'importe quoi! Des gros fournisseurs avec aucune commande (en ratio ex: fournisseur B), des petits fournisseurs avec plein de commande (en ratio ex: fournisseur A).

Si on teste les valeurs des besoins les unes après les autres (soit 1 puis 2 puis 3 ...), on s'aperçoit que les ratios de consommation par contrat tendent vers une même valeur (à la modularité près). Le fournisseur A ne verra aucune commande avant que le taux de consommation des autres avoisine le 40%. Les gros fournisseurs à 0% de consommation verront tout de suite des commandes pour rattraper les autres.

Après c'est comme tu as écrit : c'est kan3pz qui verras...

 

[kan3pz]

Après c'est comme tu as écrit : c'est kan3pz qui verras...

Effectivement sur la solution finale c'est eriiic qui a raison, je souhaite par priorité :
1 : Répondre au besoin
2 : En respectant mes contrats (un volume dispo dépassant la valeur du CT ne doit pas être considéré dans un premier temps)
3 : Si il y a situation de surplus il doit y avoir un équilibre entre les producteurs (Taux de réalisation = V appro/V contrat)

En résumé il me faut une compilation de vos 2 réponses la structure du fichier de mapomme me parait bien mais il faut modifier les règles pour avoir une répartition du volume d'appro équitable entre producteur par rapport au CT.

Dans tous les cas merci de votre aide pour ce début de solution je vais essayer d'affiner ce résultat de mon côté mais n'hésitez pas si vous souhaitez continuer !

 

[eriiic]

Comme on a du mal à sortir r@chid de sa micro-sieste de 2h je me suis permis d'adapter son tableau.
Peut-être aura-t-il une autre idée

 

[kan3pz]

Bonjour eriiic,
Merci pour cette solution c'est exactement ce qu'il me fallait !