Modélisation financière dans Excel

La modélisation financière dans Excel est le processus de construction d'un modèle financier pour représenter une transaction, une opération, une fusion, une acquisition, des informations financières pour analyser comment un changement dans une variable peut affecter le rendement final afin de prendre une décision sur un ou plusieurs des transactions financières susmentionnées.

Qu'est-ce que la modélisation financière dans Excel?

La modélisation financière dans Excel est partout sur le Web et il a été beaucoup écrit sur l'apprentissage de la modélisation financière, cependant, la plupart des éléments de formation sur la modélisation financière sont exactement les mêmes. Cela va au-delà du charabia habituel et explore la modélisation financière pratique utilisée par les banquiers d'investissement et les analystes de recherche.

Dans ce guide Excel gratuit de modélisation financière, je prendrai un exemple de Colgate Palmolive et préparerai un modèle financier entièrement intégré à partir de zéro.

Ce guide compte plus de 6000 mots et m'a pris 3 semaines. Enregistrez cette page pour référence future et n'oubliez pas de la partager :-)

LE PLUS IMPORTANT - Téléchargez le modèle Excel de modélisation Colgate Financial pour suivre les instructions

Télécharger le modèle de modèle financier Colgate

Apprenez la modélisation financière étape par étape dans Excel

Formation sur la modélisation financière dans Excel - À lire en premier

Étape 1 - Téléchargez le modèle de modèle financier Colgate. Vous utiliserez ce modèle pour le didacticiel

Téléchargez le modèle financier de Colgate

Étape 2 - Veuillez noter que vous obtiendrez deux modèles - 1) Modèle financier Colgate Palmolive non résolu 2) Modèle financier Colgate Palmolive résolu

Étape 3 - Vous travaillerez sur le modèle de modèle financier Colgate Palmolive non résolu . Suivez les instructions étape par étape pour préparer un modèle financier entièrement intégré.

Étape 4 - Bon apprentissage!

Table des matières

J'ai créé une table des matières facile à parcourir pour que vous puissiez faire cette modélisation financière

  •  # 1 - Le modèle financier de Colgate - Historique
  •  # 2 - Analyse du ratio de Colgate Palmolive
  •  # 3 - Projection du compte de résultat
  •  # 4- Calendrier du fonds de roulement
  •  # 5 - Plan d'amortissement
  •  # 6 - Calendrier d'amortissement
  •  # 7 - Autre horaire à long terme
  •  # 8 - Remplir le compte de résultat
  •  # 9 - Barème des capitaux propres
  •  # 10 - Calendrier des actions en suspens
  •  # 11 - Remplir les états des flux de trésorerie
  •  # 12- Échéancier de la dette et des intérêts recommandé
  •  Cours de modélisation financière
  •  Modèles financiers gratuits

Si vous êtes nouveau dans la modélisation financière, consultez ce guide sur Qu'est-ce que la modélisation financière?

Comment construire un modèle financier dans Excel?

Voyons comment un modèle financier est construit à partir de zéro. Ce guide détaillé de modélisation financière vous fournira un guide étape par étape pour créer un modèle financier. La principale approche adoptée dans ce guide de modélisation financière est modulaire. L'approche modulaire signifie essentiellement que nous construisons des états de base tels que l'état des résultats, le bilan et les flux de trésorerie en utilisant différents modules / calendriers. L'objectif principal est de préparer chaque déclaration étape par étape et de relier toutes les annexes aux déclarations de base une fois terminées. Je peux comprendre que cela ne soit peut-être pas clair pour le moment, mais vous vous rendrez compte que c'est très facile à mesure que nous avançons. Vous pouvez voir ci-dessous divers programmes / modules de modélisation financière -

Veuillez noter ce qui suit -

  • Les principaux états sont le compte de résultat, le bilan et les flux de trésorerie.
  • Les calendriers supplémentaires sont le calendrier d'amortissement, le calendrier du fonds de roulement, le calendrier des immobilisations incorporelles, le calendrier des capitaux propres, le calendrier des autres éléments à long terme, le calendrier de la dette, etc.
  • Les annexes supplémentaires sont liées aux déclarations de base une fois terminées
  • Dans ce guide de modélisation financière, nous allons construire un modèle financier intégré étape par étape de Colgate Palmolive à partir de zéro.

# 1 - Modélisation financière dans Excel - Projetez les historiques

La première étape du guide de modélisation financière consiste à préparer les historiques.

Étape 1A - Téléchargez les rapports 10K de Colgate

«Les modèles financiers sont préparés dans Excel et les premières étapes commencent par la connaissance de l'évolution de l'industrie au cours des dernières années. Comprendre le passé peut nous fournir des informations précieuses sur l'avenir de l'entreprise. Par conséquent, la première étape consiste à télécharger toutes les données financières de l'entreprise et à les remplir dans une feuille Excel. Pour Colgate Palmolive, vous pouvez télécharger les rapports annuels de Colgate Palmolive à partir de leur section Relations investisseurs. Une fois que vous avez cliqué sur «Rapport annuel», vous trouverez la fenêtre ci-dessous -

Étape 1B - Créer la feuille de travail des états financiers historiques
  • Si vous téléchargez 10K de 2013, vous remarquerez que seules deux années de données d'états financiers sont disponibles. Cependant, aux fins de la modélisation financière dans Excel, l'ensemble de données recommandé est d'avoir les 5 dernières années d'états financiers. Veuillez télécharger les 3 dernières années du rapport annuel et remplir l'historique.
  • Plusieurs fois, ces tâches semblent trop ennuyeuses et fastidieuses car cela peut prendre beaucoup de temps et d'énergie pour formater et mettre le fichier Excel dans le format souhaité.
  • Cependant, il ne faut pas oublier que c'est le travail que vous devez effectuer une seule fois pour chaque entreprise et que le remplissage des historiques aide également un analyste à comprendre les tendances et les états financiers.
  • Alors s'il vous plaît ne sautez pas ceci, téléchargez les données et remplissez les données (même si vous sentez que c'est le travail d'un âne ;-))

Si vous souhaitez ignorer cette étape, vous pouvez télécharger directement le  modèle historique Colgate Palmolive ici. 

Compte de résultat Colgate avec historique renseigné

Données historiques du bilan Colgate

# 2 - Analyse des ratios 

La deuxième étape de la modélisation financière dans Excel consiste à effectuer une analyse des ratios.

Une clé pour apprendre la modélisation financière dans Excel est de pouvoir effectuer une analyse fondamentale. Si l'analyse fondamentale ou l'analyse des ratios est quelque chose de nouveau pour vous, je vous recommande de lire un peu sur Internet. J'ai l'intention de faire une analyse approfondie des ratios dans l'un de mes prochains articles, cependant, voici un aperçu rapide des ratios Colgate Palmolive

IMPORTANT - Veuillez noter que j'ai mis à jour l'analyse des ratios de Colgate dans un article séparé. Veuillez jeter un œil à cette analyse complète des ratios.

Étape 2A - Analyse verticale de Colgate

Sur le compte de résultat, l'analyse verticale est un outil universel pour mesurer la performance relative de l'entreprise d'année en année en termes de coût et de rentabilité. Il doit toujours être inclus dans toute analyse financière. Ici, les pourcentages sont calculés par rapport aux ventes nettes qui sont considérées comme étant de 100%. Cet effort d'analyse verticale dans le compte de résultat est souvent appelé analyse de marge car il donne les différentes marges par rapport aux ventes.

Résultats de l'analyse verticale
  • La marge bénéficiaire a augmenté de 240 points de base, passant de 56,2% en 2007 à 58,6% en 2013. Cela est principalement dû à la diminution du coût des ventes
  • Le résultat opérationnel ou EBIT a également montré une amélioration des marges, passant de 19,7% en 2007 à 22,4% en 2012 (une augmentation de 70 points de base). Cela était dû à une diminution des frais généraux et administratifs de vente. A noter cependant que les marges EBIT ont baissé en 2013 à 20,4% en raison d'une augmentation des «Autres charges». Vérifiez également la différence entre l'EBIT et l'EBITDA
  • La marge bénéficiaire nette est passée de 12,6% en 2007 à 14,5% en 2012. Cependant, la marge bénéficiaire en 2013 est tombée à 12,9%, principalement en raison de l'augmentation des «autres dépenses».
  • Le bénéfice par action n'a cessé d'augmenter de FY2007 à FY2012. Cependant, il y a eu une légère baisse du BPA de l'exercice 2013
  • Notez également que l'amortissement et l'amortissement sont fournis séparément dans le compte de résultat. Il est inclus dans le coût des ventes
Étape 2B - Analyse horizontale de Colgate

L'analyse horizontale est une technique utilisée pour évaluer les tendances au fil du temps en calculant les pourcentages d'augmentation ou de diminution par rapport à une année de base. Il fournit un lien analytique entre des comptes calculés à différentes dates en utilisant des devises avec des pouvoirs d'achat différents. En effet, cette analyse indexe les comptes et compare leur évolution dans le temps. Comme pour la méthodologie d'analyse verticale, des problèmes surgiront et devront être étudiés et complétés par d'autres techniques d'analyse financière. L'objectif est de rechercher les symptômes de problèmes qui peuvent être diagnostiqués à l'aide de techniques supplémentaires.

Regardons l'analyse horizontale de Colgate

Résultats de l'analyse horizontale
  • On constate que le chiffre d'affaires net a augmenté de 2,0% en 2013.
  • Notez également la tendance du coût des ventes, nous voyons qu'ils n'ont pas augmenté dans la même proportion que les ventes.
  • Ces observations sont extrêmement utiles lorsque nous faisons de la modélisation financière dans Excel
Étape 2C - Ratios de liquidité de Colgate
  • Les ratios de liquidité mesurent la relation entre les actifs les plus liquides d'une entreprise (ceux qui sont le plus facilement convertibles en trésorerie) et les passifs courants. Les ratios de liquidité les plus courants sont les suivants: Ratio actuel Ratio de test d'acide (ou d'actif rapide) Ratios de trésorerie
  • Ratios de rotation tels que la rotation des comptes clients, la rotation des stocks et la rotation des comptes fournisseurs    
Points saillants des ratios de liquidité
  • Le ratio actuel de Colgate est supérieur à 1,0 pour toutes les années. Cela implique que les actifs courants sont supérieurs aux passifs courants et que Colgate dispose peut-être d'une liquidité suffisante
  • Le Quick Ratio de Colgate est compris entre 0,6 et 0,7, ce qui signifie que Colgates Cash et les titres négociables peuvent payer jusqu'à 70% des passifs courants. Cela semble être une situation raisonnable pour Colgate.
  • Le cycle de collecte de trésorerie est passé de 43 jours en 2009 à 39 jours en 2013. Cela est principalement dû à la réduction de la période de recouvrement des créances.

Jetez également un œil à cet article détaillé sur le cycle de conversion de trésorerie

Étape 2D - Ratios de rentabilité opérationnelle de Colgate

Ratios de rentabilité la capacité d'une entreprise à générer des bénéfices par rapport aux ventes, aux actifs et aux capitaux propres

Faits saillants - Ratios de rentabilité de Colgate

Comme nous pouvons le voir dans le tableau ci-dessus, Colgate a un ROE plus proche de 100%, ce qui implique de bons rendements pour les détenteurs d'actions.

Étape 2E - Analyse des risques de Colgate

Grâce à l'analyse des risques, nous essayons d'évaluer si les entreprises seront en mesure de payer leurs obligations à court et à long terme (dette). Nous calculons des ratios de levier qui se concentrent sur la suffisance des actifs ou la production d'actifs. Les ratios examinés sont

  • Ratio dette / capitaux propres
  • Ratio d'endettement
  • Intérêt ratio de couverture    
  • Le ratio dette / capitaux propres a augmenté régulièrement pour atteindre un niveau plus élevé de 2,23x. Cela signifie un effet de levier financier accru et des risques sur le marché
  • Cependant, le ratio de couverture des intérêts est très élevé, ce qui signifie moins de risque de défaut de paiement des intérêts.

# 3 -  Modélisation financière dans Excel -  Projetez le compte de résultat

La troisième étape de la modélisation financière consiste à prévoir le compte de résultat, dans lequel nous commencerons par modéliser les éléments de ventes ou de revenus.

Étape 3A - Projections des revenus 

Pour la plupart des entreprises, les revenus sont un moteur fondamental de la performance économique. Un modèle de revenus bien conçu et logique reflétant avec précision le type et les montants des flux de revenus est extrêmement important. Il existe autant de façons de concevoir un calendrier de revenus que d'entreprises. Certains types courants comprennent:

  • Croissance des ventes: L'  hypothèse de croissance des ventes pour chaque période définit le changement par rapport à la période précédente. Il s'agit d'une méthode simple et couramment utilisée, mais qui n'offre aucun aperçu des composantes ou de la dynamique de la croissance.
  • Effets inflationnistes et volume / mix: au  lieu d'une simple hypothèse de croissance, un facteur d'inflation des prix et un facteur de volume sont utilisés. Cette approche utile permet de modéliser les coûts fixes et variables dans les entreprises multi-produits et prend en compte les mouvements de prix par rapport aux volumes.
  • Volume unitaire, changement de volume, prix moyen et changement de prix:  Cette méthode est appropriée pour les entreprises qui ont une gamme de produits simple; il permet d'analyser l'impact de plusieurs variables clés.
  • Taille et croissance du marché en dollars: part de  marché et changement de part - Utile dans les cas où des informations sont disponibles sur la dynamique du marché et où ces hypothèses sont susceptibles d'être fondamentales pour une décision. Par exemple, l'industrie des télécommunications
  • Taille et croissance du marché unitaire:  Ceci est plus détaillé que le cas précédent et est utile lorsque la tarification sur le marché est une variable clé. (Pour une entreprise avec une stratégie de réduction de prix, par exemple, ou un acteur de niche à prix premium de premier ordre), par exemple, le marché des voitures de luxe
  • Capacité en volume, taux d'utilisation de la capacité et prix moyen:  ces hypothèses peuvent être importantes pour les entreprises où la capacité de production est importante pour la décision. (Lors de l'achat d'une capacité supplémentaire, par exemple, ou pour déterminer si l'expansion nécessiterait de nouveaux investissements.)
  • Disponibilité et prix des produits
  • Les revenus ont été tirés par l'investissement en capital, en marketing ou en R&D
  • Chiffre d'affaires basé sur la base installée (vente continue de pièces, jetables, service et add-ons, etc.). Les exemples incluent les entreprises classiques de rasoir et les entreprises comme les ordinateurs où les ventes de services, de logiciels et de mises à niveau sont importantes. La modélisation de la base installée est essentielle (nouveaux ajouts à la base, attrition dans la base, maintien des revenus par client, etc.).
  • Basé sur les employés:  par exemple, les revenus des sociétés de services professionnels ou des sociétés de vente telles que les courtiers. La modélisation doit se concentrer sur la dotation nette en personnel, le revenu par employé (souvent basé sur les heures facturables). Des modèles plus détaillés comprendront l'ancienneté et d'autres facteurs affectant les prix.
  •  Basé sur les magasins , les installations ou les pieds carrés: les entreprises de vente au détail sont souvent modélisées en fonction des magasins (anciens magasins et nouveaux magasins chaque année) et des revenus par magasin.
  • Basé sur le facteur d'occupation:  cette approche est applicable aux compagnies aériennes, aux hôtels, aux cinémas et aux autres entreprises à faibles coûts marginaux.
Projection des revenus de Colgate

Regardons maintenant le rapport Colgate 10K 2013. Nous notons que dans le compte de résultat, Colgate n'a pas fourni d'informations sectorielles, cependant, à titre d'information supplémentaire, Colgate a fourni quelques détails sur les segments sur Page 87 Source - Colgate 2013 - 10K, Page 86

Étant donné que nous ne disposons d'aucune information complémentaire sur les segments, nous projetterons les ventes futures de Colgate sur la base de ces données disponibles. Nous utiliserons l'approche de croissance des ventes à travers les segments pour établir les prévisions. S'il vous plaît voir l'image ci-dessous. Nous avons calculé le taux de croissance d'une année à l'autre pour chaque segment. Nous pouvons désormais supposer un pourcentage de croissance des ventes basé sur les tendances historiques et projeter les revenus sous chaque segment. Les ventes nettes totales correspondent à la somme totale du segment des soins bucco - dentaires, personnels et à domicile et de la  nutrition animale.

Étape 3B - Projections des coûts
  • Pourcentage des revenus: simple mais n'offre aucun aperçu de l'effet de levier (économie d'échelle ou coût fixe
  • Coûts autres que l'amortissement en pourcentage des revenus et l'amortissement d'un calendrier distinct: cette approche est vraiment le minimum acceptable dans la plupart des cas, et ne permet qu'une analyse partielle du levier d'exploitation.
  • Coûts variables basés sur les revenus ou le volume, coûts fixes basés sur les tendances historiques et amortissements à partir d'un calendrier séparé: cette approche est le minimum nécessaire pour une analyse de sensibilité de la rentabilité basée sur plusieurs scénarios de revenus
Projections des coûts pour Colgate

Pour projeter le coût, l'analyse verticale effectuée plus tôt sera utile. Revenons sur l'analyse verticale -

  • Puisque nous avons déjà prévu les ventes, tous les autres coûts sont des marges de ces ventes.
  • L'approche consiste à prendre les lignes directrices des marges de coûts et de dépenses historiques, puis à prévoir la marge future.
  • Par exemple, le coût des ventes a été compris entre 41% et 42% au cours des 5 dernières années. Nous pouvons envisager de prévoir les marges sur cette base.
  • De même, les frais de vente, généraux et administratifs ont toujours été de l'ordre de 34% à 36%. Nous pouvons assumer la future marge des frais de vente, dépenses administratives et autres frais généraux sur cette base. De même, nous pouvons continuer pour un autre ensemble de dépenses.

En utilisant les marges ci-dessus, nous pouvons trouver les valeurs réelles par des calculs rétrospectifs.

 Pour calculer la provision pour impôts, nous utilisons l'hypothèse du taux d'imposition effectif

  • Notez également que nous ne remplissons pas la ligne «Intérêts (revenus)» car nous aurons un relooking du compte de résultat à un stade ultérieur.
  • Frais d'intérêts et revenus d'intérêts.
  • Nous n'avons pas non plus calculé l'amortissement et l'amortissement qui ont déjà été inclus dans le coût des ventes.
  • Ceci complète le compte de résultat (du moins pour le moment!)

# 4- Modélisation financière - Calendrier du fonds de roulement

Maintenant que nous avons terminé le compte de résultat, la quatrième étape de la modélisation financière consiste à examiner le calendrier du fonds de roulement.

Voici les étapes à suivre pour le calendrier du fonds de roulement

Étape 4A - Lier les ventes nettes et le coût des ventes 

Étape 4B - Référencez les données du bilan liées au fonds de roulement
  • Référencez les données passées du bilan
  • Calculer le fonds de roulement net
  • Arriver à une augmentation / diminution du fonds de roulement
  • Veuillez noter que nous n'avons pas inclus la dette à court terme et la trésorerie et équivalents de trésorerie dans le fonds de roulement. Nous traiterons la dette et la trésorerie et les équivalents de trésorerie séparément.

Étape 4C - Calculez les ratios de rotation
  • Calculer les ratios et pourcentages historiques
  • Utiliser le solde final ou moyen
  • Les deux sont acceptables tant que la cohérence est maintenue

Étape 4D - Remplir les hypothèses pour les éléments futurs du fonds de roulement
  • Certains éléments sans facteur évident sont généralement supposés à des montants constants
  • S'assurer que les hypothèses sont raisonnables et conformes à l'entreprise

Étape 4E - Projeter les futurs soldes du fonds de roulement

Étape 4F - Calculez les variations du fonds de roulement
  • Arriver à des flux de trésorerie basés sur des éléments de campagne individuels
  • Assurez-vous que les panneaux sont exacts!

Étape 4G - Reliez le fonds de roulement prévu au bilan

Étape 4H - Lier le fonds de roulement à l'état des flux de trésorerie 

# 5 - Modélisation financière dans Excel - Plan d'amortissement

Avec l'achèvement du calendrier du fonds de roulement, la prochaine étape de cette modélisation financière est le projet de Capex de Colgate et la projection des chiffres d'amortissement et d'actifs.   Colgate 2013 - 10 km, page 49

  • L'amortissement et l'amortissement ne sont pas fournis en tant qu'élément de ligne distinct, cependant, ils sont inclus dans le coût des ventes
  • Dans de tels cas, veuillez consulter les états des flux de trésorerie où vous trouverez les frais d'amortissement et d'amortissement. Notez également que les chiffres ci-dessous sont 1) amortissement 2) amortissement. Alors, quel est le nombre d'amortissement?
  • Solde de clôture pour PPE = Solde de début + Capex - Amortissement - Ajustement pour les ventes d'actifs (équation BASE)

Étape 5A - Liez les chiffres des ventes nettes dans le tableau d'amortissement
  • Configurer les éléments de campagne
  • Ventes nettes de référence
  • Entrer les dépenses d'investissement passées
  • Arriver à Capex en% des ventes nettes

Étape 5B - Prévoir les postes de dépenses en capital
  • Afin de prévoir les dépenses d'investissement, il existe différentes approches. Une approche courante consiste à examiner les communiqués de presse, les projections de la direction et le rapport de gestion pour comprendre le point de vue de la société sur les dépenses en immobilisations futures.
  • Si l'entreprise a fourni des conseils sur les dépenses d'investissement futures, nous pouvons prendre ces chiffres directement.
  • Cependant, si les chiffres Capex ne sont pas directement disponibles, nous pouvons le calculer grossièrement en utilisant Capex en% des ventes (comme ci-dessous)
  • Utilisez votre jugement basé sur la connaissance de l'industrie et d'autres facteurs raisonnables

Étape 5C- Référence aux informations passées
  • Nous utiliserons le solde final pour PPE = solde de début + Capex - Dépréciation - Ajustement pour les ventes d'actifs (équation BASE)
  • Il est très difficile de rapprocher les immobilisations corporelles antérieures en raison des retraitements, des ventes d'actifs, etc.
  • Il est donc recommandé de ne pas concilier l'ancien EPI car cela peut prêter à confusion.

Politique d'amortissement de Colgate
  • Nous notons que Colgate n'a pas explicitement fourni une ventilation détaillée des Actifs. Ils ont plutôt matraqué tous les actifs en terrains, bâtiments, machines et autres équipements
  • En outre, la durée de vie utile des machines et équipements est fournie dans la plage. Dans ce cas, nous devrons faire quelques conjectures pour arriver à la durée de vie utile moyenne restante pour les actifs
  • De plus, aucune indication sur la durée de vie utile n'est fournie pour les «autres équipements». Nous devrons estimer la durée de vie des autres équipements

Colgate 2013 - 10 km, page 55

Vous trouverez ci-dessous le démantèlement des détails des propriétés, des usines et des équipements de 2012 et 2013

Colgate 2013 - 10 km, page 91

Étape 5D - Estimer le démantèlement des immobilisations corporelles (EPI)
  • Tout d'abord, trouvez les pondérations des actifs de l'EPI actuel (2013)
  • Nous supposerons que ces pondérations des actifs de l'EPI 2013 se poursuivront
  • Nous utilisons ces pondérations d'actif pour calculer la répartition des dépenses en capital estimées

Étape 5E - Estimation de la dépréciation des actifs
  • Veuillez noter que nous ne calculons pas l'amortissement du terrain car le terrain n'est pas un actif amortissable
  • Pour estimer la dépréciation des améliorations du bâtiment, nous utilisons d'abord la structure ci-dessous.
  • L'amortissement ici est divisé en deux parties - 1) l'amortissement de l'actif d'amélioration du bâtiment déjà inscrit au bilan 2) l'amortissement des futures améliorations du bâtiment
  • Pour le calcul de l'amortissement des améliorations de bâtiment répertoriées sur l'actif, nous utilisons la méthode simple d'amortissement linéaire
  • Pour le calcul des amortissements futurs, nous transposons d'abord les Capex à l'aide de la fonction TRANSPOSE dans Excel
  • Nous calculons l'amortissement de l'apport d'actifs de chaque année
  • De plus, l'amortissement de la première année est divisé par 2 car nous supposons la convention de mi-année pour le déploiement des actifs.

Amortissement total de l'amélioration du bâtiment = amortissement de l'actif d'amélioration du bâtiment déjà inscrit au bilan + amortissement de l'amélioration future du bâtiment Le processus ci-dessus pour estimer l'amortissement est utilisé pour calculer l'amortissement de 1) équipement de fabrication et machines et 2) autre indiqué ci-dessous.

Autres types d'équipement

Amortissement total de Colgate = amortissement (améliorations du bâtiment) + amortissement (machines et équipement) + amortissement (autre équipement) Une fois que nous avons trouvé les chiffres de l'amortissement total, nous pouvons le mettre dans l'équation BASE comme indiqué ci-dessous

  • Avec cela, nous obtenons les chiffres nets de fin de compte PP&E pour chacune des années

Étape 5F - Lier les immobilisations corporelles nettes au bilan

# 6 - Calendrier d'amortissement

La sixième étape de cette modélisation financière dans Excel consiste à prévoir l'amortissement. Nous avons deux grandes catégories à considérer ici - 1) Goodwill et 2) Autres actifs incorporels.

Étape 6A - Prévision du goodwill

Colgate 2013 - 10 km, page 61

  • Le goodwill est inscrit au bilan lorsqu'une entreprise acquiert une autre entreprise. Il est normalement très difficile de projeter le Goodwill pour les années futures.
  • Cependant, le Goodwill est soumis à des tests de dépréciation annuels qui sont réalisés par la société elle-même. Les analystes ne sont pas en mesure d'effectuer de tels tests et de préparer des estimations des dépréciations
  • La plupart des analystes ne projettent pas de bonne volonté, ils la gardent juste constante et c'est ce que nous ferons également dans notre cas.

Étape 6B - Prévision d'autres actifs incorporels
  • Comme indiqué dans le rapport 10K de Colgate, la majorité de l'immatériel à durée de vie limitée est liée à l'acquisition de Sanex
  • Les «ajouts aux actifs incorporels» sont également très difficiles à projeter
  • Le rapport 10K de Colgate nous fournit les détails des 5 prochaines années d'amortissement.
  • Nous utiliserons ces estimations dans notre modèle financier Colgate 2013 - 10K, page 61

Étape 6C - Les actifs incorporels nets de clôture sont liés aux «autres actifs incorporels»

Étape 6D - Lier la dépréciation et l'amortissement aux états des flux de trésorerie

Étape 6E - Lier Capex et ajout aux actifs incorporels aux états des flux de trésorerie

# 7 - Autre horaire à long terme

La prochaine étape de cette modélisation financière consiste à préparer l'autre calendrier à long terme. C'est le calendrier que nous préparons pour les «restes» qui n'ont pas de moteurs spécifiques pour la prévision. Dans le cas de Colgate, les autres éléments à long terme (restants) étaient les impôts différés sur le revenu (passif et actifs), les autres actifs et autres passifs.

Étape 7A - Référencez les données historiques du bilan

Calculez également les modifications de ces éléments.

Étape 7B - Prévoir les actifs et les passifs à long terme
  • Gardez les éléments à long terme constants pour les années projetées en cas d'absence de facteurs visibles
  • Liez les éléments prévus à long terme au bilan comme indiqué ci-dessous

Étape 7C - Référence d'autres éléments à long terme au bilan

Étape 7D - Lier les éléments à long terme à l'état des flux de trésorerie

Veuillez noter que si nous avons maintenu les actifs et les passifs à long terme constants, le changement qui se répercute sur le tableau des flux de trésorerie serait nul.

# 8 - Modélisation financière dans Excel - Remplir le compte de résultat

  • Avant d'aller plus loin dans cette modélisation financière basée sur Excel, nous reviendrons en fait sur le compte de résultat
  • Remplir les actions moyennes pondérées de base historiques et le nombre moyen pondéré dilué d'actions
  • Ces chiffres sont disponibles dans le rapport 10K de Colgate

Étape 8A - Référencez les actions de base et diluées

À ce stade, supposons que le nombre futur d'actions de base et diluées restera le même qu'en 2013.

Étape 8B - Calculer le bénéfice de base et dilué par action

Avec cela, nous sommes prêts à passer à notre prochain calendrier, à savoir le calendrier des capitaux propres.

# 9 - Modélisation financière - Barème des capitaux propres

L'étape suivante de cette formation sur la modélisation financière dans Excel consiste à examiner le barème des capitaux propres. L'objectif principal de ce programme est d'équité projet éléments liés comme actions des actionnaires, dividendes, rachat d'actions, d' options produit etc. rapport 10K de Colgate nous donne les détails des actions ordinaires et des activités d'actions de trésorerie au cours des dernières années , comme indiqué ci - dessous. Colgate 2013 - 10 km, page 68

Étape 9A - Rachat d'actions: remplissez les chiffres historiques 
  • Historiquement, Colgate a racheté des actions comme nous pouvons voir le calendrier ci-dessus.
  • Remplissez le rachat d'actions de Colgate (en millions) dans la feuille Excel.
  • Lier le BPA dilué historique à partir du compte de résultat
  • Le montant historique racheté doit être référencé à partir des états des flux de trésorerie

Jetez également un œil à Rachat accéléré d'actions

Étape 9B - Rachat d'actions: calculez le multiple PE (multiple EPS)
  • Calculez le prix moyen implicite auquel Colgate a effectué historiquement des rachats d'actions. Celui-ci est calculé comme suit: Montant racheté / Nombre d'actions
  • Calculer le multiple du PE = cours de l'action implicite / BPA

Étape 9C - Rachat d'actions: recherche de l'action de Colgate rachetée

Colgate n'a fait aucune annonce officielle sur le nombre d'actions qu'ils ont l'intention de racheter. La seule information selon laquelle leurs 10 000 actions rapportent est qu'ils ont autorisé un rachat de jusqu'à 50 millions d'actions. Colgate 2013 - 10 km, page 35

  • Afin de trouver le nombre d'actions rachetées, nous devons assumer le montant du rachat d'actions. Sur la base du montant de rachat historique, j'ai pris ce nombre à 1 500 millions de dollars pour toutes les années à venir.
  • Afin de trouver le nombre d'actions rachetées, nous avons besoin du prix de l'action implicite projeté du rachat potentiel.
  • Prix ​​de l'action implicite = EPS multiplex PE présumé
  • Les rachats futurs PE multiples peuvent être supposés sur la base des tendances historiques. Nous notons que Colgate a racheté des actions dans une fourchette PE moyenne de 17x - 25x
  • Ci-dessous, l'instantané de Reuters qui nous aide à valider la gamme PE pour Colgate

www.reuters.com

  • Dans notre cas, j'ai supposé que tous les futurs rachats de Colgate seront à un multiple de PE de 19x.
  • En utilisant le PE de 19x, nous pouvons trouver le prix implicite = EPS x 19
  • Maintenant que nous avons trouvé le prix implicite, nous pouvons trouver le nombre d'actions rachetées = montant $ utilisé pour le rachat / prix implicite

Étape 9D - Options d'achat d'actions: remplir les données historiques
  • À partir du résumé des actions ordinaires et des capitaux propres, nous connaissons le nombre d'options exercées chaque année.

En outre, nous avons également le produit des options des états des flux de trésorerie (environ)

  • Avec cela, nous devrions être en mesure de trouver le prix d'exercice effectif

Colgate 2013 - 10 km, page 53

Veuillez également noter que les options d'achat d'actions ont des termes contractuels de six ans et sont acquises sur trois ans. Colgate 2013 - 10 km, page 69

Avec ces données, nous remplissons les données sur les options comme ci- dessous.Nous notons également que le prix d'exercice moyen pondéré des options sur actions pour 2013 était de 42 $ et le nombre d'options exerçables était de 24,151 millions Colgate 2013 - 10K, Page 70

Étape 9E - Options d'achat d'actions: trouver le produit de l'option

En mettant ces chiffres dans nos données d'options ci-dessous, nous notons que le produit de l'option est de 1,014 milliard de dollars.

Étape 9F - Options d'achat d'actions: données d'unités de stock restreintes prévues

En plus des options d'achat d'actions, des unités d'actions restreintes sont attribuées aux employés avec une période moyenne pondérée de 2,2 ans Colgate 2013 - 10K, Page 81

Remplir ces données dans le jeu de données Options Par souci de simplicité, nous n'avons pas prévu l'émission d'options (je sais que ce n'est pas la bonne hypothèse, cependant, en raison du manque de données, je ne prends plus d'émissions d'options à l'avenir. Nous venons de prendre celles-ci sont égales à zéro, comme indiqué dans la zone grise ci-dessus.

Jetez également un œil à la méthode du stock de trésorerie

Étape 9G - Dividendes: prévoir les dividendes
  • Prévoir les dividendes estimés en utilisant le ratio de distribution des dividendes
  • Sortie de dividende fixe Paiement par action
  • À partir des rapports 10K, nous extrayons toutes les informations passées sur les dividendes
  • Avec les informations sur les dividendes payés, nous pouvons connaître le ratio de distribution des dividendes = Total des dividendes payés / Revenu net.
  • J'ai calculé le ratio de distribution des dividendes de Colgate comme indiqué ci-dessous -    Nous notons que le ratio de distribution des dividendes a été globalement compris entre 50% et 60%. Faisons une hypothèse sur le ratio de distribution des dividendes de 55% dans les années à venir.
  • Nous pouvons également lier le résultat net projeté à partir du compte de résultat
  • En utilisant à la fois le bénéfice net projeté et le ratio de distribution des dividendes, nous pouvons trouver le total des dividendes payés

Étape 8H - Prévision du compte de capitaux propres dans son intégralité

Compte tenu des prévisions de rachat d'actions, du produit des options et des dividendes versés, nous sommes prêts à compléter le barème des capitaux propres. Reliez tous ces éléments pour trouver le solde des capitaux propres de fin de chaque année, comme indiqué ci-dessous.

Étape 9I - Lier l'avoir de l'actionnaire de clôture au bilan 

Étape 9J - Lier les dividendes, le rachat d'actions et les options passe au CF

# 10 - Calendrier des actions en suspens

La prochaine étape de cette formation sur la modélisation financière en ligne dans Excel consiste à examiner le calendrier des actions supplémentaires. Résumé du calendrier des actions en circulation

  • Actions de base - réelles et moyennes
  • Capturez les effets passés des options et des convertibles, le cas échéant
  • Actions diluées - moyenne
  • Actions de référence rachetées et nouvelles actions sur options exercées
  • Calculer les parts de base prévues (réelles)
  • Calculer les actions de base et diluées moyennes
  • Référencez les actions projetées au compte de résultat (rappelez-vous la compilation du compte de résultat!)
  • Saisir l'historique des informations en circulation sur les actions
  • Remarque : ce calendrier est généralement intégré au calendrier des capitaux propres
Étape 10A - Entrez les chiffres historiques du rapport 10K 
  • Les actions émises (réalisation effective des options) et les actions rachetées peuvent être référencées à partir du barème des capitaux propres
  • De plus, les données ont pondéré un nombre moyen d'actions et l'effet des options d'achat d'actions pour les années historiques. 
Étape 10B - Reliez les émissions et les rachats d'actions à partir du calendrier des capitaux propres.

Actions de base (fin) = Actions de base (début) + Émissions d'actions - Actions rachetées.

Étape 10C - Trouvez les parts moyennes pondérées de base,
  • nous trouvons une moyenne de deux ans comme indiqué ci-dessous.
  • Ajoutez également l'effet des options et des unités d'actions restreintes (référencées dans le barème des capitaux propres de l'actionnaire) pour trouver les actions moyennes pondérées diluées.    
Étape 10D - Lier les actions pondérées de base et diluées au compte de résultat
  • Maintenant que nous avons calculé les actions moyennes pondérées diluées, il est temps pour nous de les mettre à jour dans le compte de résultat.
  • Reliez les actions moyennes pondérées diluées prévues en circulation au compte de résultat comme indiqué ci-dessous

Avec cela, nous complétons le calendrier des actions supplémentaires et le temps de passer à notre prochaine série de déclarations.

# 11 - Remplir les états des flux de trésorerie

Il est important pour nous de remplir complètement les états des flux de trésorerie avant de passer à notre prochain et dernier calendrier dans cette modélisation financière, c'est-à-dire le calendrier de la dette Jusqu'à ce stade, il n'y a que quelques choses qui sont incomplètes

  • Compte de résultat - les frais / revenus d'intérêts sont incomplets à ce stade
  • Bilan - les éléments de trésorerie et de dette sont incomplets à ce stade
Étape 11A - Calculer le flux de trésorerie pour les activités de financement

Consultez également les flux de trésorerie provenant du financement

Étape 11B - Trouver l'augmentation (la diminution) nette de la trésorerie et des équivalents de trésorerie

Étape 11C = Remplissez les états des flux de trésorerie

Retrouvez la trésorerie et les équivalents de trésorerie de fin d'année à la fin de l'année. 

Étape 11D - Liez la trésorerie et les équivalents de trésorerie au bilan.

Nous sommes maintenant prêts à nous occuper de notre dernier et dernier calendrier, c'est-à-dire le calendrier de la dette et des intérêts

# 12- Modélisation financière dans Excel - Barème de la dette et des intérêts

La prochaine étape de cette modélisation financière en ligne consiste à remplir le barème de la dette et des intérêts. Résumé de la dette et des intérêts - Annexe

Étape 12A - Établir un calendrier d'endettement
  • Référencez le flux de trésorerie disponible pour le financement
  • Faire référence à toutes les sources de capitaux propres et à l'utilisation de la trésorerie    
Étape 12B - Calculer le flux de trésorerie à partir du remboursement de la dette
  • Référencez le solde de trésorerie au début du bilan
  • Déduisez un solde minimum en espèces. Nous avons supposé que Colgate aimerait conserver un minimum de 500 millions de dollars chaque année.

Ignorer les émissions / remboursements de dette à long terme, les liquidités disponibles pour la facilité de crédit renouvelable et la section Revolver pour le moment     À partir du rapport 10K de Colgate, nous notons les détails disponibles sur la facilité de crédit renouvelable Colgate 2013 - 10K, page 35

Les remboursements de dette à long terme engagés sont également fournis dans les informations supplémentaires sur la dette. Colgate 2013 - 10 km, page 36

Étape 12C - Calculer la fin de la dette à long terme

Nous utilisons le calendrier de remboursement de la dette à long terme ci-dessus et calculons le solde final des remboursements de la dette à long terme.

Étape 12D - Liez les remboursements de la dette à long terme.

Étape 12E -Calculer les emprunts / remboursements discrétionnaires

À l'aide de la formule de balayage de trésorerie illustrée ci-dessous, calculez les emprunts / remboursements discrétionnaires. 

Étape 12F - Calculez les intérêts débiteurs de la dette à long terme
  • Calculer le solde moyen de la facilité de crédit renouvelable et de la dette à long terme
  • Faire une hypothèse raisonnable pour un taux d'intérêt sur la base des informations fournies dans le rapport 10K
  • Calculer les frais d'intérêts totaux = solde moyen de la dette x taux d'intérêt

Trouvez les frais d'intérêts totaux = intérêts (facilité de crédit renouvelable) + intérêts (dette à long terme)

Étape 12G - Lier les prélèvements de la dette principale et du revolver aux flux de trésorerie 

Étape 12H - Référence courant et long terme au bilan
  • Démarrez la partie actuelle de la dette à long terme et de la dette à long terme comme indiqué ci-dessous

  • Lier la facilité de crédit renouvelable, la dette à long terme et la partie actuelle de la dette à long terme au bilan  
Étape 12I - Calculez les revenus d'intérêts en utilisant le solde de trésorerie moyen

Étape 12J - Lier les frais d'intérêts et les revenus d'intérêts au compte de résultat 

Effectuer la vérification du bilan: Actif total = passif + capitaux propres

Étape 12K - Audit du bilan

En cas de divergence, nous devons auditer le modèle et rechercher d'éventuelles erreurs de couplage

Cours de modélisation financière recommandé


J'espère que vous avez apprécié le guide Excel gratuit de modélisation financière. Si vous souhaitez apprendre la modélisation financière dans Excel grâce à nos conférences vidéo d'experts, vous pouvez également consulter notre formation en banque d'investissement. Il s'agit principalement de 99 cours de formation en banque d'investissement. Ce cours commence par les bases et vous emmène au niveau avancé de l'emploi en banque d'investissement. Ce cours est divisé en 5 parties -

  • Partie 1 - Formation en banque d'investissement - Cours de base

    (26 cours)

  • Partie 2 - Formation avancée en modélisation des services bancaires d'investissement

    (20 cours)

  • Partie 3 - Modules complémentaires de banque d'investissement

    (13 cours)

  • Partie 4 - Cours de base sur la banque d'investissement

    (23 cours)

  • Partie 5 - Compétences générales pour les banquiers d'investissement

    (17 cours)

Téléchargement de modèles financiers


  • Modèle financier d'Alibaba
  • Modèle financier de Box IPO
  • Modèles de modélisation financière
  • Cours de modélisation financière bancaire

Et ensuite?

Si vous avez appris quelque chose de nouveau ou apprécié cette modélisation financière basée sur Excel, veuillez laisser un commentaire ci-dessous. Laissez-moi savoir ce que vous pensez. Merci beaucoup et prenez soin de vous. Bon apprentissage!