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

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 y se ha escrito mucho sobre el aprendizaje del modelado financiero, sin embargo, la mayoría de las piezas de capacitación en modelado financiero son exactamente 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ó 3 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

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

# 1 - Modelo financiero de Colgate - Histórico
# 2 - Análisis de relación de Colgate Palmolive
# 3 - Proyección del 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 de deuda e intereses recomendado
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. El enfoque modular esencialmente significa que construimos 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 puede no estar claro a partir de ahora, sin embargo, se darán 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 cronogramas adicionales son el cronograma de depreciación, el cronograma del capital de trabajo, el cronograma de intangibles, el cronograma del patrimonio de los accionistas, el cronograma de otros elementos a largo plazo, el cronograma de la deuda, etc.
Los programas adicionales están vinculados a las declaraciones centrales una vez que se completan
En esta guía de modelos financieros, crearemos un modelo financiero 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 hacer solo una vez para cada empresa y además, completar los históricos ayuda al analista a comprender las tendencias y los estados financieros.
Por lo tanto, no se salte 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 fundamental 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 conoce como análisis de márgenes, ya que arroja los diferentes márgenes en relación con las ventas.

Resultados del análisis vertical

El Margen de Utilidad ha 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, aumentando así del 19,7% en 2007 al 22,4% en 2012 (un aumento 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 EBIT se redujeron en 2013 al 20,4% debido a un aumento en “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 FY2013
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 moneda con diferente poder adquisitivo. En efecto, este análisis indexa las cuentas y compara la evolución de estas 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 técnicas adicionales.

Veamos el análisis horizontal de Colgate

Resultados del análisis horizontal

Vemos que las Ventas Netas han aumentado un 2,0% en 2013.
Además, observe 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 entre los activos más líquidos de una empresa (los que se pueden convertir más fácilmente en efectivo) y los pasivos corrientes. Los índices de liquidez más comunes son: Índice actual Índice de prueba ácida (o activo rápido) Índice de efectivo
Ratios de rotación como rotación de cuentas por cobrar, rotación de inventario y rotación de cuentas por pagar

Principales aspectos destacados de los ratios de liquidez

La razón actual de Colgate es mayor a 1.0 para todos los años. Esto implica que los activos circulantes son mayores que los pasivos circulantes y tal vez Colgate tenga suficiente liquidez.
Quick Ratio 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 situación razonable para Colgate.
El ciclo de cobranza de efectivo ha 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 grandes 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 proporciones 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 significa menos 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 diseñar un programa 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 un simple supuesto 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 frente a 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 clave. (Para una empresa con una estrategia de descuento de precios, por ejemplo, o un jugador de nicho de precio premium mejor en su clase), por ejemplo, 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 importante 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 importantes. Modelar la base instalada es clave (nuevas incorporaciones a la base, desgaste en la base, 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 tienda, instalación 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.

Proyección de 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 los segmentos, 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 segmento. Ahora podemos asumir un porcentaje de crecimiento de ventas basado en las tendencias históricas y proyectar los ingresos en cada segmento. 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 separado: 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 5 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 por intereses e ingresos por intereses.
Tampoco hemos calculado la Depreciación y Amortización que ya se ha incluido 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 controlador obvio 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 el proyecto de 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 incluyen 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. También 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. Un enfoque 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 calcularlos 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 muy difícil conciliar PP&E anteriores 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. Más bien han aporreado 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 de los gastos de capital estimados

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 de Edificios que ya figura en el balance general 2) Depreciación de futuras Mejoras de Edificios
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 por contribución de activos de cada año
Además, la depreciación del primer año se divide por 2, 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 que ya figura en el balance general + depreciación de las mejoras futuras 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 mostrado a continuación.

Otros tipos de equipos

Depreciación total de Colgate = Depreciación (Mejoras en el edificio) + Depreciación (Maquinaria y equipo) + Depreciación (otros equipos) Una vez que hayamos descubierto las cifras de depreciación total, 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: pronóstico 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. Normalmente es muy difícil proyectar el Fondo de Comercio para los años futuros.
Sin embargo, el fondo de comercio está sujeto a pruebas de deterioro anualmente que realiza 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 la mantienen constante y esto es 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 los intangibles” también son muy difíciles de proyectar
El informe 10K de Colgate nos proporciona los detalles de los próximos 5 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. Este es el cronograma que preparamos para los “sobrantes” que no tienen impulsores específicos para pronosticar. En el caso de Colgate, las otras partidas de largo plazo (remanentes) fueron impuestos a la renta diferidos (pasivo y activo), otros activos 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, entonces 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 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 básicas 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 - Modelado financiero - Programa de equidad del accionista

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 recomprado debe hacerse referencia a 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 Importe recomprado / Número de acciones
Calcule el múltiplo 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 de que sus acciones del 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. Con base en el monto histórico de la 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 de la acción implícito = EPS multiplexado de PE asumido
El múltiplo de PE de recompras futuras puede asumirse sobre la base de 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, asumí 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 encontrar 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 el 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 la cantidad de opciones ejercitables fue de 24.151 millones Colgate 2013 - 10K, Página 70

Paso 9E - Opciones sobre acciones: Encuentre los ingresos de la opción

Al poner estos números en nuestros datos de opciones a continuación, notamos 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 entregadas 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 tomaré más emisiones de opciones en el futuro. 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

Pronóstico de dividendos estimados usando 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 que la proporción 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 prevista en su totalidad

Con el pronóstico de recompra de acciones, ganancias de opciones y dividendos pagados, estamos listos para completar el Cuadro de capital contable. Vincule todos estos para encontrar el saldo de capital final para 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 medias
Capturar efectos pasados de opciones y convertibles según corresponda
Acciones diluidas - promedio
Acciones de referencia recompradas y acciones nuevas de opciones ejercidas
Calcular las cuotas básicas previstas (real)
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 capital contable.
Además, 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 de la Lista de acciones.

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 hay un par de cosas que están 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 las actividades de financiación

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 ocuparnos 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

Paso 12A: establezca un programa de deuda

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

Paso 12B - Calcular el flujo de caja a partir del reembolso de la deuda

Consulte el 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.

Omitir Emisión / Pagos de Deuda a Largo Plazo, Efectivo disponible para Facilidad de Crédito Rotatorio y sección Revolver por ahora Del informe Colgate 10K, notamos los detalles disponibles sobre Facilidad de Crédito Revolucionado Colgate 2013 - 10K, Página 35

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

Paso 12C - Calcule la deuda final a largo plazo

Usamos el programa de pago 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 / pagos 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 de una tasa de interés basada en la información proporcionada en el informe 10K
Calcular el gasto total por intereses = saldo promedio de deuda x tasa de interés

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

Paso 12G: vincular la deuda principal y las reducciones de revólver con los flujos de efectivo

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

Demarque la porción 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 enlace.

Curso recomendado de modelado financiero

Espero que haya disfrutado de la Guía gratuita de Excel de modelado financiero. Si desea aprender Modelado financiero en Excel a través de nuestras videoconferencias 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 5 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!