http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

Suivi stock: insertion alerte en cas de depassement de stock minimum

Bonjour,

Dans des articles précédents (ici et ici), je vous présente un modèle pour mettre en place un simple suivi de stock avec Excel. Mais dans cette approche, mon outil ne me permet pas d’avoir des alertes lorsque le stock minimum est dépassé.
Aussi je vous propose d’améliorer ce tableau de suivi de stock en intégrant pour chaque écriture (entrée ou sortie de stock) deux points:
·         Le calcul du stock actualisé après le mouvement;
·         Des alertes en cas d’atteinte ou de dépassement du stock minimal pour le produit en question.

Rappel:
Notre outil a 3 feuilles:

Feuille Data, dans laquelle il y a nous avons un tableau de chaque produit avec son stock initial, son stock de sécurité, et son stock minimum:

Feuille Suivi stock dans laquelle, il y a tous les mouvements de stock

Feuille TCD dans laquelle on met une fiche de stock grâce à l’utilisation d’un tableau croisé dynamique:

Notre objectif est de pouvoir avoir des alertes à chaque mouvement de stock si le stock minimum est atteint. On va donc travailler sur la feuille suivi de stock. Il va falloir suivre 2 étapes:

1. Retrouver après chqaue écriture le solde du stock mis à jour

Pour cela on va rajouter une colonne « Solde » dans le tableau de suivi de stock. Dans celle-ci, nous insérerons une formule qui fera pour chaque écriture le calcul:

Stock initiale + total des entrées (à la date du mouvement) – total des sorties (à la date du mouvement)

Cette formule nous donnera le solde.

Comment traduire cette formule en Excel:

Stock initial: on utilisera la fonction RECHERCHEV

              RECHERCHEV([@Produit];Datas!$B$4:$E$14;2;0)

L’expression [@Produit] est générée automatiquement lorsqu’on pointe sur la colonne Produit (colonne E, feuille Suivi de Stock) de notre suivi de stock. En effet notre suivi de stock est sous la forme « Tableau » (Accueil/Style/Mettre sous la forme de tableau), ce qui nous facilite la création de formule.

Pour en savoir plus sur la fonction RECHERCHEV voir ici .

 

Total des entrées:

SOMME.SI.ENS (F$5:F6;E$5:E6;[@Produit];D$5:D6; »Entrée »)

La fonction SOMME.SI.ENS est une fonction qui permet de calculer la somme selon plusieurs critères. Elle est comme la fonction SOMME.SI avec la seule différence qu’elle gère plusieurs critères. Dans notre cas actuel:

o     le premier critère est [@Produit] qui représente le nom du produit et son champ de contrôle est E$5:E6. Ainsi la formule fait la somme uniquement pour le produit actif

o    Le second critère est « Entrée » et son champ est  D$5:D6, ainsi la formule fait la somme uniquement des entrées.

Total des sorties:

SOMME.SI.ENS(F$5:F7;E$5:E7;[@Produit];D$5:D7; »Sortie »)

Pour en savoir plus sur la fonction SOMME.SI.ENS() .

2.    Faire une mise en forme conditionnelle pour avoir des alertes:

·         Pour chaque enregistrement retrouver le stock minimum:

On rajoute une colonne Minimum (colonne H) dans laquelle on retrouvera le stock minimum pour chaque produit. Il suffit d’insérer la formule:

RECHERCHEV([@Produit];Datas!$B$4:$E$14;4;0)

·   Faire la mise en forme conditionnelle:

Sélectionner votre tableau de stock ( B5:H204), dans le ruban

Accueil/Style/Mise en forme conditionnelle/Nouvelle règle

N’oublions pas qu’en colonne G nous avons les soldes actualisés après chaque écriture et en H le stock minimum:


Et le résultat final:

Si vous avez des questions, des propositions, n’hésitez pas à utiliser l’espace commentaire pour le faire.

Le téléchargement du fichier se fait  dans Ressources/Téléchargements/Suivi stock_alerte sous la catégorie Gestion Stock avec Excel

 

One Response to Suivi stock: insertion alerte en cas de depassement de stock minimum

  1. fadhi 18 avril 2016 at 20 h 00 min #

    ref designiation Qantite stock stock alerte

    M000841 ABAISSE LANGUE BOIS / B100 3,00 …………

Laisser un commentaire