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.

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.

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.

• 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.

• 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.

• 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.

• 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.

• 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:

• 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]))

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

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

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

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

• 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.

• 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.

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

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.

GRÁFICOS ANIMADOS CON EXCEL

Hola a todos, siguiendo con las entradas de gráficos extraordinarios y poco comunes, en ésta oportunidad veremos como se anima un gráfico de columnas mediante una macro. Vamos a crear el movimiento de las columnas de abajo hacia arriba, una por una como si estuvieran creciendo, lo que dará una característica especial a tus reportes y sorprenderá a más de uno.

Los pasos a seguir son los siguientes:

1.  Se crea la tabla de ventas o valores a graficar, como celda opcional está la del valor de la velocidad.

2.  En el Editor de Visual Basic se inserta un Módulo y un procedimiento, debemos dimensionar una variable matricial de 12 elementos que represente los valores de los 12 meses y una variable que es la velocidad de crecimiento de las columnas. El primer For es para la lectura de los datos,  el segundo For es para colocar valores ceros como valores iniciales a las ventas y el tercer For es para ir incrementando en una fracción de la venta al valor inicial hasta llegar al valor original de la venta, dicha fracción es la división de la venta original entre el valor de la velocidad-

3.  Se crea el gráfico de columnas, seleccionado el área de la tabla  y luego insertando el gráfico respectivo. Es necesario colocar una escala fija para el eje vertical del gráfico.

4.  Se debe colocar un código que permita ver el cambio en el valor de las ventas y el crecimiento paulatino de las columnas. Se coloca dentro del objeto Hoja1.

5.  Finalmente se coloca un botón y se le asigna la macro creada. El resultado es el siguiente:

 

DESCARGUE EL ARCHIVO AQUÍ

SIMULADOR FINANCIERO – CRONOGRAMA DE PAGOS EN EXCEL

Nuevamente con ustedes después de varias entradas de gráficos interesantes, ahora traigo una aplicación hecha con fórmulas y funciones de Excel, también aplicando algo de programación para ajustar algunas cifras, se trata de un Cronograma de pagos, el cual se usa para simular créditos bancarios personales, vehiculares e hipotecarios.

Las características principales de éste simulador financiero son las siguientes:

• Método: de Cuotas Iguales – Sistema Francés.
• Formas de Pago: Quincenal, Mensual, Bimestral, Trimestral, Semestral y Anual.
• Cuotas extras: Julio y/o Diciembre – Otras fechas
• Tipos de cuotas extras: Simple, Doble, Triple, Cuadruple y Quintuple.
• Comisiones y Gastos: Comisiones, Portes, Gastos Administrativos y Seguro de desgravamen.

Adicionalmente tienen un cuadro de OBSERVACIONES, que muestran algunos comentarios sobre falta de algunos datos importantes para el cálculo, también un cuadro llamado AJUSTE DE CUOTAS, en donde se verá comentarios sobre si es necesario ajustar los cálculos cuando se trabaje con cuotas extras.

Para el ajuste del cálculo de cuotas se ha creado el botón AJUSTAR, se debe presionar y automáticamente se ajustarán las cifras,

Este modelo esta diseñado muy parecido  a los cronogramas de pago que dan los bancos al momento de pedir un crédito, servirá para simular la variación de la cuota al varias el número de periodos, el tipo de amortización y si se consideran pagos extras en algunas fechas. La TCEA (Tasa de Costo Efectiva Anual) nos servirá para saber cual es la tasa real que estamos pagando, ya que dicha tasa es el resumen de los intereses, gastos y comisiones que son usados para calcular la Cuota de pago mensual.

Aquí algunos conceptos financieros importantes relacionados al Cronograma de Pagos:

1. Tasa de interés efectiva: la tasa de interés efectiva es la que realmente se paga o se obtiene durante un período de liquidación de intereses. Si se trata de varios períodos de liquidación de intereses, es necesario suponer que estos se capitalizan en cada período.
2. Número de Periodos: El número de periodos en un año puede ser menor a un año, es decir días, meses, trimestres, semestres, bimestres, o periodos bimensuales.
3. Número de periodos en un año: Normalmente en un año podemos tener los siguientes períodos:
   Diarios………………360 (Días comerciales) o 365 (Días Calendario)
   Mensuales……………12
   Bimestrales……………6
   Trimestrales…………..4
   Semestrales……………2
   Bimensuales………,,,,24 (Son periodos quincenales, no confundir con Bimestrales)
4. Capitalización Compuesta: Incorporación al capital inicial de una inversión de los intereses generados por la misma. Produciéndose de esta forma en el periodo siguiente intereses sobre este nuevo capital acumulado.
5. Anualidades: Una anualidad es un flujo de caja en el que los flujos de dinero son uniformes (es decir, todos los flujos de dinero son iguales) y los movimientos de dinero ocurren a un intervalo regular. Los flujos de dinero de la anualidad son los pagos de la anualidad o simplemente pagos. El nombre de anualidad es utilizado como una generalización sobre el tema, no siempre son períodos anuales de pago.
6. Amortización: En términos generales, amortización es cualquier modalidad de pago o extinción de una deuda. Aquí haremos referencia a la más común de estas modalidades. La extinción de una deuda mediante un conjunto de pagos de igual valor en intervalos regulares de tiempo. En otras palabras, este método de extinguir una deuda tiene la misma naturaleza financiera que las anualidades. Los problemas de amortización de deudas representan la aplicación práctica del concepto de anualidad.
7. Tasa Interna de retorno (TIR) Se define como una tasa inter temporal a la cual los ingresos netos del proyecto apenas cubren las inversiones y sus costos de oportunidad. Es la tasa de interés que hace al VPN del proyecto igual a cero, es decir, que el proyecto sea apenas aceptable. La TIR supone que los flujos del proyecto son reinvertidos, por lo tanto, mide la rentabilidad del dinero mantenido dentro del proyecto.
8. Tasa de Costo Efectiva Anual (TCEA) Esta tasa está compuesta de la Tasa Efectiva Anual “TEA”, el seguro de desgravamen, cargos mensuales y un cargo cobrado por única vez, dependiendo de la entidad financiera. Es importante recalcar que las entidades cuentan con un rango de TEA mínimo y un TEA máximo y el otorgamiento de la misma al usuario, se dará dependiendo de la calificación que reciba por parte de la entidad financiera.

Espero sus comentarios y/o consultas, hasta una nueva entrada.

CRONOGRAMA DE PAGOS EN EXCEL

FORMATO CONDICIONAL EN UN GRÁFICO CIRCULAR MEDIANTE UNA MACRO

Muchas veces tenemos la necesidad de condicionar los colores de las columnas de un gráfico según el valor que tiene, por ejemplo columnas de color azul para valores positivos y columnas de color rojo para valores negativos , así mismo pasa para los gráficos circulares, en donde un grupo de partes se deben representar mediante un color y otro grupo por otro color, esto dependiendo de que cada grupo pertenezca a una categoría diferente, por ejemplo vemos los gráficos siguientes:

En el primer gráfico se observa que hay dos partes, Renta Fija y Renta Variable, en el segundo gráfico están los productos que pertenecen a cada categoría, pero observamos que los colores de cada categoría coincide con los colores de sus productos. Cuando se diseña el segundo gráfico, Excel le da colores diferentes a cada parte, el fin de esta entrada es que con una macro condicionemos los colores de cada parte al tipo de producto para que coincida con los colores del primer gráfico.

Las Tablas deben estar ordenadas según éste ejemplo, de la siguiente manera:

La macro que sirve para el cambio de colores del segundo gráfico es el siguiente:

La macro primero pide que se seleccione el gráfico al que se quiere cambiar el color de las partes, el código usado es para gráficos y sus puntos, dice que para cada punto de acuerdo a sus categorías que están en la Tabla de Distribución de Productos se le dará un color específico. La función RGB ida el color de acuerdo a sus índices. Finalmente creamos un botón en la hoja de cálculo y asignamos dicha macro.

Aquí una muestra de como funciona:

Cambia color a gráfico.xlsm

CREAR UNA PRESENTACIÓN POWERPOINT CON VBA EN EXCEL

Para hacer una presentación en público generalmente utilizamos PowerPoint por ser la mejor herramienta en cuanto a presentaciones. Sin embargo el proceso de copiar y pegar los gráficos de Excel a una presentación PowerPoint puede ser tedioso y repetitivo.

En ésta entrada veremos como llevar todos los gráficos contenidos en un libro de Excel hacia una presentación PowerPoint con solo ejecutar una macro.

Para eso tenemos que seguir los siguientes pasos:

1. Librería de Objetos PowerPoint: Para poder utilizar las instrucciones VBA que nos permiten interactuar con PowerPoint es necesario agregar una referencia hacia la Librería de objetos de PowerPoint. Para hacerlo abrimos el Editor de Visual Basic y vamos a la opción Herramientas > Referencias y se mostrará el cuadro de diálogo Referencias, se busca la librería llamada Microsoft PowerPoint Object Library.

2. Macro para crear archivo PowerPoint: El primer paso en la creación de la macro será insertar un nuevo módulo de código para colocar un subprograma llamado copia_grafico_PowerPoint. La macro con la explicación en los comentarios de color verde, es la siguiente:

3. Ejecutar la Macro: Se inserta un botón en la hoja donde se encuentran los gráficos y se le asigna la macro creada. presionando el botón se ejecutará el procedimiento con el cual se copiarán todos los gráficos de la hoja actual en la nueva presentación que se guardará en la carpeta donde está ubicado el libro.

Aquí un enlace con un ejemplo de dicha macro:

De excel a PowerPoint

CREAR UN GRÁFICO DE DISPERSIÓN PARA UBICAR CIUDADES EN UN MAPAMUNDI

Hola a todos nuevamente, siguiendo con los gráficos en Excel pero con aplicaciones distintas a las ya conocidas, hoy les traigo una forma de ubicar ciudades en un Mapamundi, tan sólo teniendo ciertas coordenadas que vamos a ubicar en un gráfico de dispersión.

Para la construcción del gráfico hacemos lo siguiente:

1. Insertamos un Combobox, que es un control ActiveX que se encuentra en la ficha Desarrollador y en Insertar escogemos el cuadro combinado, luego en propiedades le asignamos una lista de ciudades y una celda vinculada. El rango de ciudades y la celda vinculada se encuentran en la hoja de Cálculos.

2. Luego insertamos la imagen del mapamundi en formato png, el titulo con el nombre de la ciudad, las temperaturas en °C y °F y la flecha que indica si es una temperatura alta o baja. Los tres últimos son cuadros de texto pero que están referenciados con sus valores respectivos de las celdas que se encuentran en la hoja Cálculos. Luego se dibuja un rectángulo con bordes redondeados para el fondo. Todas estas formas se agrupan para darle un aspecto de unidad.

3. Ahora construimos el gráfico de dispersión en base a los datos de la hoja Cálculos. Se tratan de dos series iguales en valores pero diferente en la forma del marcador, uno es un punto y el otro es un circulo. En la hoja Cálculos veremos que los valores que tomaremos para el gráfico y para las formas están referenciados con la lista de ciudades mediante la función BuscarV.

Para el Punto tomamos los siguientes Parámetros para la Serie Punto:

Para el Circulo tomamos los siguientes Parámetros para la Serie Circulo.

Obtenemos el gráfico siguiente, por supuesto después de haberle quitado todos los demás elementos ( leyenda, titulo, cuadrícula, ejes,  bordes, relleno, etc)

Finalmente lo agrupamos al objeto diseñado antes y queda lo siguiente:

Les dejo el enlace para que puedan verlo a detalle. Gracias y espero comentarios.

Gráfico de Dispersión para ubicar ciudades en un mapamundi

CREAR UN GRÁFICO TIPO SEMÁFORO PARA MEDIR LAS VENTAS DE UNA EMPRESA

Hola a todos, siguiendo con los temas de Gráficos, ahora hablaré sobre el gráfico tipo SEMÁFORO para mostrar los valores de ventas de una empresa. También pueden medirse indicadores, así como hemos estado viendo con los gráficos de entradas anteriores. Este tipo de gráfico es uno de los más comunes para medir valores, ya que se aplica una herramienta de Excel muy importante e indispensable para la creación de Dashboards, se trata del Formato Condicional.

Para este caso se ha usado el formato condicional de Conjunto de iconos, donde por cada producto se han creado tres reglas diferentes, en base al valor de venta y a los objetivos que hay que alcanzar. En la imagen siguiente se ven las flechas azules que nos indican que los valores del primer semáforo son los de la venta del producto 1, así mismo es para los demás semáforos.

Por ejemplo para el primer semáforo  las reglas para el formato condicional son las siguientes:

Las reglas se ubican en la columna H y los colores por cada fila son verde-negro-negro, negro-amarillo-negro y negro-negro-rojo. Por ejemplo para la combinación del verde es:

La lógica es la misma para los demás colores de cada uno de los semáforos. Cabe destacar que para que solo se vean los círculos y no los valores, hay que marcar donde dice “Mostrar icono únicamente”. El diseño del semáforo ya depende del gusto de cada uno, para éste caso se ha usado color de relleno y bordes de celdas. Para ver como funciona de acuerdo a valores aleatorios de ventas, siempre hay que presionar la tecla F9.

Muy bien, aprovechen estos gráficos y hasta una próxima entrada.

Aquí el enlace al Excel: Gráfico Tipo semáforo