Excel : Comment résoudre une référence circulaire?

Publié le 09 décembre 2012
par Francis Paquet M.Sc., EEE

Excel : Comment résoudre une référence circulaire?

Voici une question fréquemment posée par nos clients: “Comment résoudre une référence circulaire dans le cas du calcul d’une marge de crédit utilisée pour payer les impôts et les intérêts sur la marge de crédit”?

 

Avertissement: Nous vous proposons ci-bas une résolution théorique de ce problème. Toutefois, sachez qu’en réalité, le traitement d’une référence circulaire dans le cas d’une marge de crédit est un peu plus complexe, puisqu’il faut notamment tenir compte des plafonds et autres subtilités semblables.

 

Référence circulaire : Cas d’une marge de crédit

 

Préparation des données

 

D’abord, posons:

  • Intérêt sur la marge de crédit =  a
  • BAII = Bénéfice Avant Intérêt et Impôt = b
  • BAI  = Bénéfice Avant Impôt = d
  • Impôt = c
  • TI = Taux d’impôt
  • Autres paiements d’intérêts = e
  • Position d’ouverture de la marge de crédit  = o
  • Position de fermeture de la marge de crédit = f
  • Trésorerie disponible pour rembourser marge de crédit = v
  • TIMC = Taux d’intérêt de la marge de crédit
.
Donc, on a:
  • BAI = b – e – a
  • Impôt = c = d  * TI, ou, en remplaçant par le développement de d,
  • c = (b – e – a) * TI
  • c = (b – e ) * TI –  a * TI
  • Bénéfice net = dc
.
L’astuce consistera ensuite à isoler a.

 

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.

 

Trésorerie disponible pour rembourser la marge de crédit:

  • v = -(Encaisse d’ouverture (probablement zéro) + Variation de la trésorerie avant intérêt sur la marge de crédit et avant impôt)
♦♦♦ Dans l’équation ci-dessus, comme la marge de crédit est présentée en montant positif, v a donc un signe inverse à la trésorerie disponible. Si l’entreprise peut rembourser $1M sur la marge de crédit, v = -$1M.

.

♦♦♦ Si la valeur absolue de v est supérieure à o, on va prendre o comme variation et ça simplifie le problème! Sinon, on va prendre v comme variation (en plus ou en moins) de la marge de crédit.

 

  • Position de fermeture = f = o + v + a + c. L’intérêt se calcule sur le solde moyen de la période, soit (o + f) / 2.

 

À partir de là, on a assez d’information pour résoudre l’équation qui va nous permettre de calculer a, le montant d’intérêt sur la marge de crédit.

 

Résolution de l’équation

 

  • a  = (o + f) / 2 * TIMC
    • où f = o + v + a + c
  • a  =( o + o + v + a  + c ) / 2 * TIMC
  • a = TIMC * ( o + v/2) + TIMC / 2 * a + TIMC / 2 * c
    • où c = (b – e ) * TI –  a * TI
  • a = TIMC * ( o + v/2) + TIMC / 2 * a + TIMC / 2 * [(b – e) * TI –  a * TI]

 

Si on met maintenant en évidence TIMC et qu’on isole les termes en a, on obtient:

  •  a = TIMC * [o + v/2 + (b – e )/2 * TI] + a  *TIMC /2 * (1 – TI)
  • a  * [ 1- TIMC/2 * (1 – TI)] = TIMC * [o + v/2 + (b – e )/2 * TI]
  •  = TIMC * [o + v/2 + (b – e )/2 * TI] /[ 1- TIMC/2 * (1 – TI)]

 

Une fois que a est connu, c, d et f se calculent aisément. Bien sûr, pour cela, il faut être un brin à l’aise avec l’algèbre!

 

Application de l’équation

Voici un exemple de résultat obtenu à partir de l’équation démontrée ci-dessus.

Calcul de référence circulaire dans Excel

 


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 avec la modélisation financière dans Excel, nous vous recommandons notre formation  Excel – Modélisation financière (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Jean-Philippe Provost
Écrit il y a 1 mois
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 6 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