Tooldata Academia
Lección 35 de 37•Proyecto - Dashboard de Ventas

Crear métricas y KPIs de ventas

Define y calcula los indicadores clave.

13 minutos

Con la estructura de datos lista, es momento de definir las metricas que daran vida al dashboard. Los KPIs (Key Performance Indicators) son las metricas criticas que el negocio necesita monitorear para tomar decisiones.

En esta leccion, crearemos una hoja de metricas centralizada que alimentara todas las visualizaciones del dashboard.

KPIs esenciales para ventas

Antes de escribir formulas, definamos que necesitamos medir. Un dashboard de ventas efectivo debe responder estas preguntas:

Metricas de Volumen

  • Cuanto vendimos en dinero (Revenue)
  • Cuantas unidades vendimos
  • Cuantas transacciones realizamos

Metricas de Rendimiento

  • Como vamos vs el objetivo (Cumplimiento de meta)
  • Como comparamos vs periodo anterior (Crecimiento)
  • Cual es el ticket promedio

Metricas de Analisis

  • Que productos generan mas ingresos
  • Que vendedores cumplen metas
  • Que regiones tienen mejor desempeno

Paso 1: Crear la hoja de Metricas

Crea una nueva hoja llamada "Metricas" que servira como centro de calculos.

Estructura de la hoja

Organiza la hoja en secciones claras:

Fila 1-2:   [Encabezado: METRICAS DEL DASHBOARD]
Fila 4:     [Seccion: FILTROS ACTIVOS]
Fila 5-7:   [Celdas para filtros dinamicos]

Fila 10:    [Seccion: KPIs PRINCIPALES]
Fila 11-20: [Metricas de volumen y rendimiento]

Fila 23:    [Seccion: ANALISIS POR DIMENSION]
Fila 24-40: [Calculos por region, vendedor, producto]

Fila 43:    [Seccion: TENDENCIAS]
Fila 44-60: [Calculos mensuales y comparativos]

Configurar celdas de filtro

En las celdas de filtro, permitiremos seleccionar periodo y otras dimensiones:

  • C5: Ano (con validacion: 2025, 2026)
  • C6: Mes (con validacion: 1-12 o "Todos")
  • C7: Region (con validacion: Norte, Sur, Este, Oeste o "Todas")

Agrega validaciones de datos a cada celda de filtro.

Paso 2: Metricas de Volumen

Revenue Total

La metrica mas importante: el total de ventas en dinero.

En la celda que designes para Revenue (por ejemplo, C12):

=SUMIFS(
    tbl_Ventas[Total_Venta],
    tbl_Ventas[Fecha],">="&DATE(C5,IF(C6="Todos",1,C6),1),
    tbl_Ventas[Fecha],"<="&EOMONTH(DATE(C5,IF(C6="Todos",12,C6),1),0),
    tbl_Ventas[Region],IF(C7="Todas","*",C7)
)

Esta formula:

  1. Suma la columna Total_Venta
  2. Filtra por el rango de fechas seleccionado
  3. Filtra por region si no es "Todas"

Nota: Si C6 o C7 estan en blanco o dicen "Todos/Todas", la formula considera todos los valores.

Simplificar con formula auxiliar

Para evitar repetir la logica de filtros, crea celdas auxiliares:

  • G5 (Fecha Inicio): =DATE(C5,IF(C6="Todos",1,C6),1)
  • G6 (Fecha Fin): =EOMONTH(DATE(C5,IF(C6="Todos",12,C6),1),0)

Ahora la formula de Revenue se simplifica:

=SUMIFS(tbl_Ventas[Total_Venta],tbl_Ventas[Fecha],">="&G5,tbl_Ventas[Fecha],"<="&G6)

Unidades Vendidas

=SUMIFS(tbl_Ventas[Unidades],tbl_Ventas[Fecha],">="&G5,tbl_Ventas[Fecha],"<="&G6)

Numero de Transacciones

=COUNTIFS(tbl_Ventas[Fecha],">="&G5,tbl_Ventas[Fecha],"<="&G6)

Ticket Promedio

=IFERROR([Revenue]/[Transacciones],0)

Donde [Revenue] y [Transacciones] son referencias a las celdas que contienen esas metricas.

Unidades por Transaccion

=IFERROR([Unidades]/[Transacciones],0)

Paso 3: Metricas de Cumplimiento

Meta del Periodo

Primero, calculemos la meta total para el periodo seleccionado:

=SUMIFS(
    tbl_Vendedores[Meta_Mensual],
    tbl_Vendedores[Region],IF(C7="Todas","*",C7)
) * IF(C6="Todos",12,1)

Esta formula:

  1. Suma las metas mensuales de los vendedores
  2. Filtra por region si aplica
  3. Multiplica por 12 si es todo el ano, o por 1 si es un mes especifico

Porcentaje de Cumplimiento

=IFERROR([Revenue]/[Meta],0)

Formato: Porcentaje con 1 decimal

Gap vs Meta

=[Revenue]-[Meta]

Un valor positivo significa que superamos la meta; negativo indica que estamos por debajo.

Estado de Cumplimiento

Crea una formula que categorice el cumplimiento:

=IF([Cumplimiento]>=1,"Superado",IF([Cumplimiento]>=0.9,"En Meta",IF([Cumplimiento]>=0.7,"Riesgo","Critico")))

Paso 4: Metricas de Crecimiento

Para calcular crecimiento, necesitamos comparar con el periodo anterior.

Revenue Periodo Anterior

=SUMIFS(
    tbl_Ventas[Total_Venta],
    tbl_Ventas[Fecha],">="&EDATE(G5,-12),
    tbl_Ventas[Fecha],"<="&EDATE(G6,-12)
)

EDATE resta 12 meses a las fechas, dando el mismo periodo del ano anterior.

Crecimiento YoY (Year over Year)

=IFERROR(([Revenue]-[Revenue_Anterior])/[Revenue_Anterior],0)

Crecimiento Absoluto

=[Revenue]-[Revenue_Anterior]

Para comparacion mes a mes (MoM)

Si quieres comparar con el mes anterior en lugar del ano anterior:

=SUMIFS(
    tbl_Ventas[Total_Venta],
    tbl_Ventas[Fecha],">="&EDATE(G5,-1),
    tbl_Ventas[Fecha],"<="&EDATE(G6,-1)
)

Paso 5: Metricas por Dimension

Tabla de Revenue por Region

Crea una tabla auxiliar con las cuatro regiones:

Region Revenue Meta Cumplimiento Ranking
Norte
Sur
Este
Oeste

Formula para Revenue por region (ejemplo para Norte en C25):

=SUMIFS(tbl_Ventas[Total_Venta],tbl_Ventas[Fecha],">="&$G$5,tbl_Ventas[Fecha],"<="&$G$6,tbl_Ventas[Region],A25)

Formula para Meta por region:

=SUMIFS(tbl_Vendedores[Meta_Mensual],tbl_Vendedores[Region],A25)*IF($C$6="Todos",12,1)

Formula para Cumplimiento:

=IFERROR(C25/D25,0)

Formula para Ranking (usando RANK):

=RANK(C25,$C$25:$C$28,0)

Tabla de Top 5 Vendedores

Crea una tabla para mostrar los mejores vendedores:

Rank Vendedor Revenue Meta Cumplimiento
1
2
3
4
5

Para obtener el vendedor en posicion N, usa esta combinacion de formulas:

Primero, crea una columna auxiliar con el revenue por vendedor (puede ser una tabla dinamica o SUMIFS).

Luego usa INDEX/MATCH para obtener el nombre del vendedor en cada posicion:

=INDEX(tbl_Vendedores[Nombre],MATCH(LARGE([RangoRevenue],A32),[RangoRevenue],0))

Tabla de Top 5 Productos

Similar a vendedores:

Rank Producto Unidades Revenue Margen
1
2
3
4
5

Paso 6: Metricas de Tendencia

Revenue Mensual

Crea una tabla con los 12 meses del ano:

Mes Revenue Meta Cumplimiento vs Anterior
Enero
Febrero
...
Diciembre

Formula para Revenue del mes (ejemplo Enero, fila 45):

=SUMIFS(tbl_Ventas[Total_Venta],tbl_Ventas[Fecha],">="&DATE($C$5,A45,1),tbl_Ventas[Fecha],"<="&EOMONTH(DATE($C$5,A45,1),0))

Donde A45 contiene el numero del mes (1 para Enero).

Acumulado YTD (Year to Date)

Agrega una columna de acumulado:

=SUM($C$45:C45)

Esta formula suma desde el primer mes hasta el mes actual.

Tendencia (Promedio Movil)

Para suavizar fluctuaciones, calcula un promedio movil de 3 meses:

=IFERROR(AVERAGE(OFFSET(C45,-2,0,3,1)),"")

Paso 7: KPIs Avanzados

Margen de Ganancia

Si tus productos tienen costo, calcula el margen:

=SUMPRODUCT(
    (tbl_Ventas[Fecha]>=G5)*(tbl_Ventas[Fecha]<=G6)*
    tbl_Ventas[Unidades]*
    (VLOOKUP(tbl_Ventas[ID_Producto],tbl_Productos,4,FALSE))
)

Esta formula calcula el costo total. El margen seria:

=[Revenue]-[Costo_Total]

Y el porcentaje de margen:

=IFERROR([Margen]/[Revenue],0)

Descuento Promedio

=AVERAGEIFS(tbl_Ventas[Descuento],tbl_Ventas[Fecha],">="&G5,tbl_Ventas[Fecha],"<="&G6)

Conversion de Descuento a Revenue Perdido

=SUMPRODUCT(
    (tbl_Ventas[Fecha]>=G5)*(tbl_Ventas[Fecha]<=G6)*
    tbl_Ventas[Unidades]*tbl_Ventas[Precio_Unitario]*tbl_Ventas[Descuento]
)

Dias Promedio Entre Compras

Si tienes datos historicos de clientes:

=AVERAGEIFS(DiasEntreCompras,Fecha,">="&G5,Fecha,"<="&G6)

Paso 8: Crear Nombres de Rango

Para facilitar el uso de las metricas en el dashboard, crea nombres de rango:

  1. Selecciona la celda con el Revenue total
  2. Formulas > Definir nombre
  3. Nombre: KPI_Revenue
  4. Ambito: Libro

Repite para las metricas principales:

Nombre Descripcion
KPI_Revenue Revenue total del periodo
KPI_Unidades Unidades vendidas
KPI_Transacciones Numero de transacciones
KPI_Ticket Ticket promedio
KPI_Meta Meta del periodo
KPI_Cumplimiento Porcentaje de cumplimiento
KPI_Crecimiento Crecimiento YoY

Ahora puedes usar =KPI_Revenue en cualquier parte del libro.

Paso 9: Formato Condicional para KPIs

Aplica formato condicional para resaltar el estado de cada metrica:

Para Cumplimiento de Meta

  1. Selecciona la celda de cumplimiento
  2. Inicio > Formato condicional > Nueva regla
  3. Tipo: Usar formula
  4. Formula: =C15>=1
  5. Formato: Fondo verde, texto blanco

Repite con:

  • >=0.9 y <1: Amarillo
  • >=0.7 y <0.9: Naranja
  • <0.7: Rojo

Para Crecimiento

  • Positivo: Flecha verde hacia arriba
  • Negativo: Flecha roja hacia abajo
  1. Selecciona la celda
  2. Formato condicional > Conjunto de iconos
  3. Elige flechas direccionales
  4. Configura los umbrales

Validacion de Metricas

Antes de continuar al dashboard, verifica que las metricas sean correctas:

Checklist de validacion

Verificacion Formula de Validacion
Revenue = suma de todas las ventas =KPI_Revenue=SUM(tbl_Ventas[Total_Venta])
Unidades = suma de todas las unidades =KPI_Unidades=SUM(tbl_Ventas[Unidades])
Suma regiones = Total =SUM(RevenueRegiones)=KPI_Revenue
Suma mensual = Total anual =SUM(RevenueMensual)=KPI_Revenue

Si todas devuelven VERDADERO, tus metricas estan correctas.

Prueba de filtros

  1. Cambia el filtro de ano y verifica que las metricas se actualicen
  2. Cambia el filtro de mes y confirma los valores
  3. Cambia el filtro de region y valida la segmentacion

Tabla de Referencia de KPIs

Documenta tus KPIs para referencia futura:

KPI Formula Unidad Meta Frecuencia
Revenue SUMIFS ventas $ $2.5M/mes Diario
Unidades SUMIFS unidades # Variable Diario
Cumplimiento Revenue/Meta % 100% Mensual
Crecimiento YoY (Actual-Anterior)/Anterior % +10% Mensual
Ticket Promedio Revenue/Transacciones $ $500 Semanal
Margen Revenue-Costo % 35% Mensual

Puntos clave de esta leccion

  • Los KPIs deben responder preguntas especificas del negocio
  • Centraliza los calculos en una hoja de Metricas
  • Usa nombres de rango para facilitar referencias
  • Siempre valida que las metricas sumen correctamente
  • El formato condicional comunica el estado de un vistazo
  • Documenta cada KPI con su formula, unidad y meta

En la siguiente leccion, construiremos las visualizaciones del dashboard usando estas metricas.

Ejercicio practico

  1. Crea la hoja de Metricas con todas las secciones
  2. Implementa los filtros de ano, mes y region
  3. Calcula los 6 KPIs principales de volumen
  4. Crea las tablas de analisis por region y vendedor
  5. Implementa la tabla de tendencia mensual
  6. Aplica formato condicional a las metricas de cumplimiento
  7. Valida que todas las metricas sumen correctamente

Quiz de comprension

  1. Por que es mejor usar SUMIFS en lugar de filtrar manualmente los datos?
  2. Como calculas el crecimiento Year over Year (YoY)?
  3. Cual es la ventaja de crear nombres de rango para los KPIs?
  4. Como validarias que la suma de regiones sea igual al total?
  5. Que indica un cumplimiento de meta del 85%?

¿Completaste esta lección?

Marca esta lección como completada. Tu progreso se guardará en tu navegador.

Anterior
Estructurar datos de ventas
Siguiente
Construir visualizaciones del dashboard