Au cours des dernières semaines, nous avons examiné la hausse de l'utilisation et de la popularité d'Excel et j’ai remarqué que Excel a fait des incursions dans l’analyse. Aujourd'hui, nous examinerons un type d'analyse: la planification et l'optimisation basées sur des scénarios.
À l'aide des compléments Excel, vous pouvez créer une analyse de scénario et une planification basée sur des scénarios à l'aide de la simulation Monte Carlo. Cependant, la création de ce type d'analyse est fastidieuse, principalement parce que Excel de base manque de telles fonctionnalités intégrées. En outre, l'affichage est toujours sous forme de feuille de calcul plutôt que d'affichage graphique. Pouvoir afficher graphiquement les entrées et leurs effets sur la sortie, ainsi que leurs interactions les unes avec les autres, est extrêmement précieux pour le spectateur.
Il y a quelques versions, nous avons décidé d'ajouter l'analyse JMP aux feuilles de calcul Excel via notre propre complément Excel. Cela étend la valeur d'un tableur Excel en incorporant les graphiques JMP et la simulation Monte Carlo.
Quand puis-je prendre ma retraite - un exemple
Examinons la différence entre les deux approches en utilisant un sujet qui nous tient à cœur et qui nous est cher: Quand aurai-je assez d'argent pour prendre ma retraite? Vous trouverez ci-dessous une feuille de calcul que j'ai créée pour montrer les résultats potentiels.
Cela commence par mes économies à ce jour (solde d'ouverture), ce que je prévois d'économiser chaque année, le montant des intérêts que je pourrais gagner de mes investissements et combien de temps je compte travailler. Je calcule ensuite la valeur future de mon solde d'ouverture et la valeur future des flux d'épargne. Je ne montre que les cellules d'entrée / sortie, mais vous remarquerez que j'ai caché des colonnes.
Afin de montrer comment les nombres ont été calculés, j'ai non-caché les colonnes (à propos, est-il possible de cacher des éléments dans un tableur?). Nous pouvons maintenant voir comment j'ai créé le modèle.
Je devais faire un calcul basé sur le nombre d'années restant à la retraite. Si ce nombre était égal à 10, je souhaiterais que mes formules de valeur future concernant à la fois la valeur initiale et le flux d’épargne soient définies dans 10 ans. J'ai accompli cela en créant 30 cellules (en supposant que j'ai 30 ans ou moins pour travailler) chacune avec la formule suivante:
Les cellules de la colonne D contiennent = + IF ($ C $ 9> D5, D5 + 1,0)
Les cellules de la colonne E contiennent = IF (D6 <> 0, E5,0)
Les deux sont en termes relatifs ou absolus selon les besoins. Cela m'a permis de créer un tableau du nombre d'années requis pour mon calcul.
La cellule F10 contient = FVSCHEDULE (F6, $ E $ 5: $ E $ 54) et
La cellule F11 contient = FV (C8, C9, F7) * - 1
Ce n'est peut-être pas le meilleur moyen de créer ce modèle, mais il semble calculer correctement les résultats - qu'importe donc s'il y a un meilleur moyen? Je l'ai eu au travail, non? (Si vous n'êtes pas d'accord, vous n'êtes pas un utilisateur typique d'Excel.)
Je peux ajuster les entrées et voir les sorties changer. Belle. Pourtant, certaines informations potentielles ne sont pas apparentes dans le tableur. Il existe une représentation graphique qui permettrait de mieux comprendre le modèle.
Je ne vais pas entrer dans les détails de la façon dont Excel effectuerait une analyse basée sur des scénarios ici. Vous êtes libre d'apprendre cela à partir d'Excel. Mais je veux montrer comment JMP peut fournir des informations nouvelles qu'Excel ne peut pas.
Vous ouvrez la feuille de calcul avec le complément JMP appelé, cliquez sur l'onglet JMP et sélectionnez Créer / Modifier un modèle:
Vous définissez ensuite le modèle à l'aide de la boîte de dialogue Créer / Modifier:
Pendant la phase de définition, vous cliquez sur le bouton Choisir pour sélectionner les étiquettes d’entrée et la cellule contenant le montant saisi. Vous définissez des valeurs minimales, initiales et maximales pour chaque cellule en entrée, puis vous identifiez les cellules de sortie de la même manière par pointer-cliquer.
Après avoir défini le modèle, sélectionnez Exécuter le modèle dans l’onglet Complément JMP. JMP affiche les éléments suivants:
JMP montrera sous forme graphique les variables qui contribuent au résultat (économies futures). Ces graphiques transmettent beaucoup plus d'informations que les cellules. Comme JMP représente graphiquement les valeurs minimum / maximum de chaque variable, celle qui a la pente la plus forte est celle qui affectera le plus la variable cible (économies futures). Dans ce cas, c’est le nombre d’années où je suis capable de travailler afin de permettre à mon épargne de croître.
Mais le Profiler montrera également comment chaque variable peut affecter les autres. Je peux utiliser la souris pour sélectionner la ligne rouge verticale au milieu de chaque graphique et la faire glisser à gauche ou à droite, ce qui augmente ou diminue la valeur de cette variable. Lorsque j'augmente le nombre d'années jusqu'à la retraite, regardez ce qui se passe dans le graphique du retour sur investissement annuel:
La pente de cette ligne augmente considérablement, indiquant que cette variable a désormais un impact plus important sur le résultat. Cela a du sens: plus vous accordez de temps à votre argent pour générer des intérêts et croître, mieux c'est.
Mais l'analyse ne s'arrête pas là. Une fois que vous avez défini chaque variable sur une valeur réaliste, vous pouvez tester la probabilité d’atteindre ces valeurs, c’est-à-dire la probabilité que j’économise 10 000 dollars par an en moyenne ou que mon rendement soit de 7%. investissements? C’est là que la simulation entre en jeu.
Avec JMP, vous pouvez définir les variables sur lesquelles exécuter la simulation Monte Carlo:
L'image ci-dessus montre la probabilité d'atteindre un objectif d'économies futures d'un million de dollars en faisant varier au hasard le montant économisé par an, le taux de rendement et le nombre d'années de retraite. Une chance de 5% d'atteindre mon objectif ne me ferait pas du bien si ces chiffres étaient réels.
Mais cette série de blogs n’a pas pour objectif de montrer comment exploiter un tableur existant. Le fait est que s’il y avait des erreurs dans la feuille de calcul (et qu’il n’en trouve aucune s'il vous plaît), l’analyse JMP aurait peu d’intérêt et pourrait éventuellement conduire à des conclusions et à des décisions erronées.
La semaine prochaine, nous verrons comment créer le même modèle dans JMP.
Remarque: Cet article de blog fait partie d'un série sur les erreurs dans les feuilles de calcul . Les nouveaux articles de cette série apparaissent chaque mercredi le mercredi.
Mike Clayton a écrit:
Les exemples d’épargne comportent toujours ce que je pense être un retour sur investissement très optimiste, étant donné les systèmes d’épargne de type IRA habituels. Mon retour sur investissement en épargne de 1994 à 2014, une période de 20 ans de grande volatilité, a montré un retour sur investissement après impôts et environ 4%. Cependant, pour obtenir cet argent, je dois payer des impôts sur le revenu ... ce que je reconnais avoir évité pendant cette période d'économies régulières de 20 ans.
Alors qu'est-ce qui est réel? Comment incluez-vous les questions fiscales dans une analyse de plan d'épargne étant donné l'utilisation courante des IRA? Cela pourrait être un modèle intéressant pour ajouter les efforts de Monte Carlo AVANT de vous inscrire pour un ROTH ou un IRA régulier. Ferait un bon webinaire.
