La modélisation, qu’elle soit financière ou d’un tout autre domaine permet tout d’abord de réaliser des simulations selon la variation de données appelées hypothèses.
Une des fonctionnalités très utile dans EXCEL pour aider dans ces simulations est la table de données également appelée table de simulation:
Je vais prendre un exemple afin de vous expliquer cette fonctionnalité: Réaliser une simulation pour un prêt immobilier (taux d’intérêts / capacité de remboursement)
Cette fonctionnalité pourra être très utile également dans des cas de modélisation sur la Production, la logistique, les achats, ….
J’ai pu mettre en oeuvre une table de simulation afin de mesurer la sensibilité de l’évolution du résultat d’exploitation par à la variation du Chiffre d’affaire et à la variation du prix des matières premières.
Je l’ai également utilisé pour un cas de gestion de trésorerie, afin de définir une date optimum et un choix à faire entre prendre des tickets d’agios ou des intérêts d’escompte.
Revenons donc sur le premier exemple. Voici ce à quoi nous souhaitons arriver:
Sur la partie de gauche, les hypothèses et les calculs dont nous aurons besoin. Mais revenons d’abord sur l’objectif de cet exercice: définir le montant de remboursement mensuel selon deux axes: la durée du prêt et le taux d’intérêt.
Les hypothèses sont les suivantes:
- $C$2: Emprunt (montant total de l’emprunt)
- $C$3: Durée (en année)
- $C$5: Taux d’intérêt annuel
- $C$10: Capacité de remboursement (avec la condition opérationnelle, on peut voir directement dans le tableau les solutions qui répondent à cette contrainte).
Les calculs utilisés:
- $C$4: Durée (en mois)
- $C$6: Taux d’intérêt mensuel
- $C$7: Remboursement mensuel = -VPM(C6;C4;C2)
- $C$8: Le coût de l’emprunt (j’ai fait le calcul pour information mais ne m’en sers pas par la suite).
Une fois toutes ces hypothèses et calculs réalisés, nous allons pouvoir mettre en oeuvre la table de donnée.
Tout d’abord, dans les cellules H2:P2, mettre les hypothèses de variation de taux d’intérêt annuel puis dans les cellules G3:G16, renseigner les durées:
Ensuite en G2, taper la formule suivante : « =C7 ». Cela signifie que nous allons afficher le calcul de la valeur en C7 en fonction des taux d’intérêt et des durées.
A présent, il faut sélectionner le tableau – Se positionner sur n’importe quelle cellule remplie du tableau et faire CTRL+*.
Nous pouvons désormais paramétrer la table de données:
Puis
En ligne, il faut renseigner la cellule que nous souhaitons faire varier, en l’occurrence le taux d’intérêt (hypothèse en $C$5).
En colonne, il faut renseigner la cellule que nous souhaitons faire varier, en l’occurrence la durée du crédit (hypothèse en $C$3).
Nous avons donc le résultat souhaité. Ensuite, il est possible de créer des mises à jour conditionnelles, comme dans l’exemple.
Pour information, pour vider le contenu du tableau, il faut sélectionner toutes les valeurs car une formule matricielle est utilisées: {=TABLE(C5;C3)}
Comme d’habitude, si vous avez des questions, des problématiques à résoudre, ou des commentaires à faire, n’hésitez pas, je me ferai un plaisir de répondre.
