Suivi des ventes d'un magasin sous Excel : chiffre d'affaires, marge et analyse ABC

Votre caisse affiche un chiffre d'affaires du jour, et c'est très bien. Mais elle répond mal à la question qui compte : sur quoi gagnez-vous réellement de l'argent ? Un produit qui se vend beaucoup avec une petite marge peut rapporter moins qu'un article discret mais bien margé. Pour le savoir, il faut croiser les ventes avec les prix d'achat.

Un tableau Excel suffit pour une petite boutique, un point de vente ou une activité en ligne. Voici la méthode : un journal de ventes, quelques formules de marge, une analyse ABC pour identifier vos produits clés, et les bons réflexes pour lire le résultat.

Le journal des ventes : une ligne, une vente

Chaque ligne contient : la date, le produit, la quantité, la remise éventuelle, le vendeur et le mode de paiement. Les prix ne se tapent pas à la main : ils viennent d'une feuille « Produits » qui liste, pour chaque référence, le nom, la catégorie, le prix d'achat et le prix de vente.

Avec le produit en B2 et une table de produits en feuille Produits (nom en A, prix d'achat en C, prix de vente en D) :

=RECHERCHEV(B2;Produits!A:D;4;FAUX) pour le prix de vente,

=RECHERCHEV(B2;Produits!A:D;3;FAUX) pour le prix d'achat.

Ensuite, le calcul se fait en cascade, avec la quantité en C2, la remise en pourcentage en E2, le prix de vente en G2 et le prix d'achat en H2 :

• Chiffre d'affaires : =C2*G2*(1-E2)
• Coût de revient : =C2*H2
• Bénéfice : chiffre d'affaires moins coût de revient

Marge, taux de marge, taux de marque : ne pas les confondre

Prenons un article acheté 12 € et vendu 20 € (hors taxes). Le bénéfice unitaire est de 8 €.

Les deux sont corrects, mais ils ne disent pas la même chose. Choisissez-en un et utilisez-le toujours, sinon vous comparerez des pommes et des poires. Un commerçant qui dit « je fais 50 % » peut parler de l'un comme de l'autre : vérifiez toujours par rapport à quelle base.

Les synthèses qui servent vraiment

Une fois le journal rempli, quelques tableaux de synthèse répondent à l'essentiel :

• Par jour de la semaine : SOMME.SI sur une colonne =JOURSEM(A2;2). Vous verrez peut-être que le mardi ne vaut pas la peine d'une ouverture longue.
• Par mois : SOMME.SI.ENS avec les bornes du mois. Parfait pour mesurer la saisonnalité.
• Par vendeur : SOMME.SI sur la colonne vendeur, utile pour des primes ou pour repérer qui vend quoi.
• Top 10 produits : GRANDE.VALEUR(plage;k) avec k de 1 à 10, associé à INDEX et EQUIV pour retrouver le nom.

L'analyse ABC : trouver les produits qui font votre chiffre

Le principe repose sur l'idée que peu de produits génèrent l'essentiel du chiffre d'affaires. On classe les produits du plus vendu au moins vendu, on calcule la part de chacun puis la part cumulée. Ensuite :

• groupe A : les produits qui réalisent les premiers 80 % du chiffre d'affaires ;
• groupe B : les 15 % suivants ;
• groupe C : le reste.

Exemple chiffré. Une boutique de 5 produits fait 10 000 € de chiffre d'affaires : le produit 1 en fait 5 000 (50 %), le 2 en fait 2 000 (cumul 70 %), le 3 en fait 1 500 (cumul 85 %), le 4 en fait 900 (cumul 94 %), le 5 en fait 600 (cumul 100 %). Les deux premiers sont en A, le troisième en B, les deux autres en C.

Que faire de cette information ? Ne jamais être en rupture sur les produits A, surveiller les B, et se demander pour les C s'ils méritent la place qu'ils occupent. Dans Excel, la part cumulée se calcule avec une somme progressive : =SOMME($D$2:D2)/SOMME($D$2:$D$50) une fois les produits triés par chiffre d'affaires décroissant.

Erreurs fréquentes

• Changer le prix dans la liste de produits et fausser tout l'historique : si le prix change vraiment, créez une nouvelle référence plutôt que de modifier l'ancienne.
• Mélanger prix hors taxes et toutes taxes comprises dans la même colonne.
• Oublier de compter les remises : le chiffre d'affaires réel est après remise.
• Ne regarder que le chiffre d'affaires et jamais la marge.

Un modèle de suivi des ventes prêt à l'emploi

Si vous souhaitez gagner du temps, le modèle de suivi des ventes d'un magasin est un fichier payant qui contient tout ce qui précède. La feuille « Référentiels » regroupe les produits (référence, nom, catégorie, prix d'achat et de vente), les vendeurs, les modes de paiement et les seuils des groupes ABC. La feuille « Ventes » accepte jusqu'à 3 000 lignes et calcule prix, chiffre d'affaires, coût de revient, bénéfice et taux de marque. La feuille « Analyse ABC » classe les produits avec part, part cumulée et groupe A, B ou C.

La « Synthèse » présente les totaux, les résultats par jour de la semaine, par mois, par vendeur et par mode de paiement, le top 10 des produits et quatre graphiques. Le fichier gère 200 produits, fonctionne sans macros sous Excel 2007 et versions ultérieures ou LibreOffice, et se livre avec un tableau vierge et un exemple rempli. Ce n'est pas un logiciel de comptabilité. Notez que son « taux de marque » est calculé sur le coût de revient : c'est ce qu'on appelle plus souvent taux de marge, et la marge moyenne en synthèse est calculée sur le chiffre d'affaires.

FAQ

Faut-il saisir chaque ticket de caisse ?

Pas forcément. Si votre caisse peut exporter les ventes du jour, vous pouvez saisir par produit et par jour. L'important est que la quantité vendue et le produit soient justes.

Comment gérer les retours de marchandises ?

Dans un tableau que vous construisez vous-même, saisissez une ligne avec une quantité négative à la date du retour : le chiffre d'affaires et le bénéfice se corrigent d'eux-mêmes. Avec un fichier prêt à l'emploi, vérifiez d'abord quelles saisies il accepte.

À quelle fréquence faut-il refaire l'analyse ABC ?

Une fois par trimestre est un bon rythme. Plus souvent, vous suivrez des variations sans importance ; moins souvent, vous raterez les évolutions de gamme.

Que faire quand le fichier devient trop lourd ?

Enregistrez une copie à la fin de la période, videz le journal de ventes et recommencez avec les mêmes références. L'archive conserve l'historique.