ANÁLISIS ABC DE VENTAS POR CLIENTES (PARETO) CON POWER PIVOT

El sociólogo y matemático italiano Vilfredo Pareto (1848-1923), fue el creador del análisis conocido como ABC aplicable a muchos estudios. Este tipo de análisis se aplica también al estudio de los clientes de una empresa y comprueba que aproximadamente el 20% de nuestros clientes habituales representan el 80% de las ventas, mientras que el otro 80% restante de nuestros clientes sólo representan el 20% de las ventas, por éste motivo también se le conoce como la Ley del 80/20.

El análisis ABC nos permitirá lo siguiente:

  • Conocer la dependencia que tenemos de nuestros clientes.
  • Analizar la importancia de cada tipo de cliente para definir nuevas estrategias de ventas.
  • Evitar la pérdida de clientes.
  • Dedicar recursos necesarios a cada tipo de cliente.

image

En ésta ocasión haremos el análisis ABC de clientes por medio de una herramienta que se está haciendo cada vez más útil en Excel, Power Pivot.

Power Pivot es un complemento de Excel para análisis de datos mediante el modelamiento de datos donde se relacionan varias tablas de diferentes orígenes para poder obtener informes de tablas dinámicas con cualquier campo de las diferentes tablas relacionadas.

Una de las características más importantes y novedosas de Power Pivot son las fórmulas avanzadas que se escriben con el lenguaje de expresiones de análisis de datos llamado DAX, lo que nos permitirá crear campos calculados con fórmulas complejas.

image

PASOS PARA HACER EL ANÁLISIS ABC MEDIANTE POWER PIVOT

  • El ejemplo que veremos es la de un libro de Excel con varias hojas cada una con una Tabla; Ventas, Cliente, Producto, Marca, Sucursal y Distrito.

image

  • Para realizar el modelamiento de datos con Power Pivot, iremos a la pestaña Datos, y hacemos click en el icono Administrar modelo de datos para habilitar el complemento.

image

  • Una vez habilitado el complemento Power Pivot, agregamos cada una de las tablas al modelamiento de datos, solo colocándonos encima de cada tabla debemos presionar el botón Agregar a Modelo de datos y veremos que ingresarán a  la interfaz de Power Pivot.

image

  • Un paso importante para poder disponer de toda la información al momento de diseñar las tablas dinámicas, es la de relacionar las tablas por medio de la Vista de Diagrama. El procedimiento es muy parecido al que se hace en cualquier motor de base de datos como Access, SQL Server Management Studio, etc. la lógica es la misma en donde siempre la relación se hace mediante un campo común entre ellas, en la mayoría de los casos el campo es un código, un Id, un número de documento o cualquier campo al que se considere la llave principal.

image

  • Entre las tablas relacionadas se observa una tabla adicional que se llama Calendario, que es necesario tenerla para hacer las segmentaciones de Fecha, trimestre, año y mes. Dicha tabla se agrega a partir del botón Tabla de fechas en la pestaña Diseño de Power Pivot.

2018-08-24

  • Como haremos un análisis de ventas por cliente nos concentraremos solo en éstas dos tablas, Tabla_Ventas y Tabla_Cliente. La Tabla de Ventas necesita un campo adicional donde tenemos que mostrar la Venta total, para eso nombraremos una columna como Venta y colocaremos la fórmula:

image

  • Por el lado de la Tabla de Clientes necesitamos totalizar las ventas por cliente y colocar campos relacionados al análisis ABC que son: Venta Acumulada, % de participación de Ventas, % Acumulado de Ventas y la clasificación ABC. Las fórmulas ingresadas son las siguientes:

[Venta]=CALCULATE(SUM(Tabla_Ventas[Venta]))

image

[Acumulado]=SUMX(FILTER(Tabla_Cliente,Tabla_Cliente[Venta]>=EARLIER(Tabla_Cliente[Venta])),Tabla_Cliente[Venta])

image

[% Total]=DIVIDE(Tabla_Cliente[Venta],SUM(Tabla_Cliente[Venta]))

image

[% Acumulado]=DIVIDE([Acumulado],SUM(Tabla_Cliente[Venta]))

image

[ABC]=SWITCH(TRUE(),[% Acumulado]<=0.8,”A”,[% Acumulado]<=0.95,”B”,”C”)

image

  • Finalizado los cálculos generamos las tablas dinámicas a partir del botón Tabla dinámica de la pestaña Inicio de Power Pivot, el procedimiento que sigue es igual a cualquier diseño de Tabla dinámica, debemos colocar los campos que necesitamos ver dentro del informe, para éste caso colocamos en filas los campos ABC y CLIENTE, y en Suma de Valores el % Total y % Acumulado.

image

  • A partir de la Tabla insertamos un gráfico que nos combine el % de Ventas como columnas y el % Acumulado de Ventas como líneas, para de ésta manera obtener el gráfico de Pareto.

image

  • Asimismo podemos diseñar el informe para medir la cantidad de clientes vs el % de ventas según el tipo de cliente ABC-

image

Entonces comprobamos la gran utilidad de Power Pivot para éste tipo de análisis, fácilmente podemos actualizar la información y obtener nuevos informes cada cierto periodo y así tomar las decisiones necesarias para controlar nuestras ventas y medir a nuestros clientes.