KHIZHNYAKOVCómo calcular los bonos de los vendedores en Excel con metas y escala de comisiones
Control de ventas y cálculo de bonos en Excel: metas, embudo y escala de bonos — Plantilla Excel — Ver la plantilla →
En muchos equipos comerciales el bono se calcula a mano a final de mes, juntando datos de varias fuentes. Resultado: dudas sobre qué operaciones cuentan, discusiones sobre el porcentaje aplicado y errores que se descubren tarde.
La solución es fijar reglas claras y dejar que una hoja de cálculo las aplique siempre igual. En esta guía verás cómo definir metas, qué operaciones deben contarse, cómo montar una escala de tasas con una sola fórmula y cómo seguir el embudo sin necesidad de un CRM.
Paso 1: decide qué cuenta como resultado
La regla más importante es qué operaciones suman. Una opción habitual es contar solo lo cobrado en el mes, no lo firmado ni lo presupuestado. Para aplicarla necesitas por operación: fecha de creación, vendedor, cliente, importe, etapa (por ejemplo, Nuevo, Negociación, Pagado) y fecha de pago.
El resultado del vendedor es entonces:
=SUMAR.SI.CONJUNTO(Importe;Vendedor;A2;Etapa;"Pagado";FechaPago;">="&Inicio;FechaPago;"<="&Fin)
donde Inicio y Fin son el primer y último día del mes de cálculo. Una operación pagada sin fecha de pago no se contaría, así que conviene añadir una columna de control que avise cuando falte.
Paso 2: cumplimiento de la meta
Cada vendedor tiene una meta mensual. El cumplimiento es simplemente:
=Resultado/Meta
Si la meta de Marta es de 20.000 € y ha cobrado 17.000 €, su cumplimiento es del 85 %. Para ver el estado de un vistazo, añade un semáforo con formato condicional: verde a partir del 100 %, amarillo entre 70 % y 100 %, rojo por debajo.
Paso 3: la escala de bonos
En lugar de anidar muchas funciones SI, crea una tabla con dos columnas: «desde % de cumplimiento» y «tasa». Por ejemplo:
| Desde % de meta | Tasa de bono |
|---|---|
| 0 % | 0 % |
| 70 % | 2 % |
| 100 % | 4 % |
| 120 % | 6 % |
La tasa que corresponde es la más alta cuyo umbral se ha alcanzado, y se obtiene con una búsqueda aproximada:
=BUSCARV(Cumplimiento;Escala;2;VERDADERO)
Para que funcione, la primera columna debe estar ordenada de menor a mayor. Con el 85 % de Marta, la tasa es del 2 %. El bono es 17.000 € × 2 % = 340 €. Si su sueldo base fuera de 1.600 €, el total a pagar sería de 1.940 €.
Cuidado: este es un cálculo del bono según tu escala. No incluye impuestos ni cotizaciones, que dependen de tu política de remuneración y de la normativa vigente.
Paso 4: el embudo de ventas
Un bono no sirve de mucho si no ves lo que viene. Cuenta cuántas operaciones y qué importe hay en cada etapa:
=CONTAR.SI.CONJUNTO(Etapa;"Negociación";Vendedor;A2)
y la conversión a pago: operaciones pagadas dividido entre las creadas. Si de 40 operaciones creadas en el mes se cobran 10, la conversión es del 25 %. Esa cifra te dice si el problema del vendedor es la captación o el cierre.
Errores frecuentes
- Cambiar las reglas a mitad de mes. Fija escala y metas antes de empezar.
- Contar operaciones no cobradas. Si el criterio es «pagado», una venta sin pago no cuenta, aunque esté firmada.
- Escala desordenada. La búsqueda aproximada falla si los umbrales no están de menor a mayor.
- Mezclar bono y sueldo base en una misma celda. Sepáralos para auditar fácilmente.
- Fórmulas con números dentro. Los porcentajes deben estar en celdas de parámetros, no escritos en la fórmula.
Plantilla de Excel para ventas y bonos
La plantilla de control de ventas y cálculo de bonos aplica este método de forma automática: las operaciones se anotan en una fila, el resultado se toma de las operaciones pagadas del mes, la tasa se elige según la escala y la planilla muestra de inmediato el bono y el total a pagar. Es una plantilla de pago.
Incluye cuatro hojas:
- «Catálogos»: vendedores con meta y sueldo base, escala de bonos, productos y mes de cálculo.
- «Ventas»: hasta 1000 operaciones con fecha de creación, vendedor, cliente, producto, importe, etapa del embudo, fecha de pago y control de datos completos.
- «Planilla»: meta, resultado, cumplimiento, semáforo, tasa según la escala, bono, sueldo base, total a pagar, cartera en curso y conversión de cada vendedor.
- «Resumen»: embudo por etapas, ventas pagadas por mes del año y tres gráficos.
La meta, el sueldo base y la escala se ajustan a las condiciones de tu empresa. Funciona con Excel 2007 y posteriores y con LibreOffice, sin macros, y el archivo está en español. El cálculo es orientativo. Está pensada para jefes de ventas, directores comerciales, dueños de pequeños negocios y contables.
Preguntas frecuentes
¿Es mejor pagar bono por ventas firmadas o por ventas cobradas?
Cobradas es más prudente, porque protege a la empresa de impagos. Si usas firmadas, define qué pasa cuando el cliente no paga. Lo importante es que la regla esté escrita y sea la misma para todos.
¿Cuántos tramos debe tener una escala?
Entre tres y cinco suelen bastar. Con demasiados tramos nadie entiende cuánto le falta para subir de nivel.
¿Cómo trato las bajas o altas a mitad de mes?
Ajusta la meta de forma proporcional a los días trabajados y documenta el criterio. Así evitas agravios entre compañeros.
¿Puedo hacer seguimiento sin un CRM?
Para un equipo pequeño, sí: una tabla con etapas, fechas e importes da una visión clara del embudo. Si el equipo crece, un CRM aportará automatización y más historial.
