Percentil 90 de ventas en Excel: ejercicio práctico

Top de Productos: Identificación en Percentil 90 de Ventas en Ingeniería Industrial

Área: Logística y Gestión de Operaciones / Ingeniería Industrial

En el ámbito de la Ingeniería Industrial y la gestión de la cadena de suministro, la clasificación y priorización de inventarios y productos es una tarea fundamental para la toma de decisiones estratégicas. Identificar aquellos artículos que componen el extremo superior del rendimiento en ventas permite optimizar los niveles de stock de seguridad, enfocar los esfuerzos comerciales y aplicar estrategias diferenciadas como el análisis ABC avanzado. En este artículo se desarrolla un ejercicio práctico en Excel para automatizar la identificación de los productos situados en el percentil 90 de ventas mediante el uso de funciones estadísticas y jerárquicas.

1. Explicación de las Funciones de Excel Utilizadas

Para resolver este problema de segmentación de inventario, se emplearán tres herramientas clave dentro de Microsoft Excel, cuyas sintaxis y propósitos se detallan a continuación:

PERCENTIL.EXC(matriz; k): Devuelve el percentil exclusivo (k-ésimo percentil) de los valores en un rango, donde k se encuentra entre 0 y 1 (exclusivo). A diferencia de PERCENTIL.INC, excluye los límites extremos del conjunto de datos, lo cual es altamente recomendable en distribuciones de ventas industriales para evitar sesgos por valores atípicos absolutos.
Sintaxis: =PERCENTIL.EXC(A2:A10; 0.90)
JERARQUIA(número; referencia; [orden]): Devuelve el rango o posición de un número dentro de una lista de valores. Si el orden es 0 (o se omite), Excel asigna la jerarquía basándose en que el número mayor ocupa el primer puesto (orden descendente), ideal para clasificar volúmenes de ventas de mayor a menor.
Sintaxis: =JERARQUIA(B2; $B$2:$B$11; 0)
SI(prueba_lógica; valor_si_verdadero; valor_si_falso): Permite realizar comparaciones condicionales entre un valor y un resultado esperado. En este ejercicio se utilizará para etiquetar de forma automática como «Top 10%» a aquellos productos cuyas ventas superen o igualen el umbral calculado del percentil 90.
Sintaxis: =SI(B2 >= $F$2; "Top 10%"; "Estándar")

2. Tabla con Datos de Ejemplo

A continuación se presenta un conjunto de datos inicial que simula el registro mensual de ventas de 10 referencias de productos industriales críticos. Los datos se encuentran ubicados en el rango A1:C11 de la hoja de cálculo.

	A	B	C
1	ID Producto	Descripción	Ventas Mensuales (Unidades)
2	PRD-101	Válvula Esférica Acero 2″	1,250
3	PRD-102	Sensor de Proximidad Inductivo	450
4	PRD-103	Actuador Neumático Doble Efecto	3,100
5	PRD-104	Rodamiento de Rodillos Cónicos	890
6	PRD-105	Variador de Frecuencia 5HP	2,150
7	PRD-106	Transductor de Presión 0-10bar	670
8	PRD-107	Bomba Centrífuga Industrial 3HP	4,200
9	PRD-108	Manguera Hidráulica Alta Presión	1,800
10	PRD-109	Manómetro Digital de Precisión	950
11	PRD-110	Filtro de Aire Comprimido Principal	1,400

3. Desarrollo Paso a Paso

1 Cálculo del Umbral del Percentil 90

Ubicamos el cálculo del umbral de ventas en la celda F2, haciendo referencia al rango de ventas C2:C11.

=PERCENTIL.EXC(C2:C11; 0.90)

Resultado obtenido en la celda auxiliar:

	E	F
1	Parámetro	Valor
2	Umbral P90 Ventas	3,760.0

2 Incorporación de Jerarquía y Clasificación Top

Agregamos columnas adicionales para evaluar la posición jerárquica de cada producto y determinar su clasificación comercial. Las fórmulas se introducen desde la fila 2 hasta la 11:

Fórmula para la Jerarquía (Columna D):

=JERARQUIA(C2; $C$2:$C$11; 0)

Fórmula para la Clasificación Condicional (Columna E):

=SI(C2 >= $F$2; "Top 10%"; "Estándar")

La hoja de cálculo evoluciona mostrando la tabla final con los resultados destacados:

	A	B	C	D	E
1	ID Producto	Descripción	Ventas	Jerarquía	Clasificación
2	PRD-101	Válvula Esférica Acero 2″	1,250	7	Estándar
3	PRD-102	Sensor de Proximidad Inductivo	450	10	Estándar
4	PRD-103	Actuador Neumático Doble Efecto	3,100	3	Estándar
5	PRD-104	Rodamiento de Rodillos Cónicos	890	8	Estándar
6	PRD-105	Variador de Frecuencia 5HP	2,150	4	Estándar
7	PRD-106	Transductor de Presión 0-10bar	670	9	Estándar
8	PRD-108	Bomba Centrífuga Industrial 3HP	4,200	1	Top 10%
9	PRD-108	Manguera Hidráulica Alta Presión	1,800	5	Estándar
10	PRD-109	Manómetro Digital de Precisión	950	6	Estándar
11	PRD-110	Filtro de Aire Comprimido Principal	1,400	2	Top 10%

4. Variantes del Ejercicio

Variante A: Umbral Dinámico mediante Criterio de Familia de Producto

En entornos industriales complejos, el análisis de percentil no debe realizarse de forma global, sino segmentado por líneas de producto. Para ello, combinamos el cálculo del percentil con la función condicional avanzada SI.CONJUNTO o fórmulas matriciales con SI dentro del percentil. Si se desea evaluar únicamente los productos de la categoría «Bombas y Actuadores», la fórmula del umbral dinámico se expresa como:

=PERCENTIL.EXC(SI(B2:B11="Bombas"; C2:C11); 0.90)

Variante B: Manejo de Errores con SI.ERROR en Celdas con Datos Vacíos

Cuando los registros de inventario presentan celdas vacías o errores de lectura en los sensores de ERP, la función JERARQUIA puede arrojar errores de tipo #¡VALOR! o #N/A. Para asegurar la robustez del modelo de ingeniería, se envuelve la fórmula de jerarquía en una estructura SI.ERROR:

=SI.ERROR(JERARQUIA(C2; $C$2:$C$11; 0); "Sin Registro")

5. Ejercicio Propuesto para el Estudiante

Con el fin de consolidar los conocimientos adquiridos, se propone el siguiente desafío práctico:

Enunciado: Una planta manufacturera registra el consumo mensual de 12 materias primas estratégicas en kilogramos. Se requiere identificar cuáles de ellas se encuentran en el percentil 85 de consumo. Además, debe implementar una columna de clasificación que etiquete como «Crítico Alto» a los elementos que superen dicho percentil, «Crítico Moderado» a los que se encuentren entre el percentil 50 y el 85, y «Bajo Consumo» al resto. Utilice funciones anidadas SI, PERCENTIL.EXC y asegure la protección contra errores mediante SI.ERROR.