Excel : simulation Monte Carlo pour prévisions commerciales

La simulation Monte Carlo rendue accessible dans Excel

Prévoir son CA annuel sur un chiffre unique (par exemple 180 millions FCFA) cache une réalité : c’est une estimation ponctuelle dans un univers incertain. La simulation Monte Carlo génère des milliers de scénarios en tirant aléatoirement les paramètres clés dans leurs plages raisonnables. On obtient une distribution qui révèle la probabilité de chaque issue.

Principes fondamentaux

Monte Carlo repose sur trois étapes : définir les variables incertaines (ex : taux de conversion, panier moyen, nombre de visiteurs), attribuer à chacune une loi de probabilité (uniforme, normale, triangulaire), tirer 10 000 fois et agréger les résultats.

Fonctions Excel utiles

• ALEA() : uniforme entre 0 et 1
• ALEA.ENTRE.BORNES(a;b) : entier entre a et b
• LOI.NORMALE.INVERSE.N(probabilité; moyenne; écart_type) : tirage dans une loi normale
• LOI.BINOMIALE.INVERSE : tirage binomial

Cas pratique : lancement d’un produit

Scénario : une startup lance une app Mobile Money de crédit. Paramètres incertains :

• Nombre de téléchargements mois 1 : entre 2 000 et 8 000 (loi uniforme)
• Taux d’activation : moyenne 35 pour cent, écart-type 8 pour cent (loi normale)
• Panier moyen crédit : moyenne 45 000 FCFA, écart-type 12 000
• Marge nette : entre 2 pour cent et 6 pour cent

Construction du modèle

Feuille Simulation, 10 000 lignes :

Colonne A Telechargements : =ALEA.ENTRE.BORNES(2000;8000)
Colonne B Activation : =LOI.NORMALE.INVERSE.N(ALEA();0,35;0,08)
Colonne C Utilisateurs : =A2*B2
Colonne D Panier : =LOI.NORMALE.INVERSE.N(ALEA();45000;12000)
Colonne E Volume Credit : =C2*D2
Colonne F Marge : =ALEA()*0,04+0,02
Colonne G Profit : =E2*F2

Analyse de la distribution

Sur la colonne G, calculez :

• Moyenne : =MOYENNE(G2:G10001)
• Écart-type : =ECARTYPE.STANDARD(G2:G10001)
• Centile 5 : =CENTILE.INCLURE(G2:G10001;0,05) = pire scénario à 95 pour cent de confiance
• Centile 95 : =CENTILE.INCLURE(G2:G10001;0,95) = meilleur scénario
• Probabilité de profit positif : =NB.SI(G2:G10001; »>0″)/10000

Résultat réaliste : moyenne 12 M FCFA, centile 5 à -2 M (perte possible), centile 95 à 28 M. Probabilité de rentabilité : 78 pour cent.

Visualisation de l’histogramme

Sélectionnez la colonne Profit > Insertion > Histogramme. Ajustez le nombre de classes à 30. L’histogramme révèle si la distribution est symétrique, asymétrique ou multimodale.

Utilisation de Solver pour optimisation

Combiné à Solver, Monte Carlo permet de répondre : quel budget marketing maximise la probabilité de dépasser 20 M de profit ? Itération automatique avec contrainte de probabilité.

Validation du modèle

Faites varier les paramètres à +/- 20 pour cent : si le résultat change drastiquement, vous avez une variable critique à surveiller. Documentez les hypothèses pour justifier auprès d’investisseurs.

Compléments professionnels

Sur un angle proche : @RISK (Palisade), Oracle Crystal Ball, et plus récemment l’extension gratuite SIPMath Modeler Tools qui industrialise l’approche Monte Carlo dans Excel.

Conclusion

La simulation Monte Carlo transforme la prévision ponctuelle en distribution probabiliste. Pour un CEO qui présente son business plan à des investisseurs, passer de « On fera 180 M FCFA » à « Il y a 70 pour cent de chances de dépasser 150 M et 20 pour cent de chances de dépasser 220 M » change la crédibilité de l’analyse.

Voir aussi

• Excel : créer un tableau de bord financier dynamique
• Excel : cartographier les ventes Afrique avec cartes 3D
• Excel Solver : optimiser les tournées de livraison à Abidjan

Pourquoi simuler des previsions commerciales par Monte Carlo

Une prevision commerciale ponctuelle, par exemple Nous vendrons 1200 unites le mois prochain, donne une fausse impression de certitude. La realite est que la demande depend du prix, du climat, de la concurrence, des actions marketing, et d alea. La methode de Monte Carlo, popularisee dans les annees 1940 au laboratoire de Los Alamos, consiste a faire tourner des milliers de scenarios aleatoires et a observer la distribution des resultats. Au lieu d un chiffre, vous obtenez une fourchette avec des probabilites: il y a 80 pour cent de chance que les ventes du mois soient comprises entre 950 et 1320 unites.

Pour un dirigeant d entreprise a Abidjan ou Douala, cette information est plus utile qu une prevision unique car elle dimensionne le stock, la tresorerie et le risque. Excel propose nativement les fonctions ALEA et ALEA.ENTRE.BORNES qui generent des nombres aleatoires, et la fonction REPT combinee a une table permet de simuler facilement 10 000 tirages. Ce tutoriel construit un modele Monte Carlo complet, sans VBA, prevu pour Microsoft 365 ou Excel 2021.

Etape 1 — Identifier les variables incertaines

Avant Excel, listez les facteurs qui font varier vos ventes. Pour un boulanger a Saint-Louis qui vend du pain et des viennoiseries, les variables incertaines typiques sont: nombre de clients quotidiens (entre 200 et 350), panier moyen FCFA (entre 800 et 1500), taux de retour des invendus (entre 5 et 15 pour cent). Pour un revendeur de smartphones, ce sera plutot le volume de visiteurs, le taux de conversion, et le prix moyen vendu.

Pour chaque variable, vous avez besoin de trois informations: une valeur minimale realiste, une valeur la plus probable, et une valeur maximale. Cette triplette definit une distribution triangulaire, simple et intuitive, suffisante dans 90 pour cent des cas PME. Documentez la source des chiffres: historiques de vente, dires d expert, etudes de marche. Cette traceabilite est cle pour defendre la prevision en comite.

Etape 2 — Preparer le classeur

Creez Monte-Carlo-previsions.xlsx avec quatre feuilles: Variables, Simulation, Resultats, Distribution. Sur Variables, en colonne A le nom de chaque variable, B le minimum, C le mode (valeur la plus probable), D le maximum, E la moyenne calculee comme (Min + Mode + Max) divise par 3, F l ecart-type approximatif comme (Max – Min) divise par 6.

Cette derniere approximation suit la regle empirique 6-sigma: 99,7 pour cent des realisations d une variable se situent dans une plage de plus ou moins 3 ecarts-types. Pour un boulanger, ligne 2 NbClients: 200, 280, 350, 277, 25. Ligne 3 Panier: 800, 1100, 1500, 1133, 117. Ligne 4 TauxRetour: 0,05, 0,1, 0,15, 0,1, 0,017.

Etape 3 — Generer un tirage aleatoire unique

Sur Simulation, en cellule A1 ecrivez Tirage. En B1 NbClients, C1 Panier, D1 TauxRetour, E1 ChiffreAffaires_Net. En A2 ecrivez 1, et en B2 la formule pour tirer un nombre selon la distribution triangulaire. Excel n a pas de fonction LOI.TRIANGULAIRE native, on l implemente avec ALEA et SI:

=SI(ALEA()<(Variables!C2-Variables!B2)/(Variables!D2-Variables!B2);Variables!B2+RACINE(ALEA()*(Variables!D2-Variables!B2)*(Variables!C2-Variables!B2));Variables!D2-RACINE((1-ALEA())*(Variables!D2-Variables!B2)*(Variables!D2-Variables!C2)))

Cette formule applique la transformation inverse de la fonction de repartition de la loi triangulaire. Le resultat attendu est un nombre compris entre Min et Max, avec une concentration autour du Mode. Repetez en C2 en pointant la ligne 3 de Variables pour le Panier, en D2 pour le TauxRetour. En E2: =B2*C2*(1-D2). Recalculez avec F9 plusieurs fois pour observer la variabilite.

Etape 4 — Repeter sur 10 000 tirages

Selectionnez la plage A2:E2 et copiez. Selectionnez A3:A10001 et collez. Excel etend automatiquement les formules sur 10 000 lignes. Le calcul peut prendre 1 a 5 secondes selon la machine. Si Excel rame, basculez en mode calcul manuel: Formules, Options de calcul, Manuel. Vous declencherez le recalcul par F9 quand vous voulez un nouveau lot de tirages.

10 000 tirages suffisent largement pour stabiliser les statistiques agregees a moins de 1 pour cent pres. Aller a 100 000 ne change pas materiellement le resultat mais ralentit le classeur. Si votre machine ramе, descendez a 5000. La precision reste acceptable pour des decisions PME.

Etape 5 — Calculer les statistiques cles

Sur Resultats, en cellule B1 ecrivez Moyenne, B2 Mediane, B3 Ecart-type, B4 Minimum, B5 Maximum, B6 Percentile 5, B7 Percentile 95. En colonne C, les formules:

=MOYENNE(Simulation!E2:E10001)
=MEDIANE(Simulation!E2:E10001)
=ECARTYPE(Simulation!E2:E10001)
=MIN(Simulation!E2:E10001)
=MAX(Simulation!E2:E10001)
=CENTILE.INCLURE(Simulation!E2:E10001;0,05)
=CENTILE.INCLURE(Simulation!E2:E10001;0,95)

L intervalle de confiance a 90 pour cent est defini par les percentiles 5 et 95: il y a 90 pour cent de chance que le chiffre d affaires reel tombe dans cette fourchette. Pour le boulanger, vous obtiendrez typiquement une moyenne autour de 280 000 FCFA par jour, avec une fourchette 90 pour cent entre 220 000 et 340 000. Cette fourchette est l information operationnelle a communiquer au dirigeant.

Etape 6 — Visualiser la distribution

Sur Distribution, creez un histogramme. Definissez 20 classes regulieres entre Min et Max. Colonne A les bornes superieures, colonne B le nombre de tirages dans chaque classe avec NB.SI.ENS. Selectionnez la plage A1:B21 et inserez un graphique en histogramme via Insertion, Graphiques recommandes. Le graphique fait apparaitre une cloche asymetrique typique d une distribution triangulaire composee.

Ajoutez sur le graphique des lignes verticales de reference aux percentiles 5 et 95. Cela visualise immediatement la zone confortable des 90 pour cent. Pour les decideurs non-techniciens, ce graphique vaut mille mots: ils comprennent intuitivement que la prevision est une plage et non un point. C est cette pedagogie qui change les comportements de pilotage en PME.

Etape 7 — Conduire une analyse de scenario

Reproduisez le modele en changeant la triplette d une variable. Par exemple, scenario optimiste: NbClients (250, 320, 400). Scenario pessimiste: NbClients (150, 220, 290). Comparez les distributions resultantes. Le scenario optimiste decale toute la distribution vers le haut, le pessimiste vers le bas. Ce comparatif aide a evaluer le risque-recompense d une action commerciale: campagne marketing, baisse de prix, ouverture d un point de vente.

Pour chaque scenario, calculez aussi la probabilite que le chiffre d affaires depasse un seuil critique, par exemple le seuil de rentabilite mensuel de 6 millions FCFA: =NB.SI(Simulation!E2:E10001; »>=6000000″)/10000. Si le resultat est 0,72, vous avez 72 pour cent de chance d atteindre la rentabilite. Sous 50 pour cent, l action est risquee. Au-dessus de 80 pour cent, elle est solide.

Etape 8 — Calibrer sur les donnees historiques

Le modele est aussi bon que les triplettes Min, Mode, Max que vous avez choisies. Pour les calibrer rigoureusement, exploitez l historique. Si vous avez 24 mois de donnees, calculez le percentile 5 historique des ventes journalieres comme Min, la mediane comme Mode, le percentile 95 comme Max. Cette approche est purement empirique et evite les biais cognitifs de l estimation a dires d expert.

Au fil du temps, comparez les previsions Monte Carlo aux realisations. Tenez un fichier Excel des previsions mois M et de la realisation effective. Si la realisation tombe dans la fourchette 90 pour cent dans 9 mois sur 10, le modele est calibre. S il deborde plus souvent, vos triplettes sont trop etroites. C est cette boucle de feedback qui transforme un modele theorique en outil de pilotage utile.

Etape 9 — Etendre a la previsionnelle de tresorerie

La meme mecanique s applique a une previsionnelle de tresorerie a 12 mois. Variables incertaines: encaissements clients, decaissements fournisseurs, charges variables. Sortie: solde de tresorerie en fin de mois. Vous obtenez la probabilite de descendre sous zero, c est-a-dire le risque de besoin de tresorerie. Si cette probabilite depasse 10 pour cent sur un mois, la PME doit anticiper une ligne de credit court terme.

Couplez ce modele avec notre tutoriel tableau de bord financier OHADA qui fournit la couche comptable amont, et le guide rapprochement bancaire mobile money pour les flux d encaissement reels. L assemblage des trois forme une chaine de pilotage financier serieuse pour une PME ouest-africaine.

Etape 10 — Limiter et documenter le modele

Monte Carlo a deux limites majeures. Premierement, il suppose l independance des variables: si NbClients et Panier sont correlles, le modele sous-estime la variance reelle. Excel propose la fonction LOI.NORMALE.STANDARD.INVERSE et NORMALE.STANDARD.N pour generer des variables correlees, sujet d un futur tutoriel. Deuxiement, il ne predit pas les ruptures, par exemple un confinement sanitaire ou un coup d Etat: ces evenements sont hors distribution et exigent des scenarios catastrophes a part.

Documentez ces limites dans une feuille Hypotheses du classeur, avec la liste des variables, leurs sources, leurs distributions, et les exclusions. Cette transparence donne du poids a la prevision quand elle est presentee a un banquier, un investisseur ou un commissaire aux comptes. En PME, le serieux methodologique vaut souvent autant que la precision chiffree, parce qu il revele une discipline de pilotage qui rassure les parties prenantes.

Etape 11 — Industrialiser et automatiser le rituel mensuel

Une fois le modele valide sur deux ou trois cycles, transformez-le en gabarit Excel xltx pour eviter de recommencer chaque mois. Sauvegardez Fichier, Enregistrer sous, type Modele Excel, dans le dossier Templates Office personnel. Pour chaque nouvelle prevision, ouvrez le gabarit, mettez a jour la feuille Variables avec les triplettes recalibrees du mois, recalculez F9, exportez la feuille Distribution en PDF.

Tenez un journal Excel des previsions historiques: date de prevision, fourchette 90 pour cent, realisation effective, ecart. Apres 12 mois, vous disposez d un actif precieux qui prouve la fiabilite de votre methode. Cet actif est decisif quand vous negociez une facilite de tresorerie avec une banque locale: le banquier reconnait immediatement le serieux methodologique et accelere souvent le dossier.