Modélisation du financement de projet de A à Z – Edward Bodmer – Financement de projet et d'entreprise (2024)

Un cours en ligne sur la modélisation du financement de projets est présenté à travers des vidéos, des fichiers Excel et des captures d'écran sur cette page Web. Ce cours de modèle de financement de projet en ligne est structuré de manière à inclure à la fois la théorie et la pratique où le financement de projet et/ou la théorie et/ou la comptabilité de la modélisation sont discutés avant de résoudre les problèmes de modélisation technique. Le cours couvre des sujets avancés liés à l'amortissem*nt, aux frais de développement, à la structuration de la dette, aux références circulaires, aux problèmes de dette multiples et aux complications de la sculpture et de la DSRA. Le cours est présenté ci-dessous avec une série de vidéos et de fichiers associés ainsi qu'un ensemble de diapositives power point. Vous pouvez regarder les fichiers complétés ou vous rendre sur des pages où vous devez entrer diverses formules clés dans le fichier excel pour vous familiariser avec l'analyse.

Si vous débutez dans la modélisation de financement de projet et que vous n'avez pas encore réalisé de modèle de financement de projet, les fichiers et vidéos ci-dessous sont destinés à vous permettre de voir la structure de financement de projet, l'importance de la mécanique du timing, le caractère essentiel de la séparation des flux de trésorerie d'exploitation du financement des flux de trésorerie et d'autres problèmes, y compris les cascades de flux de trésorerie. J'ai vu des gens essayer d'apprendre la modélisation en copiant diverses techniques à partir de grands modèles réels. Il s'agit d'un B.S. complet. façon d'apprendre la modélisation. Les modèles sont souvent erronés, et pire que tout, les jeunes perdent du temps sur le format des modèles sans en comprendre les principes clés. L'ensemble de vidéos et de fichiers associés vous présente également certains problèmes de financement importants et difficiles liés à la levée de fonds pendant la construction, l'évaluation de la taille de la dette à l'aide de différentes approches, la simulation de différents remboursem*nts et refinancements et la mesure de l'effet des écarts de crédit et de la couverture des taux d'intérêt. Dès que vous commencerez à modéliser la dette, vous vous heurterez à un mur à cause de toutes sortes de références circulaires qui peuvent gâcher l'analyse. Au lieu d'utiliser des macros copier-coller typiques après avoir touché le mur, le cours en ligne ci-dessous utilise une approche de fonction avancée définie par l'utilisateur pour résoudre les références circulaires. L'utilisation du FDU est la clé de l'analyse avancée de la structuration du financement de projet.

.

Instructions pour télécharger des fichiers avec des macros

.

Apprendre la modélisation du financement de projet et TLNV ou TLNR

Si vous voulez apprendre la modélisation du financement de projet en une demi-heure, vous pouvez construire une sorte de modèle brut d'un projet solaire. Cela peut suffire pour votre entretien ou pour une simple analyse d'un projet. Mais vous n'aurez aucune compréhension approfondie des nuances qui apparaissent à la fois dans la théorie du financement de projet et dans les modèles de financement de projet associés. Si vous voulez devenir un bon modéliste, vous avez besoin de patience et vous ne pouvez pas regarder quelques vidéos de cinq minutes depuis votre téléphone dans le métro (car vous ne pouvez pas apprendre à partir de grands modèles réels). En raison des complexités de la modélisation et de la compréhension de la théorie, les vidéos présentées ci-dessous sont longues. Beaucoup de gens me parlent de Too Long Not Read (TLNR) ou peut-être du cousin Too Long Not Viewed. C'est parce que je vous emmène à travers un modèle de fiancé de projet complet.

Après avoir publié mon passionnant roman de 634 pages sur la modélisation du financement de projets, j'ai réalisé de nombreuses vidéos pour accompagner les sujets du livre. Je n'ai pas fait les vidéos de manière très professionnelle - trop d'aboiements de chiens dans les vidéos, de la musique en arrière-plan et trop de jurons. Plus important encore, les vidéos étaient difficiles à suivre, elles n'avaient aucune véritable théorie sur les raisons pour lesquelles vous suivez diverses procédures et elles n'avaient pas d'exemples de ce qu'il ne fallait pas faire. J'ai reçu des commentaires sur la musique de fond, la qualité, le manque de professionnalisme, etc. Alors maintenant, j'ai commencé lentement à faire un ensemble de vidéos révisées. Mon ami Fredecrico du Fonds vert pour le climat m'a dit que c'était Sh….

Il y a quelques fichiers qui sont nécessaires ou utiles si vous êtes assez courageux pour travailler sur l'ensemble de leçons. Un fichier est un fichier PowerPoint auquel il est fait référence dans les vidéos et qui documente les techniques de modélisation. Le deuxième fichier est le fichier d'exercice Excel qui accompagne le cours en ligne où vous pouvez essayer d'entrer des formules pour les lignes vides. Vous pouvez télécharger ces fichiers en cliquant sur les boutons ci-dessous.

Diapositives Power Point qui accompagnent l'analyse de A à Z de la modélisation du financement de projet et le cours en ligne

Fichier Excel pour le cours de financement de projet de A à Z qui comprend des exercices terminés et vierges abordés dans la vidéo

Les quatre premières parties du cours n'incluent même pas la dette, mais passent en revue la modélisation financière, la chronologie et la structure d'un modèle qui sont des idées essentielles.

Introduction au cours, principes de modélisation et fonctions clés

La première partie du cours passe juste en revue certains principes de modélisation d'être paresseux avec des formules Excel ; examiner certaines mauvaises pratiques dans d'autres modèles et discuter d'une théorie. J'ai également inclus quelques fichiers que vous pouvez utiliser pour prouver si certaines pratiques rendent un fichier "lourd". Un fichier « lourd » peut être trop volumineux ou trop lent. Dans le fichier que vous pouvez télécharger en cliquant sur le bouton ci-dessous, vous pouvez tester si une pratique Excel consistant à cliquer sur une ligne entière ralentit Excel ou agrandit votre fichier. La réponse est que l'utilisation d'une ligne ou d'une colonne entière avec RECHERCHE et/ou INDEX ne le fait pas. Les fichiers associés à la vidéo qui prouvent les problèmes de taille et de temps peuvent être téléchargés en appuyant sur les boutons ci-dessous.

Fichier Excel qui teste la taille et la vitesse de la fonction de recherche par rapport à INDEX et MATCH avec une entrée de lignes entières

Fichier Excel qui teste la taille et la vitesse de la fonction SUMIF avec et sans utiliser l'entrée de lignes entières

Fichier Excel qui teste la taille et la vitesse de la fonction RECHERCHE avec et sans utiliser l'entrée de lignes entières

Je crée ces exercices pour un cours que j'enseigne afin que les participants puissent réviser les sujets et que les participants puissent tester leurs connaissances. La personne très sympathique qui a organisé le cours m'a également demandé d'inclure certains comportements de modélisation en financement de projet qui sont dégoûtants. J'ai également discuté de ces pratiques plus en détail sur la page A-Z qui est liée à cette phrase. La seule fonction Excel à utiliser dans le premier exemple est la fonction RECHERCHE (RECHERCHEV et RECHERCHEH sont illégales). En français, cette fonction est RECHERCHER. J'ai inclus une fiche avec les mauvaises pratiques afin que vous puissiez les corriger.

Crimes et délits élevés dans les modèles de financement de projets (nous avons tous été coupables)

Toutes les sociétés de modélisation financière semblent avoir leurs acronymes et règles bla, bla, bla. Pour ce que ça vaut, voici quelques uns de mes commentaires (pas forcément des règles). Je ne vous dis pas quoi faire ; Je vous dis ce qu'il ne faut pas faire et l'objectif est de rendre les modèles plus faciles à comprendre et à utiliser. Je vous dis cela parce que j'ai fait toutes les erreurs et j'ai volé à la fois les mauvaises et les bonnes idées de nombreux autres modèles. Après avoir fait des erreurs, je crois que ces choses fonctionnent vraiment. Mais l'une de mes plus grandes plaintes est de prêter trop d'attention aux règles et d'appliquer les soi-disant règles de bonnes pratiques.

• Évidemment, pas de nombres codés en dur après les hypothèses dans le modèle. C'est un crime clair et un crime encore pire consiste à mélanger une formule avec des nombres codés en dur comme F5 * 1.02. Le premier peut être résolu avec F5, collage spécial, constantes ou avec des macros génériques.
• Ne pas mettre les facteurs déterminants dans la colonne de gauche.Pourquoi diable les gens mettent-ils les sommes dans la colonne de gauche au lieu des facteurs qui conduisent les équations. Il serait beaucoup mieux de montrer les facteurs qui alimentent le modèle.
• Ne pas rendre les formules identiques d'une colonne à l'autre, une autre évidente.Vous pouvez rendre les formules identiques dans toutes les colonnes avec les indicateurs TRUE/FALSE ou 1/0, ou peu importe comment vous les appelez.
• Perdre du temps sur des couleurs significatives ou dénuées de sens est l'une des choses les plus irritantes que je dois te mettrep avec quand je regarde les gens faire un modèle dans excel. C'est pourquoi j'ai réalisé le coloriage qui permet de retrouver l'emplacement des autres feuilles et d'arrêter de perdre du temps.
• Utilisez IF au lieu des fonctions MAX et MIN pour connecter le flux de trésorerie avec le calendrier de la dette.Il y a toujours un lien direct ou indirect entre l'échéancier de la dette et le bilan — aucun flux de trésorerie et aucun remboursem*nt de la dette. Les déclarations MIN et MAX (jamais IF) doivent figurer dans la cascade des flux de trésorerie et non dans l'échéancier de la dette.
• Mettre les drapeaux, les masques, les commutateurs (comme vous voulez les appeler) dans une page séparée au lieu de l'endroit où ils sont utilisés juste à côté des calculs.Pourquoi forcer les gens à faire des traces idiotes avec ces flèches bleues pour trouver les éléments qui pilotent le modèle (même s'ils ont un complément sophistiqué).
• Placer les états financiers prévisionnels au début du modèle.La structuration est le plus gros problème de FAST et d'autres acronymes bla, bla, bla. Vous devriez le modèle avec les flux de trésorerie avant impôt, les flux de trésorerie après impôt, les sources et utilisations récapitulatives, le dimensionnement de la dette, les échéanciers de la dette avec remboursem*nt et intérêts, puis, enfin, les profits et pertes et l'état des flux de trésorerie (le flux doit être naturel ).
• Divisez les pages lorsque vous concevez le modèle au lieu de laisser le modèle couler en douceur sur une seule page.Je pense même que vous devriez laisser le modèle sur une seule page après avoir terminé et ne pas le diviser. Veuillez vous mettre à la place de quelqu'un qui essaie de comprendre le modèle. Votre modèle doit avoir un flux naturel et c'est tellement much plus facile de suivre un modèle sur une seule page. Utilisez les petites colonnes à gauche pour voir la structure du modèle.
• Te tromperrassurez-vous que vous êtes malin car vous faites une formule longue et complexe.L'utilisation de formules trop longues est inexcusable et toujours évitable. La clé pour rendre les modèles transparents est de séparer les formules et de les raccourcir.
• Montrez vos prouesses Excel en appliquant des fonctions inutiles.Vous pouvez à peu près tout faire avec trois fonctions Excel - RECHERCHE, INDEX et SUMIF. LOOKUP est plus efficace et plus facile à utiliser que INDEX, MATCH et OFFSET est inutile. Lorsque vous utilisez RECHERCHE, veuillez cliquer sur les lignes entières plutôt que de perdre du temps à réparer les cellules.
• Pas de formules au bilan.Lorsque vous créez un modèle, tous les soldes de la dette, DSRA, l'usine nette et d'autres éléments doivent figurer dans diverses sections structurées du modèle. Le seul solde qui n'existe pas dans votre modèle est le solde des capitaux propres, que vous devez calculer après les formules du bilan plutôt que des liens vers le solde de clôture
• N'exagérez pas pour montrer vos compétences en audit.Utilisez les commutateurs TRUE/FALSE pour vérifier le bilan, les soldes de la dette et les références circulaires, mais vous n'avez pas besoin d'aller trop loin.
• Ne polluez pas vos modèles fondamentaux avec plusieurs scénarios.Mettez les scénarios dans une feuille séparée (sauf P99 ou P50) et laissez la feuille de scénario être un gâchis avec la fonction d'index et les numéros de code.
• Ne compliquez pas les fiches récapitulatives. Une règle simple est que les feuilles de sortie peuvent être supprimées sans affecter le modèle. Il est assez évident que vous ne voulez pas mélanger les sorties et les entrées. Mettrevotre résumé sur une seule page pour que vous n'ayez pas à regarder autour de vous. Inclure les IRR, DSCR, LLCR, un graphique des flux de trésorerie par rapport au service de la dette, un résumé des sources et des utilisations et des facteurs clés tels que le facteur de capacité, le coût par kW, le coût d'exploitation et de maintenance actualisé et la capacité de l'usine. Ensuite, vous pouvez même l'appeler un tableau de bord si vous en avez besoin.
• N'utilisez pas la formule NPV ou XNPV lorsque les taux d'intérêt changent.Utilisez un facteur composé lorsque les taux d'intérêt ne sont pas constants, puis calculez la valeur actuelle avec la fonction SOMMEPROD où vous divisez le flux de trésorerie par le facteur composé.
• Mettre quarante drapeaux en haut d'une feuille séparée craint.Vous avez besoin d'une phase de construction et d'une phase d'exploitation en haut de votre feuille unique. Mais n'en mettez pas trop en haut d'une feuille séparée (cela sera très controversé pour les dushbags qui croient suivre une sorte de normes de bonnes pratiques comme SMART).
• Trouvez un bon style et ne m'écoutez pas.J'aime la convention des nombres positifs. J'aime l'utilisation de soulignements pour les éléments clés. J'aime utiliser de fines colonnes à gauche.
• Prenez le parti des Anti-Range Namers.La controverse sur le nom de la gamme est un sujet lors des soirées Excel. Il ne devrait pas y avoir de dispute. Sauf pour les macros, le nom de la plage est nul. Certaines entreprises essaient de vous faire mettre des noms de gamme dans une cellule Excel. Cela ajoute un journal de temps et si vous avez des formules simples, cela n'ajoute rien.
• Ne chimpez pas sur les sous-totaux dans la cascade des flux de trésorerie.Les sous-totaux sont importants dans les cascades de flux de trésorerie. Reliez l'échéancier de la dette aux sources et aux utilisations ainsi qu'à la cascade de flux de trésorerie. Pourquoi inciteriez-vous les gens à rechercher des liens basiques et évidents.
• Lorsque vous utilisez l'outil de recherche d'objectif, utilisez la VAN = 0 plutôt qu'une différence IRR égale à zéro.Lorsque le TRI ne peut pas être calculé, la recherche de but peut devenir folle comme nous le savons tous. Comme vous le savez, lorsque la VAN est nulle, le TRI est le taux d'actualisation. Il est préférable d'utiliser la règle NPV.
• Ne calculez pas le TRI du projet à partir de CFADS.C'est une erreur fondamentale de la théorie financière ; calculez plutôt le TRI du projet avant que toute équation financière ne soit dans le modèle.
• Travaillez sur les lignes bleues cauchemardesques à partir de références circulaires en dernier.Lorsque vous travaillez sur votre modèle, essayez d'éviter les références circulaires en utilisant par exemple l'EBITDA au lieu du DSCR et les dépenses de construction au lieu des utilisations totales du financement.

Partie 1 : Échéanciers et consolidation des périodes périodiques dans les relevés annuels ou trimestriels

Développer des dates et des échéanciers et mettre les échéanciers en haut de chaque feuille est une partie standard de la modélisation financière et est inclus dans toutes les anciennes classes de bla bla bla que vous pouvez obtenir ailleurs. Les formules pour établir des dates et des commutateurs (ou des drapeaux ou des masques ou tout ce que vous voulez les appeler) sont très standard. Un problème un peu délicat est de faire attention à l'analyse annuelle lors du passage des flux de trésorerie mensuels aux flux de trésorerie semestriels.

Pour l'année fiscale utilisée dans la page annuelle, vous voulez trouver la fin du mois avant le COD. Vous pouvez simplement utiliser la fonction EOMONTH et entrer -1 pour le mois - EOMONTH(COD,-1). Trouvez ensuite le mois de cette date à l'aide de la fonction MOIS. Une fois que vous avez la fin du mois de l'exercice, entrez un commutateur qui est vrai chaque fois que le mois de fin dans la chronologie principale est égal à ce mois. Vous devez créer l'année de la date avec la formule selon laquelle l'année change lorsque le mois fiscal est atteint. Pour ce faire, utilisez une formule IF - IF(MONTH(end date in time line) = Month End défini ci-dessus, Year = year +1, an precedent year). Cela crée un compteur d'année qui s'incrémente chaque fois que la fin de l'année fiscale est vraie. Le processus est illustré dans les deux captures d'écran ci-dessous. La première capture d'écran est vraiment la clé. Notez que la date d'opération commerciale est le 1er août 2023. Vous devez trouver le mois précédent (y compris si le mois se trouve être décembre). Ce mois peut être utilisé pour piloter la fonction SUMIF. Notez qu'au lieu d'utiliser la fonction MONTH(COD) – 1 qui ne fonctionnerait pas pour janvier ou décembre, vous pouvez utiliser le EDATE avec -1 pour le mois, puis mettre le mois autour du EDATE. La formule est MOIS(EDATE(COD,-1)).

La deuxième partie de cette méthode est illustrée dans la capture d'écran ci-dessous. Dans cet exemple, la date COD utilisant l'analyse mensuelle standard est le 1er mai 2018. Cela signifie que la fin de l'année fiscale sera avril. Mais lorsque le modèle passe en mode semi-annuel, le mois d'avril est dans la date de fin et non dans la date de début. Mais vous voulez que le changement pour une nouvelle année commence après la date de clôture. Vous devriez donc prendre le mois de clôture PRIOR et l'utiliser dans le test. Une fois que vous avez fait tout cela, vous pouvez utiliser la fin de l'année fiscale calculée plutôt que l'année civile calculée à partir de la date de début ou de fin. L'année fiscale est calculée à partir de l'année fiscale précédente et en incrémentant l'année lorsque le commutateur est VRAI.

Section 2 : Volumes et capacité et utilisation de la fonction d'interpolation

Dans un modèle, qu'il s'agisse d'un modèle agricole, d'un modèle d'usine de papier hygiénique à Jamica, d'un modèle d'aéroport ou d'un projet d'électricité, ne commencez pas avec quelque chose qui a de l'argent (Euro, USD, CFA, etc.) Au lieu de cela, commencez avec la capacité et les volumes et ce que fait réellement la machine ou le projet. C'est l'objet de la deuxième partie du cours.Dans cette section, la fonction INTERPOLER UDFest présenté pour illustrer comment faire augmenter ou diminuer progressivement les volumes, le facteur de capacité ou d'autres éléments (un moyen simple d'interpoler n'est pas dans Excel). Vous pouvez télécharger un fichier avec la fonction d'interpolation ci-dessous afin de pouvoir ajouter la fonction à n'importe lequel de vos modèles et interpoler très rapidement.

Télécharger le fichier Excel avec la fonction d'interpolation en utilisant la croissance composée ou l'interpolation linéaire

Les problèmes liés à la non-utilisation de la recherche ou de l'interpolation_recherche sont illustrés dans la capture d'écran ci-dessous. Dans ce cas, quelqu'un a pensé qu'il était vraiment cool d'utiliser MATCH et INDEX ensemble. Mais cela ne fait que créer un long gâchis. Il est bien préférable d'utiliser la fonction RECHERCHE avec une ligne entière.

Section 3 : Flux de trésorerie d'exploitation avant impôt

La troisième partie du cours commence par quelques équations impliquant de l'argent. Dans tout projet d'un PPP pour une université, une route à péage, une centrale de cogénération ou tout projet d'électricité, vous devriez commencer par l'EBITDA et les dépenses en capital (et peut-être le fonds de roulement). Ces éléments définissent le TRI projet avant impôt du projet. Avec le TRI projet avant impôt vous disposez des informations essentielles sur le projet. Dans cette page, j'utilise l'IRR même si je pense qu'il y a un certain nombre de problèmes avec l'IRR. Pour voir une discussion sur les problèmes avec l'IRR, vous pouvez aller sur ma page séparée qui traite du sujet.

Section 4 : Amortissem*nt et taxes pour calculer le TRI du projet après impôt

La deuxième série de vidéos complète le modèle sans dette et commence à ajouter de la dette aux modèles. La dernière vidéo de cette section commence par ajouter un modèle de résolution circulaire au modèle. C'est une idée importante en financement de projet où l'on souhaite conserver de la souplesse face aux références circulaires naturelles.

En ajoutant l'amortissem*nt continu sur les dépenses en capital futures, j'ai inclus une fonction qui vous permet de sélectionner la ligne complète des dépenses en capital futures et des durées de vie restantes. Avec ces éléments de données et la durée de vie de l'équipement, vous pouvez déduire l'amortissem*nt des dépenses en capital futures. L'amortissem*nt futur est soit issu de l'amortissem*nt standard si la durée de vie est supérieure à la durée de vie restante. Alternativement, il est calculé à partir de la durée de vie restante lorsque la durée de vie restante est inférieure à la durée de vie de l'actif. Un fichier contenant la fonction est disponible en téléchargement en appuyant sur le bouton ci-dessous (notez qu'il faut appuyer sur Shift, CNTL, Enter au lieu d'enter et vous ne pouvez pas utiliser toute la ligne). Le fichier contient également une fonction plus flexible qui simule l'amortissem*nt dégressif variable.

Feuille de calcul comprenant des fonctions de calcul de l'amortissem*nt sur les dépenses en capital futures (utilisez ALT F8 pour transférer)

Les captures d'écran ci-dessous illustrent l'utilisation de la fonction avec un amortissem*nt linéaire. La première capture d'écran montre comment fonctionner dans le cas où la durée de vie restante est supérieure à la durée de vie de l'actif et où l'actif peut être entièrement amorti sur la durée de vie restante. Dans ce cas, les dépenses en capital ont lieu dans l'année 20 et la durée de vie restante est de 34 ans. Notez que deux dépenses en capital sont saisies et que l'amortissem*nt se stabilise à un niveau de 150. Après le retrait du premier actif dans la période 24, l'amortissem*nt tombera à 100. La deuxième capture d'écran montre le cas où la durée de vie restante est inférieure à l'actif vie. Dans ce cas, l'amortissem*nt est calculé sur la durée de vie restante de l'actif. Les entrées de cette fonction incluent la durée de vie restante, le tableau des dépenses en capital et la durée de vie de l'actif, comme indiqué dans les deux captures d'écran.

La vidéo ci-dessous passe en revue divers amortissem*nts et problèmes liés aux futures dépenses en capital et explique pourquoi le problème des futures dépenses en capital et de l'amortissem*nt serait très compliqué sans fonction. Vous auriez à créer une de ces matrices diagonales et à ajuster les situations où l'amortissem*nt se prolongerait au-delà de la durée de vie du projet.

.

.

Le code VBA de la fonction d'amortissem*nt est disponible en téléchargement ci-dessous. Comme il s'agit d'une fonction, vous devez copier le code dans votre modèle plutôt que d'avoir simplement le fichier avec le code VBA ouvert (avec les anciennes macros habituelles, vous pouvez le faire). Vous pouvez obtenir cette fonction avec le code VBA dans votre modèle de financement de projet de trois manières différentes :

1. La première consiste à ouvrir le fichier ci-dessus et à appuyer sur ALT, F8. Modifiez ensuite la page VBA avec la sous-routine nommée A_Depreciation_functions. Copiez tout le contenu. Après cela, accédez à votre feuille et appuyez à nouveau sur ALT, F8. Créez ensuite une sous-routine VBA vierge et copiez le contenu au-dessus de cette routine.
2. La deuxième façon consiste à copier la fonction qui se trouve sur le formulaire qui apparaît lorsque vous ouvrez le fichier. Copiez simplement le code, accédez à votre feuille, appuyez sur ALT, F8, créez une macro factice et collez.
3. La troisième méthode est similaire à ci-dessus, mais vous copiez simplement le code ci-dessous.

Option Base 1''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' ''''''' Fonctions d'amortissem*nt''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''Sub A_Depreciation_Functions()End SubFunction depreciation_remaining_life_2(capital_expenditure, resting_life , max_life) As Variant ' Lorsque la sortie est un tableau définir comme Variantcap_exp_periods = capital_expenditure.Count ' Voir combien de périodes de dépenses en capital sont modéliséesDim Depreciation_Expense(5000) As Single ' Créer une nouvelle variable de tableau qui est la outputDim restant_life1(5000) As Single ' Déterminer s'il faut utiliser la durée de vie restante ou quelque chose de plus court Pour millésime = 1 To cap_exp_periods ' faire une deuxième boucle pour évaluer actif par actif Si la vie_restante(vintage) >= la vie_max Alors la vie_restante1(vintage) = la vie_maxElse la vie_restante1(vintage) = la vie_restante(vintage)End IfNext vintageFor model_year = 1 Pour cap_exp_periods ' boucler autour de chaque période et faire un carré avec des colonnes et des lignes For vintage = 1 To cap_exp_periods ' faire une deuxième boucle pour évaluer l'actif par l'âge de l'actif = model_year - millésime + 1 ' calculer l'âge de chaque dépense (la diagonale) Si (âge > 0 Et la vie_restante1(ancien) <> 0 Et la vie_restante1(ancien) >= âge) Alors ' Uniquement lorsque l'actif est en vie Depreciation_Expense(model_year) = _ capital_expenditure(vintage) / resting_life1(vintage) + Depreciation_Expense (model_year) End If Next millésime ' Notez que le millésime est utilisé pour la dépense en capital Next model_yeardepreciation_remaining_life_2 = Depreciation_ExpenseEnd Function.

Section 5 : Bilan et vérification

Le bilan est un bon moyen (mais pas parfait) de vérifier votre modèle. Il y a quelques années, un élève de ma classe m'a dit qu'il met un bilan dans le modèle très tôt dans le processus. Au début, je n'en voyais pas la valeur. Avec des modèles complexes et la résolution de références circulaires, je pense que cette suggestion était tout à fait pertinente. A tel point que j'ai mis un test tout en haut du modèle et croyez le dicton sur le t-shirt ci-dessous de mon ami Hedieh (vous pouvez commander ce t-shirt ou un mug si vous le souhaitez. Il suffit d'envoyer un e-mail à edwardbodmer@gmail.com).

Vous pouvez placer le bilan dans le modèle après avoir modélisé la charge d'amortissem*nt. Le bilan doit simplement recueillir les soldes de clôture des autres sections du modèle. La vidéo ci-dessous décrit le processus d'équilibrage du bilan avant que toute dette ne soit introduite dans le modèle.

Section 7 : Modélisation de situations plus complexes avec de multiples problèmes d'endettement, découpage et références circulaires inévitables

Une fois que vous avez structuré le modèle sans dette et sans références circulaires, vous pouvez commencer à entrer la dette dans le modèle. Si vous structurez la dette - en trouvant le montant de la dette, en calculant différents remboursem*nts de dette, en essayant différentes méthodes de financement de la dette, en mettant en place différents flux de trésorerie ou même en modifiant les frais et les taux d'intérêt, vous rencontrerez très probablement des références circulaires. D'un point de vue théorique, la circulaire apparaît parce que le modèle pilote la négociation des conditions de la dette, mais les conditions de la dette conduisent le modèle. Par exemple, le modèle détermine le montant des intérêts pendant la construction et est nécessaire pour évaluer la dette totale. Mais le pourcentage de la dette totale est déterminé par la feuille de conditions. En expliquant la structure des tirages de dette, des remboursem*nts de dette et des intérêts dans un modèle de financement de projet, je vous suggère de créer le modèle sans références circulaires. Le fichier que vous pouvez télécharger ci-dessous qui accompagne la discussion n'a pas de références circulaires - j'ai juste laissé des blancs pour des choses comme les flux DSRA, l'IDC accumulé et l'effet fiscal des intérêts sur le CFADS.

Configuration des prises en charge de la dette et des échéanciers de la dette

J'ai mis beaucoup de temps à ajouter les problèmes de dette à cette leçon de A à Z. Lorsque vous ajoutez de la dette à un modèle de financement de projet, vous rencontrez des problèmes de référence circulaires. Ces références circulaires proviennent à la fois de problèmes de financement et de remboursem*nt. En travaillant sur les différents sujets de structure de la dette, je montre comment mettre en place le modèle sans références circulaires. Ensuite, dans la section suivante, je vous montre comment utiliser les UDF qui vous permettront de vérifier votre travail et de créer de jolis tableaux de bord flexibles. J'espère que vous cesserez d'être un copier-coller dans tous les domaines de votre vie.

Avant d'aborder les différents sujets de financement mécanique par emprunt, vous devriez télécharger les deux fichiers que j'utilise pour cet exercice. Je suis désolé, mais ces fichiers sont différents des fichiers ci-dessus car j'ai essayé de me concentrer sur des problèmes délicats de dimensionnement de la dette. Ces fichiers reliés aux boutons ci-dessous utilisent le cas d'un parc éolien avec différentes possibilités de dimensionnement de la dette (de P99, P50 et dette au capital), de multiples problèmes de dimensionnement de la dette et de multiples possibilités de refinancement. Vous pouvez télécharger les fichiers utilisés pour réaliser les exercices et une version complète des fichiers en cliquant sur les boutons ci-dessous.

Vidéos sur la structuration de la dette

Structure des entrées et hypothèses

La manière dont vous pouvez définir des hypothèses lorsque vous avez plusieurs problèmes de dette est illustrée dans la capture d'écran ci-dessous. Vous devez comprendre que certains paramètres s'appliquent à tous les problèmes de dette. Vous ne pouvez pas avoir plus d'un ratio global de couverture du service de la dette (vous pouvez avoir un DSCR senior et un DSCR total — pas un DSCR subordonné). Vous ne pouvez pas avoir plus d'un ratio global d'endettement sur capital. Le DSRA qui protège les prêteurs porte généralement sur le service global de la dette. D'autres paramètres tels que le taux d'intérêt, la durée de la dette et le modèle de remboursem*nt de la dette peuvent être définis sur la base d'une émission de dette individuelle. La capture d'écran ci-dessous illustre comment vous pouvez configurer les hypothèses de dette avec un seul problème de dette. La première capture d'écran illustre comment il peut y avoir plusieurs contraintes globales de dimensionnement de la dette et comment le financement de la dette est déterminé par un paramètre de financement initial par actions.

La capture d'écran ci-dessous illustre les entrées et les hypothèses qui doivent être saisies problème par problème. La première entrée correspond au pourcentage de la dette totale représenté par l'émission de dette. J'ai étiqueté ce problème comme un problème de non capture. En effet, s'il y a plusieurs problèmes de dette et de sculpture, il doit y avoir un problème qui capture le reste du service de la dette et garantit que le service total de la dette est couvert. Je trouve toute l'idée de la sculpture pour les problèmes de dettes multiples complexe et j'ai inclus une page distincte qui traite du sujet de la sculpture des problèmes de dettes multiples. La durée de la dette est entrée par émission et si la dette avec la durée la plus longue devrait généralement être l'émission de capture de la dette. Vous pouvez entrer une période de grâce près du mandat et vous pouvez mettre des dates précises pour le début et la fin du remboursem*nt. Notez que j'ai utilisé le fichier de macros génériques pour colorer les différentes choses (veuillez essayer ceci). Comme pour ma philosophie générale, j'espère que vous garderez la structuration des problèmes de dette cohérente - la taille, le financement, le remboursem*nt, le taux d'intérêt et les frais et les protections.

Structure de la taille de la dette et résumé des sources et utilisations des fonds

Une fois les entrées établies, je suggère de structurer l'ensemble du modèle sans références circulaires, ce qui signifie que vous saisissez les titres et effectuez divers calculs. Structurer l'ensemble du modèle sans les références circulaires signifie que vous pouvez laisser certains éléments comme IDC dans les sources périodiques et utilise des fonds vides. Vous pouvez également faire des erreurs exprès, comme ne pas soustraire les impôts dans le calcul du CFADS. Dans d'autres cas, comme pour le TRI de la dette, vous pouvez simplement entrer un nombre fixe.

Passons maintenant à la modélisation de la taille de la dette, du financement de la dette, du remboursem*nt de la dette, des intérêts et des frais et des protections de crédit. D'une manière ou d'une autre, je vous suggère de mettre un état récapitulatif des sources et utilisations des fonds avec le montant total sur la période de construction. Si vous configurez votre modèle, vous ne pourrez pas entrer l'IDC (I Don't Care, ou intérêt pendant la construction), le DSRA et les frais. Vous ne pourrez généralement pas non plus saisir le montant de la dette. Mais entrez simplement un espace réservé pour la dette et laissez l'IDC, le DSRA et les frais vides. Ensuite, vos sources et utilisations récapitulatives des fonds devraient ressembler à la capture d'écran ci-dessous. Il s'agit de la première étape de l'analyse et celle-ci est liée au dimensionnement de la dette. Notez comment j'ai mis des options pour le dimensionnement de la dette de la dette au capital ou de la dette d'entrée ou de la sculpture à droite du montant de la dette et celles-ci ne sont pas encore remplies. Le fait que ces éléments ne soient pas encore entrés devrait vous inquiéter des références circulaires. Lorsque vous devez descendre pour trouver des choses, les références circulaires sont un problème.

État des flux de trésorerie numéro 1 - Sources et utilisations pendant la construction

Passons maintenant au financement de la dette pendant la construction compte tenu du montant de la dette et des capitaux propres provenant de l'état récapitulatif des sources et utilisations des fonds. J'utilise la méthode Hedieh pour entrer les capitaux propres à l'avance comme variable d'entrée. Je pense que c'est génial. Si vous voulez que tout soit au prorata, vous pouvez entrer zéro comme pourcentage initial des capitaux propres. Si vous souhaitez utiliser tout le financement initial, vous pouvez saisir 100 % pour l'intrant. Bien sûr, vous pouvez entrer n'importe quoi entre. Compte tenu de cet apport pour lequel j'ai utilisé 50 %, ce qui implique qu'une partie du financement est constituée de fonds propres et d'autres au prorata. Dans la capture d'écran ci-dessus, dans la section ci-dessous, les sources et les utilisations des fonds, j'ai calculé le pourcentage de financement provenant de la dette au prorata et des capitaux propres au prorata. Pour effectuer ce calcul, calculez d'abord les 50 % des capitaux propres. Ensuite, vous pouvez déduire le financement total au prorata. Le pourcentage de la dette au prorata est la dette totale divisée par le financement au prorata et les capitaux propres sont le reste.

Une fois que vous avez les pourcentages au prorata, vous pouvez saisir le relevé des sources et des utilisations des fonds période par période. Vous devriez considérer cela comme un état des flux de trésorerie avant COD. Avant COD, il n'y a pas d'EBITDA ni de rentrée de fonds, donc tout consiste à trouver des liquidités pour financer les divers besoins qui incluent les dépenses en capital et peuvent inclure IDC, les frais, le financement DSRA, les coûts de développement, les frais de développement, etc. Un exemple de tableau des flux de trésorerie avant COD (les sources et les utilisations des fonds) est illustré dans la capture d'écran ci-dessous. Attention, les chiffres ne sont pas remplis. C'est encore à cause du cauchemar de la référence circulaire.

Pour calculer le financement initial par capitaux propres ou au prorata, vous pouvez commencer par évaluer les capitaux propres à l'avance. J'ai vu de nombreuses façons de le faire, mais je commence par calculer le montant des capitaux propres restants qui peuvent être utilisés pour financer les capitaux propres qui seront utilisés dans la fonction MIN. Pour ce faire, je calcule d'abord l'engagement total en fonds propres, puis je soustrais le montant des fonds propres déjà utilisés, qui correspond au solde d'ouverture. Ensuite, je calcule le solde d'ouverture et de clôture des capitaux propres. Le montant du financement est calculé avec la fonction MIN où vous calculez le minimum des besoins de financement ou les capitaux propres restants.

Financement par actions = MIN (financement restant, besoins de trésorerie)

Structure des échéanciers de la dette à émissions multiples

Une fois le premier état des flux de trésorerie - les sources et les utilisations des fonds par période - établi, vous pouvez saisir l'échéancier de la dette. L'échéancier de la dette présente simplement le solde d'ouverture, les ajouts et le solde de clôture. Je ne peux pas imaginer un modèle de financement de projet qui n'ait pas d'échéancier de dette. Dans l'exemple ci-dessous, j'ai omis le remboursem*nt qui est abordé dans la section suivante. Comme vous pouvez le voir, l'échéancier de la dette comprend également les taux d'intérêt et les frais avec une séparation des intérêts entre IDC et les intérêts débiteurs. C'est assez standard, à l'exception peut-être du fait que les prélèvements sur la dette sont calculés à partir du pourcentage de la dette totale recueilli à partir de l'état des sources et des utilisations des fonds.

J'ai également inclus une capture d'écran avec le résumé de tous les problèmes de dette ci-dessous. Cela additionne simplement tous les frais d'intérêt, l'IDC et les frais à l'aide d'une fonction SUMIF. Bien sûr, j'applique le principe paresseux et j'utilise le SUMIF avec les colonnes entières de données. Comme pour l'échéancier de la dette, ce n'est qu'un processus long et douloureux. Notez qu'après le calcul du total des frais et de l'IDC total, je n'ai pas placé le montant dans les sources et utilisations des fonds. Lorsque vous mettez l'IDC et les frais dans les sources et les utilisations des fonds, une référence circulaire redoutée se produit.

Structure de remboursem*nt avec plusieurs sculptures

Dans cet exercice de A à Z, j'ai inclus des calendriers de remboursem*nt où plusieurs problèmes de dette sont utilisés pour sculpter. Sur une autre page de la section financement de projet avancé, j'explique ce processus en détail. Veuillez noter que vous pouvez calculer cela en prétendant temporairement que le CFADS est le même que l'EBITDA. Vous changerez cela lorsque vous implémenterez l'UDF ci-dessous. En bref, si vous voulez plusieurs problèmes sculptés qui produisent à la fois le DSCR cible et la répartition correcte de la dette, vous pouvez procéder comme suit :

1. Calculer le montant agrégé de la dette avec le CFADS agrégé en utilisant un TRI de la dette. Pour calculer le TRI de la dette, vous aurez une référence circulaire car le montant de la dette globale est utilisé pour répartir la dette, calculer le service de la dette et finalement arriver au TRI de la dette lui-même.

2. Calculez le service de la dette pour l'émission de la dette capturante. Cette émission de dette capturante est calculée de la même manière que la dette globale, mais le taux d'intérêt sur l'émission de dette est utilisé et le service de la dette sur les autres émissions est soustrait. La capture d'écran ci-dessous illustre le calcul de l'émission de la dette de capture et de la dette globale.

3. Calculez le LLCR pour les émissions de dette sans capture. Pour ce faire, calculez d'abord le solde de la dette associé à l'émission de la dette, où le solde est le montant total de la dette à partir de la VAN du service de la dette globale à l'étape 1. Ceci est multiplié par le pourcentage de dette cible. Une fois que vous avez le solde de la dette pour les problèmes non capturés, c'est le dénominateur du LLCR utilisé pour la sculpture. Le numérateur est la VA du CFADS au taux d'intérêt applicable. Le LLCR affiché dans la deuxième capture d'écran est calculé à l'aide de la formule :

LLCR = NPV(CFADS)/Dette du solde global de la dette * Pourcentage de la dette

4. La dernière partie consiste à calculer le TRI de la dette à partir du service total de la dette. Pour le calculer, additionnez le service de la dette des différentes émissions. Ensuite, mettez la dette totale émise au COD comme investissem*nt négatif. Avec le flux de trésorerie sur la dette, calculez le TRI de la dette. Notez qu'il existe deux références circulaires de ce processus. Le premier est le TRI et le second est le solde de la dette. Nous mettons en place les horaires, alors ne vous en souciez pas encore.

Structure du compte DSRA

Le compte DSRA peut être structuré une fois le service de la dette établi à partir de l'échéancier de la dette. J'avais l'habitude d'être très inquiet au sujet de la DSRA et des références circulaires. J'ai surmonté cette peur en commençant par la structure de la DSRA qui est illustrée ci-dessous. Commencez toujours par le service de la dette, puis dérivez le compte de réserve requis pour le service de la dette. Ceci est illustré dans la capture d'écran ci-dessous. J'utilise souvent la fonction OFFSET, mais je n'ai utilisé que la moitié du service de la dette de l'année suivante dans ce cas. Si vous utilisez la fonction OFFSET, utilisez ajuster la colonne avec un signe négatif pour les périodes que vous souhaitez déplacer du futur comme illustré ci-dessous :

Exigence de service de la dette = OFFSET (service de la dette, 0, - périodes DSRA)

Dans la formule ci-dessus, le zéro au milieu signifie que vous n'ajustez pas les lignes. Après avoir établi le montant de la réserve requise, calculez le montant du financement requis ou de l'extinction autorisée. Il s'agit de l'exigence totale moins le solde d'ouverture du compte DSRA. Enfin, après avoir calculé la réserve requise, calculez le solde du compte DSRA. Il s'agit d'un compte normal avec un solde d'ouverture et de clôture. Lors du calcul du financement requis, séparez le financement entre le financement pendant la construction qui ira dans les sources et les utilisations des fonds (état des flux de trésorerie 1) et dans la cascade des flux de trésorerie (état des flux de trésorerie 2). Ce financement peut être divisé à l'aide des commutateurs VRAI et FAUX.

Dans la capture d'écran, je n'ai pas inclus les revenus d'intérêts, la possibilité d'utiliser une lettre de crédit au lieu d'un DSRA en espèces et enfin, les frais de lettre de crédit. Ces problèmes peuvent être douloureux, en particulier du point de vue des références circulaires. J'ai inclus ces problèmes dans la section sur la modélisation avancée du financement de projet.

État des profits et pertes avec IDC et dépréciation et amortissem*nt des frais

Une fois que vous avez terminé les parties de la dette du modèle, vous devez contourner et calculer le CFADS. Le problème difficile dans le calcul du CFADS est que les impôts, les revenus d'intérêts font partie du calcul du CFADS. Mais ces postes dépendent de la dette qui elle-même dépend du CFADS. Il peut également y avoir un problème avec les changements dans le DSRA qui peuvent potentiellement affecter le CFADS et sont motivés par le DSRA et la dette. Lors de la configuration du modèle pour se préparer à la référence circulaire, vous devez aller jusqu'au tableau des flux de trésorerie où le CFADS est calculé. Cela nécessite le calcul des impôts dans un compte de résultat et la mise en place d'une cascade de flux de trésorerie (état des flux de trésorerie numéro 2).

Lors de la structuration du compte de résultat, vous devez inclure une sorte d'amortissem*nt ou de dépréciation sur les éléments de financement qui ont été capitalisés. J'insiste pour que vous gardiez la charge d'amortissem*nt séparée entre les montants liés au financement et les montants de base associés aux dépenses en capital, aux coûts de développement et à d'autres éléments. L'amortissem*nt de base est nécessaire pour calculer les taxes d'exploitation et si vous mélangez l'amortissem*nt IDC qui est influencé par le financement, le TRI de votre projet sera erroné. Par conséquent, je suggère de mettre l'amortissem*nt sur IDC et les frais près du compte de résultat où vous calculerez les impôts payés après le financement. Ceci est illustré dans la capture d'écran ci-dessous où le taux d'amortissem*nt sur IDC et les frais est supposé être le même que le taux d'amortissem*nt sur les actifs de base.

Le reste du compte de résultat est standard, comme le montre la capture d'écran ci-dessous. L'amortissem*nt de l'IDC et l'amortissem*nt des frais sont inclus dans des éléments distincts. Comme dans de nombreux autres modèles, la perte d'exploitation nette est calculée avec les fonctions MIN et MAX. La fonction MAX est utilisée pour tester si l'EBT est positif ou négatif. La fonction MIN est utilisée pour tester si le solde du NOL peut être utilisé lorsqu'il y a des impôts positifs. La seule chose que cela n'a pas, c'est l'expiration des NOL. Il s'agit d'un problème difficile abordé dans la section finance d'entreprise du site Web.

Cascade de flux de trésorerie et calcul du DSCR

La déclaration finale que vous pouvez développer pour la section de structuration est la cascade de flux de trésorerie et le calcul DSCR. Le DSCR doit être le même que le DSCR que vous avez entré en entrée. Pour éviter les renvois circulaires j'ai commis une erreur exprès et n'ai pas soustrait les taxes dans le calcul du CFADS. Ceci est illustré dans la capture d'écran ci-dessous. Notez que dans la capture d'écran, le DSCR est le même que l'entrée DSCR. La seule chose déroutante est de savoir où mettre les modifications dans DSRA. J'ai mis le changement dans le DSRA après le CFADS pour qu'il n'affecte pas le DSCR et la sculpture et le dimensionnement de la dette. J'aborde la question de l'insertion du DSRA dans le CFADS ou en tant qu'ajustement du service de la dette dans la section avancée. Maintenant, nous devons faire face à des références circulaires.

Section 9 : Utilisation du modèle parallèle/UDF pour résoudre les références circulaires

C'est bien de dire qu'il ne devrait pas y avoir de références circulaires quand on s'endette, mais c'est arrogant. Vous devez créer pas mal de choses qui sont fixes pour éviter les références circulaires même si vous utilisez un modèle d'analyse de crédit. Le reste de ce cours de A à Z traite de sujets qui créent des références circulaires. Si vous ne voulez pas de références circulaires, vous vous heurterez à un mur. Le mur que vous frappez n'implique pas seulement de trouver un moyen de gérer les références circulaires, mais la programmation est plus pénible car vous finirez par monter et descendre beaucoup.

Je travaille depuis quelques années sur un modèle complet qui résout tous les éléments qui provoquent des références circulaires avec des fonctions définies par l'utilisateur. J'ai utilisé le fichier pour illustrer comment vous pouvez résoudre toutes les références circulaires et vous permettre de faire une analyse de scénario efficace. Vous pouvez aller à la section de référence circulaire du site Web pour voir comment cela fonctionne. Veuillez voir clairement que si vous utilisez cette méthode, vous pouvez simplement ajouter le modèle parallèle en quelques minutes.

Exemple de financement de projet qui résout les références circulaires à l'aide de la FDU et du modèle parallèle à partir d'un modèle

Ajout du modèle parallèle

Modélisation de la dette à P99 et des tarifs à P50

Section 10 : Structuration de la dette mezzanine et des prêts d'actionnaires dans le modèle

Inclure la dette mezzanine et les prêts d'actionnaires est principalement pénible. Ces problèmes de dette ou de quasi-dette affectent les impôts et ajoutent à la douleur de référence circulaire. Vous pouvez également avoir une sculpture distincte pour la dette totale qui comprend la dette mezzanine et modéliser une cascade. Dans le cas de la dette des actionnaires, vous ne voulez pas fausser les statistiques de la dette, les balayages de trésorerie ou les autres conditions de la dette par la dette des actionnaires. Le seul effet de la dette d'actionnaire est sur l'impôt et éventuellement sur le coût du projet du fait de l'inscription d'IDC sur la dette d'actionnaire.

Partie 9 : Structurer le refinancement multiple dans le modèle

Saisissez d'abord une émission refinancée et assurez-vous qu'elle fonctionne. Entrez ensuite plusieurs problèmes avec la possibilité de modifier les hypothèses.

C'est comme si vous ajoutiez une feuille à votre modèle, puis utilisiez la fonction Somme ou toute autre fonction qui n'est liée à rien dans votre modèle. C'est le point de départ.

Je n'aime pas les modèles de modèles. Essayez de tout mettre dans le monde. Mais d'accord. Mettez des choses imaginables.

Collectez ensuite quelques intrants pour lesquels vous disposez d'un montant fixe. C'est la seule connexion.

Vous n'avez même pas besoin de vous connecter si vous souhaitez utiliser le modèle parallèle pour la vérification, pour l'analyse de sensibilité et pour les concepts avancés de financement de projet comme la sculpture avec DSRA, l'ajout de paiements forfaitaires, le refinancement, etc.

Combien de minutes faut-il pour mettre en œuvre le modèle parallèle.

Des points:

1. Comment copier le modèle dans votre fichier
2. Utilisation du tableau de la dette pour trouver des entrées pour les problèmes de dette
3. Comprendre où commencer et où finir
4. Comprendre l'option de débogage
5. Comprendre le manuel et les CNTL, ALT, F9
6. L'avantage des feuilles de calcul à partir de visicalc était que vous pouviez tout voir

Points de vente

1. Pas seulement un autre programme VBA
2. Pas comme un programme de vente
3. Le plus grand point est une référence circulaire
4. Les gens résisteront

Résistance

• C'est de la magie et une boîte noire
• C'est comme une macro protégée par un mot de passe
• Ceci est juste un autre gadget complémentaire
• Histoire de l'UDF

La dernière chose que je voulais faire était de programmer un modèle financier

Questions de référence circulaires et problèmes de copier-coller

Implémentation du modèle UDF

Utilisation du calcul manuel et CNTL, ALT, F9

Explication du commutateur de débogage

Copier les problèmes de dette

Interprétation de la sortie

Modification de la structure de sortie

Avantages du modèle parallèle et de la FDU autre que la référence circulaire

• Vérifier le modèle
• Manière cohérente de vérifier
• Compute Sculpting Remboursem*nt dans des situations complexes
• Exécuter des scénarios alternatifs
• Évaluer les problèmes qui peuvent être difficiles à programmer
• Rapports faciles à lire
• Voir les équations pour la sculpture difficile, DSRA et autres problèmes de structuration

Résolution de référence circulaire - Supposons que vous souhaitiez effectuer une analyse où P90 détermine la taille de la dette, mais P50 détermine l'objectif de prix.

Quiconque a travaillé de manière sérieuse sur des modèles de financement de projets devrait savoir à quel point les solutions classiques aux références circulaires - copier-coller ou bouton d'itération - peuvent ruiner un modèle. Le problème avec les macros copier-coller est que votre modèle est soudainement arrêté. Aucun but recherché. Pas de tableaux de données. Pas de simples boutons rotatifs pour la sensibilité. Pas de pages de résumé efficaces où les gens peuvent essayer différents scénarios.

Vérification du modèle

Exemple d'erreurs dans le modèle difficiles à vérifier

Exécution d'options de structuration alternatives sans programmation

Problèmes complexes que vous ne souhaitez peut-être pas programmer, mais faites une analyse rapide

Vous pouvez vérifier le modèle, puis dire que vous voulez faire des choses plus complexes comme :

• Dimensionnement de la dette à l'aide de méthodes alternatives
• Paiements forfaitaires
• DSRA en L/C avec frais
• DSRA se déplace dans CFADS pour le calcul DSCR
• DSRA se déplace dans le service de la dette pour le calcul DSCR
• Problèmes multiples avec la sculpture
• Prêt relais participatif
• Part des capitaux propres au départ et partie au prorata
• Optimisation du DSCR pour la sculpture avec une contrainte de durée de vie moyenne du prêt et un ratio dette/capital

Ajouter/Modifier l'UDF

Mettez la sortie - travaillez à l'envers

Créer une variable publique

Construire un modèle dans excel. Peut être un modèle simple

Répliquez votre analyse Excel

En documentant votre calcul, vous pouvez le vérifier beaucoup plus facilement

Bientôt, vous vous rendrez compte que ce n'est pas très différent de faire un modèle; qui peut être bon ou mauvais.

Vérification de la FDU

Faire des tests en dehors du modèle

Le bilan doit s'équilibrer comme n'importe quel modèle

La dette doit être remboursée comme n'importe quel modèle

Vérification des totaux et des choses comme les sources et les utilisations

Fonctionnement de l'UDF

Tout programme (y compris Excel) a des entrées, des sorties et des calculs

Ces fichiers proviennent du site Web de wikispaces. Je suis en train de télécharger tous les fichiers. Mais en attendant, vous pouvez m'envoyer un e-mail à edwardbodmer@gmail.com pour obtenir la bibliothèque de ressources. Les fichiers seront situés dans la section financement du projet du chapitre 1.

Modèle de miroir circulaire.xlsm

La troisième série de vidéos continue sur les problèmes délicats liés à la dette. La première vidéo de cet ensemble complète le modèle sans dette et commence à ajouter de la dette aux modèles. La dernière vidéo de cette section commence par ajouter un modèle de résolution circulaire au modèle. C'est une idée importante en financement de projet où l'on souhaite conserver de la souplesse face aux références circulaires naturelles.

Si vous remplissez tous les exercices et m'envoyez les fichiers avec une somme modique, je publierai votre nom sur mon site Web afin que vous puissiez le montrer à votre patron ou à votre futur employeur. Je vais également vous obtenir un badge officiel. Une illustration du fonctionnement des modèles est présentée ci-dessous. Les onglets jaunes dans l'extrait indiquent les éléments à remplir. Les feuilles remplies se trouvent à côté des onglets jaunes (j'espère ne pas avoir fait d'erreur).

Exercice de A à Z avec des titres existants sélectionnés pour le modèle solaire

On m'a demandé de préparer un exercice où les gens pourraient rapidement créer un modèle qui évalue les prix des offres et d'autres aspects du modèle et peut vous obtenir la plupart des résultats sans passer trop de temps sur certains des détails horribles qui peuvent rendre la modélisation du financement de projet si difficile. On m'a dit que la création d'un modèle de financement de projet relativement simple ne pouvait pas être enseignée en une seule journée. Ce n'est pas vrai. Vous pouvez voir les éléments essentiels de la création d'un modèle, y compris la description des intrants, l'établissem*nt du flux de trésorerie d'exploitation, le calcul de l'amortissem*nt et du TRI du projet, l'incorporation de la dette, la création d'une cascade de flux de trésorerie et le calcul de certains ratios financiers clés en une matinée.

Le fichier disponible en téléchargement le fait pour un boîtier solaire simple. Pour faire simple je n'ai même pas inclus de période de construction et fait le modèle et le modèle annuel. Pour certains éléments, je n'ai pas inclus de titres afin que vous puissiez faire la partie vraiment importante d'un modèle qui consiste à structurer le modèle. Pour les autres parties, j'ai inclus des titres afin que vous n'ayez pas à perdre beaucoup de temps à taper. L'exercice applique, espérons-le, une partie de la religion de la modélisation rapide, ce qui signifie qu'il est flexible (sauf pour la construction d'une période), il est précis - les soldes du bilan et la dette sont remboursés ; il est structuré, où vous commencez par les opérations physiques, puis passez aux revenus, aux dépenses et aux dépenses en capital, puis à l'amortissem*nt, ce qui vous permet de calculer les taxes d'exploitation et de projeter les flux de trésorerie. Après le flux de trésorerie du projet, vous intégrez la dette avec un relevé des sources et des utilisations des fonds et un échéancier de la dette. Ce n'est qu'après l'échéancier de la dette que vous créez les états financiers. Pour télécharger le fichier, appuyez sur le bouton ci-dessous.

Fichier Excel avec exercice pour créer un modèle de financement de projet solaire de base démontrant les problèmes de structuration

Partie 1 : Données d'exploitation

Dans la première partie, vous accédez aux données d'exploitation. Vous utilisez la fonction INDEX pour sélectionner l'un des scénarios. Mettez également l'onglet développeur dans votre excel et faites une boîte de spinner avec la méthode d'essuie-glace pour utiliser le formulaire dans d'autres feuilles. La première partie est illustrée dans la capture d'écran ci-dessous.

Partie 2 : Chronométrage

Dans la deuxième partie, faites un changement de synchronisation et utilisez la fonction EDATE (FETCHA.MOIS) pour calculer les dates annuelles. J'ai mis quelques résultats récapitulatifs en haut de la page. Utilisez ALT et -> pour regrouper les lignes et choisir de ne pas afficher les sorties. En anglais, vous pouvez utiliser ALT, E, I, S pour lancer le modèle pendant 40 ans. Avec les MACROS GÉNÉRIQUES ouvertes, vous pouvez utiliser la combinaison ALT, S. Les parties de synchronisation du modèle sont présentées sur la capture d'écran ci-dessous.

Partie 3 : Définir les entrées

Après avoir configuré la chronologie, extrayez les entrées pertinentes du modèle de la page d'exploitation et de la page de la dette. Les entrées doivent être discutées et vous devriez voir comment changer les scénarios avec votre spinner box. Vous devez comprendre que les intrants sont disposés dans un ordre approprié qui sépare l'exploitation du financement ; qui commence par des opérations physiques ; qui inclut des moyens de revenir sur le prix du contrat et qui inclut une différenciation logique des parties de la dette du modèle. Lorsque vous avez lié les sorties, utilisez la séquence CNTL, ALT, C des MACROS GÉNÉRIQUES pour montrer l'emplacement des entrées et pour illustrer la structure du modèle. La section d'entrée est illustrée sur la capture d'écran ci-dessous.

Partie 4 : Construire la section des opérations du modèle

Dans la première partie du modèle, je n'ai pas fourni les titres. Vous êtes censé arriver aux volumes produits en MWH qui est la base des revenus. Pour ce faire, vous devez savoir quel sera le facteur de capacité solaire ou le rendement. Je vous suggère d'entrer le pilote de la formule dans une colonne de gauche, que vous mettiez les unités dans une colonne de gauche et que vous utilisiez SHIFT, CNTL, R pour copier rapidement les choses vers la droite. Vous devez également calculer un indice de dégradation qui commence l'année 1 en prenant la période précédente et en la multipliant par (1+dégradation). Ensuite, vous pouvez diviser la capacité par dégradation et calculer la capacité après dégradation. Vous pouvez utiliser la méthode de votre choix pour insérer les lignes. Vous pouvez créer votre propre touche de raccourci pour le soulignement.

Partie 5 : Revenus, dépenses, dépenses en immobilisations et flux de trésorerie disponibles

Dans tout modèle de financement d'entreprise, modèle de fusion et acquisition, modèle immobilier ou modèle de financement de projet, vous aurez besoin de revenus, de dépenses et de dépenses en capital. Les hypothèses qui créent ces trois éléments (ainsi que le fonds de roulement) guideront tout le reste de l'analyse. Dans la section suivante, vous devez calculer les revenus, les dépenses et les dépenses en capital à partir des intrants. Je pense que c'est une très bonne idée de mettre les pilotes de chaque formule dans une colonne de gauche afin que vous puissiez être transparent (je déteste chercher d'où viennent les chiffres). Dans cette partie de l'analyse, je vous ai donné les titres comme indiqué sur la capture d'écran ci-dessous. Une fois que vous avez calculé le TRI du projet, vous pouvez utiliser une recherche d'objectif et une macro pour évaluer le prix requis.

Partie 6 : Amortissem*nt, taxes d'exploitation et flux de trésorerie après impôt

S'il n'y avait pas d'impôts et que vous ne vouliez pas présenter de compte de profits et pertes, vous pourriez éliminer cette partie du modèle. Avec les impôts, le bouclier contre l'amortissem*nt est un élément important. Calculez l'amortissem*nt en créant un compte avec le solde de l'usine comme indiqué ci-dessous. Lorsque vous calculez l'amortissem*nt, vous pouvez utiliser la fonction MIN pour vous assurer que l'amortissem*nt ne dépasse pas le solde net de l'usine. Une fois que vous avez l'amortissem*nt, vous pouvez calculer l'EBIT, les taxes sur l'EBIT, le flux de trésorerie du projet après impôt et le TRI du projet. La capture d'écran ci-dessous illustre cette partie du modèle.

Partie 7 : Sources et utilisations avant COD

Une fois que vous avez terminé le flux de trésorerie d'exploitation et les impôts d'exploitation, vous pouvez passer à l'intégration de la dette dans le modèle. Dans chaque modèle de financement de projet, il devrait y avoir des sources et des utilisations de fonds pour évaluer les flux de trésorerie pendant la période de construction. Vous pouvez considérer cela comme un moyen d'évaluer la taille de la dette. Dans l'exemple, vous pouvez remplir les éléments sur les sources et les utilisations des fonds à partir des entrées et des éléments au-dessus des sources et des utilisations. Notez que ce n'est pas réaliste car des éléments tels que les intérêts pendant la construction et les frais viennent d'en bas et non d'en haut et vous obtenez une référence circulaire.

Partie 8 : Échéancier de la dette

Le calendrier de la dette doit comporter des calculs pour le remboursem*nt de la dette, les frais d'intérêts et les frais. Le remboursem*nt est souvent l'aspect le plus difficile d'un modèle. Il existe des méthodes alternatives démontrées pour calculer le remboursem*nt, y compris le remboursem*nt forfaitaire, le remboursem*nt hypothécaire et le remboursem*nt gonflé qui correspond au flux de trésorerie lorsque l'inflation est appliquée au prix. Utilisez la fonction INDEX pour sélectionner l'une des trois méthodes de remboursem*nt. Une capture d'écran est présentée ci-dessous.

Partie 9 : État des profits et pertes et état des flux de trésorerie

Vous êtes maintenant à la partie facile. La seule raison pour laquelle un compte de résultat est nécessaire dans un modèle de financement de projet est de calculer les impôts et de calculer le revenu net aux fins d'équilibrer le bilan. L'état des flux de trésorerie peut être plus complexe s'il y a des transferts de trésorerie, des clauses restrictives et d'autres éléments.

Partie 10 : Bilan et ratios financiers

La dernière partie de l'exercice consiste à établir un bilan et à calculer quelques ratios financiers. Les composantes du bilan doivent toutes provenir d'éléments du modèle et le solde des capitaux propres doit être calculé. Les ratios tels que IRR, DSCR et LLCR proviennent du tableau des flux de trésorerie.

Exercice qui inclut la période de construction

On m'a demandé de préparer un exercice où les gens pourraient rapidement créer un modèle qui évalue les prix des offres et d'autres aspects du modèle et peut vous obtenir la plupart des résultats sans passer trop de temps sur certains des détails horribles qui peuvent rendre la modélisation du financement de projet si difficile. On m'a dit que la création d'un modèle de financement de projet relativement simple ne pouvait pas être enseignée en une seule journée. Ce n'est pas vrai. Tu peux voir

Fichier Excel avec exercice pour créer un modèle de cycle combiné avec des problèmes de synchronisation et de construction

Exercice d'étude de cas sur la façon dont vous pouvez évaluer et modèle existant

Certaines personnes malchanceuses doivent créer des modèles et faire face à des références circulaires et à de nombreux détails horribles comme des retenues à la source sur quatre tranches de dette. De plus en plus de personnes doivent interpréter et utiliser des modèles, ce qui est sans doute encore plus désagréable en raison de la façon dont les modèles sont devenus lourds et trop détaillés.

Cette étude de cas et cet exercice sont conçus pour vous permettre d'évaluer plus efficacement les modèles créés par d'autres personnes, notamment en disséquant les flux de trésorerie dans les modèles, en calculant des ratios financiers alternatifs, en ajoutant votre propre analyse de scénario, en disséquant le modèle avec une analyse de sensibilité, en créant une page de résumé transmet la transaction, en créant des graphiques efficaces pour illustrer les risques et enfin en formatant le modèle avec de belles couleurs et des titres. J'espère que cet exercice sera pratique pour vous et que vous pourrez utiliser immédiatement certaines des idées dans votre travail actuel.

Le fichier que j'ai utilisé pour ce cas est le cas complet du modèle ci-dessus. Mais il peut s'agir de n'importe quel modèle de financement de projet, modèle d'entreprise, modèle de fusion et acquisition ou autre analyse financière comportant quelques entrées et sorties. Le modèle de base que j'ai choisi pour cet exercice est disponible en téléchargement en cliquant sur le bouton ci-dessous.

Introduction à la mission de revue de modèle

J'ai structuré cette tâche en essayant d'expliquer les détails de la façon de remplir les éléments Excel. Ensuite, j'ai inclus des questions qui devraient être complétées avec seulement une ou deux phrases où vous me dites les implications des tâches de modélisation. J'ai également fourni des titres pour de nombreux éléments afin que vous n'ayez pas à perdre beaucoup de temps à taper des éléments et que l'exercice ne prenne pas trop de temps. Je vous déconseille fortement de faire cet exercice sur un ordinateur Apple ; que vous ouvriez et utilisiez le fichier GENERIC MACRO, le fichier LOOKUP INTERPOLATE et le fichier SCENARIO REPORTER pour accélérer le traitement du cas. Ces trois fichiers sont disponibles en téléchargement ci-dessous.

Fichier de macro générique qui vous permet de copier vers la droite (Shift, CNTL, R) et de colorer et formater les feuilles (CNTL, ALT, C)

Télécharger le fichier Excel avec la fonction d'interpolation en utilisant la croissance composée ou l'interpolation linéaire

Fichier Scenario Reporter - Avec ce fichier, vous ouvrez le fichier, puis copiez la feuille de scénario dans votre fichier et utilisez les boutons

Partie 1 : Dissection des flux de trésorerie

Utilisez la fonction SUMIF pour convertir les données en montants annuels. Lorsque vous utilisez SUMIF, cliquez sur la ligne entière. Question - comment le TRI du projet et des capitaux propres se compare-t-il aux rendements globaux du marché boursier. Pensez-vous que cela est réaliste.

Partie 2 : Calcul des ratios financiers alternatifs

J'espère que vous apprendrez à regarder des ratios autres que le DSCR. Dans ce cas, vous pouvez examiner le LLCR et le PLCR. Le seul calcul que vous devez faire pour cela est d'utiliser la fonction SUMPRODUCT sous le tableau des flux de trésorerie avec l'indice des taux d'intérêt et de trouver la dette à COD à partir des sources et des utilisations. Expliquer comment interpréter les DSCR, LLCR et PLCR.

Partie 3 : Ajouter votre propre analyse de scénario

Utilisez le gestionnaire de scénarios pour ajouter un scénario. Dans ce cas, pourriez-vous inventer quelques cas avec des entrées différentes. Modifiez les éléments suivants : le coût du projet, la durée de vie de la centrale, la disponibilité. Ouvrez ensuite le rapporteur de scénario et créez un rapport de scénario.

Partie 4 : Dissection du modèle avec analyse de sensibilité

Partie 5 : Créer des graphiques efficaces pour illustrer les risques

Partie 6 : Création d'une page de résumé qui transmet les aspects clés de la transaction

Partie 7 : Formater le modèle avec de belles couleurs et des titres