Automatiser les rapports de simulation Monte Carlo, Excel VBA
Automatiser les rapports de simulation Monte Carlo dans Excel avec VBA permet de générer
rapidement plusieurs résultats de simulation et de produire des rapports analytiques basés sur
ces résultats. Voici un exemple détaillé de la façon dont vous pouvez créer une simulation de
Monte Carlo pour un projet ou un investissement, en générant des résultats aléatoires et en
créant un rapport avec VBA dans Excel.
Étapes de la simulation Monte Carlo
1. Définir les variables d’entrée (paramètres du modèle) :
– Par exemple, si vous simulez un projet, vous pouvez avoir des variables
comme le coût initial, les revenus attendus, les coûts opérationnels, les taux de
croissance, etc.
2. Générer des nombres aléatoires pour les entrées :
– Utilisez les distributions statistiques appropriées pour générer des valeurs
aléatoires (par exemple, une distribution normale pour les rendements).
3. Exécuter la simulation sur un grand nombre de itérations :
– Par exemple, 10 000 itérations pour obtenir une distribution des résultats.
4. Analyser les résultats :
– Calculez des mesures comme la moyenne, la médiane, les percentiles, etc.
5. Automatiser la génération du rapport :
– Créez un rapport dans une nouvelle feuille Excel avec des graphiques, des
tableaux et des mesures analytiques.
Code VBA pour Automatiser la Simulation Monte Carlo
Voici un exemple détaillé de code VBA pour une simulation Monte Carlo :
1. Préparer les entrées
Ce code suppose que vous avez un modèle simple avec trois variables :
1. Coût initial (qui suit une distribution normale),
2. Revenu annuel (qui suit une distribution uniforme),
3. Durée de vie du projet (nombre d’années).
2. Code VBA pour la simulation
Sub SimulationMonteCarlo()
' Définir les paramètres de la simulation
Dim nbIterations As Long
nbIterations = 10000 ' Nombre d'itérations
Dim i As Long
Dim coutInitial As Double
Dim revenuAnnuel As Double
Dim dureeVie As Long
Dim fluxDeTresorerie As Double
Dim resultatFinal As Double
Dim totalResultat As Double
' Créer un tableau pour stocker les résultats
Dim resultats() As Double
ReDim resultats(1 To nbIterations)
' Configurer la feuille de résultats
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Rapport Monte Carlo"
With Sheets("Rapport Monte Carlo")
.Cells(1, 1).Value = "Iteration"
.Cells(1, 2).Value = "Flux de Trésorerie"
End With
' Lancer la simulation Monte Carlo
For i = 1 To nbIterations
' Générer les variables aléatoires pour chaque itération
coutInitial = Application.WorksheetFunction.NormInv(Rnd(), 100000,
20000) ' Coût initial avec une distribution normale
revenuAnnuel = Application.WorksheetFunction.RandBetween(5000,
20000) ' Revenu annuel avec une distribution uniforme
dureeVie = Application.WorksheetFunction.RandBetween(5, 15) ' Durée
du projet entre 5 et 15 ans
' Calculer le flux de trésorerie (revenus annuels - coûts sur la
durée)
fluxDeTresorerie = revenuAnnuel * dureeVie - coutInitial
' Stocker le résultat dans le tableau
resultats(i) = fluxDeTresorerie
' Enregistrer les résultats dans la feuille Excel
Sheets("Rapport Monte Carlo").Cells(i + 1, 1).Value = i
Sheets("Rapport Monte Carlo").Cells(i + 1, 2).Value =
fluxDeTresorerie
' Ajouter à la somme des résultats pour calculer la moyenne plus
tard
totalResultat = totalResultat + fluxDeTresorerie
Next i
' Calculer des statistiques pour les résultats
Dim moyenne As Double
Dim ecartType As Double
Dim minResultat As Double
Dim maxResultat As Double
moyenne = totalResultat / nbIterations
ecartType = Application.WorksheetFunction.StDev(resultats)
minResultat = Application.WorksheetFunction.Min(resultats)
maxResultat = Application.WorksheetFunction.Max(resultats)
' Ajouter les résultats de la simulation dans le rapport
With Sheets("Rapport Monte Carlo")
.Cells(nbIterations + 3, 1).Value = "Moyenne"
.Cells(nbIterations + 3, 2).Value = moyenne
.Cells(nbIterations + 4, 1).Value = "Écart-type"
.Cells(nbIterations + 4, 2).Value = ecartType
.Cells(nbIterations + 5, 1).Value = "Min"
.Cells(nbIterations + 5, 2).Value = minResultat
.Cells(nbIterations + 6, 1).Value = "Max"
.Cells(nbIterations + 6, 2).Value = maxResultat
End With
' Créer un graphique pour visualiser la distribution des résultats
Dim chartObj As ChartObject
Set chartObj = Sheets("Rapport Monte
Carlo").ChartObjects.Add(Left:=200, Width:=400, Top:=50, Height:=300)
chartObj.Chart.ChartType = xlColumnClustered
chartObj.Chart.SetSourceData Source:=Sheets("Rapport Monte
Carlo").Range("B2:B" & nbIterations + 1)
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Distribution des résultats Monte
Carlo"
MsgBox "Simulation Monte Carlo terminée !", vbInformation
End Sub
Explication du code
1. Définition des paramètres :
– nbIterations: Nombre d’itérations pour la simulation (10 000 par défaut).
– coutInitial: Coût initial généré avec une distribution normale.
– revenuAnnuel: Revenu annuel généré avec une distribution uniforme.
– dureeVie: Durée du projet générée entre 5 et 15 ans avec une distribution
uniforme.
2. Boucle de simulation :
– La boucle For i = 1 To nbIterations génère les variables aléatoires pour
chaque itération, effectue le calcul du flux de trésorerie et enregistre les
résultats dans un tableau.
3. Calcul des statistiques :
– Une fois les itérations terminées, le code calcule la moyenne, l’écart-type, le
minimum et le maximum des résultats générés.
4. Création du rapport :
– Les résultats de chaque itération sont enregistrés dans une nouvelle feuille
nommée « Rapport Monte Carlo ».
– Des statistiques sont calculées et ajoutées sous les résultats de la simulation.
5. Graphique :
– Un graphique en colonnes est créé pour visualiser la distribution des résultats.
Résultat attendu dans Excel
1. Une nouvelle feuille est créée avec les résultats de la simulation Monte Carlo.
2. Pour chaque itération, vous avez la valeur du flux de trésorerie.
3. En bas de la feuille, vous trouverez des statistiques comme la moyenne, l’écart-type, le
min et le max.
4. Un graphique illustrant la distribution des résultats est également ajouté à la feuille.
Personnalisation
Distribution des variables : Vous pouvez ajuster les types de distributions pour
chaque paramètre selon vos besoins (par exemple, utiliser une distribution triangulaire,
lognormale, etc.).
Modèle : Vous pouvez remplacer le modèle simple de flux de trésorerie par des
calculs plus complexes en fonction de votre projet.
Rapport : Le format du rapport peut être personnalisé (ajout de couleurs,
d’annotations, etc.).
Cela vous permet d’automatiser efficacement la simulation Monte Carlo dans Excel et de
générer un rapport détaillé et visuel des résultats.