Comment utiliser le gestionnaire de scénarios pour modéliser les calculs de prêt

Le gestionnaire de scénarios est regroupé avec la recherche d'objectifs et les tableaux de données dans la section Analyse de simulation de l'onglet Données. Le fait d'ê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 à consonance 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 modélisateurs financiers experts.

Mise en place 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êt 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 pouvez tester la sensibilité des remboursements mensuels aux variations des taux d'intérêt et des conditions de prêt. Suivez ces étapes:

Téléchargez le fichier 0801.xlsx , ouvrez-le et sélectionnez l'onglet intitulé 8-14, ou configurez simplement le modèle avec des hypothèses d'entrée codées en dur.

Paramétrage de la fonction PMT pour calculer les remboursements mensuels du prêt.

Dans la cellule B11, tapez =PMT( et appuyez sur Ctrl+A.

La boîte de dialogue Arguments de fonction s'affiche.

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 : Le 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 période de prêt, vous pouvez donc laisser ce champ vide.)
• Type : si vous souhaitez que les paiements soient effectués au début ou à la fin de la période. (Vous pouvez laisser ce champ vide pour les besoins de cet exercice.)

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 se composent, il est plus précis de diviser chaque champ par 12 dans la formule. Ainsi, le taux dans le premier champ est converti en un taux mensuel, et le nombre de périodes dans le deuxième champ est également converti en un taux mensuel.

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 changements d'intrants sur vos mensualités. Suivez ces étapes:

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

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

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

La boîte de dialogue Ajouter un scénario s'affiche.

Construire le scénario à l'aide de Scenario Manager.

Saisissez un nom pour le premier scénario dans la zone Nom du scénario (par exemple, Scénario un).

Entrez les références de cellule pour les cellules variables dans la zone Cellules changeantes.

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.

Cliquez sur OK.

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

Cliquez sur OK pour accepter ces valeurs en tant que scénario un.

Cliquez sur Ajouter pour ajouter un autre scénario.

La boîte de dialogue Ajouter un scénario s'affiche à nouveau.

Saisissez un nom pour le deuxième scénario dans la zone Nom du scénario (par exemple, Scénario deux).

Cliquez sur OK.

La boîte de dialogue Valeurs du scénario s'affiche à nouveau.

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).

Saisie des valeurs de scénario à l'aide de Scenario Manager.

Cliquez sur OK.

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

Suivez à nouveau les étapes 7 à 9 pour créer des scénarios supplémentaires.

Après avoir 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 à une 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.