Modelització financera a Excel (guia gratuïta pas a pas + plantilla)

El modelatge financer a Excel és el procés de creació d’un model financer per representar una transacció, operació, fusió, adquisició, informació financera per analitzar com un canvi d’una variable pot afectar el retorn final per tal de prendre una decisió sobre una o més de les esmentades transaccions financeres.

Què és el modelatge financer a Excel?

Modelització financera a Excel és a tot el web i s’han escrit moltes coses sobre l’aprenentatge de la modelització financera, però la majoria de les formacions de modelització financera són exactament les mateixes. Això va més enllà de la tonteria habitual i explora la pràctica de la Modelització Financera que fan servir els banquers d’inversió i els analistes de la investigació.

En aquesta guia gratuïta de models financers d’Excel, agafaré un exemple de Colgate Palmolive i prepararé un model financer totalment integrat des de zero.

Aquesta guia té més de 6.000 paraules i em va trigar tres setmanes a completar-la. Deseu aquesta pàgina com a referència futura i no oblideu compartir-la :-)

EL MÉS IMPORTANT - Descarregueu la plantilla Excel de modelatge financer de Colgate per seguir les instruccions

Descarregueu la plantilla de model financer de Colgate

Apreneu la modelització financera pas a pas a Excel

Modelització financera a la formació d'Excel: llegiu-me primer

Pas 1 - Baixeu-vos la plantilla de model financer de Colgate. Utilitzarà aquesta plantilla per al tutorial

Descarregueu el model financer de Colgate

Pas 2: Tingueu en compte que obtindreu dues plantilles: 1) Model financer no resolt de Colgate Palmolive 2) Model financer de Colgate Palmolive resolt

Pas 3- Treballareu en el Plantilla de model financer Colgate Palmolive sense resoldre. Seguiu les instruccions pas a pas per preparar un model financer totalment integrat.

Pas 4 - Feliç aprenentatge!

Taula de continguts

He fet una taula de contingut fàcil de navegar perquè pugueu fer aquesta Modelització Financera

# 1 - Model financer de Colgate: històric
# 2 - Anàlisi de la relació de Colgate Palmolive
# 3 - Projecció del compte de resultats
# 4- Calendari de circulant
# 5 - Calendari d'amortització
# 6 - Calendari d'amortitzacions
# 7 - Un altre horari a llarg termini
# 8 - Emplenament del compte de resultats
# 9 - Calendari patrimonial accionista
# 10 - Horari pendent d'accions
# 11 - Emplenament dels extractes de fluxos d'efectiu
# 12- Es recomana el calendari de deutes i interessos
Curs de Modelització Financera
Models financers gratuïts

Si no coneixeu el modelatge financer, consulteu aquesta guia sobre què és el modelatge financer?

Com es construeix un model financer a Excel?

Vegem com es construeix un model financer des de zero. Aquesta guia detallada de models financers us proporcionarà una guia pas a pas per crear un model financer. El principal enfocament adoptat en aquesta guia de modelització financera és Modular.L’enfocament modular significa essencialment que generem estats bàsics com el compte de resultats, el balanç i els fluxos de caixa utilitzant diferents mòduls / programacions. L’objectiu principal és preparar cada declaració pas a pas i connectar tots els horaris de suport a les declaracions bàsiques en finalitzar-les. Puc entendre que potser no estigui clar a partir d’ara, però, us adonareu que això és molt fàcil a mesura que avancem. A continuació, podeu veure diversos mòduls / calendaris de modelització financera:

Tingueu en compte el següent -

Els estats bàsics són el compte de pèrdues i guanys, el balanç i els fluxos de caixa.
Els calendaris addicionals són el calendari d’amortització, el de circulant, el de béns intangibles, el de patrimoni net, el calendari d’altres partides a llarg termini, el deute, etc.
Els horaris addicionals estan enllaçats a les declaracions bàsiques quan es completen
En aquesta guia de modelització financera, crearem un model financer integrat pas a pas de Colgate Palmolive des de zero.

# 1: Modelització financera a Excel: projecta els històrics

El primer pas de la Guia de modelització financera és preparar els articles històrics.

Pas 1A: baixeu els informes 10K de Colgate

“Els models financers es preparen en excel·lència i els primers passos comencen per saber com ha anat la indústria en els darrers anys. Comprendre el passat ens pot proporcionar informació valuosa relacionada amb el futur de l’empresa. Per tant, el primer pas és descarregar tots els recursos financers de l’empresa i omplir-los en un full Excel. Per a Colgate Palmolive, podeu descarregar els informes anuals de Colgate Palmolive des de la secció Relació amb inversors. Un cop feu clic a "Informe anual", trobareu la finestra com es mostra a continuació -

Pas 1B: creeu el full de treball dels estats financers històrics

Si descarregueu 10.000 exemplars del 2013, notareu que només hi ha disponible dos anys d’estats financers. No obstant això, a efectes de Modelització Financera en Excel, el conjunt de dades recomanat és tenir els darrers cinc anys d'estats financers. Descarregueu els darrers 3 anys de l'informe anual i completeu l'històric.
Moltes vegades, aquestes tasques semblen massa avorrides i tedioses, ja que pot trigar molt de temps i energia a formatar i posar l’excel al format desitjat.
Tanmateix, no s’ha d’oblidar que aquesta és la feina que només s’ha de fer una vegada per a cada empresa i, a més, completar els històrics ajuda un analista a entendre les tendències i els estats financers.
Així que si us plau, no us ho ometeu, descarregueu-ne les dades i empleneu-les (fins i tot si creieu que això és una feina de burro;

Si voleu ometre aquest pas, podeu descarregar directament el fitxer Colgate Palmolive Model històric aquí.

Declaració de resultats de Colgate amb població històrica

Dades històriques del balanç de Colgate

# 2 - Anàlisi de la relació

El segon pas de la modelització financera a Excel és realitzar una anàlisi de la relació.

Una clau per aprendre la modelització financera a Excel és poder realitzar anàlisis fonamentals. Si l’anàlisi fonamental o l’anàlisi de la relació és alguna cosa nou per a vosaltres, us recomano que llegiu una mica a Internet. Tinc la intenció de fer una anàlisi de les relacions en profunditat en una de les meves properes publicacions, però, aquí teniu una instantània ràpida de les relacions Colgate Palmolive

IMPORTANT: tingueu en compte que he actualitzat l’anàlisi de la relació de Colgate en una publicació independent. Feu un cop d'ull a aquesta anàlisi de la ràtio completa.

Pas 2A: anàlisi vertical de Colgate

Al compte de pèrdues i guanys, l’anàlisi vertical és una eina universal per mesurar el rendiment relatiu de l’empresa d’any en any en termes de cost i rendibilitat. S’ha d’incloure sempre com a part de qualsevol anàlisi financera. Aquí, els percentatges es calculen en relació amb les vendes netes que es consideren del 100%. Aquest esforç d’anàlisi vertical al compte de pèrdues i guanys es denomina sovint anàlisi de marge, ja que produeix els diferents marges en relació amb les vendes.

Resultats d’anàlisi vertical

Marge de benefici ha augmentat en 240 punts bàsics, passant del 56,2% el 2007 al 58,6% el 2013. Això es deu principalment a la disminució del cost de vendes
Benefici d’explotació o EBIT també ha demostrat una millora dels marges, passant del 19,7% el 2007 al 22,4% el 2012 (un increment de 70 punts bàsics). Això es va deure a la disminució dels costos generals i administratius de venda. Tanmateix, tingueu en compte que els marges EBIT es van reduir el 2013 fins al 20,4% a causa d'un augment de "Altres despeses". Comproveu també la diferència entre EBIT i EBITDA
Marge de benefici net va augmentar del 12,6% el 2007 al 14,5% el 2012. Tot i això, el marge de benefici el 2013 va disminuir fins al 12,9%, principalment a causa de l'augment de les "altres despeses".
Guanys per compartir han augmentat constantment des de l'any fiscal 2007 fins a l'any 2012. No obstant això, hi ha hagut una lleugera caiguda en l'EPS de l'exercici 2013
Tingueu en compte també que l’amortització i l’amortització es proporcionen per separat al compte de pèrdues i guanys. S'inclou al cost de vendes

Pas 2B: anàlisi horitzontal de Colgate

L’anàlisi horitzontal és una tècnica que s’utilitza per avaluar les tendències al llarg del temps mitjançant el càlcul d’augments percentuals d’excel·lència o disminució en relació amb un any base. Proporciona un enllaç analític entre comptes calculats en dates diferents mitjançant divises amb diferents poders de compra. De fet, aquesta anàlisi indexa els comptes i compara l’evolució d’aquests al llarg del temps. Igual que amb la metodologia d’anàlisi vertical, apareixeran qüestions que cal investigar i complementar amb altres tècniques d’anàlisi financera. L’objectiu és buscar símptomes de problemes que es puguin diagnosticar mitjançant tècniques addicionals.

Vegem l'anàlisi horitzontal de Colgate

Resultats d’anàlisi horitzontal

Veiem que les vendes netes han augmentat un 2,0% el 2013.
Tingueu en compte també la tendència del cost de les vendes, ja que veiem que no han crescut en la mateixa proporció que les vendes.
Aquestes observacions són extremadament útils mentre fem models financers a Excel

Pas 2C: relacions de liquiditat de Colgate

Les ràtios de liquiditat mesuren la relació dels actius més líquids d’una empresa (els més fàcilment convertibles en efectiu) amb els passius corrents. Les ràtios de liquiditat més habituals són: Ràtio actual Ràtio de prova d’àcid (o actiu ràpid) Ràtio d’efectiu
Ràtios de facturació, com ara facturació de comptes a cobrar, facturació d’inventari i facturació de deutes

Aspectes clau de les relacions de liquiditat

Relació actual de Colgate és superior a 1,0 durant tots els anys. Això implica que els actius corrents són superiors als passius corrents i potser Colgate té prou liquiditat
Relació ràpida de Colgate està en el rang de 0,6-0,7, això vol dir que els títols Colgates Cash i negociables poden pagar fins al 70% del passiu corrent. Sembla una situació raonable per a Colgate.
Cicle de cobrament d’efectius ha disminuït de 43 dies el 2009 a 39 dies el 2013. Això es deu principalment a la reducció del període de cobrament de comptes a cobrar.

A més, consulteu aquest article detallat sobre el cicle de conversió d’efectiu

Pas 2D: relacions de rendibilitat operativa de Colgate

Ràtio de rendibilitat: la capacitat d’una empresa de generar beneficis en relació amb les vendes, els actius i el patrimoni net

Aspectes clau: relacions de rendibilitat de Colgate

Com podem veure a la taula anterior, Colgate té un ROE proper al 100%, la qual cosa implica grans rendiments per als accionistes.

Pas 2E: anàlisi de riscos de Colgate

Mitjançant l’anàlisi de riscos, intentem mesurar si les empreses podran pagar les seves obligacions a curt i llarg termini (deute). Calculem les ràtios de palanquejament que se centren en la suficiència d’actius o la generació d’actius. Les proporcions que es veuen són

Ràtio de deute amb renda variable
Ràtio d’endeutament
Ràtio de cobertura d’interessos
La ràtio d'endeutament i renda variable ha augmentat constantment fins a un nivell superior de 2,23x. Això significa un major apalancament financer i riscos al mercat
Tot i això, la ràtio de cobertura d’interessos és molt elevada, cosa que significa menys risc d’incompliment del pagament d’interessos.

#3 – Modelització financera a Excel: Projecte del compte de resultats

El tercer pas de la modelització financera és pronosticar el compte de pèrdues i guanys, en el qual començarem per modelar les partides de vendes o d’ingressos.

Pas 3A: projeccions d'ingressos

Per a la majoria de les empreses, els ingressos són un motor fonamental del rendiment econòmic. És extremadament important un model d’ingressos ben dissenyat i lògic que reflecteixi amb precisió el tipus i la quantitat de fluxos d’ingressos. Hi ha tantes maneres de dissenyar un calendari d’ingressos com empreses. Alguns tipus comuns inclouen:

Creixement de les vendes:El supòsit de creixement de les vendes en cada període defineix el canvi respecte al període anterior. Es tracta d’un mètode senzill i d’ús habitual, però que no ofereix cap informació sobre els components o la dinàmica del creixement.
Efectes inflacionaris i de volum / barreja:En lloc d’una simple hipòtesi de creixement, s’utilitzen un factor d’inflació de preus i un factor de volum. Aquest enfocament útil permet modelar els costos fixos i variables en empreses multiproducte i té en compte els moviments de preu i volum.
Volum unitari, canvi de volum, preu mitjà i variació de preu:Aquest mètode és adequat per a empreses que tinguin una combinació senzilla de productes; permet analitzar l'impacte de diverses variables clau.
Mida i creixement del mercat del dòlar:Quota de mercat i canvi de quota: útil per als casos en què hi hagi informació disponible sobre la dinàmica del mercat i en què és probable que aquests supòsits siguin fonamentals per a una decisió. Per exemple, la indústria de les telecomunicacions
Mida i creixement del mercat unitari:Això és més detallat que el cas anterior i és útil quan el preu del mercat és una variable clau. (Per a una empresa amb una estratègia de descompte de preus, per exemple, o un jugador de nínxol de millor preu de la millor categoria), p. Mercat de cotxes de luxe
Capacitat de volum, taxa d’utilització de capacitat i preu mitjà:Aquests supòsits poden ser importants per a les empreses on la capacitat de producció és important per a la decisió. (Per exemple, en la compra de capacitat addicional o per determinar si l'ampliació requeriria noves inversions.)
Disponibilitat i preus del producte
Els ingressos van ser impulsats per la inversió en capital, màrqueting o R + D
Basat en ingressos segons la base instal·lada (venda contínua de peces, d'un sol ús, de serveis i complements, etc.). Alguns exemples inclouen empreses clàssiques i empreses com ara ordinadors on les vendes de serveis, programari i actualitzacions són importants. El modelatge de la base instal·lada és clau (noves incorporacions a la base, desgast a la base, ingressos continus per client, etc.).
Basat en empleats:Per exemple, els ingressos d'empreses de serveis professionals o empreses basades en vendes com ara corredors. El modelatge s'hauria de centrar en la dotació neta, els ingressos per empleat (sovint en funció de les hores facturables). Els models més detallats inclouran l’antiguitat i altres factors que afecten els preus.
Basat en botigues, instal·lacions o imatges quadrades: Les empreses minoristes solen modelar-se en funció de les botigues (botigues antigues més botigues noves cada any) i els ingressos per botiga.
Basat en el factor d’ocupació: Aquest enfocament és aplicable a línies aèries, hotels, cinemes i altres empreses amb baixos costos marginals.

Projecció d'ingressos de Colgate

Vegem ara l'informe Colgate 10K 2013. Observem que al compte de pèrdues i guanys, Colgate no ha proporcionat informació segmentada, però, com a informació addicional, Colgate ha proporcionat alguns detalls dels segments a la pàgina 87. Font - Colgate 2013 - 10K, pàgina 86

Com que no tenim més informació sobre els segments, projectarem les vendes futures de Colgate a partir d’aquestes dades disponibles. Utilitzarem l’enfocament del creixement de les vendes entre segments per obtenir les previsions. Consulteu la imatge següent. Hem calculat la taxa de creixement interanual de cada segment. Ara podem assumir un percentatge de creixement de les vendes basat en les tendències històriques i projectar els ingressos de cada segment. Les vendes netes totals són la suma total de Atenció oral, personal i domiciliària, iSegment de nutrició per a mascotes.

Pas 3B: projeccions de costos

Percentatge d'ingressos: Senzill, però no ofereix cap visió sobre cap palanquejament (economia d'escala o càrrega de costos fixos)
Costos diferents de l'amortització com a percentatge dels ingressos i amortitzacions d’un calendari separat: aquest enfocament és realment el mínim acceptable en la majoria dels casos i només permet l’anàlisi parcial del palanquejament operatiu.
Costos variables segons els ingressos o el volum, costos fixos basats en tendències històriques i depreciació d'un calendari diferent: aquest enfocament és el mínim necessari per a l'anàlisi de sensibilitat de la rendibilitat basat en múltiples escenaris d'ingressos

Projeccions de costos per a Colgate

Per projectar el cost, serà útil l’anàlisi vertical realitzat anteriorment. Vegem l'anàlisi vertical:

Com que ja hem previst vendes, tots els altres costos són alguns marges d’aquestes vendes.
L’enfocament consisteix a agafar les directrius dels marges històrics de costos i despeses i, a continuació, predir el marge futur.
Per exemple, el cost de les vendes ha estat entre el 41% i el 42% durant els darrers 5 anys. Podem analitzar la previsió dels marges sobre aquesta base.
De la mateixa manera, les despeses de venda, generals i administratives han estat històricament compreses entre el 34% i el 36%. Podem assumir el futur marge de despeses de SG&A sobre aquesta base. De la mateixa manera, podem continuar per un altre conjunt de despeses.

Utilitzant els marges anteriors, podem trobar els valors reals mitjançant càlculs posteriors.

Per calcular la provisió per impostos, fem servir el supòsit del tipus impositiu efectiu

Tingueu en compte també que no completem la fila "Despeses d'interès (ingressos)", ja que tornarem a revisar el compte de pèrdues i guanys en una fase posterior.
Despeses d'interessos i ingressos per interessos.
Tampoc no hem calculat l’amortització i amortització que ja s’ha inclòs en el cost de vendes.
Amb això es completa el compte de resultats (almenys de moment!)

# 4- Modelització financera: calendari de circulant

Ara que hem completat el compte de pèrdues i guanys, el quart pas en la modelització financera és examinar el calendari de circulant.

A continuació es mostren els passos que cal seguir per a la programació del capital circulant

Pas 4A: enllaçar les vendes netes i el cost de les vendes

Pas 4B: feu referència a les dades del balanç de situació relacionades amb el circulant

Feu referència a les dades anteriors del balanç
Calculeu el fons de maniobra net
Arribar a un augment / disminució del capital circulant
Tingueu en compte que no hem inclòs el deute a curt termini i els efectius i equivalents en efectiu al fons de maniobra. Tractarem el deute i l’efectiu i els seus equivalents per separat.

Pas 4C: calculeu les relacions de facturació

Calculeu ràtios i percentatges històrics
Utilitzeu el saldo final o mitjà
Tots dos són acceptables sempre que es mantingui la consistència

Pas 4D: poblar els supòsits de futures partides de circulant

Alguns articles sense un controlador evident se solen assumir a quantitats constants
Assegureu-vos que els supòsits són raonables i coincidents amb el negoci

Pas 4E - Pprojectar els futurs saldos de circulant

Pas 4F: calculeu els canvis en el fons de maniobra

Arribeu als fluxos de caixa basats en línies de comanda individuals
Assegureu-vos que els senyals siguin exactes.

Pas 4G: enllaceu el capital circulant previst amb el balanç

Pas 4H: enllaçar el circulant amb l'estat de fluxos d'efectiu

# 5 - Modelització financera a Excel: calendari d'amortització

Amb la finalització del calendari de circulant, el següent pas d’aquesta modelització financera és el projecte del Capex de Colgate i el projecte de les xifres d’amortització i actius. Colgate 2013 - 10K, pàgina 49

L’amortització i l’amortització no es proporcionen com a línia de comanda independent, però s’inclouen en el cost de les vendes
En aquests casos, consulteu els estats de fluxos d'efectiu on trobareu les despeses d'amortització i amortització. Tingueu en compte també que les figures següents són 1) Amortització 2) amortització. Quin és el número d'amortització?
Saldo final per EPI = Saldo inicial + Capex - Amortització - Ajust per a vendes d’actius (equació BASE)

Pas 5A: enllaceu les xifres de vendes netes del programa d'amortització

Configureu les línies de comanda
Vendes netes de referència
Introduïu les despeses de capital passades
Arribeu a Capex com a% de les vendes netes

Pas 5B: preveure els conceptes de despesa de capital

Per tal de preveure la despesa de capital, hi ha diversos enfocaments. Un enfocament comú és examinar els comunicats de premsa, les projeccions de gestió, els MD&A per entendre la visió de l’empresa sobre la despesa de capital futura.
Si l’empresa ha proporcionat orientacions sobre futures despeses de capital, podem prendre aquestes xifres directament.
Tanmateix, si els números de Capex no estan disponibles directament, podem calcular-lo de manera crua utilitzant Capex com a% de vendes (tal com es fa a continuació)
Utilitzeu el vostre criteri basat en el coneixement de la indústria i altres motors raonables

Pas 5C: informació prèvia de referència

Utilitzarem saldo final per EPI = saldo inicial + capitalització - depreciació - ajustament per a vendes d’actius (equació BASE)
És molt difícil conciliar PP&E passats a causa de reformulacions, vendes d 'actius, etc
Per tant, es recomana no conciliar el PPE passat, ja que pot provocar certa confusió.

Política d'amortització de Colgate

Observem que Colgate no ha proporcionat explícitament un desglossament detallat dels actius. Més aviat han inclòs tots els actius en terrenys, edificis, maquinària i altres equips
A més, es proporcionen vides útils per a maquinària i equips a l'abast. En aquest cas, haurem de fer algunes suposicions per arribar a la vida útil mitjana que queda per als actius
Tampoc no es proporciona orientació per a la vida útil per a "Altres equips". Haurem d’estimar la vida útil per a altres equips

Colgate 2013 - 10K, pàgina 55

A continuació es mostra el desglossament dels detalls de l’immobilitzat material 2012 i 2013

Colgate 2013 - 10K, pàgina 91

Pas 5D: estima la ruptura de la planta i equipament immobiliari (EPI)

En primer lloc, busqueu els pesos dels actius del PPE actual (2013)
Suposarem que aquests pesos dels actius dels EPI de 2013 continuaran endavant
Utilitzem aquestes ponderacions d’actius per calcular el desglossament de la despesa de capital estimada

Pas 5E: estima la depreciació dels actius

Tingueu en compte que no calculem l’amortització del terreny, ja que el terreny no és un actiu amortitzable
Per estimar l’amortització de les millores de l’edifici, primer fem servir l’estructura següent.
L’amortització aquí es divideix en dues parts: 1) depreciació dels actius de millora de l’edifici que ja apareixen al balanç 2) depreciació de les futures millores de l’edifici
Per calcular l’amortització de les millores de l’edifici que figuren a l’actiu, fem servir el mètode d’amortització de línia recta simple
Per calcular la depreciació futura, primer transposem el Capex mitjançant la funció TRANSPOSE a Excel
Calculem la depreciació de la contribució d’actius de cada any
A més, la depreciació del primer any es divideix per 2, ja que suposem la convenció de mitjans d’any per al desplegament d’actius

Amortització total de la millora de l’edifici = amortització de l’actiu de millora de l’edifici que ja figura al balanç + depreciació de les futures millores de l’edifici El procés anterior per estimar la depreciació s'utilitza per calcular la depreciació de 1) Equips i maquinària de fabricació i 2) altres equips, tal com es mostra a continuació.

Altres tipus d'equips

Amortització total de Colgate = Amortització (millores d’edificació) + Amortització (maquinària i equips) + Amortització (altres equips)Un cop hàgim descobert les xifres d’amortització total, podem posar-ho a l’equació BASE tal com es mostra a continuació

Amb això, obtenim les xifres de PP&E Ending Net de cadascun dels anys

Pas 5F: enllaçar el PP&E net amb el balanç

# 6 - Calendari d'amortitzacions

El sisè pas d'aquesta modelització financera a Excel és predir l'amortització. Aquí tenim dues grans categories: 1) Fons de comerç i 2) Altres intangibles.

Pas 6A: predir el fons de comerç

Colgate 2013 - 10K, pàgina 61

La bona voluntat apareix al balanç quan una empresa adquireix una altra empresa. Normalment és molt difícil projectar la bona voluntat per als propers anys.
No obstant això, el fons de comerç està sotmès a proves de deteriorament anuals que realitza la pròpia empresa. Els analistes no estan en condicions de realitzar aquestes proves i preparar estimacions de deterioraments
La majoria dels analistes no projecten bona voluntat, sinó que mantenen això constant i això també ho farem en el nostre cas.

Pas 6B: predir altres actius intangibles

Com s’indica a l’informe 10K de Colgate, la majoria de la vida intangible de la vida finita està relacionada amb l’adquisició de Sanex
Les "incorporacions a intangibles" també són molt difícils de projectar
L’informe 10K de Colgate ens proporciona els detalls dels propers cinc anys de despesa d’amortització.
Utilitzarem aquestes estimacions al nostre model financerColgate 2013 - 10K, pàgina 61

Pas 6C: els intangibles nets finals es vinculen amb els "Altres actius intangibles"

Pas 6D: enllaçar l'amortització i l'amortització als estats de fluxos d'efectiu

Pas 6E: enllaçar Capex i addició a intangibles als estats de fluxos de caixa

# 7 - Un altre horari a llarg termini

El següent pas d’aquesta modelització financera és preparar l’altra programació a llarg termini. Aquest és el calendari que preparem per a les "restes" que no tenen motors específics per a la previsió. En el cas de Colgate, els altres ítems a llarg termini (sobrants) eren els impostos diferits sobre la renda (passiu i actius), altres actius i altres passius.

Pas 7A: feu referència a les dades històriques del balanç

A més, calculeu els canvis en aquests elements.

Pas 7B: pronostiqueu els actius i els passius a llarg termini

Mantingueu constants els articles a llarg termini durant els anys projectats en cas que no hi hagi conductors visibles
Enllaceu els conceptes previstos a llarg termini amb el balanç de situació com es mostra a continuació

Pas 7C: fer referència a altres elements a llarg termini al balanç

Pas 7D: enllaceu els articles a llarg termini amb l'estat de fluxos d'efectiu

Tingueu en compte que si hem mantingut els actius i passius a llarg termini com a constants, el canvi que flueix al compte de fluxos d'efectiu seria nul.

# 8 - Modelització financera a Excel: emplenament del compte de pèrdues i guanys

Abans de seguir avançant en aquesta Modelització Financera basada en Excel, en realitat tornarem enrere i revisarem el compte de resultats
Poble de les accions mitjanes ponderades bàsiques històriques i del nombre mitjà ponderat diluït d’accions
Aquestes xifres estan disponibles a l’informe 10K de Colgate

Pas 8A: feu referència a les accions bàsiques i diluïdes

En aquesta etapa, suposem que el nombre futur d’accions bàsiques i diluïdes seguirà sent el mateix que el 2013.

Pas 8B: calculeu els beneficis per acció bàsics i diluïts

Amb això, estem preparats per passar a la nostra pròxima programació, és a dir, Programa de renda variable dels accionistes.

# 9 - Modelització financera: calendari patrimonial dels accionistes

El següent pas d’aquest model de formació financera a Excel és examinar el calendari patrimonial dels accionistes. L’objectiu principal d’aquest calendari és projectar elements relacionats amb el patrimoni net, com ara el patrimoni net, dividends, recompra d’accions, ingressos per opcions, etc. L’informe 10K de Colgate ens proporciona els detalls de les accions comunes i les accions de tresoreria dels darrers anys, tal com es mostra a continuació. Colgate 2013 - 10K, pàgina 68

Pas 9A: recompra compartida: ompliu els números històrics

Històricament, Colgate ha recuperat accions tal com podem veure a la programació anterior.
Indiqueu la recompra de les accions de Colgate (milions) al full Excel.
Enllaceu el EPS diluït històric del compte de pèrdues i guanys
L'import històric recompra s'ha de fer referència als estats de fluxos d'efectiu

A més, consulteu la recompra d'accions accelerades

Pas 9B: recompra compartida: calculeu el múltiple PE (múltiple EPS)

Calculeu el preu mitjà implícit al qual Colgate ha recuperat històricament les accions. Es calcula com a quantitat recompra / nombre d’accions
Calculeu el múltiple PE = preu de l’acció implicat / EPS

Pas 9C: recompra compartida: trobar la recompra compartida de Colgate

Colgate no ha anunciat oficialment quantes accions tenen intenció de tornar a comprar. L'única informació que indiquen les seves accions de 10.000 informes és que han autoritzat una recompra de fins a 50 milions d'accions. Colgate 2013 - 10K, pàgina 35

Per trobar el nombre d’accions comprades, hem d’assumir l’import de la recompra de les accions. Basant-me en l’import històric de la recompra, he pres aquest nombre com a 1.500 milions de dòlars per a tots els anys futurs.
Per tal de trobar el nombre d’accions comprades, necessitem el preu de l’acció implícit previst de la recompra potencial.
Preu de les accions implicat = EPS multiplexat de PE suposat
Es poden assumir futurs recompra PE en funció de les tendències històriques. Observem que Colgate ha comprat accions en un rang PE mitjà de 17x a 25x
A continuació es mostra la instantània de Reuters que ens ajuda a validar el rang de PE per a Colgate

www.reuters.com

En el nostre cas, he suposat que totes les recompenses futures de Colgate seran en un múltiple PE de 19x.
Utilitzant el PE de 19x, podem trobar el preu implícit = EPS x 19
Ara que hem trobat el preu implícit, podem trobar el nombre d’accions recompra = import de $ utilitzat per a la recompra / preu implícit

Pas 9D: opcions d’accions: poblar dades històriques

A partir del resum de les accions ordinàries i del patrimoni net, sabem el nombre d’opcions exercides cada any.

A més, també disposem dels ingressos de l’opció dels estats de fluxos d’efectiu (aprox)

Amb això, hauríem de ser capaços de trobar el preu de vaga efectiu

Colgate 2013 - 10K, pàgina 53

Tingueu en compte, a més, que les opcions sobre accions tenen terminis contractuals de sis anys i s’inverteixen en tres anys. Colgate 2013 - 10K, pàgina 69

Amb aquestes dades, emplenem les dades d’Opcions tal com es mostra a continuació També observem que el preu de vaga mitjà ponderat de les opcions sobre accions per al 2013 va ser de 42 dòlars i el nombre d’opcions que es poden exercir va ser de 24.151 milions Colgate 2013 - 10K, pàgina 70

Pas 9E - Opcions d'accions: cerqueu els ingressos de l'opció

Posant aquests números a les nostres dades d’opcions a continuació, observem que el producte obtingut per l’opció és de 1.014.000 milions de dòlars

Pas 9F - Opcions d’estoc: dades de la unitat d’estoc restringida de previsió

A més de les opcions sobre accions, hi ha unitats d’estoc restringides donades als empleats amb un període mitjà ponderat de 2,2 anys Colgate 2013 - 10K, pàgina 81

Aplegant aquestes dades al conjunt de dades Options Per simplicitat, no hem projectat l'emissió d'opcions (sé que aquesta no és la suposició correcta, però, per manca de dades, no em prenc cap emissió d'opcions més endavant. Acabem de prendre-les com a zero com es ressalta a la zona gris superior. A més, es preveu que les unitats d’estoc restringit siguin de 2,0 milions en el futur.

A més, consulteu el mètode de les accions del tresor

Pas 9G- Dividends: pronostiqueu els dividends

Previsioneu dividends estimats mitjançant la ràtio de pagament de dividends
Pagament fix per dividend per acció
Dels informes de 10.000 milions, extraiem tota la informació anterior sobre dividends
Amb la informació dels dividends pagats, podem esbrinar la ràtio de pagament del dividend = Total de dividends pagats / Ingressos nets.
He calculat la ràtio de pagament de dividends de Colgate com es mostra a continuació - Observem que la ràtio de pagament dels dividends ha estat, en general, del 50% al 60%. Fem una suposició de la ràtio de pagament dels dividends del 55% en els propers anys.
També podem vincular el resultat net previst del compte de pèrdues i guanys
Utilitzant tant el benefici net previst com la ràtio de pagament de dividends, podem trobar el total de dividends pagats

Pas 8H: previsió del compte de capital en la seva totalitat

Amb la previsió de la recompra d’accions, els ingressos de les opcions i els dividends pagats, estem preparats per completar el calendari patrimonial de l’accionista. Enllaceu-los per trobar el saldo de renda variable final de cada any, tal com es mostra a continuació.

Pas 9I: enllaçar el patrimoni final de l'accionista amb el balanç

Pas 9J: enllaceu els dividends, la recompra de les accions i les opcions passen a CF

# 10 - Horari pendent d'accions

El següent pas d'aquesta modelització financera en línia a la formació d'Excel és examinar el calendari de participació en accions. Resum del calendari pendent d'accions

Accions bàsiques: reals i mitjanes
Captureu efectes passats d'opcions i convertibles segons correspongui
Accions diluïdes: mitjana
Accions de referència recomprades i accions noves d’opcions exercides
Calcula les accions bàsiques previstes (reals)
Calculeu les accions bàsiques i diluïdes mitjanes
Referència de les accions projectades al compte de pèrdues i guanys (recordeu el compte de resultats acumular
Introduïu informació històrica sobre accions compartides
Nota: Aquest calendari s'integra habitualment amb el programa de renda variable

Pas 10A: introduïu els números històrics de l'informe 10K

Les accions emeses (realització real de les opcions) i les accions recompra es poden fer referència a la Planificació de l’accionariat
A més, l'entrada va ponderar un nombre mitjà d'accions i l'efecte de les opcions sobre accions durant els anys històrics.

Pas 10B: enllaçar les emissions i les recompra d’accions del programa de renda variable.

Accions bàsiques (finals) = Accions bàsiques (principi) + Emissions d’accions: accions recompra.

Pas 10C: trobeu les accions mitjanes ponderades bàsiques,

trobem una mitjana de dos anys com es mostra a continuació.
A més, afegiu l’efecte de les opcions i les unitats d’accions restringides (a les quals es fa referència des del calendari patrimonial de l’accionista) per trobar les accions mitjanes ponderades diluïdes.

Pas 10D: enllaçar les accions ponderades bàsiques i diluïdes al compte de pèrdues i guanys

Ara que hem calculat les accions mitjanes ponderades diluïdes, és hora que actualitzem el mateix al compte de resultats.
Vinculeu les accions mitjanes ponderades diluïdes previstes pendents amb el compte de pèrdues i guanys, tal com es mostra a continuació

Amb això, completem el calendari i el temps de manteniment de les accions per passar al nostre següent conjunt d’expressions.

# 11 - Emplenament dels extractes de fluxos d'efectiu

Per a nosaltres és important completar completament els estats de fluxos d’efectiu abans de passar al nostre proper i definitiu calendari en aquesta Modelització Financera, és a dir, la Programació del Deute Fins a aquesta etapa, només hi ha un parell de coses incompletes

Compte de pèrdues i guanys: els interessos / ingressos estan incomplets en aquesta fase
Balanç: els efectius i les partides de deute estan incompletes en aquesta etapa

Pas 11A: calcular el flux de caixa de les activitats de finançament

També podeu consultar el flux de caixa del finançament

Pas 11B: trobeu un augment (disminució) net en efectiu i equivalents en efectiu

Pas 11C = Completeu els estats de fluxos d'efectiu

Cerqueu efectiu i efectius equivalents al final de l’any al final de l’any.

Pas 11D: enllaceu els efectius i els seus equivalents amb el balanç.

Ara estem preparats per tenir cura del nostre últim i definitiu calendari, és a dir, del calendari de deutes i interessos

# 12- Modelització financera a Excel: calendari de deutes i interessos

El següent pas d’aquesta modelització financera en línia és completar el calendari de deutes i interessos. Resum del Deutes i interessos: calendari

Pas 12A: configureu un calendari de deutes

Feu referència al flux de caixa disponible per al finançament
Feu referència a totes les fonts de recursos propis i usos d’efectiu

Pas 12B: calculeu el flux de caixa a partir de l'amortització del deute

Feu referència al saldo inicial d’efectiu del balanç
Deduïu un saldo de caixa mínim. Hem assumit que Colgate voldria mantenir un mínim de 500 milions de dòlars cada any.

Omet Emissió / Amortització de deutes a llarg termini, efectiu disponible per a la secció Revolver Credit Facility i Revolver A l’informe 10K de Colgate, observem els detalls disponibles sobre el mecanisme de crèdit revolt Colgate 2013 - 10K, pàgina 35

També es proporciona la informació addicional sobre el deute sobre els pagaments del deute a llarg termini compromesos. Colgate 2013 - 10K, pàgina 36

Pas 12C: calculeu el deute final a llarg termini

Utilitzem el calendari de devolució del deute a llarg termini proporcionat anteriorment i calculem el saldo final de les amortitzacions del deute a llarg termini

Pas 12D: enllaçar els pagaments del deute a llarg termini.

Pas 12E: calcular els préstecs / amortitzacions discrecionals

Mitjançant la fórmula d’escombrat d’efectiu que es mostra a continuació, calculeu els préstecs / amortitzacions discrecionals.

Pas 12F: calculeu les despeses d'interès del deute a llarg termini

Calculeu el saldo mitjà de la facilitat de crèdit rotativa i del deute a llarg termini
Feu una suposició raonable d'un tipus d'interès basat en la informació proporcionada a l'informe 10K
Calcular Despeses d’interès total = saldo mitjà del deute x tipus d’interès

Trobeu la despesa total d’interessos = Interès (facilitat de crèdit giratori) + Interès (deute a llarg termini)

Pas 12G: enllaçar els ingressos del deute principal i el revòlver als fluxos de caixa

Pas 12H: corrent de referència i llarg termini al balanç

Delimiteu la part actual del deute a llarg termini i el deute a llarg termini tal com es mostra a continuació

Enllaceu el mecanisme de crèdit rotatiu, el deute a llarg termini i la part actual del deute a llarg termini amb el balanç

Pas 12I: calculeu els ingressos per interessos mitjançant el saldo mitjà de caixa

Pas 12J: enllaçar les despeses d'interessos i els ingressos per interessos al compte de pèrdues i guanys

Realitzeu la comprovació del balanç: Actiu total = Passiu + Patrimoni net

Pas 12K: auditeu el balanç

Si hi ha alguna discrepància, hem d’auditar el model i comprovar si hi ha errors d’enllaç

Curs de modelització financera recomanat

Espero que us hagi agradat la guia gratuïta de models financers d'Excel. Si voleu aprendre models financers a Excel mitjançant les nostres conferències de vídeo expert, també podeu consultar la nostra formació en banca d'inversions. Es tracta principalment de 99 cursos de formació en banca d'inversió. Aquest curs parteix dels conceptes bàsics i us conduirà al nivell avançat de feina en banca d’inversions. Aquest curs es divideix en 5 parts:

Primera part: formació en banca d'inversions: cursos bàsics
(26 cursos)
Part 2: Formació avançada en models de banca d'inversions
(20 cursos)
Part 3: Complements de banca d'inversió
(13 cursos)
Part 4: Cursos de la Fundació Bancària d’Inversions
(23 cursos)
Part 5 - Habilitats suaus per a banquers d'inversió
(17 cursos)

Descàrrega de models financers

Model financer d'Alibaba
Model financer de caixa IPO
Plantilles de modelització financera
Curs de Modelització Financera Bancària

Què després?

Si heu après alguna cosa nova o us ha agradat aquest model financer basat en Excel, deixeu un comentari a continuació. Feu-me saber què en penseu. Moltes gràcies i atenció. Feliç aprenentatge!