Técnicas de modelización financiera | lección tutorial de excel

Actualización 2017: Haga clic aquí para obtener la nueva guía definitiva sobre convenciones y mejores prácticas de modelos financieros.

Técnicas de modelado financiero

Dado que los modelos financieros requieren mucho trabajo con hojas de cálculo, generalmente en Microsoft Excel, quería tomarme el tiempo para resaltar algunas características clave de muchos de los modelos financieros que se encuentran en Wall Street y en las empresas estadounidenses. Algunos de estos puntos, comunes a la mayoría de los modelos financieros con los que se encuentra, implican una codificación de colores adecuada (para facilitar su uso) y abordar problemas de circularidad (para una funcionalidad adecuada). Si bien hay muchos otros temas de discusión relacionados con los modelos financieros, como por ejemplo: Por ejemplo, análisis de escenario/sensibilidad y rentabilidad TIR (utilizado para evaluar e interpretar el valor de una empresa o valor), los guardaremos para artículos futuros.

¿Donde debería empezar?

Como ex banquero de inversiones, no puedo enfatizar lo suficiente la importancia de formatear adecuadamente su trabajo, ya sea una presentación de PowerPoint ante una junta directiva, un memorando de oferta enviado a un inversionista potencial o incluso un modelo financiero en preparación para un cliente. Uno de los estándares de formato más importantes e importantes es el concepto de codificar con colores su modelo. ¿Por qué es tan importante la codificación de colores?

Pongamos un ejemplo: imagina que eres el analista principal de un negocio muy importante y eres responsable de mantener el modelo financiero de ese negocio. Sin embargo, como usted es banquero de inversiones, también está involucrado en otros negocios que requieren su atención, y uno de los directores ha decidido enviarlo a un viaje a Europa para trabajar con un cliente. Otro analista/socio/vicepresidente debe asumir sus responsabilidades originales de modelado y ser capaz de comprender y navegar fácilmente este modelo en su ausencia.

Sin un conjunto de estándares de codificación de colores, a su sucesor le resultaría muy difícil seguir el modelo financiero porque no sabría dónde cambiar una entrada o una fórmula. Honestamente, revisar el trabajo de otra persona en un modelo financiero sin estas pautas codificadas por colores puede ser muy frustrante y, peor aún, ¡consumir mucho tiempo! Aquí es donde la aplicación de técnicas de codificación de colores adecuadas puede ayudarle a usted y a su equipo de negociación a ahorrar tiempo (¡y trabajo!).

Arriba se muestra un ejemplo del uso de códigos de colores en un modelo financiero. Tenemos ventas históricas para los años 2004-2006 que se ingresaron manualmente en el modelo. Esto se refleja en el uso de texto azul en las celdas y sombreado amarillo en el fondo. Esta combinación de colores permite al usuario del modelo financiero ver fácilmente lo que se ha ingresado manualmente en el modelo y determinar qué otras celdas pueden necesitar cambios para ajustar pronósticos y suposiciones, como por ejemplo: B. Celdas F4 a H4, que predicen las tasas de crecimiento de las ventas. Este texto azul con fondo amarillo es una práctica habitual en Wall Street y debería incorporarse a cualquier modelo financiero. Esto corresponde a la práctica de utilizar texto negro y un fondo claro para identificar fórmulas en un modelo financiero. Ejemplos de esta práctica incluyen las celdas D4 a E4 y F3 a H3, donde se calculan tanto las tasas de crecimiento históricas como los montos de ingresos futuros. A continuación se presentan algunas pautas generales para codificar celdas con colores y aplicar este formato.

¡Mi modelo funciona! ¡No, no lo hace!

El propósito de construir un modelo financiero es crear un conjunto dinámico de predicciones sobre el estado futuro de una empresa o economía e interpretar los resultados. ¿Cómo hacemos que un modelo sea dinámico? Como banquero de inversión o analista de acciones, su objetivo es analizar las ventas, las ganancias, los flujos de efectivo y las cuentas del balance de una empresa durante un período de tiempo específico (semanas, meses o años). En un modelo financiero, cada uno de estos elementos está “interconectado” de tal manera que cambiar los supuestos sobre un criterio puede afectar a todos los demás (ver breve lección en video). Echemos un vistazo más de cerca a esta conexión básica:

Los informes financieros condensados ​​de una empresa se presentan a continuación:

Aquí tenemos los cuatro componentes principales de un modelo financiero:

1. Declaración de ganancias y pérdidas
2. Hoja de balance
3. Estado de flujo de efectivo
4. Plan de deuda

El plan de deuda se utiliza para realizar un seguimiento de los pagos de deuda o de los préstamos cuando se necesita efectivo.

Para comprender la conexión entre los estados financieros, comencemos con los ingresos netos.

Comprender las conexiones de 3 declaraciones

Todos los elementos del estado de resultados, desde los ingresos hasta los impuestos, afectan la ganancia neta al final del día. El beneficio neto es nuestro punto de partida para la contabilidad del flujo de caja y esto será crucial para comprender la circularidad que surge en un modelo financiero. Dado que los ingresos netos no necesariamente son efectivo, se realizan algunos ajustes, tales como: B. la provisión para gastos de depreciación (no en efectivo) que se encuentra en el estado de resultados, así como el cambio año tras año en los inventarios en el balance general ($1000-$400=$600). Estos $600 representan el inventario vendido y se reportan en el estado de resultados como “Costo de los bienes vendidos”.

A continuación, observamos en el estado de flujo de efectivo que la compañía gastó $500 en gastos de capital durante el año, lo que resultó en una disminución en el flujo de efectivo pero un aumento en PP&E en el balance general debido al aumento en el equipo comprado. Considere que los PP&E solo aumentaron $450 durante el año debido a un gasto de depreciación de $50, lo que disminuyó el valor de los PP&E. Ahora que hemos tabulado tanto el efectivo de operaciones de $685,6 como el efectivo de inversiones de ($500), podemos ver que tenemos $185,6 para pagar la deuda (asumiendo que los $100 originales en el balance general son el saldo mínimo requerido). no está disponible para pagar ninguna deuda). Si utilizamos este exceso de efectivo para pagar la deuda, nuestro saldo final de deuda, como se muestra en la tabla de deuda anterior, será de $814,4. Este monto de deuda también se muestra en el balance como saldo final del “Año 2”. Luego registramos este cambio de deuda en la sección Efectivo proveniente de financiamiento del estado de flujos de efectivo y encontramos un cambio neto de efectivo de cero para el año (¡lo gastamos todo pagando deuda!).

Circularidad de los gastos por intereses en los modelos financieros.

Si el problema no parece evidente al vincular de esta forma sus estados financieros, volvamos a centrarnos en la cuenta de resultados. Recuerde que mencioné que cada partida del estado de resultados afecta el ingreso neto al final del día. Si observa detenidamente, notará que también incluye los gastos por intereses, que son una función de su tasa de interés (10%) multiplicada por el saldo de su deuda. Aquí presentamos la circularidad creada en el modelo y explicamos por qué Excel no siempre está satisfecho con su decisión de crear un modelo tan dinámico.

Vincular los gastos por intereses con su estado de resultados introduce un razonamiento circular en el modelo.

1. Los ingresos netos disminuyen (los gastos por intereses reducen los ingresos netos)
2. Se reduce el efectivo disponible para pagar la deuda (una menor utilidad neta conduce a menores flujos de efectivo)
3. Esto aumenta los niveles de deuda (menores flujos de efectivo significan menos dinero para pagar las deudas)
4. Aumentos en los gastos por intereses (los mayores rendimientos de la deuda conducen a mayores gastos por intereses)
5. Los ingresos netos caen… y así sucesivamente. Este proceso de iteración se repite una y otra vez hasta alcanzar valores de estado estable.
6. Esta es la referencia circular en un modelo de información financiera y debe abordarse

Debido a esta circularidad en el modelo financiero, Excel puede volverse inestable y mostrar “REF!”, “Div/0!” o error “#valor”. No importa cuál aparezca, ¡no es bueno! Para hacer frente a la circularidad creada en el modelo, tenemos algunas soluciones. Primero, asegúrese de que «Iteraciones» esté habilitada en su modelo (vea las imágenes a continuación). Esto se puede hacer mediante:

Excel 2003: Herramientas -> Opciones -> pestaña Cálculo -> Establecer iteraciones en 100 (casilla de verificación)

Excel 2007: botón de Office -> Opciones de Excel -> pestaña Fórmulas -> Establecer iteraciones en 100 (casilla de verificación)

La siguiente solución es hacer una de las siguientes:

Opción 1: romper la circularidad manualmente

1. Copie la referencia de gastos por intereses del estado de resultados a la derecha, más allá de la última columna de pronóstico.
2. Reemplace las proyecciones de gastos por intereses en el estado de resultados con ceros. Esto efectivamente «rompe» el argumento circular: los errores ahora deberían desaparecer.
3. Copie las fórmulas de gastos por intereses (que pegó a la derecha de su modelo) y péguelas nuevamente en el estado de resultados.

Opción 2: Insertar un interruptor de circularidad (opción preferida)

1. Cree una celda de entrada en algún lugar del modelo donde el usuario pueda ingresar «1» o «0».
2. Cuando el usuario ingresa «0» en esta celda, le dice a Excel que inserte automáticamente ceros en lugar de las proyecciones de gastos por intereses en el estado de resultados. Esto «rompe» la circularidad y elimina los errores.
3. Luego, el usuario puede volver a ingresar «1» en esta celda, lo que reemplazará los ceros con la referencia correcta de gastos por intereses en el estado de resultados.

Conclusión sobre las técnicas de modelización financiera.

La creación de modelos financieros eficaces requiere la aplicación de mejores prácticas, y las dos anteriores (codificación de colores y tratamiento de la circularidad) son dos de las más importantes. Un modelo de trabajo dinámico es muy útil cuando se intenta hacer pronósticos financieros o evaluar oportunidades de inversión, pero solo en la medida en que el modelo sea fácil de entender y navegar. La incorporación de estas mejores prácticas puede ahorrarle tiempo y molestias en el futuro y permitir que otros inspeccionen su trabajo y reparen el modelo cuando usted no esté presente.