Crear una tabla de antigüedad de facturas a cobrar (ageing) en PowerPivot

Vamos a empezar una nueva línea de aprendizaje sobre el Excel PowerPivot.

En este caso diseñaremos una base de datos para llevar el control de las facturas emitidas, cobradas, y pendientes de cobrar en función de la fecha de vencimiento.

Lo que queremos obtener no es ni más ni menos que esto:

1imagendelageing

Para obtener este informe necesitaremos vincular las siguientes tablas: Movimientos bancarios, Facturas y Clientes, que previamente habremos creado en Excel.

 

2

Empecemos creando la tabla de Clientes, importantísimo crear una columna IdCliente donde le asignaremos un identificador numérico no repetitivo a cada uno de nuestros clientes, tal como se ve en la imagen de abajo (hacer click sobre la imagen para agrandar)

Los campos a incluir son muy diversos, en función de la información que queramos obtener:3tabla clientesok

Crearemos otra tabla llamada Facturas, que será la tabla “matriz” del conjunto, y donde situaremos prácticamente la totalidad de las medidas en el PowerPivot.

Importante:

1. Fijaremos un identificador numérico a las facturas, (IdFacturas), un Código de factura (nuestra enumeración, por ejemplo si nuestra serie de facturas empieza por 2013_001 añadiremos 20103001, siempre un código diferente para cada factura.

2. Incluiremos Fecha de emisión y Fecha de vencimiento

3. Añadiremos el identificador del cliente al que hemos girado la factura (IdCliente). Esta columna la relacionaremos posteriormente con la columna de idéntico nombre de la tabla Clientes

4. Importes de la factura, desglose de IGIC o IVA, retenciones, líquido, etc…

4tabla facturasok

Ya por último crearemos la tabla Movimientos Bancarios, donde añadiremos las siguientes columnas:

 

5tabla movimientos bancarios

 

Es importante saber que la columna que se relacionará aquí con la tabla Facturas es la de IDFactura.O sea, la columna IdFactura de la tabla Movimientos Bancarios estará relacionada con la columna CódigoFactura de la tabla Facturas, tal como se ve en la imagen inferior.

4_1 realcion facturas MovBancarios

Esto tiene sentido ya que al introducir los datos del banco cazaremos lo cobrado con el número de factura que cobramos, sin tener que saber que IdFactura es. Esto facilitará la labor.

Si se reciben diferentes cobros de una misma factura añadirlos igualmente, al calcular las medidas se sumarán todos los cobros obtenidos por una factura.

Acto seguido pasamos a vincular las tablas del Excel con PowerPivot, bastará con seleccionar los datos de cada hoja, incluyendo encabezados, y pulsar el botón de PowerPivot, tal como se ilustra debajo:

creartablavinculada

Una vez tengamos las tres tablas creadas, es momento de crear las medidas dentro del mismo Excel (no de la base de datos PowerPivot).

El procedimiento es en una nueva hoja, añadir dentro de la ventana de PowerPivot, “Crear Tabla dinámica”. ¡Ojo! Es dentro del complemento PowerPivot, no desde  Insertar / Tabla Dinámica.

creartabladinamica

Acto seguido vamos a empezar a crear las Medidas, que es la forma que tiene PowerPivot de llamar a unas fórmulas que pueden contener otras y que potencian las posibilidades de este software al integrar nuevas funciones.

Pulsamos “Nueva Medida”

nuevamedida

Y vamos a añadir esta serie de medidas, en las tablas correspondientes:

1. Total Cobrado

6medida totalcobrado

2. Total Facturado

 

7medida totalfacturado

 

3. Pendiente de cobro hasta 30 días (fecha de vencimiento de la factura no pagada hasta hoy, de ahí que utilicemos el comando TODAY) 8 medida pdte 30 dias

 

4. Pendiente de cobro (antigüedad de deuda de 30 a 60 días)

9 medida pendiente 3060 dias

 

Y así sucesivamente hasta llegar hasta los 365 días o con posterioridad.

Vemos a continuación como las medidas se ven reflejadas en las diferentes tablas:

– En Movimientos bancarios 10visual movim bancarios

– En Facturas 11visual movim facturas

 

Ahora sólo nos queda colocar las medidas ya creadas en los campos de valores,  y los campos Razón Social y Código de Factura como filas, dentro de la tabla dinámica. 12 segmentacion

 

Con esto ya obtenemos nuestro ageing

1imagendelageing