lunes, 6 de agosto de 2012

Calcular un aging de deuda con tablas dinámicas de Excel.

Hace bastante tiempo que no trabajamos con tablas dinámicas con nuestro Excel. Aprovechando la consulta realizada por un lector, realizaremos un ejercicio doble con tablas dinámicas, uno primero para Agrupar un campo y un segundo para generar un Campo calculado. Son tareas que ya hemos aprendido, en algún momento, pero creo conveniente recordar cómo trabajar con estas herramientas, que hacen realmente potente nuestra hoja de cálculo.
La cuestión la planteó un lector a través de un comentario:

...elaborar el análisis de vencimiento y estime la provisión de cuentas incobrables al cierre del ejercicio. Para elaborar el análisis de vencimiento usted debe clasificar los montos de las facturas en cada categoría según su vencimiento según como se indica a continuación:

Por vencer: Montos cuyo atraso (días) no supera los 30 días
1 - 30 dias: Montos cuyo atraso (días) es mayor o igual a 1 día y menor que 31 días
31 - 60 dias: Montos cuyo atraso (días) es mayor o igual a 31 días y menor que 61 días
61 - 90 dias: Montos cuyo atraso (días) es mayor o igual a 61 días y menor que 91 días
91 - > dias: Montos cuyo atraso (días) es mayor o igual a 91 días

La provisión se estima como un porcentaje del total de la cuenta según cada categoría:
Por vencer: 5%
1 - 30 dias: 10%
31 - 60 dias: 20%
61 - 90 dias: 50%
91 - > dias: 100%

Nota:
Ubique la información en dos hojas que deberá de identificar como:

Hoja1: Análisis de vencimiento
Hoja2: Provisión...

Comenzamos trabajando sobre una base de datos con información de clientes y ciertas facturas, algunas de las cuales ya están vencidas...

Calcular un aging de deuda con tablas dinámicas de Excel.


Lo primero que haremos será construir una tabla dinámica, normal, llevando al área de datos el campo 'Importe', y el campo de 'Días vencidos' al área de filas. Luego seleccionaremos algún elemento del campo a agrupar, esto es, del campo 'Días vencidos' y Agruparemos selección:

Calcular un aging de deuda con tablas dinámicas de Excel.


El resultado será la primera de las peticiones del lector, un aging de deuda, en cuatro tramos:

Calcular un aging de deuda con tablas dinámicas de Excel.


La peculiaridad, y lo interesante de este ejercicio, es observar cómo hemos delimitado por arrriba y por debajo los días vencidos en la agrupación, y por otro lado, el intervalo restante entre 0 y 90, haberlo agrupado por tramos de 30.

Para el segundo trabajo, creamos una nueva tabla dinámica, independiente de la primera (habrá un post en un futuro breve para explicar este aspecto), de igual forma que la primera, esto es, llevando al área de filas el campo 'Días vencidos' y al área de datos el campo 'Importe'. Lo especial en este caso es que debemos crear un Campo calculado, con un condicional que evalúe los días vencidos:

Calcular un aging de deuda con tablas dinámicas de Excel.


La fórmula condicional para este campo calculado que hemos llamado 'Provisión' es:
= SI('Días vencidos'<=0;Importe*5%;SI(Y('Días vencidos'>0;'Días vencidos'<=30);Importe*10%;SI(Y('Días vencidos'>30;'Días vencidos'<=60);Importe*20%;SI(Y('Días vencidos'>60;'Días vencidos'<=90);Importe*50%;Importe*100%)))). Prácticamente igual a la que he incluido , como comprobación, en la hoja de Excel: =SI(F9<0;G9*5%;SI(F9<=30;G9*10%;SI(F9<=60;G9*20%;SI(F9<=90;G9*50%;G9*100%))))

Calcular un aging de deuda con tablas dinámicas de Excel.


Podemos comprobar como la fórmula en la hoja de cálculo o la del campo calculado en la tabla dinámica me devuelve el mismo resultado.
La gran ventaja de realizar el cálculo en el interior de una tabla dinámica es que en el futuro no tendré que preocuparme de cuantos registros tenga para calcular...

Un aspecto importante de este post es adelantar la importancia de trabajar, en estos casos, con tablas dinámicas independientes, ya que si construimos ambas Tablas dinámicas sobre el mismo origen, los cambios en una se reflejan en la otra... lo que nos ocasionaría distorsiones y errores en nuestros cálculos..

4 comentarios:

  1. Necesito generar una actualización de montos impagos en base mensual desde 2003 a la fecha del cálculo. La actualización se rige por la confrontación del monto al índice de IPC al momento de la exigibilidad de la deuda con el del mes en que permanece impaga multiplicando este resultado por el interés correspondiente al lapso de meses sobre una base de 6% anual

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      un placer saludarte.
      No puedo ayudarte si no porporcionas ningún dato ni información sobre cómo tienes los datos base.
      Un cordial saludo

      Eliminar
  2. Ismael, gusto en saludarte. Quería pedirte ayuda para resolver un trabajo que no he podido solucionar. Tengo una base de datos de clientes con todas las facturas emitidas y los pagos realizados en un período (septiembre 2012/agosto 2013). Lo tengo en una tabla dinamica y por simple inspección puedo saber cual fue la mayor deuda de un cliente, considerando que los primeros meses fue acumulando ventas, pero posteriormente fue pagando y también acumulaba nuevas ventas. No he podido encontrar una forma de calcular cual fue el mayor endeudamiento acumulado, no me importa la fecha, sino que el valor para saber si este u otro cliente utilizó realmente su línea de crédito asignada, de manera de tomar la decisión de disminuir su línea para el nuevo período.

    Mi nombre es Ricardo González, mi correo es gonzalezg.ricardo@gmail.com y soy de Chile

    ResponderEliminar
    Respuestas
    1. Hola Ricardo,
      sería conveniento ver cómo distingues las Ventas de los Pagos, si como supongo tienes un campo que lo hace de alguna manera con algún texto descriptivo o código, del estilo V / P, podrías añadirlo al área de columnas de la Tabla dinámica, en un nivel superior al del Importe, así tendrías por separado Ventas y Pagos, para realizar los cálculos necesarios. Incluso podrías incluir un Elemento calculado para realizar la diferencia entre Ventas y Pagos.

      Respecto al 'endeudamiento' no tengo claro a qué te refieres o cómo lo calcularías con los datos de Ventas, salvo que por endeudamiento entiendas el saldo vivo diferencia de V - P; pero claro está, deberías tener por otro lado, apra cad cliente cuál es su línea de crédito concedida para poder compararla...

      Slds

      Eliminar