Un apprenant m’a récemment parlé d’un projet de modélisation financière sur lequel il travaillait et pour lequel, les comptes à recevoir et les comptes à payer avaient une importance particulière. Il souhaitait savoir comment modéliser, de façon dynamique, le suivi de ce type de comptes lorsqu’il y a plusieurs échéances de remboursements pour une créance. Cet article vise donc à enseigner comment créer une prévision de comptes à recevoir et/ou une prévision de comptes à payer.
Exemple de prévision de comptes à payer
L’exemple que l’apprenant m’a fourni est le suivant :
Une créance de 10,000 $ :
- avec un règlement de 30 % lors de la facturation
- avec un règlement de 30 % à 30 jours
- le solde à 60 jours
Approche simple avec fonctions de base dans Excel
L’approche la plus simple, pour effectuer une prévision de comptes à payer, c’est de supposer que les périodes de remboursement sont fixes dans le temps. Ceci permet de créer des formules très simples, comme celle présentée ci-dessous. D’abord, on insère les différentes créances selon l’occurrence à la ligne 7. Ensuite, on indique les pourcentages de remboursement à chaque mois dans la colonne D. Pour calculer le remboursement du premier mois, on va chercher la créance du mois actif, que l’on multiple par le pourcentage de la cellule D10.
À la ligne 11, on va plutôt appliquer le pourcentage de la cellule D11 à la cellule de la colonne précédente de la ligne 7, tel qu’illustré ci-dessous (1 mois de décalage) :
On va finalement effectuer un calcul semblable pour les remboursements du 3ième mois. On va donc se trouver à modéliser adéquatement le remboursement des créances en leurs 3 versements mensuels respectifs.
Pour calculer le solde des comptes à payer, on pourra simplement utiliser la formule ci-dessous :
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Avantages et inconvénients de cette méthode de prévision des comptes à payer
Le principal avantage de cette méthode est sans contredit sa facilité d’usage. Par contre, en modélisation financière, on voudra souvent tester divers scénarios et effectuer des analyses de sensibilité. On voudra notamment comprendre quel serait l’effet de rembourser avec un mois de retard ses créances, par exemple. Avec l’approche ci-dessus, on ne peut pas simplement modifier la cellule B12 en y inscrivant le mot “Mois 4”, pour que par magie, les remboursements se décalent d’un mois. Il faudra aussi modifier la formule de la ligne 12. Ceci nous empêchera donc de tester de façon dynamique divers scénarios. Il s’agit de la lacune la plus importante avec cette approche.
Approche avec la fonction Décaler
La fonction DÉCALER est une fonction plus dynamique, qui permet notamment de se déplacer aisément dans un chiffrier pour récupérer les valeurs désirées. Elle peut être très utile lors de la prévision de comptes à payer ou de comptes à recevoir. Ci-dessous, la fonction DÉCALER soit être interprétée comme suit:
- Point de départ: Cellule de la ligne 20, de la colonne active : E20
- Décalage vers le bas : 0
- Décalage vers la gauche (le signe – signifie vers la gauche) : 0
- Sélection en hauteur : 1 ligne
- Sélection en largeur: 1 colonne
Le résultat de la formule DÉCALER, dans la cellule E23, rapporte donc le contenu de la cellule E20, soit 10,000$, qui est ensuite multiplié par le pourcentage de la ligne 23, soit 30%.
À la ligne 25, la même formule est utilisée et peut être interprétée comme suit :
- Point de départ: Cellule de la ligne 20, de la colonne active : E20
- Décalage vers le bas : 0
- Décalage vers la gauche (le signe – signifie vers la gauche) : 3
- Sélection en hauteur : 1 ligne
- Sélection en largeur: 1 colonne
Le résultat de la formule DÉCALER, dans la cellule E25, rapport ici une erreur puisqu’en reculant de 3 colonnes sur la ligne 20, la formule se trouve à pointer sur du texte, plus précisément sur le mot “Créances”. La formule DÉCALER permet donc de modifier dynamiquement les mois de décalage dans la colonne B mais il faut faire attention de mettre des limites pour ne pas récupérer du texte en se déplaçant ainsi vers la gauche.
Plusieurs fonctions permettent d’empêcher une telle erreur. Je ne recommande pas d’utiliser les fonctions comme SIERREUR, par exemple, puisque vous pourriez ainsi enrayer de réelles erreurs sans vous en apercevoir. Vous pourriez par contre travailler avec une fonction SI, comme celle présentée ci-dessous. Si la formule se trouve dans une colonne dont le rang est inférieur au décalage requis, on retourne 0, sinon on effectue le calcul.
Avantages et inconvénients de cette approche pour la prévision des comptes à payer
L’avantage principal de cette deuxième approche réside dans le fait que l’usager peut aisément changer les mois de décalage dans la colonne B et que l’on peut ainsi effectuer aisément des analyses de scénarios et de sensibilité, ce qui est l’objectif ultime en modélisation financière. Toutefois, Microsoft nous laisse savoir que la fonction DÉCALER est très gourmande et fait partie des fonctions volatiles d’Excel. Si vous utilisez une telle fonction, c’est donc en toute connaissance de cause et avec parcimonie. Personnellement, je n’ai jamais rencontré de problèmes avec cette fonction car mes fichiers Excel sont toujours optimisés. Par contre, dans des fichiers Excel très lourds, construits selon de mauvaises pratiques d’affaires, une fonction volatile pourrait amener de l’instabilité.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Afin d’approfondir vos connaissances en modélisation financière, vous pouvez vous inscrire à notre série de 3 formations sur le sujet, dont la première s’intitule Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi la formation en ligne Excel – Modélisation financière (niveau 1):
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
