Excel pour analyste financier : compétences et formules clés
Les compétences Excel indispensables en finance : modélisation, références absolues, RECHERCHEX, CAGR, VAN, TRI, scénarios et contrôles.
Par Ayoub Farid · · 3 min de lecture
Les analystes financiers passent leur vie sur Excel. Le test Excel d'embauche pour ces postes comprend souvent une épreuve de modélisation financière en temps limité : la rapidité et la clarté de la structure comptent donc autant que la maîtrise technique des fonctions Excel.
La structure avant tout
- Hypothèses, calculs et synthèses séparés sur des feuilles de calcul distinctes ou des zones visuellement identifiables.
- Hypothèses isolées dans des cellules dédiées, jamais saisies en dur dans les formules Excel : taux de croissance, taux d'imposition, taux d'actualisation.
- Formules homogènes sur chaque ligne, de sorte qu'une formule unique puisse être étirée vers la droite sans modification.
- Code couleur standardisé : le texte en bleu pour les données sources ou hypothèses manuelles est la convention la plus répandue.
Formules utiles au quotidien
| Opération | Formule |
|---|---|
| Croissance | =B2*(1+$C$1) |
| Variation annuelle (YoY) | =(C2-B2)/B2. Voir variation en pourcentage |
| TCAM (CAGR) | =(end/start)^(1/years)-1 |
| Sommes conditionnelles | SUMIFS (SOMME.SI.ENS) |
| Recherches | XLOOKUP (RECHERCHEX), INDEX MATCH (INDEX/EQUIV) |
| Marges | =(B2-C2)/B2. Voir marge bénéficiaire |
| Valeur actuelle nette | =NPV(rate,C2:G2)+B2 (VAN) |
| Taux de rentabilité | =IRR(B2:G2) (TRI) |
| Mensualité d'emprunt | =PMT(rate/12,years*12,-amount) (VPM) |
Remarque : dans Excel, la fonction NPV (VAN en français, avec des points-virgules) actualise le premier flux d'une période complète. L'investissement initial (période 0) doit donc être ajouté en dehors de la formule.
L'importance capitale des références mixtes
Les modèles de prévision se recopient à la fois vers la droite (les années) et vers le bas (les postes de coûts). Les références doivent donc alterner judicieusement les symboles $. Un taux de croissance situé en colonne C utilisé pour plusieurs années s'écrit $C2. Un en-tête d'année situé en ligne 1 utilisé sur toute une plage s'écrit D$1. Voir les références mixtes.
Gestion des scénarios
- Une cellule de sélection du scénario avec
CHOOSE(CHOISIR) ouXLOOKUP(RECHERCHEX) pour basculer dynamiquement entre le cas central, optimiste et pessimiste. - Les tables de données (Outils d'analyse de scénarios) pour tester la sensibilité du modèle.
Contrôles de cohérence
Vérifiez l'équilibre du bilan comptable (l'écart doit être strictement nul), le bouclage de la trésorerie et l'absence totale de chiffres écrits en dur dans les lignes de calcul.
Questions fréquentes
Quelles sont les compétences Excel requises pour un analyste financier ?
La structuration de modèles financiers, la maîtrise des références absolues et mixtes, les fonctions SOMME.SI.ENS (SUMIFS), RECHERCHEX (XLOOKUP), les formules de croissance et de marge, la VAN (NPV) et le TRI (IRR), ainsi que les tests de scénarios et de cohérence.
Quelles fonctions Excel utilise-t-on le plus en finance ?
NPV (VAN), XNPV (VAN.PAIEMENTS), IRR (TRI), XIRR (TRI.PAIEMENTS), PMT (VPM), SUMIFS (SOMME.SI.ENS), XLOOKUP (RECHERCHEX), INDEX/MATCH (INDEX/EQUIV), EOMONTH (FIN.MOIS) et ROUND (ARRONDI) sont omniprésentes.
Comment se préparer à un test de modélisation financière en entretien ?
Entraînez-vous à construire rapidement un prévisionnel simple sur 3 ans : séparez bien les hypothèses dans une zone propre, appliquez des formules uniformes sur chaque ligne et recopiez vers le bas à l'aide de la poignée de recopie.
Pourquoi le résultat de la fonction VAN (NPV) diffère-t-il de celui d'une calculatrice financière ?
La fonction NPV d'Excel actualise la toute première valeur de la plage d'une période entière. Le flux d'investissement initial (période 0) doit donc être additionné directement hors de la formule pour éviter d'être faussement actualisé.