Excel : Modélisation de comptes à payer et comptes à recevoir

Publié le 13 juillet 2020
par Sophie Marchand M.Sc., CPA, CGA, MVP
Décaissement dynamique

Excel : Modélisation de comptes à payer et comptes à recevoir

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. L’exemple qu’il 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

Cet article vise à répondre à cette question.

 

Approche simple avec fonctions de base dans Excel

L’approche la plus simple, 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.

 

Modélisation des Comptes à Payer

 

À 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) :

Décaissements

 

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.

Modélisation des Comptes à Payer

 

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

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. 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%.

Modélisation des Comptes à Payer

 

À 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.

Décaissement dynamique

 

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.

Décaissement dynamique

 

Avantages et inconvénients

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

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):

Jean-Philippe Provost
Écrit il y a 1 semaine
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 5 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Paul Chainé
Écrit il y a 1 an
Vraiment très agréable à suivre

La narration et les explications se font avec un débit qui laisse le temps de voir et comprendre ce qui est expliqué, dans un style amical et décontracté, la simplicité des exercices est très appréciée ce qui fait qu'on les fait et c'est justement ce qui nous permet d'assimiler la matière.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top