Mejores prácticas avanzadas de modelado financiero: trucos para un modelado inteligente y sin errores

Resumen ejecutivo

Introducción: un modelo financiero

Los modelos financieros son una parte indispensable del conjunto de herramientas financieras de cada empresa. Son hojas de cálculo que detallan los datos financieros históricos de un negocio determinado, pronostican su rendimiento financiero futuro y evalúan su perfil de riesgos y rendimientos. Los modelos financieros generalmente se estructuran en torno a los tres estados financieros de la contabilidad, a saber: estado de resultados, balance general y estado de flujo de efectivo. La gestión de la mayoría de las corporaciones se basa, al menos en parte, en los detalles, suposiciones y resultados de los modelos financieros, todos los cuales son fundamentales para los procesos de toma de decisiones estratégicas y de capital de dichas empresas.

Este artículo sirve como una guía paso a paso para los profesionales financieros novatos e intermedios que buscan seguir las mejores prácticas de los expertos al crear modelos financieros. Para el modelador financiero avanzado, este artículo también mostrará una selección de consejos y trucos de nivel experto para optimizar el tiempo, la producción y la eficacia del modelado. Vamos a empezar.

Planificación de su modelo

Al igual que con todas las cosas complejas, el primer paso para construir un modelo financiero ("modelo") es diseñar cuidadosamente un plano. Los cambios estructurales imprevistos y no planificados a mitad de un ejercicio de modelado pueden llevar mucho tiempo, ser confusos y propensos a errores, especialmente si el adaptador del modelo no es el mismo que su autor. Dichos desafíos se subvierten fácilmente con un poco de tiempo de planificación dedicado al inicio del ejercicio. Recomiendo que su fase de planificación sea la siguiente:

1. Definir el objetivo final del modelo.

Definir claramente el propósito de un modelo es clave para determinar su diseño, estructura y resultados finales óptimos. Como parte de este proceso, tómese el tiempo para asegurarse de que las partes interesadas clave de su modelo aprueben su plan y el diseño del proceso antes de comenzar a construir. Esto les da la oportunidad de expresar cualquier preferencia o intención final, evitando así cualquier "desplazamiento del alcance" (lenguaje de la industria) o redirección dolorosa en el futuro.

2. Comprender los plazos tanto para la construcción del modelo como para su vida útil.

Aunque secundario al objetivo final del modelo, la comprensión de los plazos para construir el modelo y durante cuánto tiempo se utilizará el modelo también son datos importantes para determinar el enfoque del ejercicio de modelado. Los modelos de larga duración y larga duración (vida útil) generalmente se construyen a la medida desde cero e incluyen enormes cantidades de detalles operativos, flexibilidad y capacidades de sensibilidad. Para modelos operativos o de proyectos de capital más inmediatos y de menor duración, los modeladores suelen utilizar plantillas prefabricadas para maximizar la velocidad de construcción y minimizar los errores. Además, las plantillas modelo también tienden a ser más familiares y, por lo tanto, más fáciles de usar/manipular por diferentes partes interesadas dentro de las organizaciones.

3. Determinar el equilibrio óptimo entre "detalle" y "reutilización".

Al decidir el equilibrio óptimo entre el nivel deseado de detalle y la reutilización del modelo (es decir, si el modelo está destinado a ser reelaborado para múltiples tipos de transacciones/propósitos o, en cambio, ha sido diseñado solo para este ejercicio único), un El marco útil para decidir sobre la elección/enfoque del modelo, que he seguido durante la mayor parte de mi carrera, es el siguiente:

Con la fase de anteproyecto/planificación ahora completa y las decisiones clave resueltas, ahora podemos pasar a la siguiente fase de modelado.

Estructurando su modelo

En este momento, estamos listos para abrir Excel y comenzar a pensar en la estructuración. En el nivel más alto posible, cada modelo puede/debe dividirse en tres secciones: (a) entradas/impulsores, (b) cálculos (estados financieros proyectados) y (c) salidas. Cuanto mejor sea segregar estas secciones, más fácil será auditar y modificar el modelo mientras se minimizan los errores y se optimiza a tiempo.

He seguido el mismo enfoque estructural para casi todos los modelos que he construido; un enfoque que tanto mis respectivas partes interesadas como yo siempre hemos encontrado práctico, digerible y, en última instancia, útil. Sus secciones son las siguientes:

1. Portada (pestaña): nombre del código del proyecto, una descripción de la intención del modelo, la información de contacto del autor y cualquier exención de responsabilidad aplicable.
2. Pestaña Controladores: Entradas y suposiciones.
3. Ficha Modelo: Cálculos (es decir, las tres proyecciones y cálculos de los estados financieros).
4. Pestaña Salidas: un resumen limpio y ordenado de los puntos destacados más importantes del modelo.
5. Pestaña Sensibilidades: El rango de escenarios, sensibilidades y resultados de datos en los que se basará la gerencia a medida que hagan la transición a su proceso de toma de decisiones.

Desglosaré cada una de estas secciones para usted, una a la vez. Como sigue:

Portada

La portada es el primer punto de contacto con tu obra. Si bien es el más simple de construir, cuando se hace bien, deja una excelente primera impresión y explica claramente lo que está por venir. Una portada sencilla e instructiva suele ser el mejor enfoque y, por lo general, incluye las siguientes secciones:

1. Nombre del Modelo: Se explica por sí mismo.
2. Propósito del modelo: un párrafo que describe su(s) uso(s) previsto(s)
3. Índice del modelo: una tabla breve que detalla la descripción y el propósito de cada pestaña. Esta sección puede ser especialmente útil para los operadores no financieros, ayudándolos a "digerir" la estructura y el flujo del modelo al resaltar qué pestañas necesitan usar para las entradas, en qué salidas enfocarse durante la toma de decisiones y qué pestañas de cálculos complejos deben. dejar intacto.
4. Historial de versiones del modelo: Invertir unos segundos en escribir, por fecha, los cambios clave realizados en el modelo a medida que avanza, siempre ahorra tiempo en el futuro, especialmente si necesita volver a rastrear y revertir/modificar los cambios. Esto es especialmente cierto para modelos complejos y modelos que puede usar como plantillas en períodos futuros.
5. Información de contacto del autor: Se explica por sí mismo
6. Descargos de responsabilidad legales aplicables (si los hubiere, según lo indique su asesor legal) : Se explica por sí mismo

Tenga en cuenta: recomiendo que la portada siempre esté bloqueada para cualquier persona sin autorización expresa para realizar cambios, fuera del autor.

Ficha del conductor: entradas y suposiciones

Inmediatamente después de la portada del modelo, debe venir la pestaña de drivers (entradas) . Debe asegurarse de que esta pestaña sea clara, concisa y fácil de entender, ya que esta es la pestaña que los operadores no financieros probablemente manipularán con más frecuencia. Por lo general, recomiendo implementar dos secciones de entrada dentro de la pestaña de entradas, una para entradas estáticas y otra para dinámicas . Por entradas estáticas me refiero a entradas que no cambian con el tiempo, como el hipotético "tamaño de una planta de energía" o "el saldo de deuda inicial de una empresa"; y por insumos dinámicos , me refiero a insumos que son variables a lo largo del tiempo (p. ej., de mes a mes o de año a año) como supuestos de "inflación", "costo de la deuda" o supuestos de "crecimiento de ingresos".

Ejemplo de una pestaña de supuestos y controladores de muestra

Dentro de las dos secciones de entrada estática frente a dinámica anteriores, le recomiendo que también separe claramente sus datos en dos tipos: (1) cifras codificadas que no cambian independientemente del escenario de suposiciones, y (b) parámetros de sensibilización que impulsar diferentes escenarios de suposición y, en última instancia, sus tablas de sensibilidad. Tenga en cuenta, sin embargo, que nunca sabrá completamente qué parámetros van a constituir parámetros de sensibilidad y cuáles no hasta las etapas finales del proyecto. Para obtener más información sobre el modelado de sensibilidad, consulte el siguiente artículo.

Pestaña Modelo: Cálculos detallados y acumulación operativa

Esta pestaña representa el corazón del modelo, donde todas las entradas, suposiciones y escenarios trabajan juntos para proyectar el desempeño financiero de una empresa en sus últimos años. También es fuera de esta pestaña que se ejecutarán varios escenarios basados ​​en suposiciones, así como la parte de valoración del ejercicio que se llevará a cabo antes de la decisión estratégica final.

Ejemplo de una pestaña de modelo de muestra

Ficha Escenarios y sensibilidades

Los operadores de modelos de terceros autorizados utilizarán la pestaña Escenarios y sensibilidades con bastante frecuencia, aunque solo sea para seleccionar su elección de escenarios preprogramados. Por esta razón, debe crear escenarios de forma intuitiva, proteger los escenarios reales de la edición externa y crear sensibilidades lo suficientemente variadas como para que el puñado de escenarios preprogramados sea suficiente para generar una visión amplia de los posibles resultados una vez que se hayan establecido las tablas de sensibilidad (muestra a continuación). también se construyen.

Para su consideración, la estructura de formato de escenarios en la que me he basado a lo largo de mi carrera es la siguiente, solo como un tipo de ejemplo:

Algunas notas sobre la imagen de arriba:

1. El usuario del modelo debería poder editar solo esto, ya que es donde seleccionará el número de escenario. El número se refiere a uno de los escenarios presentados en el lado derecho de la hoja de cálculo. El usuario entonces presentará el escenario seleccionado (en este caso, el No. 6) en la primera columna. Esta es la única columna de la hoja de cálculo Escenario y sensibilidades a la que se hace referencia en el modelo.
2. Agregue aquí un par de campos de descripción que resuman efectivamente lo que representa el escenario seleccionado.
3. Siempre encuentro muy útil, especialmente si alguien más usará el modelo, agregar una columna que especifique cada unidad de entrada.
4. Esta columna extrae la estadística/campo de apalancamiento del escenario seleccionado (en este caso, No. 6), que se muestran a la derecha (en azul). La fórmula requerida para impulsarlo es una función de compensación, es decir, “=DESPLAZAMIENTO (inserte una celda vacía inmediatamente a la izquierda del primer escenario resaltado en rojo arriba, Celda donde se selecciona/resalta el escenario)”. Tenga en cuenta que hay un espacio vacío entre las dos celdas, por lo que las dos comas (,,) no son un error tipográfico.
5. Agrupa tus suposiciones en macrocategorías y subcategorías. Esto lo ayudará a usted (el modelador) y a sus usuarios a comprender claramente qué escenario ha seleccionado el modelo.

Ejemplo de una tabla de sensibilidad de muestra

Ficha Salida

Las pestañas de salida son las pestañas que los operadores del modelo utilizarán con mayor frecuencia. A lo largo de los años, me he inclinado hacia al menos tres pestañas de salida para modelos de complejidad media a alta:

1. Pestaña de resultados financieros: este es un resumen abreviado de las finanzas detalladas en la pestaña del modelo. Suelen presentarse con carácter anual (aunque el modelo puede ser trimestral). Esta salida debe tener entre 50 y 150 filas y debe presentar todos los elementos de línea clave de las pestañas de cálculo. Asegúrese de presentar suficientes detalles para asegurarse de que los usuarios no estén alternando entre esta pestaña y varias pestañas de Cálculos. Tenga en cuenta también que, como práctica recomendada, ninguna pestaña de salida debe volver a realizar ningún cálculo y esta información solo debe incluir enlaces directos.
2. Pestaña Resumen ejecutivo: esta pestaña es bastante estándar y generalmente presenta una combinación de gráficos, tablas y tablas que ilustran, de la manera más simple y fácil de digerir posible, las diversas tendencias, análisis y estadísticas resumidas clave que los ejecutivos y los miembros de la junta necesitan para navegar sus decisiones clave.
3. Pestaña de salida específica: esta pestaña contiene salidas específicas, generalmente dictadas por la plantilla del memorando de inversión, la presentación del comité de inversión o las solicitudes de los ejecutivos y miembros de la junta según sea necesario para llegar a sus puntos de decisión.

Ejemplo de una pestaña de resultados del modelo, que incluye tablas, cuadros y gráficos

En este momento, la fase de construcción del modelo está oficialmente completa. Podemos dirigir nuestra atención a algunas de las mejores prácticas de modelado a nivel de expertos a las que me referí al comienzo del artículo. Comencemos con el formateo.

Formatear tu modelo

En primer lugar, es importante señalar que cada empresa/grupo puede tener sus propias preferencias o prácticas internas. Como tal, durante la construcción, es importante consultar primero y adherirse a cualquier formato que prescriba su empresa respectiva. Sin embargo, en ausencia de prácticas específicas de la empresa, el contenido a continuación detalla el lenguaje universal de Wall Street para formatear un modelo.

El primer y más bajo método de formato para el modelado financiero es usar esquemas de color consistentes e identificables para indicar diferentes tipos de celdas y datos. Como sigue:

Azul = Entradas o cualquier dato codificado de forma rígida, como valores históricos, suposiciones y controladores.

Negro = Fórmulas, cálculos o referencias derivadas de la misma hoja.

Verde = Fórmulas, cálculos y referencias a otras hojas (tenga en cuenta que algunos modelos omiten este paso por completo y usan negro para estas celdas).

Púrpura = Enlaces, entradas, fórmulas, referencias o cálculos a otros archivos de Excel (nuevamente, tenga en cuenta que algunos modelos omiten este paso por completo y también usan negro para estas celdas).

Rojo = Error a corregir.

Ejemplo de resumen financiero bien formateado (codificado por colores)

Tenga en cuenta que no hay una función de automatización incorporada para codificar con colores sus hojas de cálculo de Excel de acuerdo con los estándares universales de codificación de colores anteriores. En su lugar, puede diseñar su(s) propia(s) macro(s) para lograr estos resultados y, posteriormente, crear combinaciones de accesos directos para codificar por color automáticamente su trabajo.

En algún momento de mi pasado reciente, recibí de un colega (a quien agradezco hasta el día de hoy), las siguientes macros (incluidas instrucciones detalladas), que desde entonces me han ahorrado varias horas de trabajo manual. Me gustaría compartirlos, si puedo.

Instrucciones de creación de macros (para las versiones de Excel para Mac y PC):

1. Presione Alt + W + M + R, al mismo tiempo, para nombrar y comenzar a grabar su macro.
2. Presione F5 ("Saltar a la celda") y luego Alt + S, al mismo tiempo, para llegar al menú "Ir a especial".
3. Presiona "O" para seleccionar Constantes y "X" para desmarcar el texto.
   
4. Ahora presione Alt + H + FC (o Ctrl + 1), al mismo tiempo, y seleccione su color de fuente azul para estas constantes.
5. Presiona Esc.
6. Ahora haga lo mismo, comenzando con F5, pero seleccione Fórmulas (F) en lugar de constantes y presione "X" para desmarcar el texto.
7. Ahora presione Alt + H + FC (o Ctrl + 1) y seleccione un color de fuente negro para estas constantes.
8. Deja de grabar la macro con Alt + W + M + R o Alt + T + M + R.

Enlaces a otros libros de trabajo

Encontrar enlaces a otros libros y hojas de trabajo es complicado, y lo más probable es que tenga que usar VBA para que esto funcione correctamente. Aquí está la idea básica: busque la presencia del símbolo “!” en cada celda que contiene una fórmula en su libro de trabajo y luego cambie el color de la fuente a verde. Deberá modificar esto en el Editor de VBA y convertirlo en un bucle for each ciclo a través de todas las instancias de "!" encuentre, y luego cambie el color de fuente para cada uno de estos.

Tenga en cuenta que este atajo aún no funcionará el 100 % del tiempo porque algunas fórmulas harán referencia a celdas en otras hojas de trabajo sin vincularlas directamente. Afortunadamente, las celdas verdes son más raras que las celdas negras o azules, por lo que el método anterior funciona bastante bien en la mayoría de los modelos (y puede formatear orgánicamente el resto de sus enlaces a otras hojas de trabajo manualmente a medida que aparecen o cuando los encuentra).

Mejores prácticas para auditar un modelo

Al modelar, lo animo a que siempre tenga esta pregunta en el fondo de su mente: "¿Estoy haciendo que este modelo sea fácilmente auditable?" porque para cada tarea ejecutada, fórmula creada y enlace creado, siempre habrá una forma más rápida y "más sucia" (en la jerga de la industria) de hacer el trabajo. Dichos trucos y trucos, por ingeniosos que parezcan en ese momento, y especialmente después de intervalos de tiempo, invariablemente se olvidarán y conducirán a errores difíciles de rastrear. Tener en mente a un revisor en tercera persona lo guiará a través de su proceso y lo ayudará a tomar la decisión correcta en los momentos clave.

A continuación se presentan una serie de mejores prácticas sobre cómo construir con una mentalidad de auditor. Como sigue:

1. Una fila, una fórmula

Debe tener solo una fórmula por fila, lo que significa que cualquier fórmula que se use en la primera celda de cualquier fila debe ser la misma fórmula aplicada uniformemente en toda la fila. Los usuarios deben comprender la estructura de su modelo mirando la primera celda de cada fila a medida que avanzan verticalmente hacia abajo en su modelo.

Si bien esto es simple en principio, se viola con la suficiente frecuencia como para resaltarlo más. Una instancia común a menudo ocurre cuando las hojas de cálculo se dividen entre un grupo de columnas de "finanzas históricas" y "pronósticos de años posteriores" (consulte la imagen anterior titulada "Ejemplo de resumen financiero bien formateado (codificado por colores)", como referencia ).

Una manera fácil de abordar estos casos es el uso de banderas (p. ej., 1/0, TRUE / FALSE ) ubicadas en la parte superior de la hoja de cálculo, luego se hace referencia mediante declaraciones IF a través del cuerpo del modelo. Una ilustración simple de esto en el trabajo es la siguiente:

Ejemplo de "El uso de banderas" en el modelado de Excel

2. No hay números codificados incrustados en las fórmulas

Nunca use números codificados incrustados en fórmulas porque son muy difíciles de detectar si el usuario está menos familiarizado con el modelo. En su lugar, resalte y separe claramente las entradas/códigos duros de las fórmulas; mejor aún, reúna todas las entradas/códigos duros (según corresponda) y agréguelos en la misma pestaña. Posteriormente, haga que sus fórmulas las extraigan/hagan referencia a ellas según corresponda desde la celda requerida y desde la pestaña correspondiente.

3. Lo simple siempre es mejor

Siempre es mejor evitar fórmulas complicadas. En su lugar, divida su fórmula en pasos fáciles de digerir. En lugar de una fila aparentemente ordenada, este enfoque a menudo creará muchas más filas, lo que dará como resultado una hoja de cálculo más grande; pero uno que será mucho más fácil de seguir y auditar por un tercero.

4. Adhiérase consistentemente a su convención de signos

Debe decidir en el tiempo cero cuál será su convención/clave de signos. A modo de ilustración, pregúntese en la etapa de diseño de su modelo: "¿Se presentarán los costos, gastos, deducciones, depreciación, CapEx, etc. como números negativos o positivos?" Mi preferencia personal es presentar siempre los costos como números negativos por dos razones: (a) Los totales siempre serán sumas directas y minimizará el error del usuario, y (b) será más fácil detectar errores usando solo los signos.

5. Evite nombrar sus celdas, en su lugar confíe en la lógica de cuadrícula de Excel

Siempre que sea posible, recomiendo enfáticamente evitar nombrar sus celdas, ya que se vuelve difícil ubicar la fuente de entrada para dicha celda nombrada (por ejemplo, "Inflación") en el futuro. En su lugar, le recomiendo que confíe en la convención de cuadrícula de Excel dentro de sus fórmulas (por ejemplo, simplemente vinculándola a la celda C4 o ubicación, [Tab Name]l'!G21 , si la referencia está en una pestaña o libro de trabajo diferente).

6. Nunca tenga la misma entrada en múltiples ubicaciones

Organice sus entradas de manera simple y transparente. Mi recomendación es que consolide todas las entradas en unas pocas pestañas del controlador y haga referencia a ellas desde sus puntos de origen singulares en toda la hoja de cálculo.

7. Evite vincular archivos

Evite vincular a otros archivos. Es mejor ingresar los datos relevantes que necesita de un archivo diferente como entradas codificadas, que luego actualiza manualmente según sea necesario. Se sabe que los enlaces cruzados bloquean modelos de Excel más grandes o se actualizan de manera inconsistente, lo que crea errores difíciles de rastrear.

8. No oculte hojas o filas

Dentro de una hoja de cálculo más larga, "agrupa" filas/columnas en lugar de "ocultarlas".

9. Menos pestañas más grandes son mejores que múltiples pestañas más pequeñas

Esta práctica se basa 100% en la experiencia. Es más fácil seguir y auditar una matriz continua de datos en una hoja de cálculo grande y contigua que en varias pestañas o, lo que es peor, en varias hojas de cálculo con enlaces cruzados.

10. Cree comprobaciones en todo su modelo a través de "Comprobaciones de error agregadas" ubicadas en una pestaña

Las comprobaciones son la forma más fácil de revisar rápidamente la integridad de un modelo. Los “cheques” abarcan todo, desde garantizar que los totales que deberían empatar realmente lo hagan hasta garantizar que el balance de uno realmente se equilibre. Por lo general, construyo algunos controles en la parte superior o inferior de cada hoja de cálculo y luego los consolido en una "Pestaña de control" separada. Esto asegura que sea fácil encontrar un error en el modelo y luego rastrear dónde se originó ese error.

Ejemplo de un Balance "Cheque"

Tenga en cuenta que confiar únicamente en las verificaciones para verificar la integridad de un modelo nunca es una buena idea, ya que las verificaciones suelen ser de un nivel bastante alto. Pero es un buen punto de partida.

Carve Out – Para usuarios avanzados: Consejos de Excel

Esta sección cubre un par de mejores prácticas muy efectivas de Excel para nuestros usuarios más avanzados. Estos pueden requerir un poco de ajuste, pero deberían ahorrar varias horas de trabajo más tarde y ser relativamente simples de implementar. Son los siguientes, en viñetas breves, sucintas y directas:

1. Utilice tantos atajos de teclado como sea posible. Hay varios archivos en Internet en atajos de teclado de Excel que se adaptan a diferentes necesidades. Me referiré a un par aquí:
   • Hoja de referencia clave de modelado
   • Lista completa de consejos de Excel
2. Use F5 ("ir a especial") para ubicar rápidamente todos los números o fórmulas codificados.
3. Utilice Trace Precedents y Trace Dependents para auditar el modelo.
4. Use XNPV y XIRR para permitir la aplicación de fechas personalizadas a los flujos de efectivo, en ruta a un análisis de rendimiento; esto, a diferencia de las funciones NPV y IRR de Excel, que asumen implícitamente intervalos de tiempo equidistantes para el cálculo.
5. Use la función INDEX MATCH sobre la función VLOOKUP para buscar información en hojas de cálculo grandes.
6. VLOOKUP casi siempre es superior a las declaraciones IF ; ponte cómodo con eso.
7. Acostúmbrese a incluir IFERROR en la sintaxis de sus fórmulas.
8. Utilice una combinación de la función de fecha, EOMONTH e instrucciones IF para hacer que las fechas sean dinámicas.
9. Quite las cuadrículas al presentar o compartir el modelo financiero; lo convierte en un documento de salida más limpio y pulido.

Amalo o odialo…

Lo ames o lo odies, Excel es omnisciente, omnipresente y omnipotente cuando se trata de finanzas corporativas, análisis y toma de decisiones basada en datos. Y créalo o no, no tiene por qué ser intimidante o doloroso, incluso para los novatos o no iniciados. Como la mayoría de las cosas en la vida, la práctica, la consistencia y la atención a los detalles (y en el caso de Excel, los atajos) lo llevarán casi todo el camino.

Una vez que se familiarice con la aplicación, encontrará que es una poderosa herramienta de productividad y narración numérica, sin la cual apenas podrá funcionar, incluso en su vida personal. A medida que avanza a través de las diversas etapas de la fluidez de Excel, le deseo lo mejor y lo animo a que mantenga este artículo como una guía práctica a la que consulte con frecuencia.