SUIVI DU STOCK

 

Contexte

Vous travaillez dans un magasin et vous devez gérer le stock des produits. Vous enregistrez chaque entrée et sortie de produit pour connaître les quantités en stock, les produits en rupture de stock, et la valeur totale de vos stocks. Le tableau de bord doit également calculer la rotation des stocks et la valeur totale en fonction des prix unitaires.

Tableaux de données

1. Tableau des Transactions (A1)
• A1 : Date
• B1 : Produit
• C1 : Type de Mouvement (Entrée, Sortie)
• D1 : Quantité
• E1 : Prix Unitaire
• F1 : Catégorie

Remplissez les lignes avec des données fictives, par exemple :

Date	Produit	Type de mouvement	Quantité	Prix ​​Unitaire	Catégorie
01/10/2024	Clavier	Entrée	50	20	Informatique
01/10/2024	Souris	Sortie	10	15	Informatique
10/02/2024	Papier	Entrée	100	0,5	Bureautique
10/03/2024	Stylo	Sortie	20	1	Bureautique
10/04/2024	Souris	Entrée	30	15	Informatique

 

2. Tableau Résumé des Stocks (S1)
• H2 : Produit
• I2 : Quantité totale d'entrée
• J2 : Quantité totale de sortie
• K2 : Quantité en stock
• Remplissez les produits à partir de la colonne H pour chaque produit unique.

Questions

1. Quantité Totale Entrée pour chaque Produit
   Utiliser SOMME.SI.ENS pour calculer la quantité totale entrée pour chaque produit et afficher le résultat dans la colonne I :

=SOMME.SI.ENS (D2:D20, B2:B20, H2, C2:C20, "Entrée")

2. Quantité Totale Sortie pour chaque Produit
   Utiliser SOMME.SI.ENS pour calculer la quantité totale sortie pour chaque produit et afficher le résultat dans la colonne J :

=SOMME.SI.ENS(D2:D20, B2:B20, H2, C2:C20, "Sortie")

3. Quantité en Stock pour chaque Produit
   Calculez la quantité en stock (Quantité Totale Entrée - Quantité Totale Sortie) et affichez le résultat dans la colonne K :

=I2 - J2

4. Valeur Totale du Stock
   Dans une cellule de résumé, utiliser SOMMEPROD pour calculer la valeur totale des produits en stock en multipliant la quantité en stock de chaque produit par son prix unitaire :

=SOMMEPROD(K2:K10, INDEX(D2:D20, EQUIV(H2:H10, B2:B20, 0)))

5. Produits en Rupture de Stock
   Utiliser SIet NB.SI pour afficher "En Stock" si la quantité en stock est supérieure à 0, et "Rupture" sinon. Placez cette formule dans la colonne L pour chaque produit.

=SI(K2 > 0, "En Stock", "Rupture")

6. Recherche du Prix Unitaire le Plus Récent pour chaque Produit
   Utiliser RECHERCHEV pour obtenir le prix unitaire le plus récent de chaque produit dans la colonne E et l'afficher dans la colonne M :

=RECHERCHEV(H2, FILTRE(B2:E20, (B2:B20=H2)), 4, FAUX)

7. Rotation des Stocks
   Calculez la rotation des stocks pour chaque produit, en divisant la quantité totale sortie par la quantité moyenne en stock. Tu peux calculer la quantité moyenne en utilisant la formule SOMME(D2:D20) / NB.SI(B2:B20, H2)et l'afficher dans une colonne dédiée.

=J2 / ((I2 + J2) / 2)

8. Suivi par catégorie
   Utiliser SOMME.SI.ENS pour calculer la quantité totale en stock par catégorie et afficher le résultat dans un autre tableau de résumé par catégorie.

Par exemple, pour la catégorie "Informatique", dans la cellule O2 :

=SOMME.SI.ENS(D2:D20, F2:F20, "Informatique", C2:C20, "Entrée") - SOMME.SI.ENS(D2:D20, F2:F20, "Informatique", C2:C20, "Sortie")

9. Alertes Automatiques
   Utiliser une mise en forme conditionnelle pour colorer en rouge les cellules de la colonne K si la quantité en stock tombe en dessous de 5, pour indiquer qu'un produit est presque en rupture de stock.

Objectif Final

Ces fonctions vous permettent de construire un tableau de bord complexe et dynamique pour la gestion des stocks, vous donnant une vue d'ensemble sur la quantité en stock, la rotation des stocks, la valeur totale en stock, ainsi que des alertes sur les produits. en rupture ou presque en rupture de stock. Vous pourrez ainsi prendre de meilleures décisions d'approvisionnement.