Exercices corrigés autour des principaux outils Excel pour l’analyse du portefeuille client

L’analyse du portefeuille client est une pratique essentielle pour toute entreprise cherchant à maximiser la valeur de ses clients tout en optimisant ses ressources. Excel, avec ses puissantes fonctions et outils de visualisation, permet de mener cette analyse de manière efficace et structurée. Grâce à des outils comme la segmentation RFM (Récence, Fréquence, Montant), le calcul de la Valeur Vie Client (CLV), les tableaux croisés dynamiques, et des fonctions avancées telles que FORECAST() pour la prévision, Excel offre une plateforme flexible pour analyser la rentabilité et le comportement d’achat des clients. Ces outils facilitent la segmentation, l’identification des clients les plus rentables, la prévision des revenus futurs, et l’optimisation des stratégies de fidélisation.

Six Modèles Excel pour l’Analyse du Portefeuille Client

Exercice 1 : Segmentation RFM avec des fonctions Excel

Contexte :
Vous avez une base de données clients avec des informations sur le nombre de jours depuis leur dernier achat, le nombre d’achats et le montant total dépensé. Vous devez effectuer une segmentation RFM (Récence, Fréquence, Montant) à l’aide d’Excel.

Objectif :

• Calculer les scores Récence, Fréquence, et Montant à l’aide de la fonction IF().
• Utiliser VLOOKUP() ou XLOOKUP() pour rechercher les valeurs dans les tableaux de correspondance.
• Colorer les scores RFM automatiquement avec la mise en forme conditionnelle.

Étapes de résolution :

1. Récence (R) : Attribuer un score basé sur le nombre de jours depuis le dernier achat :

• 5 (0 à 30 jours)
• 4 (31 à 60 jours)
• 3 (61 à 90 jours)
• 2 (91 à 120 jours)
• 1 (121 jours et plus)

Formule Excel pour Récence:
=SI([Dernier achat]<=30, 5, SI([Dernier achat]<=60, 4, SI([Dernier achat]<=90, 3, SI([Dernier achat]<=120, 2, 1))))

2. Fréquence (F) : Attribuer un score basé sur le nombre total d’achats :

• 5 (10 achats ou plus)
• 4 (6 à 9 achats)
• 3 (4 à 5 achats)
• 2 (2 à 3 achats)
• 1 (1 achat)

Formule Excel pour Fréquence:
=SI([Nombre d'achats]>=10, 5, SI([Nombre d'achats]>=6, 4, SI([Nombre d'achats]>=4, 3, SI([Nombre d'achats]>=2, 2, 1))))

3. Montant (M) : Attribuer un score basé sur le montant total dépensé :

• 5 (Plus de 1500 €)
• 4 (1001 à 1500 €)
• 3 (501 à 1000 €)
• 2 (201 à 500 €)
• 1 (Moins de 200 €)

Formule Excel pour Montant:
=SI([Montant total]>=1500, 5, SI([Montant total]>=1000, 4, SI([Montant total]>=500, 3, SI([Montant total]>=200, 2, 1))))

4. Mise en forme conditionnelle : Utiliser la mise en forme conditionnelle pour colorer les scores RFM automatiquement. Par exemple, les cellules avec un score de 5 seront colorées en vert, tandis que celles avec un score de 1 seront colorées en rouge.

Exercice 2 : Calcul de la Valeur Vie Client (CLV)

Contexte :
Vous avez les informations suivantes pour trois clients concernant leurs achats sur les deux dernières années.

Objectif :
Calculer la CLV pour chaque client en utilisant la formule simple de CLV et un taux de churn de 20 %.

Formule Excel pour CLV:
= (Valeur_moyenne * Nombre_achats * Durée_relation)

Étapes de résolution :

1. Calculer la CLV de base sans le taux de churn.

Client A:
  CLV = 200 * 5 * 2
  CLV = 2000 €

Client B:
  CLV = 150 * 10 * 3
  CLV = 4500 €

Client C:
  CLV = 300 * 3 * 4
  CLV = 3600 €

2. Ajuster la CLV en tenant compte du taux de churn de 20 %.

Formule ajustée:
CLV ajustée = CLV / (1 - Taux de churn)

Client A:
  CLV ajustée = 2000 / (1 - 0.20)
  CLV ajustée = 2000 / 0.80
  CLV ajustée = 2500 €

Client B:
  CLV ajustée = 4500 / 0.80
  CLV ajustée = 5625 €

Client C:
  CLV ajustée = 3600 / 0.80
  CLV ajustée = 4500 €

Exercice 3 : Analyse de la marge par client avec SUMPRODUCT()

Contexte :
Vous souhaitez analyser la marge brute générée par chaque client en fonction de leurs achats et des coûts associés. Voici les données :

Objectif :
Calculer la marge brute totale et la marge pondérée pour chaque client.

Formule Excel pour marge brute:
= Montant total - Coût total

Étapes de résolution :

1. Calculer la marge brute de chaque client.

Client A:
  Marge brute = 3000 - 1800 = 1200 €

Client B:
  Marge brute = 5000 - 2500 = 2500 €

Client C:
  Marge brute = 2000 - 1000 = 1000 €

2. Utiliser la fonction SUMPRODUCT() pour pondérer la marge brute par le nombre d’achats (si fourni) ou d’autres critères si applicable.

Formule Excel:
=SUMPRODUCT(Montant_total, Coût_total)

Exercice 4 : Calcul du taux de rétention et churn avec COUNTIFS()

Contexte :
Vous devez analyser le taux de rétention des clients au fil du temps. Voici les informations sur l’activité des clients au cours des trois dernières années :

Objectif :
Calculer le taux de rétention annuel et visualiser le taux de churn avec un graphique à barres.

Formule Excel pour taux de rétention:
=COUNTIFS(Année1:Année3, "Actif") / TOTAL_CLIENTS

1. Compter le nombre de clients actifs chaque année avec COUNTIFS().

Année 1:
  Clients actifs = 2 (Client A et C)
Année 2:
  Clients actifs = 2 (Client A et B)
Année 3:
  Clients actifs = 1 (Client B)

2. Calculer le taux de churn pour chaque année avec la formule suivante.

Taux de churn = 1 - (Clients actifs année N / Clients actifs année N-1)

Visualiser ces taux avec un graphique à barres.

Exercice 5 : Analyse du Coût d’Acquisition Client (CAC) (Suite)

1. Calcul du CAC pour chaque cas :

CAC = Dépenses marketing / Nombre de clients acquis

• Pour les dépenses de 10 000 € et 50 clients acquis :

CAC = 10 000 / 50
CAC = 200 €

• Pour les dépenses de 15 000 € et 75 clients acquis :

CAC = 15 000 / 75
CAC = 200 €

Les deux campagnes ont un CAC de 200 €, ce qui est le coût maximum acceptable.

2. Utilisation de la fonction GOAL SEEK :

L’entreprise veut savoir combien elle peut dépenser si elle souhaite acquérir 100 clients tout en maintenant un CAC de 200 €. Pour ce faire, vous pouvez utiliser l’outil Valeur cible (GOAL SEEK) dans Excel pour ajuster les dépenses marketing afin d’atteindre cet objectif.

Étapes :

• Allez dans l’onglet Données.
• Cliquez sur Analyse des scénarios, puis Valeur cible.
• Définissez la cellule du CAC.
• Fixez la valeur du CAC à 200.
• Choisissez la cellule des dépenses marketing pour modifier cette valeur.

Résultat attendu avec GOAL SEEK :

Dépenses marketing = CAC * Nombre de clients
Dépenses marketing = 200 * 100
Dépenses marketing = 20 000 €

L’entreprise peut dépenser 20 000 € pour acquérir 100 clients tout en respectant un CAC de 200 €.

Exercice 6 : Création d’un tableau de bord client

Contexte :
Vous souhaitez visualiser les performances de votre portefeuille client en créant un tableau de bord dans Excel. Voici les données de base sur les clients :

Objectif :

• Créer un tableau de bord interactif pour suivre les performances des clients.
• Utiliser des Slicers (segments) pour filtrer les données par clients.
• Visualiser les tendances d’achat avec des sparklines.
• Créer un graphique combiné pour afficher à la fois le nombre d’achats et le montant total.

Étapes de résolution :

Création d’un tableau de bord :

• Créez un tableau croisé dynamique à partir des données clients.
• Ajoutez les slicers (segments) pour filtrer par Nombre d’achats, Montant total, ou CLV.
• Utilisez les sparklines pour visualiser les tendances d’achat directement dans les cellules. Formule Excel pour Sparklines :
• Sélectionnez les cellules pour le nombre d’achats et le montant total, puis allez dans Insertion > Sparklines et choisissez Ligne ou Colonne pour afficher la tendance des dépenses par client.

Création d’un graphique combiné :

• Sélectionnez les colonnes Nombre d’achats et Montant total (€).
• Allez dans Insertion > Graphiques et choisissez un Graphique combiné.
• Sur le graphique, affichez le Nombre d’achats en barres et le Montant total en lignes pour une visualisation claire des performances par client. Résultat attendu :
• Vous obtenez un tableau de bord interactif qui vous permet de suivre en temps réel les performances de votre portefeuille client, avec la possibilité de filtrer par segment de clients grâce aux slicers.

Exercice 7 : Prévision des revenus futurs à l’aide de FORECAST()

Contexte :
Votre entreprise souhaite estimer les revenus futurs d’un client en fonction de ses dépenses passées. Voici les données des montants dépensés par année pour un client :

Objectif :
Utiliser la fonction FORECAST() d’Excel pour prévoir le montant que le client pourrait dépenser en 2023.

Étapes de résolution :

1. Utilisation de la fonction FORECAST() : La fonction FORECAST() est utilisée pour prévoir une valeur future en se basant sur les tendances des années précédentes.

   Formule Excel pour la prévision :
   =FORECAST(2023, [Montant dépensé], [Années])

2. Prévision pour 2023 :

• En utilisant les montants dépensés de 2019 à 2022, Excel peut prédire la dépense pour 2023.

   =FORECAST(2023, B2:B5, A2:A5)

Résultat :

Excel prédit que le client dépensera environ 1200 € en 2023 en fonction des tendances des années précédentes.

Exercice 8 : Analyse des cohortes de clients avec des tableaux croisés dynamiques

Contexte :
Vous souhaitez suivre les performances des groupes de clients (cohortes) acquis au fil du temps, afin d’analyser leur rétention et leur comportement d’achat.

Objectif :
Analyser la rétention des cohortes et suivre l’évolution des achats au fil des ans.

Étapes de résolution :

Créer un tableau croisé dynamique à partir des données des cohortes :

• Placez l’année d’acquisition en lignes.
• Placez les achats par année en colonnes pour chaque client.

Analyser la rétention :

• Visualisez le nombre de clients actifs pour chaque année de cohorte.
• Utilisez des Graphiques à barres pour suivre les tendances de rétention et d’achats.

Résultat attendu :

• Le tableau croisé dynamique montre que Client A, acquis en 2020, reste actif avec une baisse progressive des achats chaque année, tandis que Client B a un taux de rétention plus faible en année 2.

Ces exercices corrigés couvrent l’ensemble des principaux outils Excel pour réaliser une analyse approfondie du portefeuille client, incluant la segmentation RFM, le calcul de la CLV, l’analyse des marges, et les prévisions de revenus. Ces outils aident à mieux comprendre les comportements clients et à optimiser les stratégies de gestion et de fidélisation des clients.

Autres articles

Créer une Carte de Positionnement Concurrentiel ➤...

Une carte de positionnement concurrentiel est un outil visuel qui...

Read more

Étude de Marché : Méthodes et Modèles...

L'étude de marché est une démarche essentielle pour toute entreprise...

Read more

La Revue de Marché Marketing : Modèle...

Cet article explore les éléments clés d’une revue de marché...

Read more