Optimiser la Marge Brute grâce à la Data Analytics

Virginie LAFARGUE

https://www.linkedin.com/in/virginie-lafargue-523b7a7b/

SOMMAIRE

Bienvenue sur cette page dédiée à un projet de Data Analytics !

J'ai imaginé ce projet en m'inspirant d'une problématique rencontrée au cours de mon expérience professionnelle.

Grâce à mes nouvelles compétences en data analytics et à mon expertise métier, j'ai relevé le défi de répondre à une question complexe par une solution simple et instantanée, un défi passionnant !

N’hésitez pas à me contacter si vous souhaitez en savoir plus.

Portable : + 33 6 84 62 50 00

Email : [email protected]

📝Contexte

🔬 La société Innovitae, spécialisée dans le Diagnostic In Vitro, distribue des réactifs et des instruments, et propose des prestations de services aux laboratoires d'analyses médicales privés et publics.

🚀 En pleine expansion, Innovitae s'est fixée des objectifs ambitieux pour 2024 : réaliser une forte croissance de son chiffre d'affaires tout en maintenant un taux de marge brute global de 50 %.

⚠️ Si ce taux n'est pas atteint, l'entreprise devra rapidement en identifier les causes et mettre en place des actions correctives pour optimiser sa performance et protéger sa rentabilité à long terme.

❔Problématique

L’entreprise Innovitae fait face à deux problématiques :

Le suivi des taux de marge s'appuie sur de multiples fichiers Excel de ventes et d'achats, ce qui rend le traitement des données lourd, lent et sujet aux erreurs.
Les statistiques issues de l'ERP ne prennent pas en compte les prix d'achat spéciaux accordés par les fournisseurs.

⏳ Par conséquent, l'identification des causes de baisse du taux de marge brute prend trop de temps, retardant ainsi la mise en place d'actions correctives.

💡 La création d'un dashboard automatisé et interactif devient donc essentielle pour fiabiliser les données, simplifier les analyses et identifier rapidement les leviers d'optimisation.

🎯 Objectifs

Ce projet consiste à créer un rapport automatisé et interactif offrant :

🛡️ Un calcul fiable des coûts d’achat basé sur le numéro de lot ou de série, intégrant les prix d’achat spéciaux.
📊 Une vision claire et instantanée des marges brutes et des taux de marge brute, au niveau global ainsi que par chef de produit, famille de produits, type de clients…
⚠️ Une identification immédiate des causes de chute du taux de marge brute, pour lancer rapidement des actions correctives.

Ce dashboard est conçu pour les Product Managers (Service Marketing), chargés de vérifier l’atteinte des objectifs des produits sous leur responsabilité et de proposer des actions correctives en cas de dérive.

Il fournit également des visuels pertinents pour le Comité de Direction, illustrant la performance commerciale et opérationnelle de l’entreprise.

✅ Bénéfices

Les bénéfices attendus sont :

🌐 Une vision transversale et instantanée des performances des équipes, produits, clients et fournisseurs.
🚀 Une réactivité accrue pour définir et appliquer des actions correctives en cas de dégradation de la marge brute ou du taux de marge brute.
⏱️ Un gain de temps important pour l’analyse et l’exploitation des données, estimé à 3 jours par mois minimum dans le contexte de l’entreprise concernée
🛡️ Une sécurisation des calculs de marge brute garantissant l’intégrité des données et la fiabilité des calculs.

🔢 Kpi

KPI pour évaluer la marge brute :
- 💰 Marge brute en € = Chiffre d’affaires - Coût d’achat → représente le profit généré par les ventes après déduction du coût des marchandises vendues.
- ◔ Taux de marge brute en % = Marge brute / Chiffre d’affaires → exprime la marge en pourcentage du chiffre d’affaires et montre l’efficacité de l’entreprise à générer du profit à partir de ses ventes.
KPI pour identifier les segments (tels que la famille de produits, le type de clients…) qui ont le plus contribué à l’évolution du taux de marge brute entre deux périodes :
- 📊 Score de marge brute = (Taux de marge brute actuel – Taux de marge brute précédent) × (Marge brute € précédente / Marge brute € totale de la période précédente)
```
      *Période précédente = même période que celle analysée, mais lors de l’exercice fiscal*   

      *précédent*
```
Autres KPI :
- 💰 Chiffre d’Affaires en €
- 📈 Pourcentage du Chiffre d’Affaires Total
- 💶 Prix moyen de ventes
- 🛒 Prix moyen d’achat

Pour plus d’informations sur les concepts du taux de marge brute, marge brute ou score de marge brute, veuillez consulter l’Annexe (5) du Dashboard.

📊 Dashboard

Le dashboard est composé de 5 parties :

PARTIE	OBJECTIF
1. Performance Globale - Chiffres d'Affaires et Marge Brute	Présentation des indicateurs clés de chiffre d’affaires et de marge brute pour évaluer rapidement la performance globale de l’entreprise
2. Contributeurs à l'évolution du Taux de Marge Brute Global	Identification des principaux facteurs à l’origine de l’évolution du taux de marge brute global
3. Analyse de la Marge Brute par Segment	Présentation des résultats de marge brute par famille de produits, fournisseur et membre des équipes commerciales et marketing
4. Produits & Clients à l'origine de la Baisse du Taux de Marge	Identification des produits et clients à marge décroissante, en priorisant les plus impactants en vue d’un traitement prioritaire
5. Annexe	Rappel des concepts de base : marge brute, taux de marge brute, score de marge brute et les principaux facteurs influençant ce taux

>> Dans la vidéo ci-dessous, je vous présente ce Dashboard Automatisé et Interactif

https://www.loom.com/share/3fb06ebd575448dd9e9ec7c2c35a0256?sid=b6e3d405-33ca-4c30-84a1-989577b61a7c

>> Prêt à découvrir ce Dashboard par vous-même ?

Dashboard Marge Brute

🗃️ Données Brutes

Projet Profitabilité | DrawSQL

❗Ce projet utilise des données artificielles et un nom de société fictif, créés pour simuler un contexte rencontré lors d’une une expérience professionnelle antérieure. Les chiffres présentés sont entièrement fictifs et ne reflètent en aucun cas la réalité.

🛠️ Slack Technique

> OPTION 1

⬇️ Ingestion des données : Airbyte

🗄️ Transformations et modélisation des données au sein du Data Warehouse : BigQuery / Langage SQL

📊 Data Visualisation : Looker Studio

⚙️ Automatisation des transformations : dbt (à venir)

> OPTION 2

🟨 Power BI : ingestion, transformation, modélisation et visualisation des données (à venir)

💡Modélisation

A private Miro board

`</>` Requêtes SQL

Exemples de requêtes SQL utilisées pour ce projet :

Requête permettant de calculer le chiffre d’affaires, le coût total et la marge brute par commande, en prenant en compte les prix d’achat spécifiques associés aux numéros de lot ou de série :

WITH sales_with_lot_serial_number AS (     --- Clean and add new colum to merge serial and lot number in order to be able to join sales and purchases table properly
  SELECT
    order_id,
    invoice_date,
    product_id,
    customer_id,
    lot_number,
    serial_number,
    CONCAT(IFNULL(lot_number, '0'), '-', IFNULL(serial_number, '0')) AS lot_serial_number,
    quantity,
    sales_price
  FROM profitability-analysis-459007.gross_margin_project.sales
  WHERE order_id IS NOT NULL
    AND product_id IS NOT NULL
    AND invoice_date IS NOT NULL
    AND customer_id IS NOT NULL
)

SELECT
    s.order_id,
    invoice_date,
    s.product_id,
    h.product_name,
    s.lot_number,
    s.serial_number,
    s.lot_serial_number,
    h.product_family,
    h.product_category,
    s.customer_id,
    c.customer_name,
    c.customer_type,
    supplier_name,
    ca.salesrep_initials,
    b.salesrep_fullname,
    pa.product_manager_initials,
    product_manager_name AS product_manager_fullname,
    quantity,
    sales_price,
    purchase_price,
    quantity * sales_price AS revenue,
    quantity * purchase_price AS cost,
   (quantity * sales_price) - (quantity * purchase_price) AS gross_margin_euros,
    IF(quantity * sales_price = 0,NULL,((quantity * sales_price) - (quantity * purchase_price)) / (quantity * sales_price)) AS gross_margin_rate,
    margin_budget AS product_family_margin_rate_budget,
    budget AS salesrep_yearly_budget,
    product_manager_yearly_budget
FROM sales_with_lot_serial_number s
LEFT JOIN  profitability-analysis-459007.gross_margin_project.purchases_without_duplicate p
    ON s.product_id = p.product_id 
    AND s.lot_serial_number  = p.lot_serial_number
LEFT JOIN profitability-analysis-459007.gross_margin_project.customer c
  ON s.customer_id = c.customer_id
LEFT JOIN profitability-analysis-459007.gross_margin_project.customer_attribution ca
  ON s.customer_id = ca.customer_id
LEFT JOIN profitability-analysis-459007.gross_margin_project.product_attribution pa
  ON s.product_id = pa.product_id
LEFT JOIN profitability-analysis-459007.gross_margin_project.product_hierarchy h
  ON s.product_id = h.product_id
LEFT JOIN profitability-analysis-459007.gross_margin_project.salesrep_yearly_budget b
  ON ca.salesrep_initials= b.salesrep_initials
LEFT JOIN profitability-analysis-459007.gross_margin_project.product_manager_yearly_budget bp
  ON pa.product_manager_initials = bp.product_manager_initials

Requête pour calculer, par Product Manager ou au niveau global, les YTD du chiffre d’affaires, de la marge brute et du taux de marge brute, leur évolution, leur réalisé vs le budget, et le score de marge brute :

-- Budgeted/Target gross_margin_rate considered for product_manager = 0.5

WITH ytd_calc AS (
  
  SELECT
    month,
    year,
    product_manager_initials,
    product_manager_fullname,
    product_manager_budget,
    revenue,
    cost,
    gross_margin_euros,
    previous_revenue,
    previous_cost,
    previous_gross_margin_euros,
    two_years_before_revenue,
    two_years_before_cost,
    two_years_before_gross_margin_euros,
    SUM(revenue) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_revenue,
    SUM(cost) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_cost,
    SUM(gross_margin_euros) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_gross_margin_euros,
    SUM(product_manager_budget) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_product_manager_budget,
    SUM(previous_revenue) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_previous_revenue,
    SUM(previous_cost) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_previous_cost,
    SUM(previous_gross_margin_euros) OVER (PARTITION BY product_manager_fullname, year ORDER BY month) AS ytd_previous_gross_margin_euros,
    SUM(previous_gross_margin_euros) OVER (PARTITION BY year ORDER BY month) AS ytd_total_previous_gross_margin_euros
  FROM profitability-analysis-459007.gross_margin_project.monthly_performance_by_product_manager

  UNION ALL

  SELECT
    month,
    year,
    'ALL' AS product_manager_initials,
    'ALL' AS product_manager_fullname,
    SUM(product_manager_budget) AS product_manager_budget,
    SUM(revenue) AS revenue,
    SUM(cost) AS cost,
    SUM(gross_margin_euros) AS gross_margin_euros,
    SUM(previous_revenue) AS previous_revenue,
    SUM(previous_cost) AS previous_cost,
    SUM(previous_gross_margin_euros) AS previous_gross_margin_euros,
    SUM(two_years_before_revenue) AS two_years_before_revenue,
    SUM(two_years_before_cost) AS two_years_before_cost,
    SUM(two_years_before_gross_margin_euros) AS two_years_before_gross_margin_euros,
    SUM(SUM(revenue)) OVER (PARTITION BY year ORDER BY month) AS ytd_revenue,
    SUM(SUM(cost)) OVER (PARTITION BY year ORDER BY month) AS ytd_cost,
    SUM(SUM(gross_margin_euros)) OVER (PARTITION BY year ORDER BY month) AS ytd_gross_margin_euros,
    SUM(SUM(product_manager_budget)) OVER (PARTITION BY year ORDER BY month) AS ytd_product_manager_budget,
    SUM(SUM(previous_revenue)) OVER (PARTITION BY year ORDER BY month) AS ytd_previous_revenue,
    SUM(SUM(previous_cost)) OVER (PARTITION BY year ORDER BY month) AS ytd_previous_cost,
    SUM(SUM(previous_gross_margin_euros)) OVER (PARTITION BY year ORDER BY month) AS ytd_previous_gross_margin_euros,
    MAX(SUM(previous_gross_margin_euros)) OVER (PARTITION BY year ORDER BY month)  AS ytd_total_previous_gross_margin_euros
  FROM profitability-analysis-459007.gross_margin_project.monthly_performance_by_product_manager
  GROUP BY month, year

)

SELECT
  *,
  SAFE_DIVIDE(gross_margin_euros, revenue) AS gross_margin_rate,
  SAFE_DIVIDE(previous_gross_margin_euros, previous_revenue) AS previous_gross_margin_rate,
  SAFE_DIVIDE(two_years_before_gross_margin_euros, two_years_before_revenue) AS two_years_before_gross_margin_rate,
  SAFE_DIVIDE(ytd_gross_margin_euros, ytd_revenue) AS ytd_gross_margin_rate,
  SAFE_DIVIDE(ytd_previous_gross_margin_euros, ytd_previous_revenue) AS ytd_previous_gross_margin_rate,
  SAFE_DIVIDE(ytd_revenue, ytd_product_manager_budget) AS ytd_actual_revenue_to_budget,
  SAFE_DIVIDE(ytd_revenue - ytd_previous_revenue, ytd_previous_revenue) AS ytd_revenue_evolution,
  SAFE_DIVIDE(ytd_gross_margin_euros, ytd_revenue) - SAFE_DIVIDE(ytd_previous_gross_margin_euros, ytd_previous_revenue) AS ytd_gross_margin_rate_evolution,
  SAFE_DIVIDE(ytd_gross_margin_euros, ytd_revenue) - 0.5 AS ytd_gross_margin_rate_to_budget,
  SAFE_DIVIDE(ytd_gross_margin_euros - ytd_previous_gross_margin_euros, ytd_previous_gross_margin_euros) AS ytd_gross_margin_euros_evolution_percent,
  ytd_gross_margin_euros - ytd_previous_gross_margin_euros AS ytd_gross_margin_euros_evolution,
  (SAFE_DIVIDE(ytd_gross_margin_euros, ytd_revenue) - SAFE_DIVIDE(ytd_previous_gross_margin_euros, ytd_previous_revenue)) * (SAFE_DIVIDE(ytd_previous_gross_margin_euros,     ytd_total_previous_gross_margin_euros)) AS gross_margin_score

FROM ytd_calc
ORDER BY year, month