Analyse et prévisions
Définition
L'analyse et les prévisions regroupent les outils de simulation d'Excel : la consolidation de plages, la Valeur cible, le Gestionnaire de scénarios, les fonctions financières VPM et NPM, et la feuille de prévision. Ces outils répondent à des questions du type "que se passe-t-il si" et figurent au programme de l'examen MO-211.
La consolidation de données
L'outil Consolider (onglet Données) regroupe des plages issues de plusieurs feuilles dans une seule table de synthèse. On choisit une fonction d'agrégation, SOMME ou MOYENNE par exemple, puis on ajoute chaque plage source. La consolidation par position additionne les cellules au même emplacement ; la consolidation par catégorie additionne selon les libellés des lignes et des colonnes. Une consolidation liée met à jour la synthèse quand les sources changent.
La Valeur cible
La Valeur cible (Données > Analyse de scénarios > Valeur cible) modifie une cellule d'entrée pour qu'une formule atteigne un résultat donné. Elle exige trois éléments : la cellule cible, qui doit contenir une formule, la valeur à atteindre, et la cellule à modifier, qui doit être utilisée par cette formule.
Cellule cible : B5 = VPM(B3/12;B4;B2)
Valeur à atteindre : 500
Cellule à modifier : B2 (montant du prêt)
Excel fait varier B2 jusqu'à ce que B5 atteigne 500. Si la cellule à modifier ne participe pas au calcul, aucun résultat ne peut être trouvé.
Le Gestionnaire de scénarios
Le Gestionnaire de scénarios (Analyse de scénarios > Gestionnaire de scénarios) enregistre plusieurs jeux de valeurs pour les mêmes cellules : un scénario optimiste, un pessimiste, un réaliste. Chaque scénario porte un nom et se rappelle à tout moment. Le bouton Synthèse produit un rapport qui compare les scénarios côte à côte, avec les cellules variables et les cellules de résultat.
Les fonctions financières VPM et NPM
| Fonction | Syntaxe | Description |
|---|---|---|
| VPM (PMT) | =VPM(taux;npm;va;[vc];[type]) | Renvoie le montant d'un paiement périodique |
| NPM (NPER) | =NPM(taux;vpm;va;[vc];[type]) | Renvoie le nombre de périodes d'un prêt |
Le taux doit correspondre à la période : un taux annuel de 3,6 % donne un taux mensuel de 0,036/12. VPM renvoie un montant négatif, car il représente un flux sortant. NPM calcule le nombre de mensualités nécessaires pour rembourser un capital donné.
=VPM(0,036/12;48;15000) renvoie environ -336,57
=NPM(0,036/12;300;15000) renvoie environ 51,5
La prévision
La Feuille de prévision (Données > Prévision > Feuille de prévision) génère des valeurs futures à partir d'une série historique de dates et de valeurs. Excel crée un tableau et un graphique de prévision, avec un intervalle de confiance réglable. La fonction PREVISION.LINEAIRE (FORECAST.LINEAR) fait le même calcul en formule : elle extrapole une valeur à partir d'une droite de régression.
Exemple concret
Un emprunt de 15 000 € sur 48 mois à 3,6 % annuel donne une mensualité de :
=VPM(0,036/12;48;15000)
Le taux mensuel est 0,003. Avec la Valeur cible, on peut inverser la question : quel capital emprunter pour une mensualité de 300 € ? La cellule cible contient la formule VPM, la valeur visée est -300, et la cellule à modifier est celle du capital.