Modélisation financière dans Excel Pour les nuls - Cheat Sheet

Par Danielle Stein Fairhurst

Lorsque vous créez des modèles financiers dans Microsoft Excel, les fonctions sont le nom du jeu. Vous devez également vérifier votre travail - et le vérifier à nouveau - pour vous assurer qu'aucune erreur ne passe entre les mailles du filet. Enfin, pour rendre votre travail rapide et facile, les raccourcis clavier sont une bouée de sauvetage.

Des fonctions Excel essentielles pour construire des modèles financiers

Aujourd'hui, plus de 400 fonctions sont disponibles dans Excel, et Microsoft continue d'en ajouter à chaque nouvelle version du logiciel. Beaucoup de ces fonctions ne sont pas pertinentes pour une utilisation en finance, et la plupart des utilisateurs d'Excel n'utilisent qu'un très faible pourcentage des fonctions disponibles. Si vous utilisez Excel à des fins de modélisation financière, vous devez au moins maîtriser les fonctions les plus couramment utilisées.

Bien qu'il y en ait beaucoup, beaucoup d'autres que vous trouverez utiles pour construire des modèles, voici une liste des fonctions les plus basiques dont vous ne pouvez pas vous passer.

Fonction	Description
SOMME	Ajoute ou totalise une plage de cellules.
MIN	Calcule la valeur minimum d'une plage de cellules.
MAX	Calcule la valeur maximum d'une plage de cellules.
MOYENNE	Calcule la valeur moyenne d'une plage de cellules.
ROUND	Arrondit un nombre unique à la valeur spécifiée la plus proche, généralement à un nombre entier.
ROUNDUP	Arrondit à un seul nombre à la valeur spécifiée la plus proche, généralement à un nombre entier.
ROUNDDOWN	Arrondit à un seul nombre à la valeur spécifiée la plus proche, généralement à un nombre entier.
IF	Renvoie une valeur spécifiée uniquement si une condition unique a été remplie.
IFS	Renvoie une valeur spécifiée si les conditions complexes ont été remplies.
COUNTIF	Compte le nombre de valeurs dans une plage qui satisfait à un critère unique.
COUNTIFS	Compte le nombre de valeurs dans une plage qui répond aux critères multiples.
SUMIF	Somme les valeurs dans une plage qui satisfont un certain critère simple.
SUMIFS	Résume les valeurs dans une plage qui répond aux critères multiples.
VLOOKUP	Recherche une plage et renvoie la première valeur correspondante dans une table verticale correspondant exactement à l'entrée spécifiée.
HLOOKUP	Recherche une plage et renvoie la première valeur correspondante dans une table horizontale correspondant exactement à l'entrée spécifiée. Une erreur est renvoyée si elle ne peut pas trouver la correspondance exacte.
INDEX	Fonctionne comme les coordonnées d'une carte et renvoie une valeur unique en fonction des numéros de colonne et de ligne que vous entrez dans les champs de fonction.
MATCH	Renvoie la position d'une valeur dans une colonne ou une ligne. Les modélisateurs combinent souvent MATCH avec la fonction INDEX pour créer une fonction de recherche, qui est beaucoup plus robuste et flexible et utilise moins de mémoire que VLOOKUP ou HLOOKUP.
PMT	Calcule le total paiement annuel d'un prêt.
IPMT	Calcule la composante intérêt d'un prêt.
PPMT	Calcule la composante principal d'un prêt.
VAN	Prend en compte la valeur temps de l'argent en donnant la valeur actualisée nette des flux de trésorerie futurs en dollars courants, en fonction du montant de l'investissement et du taux d'actualisation.

Il y a bien plus à être un bon modélisateur financier que de simplement connaître de nombreuses fonctions Excel. Un modélisateur qualifié peut choisir quelle fonction est la meilleure à utiliser dans quelle situation. Habituellement, vous pouvez trouver plusieurs façons d'obtenir le même résultat, mais la meilleure option est toujours la fonction ou la solution la plus simple, la plus claire et la plus facile à comprendre pour les autres.

Ce qu'il faut rechercher lors de la vérification ou de l'audit d'un modèle financier

Si vous utilisez Excel depuis un moment, vous préférez probablement créer vos propres feuilles de calcul ou modèles financiers à partir de zéro. Dans un environnement d'entreprise, cependant, les gens ont rarement cette opportunité. Au lieu de cela, ils sont censés reprendre un modèle existant que quelqu'un d'autre a construit.

Vous occupez peut-être un poste que vous remplacez et il existe un modèle de reporting financier que vous devrez mettre à jour chaque mois. Ou vous avez été invité à calculer les commissions de vente chaque trimestre en vous basant sur une feuille de calcul monstrueuse de 50 onglets dont vous n'aimez pas vraiment l'apparence. Non seulement héritez-vous des modèles des autres avec les entrées, les hypothèses et les calculs que le modeleur original a saisis, mais vous héritez également des erreurs du modeleur.

Si vous prenez la responsabilité du modèle de quelqu'un d'autre, vous devez être prêt à le prendre en charge et à le personnaliser. Vous devez être responsable du fonctionnement de ce modèle et confiant que cela fonctionne correctement. Voici une liste de choses à vérifier lorsque vous héritez pour la première fois du modèle financier de quelqu'un d'autre:

Familiarisez-vous avec son apparence. Parcourez chaque feuille pour voir quels schémas de couleurs ont été utilisés. Lisez toute la documentation. Y at-il une clé pour aider à voir quelles cellules sont lesquelles? Le modélisateur a-t-il différencié les formules et les hypothèses codées en dur?
Regardez bien les formules. Sont-ils cohérents? Contiennent-ils des valeurs codées en dur qui ne seront pas mises à jour automatiquement et causeront donc des erreurs?
Exécutez une vérification d'erreur. Appuyez sur le bouton Vérification des erreurs dans la section Audit des Formules de l'onglet Formules du Ruban pour voir d'un coup d'œil s'il existe des erreurs Excel sur la feuille susceptibles de provoquer des problèmes.
Vérifiez les liens vers des fichiers externes. Les liens externes peuvent être une partie valide du processus opérationnel de travail, mais vous devez savoir si ce fichier reçoit des entrées des classeurs externes pour s'assurer que personne ne modifie par inadvertance les feuilles ou noms de fichiers, provoquant des erreurs dans votre modèle.Recherchez des liens externes en appuyant sur le bouton Modifier les liens dans la section Connexions de l'onglet Données du ruban.
Passez en revue les plages nommées. Les plages nommées peuvent être utiles dans un modèle financier, mais elles contiennent parfois des erreurs dues à des noms redondants, ainsi qu'à des liens externes. Passez en revue les plages nommées dans le Gestionnaire de noms, qui se trouve dans la section Noms définis de l'onglet Formules du ruban. Supprimez toutes les plages nommées qui contiennent des erreurs ou ne sont pas utilisées, et si elles contiennent des liens vers des fichiers externes, prenez-en note et assurez-vous qu'elles sont nécessaires.
Vérifier les calculs automatiques. Les formules doivent être calculées automatiquement, mais parfois, lorsqu'un fichier est très volumineux ou qu'un modélisateur aime contrôler manuellement les modifications, le calcul a été défini sur manuel au lieu d'automatique. Si vous voyez le mot Calculer dans la barre d'état en bas à gauche, cela signifie que le calcul a été réglé sur manuel, donc vous êtes probablement dans une enquête complexe! Appuyez sur le bouton Options de calcul dans la section Calcul de l'onglet Formules du ruban pour passer du calcul manuel au calcul automatique.

En plus de ces étapes, voici quelques outils d'audit pratiques dans Excel que vous pouvez utiliser pour vérifier, auditer, valider et, si nécessaire, corriger un modèle hérité afin d'être sûr des résultats de votre modèle financier:

Inspecter le classeur. Apprenez à connaître les caractéristiques cachées de votre modèle et à identifier les fonctionnalités potentiellement problématiques qui pourraient être difficiles à trouver avec cet outil peu connu. Pour l'utiliser, ouvrez le classeur, cliquez sur le bouton Fichier sur le ruban; Dans l'onglet Info, cliquez sur le bouton Vérifier les problèmes.
F2: Si les cellules source d'une formule sont sur la même page, le raccourci F2 place la cellule en mode édition, ce raccourci est donc un bon moyen de voir visuellement d'où proviennent les données sources.
Traces précédentes / personnes à charge: Les outils d'audit Excel tracent visuellement les relations avec les flèches de la ligne de traçage. Vous pouvez accéder à ces outils dans la section Audit de formules de l'onglet Formules du ruban.
Évaluer la formule: Éliminez les formules longues et complexes à l'aide de l'outil Évaluer la formule, dans la section Audit de la formule de l'onglet Formules du ruban.
Outils de vérification d'erreur: Si vous faites une erreur - ou ce que Excel pense être une erreur - un triangle vert apparaîtra dans le coin supérieur gauche de la cellule. Cela se produira si vous omettez des cellules adjacentes, ou si vous entrez une entrée sous forme de texte, ce qui semble être un nombre.
Fenêtre de surveillance: Si vous souhaitez garder un œil sur les cellules de sortie, cet outil affiche le résultat des cellules spécifiées dans une fenêtre séparée. Vous pouvez trouver cet outil dans la section Audit de formules de l'onglet Formules du ruban. Il est utile de tester les formules pour voir l'impact d'une modification des hypothèses sur une ou plusieurs cellules distinctes.
Afficher les formules: Pour voir toutes les formules en un coup d'œil plutôt que les valeurs résultantes, appuyez sur le bouton Afficher les formules dans la section Audit de formules de l'onglet Formules du ruban (ou utilisez le raccourci Ctrl + ').Afficher les formules est également un moyen très rapide et facile de voir si des valeurs codées en dur existent.

Raccourcis clavier Excel pour les modélisateurs financiers

Si vous passez beaucoup de temps à modéliser dans Excel, vous pouvez gagner du temps en apprenant quelques raccourcis clavier. Une grande partie des compétences du modélisateur sont sur la vitesse et la précision, et en pratiquant ces raccourcis jusqu'à ce qu'ils deviennent mémoire musculaire, vous serez un modeleur plus rapide et plus précis.

Voici une liste des raccourcis les plus utiles qui devraient faire partie de votre utilisation quotidienne du clavier si vous êtes un modélisateur financier:

Édition
Ctrl + S	Enregistrer le classeur.
Ctrl + C	Copier.
Ctrl + V	Coller.
Ctrl + X	Couper.
Ctrl + Z	Annuler.
Ctrl + Y	Rétablir.
Ctrl + A	Sélectionner tout.
Ctrl + R	Copiez la cellule située à l'extrême gauche de la plage. (Vous devez d'abord sélectionner Range.)
Ctrl + D	Copiez la cellule supérieure dans la plage. (Vous devez d'abord sélectionner la plage.)
Ctrl + B	Gras.
Ctrl + 1	Boîte de format.
Alt + Tab	Changer de programme.
Alt + F4	Fermer le programme.
Ctrl + N	Nouveau classeur.
Maj + F11	Nouvelle feuille de calcul.
Ctrl + W	Fermer la feuille de calcul.
Ctrl + E + L	Supprimer une feuille.
Ctrl + Tab	Changer de classeur.
Navigation
Maj + barre d'espace	Sélectionnez la ligne.
Ctrl + Barre d'espace	Mettre en surbrillance la colonne.
Ctrl + - (trait d'union)	Supprimer les cellules sélectionnées.
Touches fléchées	Déplacer vers de nouvelles cellules.
Ctrl + Pg Haut / Bas Bas	Changer les feuilles de travail.
Ctrl + Flèches	Aller à la fin de la gamme continue et sélectionner une cellule.
Touches Shift + Flèche	Sélectionner une plage.
Maj + Ctrl + Flèches	Sélectionner une plage continue.
Accueil	Aller au début de la ligne.
Ctrl + Accueil	Déplacer vers la cellule A1.
Dans Formules
F2	Formule de modification, montrant les cellules précédentes.
Alt + Entrée	Commencer une nouvelle ligne dans la même cellule.
Touches Maj + Flèche	Sélectionnez dans les cellules.
F4	Modifier le référencement absolu ("$").
Echap	Annuler une entrée de cellule.
ALT + = (signe égal)	Somme les cellules sélectionnées.
F9	Recalculer tous les classeurs.
Ctrl + [	Mettez en surbrillance les cellules précédentes.
Ctrl +]	Mettre en surbrillance les cellules dépendantes.
F5 + Entrée	Revenir à la cellule d'origine.

Pour trouver le raccourci d'une fonction, appuyez sur la touche Alt et les touches de raccourci afficheront le ruban. Par exemple, pour accéder au Gestionnaire de noms, appuyez sur Alt + M + N et la boîte de dialogue Gestionnaire de nom apparaît.

Dans le coin supérieur gauche, vous trouverez la barre d'outils Accès rapide. Vous pouvez modifier les raccourcis qui apparaissent sur la barre d'outils Accès rapide en cliquant sur la petite flèche à droite de la barre d'outils et en sélectionnant ce que vous voulez ajouter dans le menu déroulant qui apparaît. Par exemple, si vous ajoutez Collage spécial à la barre d'outils Accès rapide, Collage spécial est accessible avec le raccourci Alt + 4. Notez que cela ne fonctionne que lorsque la barre d'outils Accès rapide a été personnalisée, et tout ce que vous mettez dans la quatrième position sera accessible par le raccourci Alt + 4.