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:
Comenzamos trabajando sobre una base de datos con información de clientes y ciertas facturas, algunas de las cuales ya están vencidas...
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:
El resultado será la primera de las peticiones del lector, un aging de deuda, en cuatro tramos:
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:
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%))))
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..
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...
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:
El resultado será la primera de las peticiones del lector, un aging de deuda, en cuatro tramos:
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:
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%))))
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..
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
ResponderEliminarHola, que tal?
Eliminarun placer saludarte.
No puedo ayudarte si no porporcionas ningún dato ni información sobre cómo tienes los datos base.
Un cordial saludo
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.
ResponderEliminarMi nombre es Ricardo González, mi correo es gonzalezg.ricardo@gmail.com y soy de Chile
Hola Ricardo,
Eliminarserí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
Hola!
ResponderEliminarAlguien quien me pueda ayudara con un excel que me permita controlar las cobranzas por tramos 30 60 90 ....
Gracias!
Hola Marco,
Eliminaresto es precisamente lo que se explica en el post...
La duda??
Saludos