Modelo financiero en Excel: guía para construir uno básico

Un modelo financiero en Excel organiza los supuestos, los cálculos y los resultados de una pequeña empresa para proyectar su flujo de caja. En este tutorial construirás un libro con cinco hojas: Entradas, Ingresos, Gastos, Flujo de caja y Resumen. El método funciona en las versiones de escritorio de Excel desde 2007, en Excel para Mac y también en Excel para la web, con las pequeñas diferencias de interfaz que existen entre ellas.

Qué es un modelo financiero y qué problema resuelve

Un modelo financiero reúne, en un mismo libro, las hipótesis de negocio, los cálculos derivados y una presentación que facilita revisarlos. En este ejemplo projectionamos los ingresos y gastos de una pequeña empresa durante seis meses.

La hoja Entradas contiene las hipótesis: precio de venta, unidades vendidas, costos y gastos. Las hojas Ingresos y Gastos calculan los importes mensuales a partir de esas referencias. La hoja Flujo de caja combina ambos bloques, calcula el saldo mensual y acumula la caja. Finalmente, Resumen reúne los resultados y permite visualizarlos en un gráfico.

La ventaja principal es cambiar un supuesto sin reescribir el modelo. Si sube el precio de venta o una unidad vendida, los resultados y el gráfico se actualizan. Para(this) iniciar, abre un libro nuevo, crea las cinco hojas indicadas y mantén cada dato de entrada en Entradas.

Las hojas y referencias que forman un modelo financiero

La estructura debe separar entradas, cálculos y resultados. Así resulta más fácil comprobar qué dato modifica el modelo y dónde aparece un error.

Las referencias entre hojas se escriben así: =Entradas!B2. Cuando el signo ! aparece dentro de una fórmula, Excel indica que la referencia está en otra hoja del mismo libro.

Supuestos separados de las fórmulas

En Entradas, escribe los datos como valores. En las demás hojas, utiliza fórmulas que apunten a estas celdas. Para copiar fórmulas sin que cambie el supuesto, utiliza referencias absolutas, por ejemplo $B$2.

Referencia entre hojas con SUMAR

La función SUMAR suma un rango. En una hoja de cálculo, por ejemplo, puedes calcular el total de cuatro celdas con:

=SUMAR(B2:B5)

Si la suma está en otra hoja, conserva el rango dentro de la fórmula:

=SUMAR(Gastos!B2:B5)

Cómo construir un modelo de ingresos y gastos

Ahora construiremos la parte central del libro. Cada mes mostrará los ingresos y dos categorías de gasto. Todas las cifras se moverán a partir de Entradas.

1. Renombra la hoja Ingresos con el botón derecho sobre su pestaña.
2. En A1, escribe Mes y en B1, Ingresos.
3. En A2, escribe Enero y en A3, Febrero. Continúa hasta Junio.
4. Selecciona B2 e introduce la fórmula de ingresos.
5. Arrastra el controlador de relleno de B2 hasta B7.
6. Cambia a la hoja Gastos.
7. En A1, escribe Gasto y en B1, Importe.
8. En A2, escribe Costo de ventas; en A3, Alquiler; en A4, Marketing.
9. En B2, escribe la fórmula del costo variable.
10. En B3 y B4, escribe las referencias a los gastos fijos.
11. Copia cada fórmula de B2:B4 hasta B7.

Ingresos por mes con una fórmula de referencia

Los ingresos de cada mes son el precio de venta multiplicado por las unidades. La fórmula se mantiene en Ingresos y consulta Entradas.

La referencia absoluta mantiene los mismos supuestos aunque copies la fórmula. En este modelo todos los meses tienen las mismas unidades; más adelante puedes crear una columna de unidades por mes en Entradas.

Gastos fijos y variables por mes

El costo de ventas depende de las unidades vendidas. El alquiler y el marketing son fijos dentro del escenario.

En B3 utiliza esta referencia:

=Entradas!$B$5

En B4:

=Entradas!$B$6

Después copia B2:B4 hasta B7.

Calcular el saldo y el flujo de caja acumulado

El flujo mensual responde a una pregunta sencilla: ¿cuánto entra menos lo que sale cada mes? El saldo acumulado muestra cuánto efectivo queda después de sucesivos flujos.

1. Renombra una hoja como Flujo de caja.
2. En A1, escribe Mes; en B1, Ingresos; en C1, Gastos; en D1, Flujo mensual; y E1, Saldo acumulado.
3. En A2, escribe Enero y continúa hasta Junio.
4. En B2, escribe =Ingresos!B2.
5. En C2, escribe =SUMAR(Gastos!B2:B4).
6. Copia B2:C2 hasta B7:C7.
7. En D2, calcula el flujo mensual.
8. En E2, calcula el primer saldo acumulado.
9. Copia las fórmulas hasta la fila 7.

La fórmula del flujo mensual

La fórmula del flujo mensual combina las filas de ingresos y gastos con SUMAR.

=SUMAR(B2:C2)

En realidad, SUMAR suma tanto el ingreso como el gasto, pero como los gastos ya están guardados como importes positivos, el resultado no es el flujo neto. Para obtenerlo correctamente en este diseño, usa:

=B2-C2

Después de corregir D2, el resultado será 450,00 y el flujo acumulado arrivalá a 2.700,00 en junio.

El saldo acumulado que revela el punto más bajo

El primer saldo acumulado es igual al flujo de enero:

=E2

Para el mes siguiente, suma el saldo anterior y el flujo actual:

=E2+D3

Copia E3 hasta E7. Esta suma progresiva conserva la referencia a la fila anterior.

En este ejemplo, el saldo baja al inicio porque no hay una entrada de efectivo inicial. Si el negocio comienza con 1.000,00, puedes añadir una celda de caja inicial y sumar ese importe al primer acumulado.

Revisar el modelo con un gráfico y una tabla resumen

Un gráfico no sustituye la revisión de fórmulas, pero hace visible si el saldo cae, se mantiene o crece. La hoja Resumen debe ser concisa: mostrar resultados, no repetir todos los cálculos.

1. Renombra una hoja como Resumen.
2. Copia A1:E7 de Flujo de caja.
3. Pega esos valores en A1:E7 de Resumen.
4. Selecciona B1:E7.
5. Inserta un gráfico de líneas o columnas.
6. Mantén el flujo mensual y el saldo acumulado como series comparables.
7. Cambia el color del flujo para distinguirlo del saldo.

Crear una tabla de resultados junto al modelo

En Resumen, organiza los resultados con una fila por mes y cuatro columnas. Si pegas el rango completo se conservan las fórmulas; si solo quieres valores, utiliza Pegado especial como valores.

Si quieres una copia estática para compartir, selecciona A1:E7 y usa Ctrl+C. Después, en otra zona de la hoja, abre Ctrl+Alt+V, elige Valores y pulsa Aceptar.

Insertar un gráfico del flujo de caja

La ruta de la cinta es:

También puedes usar:

Selecciona las series deseadas. Para comparar el flujo y el saldo, usa las columnas D y E. Si insertas un gráfico vacío, haz clic derecho sobre él y elige Seleccionar datos; después indica los rangos de la hoja Resumen.

Qué hacer cuando el modelo no cuadra o una fórmula falla

Un modelo financiero debe revisarse por capas: primero, fórmulas; después, referencias; finalmente, datos de entrada. No conviene corregir el resultado final directamente, porque ocultaría el origen del problema.

• Si aparece #¡VALOR!, comprueba si una celda contiene texto donde la fórmula espera un número.
• Si aparece #REF!, revisa si una referencia fue eliminada al borrar filas o columnas.
• Si aparece #DIV/0!, busca una división entre celdas vacías o con valor cero.
• Si el total no cambia, confirma que la fórmula no sea un valor fijo y que apunte al rango correcto.
• Si el gráfico está desactualizado, comprueba que sus datos estén en Resumen y que procedan de la hoja Flujo de caja.

Revisar referencias y resultados inesperados

Selecciona una celda y observa la barra de fórmulas. No mires solo el resultado mostrado. Si D2 contiene una suma incorrecta, la barra puede revelar el error.

La causa es que SUMAR suma los gastos positivos. Sustituye la fórmula por =B2-C2 y copia D2 hasta D7. Después corrige también E2 y arrastra E2 hasta E7.

Evitar que falten datos en los supuestos

Marca las celdas editables en Entradas, por ejemplo con un color distinto, y evita dejarlas vacías. Si una entrada puede ser cero, escribe 0; si no aplica, utiliza una celda de texto explicativo, como «No aplica», y ajusta las fórmulas para que no intenten multiplicarlo.

Después de corregir la entrada, revisa en este orden: Ingresos, Gastos, Flujo de caja y Resumen. Si una hoja depende de otra, el error puede aparecer primero en la hoja de destino.

Qué debe tener un modelo financiero para ser útil

Un modelo útil debe ser fácil de modificar y auditable. En este libro, las hojas Entradas, Ingresos, Gastos, Flujo de caja y Resumen separan los datos de entrada de los resultados. Las referencias absolutas protegen los supuestos al copiar fórmulas y las referencias entre hojas conectan el cálculo.

• Entradas: unaassumption(por supuesto), una cifra y una unidad.
• Ingresos y gastos: bloques separados, sin duplicar supuestos.
• Flujo de caja: fórmulas homogeneous y consistentes en todas las filas.
• Resumen: resultados claros y un gráfico que facilite la lectura.
• Archivo de trabajo: no mezcles datos definitivos con celdas de cálculo.
• Documentación: anota la fuente, la fecha y el significado de cada entrada.
• Revisión: comprueba que el saldo final sea coherente con los flujos mensuales.

Si el libro tuviera más de un millón de filas por hoja, Excel mostraría un error al intentarlo; aun dentro del límite de 1.048.576 filas por hoja, conviene dividir el modelo por año o por área. El límite de columnas también es de 16.384, hasta XFD.

Lo que ya sabes hacer con un modelo financiero

• Organizaste entradas, cálculos y resultados en cinco hojas.
• Relacionaste Ingresos y Gastos con referencias a Entradas.
• Calculaste el flujo mensual con =B2-C2.
• Acumulaste el saldo mes a mes con una suma progresiva.
• Localizaste y corregiste una fórmula que sumaba los gastos.
• Creaste un Resumen y un gráfico que reaccionan a los supuestos.
• Dejaste lista la base para comparar otro escenario.

El siguiente paso es duplicar el libro o añadir una hoja Escenario optimista con otros supuestos y comparar ambos resultados.