SUIVI DES ACHATS AVEC EXCEL

 

Contexte de l'exercice

Une entreprise de distribution souhaite suivre ses achats et ses fournisseurs de manière détaillée. Vous avez accès à des informations sur les articles achetés, les quantités, les prix unitaires, les fournisseurs et les dates d'achat. L'objectif est de créer un système qui permet de suivre les dépenses, les fournisseurs les plus sollicités, les articles les plus achetés, et de visualiser ces informations dans un tableau de bord.

Objectifs de l'exercice

1. Suivre les dépenses mensuelles et annuelles.
2. Identifiant des fournisseurs principaux et des articles les plus achetés.
3. Analyser les quantités achetées par article et par fournisseur.
4. Créer des tableaux croisés dynamiques pour des CV rapides.
5. Créer un tableau de bord interactif pour visualiser les principaux indicateurs d'achat.

Structure des données

Date d'achat	Article	Quantité	Prix ​​Unitaire (€)	Fournisseur	Montant total des achats (€)
01/01/2023	Papier	50	0,2	Fournisseur A
15/01/2023	Stylo	100	0,5	Fournisseur B
20/02/2023	Carton	30	1	Fournisseur A
10/02/2023	Papier	75	0,2	Fournisseur C
15/03/2023	Enveloppe	150	0,1	Fournisseur B
05/03/2023	Carton	20	1	Fournisseur A
10/04/2023	Stylo	200	0,5	Fournisseur C

 

Tâches à Réaliser

1. Calculer le Total Achat par Ligne

• Objectif : Calculer le coût total pour chaque ligne d'achat.
• Formule : Dans la colonne F2 , entre la formule suivante et copier-la vers le bas :

=C2 * D2

• Explication : Multipliez la quantité achetée par le prix unitaire.

2. Extraire le Mois et l'Année de l'Achat

• Objectif : Ajouter des colonnes pour le Mois et l’Année de chaque achat.
• Formule pour le Mois : En G2 , utiliser :

=MOIS(A2)

• Formule pour l'Année : En H2 , utiliser :

=ANNEE(A2)

• Copiez ces formules vers le bas pour obtenir les mois et les années pour chaque achat.

3. Créer un Tableau Croisé Dynamique pour Analyser les Dépenses

• Objectif : Utiliser un Tableau Croisé Dynamique pour résumer les dépenses par Fournisseur et par Article .
• Étapes :
• Sélectionne les données (colonnes A à H ) et insère un Tableau Croisé Dynamique .
• Placer les Fournisseurs en lignes et les Articles en colonnes.
• Placer Total Achat (€) dans la zone des Valeurs .
• Ajustez le Tableau Croisé Dynamique pour obtenir le total des achats par fournisseur et par article.

4. Analyser les Dépenses Mensuelles et Annuelles

• Objectif : Ajouter un deuxième Tableau Croisé Dynamique pour résumer les dépenses par Mois et Année .
• Étapes :
• Crée un nouveau Tableau Croisé Dynamique avec les données.
• Place Année en lignes et Mois en colonnes.
• Placer Total Achat (€) dans les Valeurs .
• Ce Tableau Croisé Dynamique vous donnera un aperçu des dépenses totales par mois et année.

5. Calculer le Coût Total d'Achat par Article

• Objectif : Utiliser une fonction SOMME.SI pour calculer le coût total de chaque article dans une cellule séparée.
• Formule : Pour calculer le coût total d'un article (par exemple, Papier ) dans une cellule spécifique :

=SOMME.SI(B:B; "Papier"; F:F)

• Répète cette formule pour chaque article que tu souhaites analyser.

6. Création du Tableau de Bord

• Objectif : Mettre en place un tableau de bord avec des Graphiques et des Segments pour rendre l'analyse interactive.
• Étapes :
• Graphique 1 : Crée un Graphique en Secteurs pour afficher la répartition des dépenses par fournisseur, basé sur le Tableau Croisé Dynamique des Fournisseurs .
• Graphique 2 : Utiliser un Graphique en Barres pour afficher le coût total d'achat par article.
• Graphique 3 : Insère un dépenses Graphique en Lignes pour visualiser les mensuelles ou annuelles.
• Segments : Ajoute des segments pour le Fournisseur , le Mois et l' Année pour filtrer les données dans les graphiques.
• Mets ces graphiques ensemble dans une feuille nommée Tableau de Bord , et organise-les de manière à ce qu'ils soient faciles à lire.

7. Ajouter des Indicateurs Clés (KPI)

• Objectif : Ajouter des cellules avec des indicateurs clés pour afficher les informations principales comme le Total Dépenses , Fournisseur Principal , Article le plus Acheté , etc.
• Utiliser des formules comme MAX et INDEX avec EQUIV pour extraire des informations importantes.

Pour compléter cet exercice, voici d'autres analyses et fonctionnalités avancées pour approfondir la gestion des achats dans Excel.

8. Analyser le Fournisseur le Plus Sollicité

• Objectif : Identifier le fournisseur avec le montant d'achats le plus élevé.
• Étapes :
• Utilisez un Tableau Croisé Dynamique pour voir le total des achats par fournisseur.
• Ajout d'une cellule pour afficher le fournisseur principal.
• Utiliser les fonctions INDEX et EQUIV pour extraire le nom du fournisseur en fonction du montant le plus élevé :

=INDEX(D:D; EQUIV(MAX(F2:F6); F2:F6; 0))

• Explication : Cette formule extrait le fournisseur correspondant au montant le plus élevé dans la colonne F .

9. Calculer le Prix Moyen d'Achat par Article

• Objectif : Connaître le prix moyen d'achat pour chaque article acheté.
• Formule :

=SOMME.SI(B:B; "Papier"; F:F) / SOMME.SI(B:B; "Papier"; C:C)

• Explication : La formule divise le coût total de l'article (par exemple, "Papier") par la quantité totale achetée pour cet article, ce qui donne le prix moyen d'achat.

10. Estimer les Coûts d'Achats Prévus

• Objectif : Estimer les coûts d'achats pour un trimestre ou une année suivante en fonction des données historiques.
• Étapes :
• Ajout d'une colonne pour le Taux de Croissance des achats d'un mois à l'autre.
• Calcule la moyenne de ces taux de croissance.
• Utilisez cette moyenne pour estimer les dépenses futures avec cette formule :

=Dernier Total Mensuel * (1 + Taux de Croissance Moyen)

• Explication : Cela projette les dépenses futures en fonction du taux de croissance moyen des mois précédents.

11. Suivre la Performance des Fournisseurs

• Objectif : Suivre et comparer les performances des fournisseurs en termes de coûts, qualité et délais.
• Étapes :
• Ajout d'une colonne pour Notes de Performance (par exemple, entre 1 et 5) pour chaque fournisseur, basée sur des critères comme la qualité et les délais.
• Utilisez un Tableau Croisé Dynamique pour calculer la note moyenne de chaque fournisseur.
• Insère un Graphique en Barres pour visualiser les performances des fournisseurs en fonction de leur note moyenne.

12. Générer un Rapport Mensuel Automatisé

• Objectif : Créer un rapport mensuel avec des curriculum vitae d'achats, les coûts par fournisseur, les articles les plus achetés, etc.
• Étapes :
• Ajout d'une feuille pour le Rapport Mensuel .
• Utiliser les fonctions SOMME.SI.ENS , INDEX , EQUIV , et MOIS pour extraire les données pertinentes pour le mois souhaité.
• Formatez le rapport avec des cellules dynamiques, par exemple :

= SOMME.SI.ENS(F:F; A:A; ">=01/01/2023"; A:A; "<=31/01/2023")

• Explication : Cette formule calcule le total des achats pour janvier 2023. Adaptez les dates pour chaque mois.

13. Visualiser les Variations Mensuelles des Coûts

• Objectif : Créer un Graphique en Courbes montrant les variations des coûts mensuels sur une période donnée.
• Étapes :
• Créé un Tableau Croisé Dynamique avec Année et Mois en axes et le Total d'Achat comme Valeur.
• Insère un Graphique en Courbes basé sur le Tableau Croisé pour voir la tendance des coûts mensuels.
• Personnalisez le graphique pour qu'il reflète clairement les photos et les baisses de coûts.

14. Automatiser le Tableau de Bord avec des Macros (Optionnel)

• Objectif : Automatiser l'actualisation des tableaux croisés dynamiques et des graphiques dans le tableau de bord.
• Étapes :
• Enregistrez une macro qui met à jour les tableaux croisés et rafraîchit les graphiques.
• Attribuez cette macro à un bouton dans le Tableau de Bord pour actualiser les données en un clic.
• Pour enregistrer une macro, va dans l'onglet Développeur , choisis Enregistrer une macro , et effectuez les actions souhaitées.

Résultat Final Attendu

• Un Tableau de Bord interactif avec :
• Un résumé des coûts par fournisseur et par article.
• Des indicateurs de performance pour les fournisseurs.
• Une projection des coûts futurs.
• Des graphiques pour visualiser les tendances et les variations mensuelles.
• Des boutons de macro pour actualiser les données et générer des rapports mensuels.

Bas du formulaire