MRP/MPS & gestion des stocks : Modèle Excel simplifié — guide complet et pratique

Pour réussirle pilotage des stocks, Il s’agit d’aligner une demande planifiée crédible (MPS) avec une fourniture réaliste (MRP), tout en tenant la promesse client et le cash. Le modèle Excel que je vous ai fourni met ces trois briques au même endroit, avec des KPI colorés pour décider vite et bien.

Ce que font MPS et MRP

MPS (Master Production Schedule) transforme les besoins commerciaux en quantités hebdomadaires de produits finis. C’est la “vérité” de la demande que l’usine suit réellement

MRP (Material Requirements Planning) déduit à partir de ce MPS les besoins de composants, calcule les manques et propose des ordres (achats ou ordres de fabrication) en respectant le délai et la politique de lot

Le lien BOM (nomenclature) indique combien d’unités de chaque composant sont nécessaires pour fabriquer 1 unité de produit parent

Architecture du classeur

Paramètres définit l’horizon de 12 semaines et les dates de début de semaine. Il contient aussi les seuils KPI (benchmarks) utilisés pour le code couleur

Articles liste chaque article avec son type (FG = fini, COMP = composant), délai (en semaines), politique de lot (L4L, EOQ, Fixed), taille/EOQ, stock de sécurité et stock initial

Nomenclature relie chaque produit fini à ses composants avec un usage (ex. 2 × C1 pour 1 × FG1)

MPS porte la demande planifiée des produits finis, semaine par semaine

Réceptions recense ce qui est déjà lancé et arrivera dans l’horizon (commandes en cours)

MRP calcule pour chaque article et chaque semaine la chaîne complète
Besoins bruts → Réceptions planifiées → Stock projeté → Besoins nets → Réceptions prévues → Lancements prévus

KPI & Benchmarks agrège des indicateurs lisibles avec un code couleur paramétrable

Ordres proposés sert de zone de collecte pour sortir une liste de lancements à diffuser

La logique de calcul, pas à pas

Besoins bruts
Pour un produit fini, ce sont les quantités du MPS
Pour un composant, on multiplie le MPS du parent par l’usage de la nomenclature et on additionne si le composant est utilisé par plusieurs parents

Réceptions planifiées
Ce sont vos ordres déjà lancés attendus cette semaine-là

Stock projeté
Stock projeté(t) = Stock projeté(t−1) + Réceptions planifiées(t) + Réceptions prévues(t) − Besoins bruts(t)
Au temps 0, on part du stock initial

Besoins nets
Si le stock futur ne permet pas de rester au-dessus du stock de sécu, on calcule le manque
Besoins nets(t) = MAX(0 ; Stock sécu + Besoins bruts(t) − [Stock projeté(t−1) + Réceptions planifiées(t)])

Réceptions prévues (lot sizing)
Si L4L (lot-for-lot), la réception prévue = besoin net
Si EOQ/Fixed, on arrondit au multiple de lot supérieur pour couvrir le besoin

Lancements prévus
On décale chaque réception prévue de LT (lead time) semaines vers l’amont. C’est ce que vous devez lancer cette semaine pour être livré à temps

Les KPI de sortie, expliqués avec leurs couleurs

Service level = 1 − (semaines de rupture / 12)
Vert si ≥ 98%, Jaune si ≥ 95%, Rouge sinon
Lecture opérationnelle : si vous planifiez correctement et que vos hypothèses tiennent, vous devez voir du vert sur les produits finis et les composants critiques

Semaines de rupture = nombre de semaines où le stock projeté < 0
Vert si 0, Jaune si ≤ 1, Rouge si > 1
C’est l’indicateur d’alerte immédiate : s’il n’est pas zéro, votre plan ne tient pas

Semaines < stock de sécu = semaines où le stock projeté < SS
Indique les zones de vigilance, même sans rupture. À suivre en conjonction avec le service level

Stock projeté moyen
Vous renseigne sur l’encours moyen. À corréler avec la couverture et le coût de stock

Demande hebdo moyenne
Aide à vérifier l’échelle de vos politiques de lot. Si la demande moyenne est 80 et le lot “Fixed” est 300, attendez-vous à de grandes oscillations

Couverture moyenne (semaines) = stock projeté moyen / demande hebdo moyenne
Vert si entre 2 et 4 semaines, Rouge sinon
Si c’est trop bas, vous jouez avec le feu. Si c’est trop haut, vous immobilisez du cash inutilement

Tous ces seuils se règlent dans Paramètres → Seuils KPI, et la couleur se met à jour automatiquement

Comment utiliser le modèle au quotidien

Initialiser les données
Renseignez Articles (délai, lot, stock sécu, stock initial), Nomenclature, MPS des produits finis, et vos Réceptions déjà en route

Calculer et lire
Passez dans MRP pour visualiser les lancements prévus par article et par semaine. Vérifiez qu’aucune ligne n’affiche de stock projeté négatif après planification. Corrigez la politique de lot ou le stock de sécu si nécessaire

Décider avec les KPI
Ouvrez KPI & Benchmarks
Si le service level est rouge, concentrez-vous d’abord sur les produits finis puis cascadez vers les composants qui causent la rupture
Si la couverture est trop haute, réduisez les lots ou échelonnez les réceptions pour lisser l’encours

Boucle hebdomadaire
Mettez à jour MPS (nouvelles prévisions / commandes), ajustez les réceptions (retards/avances), et réévaluez les stocks de sécu si votre variabilité a changé. Sortez ensuite la liste des lancements et partagez-la aux achats/atelier

Exemples d’arbitrages courants

Risque de rupture sur C2 à S+3
Actions possibles
Avancer une réception existante
Décaler une partie de la production FG pour réduire le besoin brut
Augmenter temporairement le stock de sécu si la variabilité est durable

Couverture trop élevée sur C1 (6–8 sem.)
Actions possibles
Passer la politique de Fixed → L4L le temps d’absorber l’encours
Réduire l’EOQ si justifié par des coûts de lancement réellement faibles
Échelonner les réceptions pour réduire les pics de stock

Oscillations de production FG1 (effet yo-yo)
Lissez le MPS pour éviter de propager des vagues dans la supply chain
Vérifiez que vos lots ne forcent pas des tailles bien supérieures à la demande moyenne

Bonnes pratiques de paramétrage

Gardez les politiques de lot au plus simple (L4L pour démarrer), puis introduisez EOQ/Fixed seulement là où il y a un vrai gain économique mesuré

Basez le stock de sécu sur la variabilité (écart-type de la demande et du délai), pas sur une intuition. Le modèle accepte la valeur directement, mais rien n’empêche d’ajouter un petit calcul dans l’onglet Articles

Mettez à jour MPS de manière hebdomadaire avec les dernières infos commerciales et réalités de capacité

Vérifiez la complétude de la nomenclature. Un composant oublié = un plan irréaliste

Tracez les retards fournisseurs dans Réceptions : le MRP doit voir la réalité, pas le souhait

Limites (et comment les dépasser)

Le modèle ne gère pas la capacité atelier ni les contraintes de calendrier fin (jours fériés, fermetures). Pour des pics connus, lissez le MPS et anticipez dans Réceptions

Le MRP ici est à granularité hebdo. Si votre flux est très rapide ou très tendu, déclinez-le à J+1 (mêmes formules, pas le même calendrier)

Il n’optimise pas les coûts. Vous pouvez ajouter un onglet simple
coût de possession (€/u/semaine) × stock moyen

• coût de lancement × nombre de lancements
  et piloter un compromis entre service et cash

Micro-guide d’implémentation

Paramétrez 12 semaines à partir du prochain lundi dans Paramètres
Saisissez 3–5 produits finis prioritaires dans Articles et leur MPS dans MPS
Complétez la BOM minimale pour ces produits et quelques réceptions déjà en route
Ouvrez MRP pour récupérer la première liste de lancements et traitez immédiatement les rouges du KPI & Benchmarks

Décryptage 👇

Ce modèle vous donne une chaîne de décisions claire
une demande réaliste (MPS), des besoins nets et des lancements calés sur les délais (MRP), et des KPI colorés qui disent tout de suite si votre plan tient la route. Avec une routine hebdomadaire simple, vous gagnez en fiabilité de service, maîtrise des stocks, et sérénité dans vos arbitrages.

À quoi sert l’exemple (le cas)

Cas typique d’une PME d’assemblage qui lance/relance deux produits finis sur 12 semaines avec une montée de charge progressive. L’objectif est double :

1. Garantir le service (zéro rupture sur les produits finis) ;
2. Limiter l’encours (éviter une couverture de stock excessive), en gérant des contraintes fournisseurs (MOQ, délai).

Concrètement, l’exemple met volontairement en scène :

• Un composant contraint (C2) avec lot fixe 300 et LT=2 sem. → risque d’oscillation de stock et de tension si on ne lance pas assez tôt.
• Un composant “économique” (C1) en EOQ 200 et LT=1 sem. → plus souple, on peut lisser les réceptions.
• Des produits finis (FG1, FG2) en L4L → pas d’accumulation inutile au niveau des FG.

Ce que contient l’exemple (hypothèses clés)

Articles (paramètres d’appro)

• FG1 (LT 2 sem., L4L), stock sécu 50, stock initial 120
• FG2 (LT 1 sem., L4L), stock sécu 40, stock initial 80
• C1 (EOQ 200, LT 1 sem.), SS 100, stock initial 500
• C2 (Fixed 300, LT 2 sem.), SS 200, stock initial 900
• C3 (L4L, LT 1 sem.), SS 50, stock initial 300

Nomenclature (BOM)

• FG1 = 2×C1 + 1×C2
• FG2 = 3×C2 + 2×C3

MPS (demande planifiée des FG)

• FG1 par semaine : 80, 90, 100, 110, 100, 90, 80, 70, 60, 70, 80, 90
• FG2 par semaine : 50, 50, 60, 60, 70, 70, 60, 60, 50, 50, 45, 45

Réceptions déjà lancées (connues)

• C1 : +200 en S2
• C2 : +300 en S4

Ce que cet exemple vous montre (pédagogie intégrée)

1) Comment la contrainte lot fixe crée des paliers d’encours (C2)

La demande hebdo de C2 dépend du MPS :

• S1 : 230 (FG1 80×1 + FG2 50×3)
• S2 : 240 ; S3 : 280 ; S4 : 290 …
  Avec un stock initial 900, vous tenez les premières semaines, mais dès S3 vous frôlez le stock de sécu (200).
  En S4, une réception planifiée de 300 arrive (déjà lancée) ; comme la politique est “Fixed 300”, le MRP peut proposer encore 300 si le besoin net reste > 0 à cause du SS → on voit alors un rebond de stock (couverture qui grimpe).
  👉 But pédagogique : visualiser l’effet MOQ (lot fixe) sur l’encours et la couverture. Les KPI colorés vous signalent si vous surcouvrez (rouge hors plage 2–4 sem.).

2) Comment l’EOQ lisse la vie (C1)

Pour C1 (usage 2× dans FG1), la conso hebdo suit FG1 (160, 180, 200, 220, …). L’EOQ 200 et le LT court permettent des réceptions plus fréquentes mais raisonnables, donc moins d’à-coups dans le stock projeté.
👉 But : montrer une politique plus agile quand les coûts de lancement le permettent.

3) Pourquoi les FG en L4L

Les FG ne doivent pas “gonfler” l’encours : on produit au plus juste. Avec L4L, la réception prévue = besoin net, donc pas d’effet d’entrepôt au niveau FG.
👉 But : garder le cash au bon niveau de stock (plutôt composants contraints que produits finis).

Comment lire les KPI de l’exemple

Onglet “KPI & Benchmarks” (avec couleurs paramétrables dans Paramètres) :

• Service level (Vert ≥ 98 %, Jaune ≥ 95 %)
  Montre si votre plan tient sans rupture. Avec les valeurs de l’exemple, les FG doivent rester verts si vous laissez le MRP lancer aux bons horizons.
• Semaines de rupture (Vert = 0, Jaune = 1, Rouge > 1)
  Si C2 n’est pas lancé assez tôt (LT=2), vous verrez jaune/rouge.
• Semaines < stock de sécu
  Alerte de vigilance même si le service est encore assuré.
• Couverture moyenne (sem.) (Vert entre 2 et 4, sinon rouge)
  Avec C2 lot 300, attendez-vous à des paliers de couverture ; si ça vire rouge haut, deux options : réduire le lot (si possible) ou échelonner.
• Synthèse globale (cartes à droite)
  Service level moyen, total de semaines de rupture, couverture moyenne — tout en vert/jaune/rouge selon vos seuils.

Ce que vous pouvez tester tout de suite

• Avancer la réception C2 (S4 → S3) : vous verrez le service level s’améliorer et le risque de rupture reculer, mais attention à la couverture.
• Passer C2 de Fixed 300 → L4L (ou lot 150 si le fournisseur accepte) : la couverture baisse, le cash s’améliore, mais le nombre de lancements augmente.
• Augmenter le stock de sécu C2 si votre variabilité est forte : moins de semaines < SS, mais encours plus élevé.
• Lisser le MPS (réduire les pics) : diminue les oscillations dans toute la chaîne.

Le fichier exemple est conçu pour illustrer des arbitrages réels :

• tenir le service malgré un composant à MOQ/lot fixe,
• éviter la sur-couverture,
• montrer l’impact des politiques de lot et des délais sur les KPI.

Il vous sert de bac à sable : vous changez 1–2 paramètres (lot, délai, SS, timing de réception) et vous voyez immédiatement comment réagissent service level, ruptures et couverture.

Modèle vierge MRP/MPS & stocks – mode d’emploi

Démarrage express

1) Paramètres

• Ouvrez l’onglet Paramètres.
• Mettez la Date de début (lundi) au prochain lundi (format jj/mm/aaaa).
• Laissez 12 semaines (ou ajustez). Les dates d’en-tête se remplissent toutes seules.
• (Optionnel) Réglez vos seuils KPI :
  • Service level vert à 0,98 (98 %)
  • Jaune à 0,95 (95 %)
  • Couverture cible 2–4 semaines

2) Articles

• Allez dans Articles. Une ligne = 1 article (FG = produit fini, COMP = composant).
• Renseignez :
  • Type : FG (fini) ou COMP (composant)
  • Délai (sem.) : lead time total d’appro/production
  • Politique : L4L (au plus juste), EOQ (lot éco), Fixed (lot fixe/MOQ)
  • Taille lot/EOQ : multiple de commande si EOQ/Fixed
  • Stock de sécu et Stock initial

3) Nomenclature (BOM)

• Ouvrez Nomenclature.
• Pour chaque parent (FG), indiquez les composants et l’usage (ex. 2 = 2 pièces du composant pour 1 parent).

4) MPS (demande planifiée)

• Dans MPS, ne listez que vos produits finis (FG).
• Saisissez la quantité par semaine sur 12 semaines. C’est VOTRE vérité de demande.

5) Réceptions connues

• Dans Réceptions, saisissez les commandes déjà lancées (achats/OF) et leurs semaines d’arrivée.

6) Calcul & lecture

• Ouvrez MRP. Pour chaque article et chaque semaine, le modèle calcule :
  Besoins bruts → Réceptions planifiées → Stock projeté → Besoins nets → Réceptions prévues → Lancements prévus
• Les lancements prévus sont vos décisions de cette semaine (décalées du délai).

7) KPI & Benchmarks

• Ouvrez KPI & Benchmarks :
  • Service level (vert/jaune/rouge) = 1 − (semaines de rupture / 12)
  • Semaines de rupture (stock projeté < 0)
  • Semaines < stock de sécu
  • Couverture moyenne (vert entre 2–4 sem.)
• Ajustez les seuils dans Paramètres si vos standards diffèrent.

8) Sortie opérationnelle

• Dans MRP, filtrez les lignes “Lancements prévus” (>0) et copiez-les dans Ordres proposés pour diffusion à l’atelier/achats.

---

Votre routine hebdomadaire (simple et efficace)

Chaque lundi :

• Mettez à jour MPS (nouvelles prévisions/commandes).
• Corrigez Réceptions (avances/retards réels).
• Passez MRP : validez les lancements prévus de la semaine (OF/PO).
• Ouvrez KPI & Benchmarks : si service level ≠ vert ou ruptures > 0 :
  1. Avancez une réception ou augmentez le lot.
  2. Augmentez le stock de sécu (si variabilité durable).
  3. Lissez le MPS (si pics inutiles).

Milieu de semaine (5 minutes) :

• Si un fournisseur annonce un retard, modifiez Réceptions et vérifiez l’impact MRP/KPI.

---

Quand changer quoi (aide-mémoire)

• Rouge “Semaines de rupture” → Avancez une réception, lancez plus tôt (délai), ou augmentez le stock de sécu.
• Couverture trop haute (rouge) → Réduisez EOQ/Fixed, passez temporairement en L4L, échelonnez les arrivées.
• Yoyo de stocks → Lissez le MPS, vérifiez que le lot n’est pas disproportionné vs demande moyenne.

---

Bons réflexes

• Un FG sans MPS → pas de besoins pour ses composants.
• Un code article différent entre Articles et MPS/Réceptions/BOM → formules à #N/A (corrigez l’orthographe exacte).
• Dates : toujours jj/mm/aaaa.
• BOM complète : un composant oublié fausse tout le plan.

---

Personnaliser en 2 minutes

• Paramètres → Seuils KPI : ajustez les couleurs à vos cibles internes.
• Articles : ajoutez une colonne “Fournisseur/MOQ” (facultatif) si vous voulez affiner.
• Besoin d’un coût de stock (€/sem) ou d’un export automatique des lancements ? Dites-moi et je vous livre la variante.

---

Check-list “prêt pour la première semaine”

☑ Paramètres : date de lundi OK, 12 semaines
☑ Articles : délais, politiques, lots, SS, stock initial saisis
☑ BOM : usages renseignés pour chaque FG
☑ MPS : quantités hebdo remplies pour les FG
☑ Réceptions : commandes déjà lancées saisies
☑ MRP : lancements prévus validés
☑ KPI : Service level vert, ruptures = 0 (sinon, ajuster)