Modèle Excel MRP gestion de stock : le vrai calcul derrière vos stocks (et pourquoi le « temps de sécurité » change la donne)

Entre « zéro rupture » et « zéro immobilisation », l’équilibre est fragile. Trois paramètres souvent mal compris — stock spécial, temps de sécurité et consignation fournisseur — suffisent pourtant à faire dérailler (ou à sauver) un MRP. Enquête au cœur d’un pilotage que l’on croit standard… et qui ne l’est jamais vraiment.

1) Le paradoxe MRP : standard, mais jamais neutre

Sur le papier, le MRP est simple : besoin net = demande – stocks nettable – réceptions fermes, puis on planifie avec un lead time et une méthode de lot. Dans les ateliers, c’est une autre histoire.

« On avait du stock partout, mais pas au bon endroit… Le MRP nous criait “lancez”, alors qu’un lot dormant en consignation aurait suffi », soupire une directrice supply chain d’un sous-traitant automobile.

Ce qui fausse le calcul ? Ce que le MRP “voit” (nettable) et quand il le “voit” (temps de sécurité vs stock de sécurité). Ajoutez une consignation fournisseur mal paramétrée, et vous passez de la maîtrise au yo-yo.

2) Trois leviers qui changent tout

a) Stock spécial : nettable… ou invisible

Projets, prototypes, réservations client, quarantaine qualité : la même pièce, plusieurs “natures” de stock.

SPEC (projet) : le marquer nettable = Yes si vous voulez qu’il couvre la demande. Sinon, il devient invisible pour le MRP.
QH/BLK/RSV : par défaut non nettable (quarantaine, bloqué, réservé).
Un clic de trop côté « nettable », et le besoin disparaît… jusqu’à la rupture.

b) Temps de sécurité vs stock de sécurité

Deux façons de se protéger :

Stock de sécurité (SS) : on rehausse le niveau de PAB à ne pas franchir.
Temps de sécurité (ST) : on avance la date de besoin (le même volume, plus tôt).

Règle d’or : choisissez l’un ou l’autre. Le ST remplace le SS (il ne s’additionne pas). Le ST lisse mieux l’incertitude de lead time ; le SS amortit les variations de volume.

c) Consignation fournisseur : un “stock caché” à rapatrier

Le MRP devrait d’abord proposer un transfert CONS → UNR (consignation vers stock propre) avant de lancer un ordre. Sinon, vous fabriquez ou commandez… alors que la pièce est déjà disponible à la porte.

3) Paramétrer sans se tromper (le guide express)

Netting : listez vos types de stock (UNR, QH, BLK, RSV, CONS, SPEC) et fixez le flag “nettable”. Un audit mensuel évite l’effet « stock fantôme ».
Lot sizing : L4L pour coller à la demande, FOQ si vous avez un coût fixe de lancement; round au multiple du process.
Lead time : source unique (référentiel articles), révisé trimestriellement.
Règle consigne : offset de transfert (jours) documenté; priorité transfert > ordre.
Sécurité : si Use_Safety_Time = Yes, alors SS = 0 dans le calcul MRP.

4) Terrain – l’automobile en première ligne

Contexte : un équipementier carrosserie-assemblage. Le MRP lance des OF alors que 300 pièces dorment en consignation. En parallèle, des lots “projet X” (SPEC) sont exclus du netting. Résultat : surstock d’un côté, ruptures de l’autre.

Remède :

Cartographie des stocks par nature et réglage nettable.
Règle MRP : couvrir le manque d’abord par CONS → UNR (offset 2 jours), puis FOQ 200.
Temps de sécurité activé pour un module capteur à lead time volatile (ST = 7 jours) ; SS mis à 0.
Effet : -22 % de WIP, zéro rupture sur 8 semaines, baisse de 15 % des lancements “urgents”.

5) Encadré – Quand choisir le temps plutôt que le stock ?

Variabilité de délai (transit, douanes, changeover long) → Temps de sécurité.
Variabilité de volume (prévisions mouvementées) → Stock de sécurité.
Environnement JAT / flux tendu → ST évite de gonfler artificiellement le PAB.
Coûts de possession élevés → privilégiez ST ; il décale le besoin sans immobiliser.

6) Le tableau de bord qui évite les mauvaises surprises

Service : taux de remplissage, ruptures évitées par transferts CONS.
Stocks : rotation, % non nettable, SPEC nettable vs non nettable.
MRP : propositions actives (seulement semaines avec actions), part transfert vs ordres.
Fiabilité : écart prévision/réel, tenue de lead time, taux FOQ vs L4L.

Astuce lisibilité : une vue “semaines actives” (demande/réception >0) et des zéros masqués transforment un grand tableau en outil de décision.

7) Les erreurs qui coûtent (et leurs parades)

SPEC mis nettable “par défaut” → fausse absence de besoin. Parade : règle claire « SPEC nettable = projet validé seulement ».
ST + SS activés ensemble → sur-couverture. Parade : mutual exclusif.
Consignation ignorée → on produit/commande en double. Parade : MRP priorise CONS avec indicateur “transferts d’abord”.
Lead time figé → ST inutile ou insuffisant. Parade : revue trimestrielle et ST ajusté sur P95 des délais.
FOQ trop gros → montagnes russes de PAB. Parade : multiple réaliste, FOQ calé sur capacité/SMED.

8) Méthode en 8 étapes (à dérouler en deux semaines)

Lister toutes les natures de stock et fixer le flag nettable.
Mesurer l’existant consignation par article.
Choisir par article ST OU SS (pas les deux) et documenter le critère.
Définir la priorité CONS → UNR, offset et règles d’exception.
Nettoyer les FOQ/multiples et vérifier la cohérence avec la capacité.
Réviser les lead times (P95) et tracer l’historique.
Activer une vue MRP “semaines actives” + filtres sans zéros.
Boucler en rituel hebdo : décisions courtes, paramètres ajustés, écarts close-loop.

9) Ce qu’il faut retenir

Le MRP n’est pas « juste un calcul ». C’est une convention de visibilité : ce que vous lui montrez (stocks nettable), quand vous lui demandez d’agir (temps de sécurité) et où vous prenez d’abord (consignation) fabriquent vos résultats. Bien paramétré, il évite les urgences, libère du cash et protège votre service client — sans gonfler les entrepôts.

Et si votre tableau MRP ressemble encore à un mur de zéros, rappelez-vous : ce n’est pas un détail de mise en forme, c’est souvent le signe que le système ne dit pas où agir. Masquez les zéros, filtrez les semaines actives, et laissez les décisions importantes prendre toute la place.

Modèle MRP Excel : stock spécial, priorité consignation, temps de sécurité

1) Onglets et rôle

Parametres
- Start_Date, Horizon_Weeks (26 par défaut), Bucket=Weekly.
- Types de stock (UNR, QH, BLK, RSV, CONS, SPEC) avec le flag “Nettable dans MRP ?” qui décide ce que le calcul voit.
- Méthodes de lot : L4L et FOQ.
Articles
- Par article : Lead_Time_Days, Lot_Method, FOQ_Qty, Safety_Stock_Qty, Safety_Time_Days, Use_Safety_Time (Yes/No), Consign_Transfer_Offset_Days.
- Règle : si Use_Safety_Time=Yes, le temps de sécurité décale la demande et le SS est ignoré.
Stocks
- Quantités par type de stock (ex. UNR, CONS, SPEC). Seuls les types nettables entrent dans le calcul du stock initial.
Appro_Ouverts
- Réceptions fermes (PO/OF/STR) avec dates et quantités.
Demande
- Besoins (commandes/prévisions) datés et quantifiés.
MRP_Calc (vue de travail, 26 semaines)
- Sélection de l’Item en B4.
- Calculs hebdo :
  - Demande brute + Demande nette (décalée si temps de séc.).
  - Réceptions fermes.
  - Transferts consignation (CONS→UNR) → proposés avant les réceptions planifiées.
  - Réceptions planifiées (MRP) selon L4L/FOQ.
  - PAB, SS effectif (0 si Safety Time), Manque.
  - Date de lancement (= date de réception – lead time ou offset consignation) et Source (CONS/PO/OF).
- Lisibilité : zéros masqués (on n’affiche rien si 0), dates vides si non applicables.
Propositions
- Pour chaque semaine : Transfert CONS, Réception planifiée, Date de lancement, Source.
- Les cellules restent vides quand la valeur = 0. Colonne Has_Action (vide ou 1) et filtre actif sur non vide ⇒ on ne voit que les semaines avec action.
Propositions_Actives
- Extraction dynamique (FILTER/LET) des seules lignes où il y a une action (transfert ou réception planifiée). Idéale pour partage/impression.
Mode_d_emploi
- Rappels d’utilisation (flag nettable, priorité consignation→ordre, choix Temps de séc. OU Stock de séc.).

2) Logique clé du modèle

Stock spécial (SPEC) : inclus/exclu du netting selon Parametres → Nettable.
Temps de sécurité : décale la date de besoin (et désactive le SS pour l’article).
Consignation fournisseur : le MRP couvre d’abord le manque par CONS→UNR (offset paramétrable), puis crée des réceptions planifiées.
FOQ : arrondit au multiple choisi ; L4L colle au besoin.

3) À savoir (Excel)

Utilise XLOOKUP, LET, FILTER, CHOOSECOLS (Excel 365/2021 recommandé).
Horizon par défaut 26 semaines (modifiable), zéros masqués pour éviter le “bruit visuel”.

MRP_Stocks Download

Calculateur – MRP Simplifié (12 semaines)

Feuille Excel monoproduit pour planifier sur 12 semaines avec priorisation des transferts de consignation avant les réceptions planifiées. Conçue pour être imprimable en 1 page et utilisable sans fonctions 365.

Structure de la feuille « MRP_Simplifie »

Zone d’entrées (C5:C13)
- Article (texte), Stock initial (C5), Consignation dispo (C6), Lead time jours (C7)
- Méthode de lot L4L/FOQ (C6 liste déroulante), FOQ (C9)
- Temps de sécurité : Oui/Non (C10) + valeur en jours (C11)
- Stock de sécurité (C12) — ignoré si Temps de séc. = Oui
- Date début S1 (C13)
Saisie hebdo
- Demande C18:N18
- Réceptions fermes C19:N19
Calculs (lignes 21→27)
- Demande nette (avec décalage ST), PAB avant action, SS utilisé, Manque
- Transfert CONS (prioritaire), Réception planifiée (L4L/FOQ), PAB final
Suivi consignation
- Ligne “Consignation restante avant semaine” (ligne 30)

Règles de calcul (résumé)

Décalage Temps de séc. : la demande est avancée de ARRONDI.SUP(C11/7;0) semaines.
Formule de demande nette (par semaine k) :
=SI(ESTERREUR(INDEX($C$18:$N$18; k + $C$14)); 0; INDEX($C$18:$N$18; k + $C$14))
Stock de sécurité : =SI($C$10="Oui"; 0; $C$12)
Manque : =MAX(0; SS − PAB_base)
Transfert consignation : =MIN(Manque; Consignation_restante)
(la consignation restante se décrémente automatiquement chaque semaine)
Réception planifiée :
- L4L : quantité = manque résiduel
- FOQ : arrondi par multiple FOQ
  =ARRONDI.SUP(Manque_residuel/$C$9;0)*$C$9
PAB final : =PAB_base + Transfert + Réception planifiée

Ergonomie & impression

Zéros masqués (affichage vide si 0) pour une lecture claire.
Actions (transferts/planifiés) surbrillées en vert.
Impression paysage ajustée 1 page.

Mode d’emploi (1 minute)

Renseigner C5:C13 (méthode L4L/FOQ et Oui/Non via listes).
Saisir Demande et Réceptions fermes (C18:N19).
Lire Transfert CONS puis Réception planifiée par semaine.
Vérifier le PAB final et la consignation restante (ligne 30).
Imprimer si besoin.

Compatibilité & limites

Conçu pour Excel FR (fonctions SI, SOMME, ARRONDI.SUP, INDEX…), sans LET/FILTER.
Monoproduit, horizon 12 semaines, pas de nomenclature multi-niveaux (BOM) ni de capacité.

Personnalisations possibles

Étendre l’horizon (ajout de colonnes), ajouter un graphique PAB, bouton “Réinitialiser” (remise à 0 de C18:N19), ou version EN (formules anglaises) pour Excel US/UK.