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 :

Patrice Grimard
Écrit il y a 2 semaines
La formation est bien structuré et efficace

Elle permet de bien comprendre la base de la Modélisation financière en utilisant les outils Excels pertinents pour effectuer le type d'analyse souhaitée.

Mathieu Poliquin
Écrit il y a 3 mois
Bonnes pratiques et des méthodes qui me permettront de voir des gains de performance

Super pertinent. Malgré que je suis déjà de l'expérience en modélisation financière dans Excel, j'arrive tout de même à prendre des bonnes pratiques et des méthodes qui me permettront de voir des gains de performance concret et applicabe rapidement. Merci beaucoup

Jean-Philippe Provost
Écrit il y a 5 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 9 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.

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 e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top