Previsión de ventas en Excel: tendencia, estacionalidad y embudo comercial ponderado
Tres métodos para hacer la previsión de ventas de una pyme en Excel: tendencia con coeficientes estacionales, la función PRONOSTICO.ETS y el embudo comercial ponderado, con escenarios y seguimiento de previsión frente a real.
Una previsión de ventas en Excel estima la facturación de los próximos meses a partir de lo que ya sabes: las ventas pasadas, su estacionalidad, las operaciones en curso y los cambios previstos, como una subida de precios o un producto nuevo. Para una pyme bastan tres métodos que se pueden combinar: tendencia con coeficientes estacionales, la función de previsión de Excel y el embudo comercial ponderado. Esta guía explica cada uno con fórmulas y cómo seguir después la previsión frente a lo real.
Preparar los datos
Una fila por mes con la fecha (primer día del mes), la facturación y, si es posible, el desglose por familia de producto o canal. Hacen falta al menos dos años para ver la estacionalidad. Ajusta los meses extraordinarios (un cierre por obras, un pedido único muy grande) en una columna “ventas ajustadas” para que no distorsionen la previsión. Los totales mensuales salen del control de ventas en Excel.
Método 1: tendencia y coeficientes estacionales
- Coeficiente de cada mes: la media de ventas de ese mes en los años disponibles dividida entre la media mensual. Un 1,5 en diciembre significa que diciembre vende un 50 % más que un mes medio.
- Venta anual prevista: ventas del año pasado × (1 + crecimiento), con un crecimiento justificado por la tendencia y tus planes.
- Previsión mensual: venta anual prevista ÷ 12 × coeficiente del mes.
Excel en español separa los argumentos con punto y coma. Con la fecha en A, las ventas en B y =MES(A2) en C:
- Coeficiente de diciembre:
=PROMEDIO.SI.CONJUNTO(B:B;C:C;12)/PROMEDIO(B:B) - Previsión de un mes:
=VentaAnual/12*BUSCARX(MES(A30);Coef[Mes];Coef[Coeficiente])
Comprueba que los doce coeficientes suman 12; si no, divide cada uno entre su media.
Ejemplo: tienda de material deportivo
El año pasado la tienda facturó 380.000 € y prevé crecer un 5 %, es decir, 399.000 € este año, 33.250 € en un mes medio. Con un coeficiente de 1,5 en diciembre y de 0,7 en febrero, la previsión es de unos 49.900 € en diciembre y 23.300 € en febrero. Esa diferencia es lo que necesita la previsión de tesorería para preparar las compras de campaña y cubrir los meses flojos.
Método 2: la función PRONOSTICO.ETS
Desde Excel 2016, PRONOSTICO.ETS (suavizado exponencial) detecta tendencia y estacionalidad automáticamente:
=PRONOSTICO.ETS(A30;B$2:B$29;A$2:A$29;12)
El último argumento indica una estacionalidad de 12 meses. PRONOSTICO.ETS.CONFINT da un margen de incertidumbre, útil para un escenario bajo y otro alto. El menú Datos > Hoja de previsión hace el mismo cálculo con un gráfico. Es rápido, pero no conoce tus decisiones: una subida de precios o una línea nueva hay que añadirlas a mano.
Método 3: el embudo comercial ponderado
Si vendes a empresas o por presupuesto, el histórico dice poco sobre el próximo trimestre; cuentan las oportunidades abiertas. Lista cada una con cliente, importe, fase y fecha probable de cierre, y aplica una probabilidad por fase:
| Fase | Probabilidad orientativa |
|---|---|
| Contacto cualificado | 10 % |
| Necesidad validada, presupuesto en preparación | 30 % |
| Presupuesto enviado | 50 % |
| Negociación final | 80 % |
| Ganada | 100 % |
Venta ponderada de un mes: =SUMAPRODUCTO((MES(Op[Cierre])=MES(A2))*(AÑO(Op[Cierre])=AÑO(A2))*Op[Importe]*Op[Probabilidad]). Sustituye las probabilidades orientativas por tus tasas reales de cierre tras unos meses. La plantilla CRM en Excel puede alojar este embudo.
Combinar métodos y crear escenarios
Muchas pymes suman una base recurrente (método 1 o 2) y las operaciones nuevas del embudo (método 3). Guarda tres versiones de las hipótesis clave (prudente, base, optimista) y cámbialas con =ELEGIR(Escenario;Prudente;Base;Optimista). El escenario prudente es el que conviene comparar con los costes fijos en el punto de equilibrio.
Previsión frente a real
- Desviación:
=Real-Prevision - Desviación en %:
=SI.ERROR(Real/Prevision-1;"") - Error medio de 12 meses: la media de las desviaciones absolutas en porcentaje, calculada en una columna auxiliar.
Explica en una frase cada desviación superior al 10 %. Con unos meses de notas verás si el método tiene sesgo, por ejemplo si siempre es demasiado optimista.
De la previsión al presupuesto
La previsión de ventas es la primera línea del presupuesto empresarial y, para un negocio nuevo, la base del plan de negocios. Más guías en Finanzas para pymes.
Preguntas frecuentes
¿Qué método de previsión conviene a una pyme?
Con dos años de histórico y clientela de paso, la tendencia con coeficientes estacionales. Si vendes por presupuesto, el embudo ponderado. A menudo, una combinación de ambos.
¿Con cuánta antelación prever?
Doce meses móviles, actualizados cada mes, dan visibilidad suficiente para la tesorería y el presupuesto.
¿Cómo prever las ventas de un negocio que empieza?
Sin histórico, parte de la capacidad (clientes atendidos al día, horas facturables), del mercado de tu zona y de tu plan comercial, y sustituye las hipótesis por datos reales en cuanto los tengas.
¿PRONOSTICO.ETS funciona en Google Sheets?
Google Sheets no tiene un equivalente exacto; usa el método de coeficientes estacionales o PRONOSTICO para una tendencia lineal.
