CALCUL BESOIN PREVISIONNEL MATIERES PREMIERES

[Charles78]

Bonjour à tous,

Je pense avoir réussi à créer la trame "adéquate" permettant de calculer les besoins prévisionnels en matières premières en fonction des consommations du stock suite à des productions de produits finis.

- Pour cela j'ai 4 onglets à mettre en relation :

1) la nomenclature des produits finis (onglet "NOMENCLATURE"),

2) le planning de production des produits finis sur 12 semaines qui consomment les matières premières (onglet "PLANNING DE PRODUCTION"),

3) le stock disponible de matières premières (onglet "STOCK MATIERES PREMIERES"),

4) les commandes de matières premières sur 12 semaines (onglet "COMMANDE MATIERES PREMIERES").


- J'ai pu créer jusqu'à présent la formule pour afficher à la fin de chaque semaine de production le stock restant de matières premières (colonne "STOCK MATIERES RESTANT" dans l'onglet "STOCK MATIERES PREMIERES") mais je ne sais pas comment faire pour pouvoir afficher dans la colonne "QUANTITES MATIERES PREMIERES CONSOMMEES" le résultat car il faut prendre en compte le planning de production et la nomenclature.


- Mon objectif final est dafficher le niveau de stock restant des matières premières semaine par semaine (= 1. stock initial - 2. les sorties de stocks + 3. les entrées de stocks) afin d'identifier dans l'onglet "STOCK MATIERES PREMIERES" la matière première qui tombera en rupture à partir d'une semaine donnée (en mettant la cellule d'une certaine couleur et afficher "OUI" dans la colonne S1, S2...) et par conséquent me permettre de savoir quand et quelle matières premières je dois recommander.

1. le niveau de stock initial par matière première (stock à date à partir de l'extraction lancée le jour du calcul du besoin ou stock restant de la semaine précédente),

2. afficher semaine par semaine les sorties de stock (c'est-à-dire les consommations de matières premières en fonction du planning de production),

3. afficher les entrées de stock (s'il y a une livraison prévue chaque semaine pour chaque matière première = je n'ai rien renseigné pour le moment).


Dites moi si je ne suis pas clair (vous avez mon fichier en pièce jointe).

Merci pour votre aide.

 

[WTF]

Bonjour Charles,
Bienvenu sur le forum.
Tu trouveras en PJ une proposition.
A+

 

[Charles78]

Bonjour WTF,

Merci pour l'accueil et ton fichier. Je vais étudier le fichier et ensuite je te ferai un retour.

Merci encore.

 

[Charles78]

Bonjour WTF,

J'ai bien pris connaissance de ta proposition et je crois que je comprends dans les grands axes les formules utilisées. C'est vrai que je n'ai pas pensé à rajouter dans l'onglet nomenclature les 12 semaines avec la RECHERCHEV.

En revanche si je prends en compte le planning de production pour S1, le produit fini concerné est le 2277128 mais aucune des matières premières auxquelles il est rattaché sont consommées. Il y a le résultat 0 pour S1 que ce soit dans l'onglet nomenclature et dans l'onglet stock matières premières. Aussi, dans l'onglet matières premières, il y a bien une quantité qui est consommée pour 4 matières premières mais ces dernières ne concernent pas le produit fini 2277128 alors qu'en S1 il n'y a que ce produit fini qui est planifié dans le plannig de production avec une quantité de 6 804. Donc normalement pour la S1, les matières premières qui doivent êtres consommées sont :

- 2278643
- 2377062
- 2278644
- 2398309DS
- 2293224

Merci.

 

[Charles78]

Bonjour,

Je viens de me rendre compte que sur le planning de production il se peut que le produit fini soit planifié plusieurs fois mais la fonction RECHERCHEV renvoie systématiquement la première valeur trouvée. Donc pour le produit fini 2277128 qui est planifié deux fois à savoir en S1 et S11 même si on fait la recherchev en ayant pour matrice l'onglet planning de production et en choisissant la deuxième colonne pour les quantités qui sont en S1, il va trouver la première valeur du produit fini 2277128 qui est pour le coup 0 et non 6804. Alors là ça devient encore plus compliqué pour moi. Est-ce que passer par une macro serait plus simple ?

Merci.

 

[Charles78]

Bonjour,

Personne d'autre a une petite idée ?

Merci.

 

[Dugenou]

Bonjour,
Merci pour ce fichier très clair et bien contruit.
Comme WTF j'ai créé un tableau des consommations par matière et par semaine. A la lumière de vos explications j'ai fais des somme.si au lieu de recherchev.
J'ai créé des tableaux structurés (insertion / tableau) : la lecture des formules est facilitée : c'est juste inquiétant la première fois avec les @ et [ et ] mais on s'y fait très vite. Dans certains cas la colonne est écrite 2 fois pour éviter de changer quand on recopie à droite (comme des $ dans une formule classique) : par exemple
=SOMME.SI(Tb_Planning[[PRODUIT FINI]:[PRODUIT FINI]];Tb_Nomenclature[@[PRODUIT FINI]:[PRODUIT FINI]];Tb_Planning[S1])
se recopie à droite sans que le nom de colonne "produit fini" change, par contre la colonne "S1" va changer

Je reste à ton écoute si plus d'explications sont nécessaires
Cordialement

 

[Charles78]

Bonjour Dugenou,

Merci pour cette solution. J'ai regardé le fichier et effectivement ça m'a l'air de bien fonctionner ! Oui, j'ai compris que tu as "mis sous forme de tableau" les sources de données de l'onglet nomenclature en nommant le tableau "Tb_Nomenclature" et de l'onglet planning de production en nommant le tableau "Tb_Planning".

Juste une question: dans mon exemple, il s'agit des productions de S1 à S12 mais cela peut changer c'est à dire que dans l'onglet planning de production, le fichier source peut indiquer une autre période de production (exemple de S13 à S24). Je sais que je pourrai mettre en forme l'onglet planning de production comme tu l'as fait en renommant le tableau Tb_Planning mais j'aimerais que dans les ontres onglets (qui eux aussi indiquent les semaines de production) qu'ils reprenennent les semaines qui sont indiquées en en-tête de l'onglet planning de production. Avec ton fichier, j'ai essayé de mettre dans l'onglet nomenclature en cellule D1 la formule = B1 de l'onglet planning de production mais sachant que les deux fichiers sont déjà mis sous forme de tableau avec en tête j'obtiens le résultat 0. Pour les onglets stock matières premières et commande matières premières cela fonctionne car les données sources ne sont pas mise sous forme de tableau.

Je ne sais pas si je suis clair ?

Merci.

 

[Dugenou]

Bonjour,
En effet tu ne peux pas mettre de formule dans les titres d'un tableau structuré.
Le plus simple est de changer les semaines du planning puis de faire un copier coller dans le tableau nomenclature
Ensuite tu devras changer aussi les titres dans le tableau de livraison.
Ou bien on remet les tableaux en plage et du coup on peut lier les titres de colonnes. Il faudra réécrire les formules à la façon classique en utilisant les adresses de cellules : on sera ennuyé si tes tableaux s'allongent : il faudra adapter les formules à la nouvelle longueur des données.
Sur le fond : la liaison directe avec des colonnes comporte un certain risque : on pourrait, dans la formule, rechercher la colonne qui a le même titre mais cela va compliquer un peu. A toi de voir si tes colonnes seront toujours les mêmes au même endroit dans les 3 tableaux.

Cordialement

 

[Charles78]

Re,

Je pense que je vais rester comme ça. Je vais effectivement changer manuellement les semaines du planning puis faire un copier coller dans le tableau nomenclature.

Une dernière question si je peux me permettre, si je souhaite créer les formules sous forme de macro, quelle serait la formule en :

- D2 sur l'onglet nomenclature

- D2 et E2 sur l'onglet stock matières premières

D'habitude j'utilise l'enregistreur de macro (comme par exemple pour la formule en C2 dans l'onglet stock matières premières) mais sachant qu'il y a des noms de tableau dans la formule pour les autres cellules, je ne pense pas réussir à la retranscrire moi-même.

Merci Dugenou.

 

[Charles78]

Re,

C'est bon, j'ai trouvé un moyen de les obtenir en faisant un copier coller de la formule et en utlisant l'enregistreur de macro.

Merci à Dugenou et WTF pour votre précieuse aide.