En una empresa cuyos clientes pagan cuotas durante diversos periodos, se producen retrasos en el cobro de dichas cuotas. Para el control de las primas atrasadas, se creó una hoja a medida, que está preparada para trabajar con las cuotas atrasadas, de hasta cinco años. El objetivo es que el programa calcule el importe medio de las últimas diez semanas, para cada delegación de la empresa, a partir de una fecha que ha de determinar el usuario.
La dificultad de esta hoja está en los calendarios que se han de establecer, ya que la plantilla permite recoger los importes atrasados de las diversas delegaciones que tiene la empresa, por semanas. Además, una vez terminado el año, se han de arrastrar los importes pendientes, del año anterior, por este motivo, el libro Excel tiene hasta cinco años separados en hojas diferentes. Es decir, la composición del libro es de una hoja para establecer los calendarios y de cinco hojas más, para introducir los importes por delegación y por semanas, de cada año. A continuación explicamos con más detalle la composición y funcionamiento de la plantilla.
El planteamiento del libro Excel está basado en semanas. Esto quiere decir, que para cada año, necesitamos saber si hay 52 o 53 semanas. Por este motivo, inicialmente, el libro tiene una hoja con los calendarios de cinco años. Estos calendarios nos permitirán saber en qué semana cae cualquier fecha.
La primera hoja establece los calendarios de los cinco años que contiene la plantilla. Las fórmulas están montadas de tal manera, que el usuario solo debe introducir el año. A continuación, las fórmulas plasman el número del día por mes y por día de la semana, agrupando además, por semanas y mostrando el número de la semana. Mostramos una imagen:
En las hojas correspondientes a cada año, disponemos una tabla con la lista de todas las delegaciones de la empresa, a lo largo de una columna. Los números de las semanas se distribuyen a lo largo de una fila. Los datos que introduce el usuario, son la suma de los importes pendientes, de las cuotas de cada delegación, para cada semana. Lo interesante de estas hojas, está al final del cuadro, donde las fórmulas calculan si hay 52 o 53 semanas, a fin de que el operador sepa cuando debe parar de introducir importes. Y en la última columna, se calcula el promedio de los importes de las diez últimas semanas, basándonos en una fecha que ha de introducir el usuario. Las fórmulas calculan el número de la semana que corresponde a esa fecha y hacen el promedio de las 10 semanas anteriores. Esto permite saber cual es el promedio de deuda, en cualquier fecha del año y de las respectivas diez semanas anteriores. El cálculo se complica en los años sucesivos, porque se tienen en cuenta las diez últimas semanas correlativas, es decir, si la fecha que se introduce, para obtener el promedio, es en enero, se deberán tener en cuenta las semanas del año anterior, para completar las diez semanas de la media. Mostramos una imagen:
Las formulas utilizadas en este libro de cálculo son de Fecha y Hora, de Matemáticas, de Texto, de Búsqueda y Referencia, y de Lógica.
Aplicación realizada con Excel. Para utilizar esta aplicación es necesario disponer del programa MS Excel. La aplicación está realizada en idioma Catalán. Se puede traducir y adaptar. Nota: Los datos insertados en las imágenes son ficticios.
