Modelado financiero en Excel (Guía + Plantilla paso a paso gratis)

Tabla de contenido

¿Qué es el modelado financiero en Excel?

El modelado financiero en Excel es el proceso de construcción de un modelo financiero para representar una transacción, operación, fusión, adquisición, información financiera para analizar cómo un cambio en una variable puede afectar el rendimiento final a fin de tomar una decisión sobre uno o más de los transacciones financieras antes mencionadas.

¿Qué es el modelado financiero en Excel?

El modelado financiero en Excel está en toda la web. Se ha escrito mucho sobre el aprendizaje de modelos financieros; sin embargo, la mayoría de las piezas de formación sobre modelos financieros son las mismas. Esto va más allá del galimatías habitual y explora el modelo financiero práctico utilizado por los banqueros de inversión y los analistas de investigación.

En esta guía gratuita de Excel de modelado financiero, tomaré un ejemplo de Colgate Palmolive y prepararé un modelo financiero completamente integrado desde cero.

Esta guía tiene más de 6000 palabras y me tomó tres semanas completarla. Guarde esta página para referencia futura y no olvide compartirla :-)

LO MÁS IMPORTANTE: descargue la plantilla de Excel de modelado financiero de Colgate para seguir las instrucciones

Descargar plantilla de modelo financiero de Colgate

Aprenda el modelado financiero paso a paso en Excel

Modelado financiero en Excel Training - Léame primero

Paso 1: descargue la plantilla del modelo financiero de Colgate. Usarás esta plantilla para el tutorial.

Descargar el modelo financiero de Colgate

Ingrese la dirección de correo electrónico Al continuar con el paso anterior, acepta nuestros Términos de uso y Política de privacidad.

Paso 2: tenga en cuenta que obtendrá dos plantillas: 1) Modelo financiero de Colgate Palmolive sin resolver 2) Modelo financiero de Colgate Palmolive resuelto

Paso 3- Trabajará en la plantilla de modelo financiero de Colgate Palmolive sin resolver . Siga las instrucciones paso a paso para preparar un modelo financiero totalmente integrado.

Paso 4 - ¡Feliz aprendizaje!

Tabla de contenido

He creado una tabla de contenido fácil de navegar para que pueda realizar este modelo financiero

¿Cómo construir un modelo financiero?
# 1 - Modelo financiero de Colgate - Histórico
# 2 - Análisis de relación de Colgate Palmolive
# 3 - Proyectar el estado de resultados
# 4- Programa de capital de trabajo
# 5 - Programa de depreciación
# 6 - Programa de amortización
# 7 - Otro programa a largo plazo
# 8 - Completar el estado de resultados
# 9 - Programa de equidad del accionista
# 10 - Programa excepcional de acciones
# 11 - Completar los estados de flujo de efectivo
# 12- Programa recomendado de deuda e intereses
Curso de modelado financiero
Modelos financieros gratuitos

Si es nuevo en el modelado financiero, eche un vistazo a esta guía sobre ¿Qué es el modelado financiero?

¿Cómo construir un modelo financiero en Excel?

Veamos cómo se construye un modelo financiero desde cero. Esta guía detallada de modelos financieros le proporcionará una guía paso a paso para crear un modelo financiero. El enfoque principal adoptado en esta guía de modelos financieros es modular. Básicamente, el sistema modular significa construir estados de cuenta centrales como el estado de resultados, el balance general y los flujos de efectivo utilizando diferentes módulos / programas. El enfoque clave es preparar cada declaración paso a paso y conectar todos los programas de apoyo a las declaraciones centrales al finalizar. Puedo entender que esto no esté claro ahora; sin embargo, se dará cuenta de que esto es muy fácil a medida que avanzamos. Puede ver a continuación varios programas / módulos de modelado financiero:

Tenga en cuenta lo siguiente:

Los estados centrales son el estado de resultados, el balance y los flujos de efectivo.
Los diferentes cronogramas son el cronograma de depreciación, el cronograma del capital de trabajo, el cronograma de intangibles, el cronograma del capital contable, el cronograma de otros elementos a largo plazo, el cronograma de la deuda, etc.
Los diferentes programas están vinculados a las declaraciones centrales una vez que se completan.
En esta guía de modelos financieros, crearemos un modelo económico integrado paso a paso de Colgate Palmolive desde cero.

# 1 - Modelado financiero en Excel - Proyecte los históricos

El primer paso en la Guía de modelos financieros es preparar los históricos.

Paso 1A: descargue los informes 10K de Colgate

“Los modelos financieros se preparan en Excel y los primeros pasos comienzan con saber cómo le ha ido a la industria en los últimos años. Comprender el pasado puede proporcionarnos conocimientos valiosos relacionados con el futuro de la empresa. Por lo tanto, el primer paso es descargar todas las finanzas de la empresa y completarlas en una hoja de Excel. Para Colgate Palmolive, puede descargar los informes anuales de Colgate Palmolive desde su Sección de Relación con Inversores. Una vez que haga clic en "Informe anual", encontrará la ventana que se muestra a continuación:

Paso 1B: crear la hoja de trabajo de estados financieros históricos

Si descarga 10K de 2013, observará que solo hay disponibles datos de estados financieros de dos años. Sin embargo, a los efectos del modelo financiero en Excel, el conjunto de datos recomendado es tener los estados financieros de los últimos 5 años. Descargue los últimos 3 años del informe anual y complete el histórico.
Muchas veces, estas tareas parecen demasiado aburridas y tediosas, ya que puede llevar mucho tiempo y energía formatear y poner el Excel en el formato deseado.
Sin embargo, no se debe olvidar que este es el trabajo que debe realizar solo una vez para cada empresa y, además, completar los históricos ayuda al analista a comprender las tendencias y los estados financieros.
Así que, por favor, no omita esto, descargue los datos y complete los datos (incluso si siente que esto es un trabajo de burro ;-))

Si desea omitir este paso, puede descargar directamente el modelo histórico de Colgate Palmolive aquí.

Estado de resultados de Colgate con histórico poblado

Datos históricos del balance de Colgate

# 2 - Análisis de relaciones

El segundo paso en el modelado financiero en Excel es realizar un análisis de razones.

Una clave para aprender el modelado financiero en Excel es poder realizar análisis fundamentales. Si el análisis elemental o el análisis de razones es algo nuevo para ti, te recomiendo que leas un poco en internet. Tengo la intención de realizar un análisis de proporciones en profundidad en una de mis próximas publicaciones, sin embargo, aquí hay una instantánea rápida de las proporciones de Colgate Palmolive.

IMPORTANTE: tenga en cuenta que he actualizado el análisis de proporción de Colgate en una publicación separada. Por favor, eche un vistazo a este análisis de proporciones completo.

Paso 2A - Análisis vertical de Colgate

En el estado de resultados, el análisis vertical es una herramienta universal para medir el desempeño relativo de la empresa de un año a otro en términos de costo y rentabilidad. Siempre debe incluirse como parte de cualquier análisis financiero. Aquí, los porcentajes se calculan en relación con las ventas netas, que se consideran 100%. Este esfuerzo de análisis vertical en el estado de resultados a menudo se denomina análisis de márgenes, ya que arroja los diferentes márgenes relacionados con las ventas.

Resultados del análisis vertical

El Margen de Utilidad había aumentado 240 puntos básicos de 56,2% en 2007 a 58,6% en 2013. Esto se debe principalmente a la disminución del Costo de Ventas.
La Utilidad de Explotación o EBIT también ha mostrado mejores márgenes, pasando del 19,7% en 2007 al 22,4% en 2012 (un incremento de 70 puntos básicos). Esto se debió a la disminución de los costos administrativos y generales de venta. Sin embargo, tenga en cuenta que los márgenes de EBIT se redujeron en 2013 a 20,4% debido al aumento de “Otros gastos”. Además, consulte la diferencia entre EBIT vs EBITDA
El Margen de Utilidad Neta aumentó de 12,6% en 2007 a 14,5% en 2012. Sin embargo, el Margen de Utilidad en 2013 disminuyó a 12,9%, principalmente debido al aumento de “otros gastos”.
Las ganancias por acción han aumentado constantemente desde el año fiscal 2007 hasta el año fiscal 2012. Sin embargo, hubo una ligera caída en el BPA del año fiscal 2013
Además, tenga en cuenta que la depreciación y la amortización se proporcionan por separado en el estado de resultados. Está incluido en el costo de ventas.

Paso 2B - Análisis horizontal de Colgate

El análisis horizontal es una técnica que se utiliza para evaluar las tendencias a lo largo del tiempo mediante el cálculo de porcentajes de aumento o disminución en relación con un año base. Proporciona un vínculo analítico entre cuentas calculadas en diferentes fechas utilizando la moneda con diferentes poderes de compra. En efecto, este análisis indexa los informes y compara la evolución de estos en el tiempo. Al igual que con la metodología de análisis vertical, surgirán problemas que deben investigarse y complementarse con otras técnicas de análisis financiero. El objetivo es buscar síntomas de problemas que puedan diagnosticarse mediante métodos adicionales.

Veamos el análisis horizontal de Colgate.

Resultados del análisis horizontal

Vemos que las Ventas Netas aumentaron un 2.0% en 2013.
Además, tenga en cuenta la tendencia en el costo de ventas. Vemos que no han crecido en la misma proporción que las Ventas.
Estas observaciones son extremadamente útiles mientras hacemos modelos financieros en Excel.

Paso 2C - Ratios de liquidez de Colgate

Los índices de liquidez miden la relación de los activos más líquidos de una empresa (los que se pueden convertir más fácilmente en efectivo) con los pasivos corrientes. Los ratios de liquidez más comunes son el coeficiente circulante, el coeficiente de prueba ácida (o activo rápido).
Ratios de rotación como rotación de cuentas por cobrar, rotación de inventario y rotación de cuentas por pagar

Aspectos destacados clave de los ratios de liquidez

El coeficiente actual de Colgate es más significativo que 1.0 para todos los años. Esto implica que los activos circulantes son más importantes que los pasivos circulantes y tal vez Colgate tenga suficiente liquidez.
La relación rápida de Colgate está en el rango de 0,6-0,7; esto significa que Colgates Cash y valores negociables pueden pagar hasta el 70% de los pasivos corrientes. Esta parece una buena situación para Colgate.
El ciclo de cobranza de efectivo había disminuido de 43 días en 2009 a 39 días en 2013. Esto se debe principalmente a la reducción en el período de cobranza de cuentas por cobrar.

Además, eche un vistazo a este artículo detallado sobre el ciclo de conversión de efectivo.

Paso 2D: ratios de rentabilidad operativa de Colgate

Ratios de rentabilidad la capacidad de una empresa para generar ganancias en relación con las ventas, los activos y el capital.

Aspectos destacados clave: ratios de rentabilidad de Colgate

Como podemos ver en la tabla anterior, Colgate tiene un ROE cercano al 100%, lo que implica excelentes retornos para los accionistas.

Paso 2E - Análisis de riesgo de Colgate

A través del Análisis de Riesgos, tratamos de evaluar si las empresas podrán pagar sus obligaciones (deuda) a corto y largo plazo. Calculamos ratios de apalancamiento que se centran en la suficiencia de activos o generación a partir de activos. Las tarifas que se miran son

Coeficiente de endeudamiento
Ratio de deuda
Porcentaje de cobertura de intereses
La relación de deuda a capital ha aumentado constantemente a un nivel más alto de 2.23x. Esto significa un mayor apalancamiento financiero y riesgos en el mercado.
Sin embargo, el índice de cobertura de intereses es muy alto, lo que indica un menor riesgo de incumplimiento en el pago de intereses.

# 3 - Modelado financiero en Excel - Proyecte el estado de resultados

El tercer paso en el modelado financiero es pronosticar el estado de resultados, en el que comenzaremos con el modelado de los elementos de ventas o ingresos.

Paso 3A - Proyecciones de ingresos

Para la mayoría de las empresas, los ingresos son un factor fundamental del desempeño económico. Un modelo de ingresos lógico y bien diseñado que refleje con precisión el tipo y la cantidad de flujos de ingresos es extremadamente importante. Hay tantas formas de crear un calendario de ingresos como negocios. Algunos tipos comunes incluyen:

Crecimiento de ventas: el supuesto de crecimiento de ventas en cada período define el cambio con respecto al período anterior. Este es un método simple y de uso común, pero no ofrece información sobre los componentes o la dinámica del crecimiento.
Efectos inflacionarios y de volumen / mezcla: en lugar de una simple suposición de crecimiento, se utilizan un factor de inflación de precios y un factor de volumen. Este enfoque útil permite modelar los costos fijos y variables en empresas multiproducto y tiene en cuenta los movimientos de precio vs volumen.
Volumen unitario, cambio de volumen, precio medio y cambio de precio: este método es apropiado para empresas que tienen una combinación de productos simple; permite analizar el impacto de varias variables clave.
Tamaño y crecimiento del mercado en dólares: participación de mercado y cambio en la participación: útil para los casos en los que se dispone de información sobre la dinámica del mercado y en los que es probable que estas suposiciones sean fundamentales para tomar una decisión. Por ejemplo, la industria de las telecomunicaciones.
Tamaño y crecimiento del mercado unitario: es más detallado que el caso anterior y es útil cuando el precio en el mercado es una variable crucial. (Para una empresa con una estrategia de descuento de precios, por ejemplo, o el mejor jugador de nicho con precio premium), por ejemplo, el mercado de automóviles de lujo
Capacidad de volumen, tasa de utilización de la capacidad y precio promedio: estas suposiciones pueden ser importantes para las empresas donde la capacidad de producción es esencial para la decisión. (En la compra de capacidad adicional, por ejemplo, o para determinar si la expansión requeriría nuevas inversiones).
Disponibilidad y precio del producto
Los ingresos fueron impulsados por la inversión en capital, marketing o I + D
Ingresos basados en la base instalada (ventas continuas de repuestos, desechables, servicio y complementos, etc.). Los ejemplos incluyen negocios clásicos de cuchillas de afeitar y negocios como computadoras donde las ventas de servicios, software y actualizaciones son esenciales. Modelar la base instalada es clave (nuevas adiciones al piso, desgaste en el suelo, ingresos continuos por cliente, etc.).
Basado en empleados: por ejemplo, ingresos de empresas de servicios profesionales o empresas de ventas como corredores. El modelo debe centrarse en la dotación de personal neta, los ingresos por empleado (a menudo basados en horas facturables). Los modelos más detallados incluirán la antigüedad y otros factores que afectan los precios.
Basado en tiendas, instalaciones o pies cuadrados: las empresas minoristas a menudo se modelan en función de las tiendas (tiendas antiguas más tiendas nuevas cada año) y los ingresos por tienda.
Basado en factores de ocupación: este enfoque es aplicable a aerolíneas, hoteles, cines y otras empresas con costos marginales bajos.

Proyectar los ingresos de Colgate

Veamos ahora el informe Colgate 10K 2013. Observamos que en el estado de resultados, Colgate no ha proporcionado información segmentaria; sin embargo, como información adicional, Colgate ha proporcionado algunos detalles de los segmentos en la página 87 Fuente - Colgate 2013 - 10K, página 86

Dado que no tenemos más información sobre las características, proyectaremos las ventas futuras de Colgate sobre la base de estos datos disponibles. Usaremos el enfoque de crecimiento de ventas en todos los segmentos para derivar los pronósticos. Por favor, vea la imagen de abajo. Hemos calculado la tasa de crecimiento año tras año para cada elemento. Ahora podemos asumir un porcentaje de crecimiento de ventas basado en las tendencias históricas y proyectar los ingresos en cada parte. Las ventas netas totales son la suma total del segmento de nutrición de mascotas , cuidado oral, personal y doméstico.

Paso 3B - Proyecciones de costos

Porcentaje de ingresos: simple, pero no ofrece información sobre ningún apalancamiento (economía de escala o carga de costo fijo
Costos distintos de la depreciación como porcentaje de los ingresos y la depreciación de un programa diferente: este enfoque es realmente el mínimo aceptable en la mayoría de los casos y solo permite un análisis parcial del apalancamiento operativo.
Costos variables basados en ingresos o volumen, costos fijos basados en tendencias históricas y depreciación de un programa separado: este enfoque es el mínimo necesario para el análisis de sensibilidad de la rentabilidad basado en múltiples escenarios de ingresos

Proyecciones de costos para Colgate

Para proyectar el costo, será útil el análisis vertical realizado anteriormente. Echemos un vistazo al análisis vertical:

Como ya hemos pronosticado Ventas, todos los demás costos son algunos márgenes de estas Ventas.
El enfoque es tomar las pautas de los márgenes históricos de costos y gastos y luego pronosticar el margen futuro.
Por ejemplo, el costo de ventas ha estado en el rango del 41% al 42% durante los últimos cinco años. Podemos considerar pronosticar los márgenes sobre esta base.
Asimismo, los Gastos de Administración, Generales y de Venta han estado históricamente en el rango de 34% -36%. Podemos asumir el margen de gastos de venta, generales y administrativos futuros sobre esta base. Asimismo, podemos continuar con otro conjunto de gastos.

Usando los márgenes anteriores, podemos encontrar los valores reales mediante cálculos retroactivos.

Para calcular la provisión para impuestos, usamos el supuesto de Tasa de Impuesto Efectiva.

Además, tenga en cuenta que no completamos la fila “Gastos por intereses (ingresos)”, ya que tendremos una revisión del estado de resultados en una etapa posterior.
Gastos e ingresos por intereses.
Tampoco hemos calculado la Depreciación y Amortización, que ya se incluyó en el Costo de Ventas.
Esto completa la declaración de ingresos (¡al menos por el momento!)

# 4- Modelado financiero - Programa de capital de trabajo

Ahora que hemos completado la declaración de ingresos, el cuarto paso en el modelado financiero es mirar el programa de capital de trabajo.

A continuación se muestran los pasos que se deben seguir para el programa de capital de trabajo

Paso 4A: vincular las ventas netas y el costo de ventas

Paso 4B: consulte los datos del balance general relacionados con el capital de trabajo

Hacer referencia a los datos pasados del balance
Calcular el capital de trabajo neto
Llegar a un aumento / disminución del capital de trabajo.
Tenga en cuenta que no hemos incluido deuda a corto plazo y efectivo y equivalentes de efectivo en el capital de trabajo. Trataremos la deuda y el efectivo y equivalentes de efectivo por separado.

Paso 4C - Calcular las tasas de rotación

Calcular ratios y porcentajes históricos
Utilice el saldo final o promedio
Ambos son aceptables siempre que se mantenga una consistencia prolongada

Paso 4D - Complete los supuestos para futuros elementos de capital de trabajo

Ciertos elementos sin un conductor destacado generalmente se asumen en cantidades constantes
Asegúrese de que las suposiciones sean razonables y estén en línea con el negocio.

Paso 4E - Proyecte los saldos futuros del capital de trabajo

Paso 4F - Calcule los cambios en el capital de trabajo

Llegar a los flujos de efectivo basados en artículos de línea individuales
¡Asegúrese de que las señales sean precisas!

Paso 4G: vincule el capital de trabajo previsto con el balance general

Paso 4H: vincular el capital de trabajo con el estado de flujo de efectivo

# 5 - Modelado financiero en Excel - Programa de depreciación

Con la finalización del cronograma de capital de trabajo, el siguiente paso en este Modelado Financiero es proyectar el Capex de Colgate y proyectar las cifras de Depreciación y Activos. Colgate 2013 - 10K, página 49

La depreciación y la amortización no se proporcionan como una partida separada; sin embargo, se incluye en el costo de ventas
En tales casos, consulte los estados de flujo de efectivo donde encontrará los gastos de depreciación y amortización. Además, tenga en cuenta que las cifras siguientes son 1) Depreciación 2) Amortización. Entonces, ¿cuál es el número de depreciación?
Saldo final para PPE = Saldo inicial + Capex - Depreciación - Ajuste por ventas de activos (ecuación BASE)

Paso 5A: vincule las cifras de ventas netas en el programa de depreciación

Configurar las líneas de pedido
Ventas netas de referencia
Ingrese los gastos de capital pasados
Llegue a Capex como% de las ventas netas

Paso 5B - Previsión de las partidas de gastos de capital

Para pronosticar el gasto de capital, existen varios enfoques. Una práctica común es mirar los comunicados de prensa, las proyecciones de la gerencia, MD&A para comprender la opinión de la empresa sobre los gastos de capital futuros
Si la empresa ha proporcionado orientación sobre futuros gastos de capital, podemos tomar esas cifras directamente.
Sin embargo, si los números de Capex no están disponibles directamente, entonces podemos calcularlo de forma cruda usando Capex como% de ventas (como se hace a continuación)
Utilice su criterio basado en el conocimiento de la industria y otros factores razonables.

Paso 5C - Información pasada de referencia

Usaremos Saldo final para PPE = Saldo inicial + Capex - Depreciación - Ajuste por ventas de activos (ecuación BASE)
Es complicado conciliar PP&E pasados debido a actualizaciones, ventas de activos, etc.
Por lo tanto, se recomienda no conciliar el PPE anterior, ya que puede generar cierta confusión.

Política de depreciación de Colgate

Observamos que Colgate no ha proporcionado explícitamente un desglose detallado de los Activos. En su lugar, han agrupado todos los activos en terrenos, edificios, maquinaria y otros equipos.
Además, la vida útil de la maquinaria y el equipo se proporciona en un rango. En este caso, tendremos que hacer algunas conjeturas para llegar a la vida útil media que les queda a los activos.
Además, no se proporciona orientación sobre la vida útil para "Otros equipos". Tendremos que estimar la vida útil de otros equipos

Colgate 2013 - 10K, página 55

A continuación se muestra el desglose de los detalles de propiedad, planta y equipo de 2012 y 2013

Colgate 2013 - 10K, página 91

Paso 5D - Estimar la ruptura de la propiedad, planta y equipo (PPE)

Primero, encuentre los pesos de los activos del PPE actual (2013)
Asumiremos que estas ponderaciones de activos de PPE de 2013 continuarán avanzando.
Usamos estas ponderaciones de activos para calcular el desglose del gasto de capital estimado

Paso 5E - Estime la depreciación de los activos

Tenga en cuenta que no calculamos la depreciación de la tierra ya que la tierra no es un activo depreciable
Para estimar la depreciación de las mejoras del edificio, primero hacemos uso de la siguiente estructura.
La depreciación aquí se divide en dos partes: 1) depreciación del Activo de Mejoras al Edificio ya listado en el balance general, 2) depreciación de las futuras mejoras del Edificio.
Para calcular la depreciación de las mejoras de construcción enumeradas en el activo, utilizamos el método de depreciación de línea recta simple.
Para calcular la depreciación futura, primero transponemos el Capex usando la función TRANSPOSE en Excel.
Calculamos la depreciación de las contribuciones de activos de cada año.
Además, la depreciación del primer año se divide por dos, ya que asumimos la convención de mitad de año para el despliegue de activos.

Depreciación total de la mejora del edificio = depreciación del Activo de mejoras del edificio ya enumerado en el balance general + depreciación del futuro Mejoras del edificio El proceso anterior para estimar la depreciación se utiliza para calcular la depreciación de 1) Equipo y maquinaria de fabricación y 2) otros equipos como mostrado a continuación.

Otros tipos de equipos

Depreciación total de Colgate = Depreciación (mejoras de construcción) + Depreciación (maquinaria y equipo) + Depreciación (equipo adicional) Una vez que hayamos descubierto las cifras de depreciación real, podemos ponerlas en la ecuación BASE como se muestra a continuación.

Con esto, obtenemos las cifras de PP&E neto final para cada uno de los años

Paso 5F: vincule el PP&E neto al balance general

# 6 - Programa de amortización

El sexto paso en este modelo financiero en Excel es pronosticar la amortización. Tenemos dos categorías amplias para considerar aquí: 1) Fondo de comercio y 2) Otros intangibles.

Paso 6A: previsión del fondo de comercio

Colgate 2013 - 10K, página 61

El fondo de comercio aparece en el balance general cuando una empresa adquiere otra empresa. Suele ser complicado proyectar el Fondo de Comercio para años futuros.
Sin embargo, el fondo de comercio está sujeto a pruebas de deterioro anualmente, que son realizadas por la propia empresa. Los analistas no están en condiciones de realizar tales pruebas y preparar estimaciones de deterioros.
La mayoría de los analistas no proyectan buena voluntad; simplemente mantienen esto como constante, lo que también haremos en nuestro caso.

Paso 6B - Previsión de otros activos intangibles

Como se indica en el Informe 10K de Colgate, la mayoría del intangible de vida finita está relacionado con la adquisición de Sanex.
Las "adiciones a intangibles" también son complicadas de proyectar
El informe 10K de Colgate nos proporciona los detalles de los próximos cinco años de gastos de amortización.
Usaremos estas estimaciones en nuestro modelo financiero Colgate 2013 - 10K, página 61

Paso 6C - Los intangibles netos finales están vinculados a los "Otros activos intangibles".

Paso 6D: vincular la depreciación y la amortización a los estados de flujo de efectivo

Paso 6E: vincular el Capex y la adición de intangibles a los estados de flujo de efectivo

# 7 - Otro programa a largo plazo

El siguiente paso en este modelo financiero es preparar el Otro cronograma a largo plazo. Aquí es cuando nos preparamos para las "sobras" que no tienen impulsores específicos para la previsión. En el caso de Colgate, los otros Ítems de Largo Plazo (sobras) fueron Impuesto a la Renta Diferido (pasivo y activo), Otras inversiones y otros pasivos.

Paso 7A: consulte los datos históricos del balance general

Además, calcule los cambios en estos elementos.

Paso 7B: pronostique los activos y pasivos a largo plazo

Mantenga los elementos a largo plazo constantes durante los años proyectados en caso de que no haya controladores visibles
Vincule los elementos pronosticados a largo plazo al balance general como se muestra a continuación

Paso 7C: referencia a otros elementos a largo plazo en el balance general

Paso 7D: vincule los elementos a largo plazo al estado de flujo de efectivo

Tenga en cuenta que si hemos mantenido los activos y pasivos a largo plazo como constantes, el cambio que fluye al estado de flujo de efectivo sería cero.

# 8 - Modelado financiero en Excel - Completar el estado de resultados

Antes de avanzar más en este modelo financiero basado en Excel, volveremos y revisaremos el estado de resultados.
Complete el promedio ponderado básico histórico de acciones y el número promedio ponderado diluido de acciones.
Estas cifras están disponibles en el informe 10K de Colgate

Paso 8A: referencia a las acciones básicas y diluidas

En esta etapa, suponga que el número futuro de acciones primarias y diluidas seguirá siendo el mismo que en 2013.

Paso 8B: Calcule las ganancias por acción básicas y diluidas.

Con esto, estamos listos para pasar a nuestro próximo programa, es decir, el Programa de equidad del accionista.

# 9 - Modelización financiera - Programa de capital social

El siguiente paso en esta capacitación sobre modelado financiero en Excel es consultar el Cuadro de capital social del accionista. El objetivo principal de este cronograma es proyectar elementos relacionados con el capital social, como capital social, dividendos, recompra de acciones, ingresos de opciones, etc. El informe 10K de Colgate nos proporciona los detalles de las acciones ordinarias y las actividades de autocartera en los últimos años, como se muestra a continuación. . Colgate 2013 - 10K, página 68

Paso 9A - Recompra de acciones: complete los números históricos

Históricamente, Colgate ha recomprado acciones, como podemos ver en el calendario anterior.
Complete la recompra de acciones de Colgate (millones) en la hoja de Excel.
Vincular el EPS diluido histórico del estado de resultados
El Monto histórico de recompra debe consultarse en los estados de flujo de efectivo.

Además, eche un vistazo a Recompra acelerada de acciones.

Paso 9B - Recompra de acciones: Calcule el múltiplo PE (múltiplo EPS)

Calcule el precio promedio implícito al que Colgate ha recomprado acciones históricamente. Esto se calcula como el Monto Recomprado / Número de acciones.
Calcule el múltiplo de PE = Precio de acción implícito / EPS

Paso 9C - Recompra de acciones: encontrar la recompra de acciones de Colgate

Colgate no ha hecho ningún anuncio oficial de cuántas acciones pretenden recomprar. La única información que comparte su informe 10K es que han autorizado una recompra de hasta 50 millones de acciones. Colgate 2013 - 10K, página 35

Para encontrar el número de acciones recompradas, debemos asumir el Monto de recompra de acciones. Basado en el monto histórico de recompra, he tomado este número como $ 1,500 millones para todos los años futuros.
Para encontrar el número de acciones recompradas, necesitamos el precio implícito proyectado de la acción de la recompra potencial.
Precio real de la acción = EPS multiplexado de PE asumido.
Se puede suponer un múltiplo de PE de recompra futura basado en tendencias históricas. Observamos que Colgate ha recomprado acciones en un rango de PE promedio de 17x - 25x
A continuación se muestra la instantánea de Reuters que nos ayuda a validar el rango de PE para Colgate

www.reuters.com

En nuestro caso, he asumido que todas las recompras futuras de Colgate estarán en un múltiplo PE de 19x.
Usando el PE de 19x, podemos encontrar el precio implícito = EPS x 19
Ahora que hemos encontrado el precio implícito, podemos ver el número de acciones recompradas = $ cantidad utilizada para la recompra / precio implícito.

Paso 9D - Opciones sobre acciones: completar datos históricos

A partir del resumen de acciones ordinarias y capital contable, sabemos el número de opciones ejercidas cada año.

Además, también tenemos los ingresos de la opción de los estados de flujo de efectivo (aprox.)

Con esto, deberíamos poder encontrar un precio de ejercicio efectivo.

Colgate 2013 - 10K, página 53

Además, tenga en cuenta que las opciones sobre acciones tienen términos contractuales de seis años y se consolidan durante tres años. Colgate 2013 - 10K, página 69

Con estos datos, completamos los datos de Opciones como se muestra a continuación. También notamos que el precio de ejercicio promedio ponderado de las opciones sobre acciones para 2013 fue de $ 42 y el número de opciones ejercitables fue de 24.151 millones Colgate 2013 - 10K, Página 70

Paso 9E - Opciones sobre acciones: Encuentre las ganancias de la opción.

Al poner estos números en nuestros datos de opciones a continuación, observamos que los ingresos de la opción son $ 1.014 mil millones.

Paso 9F - Opciones sobre acciones: datos de unidades de acciones restringidas de pronóstico

Además de las opciones sobre acciones, existen Unidades de Acciones Restringidas que se otorgan a los empleados con un período promedio ponderado de 2.2 años Colgate 2013 - 10K, Página 81

Completar estos datos en el conjunto de datos de Opciones Por simplicidad, no hemos proyectado la emisión de opciones (sé que esta no es la suposición correcta; sin embargo, debido a la falta de datos, no voy a seguir adelante con más problemas de opciones. Acabamos de tomar estos como cero, como se resalta en el área gris anterior Además, se proyecta que las unidades de existencias restringidas sean de 2.0 millones en el futuro.

Además, eche un vistazo al método de autocartera.

Paso 9G- Dividendos: pronostique los dividendos

Pronostique los dividendos estimados utilizando el índice de pago de dividendos.
Pago de dividendos fijos Pago por acción
De los informes de 10K, extraemos toda la información pasada sobre dividendos.
Con la información de los dividendos pagados, podemos averiguar la relación de pago de dividendos = Total de dividendos pagados / Ingresos netos.
He calculado el índice de pago de dividendos de Colgate como se ve a continuación. Observamos que el índice de pago de dividendos ha estado en general en el rango del 50% al 60%. Supongamos una tasa de pago de dividendos del 55% en los años futuros.
También podemos vincular el ingreso neto proyectado del estado de resultados.
Usando tanto el ingreso neto proyectado como el índice de pago de dividendos, podemos encontrar el total de dividendos pagados.

Paso 8H: cuenta de capital de previsión en su totalidad

Con el pronóstico de recompra de acciones, ingresos de opciones y dividendos pagados, estamos listos para completar el Cuadro de capital contable. Vincule todos estos para encontrar el Saldo de capital final de cada año, como se muestra a continuación.

Paso 9I - Vincular el patrimonio neto del accionista final al balance general

Paso 9J - Vincular dividendos, recompra de acciones y opciones procede a CF

# 10 - Programa excepcional de acciones

El siguiente paso en este modelo financiero en línea en la capacitación de Excel es mirar el Programa de acciones pendientes. Resumen de acciones en circulación

Acciones básicas: reales y promedio
Capturar efectos pasados de opciones y convertibles según corresponda
Acciones diluidas - promedio
Acciones de referencia recompradas y acciones nuevas de opciones ejercidas
Calcular los porcentajes brutos pronosticados (reales)
Calcular acciones básicas y diluidas promedio
Haga referencia a las acciones proyectadas a la cuenta de resultados (recuerde la acumulación de la cuenta de resultados)
Ingrese información histórica sobre acciones en circulación
Nota : este programa se integra comúnmente con el programa de equidad

Paso 10A: ingrese los números históricos del informe de 10K

Las acciones emitidas (realización real de opciones) y las acciones recompradas se pueden consultar en el Cuadro de acciones del accionista.
La entrada ponderó un número promedio de acciones y el efecto de las opciones sobre acciones para los años históricos.

Paso 10B - Vincular las emisiones y recompras de acciones del Programa de acciones de capital.

Acciones Básicas (Final) = Acciones Básicas (Inicio) + Emisiones de Acciones - Acciones Recompradas.

Paso 10C - Encuentre las acciones promedio ponderadas básicas,

encontramos un promedio de dos años, como se muestra a continuación.
Además, agregue el efecto de las opciones y unidades de acciones restringidas (a las que se hace referencia en la lista de acciones del accionista) para encontrar las acciones promedio ponderadas diluidas.

Paso 10D: vincular las acciones ponderadas básicas y diluidas al estado de resultados

Ahora que hemos calculado el promedio ponderado de acciones diluidas, es hora de que actualicemos el mismo en el Estado de resultados.
Vincular las acciones promedio ponderadas diluidas pronosticadas en circulación al estado de resultados como se muestra a continuación

Con esto, completamos el Programa de acciones pendientes y el tiempo para pasar a nuestro próximo conjunto de estados de cuenta.

# 11 - Completar los estados de flujo de efectivo

Es importante para nosotros completar completamente los estados de flujo de efectivo antes de pasar a nuestro siguiente y último programa en este Modelo financiero, es decir, el Programa de deuda. Hasta esta etapa, solo quedan un par de cosas incompletas

Estado de resultados: los gastos / ingresos por intereses están incompletos en esta etapa
Balance general: el efectivo y las partidas de deuda están incompletas en esta etapa

Paso 11A - Calcular el flujo de caja para actividades de financiamiento

Además, consulte el flujo de caja de la financiación

Paso 11B - Encuentre el aumento (disminución) neto en efectivo y equivalentes de efectivo

Paso 11C = Complete los estados de flujo de efectivo

Encuentre el efectivo y equivalentes de efectivo al final del año.

Paso 11D: vincule el efectivo y los equivalentes de efectivo al balance general.

Ahora estamos listos para encargarnos de nuestro último y último programa, es decir, Programa de Deuda e Intereses

# 12- Modelado financiero en Excel - Cronograma de deuda e intereses

El siguiente paso en este modelo financiero en línea es completar el programa de deuda e intereses. Resumen de la deuda y los intereses - Programa

Grado 12A: establezca un programa de deuda

Consulte el flujo de caja disponible para financiamiento
Haga referencia a todas las fuentes de capital y usos del efectivo

Paso 12B - Calcule el flujo de efectivo del pago de la deuda

Haga referencia al saldo de caja inicial del balance general
Deduzca un saldo mínimo en efectivo. Hemos asumido que a Colgate le gustaría quedarse con un mínimo de $ 500 millones cada año.

Omita la emisión / reembolsos de deuda a largo plazo, el efectivo disponible para la línea de crédito rotatorio y la sección Revolver por ahora . Tomamos nota de los detalles disponibles sobre la Línea de Crédito Revolucionada Colgate 2013 - 10K, Página 35

También se proporciona en información adicional sobre la deuda los reembolsos de la deuda a largo plazo comprometidos. Colgate 2013 - 10K, página 36

Paso 12C - Calcule la deuda final a largo plazo.

Usamos el programa de amortización de la deuda a largo plazo proporcionado anteriormente y calculamos el saldo final de los pagos de la deuda a largo plazo.

Paso 12D: vincule los pagos de la deuda a largo plazo.

Paso 12E - Calcule los préstamos / reducciones discrecionales.

Usando la fórmula de barrido de efectivo, como se muestra a continuación, calcule los préstamos / pagos discrecionales.

Paso 12F - Calcule el gasto por intereses de la deuda a largo plazo

Calcule el saldo promedio de la línea de crédito renovable y la deuda a largo plazo
Haga una suposición razonable para una tasa de interés basada en la información proporcionada en el informe 10K
Calcular el gasto total por intereses = saldo promedio de la deuda x tasa de interés

Encuentre el gasto total por intereses = interés (línea de crédito renovable) + interés (deuda a largo plazo)

Paso 12G - Deuda de enlace principal y reducciones de revólver a los flujos de efectivo

Paso 12H - Referencia actual y a largo plazo al balance general

Demarque la parte actual de la deuda a largo plazo y la deuda a largo plazo como se muestra a continuación

Vincular la línea de crédito renovable, la deuda a largo plazo y la parte actual de la deuda a largo plazo al balance general

Paso 12I - Calcule los ingresos por intereses utilizando el saldo de efectivo promedio

Paso 12J: vincular los gastos por intereses y los ingresos por intereses al estado de resultados

Realice la verificación del balance general: Activo total = Pasivo + Capital contable

Paso 12K: auditar el balance general

Si hay alguna discrepancia, debemos auditar el modelo y verificar si hay errores de vinculación.

Curso recomendado de modelado financiero

Espero que haya disfrutado de la guía Excel de modelado financiero gratuito. Si desea aprender modelado financiero en Excel a través de nuestras conferencias en video de expertos, también puede consultar nuestra capacitación en banca de inversión. Se trata principalmente de un paquete de formación de banca de inversión de 99 cursos. Este curso comienza con lo básico y lo lleva al nivel avanzado de Trabajo de banca de inversión. Este curso se divide en cinco partes:

Parte 1 - Capacitación en banca de inversión - Cursos básicos
(26 cursos)
Parte 2 - Capacitación avanzada en modelado de banca de inversión
(20 cursos)
Parte 3 - Complementos de banca de inversión
(13 cursos)
Parte 4 - Cursos básicos de banca de inversión
(23 cursos)
Parte 5 - Habilidades sociales para banqueros de inversión
(17 cursos)

Descargar modelos financieros

Modelo financiero de Alibaba
Cuadro de modelo financiero de OPI
Plantillas de modelado financiero
Curso de modelado financiero bancario

¿Qué sigue?

Si aprendió algo nuevo o disfrutó de este modelo financiero basado en Excel, deje un comentario a continuación. Déjame saber lo que piensas. Muchas gracias y cuídate. ¡Feliz aprendizaje!