Économies unitaires (LTV/CAC et cohortes)
Quand l'utiliser
Utilisez cette compétence lorsque vous avez besoin de savoir si un client vaut plus que ce qu'il coûte à acquérir : CAC, contribution de marge brute, rétention, LTV, le ratio LTV/CAC, et le délai de récupération du CAC en mois. Idéal pour les entreprises SaaS et par abonnement, les revues de conseil d'administration et les audits où un tableau de rétention par cohorte soutient les ratios principaux. La compétence construit un fichier .xlsx téléchargeable dans l'application Claude avec openpyxl ; elle n'utilise aucun module complémentaire Excel ni connexion Microsoft 365.
Ce qu'elle construit
Un classeur avec six onglets :
- Couverture : titre, légende de couleurs, sélecteur de scénario, ratios LTV/CAC et délai de récupération principaux.
- Hypothèses : saisies en bleu (dépenses S&M, nouveaux clients, ARPU, % de marge brute, attrition, taux d'actualisation, multiplicateurs de scénario).
- Cohortes : une grille de rétention, les cohortes en lignes et les mois en colonnes, avec le % retenu et le revenu par cohorte.
- Économies unitaires : CAC, ARPU, contribution par client, délai de récupération.
- LTV-CAC : LTV par formule fermée et par contribution actualisée des cohortes, plus le ratio.
- Vérifications : cohérence, monotonie de la rétention et indicateurs de vraisemblance du délai de récupération.
Processus de construction
- Créez le classeur et les six onglets dans l'ordre ci-dessus.
- Dans Hypothèses, disposez toutes les entrées en bleu avec leurs unités ; ajoutez une cellule de scénario (1=Base, 2=Optimiste, 3=Pessimiste) nommée
scn. - Construisez le CAC et la contribution par client dans Économies unitaires à partir des dépenses et des entrées clients.
- Construisez la grille de cohortes : une base mois-0 de 100% (ou clients de départ) diminuant par la rétention au fil des mois.
- Ajoutez une ligne de revenu et de contribution sous la grille de rétention pour chaque cohorte.
- Construisez la LTV de deux manières dans LTV-CAC : forme fermée et somme actualisée des cohortes ; calculez le ratio et le délai de récupération.
- Construisez l'onglet Vérifications avec des tests de cohérence, de monotonie et de délai de récupération.
- Recalculez le classeur sans interface (LibreOffice) pour que toutes les formules soient exécutées.
- Vérifiez qu'il n'y a aucune erreur de formule (#REF!, #DIV/0!, #VALEUR!, #NOM?) ; corrigez et recalculez en boucle, puis livrez.
Spécification onglet par onglet
Couverture
- Cellule de titre, ligne d'objectif du modèle et date de construction.
- Bloc de légende de couleurs : un carré bleu étiqueté "Entrée", noir étiqueté "Formule", vert étiqueté "Lien inter-onglets".
- Écho du scénario :
=CHOISIR(scn;"Base";"Optimiste";"Pessimiste")récupérant le scénario actif depuis Hypothèses. - Sorties principales en vert avec liens inter-onglets : LTV/CAC
=LTV-CAC!<cellule_ratio>, délai de récupération du CAC en mois, et LTV actualisée. - Une note de lecture unique indiquant que le classeur se recalcule en direct dans Excel et a été vérifié sans erreur à la construction.
Hypothèses
- B2 Sélecteur de scénario (bleu, 1/2/3), nommé
scn. - Dépenses S&M par période B5 ; nouveaux clients acquis B6.
- ARPU par mois B7 ; % de marge brute B8.
- % d'attrition mensuel B9 (donc rétention mensuelle
=1-B9) ; taux d'actualisation par mois B10. - Multiplicateurs de scénario (Base/Optimiste/Pessimiste) pour l'attrition et l'ARPU : attrition active
=CHOISIR(scn;...)en B12, ARPU actif=CHOISIR(scn;...)en B13.
Économies unitaires
- CAC B3
=Hypothèses!B5/Hypothèses!B6. - ARPU effectif B4
=Hypothèses!B7*Hypothèses!B13. - Bénéfice brut par client-mois B5
=B4*Hypothèses!B8. - Rétention mensuelle B6
=1-Hypothèses!B9*Hypothèses!B12. - Délai de récupération du CAC (mois) B7
=CAC/bénéfice brut par client-mois=B3/B5. Si vous préférez un délai de récupération basé sur la cohorte, calculez le bénéfice brut cumulé par mois de cohorte jusqu'à ce qu'il dépasse le CAC et indiquez l'index de ce mois. - Durée de vie moyenne du client (mois) B8
=1/(Hypothèses!B9*Hypothèses!B12).
Cohortes
- Ligne 3 en-têtes de mois 0,1,2,...,N dans les colonnes.
- Colonne A étiquettes de cohorte (Cohorte 1, Cohorte 2, ...).
- % retenu : cellule mois-0 = 1 (100%) ; chaque mois suivant
=cellule_mois_précédent*'Économies unitaires'!$B$6(application de la rétention mensuelle). Une formule cohérente sur toute la ligne. - Clients actifs par cohorte :
=%retenu * Hypothèses!$B$6. - Revenu par mois-cohorte :
=clients actifs * 'Économies unitaires'!$B$4. - Contribution par mois-cohorte :
=revenu * Hypothèses!$B$8.
LTV-CAC
- LTV forme fermée B3
='Économies unitaires'!B4*Hypothèses!B8/('Économies unitaires'!B6 durée de vie d'attrition)i.e.=ARPU*%MG / attrition; dans les cellules='Économies unitaires'!B4*Hypothèses!B8/(Hypothèses!B9*Hypothèses!B12). - LTV actualisée-cohorte B4 : somme sur les mois de la contribution par client actualisée,
=SOMMEPROD(ligne_contribution_par_client; 1/(1+Hypothèses!B10)^index_mois)en utilisant la ligne de la cohorte mois-0 divisée par les clients de départ pour obtenir une valeur par client. - LTV/CAC B5
=B4/'Économies unitaires'!B3(utilisez la LTV actualisée comme référence). - Écho du délai de récupération B6
='Économies unitaires'!B7.
Vérifications
- Cohérence :
=SI(ABS(LTV_forme_fermee-LTV_actualisée)/LTV_actualisée<=Hypothèses!tol;"OK";"ÉCHEC"). - Rétention monotone au sein de chaque cohorte :
=SI(SOMMEPROD(--(cellules_mois_actuel>cellules_mois_précédent))=0;"OK";"ÉCHEC")pour qu'aucun mois ne dépasse le précédent. - Délai de récupération positif et dans une limite définie (entrée bleue).
- LTV/CAC calculée à partir du même ARPU, de la même marge et de la même attrition que ceux utilisés dans la grille de cohortes.
Conventions de formule et de formatage
- Police bleue pour les entrées (Hypothèses uniquement). Noire pour les formules internes à l'onglet. Verte pour les liens inter-onglets.
- Aucun nombre codé en dur dans les formules ; les constantes se trouvent dans Hypothèses. Utilisez des références absolues vers les pilotes de rétention et d'ARPU afin que les lignes de cohortes se remplissent proprement.
- Une seule formule cohérente par ligne de cohorte pour qu'elle se recopie sans modifications sur les mois.
- Rétention et marges en pourcentage ; CAC, LTV, ARPU en devise ; délai de récupération et durée de vie en mois avec une décimale.
- Nommez
scn,tol, CAC et la cellule de rétention pour des formules lisibles dans Vérifications et LTV.
Vérifications
- LTV/CAC calculée de manière cohérente (forme fermée et actualisée dans la tolérance).
- Délai de récupération du CAC rapporté en mois et positif.
- Rétention monotone non croissante au sein de chaque cohorte (aucun mois ne dépasse le précédent).
- Marges et rétention entre 0 et 1 ; CAC et LTV non négatifs.
Recalculer et vérifier
Après l'écriture, recalculez sans interface avec LibreOffice pour que le texte des formules openpyxl devienne des valeurs calculées. Parcourez chaque feuille à la recherche de #REF!, #DIV/0!, #VALEUR!, #NOM?. Notez que la LTV et le délai de récupération divisent par l'attrition, donc protégez contre l'attrition = 0 produisant #DIV/0!. Corrigez toute formule erronée ou plage nommée manquante et recalculez à nouveau en boucle. Livrez uniquement lorsque l'onglet Vérifications affiche tous "OK" et qu'aucune chaîne d'erreur ne subsiste.
Entrées à recueillir
- Dépenses de vente et marketing et nouveaux clients sur la même période (pour le CAC).
- ARPU par mois et % de marge brute.
- Attrition mensuelle (ou rétention) et taux d'actualisation.
- Nombre de cohortes et mois à modéliser dans la grille.
- Multiplicateurs de scénario pour l'attrition et l'ARPU (Base/Optimiste/Pessimiste) et la bande de tolérance.
Exemple
Hypothétique : S&M 200 000, 400 nouveaux clients, donc CAC 500. ARPU 50 par mois, marge brute 80% donne 40 de bénéfice brut par client-mois, délai de récupération 12,5 mois. Attrition 4% mensuelle implique une durée de vie moyenne de 25 mois ; LTV forme fermée environ 1 000 et LTV actualisée des cohortes légèrement inférieure ; LTV/CAC proche de 2,0x. La grille de cohortes décroît pour chaque cohorte 100%, 96%, 92,2%, ... sur les mois, et la contribution s'additionne pour former la LTV actualisée.