Tutorial: forecast a 12 meses con resultados, balance y flujos de caja enlazados en tres escenarios, desde el libro diario en cuatro prompts

En dos frases

Un forecast de los tres estados es sobre todo disciplina contable: cada euro de beneficio tiene que acabar en algún sitio del balance, y la caja es lo que queda después de que el circulante, la inversión y la deuda se hayan llevado su parte. Este tutorial usa un asistente de IA para escribir el código y las fórmulas, deja en tus manos las decisiones sobre los drivers y obliga al modelo a demostrar cada mes que cuadra.

Por qué dividirlo en cuatro prompts

Si le pides a un asistente «monta un forecast a 12 meses con tres escenarios» de una vez, lo normal es que produzca algo con buena pinta y al menos uno de estos errores: la caja tecleada como cifra de cuadre, unos clientes que no se mueven con las ventas, intereses que ignoran las amortizaciones del préstamo o un balance que solo cuadra el primer mes. No sabrás cuál hasta que escarbes.

Cuatro prompts más pequeños con una parada tras cada uno te permiten revisar los cimientos antes de construir encima. El orden importa: primero los datos reales, luego los drivers, después la mecánica y, solo al final, el relato.

La empresa ficticia y los archivos

La muestra es un pequeño distribuidor mayorista con ventas estacionales (un septiembre a noviembre fuerte y un agosto flojo), un margen bruto del 37 % al 38 %, existencias de casi dos meses de coste de ventas, clientes que pagan a unos 60 días y un préstamo bancario que se amortiza a 5.000 al mes. Tiene 24 meses de diario, de octubre de 2024 a septiembre de 2026, con 694 apuntes.

• Libro diario, 24 meses (CSV)
• Mapeo de cuentas (CSV)
• Modelo de referencia (Python, solo biblioteca estándar)
• Salida: 12 meses × 3 escenarios (CSV)

Los archivos tienen las columnas y las líneas en inglés y las cuentas con códigos del Plan General Contable (700, 430, 572…). El modelo de referencia es nuestro: es aquello a lo que debería equivaler una respuesta correcta a los prompts 1 a 4. Úsalo para comprobar lo que te construya el asistente, no como herramienta de planificación terminada.

Cómo sacar el diario de tu ERP

Necesitas una fila por apunte con fecha, cuenta, debe y haber; la descripción y el número de asiento ayudan. Exporta al menos 12 meses y mejor 24, para que cada mes previsto tenga una referencia del mismo mes del año anterior.

• Odoo: la lista de apuntes contables (o el informe de libro mayor) filtrada por fecha y exportada a XLSX.
• Microsoft Dynamics 365 Business Central: la página de movimientos de contabilidad, filtrada por fecha de registro y abierta en Excel.
• NetSuite: una búsqueda guardada de transacciones con las líneas de impacto contable (cuenta, periodo, debe, haber), exportada a CSV.
• QuickBooks Online y Xero: el informe de libro mayor o de diario del periodo, exportado a Excel o CSV.
• Sage, A3 y Holded: la exportación del libro diario del periodo en Excel o CSV.

Exporta también el plan de cuentas. El archivo de mapeo es corto y se escribe una vez: cuenta, línea del estado (Revenue, COGS, Opex, Payroll, Depreciation, Interest, Tax, Cash, Receivables, Inventory, PPE, Payables, Tax payable, Debt, Equity) y estado (PL o BS).

Prompt 1: los datos reales por mes

Adjuntos: general-ledger.csv (apuntes del diario) y account-mapping.csv.
Escribe y ejecuta código (no hagas cálculos de cabeza) que:
1. Compruebe que el total del debe es igual al del haber, en conjunto y en cada asiento. Si no, para y avísame.
2. Aplique el mapeo y liste todas las cuentas sin mapear con su total. No te inventes ningún mapeo.
3. Genere, para cada mes, los movimientos de la cuenta de resultados por línea y los saldos de cierre del balance por línea.
4. Muestre una tabla de control: en cada mes, activo menos pasivo menos patrimonio neto menos resultado acumulado debe dar cero.
Enséñame el código, las cuentas sin mapear y la tabla de control. Después, para.

Parada de revisión. Comprueba que los saldos del último mes coinciden, línea a línea, con tu sumas y saldos cerrado. Si la tabla de control no da cero, el problema está en el mapeo o en la exportación, no en el forecast.

Prompt 2: ratios de referencia y drivers propuestos

Usando solo los datos reales mensuales del paso anterior, calcula con código:
- las ventas de los últimos 12 meses y las de los mismos meses del año anterior (tasa de crecimiento);
- el % de margen bruto de los últimos 12 meses;
- la media mensual de gastos de explotación y de personal de los últimos 3 meses;
- los días implícitos de clientes (saldo / ventas del mes × 30), existencias (saldo / coste de ventas del mes × 30)
  y proveedores (saldo / compras del mes × 30) en cada uno de los últimos 3 cierres.
Después propón los drivers de tres escenarios (base, optimista y pesimista) en una tabla con una columna por escenario
y una columna "por qué" que cite el ratio del que parte. Titula la tabla PROPUESTA. No construyas nada todavía. Para.

En la muestra, los ratios son 2,70 millones de ventas en los últimos 12 meses, un margen bruto del 37,5 % y unos días implícitos de unos 59 para clientes, 54 para existencias y 43 para proveedores. Tú decides después los valores finales. Los de nuestro modelo de referencia son:

Driver	Base	Optimista	Pesimista
Ventas frente al mismo mes del año anterior	+5 %	+10 %	−5 %
Margen bruto frente a los últimos 12 meses	±0 puntos	+1 punto	−2 puntos
Días de cobro (DSO)	60	55	70
Días de existencias (DIO)	55	52	62
Días de pago (DPO)	45	45	38

Comunes a los tres: gastos de explotación a la media de los últimos tres meses más un 3 % de inflación, una subida del 3 % en personal desde enero, 30.000 de inversión en marzo de 2027 amortizados en 60 meses, el préstamo amortizado a 5.000 al mes con un interés del 4,5 % y el impuesto sobre sociedades al 25 % del beneficio mensual positivo, pagado en julio. Ese calendario fiscal es una simplificación: sustitúyelo por tu calendario real de pagos fraccionados y liquidación.

Prompt 3: el modelo, con fórmulas

Construye el forecast de los próximos 12 meses con los drivers de la tabla adjunta (valores finales).
Reglas:
- Usa fórmulas o código que recalculen; no teclees nunca un resultado. Todos los drivers, en una hoja de Supuestos.
- Resultados: ventas = mismo mes del año anterior × (1 + crecimiento); coste de ventas a partir del margen bruto; gastos de
  explotación, personal, amortización (existente + nueva inversión / 60), intereses = deuda inicial × tipo / 12,
  impuesto sobre el beneficio positivo.
- Balance: clientes, existencias y proveedores a partir de los días; inmovilizado = inicial + inversión − amortización;
  deuda con su calendario de amortización; patrimonio neto = inicial + resultado.
- Estado de flujos, método indirecto: resultado + amortización − aumento de clientes − aumento de existencias
  + aumento de proveedores + impuesto devengado − impuesto pagado; inversión = − capex; financiación = − amortizaciones de deuda.
- Caja = caja inicial + flujo total. La caja nunca es una cifra de cuadre.
- Una hoja de Comprobaciones con, para cada mes: activo − pasivo − patrimonio neto; caja inicial + flujo − caja final;
  patrimonio inicial + resultado − patrimonio final. Todas deben dar cero.
Devuélveme el archivo y la tabla de Comprobaciones. Para.

Si tu asistente trabaja dentro de Excel, pide rangos con nombre en la hoja de Supuestos y fórmulas sencillas que puedas auditar. Si trabaja con código, pide el script además de la salida. En los dos casos, abre primero la hoja de Comprobaciones.

El núcleo de nuestro modelo de referencia, para comparar:

ar1, inv1 = rev * g["dso"] / 30, cogs * g["dio"] / 30
purch = cogs + inv1 - inv; ap1 = purch * g["dpo"] / 30
taxpaid = taxp if m.endswith("-07") else 0
cfo = ni + dep - (ar1 - ar) - (inv1 - inv) + (ap1 - ap) + (tax - taxpaid)
cfi = -capex; cff = -min(INSTAL, debt)
cash += cfo + cfi + cff
...
check = round(cash + ar + inv + ppe - ap - taxp - debt - eq, 2)   # debe ser 0 todos los meses

Prompt 4: escenarios y relato

Ejecuta los tres escenarios y dame una sola tabla resumen: ventas, EBITDA, resultado neto, caja final, deuda,
la caja mínima a fin de mes y el mes en que se produce, y si todas las comprobaciones dan cero.
Después redacta un relato de 200 palabras como máximo para el comité de dirección que:
- use solo cifras de la tabla resumen;
- explique qué explica la diferencia entre escenarios (nombra los drivers);
- diga qué mes es el de menor caja y por qué;
- enumere las tres hipótesis que más pesan y una sensibilidad que convenga probar después.

La salida de referencia de la muestra:

Próximos 12 meses	Base	Optimista	Pesimista
Ventas	2.835.635	2.970.665	2.565.575
EBITDA	264.845	345.169	112.300
Resultado neto	164.020	225.480	45.223
Caja final, sep. 2027	464.568	562.716	218.582
Caja mínima a fin de mes	242.999 (oct. 2026)	273.175 (oct. 2026)	120.066 (jul. 2027)
Comprobaciones mensuales	12/12 a cero	12/12 a cero	12/12 a cero

Un buen relato debería ver que la caja baja en octubre en todos los escenarios. No es una caída de ventas: septiembre es el mes de más compras y sus facturas de proveedores se pagan en octubre. En el escenario pesimista el mínimo se desplaza a julio, cuando se paga el impuesto devengado sobre un margen más débil y unos cobros más lentos.

Dónde encaja la previsión propia de Excel

Nuestra regla de ventas (mismo mes del año anterior más crecimiento) es deliberadamente sencilla y fácil de explicar. Si prefieres una base estadística, la hoja de previsión de Excel y la función PRONOSTICO.ETS, documentadas por Microsoft, ajustan un modelo de suavizado exponencial que detecta la estacionalidad y pueden añadir un intervalo de confianza. Úsala para generar la línea base de ventas, guárdala en la hoja de Supuestos y deja que el resto del modelo funcione igual. Python en Excel es otra opción si tu equipo ya lo usa.

Pasos de revisión humana

1. Saldos de apertura: el primer mes previsto parte del sumas y saldos cerrado, línea a línea.
2. Un mes a mano: recalcula con la calculadora los clientes, proveedores, el flujo y la caja final de octubre.
3. Drivers: cada uno tiene un responsable y un motivo; los días de cobro y de pago se comparan con los tres últimos cierres.
4. Comprobaciones: las tres filas de comprobación dan cero en todos los meses y escenarios. Una diferencia de 0,01 es una fórmula que no apunta a la celda correcta, no un detalle de redondeo.
5. Relato: todas las cifras del texto están en la tabla resumen.

Errores típicos

• La caja como cifra de cuadre. Si la caja se teclea o se calcula como «lo que haga cuadrar», el modelo cuadrará siempre y no te dirá nada.
• Intereses sin calendario de amortización. Los intereses deben bajar a medida que se devuelve el préstamo; un importe fijo sobrestima el coste y esconde la relación.
• Días aplicados sobre la base equivocada. Clientes van con ventas, existencias con coste de ventas y proveedores con compras (coste de ventas más la variación de existencias).
• Un salto en el primer mes. Si tus drivers se alejan mucho de los días implícitos del último cierre, el primer mes previsto da un salto. Parte de los días reales y muévelos poco a poco.
• Veinte escenarios. Tres escenarios bien explicados se defienden mejor que muchos sin explicar; una sensibilidad sobre un driver suele ser más útil que otro escenario.
• Ignorar el calendario fiscal. Devengar el impuesto sin calendario de pagos sobrestima la caja durante casi todo el año.
• Subir el diario completo a una cuenta personal. Contiene clientes, proveedores y sueldos. Usa un plan de empresa aprobado, o agrega y anonimiza antes de subirlo.

Límites

El modelo es mensual y basado en drivers, no una previsión de tesorería por operaciones; para las próximas semanas de caja es más preciso un presupuesto de cobros y pagos a partir de partidas abiertas. No trata el IVA, varias monedas ni saldos intragrupo, y el tratamiento fiscal está simplificado. El asistente escribe la mecánica; los drivers, y la decisión de presentarlos, son tuyos.