P&L / BFR / Cash-flow sur 36 mois : Modèle Excel

Comment bâtir un modèle entièrement renseigné par formules (marge, impôt sur EBIT > 0, ΔBFR, CAPEX, cash)

Objectif

Mettre en place un triptyque P&L – BFR – Cash-flow sur 36 mois, totalement automatisé par formules. L’intérêt : une cohérence stricte entre rentabilité (P&L), besoin en fonds de roulement (BFR) et trésorerie (Cash-flow), avec des hypothèses “en bleu” et la bascule Bas / Central / Haut. Ce guide détaille l’architecture, les formules clefs et les points d’attention (marge, impôt calculé uniquement si EBIT > 0, variation de BFR (ΔBFR), CAPEX et cash).

1) Architecture du classeur (vue d’ensemble)

Hypothèses
- Croissance mensuelle du CA, marge brute %, Opex (S&M, R&D, G&A), taux d’impôt, DSO/DPO/DIO, CAPEX mensuel, amortissements, trésorerie initiale, mois de départ (M1).
- Sélecteur de Scénario actif (Bas / Central / Haut).
P&L_Mensuel (36 colonnes M1→M36)
- CA → COGS → Marge brute → Opex → EBITDA → Amort. → EBIT → Impôt (si EBIT > 0) → Résultat net.
BFR
- Clients (AR), Stock, Fournisseurs (AP), BFR net = AR + Stock − AP, ΔBFR = variation mensuelle du BFR net.
CashFlow_Mensuel
- NI (résultat net) + Amort. − ΔBFR − CAPEX → Cash net du mois → Trésorerie fin (avec Trésorerie début mois suivant en report).
Résumé / Dashboard
- KPI (CA M12/M24/M36, EBITDA M12, Cash fin M12/M24/M36), runway, premiers mois EBITDA > 0 et NI > 0, graphiques (CA, cash).

Astuce de robustesse : formater les entêtes M1→M36 en vraies dates (ex. =EDATE(DateDépart; k)) et afficher mmm-yy. Cela facilite la saisonnalité et l’alignement temporel.

2) Hypothèses & scénarios (la “source de vérité”)

Hypothèses en bleu pour signaler les valeurs éditables.
Scénarios (Bas, Central, Haut) stockés dans un onglet dédié, puis tirés en Hypothèses via IF() ou INDEX/MATCH() selon le scénario actif.
Dates : DateDépart (M1) + EDATE(DateDépart; k) pour M2…M36.
Saisonnalité (optionnelle) : vecteur 12 mois (moyenne = 1) appliqué au CA de chaque mois selon MONTH(EDATE(...)).

3) P&L mensuel : formules clefs

3.1 Chiffre d’affaires (CA)

Base M1, croissance mensuelle, ajustements éventuels (saisonnalité) :

CA_M1 = Hypothèses!CA_base
CA_Mt = CA_M(t-1) * (1 + Hypothèses!Croissance) * SaisonFacteur_t

Sans saisonnalité, retirez * SaisonFacteur_t.

3.2 Coûts variables & marge brute

COGS_t = CA_t * (1 - Hypothèses!MargeBrute%)
MargeBrute_t = CA_t - COGS_t

3.3 Opex & EBITDA

EBITDA_t = MargeBrute_t - (Opex_S&M_t + Opex_R&D_t + Opex_G&A_t)

Au départ, on peut modéliser les Opex fixes mensuels. Ensuite, les faire dépendre du CA (variable/step-fixed).

3.4 Amortissements, EBIT, impôt conditionnel, résultat net

EBIT_t = EBITDA_t - Amortissements_t
Impôt_t = MAX(EBIT_t, 0) * TauxImpôt
RésultatNet_t = EBIT_t - Impôt_t

Impôt uniquement si EBIT > 0 : évite de “récupérer un crédit d’impôt” non prévu.
Amortissements : constants (CAPEX lissé) ou calculés à partir d’un registre d’immobilisations.

4) BFR : Clients, Stock, Fournisseurs, ΔBFR

Le BFR convertit le P&L en exigences de cash via les délais.

4.1 Postes du BFR (méthode “jours”)

AR_t    = CA_t   * (DSO / 30)
Stock_t = COGS_t * (DIO / 30)
AP_t    = COGS_t * (DPO / 30)
BFRnet_t = AR_t + Stock_t - AP_t

Les jours (DSO/DPO/DIO) sont des leviers puissants : ils influencent la consommation de cash indépendamment du P&L.

4.2 Variation de BFR (ΔBFR)

ΔBFR_t = BFRnet_t - BFRnet_(t-1)

Pour M1, on peut poser ΔBFR_M1 = BFRnet_M1 (vs. mois 0 = 0), ou partir d’un BFR initial si l’entreprise existe déjà.

5) Cash-flow mensuel : du résultat au cash

5.1 Construction directe

CashFlowOp_t = RésultatNet_t + Amortissements_t - ΔBFR_t
CAPEXcash_t  = - CAPEX_t                // sortie de cash
CashNet_t    = CashFlowOp_t + CAPEXcash_t
CashDébut_t  = (t=1 ? TrésorerieInitiale : CashFin_(t-1))
CashFin_t    = CashDébut_t + CashNet_t

Amortissements : ajoutés car non cash.
ΔBFR : soustrait si le BFR augmente (le cash “part” dans le cycle), ajouté s’il diminue.
CAPEX : traité en cash (sortie), indépendamment de l’amortissement P&L.

5.2 Contrôles rapides

Runway : nombre de mois avant que MIN(CashFin_1:CashFin_36) < 0.
1er mois EBITDA > 0 et 1er mois Résultat net > 0.
Alignement P&L ↔ Cash : sur longue période, la somme des NI + Amort. − ΔBFR − CAPEX doit expliquer la variation de trésorerie.

6) Scénarios & sensibilité (Bas / Central / Haut)

Bas : croissance plus lente, marge plus basse, DSO↑/DPO↓/DIO↑, CAPEX identique ou plus élevé.
Haut : croissance plus rapide, marge ↑, DSO↓/DPO↑/DIO↓, Opex mieux absorbés.
Mesurer l’effet sur EBITDA, NI, CashFin, runway.
Documenter les hypothèses : chaque valeur en bleu doit avoir un raisonnement (source, test, benchmark).

7) Points d’attention & bonnes pratiques

7.1 Cohérence temporelle

Mois M1 = date réelle ; entêtes M1→M36 en date (pas en texte).
Saisonnalité via MONTH() → facteurs 12 mois.

7.2 Impôt “sur EBIT > 0”

Politique prudente pour éviter d’anticiper des crédits ; si nécessaire, modéliser les pertes reportables (NOL) avec une table dédiée.

7.3 CAPEX & amortissements

CAPEXcash en Cash-flow (sortie) ≠ Amortissements en P&L (lissage).
Si vous avez des lots CAPEX à dates distinctes, tenir un tableau des immobilisations (montant, début, durée) et sommer les amortissements.

7.4 BFR : ne pas sous-estimer l’effet “jours”

Une baisse de DSO de 60→45 jours libère du cash, même à P&L constant.
DIO (stocks) très sensible si la marge brute est faible (COGS élevé).

7.5 Opex : fixe, variable, “step-fixed”

Débuter fixe (plus simple), puis introduire :
- part variable indexée au CA,
- seuils (embauche à M9, nouvelle licence à M12).

7.6 Validation

Refaire les sommes 12 mois (CA, COGS, Opex) et vérifier les ratios (marge brute %, EBITDA %).
S’assurer que CashFin ne contredit pas la dynamique du BFR (ex. DSO qui s’allonge doit tirer la trésorerie vers le bas).

8) Extraits de formules (compatibles Excel en anglais)

(À adapter si vous utilisez Excel FR : IF→SI, MAX→MAX, etc.)

P&L

COGS_t        = CA_t * (1 - Margin%)
GrossMargin_t = CA_t - COGS_t
EBITDA_t      = GrossMargin_t - (Opex_SM_t + Opex_RD_t + Opex_GA_t)
EBIT_t        = EBITDA_t - Depreciation_t
Tax_t         = IF(EBIT_t>0, EBIT_t * TaxRate, 0)
NetIncome_t   = EBIT_t - Tax_t

BFR

AR_t      = Revenue_t * (DSO/30)
Inventory = COGS_t     * (DIO/30)
AP_t      = COGS_t     * (DPO/30)
NWC_t     = AR_t + Inventory - AP_t
DeltaNWC  = NWC_t - NWC_(t-1)

Cash-flow

CFO_t      = NetIncome_t + Depreciation_t - DeltaNWC_t
CapexCash  = - Capex_t
NetCash_t  = CFO_t + CapexCash
BegCash_t  = IF(t=1, OpeningCash, PrevMonth_EndingCash)
EndCash_t  = BegCash_t + NetCash_t

9) Lecture des résultats (mode “diagnostic rapide”)

CFD “moral” du P&L : si la marge brute % est stable mais EBITDA se dégrade → Opex mal calibrés.
Cash qui diverge du NI : regarder ΔBFR (DSO/DPO/DIO) et CAPEX.
Runway < 12 mois : prioriser la réduction du DSO, négocier DPO, phaser CAPEX, revoir le mix prix/marge.
Premier mois NI > 0 tardif mais EBITDA positif tôt : poids des amortissements ou impôt ; vérifier structure d’actifs.

10) Checklists express

Avant projection

Hypothèses “bleues” complètes (croissance, marge, Opex, DSO/DPO/DIO, impôt, CAPEX, amort., cash initial).
Dates M1→M36 au bon format (date).
Scénarios Bas/Central/Haut opérationnels.

Après projection

Marge brute % cohérente par rapport au secteur.
ΔBFR logique vs DSO/DPO/DIO.
Cash fin positif ou plan d’action (BFR, CAPEX, levée).
Sensibilité : ±10 % sur 3 drivers (croissance, marge, DSO).

Le carnet de bord en six volets – P&L / BFR / Cash-flow sur 36 mois : Modèle Excel

1) Scenarios — « Trois climats pour un même voyage »

Trois colonnes — Bas, Central, Haut — étalent les saisons de ton projet.
On y règle la croissance, la marge brute, les Opex, les jours DSO/DPO/DIO, le CAPEX, l’amortissement, la trésorerie initiale.
Les chiffres sont préremplis et coloriés avec sobriété ; ils fixent le décor sans l’imposer. Tu n’as qu’à ajuster.

2) Hypotheses — « La salle des machines »

Ici, tout est bleu lorsqu’on peut toucher.
En B2, un sélecteur décide du climat (Bas/Central/Haut).
En B3, le mois de départ (au bon format mmm-yy) donne le tempo des 36 mois.
Chaque valeur bleue est une promesse de causalité : modifie-la, le reste suit.

3) PL_Mensuel — « Le récit de la rentabilité »

Douze lignes, trente-six colonnes, et une musique :
CA → COGS → Marge brute → Opex → EBITDA → Amortissements → EBIT → Impôt → Résultat net.
Les formules veillent : l’impôt ne tombe que si l’EBIT est positif. Les en-têtes sont de vraies dates — aucune confusion de format — pour que le temps soit un allié, pas un piège.

4) BFR — « Les coulisses du cash »

Trois leviers (Clients, Stock, Fournisseurs) dessinent la gravité cachée de ton cycle d’exploitation :
AR = CA × DSO/30, Stock = COGS × DIO/30, AP = COGS × DPO/30.
Le BFR net respire, le ΔBFR enregistre chaque souffle. Tu vois quand la trésorerie se tend ou se détend, sans rhétorique.

5) CashFlow — « La vérité comptée en euros »

On remonte du résultat au cash, sans tricher :
NI + Amortissements − ΔBFR − CAPEX = Cash net.
La trésorerie de début appelle la trésorerie de fin, mois après mois — une continuité qui rassure et alerte, selon la pente.

6) Dashboard — « La vue du pilote »

Des tuiles KPI (CA M12, Marge %, EBITDA M12, Cash M12/M24/M36) plantent des jalons.
Deux graphiques déroulent la trajectoire : CA et Cash de fin.
Une alarme murmure la vérité : si le cash devient négatif, la cellule rougit — on ne discute pas avec la gravité.

Des choix de conception assumés

Des dates réelles, pas des fictions

Les 36 en-têtes sont des valeurs de date (format mmm-yy). Peu importe la langue d’Excel, l’axe du temps reste fidèle.

Des feuilles « sans diacritiques »

Noms d’onglets simplifiés (Scenarios, Hypotheses, PL_Mensuel, BFR, CashFlow, Dashboard) pour garantir des liens solides partout.

Un impôt… à propos

La formule “impôt si EBIT > 0” évite le mirage des crédits automatiques ; elle privilégie une prudence d’exploitant.

La méthode d’emploi en trois gestes

1) Choisir l’humeur du modèle

Hypotheses!B2 → Bas, Central, ou Haut. Observe l’effet immédiat.

2) Poser la date de départ

Hypotheses!B3 → un mois qui devient M1. Le calendrier se déroule tout seul.

3) Agir sur le réel

Ajuste les cellules bleues : croissance, marge, Opex, DSO/DPO/DIO, CAPEX, cash initial.
Lis la réponse dans le P&L, sens le tirage dans le BFR, tranche sur le CashFlow, valide sur le Dashboard.

Ce que ce fichier t’offre vraiment

Un modèle qui ne “dit” rien : il montre

Chaque hypothèse explique une courbe ; chaque courbe révèle une contrainte.
Tu orientes le système ; lui projette l’avenir.

Une grammaire du financement

Runway, premier mois EBITDA > 0, premier mois NI > 0 : des repères pour parler à un banquier, un investisseur, un comité.

La transparence comme style

Aucune macro, pas de formule locale exotique. Des formules claires, auditables, que l’on peut reprendre, étendre, documenter.

Avec ces formules et cette discipline, vous obtenez un modèle 36 mois qui s’auto-alimente depuis les hypothèses, traite correctement l’impôt sur EBIT > 0, capture la variation de BFR, déduit le CAPEX, et explique la trajectoire de trésorerie. Il devient un outil de décision : on teste un levier (prix, DSO, CAPEX, rythme d’embauche), on lit l’effet sur EBITDA, NI, Cash, on tranche.