Modelado financieroIntermedio10 min de lectura

Cómo construir un modelo de 3 estados en Excel

Construye un modelo de 3 estados vinculado en Excel: P&L, Balance General y Flujo de Caja conectados para que un cambio en supuestos fluya por los tres automáticamente.

Esta guía te lleva paso a paso por la construcción de un modelo financiero de tres estados completamente vinculado en Excel, partiendo de un libro en blanco, de modo que un solo cambio en la tasa de crecimiento de ingresos o en el supuesto de DSO se propague automáticamente por el P&L, el Balance General y el Estado de Flujo de Caja. Sin más actualizaciones manuales entre pestañas cada vez que hay una revisión del directorio. ### Por qué una plantilla de estados financieros vinculados supera las actualizaciones manuales La mayoría de los analistas comienzan con tres pestañas separadas y las conectan después. Eso funciona hasta que deja de funcionar, normalmente a las 11 de la noche antes de la entrega del board pack, cuando el estado de flujo de caja no cuadra. Construir la arquitectura de vínculos desde el principio toma 30 minutos extra y ahorra horas de conciliación más adelante.

Lo que necesitarás

Excel 2016 o posterior (XLOOKUP disponible desde 2019+; aquí se usa INDEX/MATCH para mayor compatibilidad)
Familiaridad con referencias absolutas vs. relativas y rangos con nombre
Comprensión básica de cómo la utilidad neta fluye hacia las utilidades retenidas y cómo los cargos no monetarios fluyen hacia el flujo de caja operativo
Datos de origen: base de ingresos, estructura de costos, días de capital de trabajo, tasa de CapEx, cronograma de deuda (o valores provisionales)

Guía paso a paso

Diseña la arquitectura de pestañas para un modelo de 3 estados en Excel

Antes de escribir una sola fórmula, mapea la estructura de tus pestañas. La dirección de cada referencia importa: Assumptions alimenta todo, el P&L lleva la utilidad neta al Balance General, y el Balance General lleva los movimientos de capital de trabajo al Estado de Flujo de Caja. Las referencias circulares (típicamente en el revolvente o los intereses) se resuelven al final.

Crea 6 pestañas en este orden: Assumptions, P&L, BalSheet, CashFlow, Debt, Checks
Codifica las pestañas con colores: azul para inputs (Assumptions), blanco para los estados, rojo para Checks
Usa la columna A como etiquetas de fila, la columna B como columna de unidades/notas, y las columnas C en adelante como años fiscales (FY2024, FY2025, FY2026, FY2027, FY2028)
Fija la fila 1 y la columna A en cada pestaña de estados para que los encabezados permanezcan visibles al navegar
Agrega una celda de versión en Assumptions!B1 con formato v1.0 | May 2026. Los board packs se revisan 4-5 veces y el control de versiones evita enviar el archivo equivocado

Pro Tip

Nombra las columnas de año con una fórmula en la fila de encabezado como =DATE(Assumptions!$C$2,12,31) con formato "AAAA", de modo que todo el modelo se desplace al cambiar el año base en una sola celda.

Construye la pestaña de Assumptions

La pestaña Assumptions es el único lugar donde deben vivir los números codificados directamente. Todos los drivers están aquí. Los estados los consumen; nada regresa a ella (excepto los datos reales, que se manejan por separado).

Driver	Etiqueta	FY2025A	FY2026E	FY2027E	FY2028E
Crecimiento de ingresos	`rev_growth`	14.2%	12.5%	11.0%	9.5%
Margen bruto	`gm_pct`	38.5%	38.5%	39.0%	39.5%
Margen EBITDA	`ebitda_pct`	21.2%	21.5%	22.0%	22.5%
DSO (días)	`dso`	47	45	45	44
DIO (días)	`dio`	30	28	27	27
DPO (días)	`dpo`	34	32	33	33
CapEx % ingresos	`capex_pct`	3.4%	3.2%	3.0%	2.8%
D&A % ingresos	`da_pct`	2.1%	2.0%	1.9%	1.9%
Tasa de impuestos	`tax_rate`	26%	26%	26%	26%

Nombra cada fila de driver usando el Administrador de nombres de Excel (Fórmulas > Administrador de nombres), con alcance de libro. Por ejemplo, nombra el rango de fila del DSO como dso_row. Así las fórmulas de los estados son legibles de un vistazo
Mantén los datos reales (FY2025A) en una columna visualmente diferente (relleno gris claro) para evitar ediciones accidentales
Agrega una celda de Ingresos Base: Assumptions!C5 = 18400000 ($18.4M). Todas las fórmulas de ingresos multiplican desde esta celda, no de unas a otras en cadena

Pro Tip

Agrega un selector de "Escenario" en Assumptions!B2 (Base / Optimista / Conservador) y usa IF o CHOOSE para alternar filas completas de supuestos. Así evitas construir tres modelos separados para el mismo deal.

Construye la pestaña P&L

Con los supuestos en su lugar, la pestaña P&L se convierte principalmente en aritmética. Mantén el patrón de fórmulas consistente en cada fila para que la auditoría sea rápida.

// Ingresos FY2026E (base de pestaña Assumptions × (1 + crecimiento))
C5 = Assumptions!C5 * (1 + Assumptions!C8)    // $18.4M × 1.125 = $20.7M

// Utilidad bruta
C7 = C5 * Assumptions!C9                        // $20.7M × 38.5% = $7.97M

// COGS (derivado, no un input directo)
C6 = C5 - C7                                    // $12.73M

// EBITDA
C10 = C5 * Assumptions!C10                      // $20.7M × 21.5% = $4.45M

// D&A
C11 = C5 * Assumptions!C15                      // $20.7M × 2.0% = $414K

// EBIT
C12 = C10 - C11                                 // $4.04M

// Gasto por intereses (viene de la pestaña Debt)
C13 = -Debt!C18                                 // convención negativa

// EBT
C14 = C12 + C13

// Impuestos
C15 = -MAX(C14 * Assumptions!C16, 0)            // piso en cero para evitar impuesto negativo

// Utilidad neta
C16 = C14 + C15

Usa una convención de signos consistente en todo el modelo: ingresos positivos, costos y gastos positivos (mostrados como deducciones en la fórmula, no como valores negativos codificados directamente)
Construye SG&A e I+D como líneas separadas usando la diferencia entre ebitda_pct y gm_pct. Los prestamistas y miembros del comité de inversión siempre piden el desglose
Verificación cruzada: =C10/C5 junto a la línea de EBITDA debe ser exactamente igual a Assumptions!C10. Si no lo es, hay un problema de redondeo

Pro Tip

Dale formato al P&L con filas gris claro alternadas en cada línea de subtotal (Utilidad Bruta, EBITDA, EBIT, EBT, Utilidad Neta). Los revisores buscan estos puntos de referencia primero.

Construye la pestaña de Balance General

El Balance General es donde la mayoría de los modelos vinculados fallan. Las cuentas por cobrar, el inventario y las cuentas por pagar se calculan a partir de los días de capital de trabajo en la pestaña Assumptions, no se ingresan manualmente.

// Cuentas por cobrar (basadas en DSO)
C5 = ('P&L'!C5 / 365) * Assumptions!C12        // ($20.7M / 365) × 45 = $2.56M

// Inventario (basado en DIO, usa COGS)
C6 = ('P&L'!C6 / 365) * Assumptions!C13        // ($12.73M / 365) × 28 = $977K

// Cuentas por pagar (basadas en DPO, usa COGS)
C20 = ('P&L'!C6 / 365) * Assumptions!C14       // ($12.73M / 365) × 32 = $1.12M

// Utilidades retenidas (año anterior + utilidad neta - dividendos)
C30 = D30 + 'P&L'!C16 - Assumptions!C22        // D30 = UR del año anterior

Construye el movimiento completo de PP&E: PP&E inicial + CapEx - D&A = PP&E final. El CapEx viene de ='P&L'!C5 * Assumptions!C15 (ingresos × % CapEx)
El revolvente (deuda a corto plazo) es un plug; vuelve a él después de construir el Flujo de Caja
Agrega una fila de verificación al final: =C_TotalActivos - C_TotalPasivosPatrimonio. Debe dar exactamente cero. Si no, el modelo no cuadra y todo lo que depende de él es poco confiable

Pro Tip

Bloquea la celda del saldo inicial de utilidades retenidas (la columna FY2025A) y vincúlala a la celda de los estados financieros auditados. Una utilidad retenida de año futuro que encadena desde un saldo inicial incorrecto contamina todos los años siguientes de forma silenciosa.

Construye el Estado de Flujo de Caja

El Estado de Flujo de Caja se deriva completamente del P&L y los cambios en el Balance General. Nada se codifica directamente aquí, salvo los elementos sin driver previo (como pagos únicos).

// Sección de Flujo de Caja Operativo
// Comenzar con la utilidad neta
C5 = 'P&L'!C16                                  // $2.38M

// Agregar D&A (cargo no monetario)
C6 = 'P&L'!C11                                  // $414K

// Cambio en cuentas por cobrar (aumento en CxC = uso de caja, negativo)
C7 = -(BalSheet!C5 - BalSheet!D5)               // -(2.56M - 2.37M) = -$190K

// Cambio en inventario
C8 = -(BalSheet!C6 - BalSheet!D6)

// Cambio en cuentas por pagar (aumento en CxP = fuente de caja, positivo)
C9 = BalSheet!C20 - BalSheet!D20

// Total FCO
C11 = SUM(C5:C10)

// Sección de Flujo de Caja de Inversión
C14 = -('P&L'!C5 * Assumptions!C15)            // CapEx salida de caja: -$662K

// Sección de Flujo de Caja de Financiamiento
C17 = -(Debt!C12 - Debt!D12)                    // Pago neto de deuda

// Variación neta de caja
C20 = C11 + C14 + C17

// Saldo final de caja
C22 = BalSheet!D25 + C20                         // Caja del año anterior + variación

Verifica que C22 sea igual a BalSheet!C25 (caja en el Balance General). Esta es la segunda verificación de cuadre. Si falla, encuentra la diferencia antes de continuar
Los intereses pagados van en el FCO bajo GAAP (EE.UU.), pero muchos equipos de FP&A los muestran en el flujo de financiamiento para comparabilidad con IFRS. Elige uno y anótalo en el encabezado de la pestaña

Vincula los tres estados financieros en Excel

Con las tres pestañas construidas, confirma que los vínculos están intactos y son direccionalmente correctos. El orden de conexión es: Assumptions → P&L → Balance General → Flujo de Caja → de vuelta al Balance General (plug de caja).

Rastrea Assumptions!C8 (crecimiento de ingresos) a través del modelo: debe cambiar P&L!C5, lo que cambia BalSheet!C5 (CxC), BalSheet!C6 (inventario), BalSheet!C20 (CxP) y CashFlow!C7/C8/C9 (movimientos de capital de trabajo)
El plug del revolvente en el Balance General cierra el ciclo: Revolvente = Revolvente Anterior + Disposición de Revolvente, donde Disposición de Revolvente = -MIN(CashFlow!C20 + BalSheet!D25 - Assumptions!C_MinCash, 0). Esta fórmula activa el revolvente solo cuando la caja proyectada cae por debajo del piso mínimo de caja
Verifica que al cambiar Assumptions!C12 (DSO de 45 a 50 días) las CxC aumenten en ~$284K, el FCO disminuya en la misma cantidad, la caja final disminuya en ~$284K y el revolvente aumente en ~$284K. Si los cuatro se mueven juntos, el vínculo de los tres estados funciona

Pro Tip

Agrega una fila de "Prueba de Delta" en la pestaña Checks. Cambia un supuesto en una cantidad fija (por ejemplo, crecimiento de ingresos de 12.5% a 13.5%), verifica la cascada y luego presiona Ctrl+Z. Haz esto antes de enviar cualquier modelo externamente.

Construye la pestaña Checks para el modelo financiero vinculado

Un modelo sin verificaciones es un riesgo. La pestaña Checks detecta los dos modos de falla que realmente ocurren: Balance General descuadrado y saldo final de caja del Estado de Flujo de Caja que no coincide con el del Balance General.

// Verificación del Balance General (debe = 0)
C5 = BalSheet!C_TotalActivos - BalSheet!C_TotalPasivosPatrimonio

// Cuadre de caja (debe = 0)
C6 = BalSheet!C25 - CashFlow!C22

// Verificación del movimiento de utilidades retenidas (debe = 0)
C7 = BalSheet!C30 - (BalSheet!D30 + 'P&L'!C16 - Assumptions!C22)

// Verificación del crecimiento de ingresos (debe = 0)
C8 = 'P&L'!C5 - ('P&L'!D5 * (1 + Assumptions!C8))

Dale formato a cada celda de verificación con formato condicional: relleno verde si =0, relleno rojo si <>0. La pestaña Checks debe estar completamente verde antes de que el modelo salga de tus manos
ModelMonkey puede revisar las 4 verificaciones y explicar las discrepancias en lenguaje simple, lo que es útil cuando un analista junior ha estado editando el modelo y necesitas diagnosticar qué vínculo se rompió
Agrega un SUMPRODUCT sobre todas las celdas de verificación: =SUMPRODUCT(ABS(C5:C8)). Si esto devuelve algo distinto de 0, el modelo tiene al menos un error y lo verás en el momento en que llegues a la pestaña

Pro Tip

Protege la pestaña Checks (Revisar > Proteger hoja, sin contraseña) para que no pueda editarse accidentalmente. Una celda de verificación que alguien haya codificado directamente en cero es peor que no tener verificación alguna.

Tu plantilla de 3 estados en Excel ya está lista

En este punto tienes un modelo de tres estados completamente vinculado: un cambio en el crecimiento de ingresos (por ejemplo, de 12.5% a 9.0%) se propaga por los $20.7M de ingresos proyectados, ajusta la utilidad bruta, el EBITDA, la utilidad neta, los saldos de CxC/inventario/CxP, el flujo de caja operativo y la caja final, todo sin tocar directamente ninguna pestaña de estados.

La arquitectura descrita aquí escala para cualquier operación. Agrega una pestaña de Retornos para MOIC/IRR, una pestaña de DCF para el valor terminal (tu WACC, tu múltiplo de salida, tu construcción de FCFF) o una pestaña de Sensibilidad para una matriz de ingresos/margen de 5×5. El núcleo de tres estados no cambia.

A partir de mayo de 2026, esta es la misma estructura de pestañas que usan los equipos de banca de inversión y FP&A en la construcción de board packs, modelos de sindicación bancaria y memos de comité de inversión. Los detalles varían; el esquema de vínculos no.

Si quieres saltarte la construcción desde cero, Elige un plan de ModelMonkey. Funciona tanto en Google Sheets como en Excel y puede generar la arquitectura de pestañas, la tabla de supuestos y las fórmulas de vínculo a partir de una descripción en lenguaje natural de tu negocio.

Conclusión

Preguntas frecuentes

¿Cómo manejo las referencias circulares en un modelo de tres estados vinculado?

La referencia circular más común viene del gasto por intereses: los intereses dependen del saldo de deuda, el saldo de deuda depende del revolvente y el revolvente depende de la caja, que a su vez depende de los intereses. La solución limpia es modelar los intereses sobre el promedio del saldo de deuda del período anterior (`= (DeudaInicial + DeudaFinal) / 2 * TasaDeInterés`) con el cálculo iterativo habilitado en Excel (Archivo > Opciones > Fórmulas > Habilitar cálculo iterativo, máximo 100 iteraciones). La mayoría de los modelos de banca de inversión usan la deuda del período anterior para evitar la circularidad por completo.

¿Cuál es la convención de signos correcta para un modelo vinculado?

Elige una convención y aplícala en todas partes: o bien todos los elementos del estado de resultados son positivos (ingresos positivos, costos positivos como deducciones) o usa la convención contable (ingresos positivos, costos negativos). La convención más común en FP&A es todo positivo con los costos mostrados como deducciones por línea. Las salidas de caja en el Estado de Flujo de Caja son negativas. El Balance General siempre es positivo. Sea cual sea tu elección, documéntala en un comentario de celda en el encabezado de la pestaña P&L.

¿Cuántos años debe cubrir una plantilla de tres estados vinculada?

El estándar son 5 años de proyecciones más 2-3 años de datos reales históricos. Los modelos de LBO frecuentemente usan 5+1 (año de salida). Los DCF típicamente usan 5 años de pronóstico explícito más un valor terminal. Construye la plantilla para 5 años de proyección por defecto: agregar columnas es trivial, pero reestructurar un modelo de 3 años a 7 años después del hecho rompe las referencias relativas en todas partes.

¿Por qué mi Estado de Flujo de Caja no cuadra con la caja del Balance General?

La causa más común es un elemento faltante o contado doble en los cambios de capital de trabajo. Verifica que cada activo corriente y pasivo corriente que cambió entre períodos tenga una línea correspondiente en el FCO. Los cambios en PP&E deben fluir a través de las actividades de inversión, no del FCO. La segunda causa más común son dividendos o emisión de capital en el Balance General que no se reflejan en el flujo de financiamiento. Ejecuta la celda de verificación `=BalSheet!C25 - CashFlow!C22` y rastrea la diferencia línea por línea.

¿Puedo usar esta plantilla tanto para reportes GAAP como IFRS?

La estructura central funciona para ambos, pero 3 elementos difieren de forma significativa: los intereses pagados (FCO bajo GAAP, FCO o flujo de financiamiento bajo IFRS), las obligaciones de arrendamiento (fuera del balance bajo el GAAP anterior, en el balance bajo IFRS 16/ASC 842) y la capitalización de I+D (gasto bajo US GAAP, puede capitalizarse bajo IAS 38). Agrega un selector de "Norma de Reporte" en la pestaña Assumptions y usa lógica `IF` en las líneas afectadas si necesitas ambas presentaciones desde el mismo modelo.