Comment utiliser Scenario Manager pour modéliser les calculs de prêt


Posted on 2017-09-11 11:36:01 category:Logiciel


Le gestionnaire de scénarios est regroupé avec la recherche d'objectifs et les tableaux de données dans la section Analyse des hypothèses de l'onglet Données.Être regroupé avec d'autres outils si utiles vous amènerait à croire que Scenario Manager est également un outil essentiel à connaître.Cependant, malgré son nom qui sonne utile et la bonne compagnie qu'il garde, Scenario Manager est assez limité dans ses fonctionnalités et est aussi utile que son nom l'indique!Il n'est donc pas fréquemment utilisé par les experts en modélisation financière.

Configuration du modèle

Pour montrer comment utiliser Scenario Manager, appliquons-le à un modèle de calcul de prêt simple.La théorie derrière les calculs de prêts est assez complexe, mais heureusement, Excel gère les prêts assez facilement.

Dans l'exemple ci-dessous, vous verrez un calculateur de taux d'intérêt sur lequel vous pourrez tester la sensibilité des remboursements mensuels aux variations des taux d'intérêt et des conditions de prêt.Suivez ces étapes:

  1. Téléchargez le fichier 0801.xlsx, ouvrez-le et sélectionnez l'onglet 8-14, ou configurez simplement le modèle avec des hypothèses d'entrée codées en dur.
    modélisation financière PMT function
    Configuration de la fonction PMT pourcalculer les remboursements mensuels de prêt.
  2. Dans la cellule B11, tapez= PMT (et appuyez sur Ctrl + A.

    La boîte de dialogue Arguments de la fonction apparaît.

    La fonction PMT nécessite les entrées suivantes:

    • Taux: le taux d'intérêt.
    • Nper: le nombre de périodes sur la durée du prêt.
    • Pv: La valeur actuelle du prêt (le montant emprunté).
    • Fv: montant restant à la fin de la période de prêt.(Dans la plupart des cas, vous souhaitez rembourser la totalité du montant pendant la durée du prêt, vous pouvez donc laisser ce champ vide.)
    • Type: si vous souhaitez que les paiements aient lieu au début ou à la fin de la période.(Vous pouvez laisser ce champ vide pour les besoins de cet exercice.)
  3. Liez les champs de la boîte de dialogue Arguments de fonction aux entrées de votre modèle.

    La fonction PMT renvoie le montant du remboursement annuel.Étant donné que vous souhaitez calculer le montant du remboursement mensuel, vous pouvez simplement diviser la formule entière par 12, mais comme les intérêts sont composés, il est plus précis de diviser chaque champ par 12 dans la formule.Ainsi, le taux du premier champ est converti en un taux mensuel et le nombre de périodes du deuxième champ est également converti en un taux mensuel.

  4. Cliquez sur OK.

    La formule est= PMT (B7/12, B9 * 12, B5).

    Cette fonction renvoie une valeur négative car il s'agit d'une dépense.Pour vos besoins ici, changez-le en positif en faisant précéder la fonction du signe moins.

Application du gestionnaire de scénarios

Vous pouvez maintenant utiliser Scenario Manager pour ajouter des scénarios.Vous souhaitez connaître l'impact des variations des intrants sur vos remboursements mensuels.Suivez ces étapes:

  1. Dans l'onglet Données, dans la section Prévision du ruban, cliquez sur l'icône Analyse hypothétique et sélectionnez Gestionnaire de scénarios dans la liste déroulante.

    La boîte de dialogue Gestionnaire de scénarios s'affiche.

  2. Cliquez sur le bouton Ajouter pour créer un nouveau scénario.

    La boîte de dialogue Ajouter un scénario apparaît.

    Scenario Manager
    Construire le scénario à l'aide du Scenario Manager.
  3. Entrez un nom pour le premier scénario dans la zone Nom du scénario (par exemple, Scénario un).
  4. Entrez les références de cellule pour les cellules variables dans la zone Modification des cellules.

    Séparez chaque référence par une virgule (s'il y en a plusieurs), mais n'utilisez pas d'espaces.Vous pouvez également maintenir la touche Ctrl enfoncée et cliquer sur chaque cellule de la feuille de calcul pour insérer les références dans la zone.

  5. Cliquez sur OK.

    La boîte de dialogue Valeurs du scénario s'affiche avec les valeurs existantes (0,045 pour le taux d'intérêt et 25 pour les années).

  6. Cliquez sur OK pour accepter ces valeurs comme scénario un.
  7. Cliquez sur Ajouter pour ajouter un autre scénario.

    La boîte de dialogue Ajouter un scénario réapparaît.

  8. Entrez un nom pour le deuxième scénario dans la zone Nom du scénario (par exemple, Scénario deux).
  9. Cliquez sur OK.

    La boîte de dialogue Valeurs du scénario réapparaît.

  10. Saisissez les valeurs des variables pour ce scénario (par exemple, 0,05 pour le taux d'intérêt et 30 pour les années).
    scenario manager
    Saisie des valeurs de scénario à l'aide du Scenario Manager.
  11. Cliquez sur OK.

    Vous revenez à la boîte de dialogue Gestionnaire de scénarios.

  12. Suivez à nouveau les étapes 7 à 9 pour créer des scénarios supplémentaires.
  13. Une fois que vous avez créé tous les scénarios, vous pouvez utiliser le gestionnaire de scénarios pour afficher chaque scénario en cliquant sur le bouton Afficher en bas.

    Les entrées sont automatiquement modifiées pour afficher les scénarios.

Les scénarios sont spécifiques à la feuille, ce qui signifie qu'ils n'existent que dans la feuille où vous les avez créés.Ainsi, lorsque vous recherchez les scénarios que vous avez créés, vous devez sélectionner la bonne feuille dans le modèle.

À propos de l'auteur du livre

Danielle Stein Fairhurst est la principale modélisatrice financière de Plum Solutions avec de nombreuses années d'expérience dans le domaine.Son groupe LinkedIn «Modélisation financière dans Excel» compte plus de 40 000 abonnés.Elle est également l'auteur de plusieurs articles et autres livres ainsi que d'un bulletin d'information sur la modélisation financière.

82257