Hojas de cálculo en Excel - página principal

Calcular vencimientos reales de facturas

Actualmente estamos inmersos en una grave crisis que obliga a muchas empresas a controlar el crédito que les conceden a sus clientes. Como muchos de vosotros sabréis, las relaciones comerciales entre empresas, no se liquidan o pagan al contado, sino que se efectúan a un determinado vencimiento. Dependiendo del sector, y del país de que se trate, este plazo de pago puede ir desde un mínimo de 30 días (con excepción de los pagos al contado), y un máximo que podemos cifrar en torno a 180 días. Así por ejemplo si la empresa A compra productos a la empresa B, ésta última pagará su factura dentro de X días, siendo X el plazo acordado entre ambas empresas.

También es normal, que los vencimientos no se paguen el día en que se produce el vencimiento real de la factura, una vez sumados los días de crédito que nos concede el proveedor, sino que se paguen en uno o como mucho dos días de vencimiento fijos al mes. Por ejemplo, si la empresa A vende un producto el día 05-02-2009 a la empresa B, y le concede un plazo de crédito de 60 días (2 meses), lo normal es que la factura venza el día 05-04-2009, y por tanto esa fecha sea el día en el que la empresa B tiene que pagar la factura. Pero claro, con la finalidad de proceder a realizar los pagos de las facturas en una fecha determinada (por ejemplo el día 10, el día 20 de cada mes), las empresas deciden acordar el pago un día fijo al mes. Siguiendo con el caso anterior, si la empresa B realiza sus pagos el día 10 de cada mes, entonces el vencimiento teórico de la factura que vencía el día 05-04-2009, se traslada automáticamente al día 10-04-2009. En este caso en concreto, la empresa B pagará su factura al cabo de 65 días (60 + 5), contados a partir de la fecha de emisión de la misma.

Bueno, pues hoy os presento una sencilla utilidad que nos servirá para obtener el vencimiento real de una factura, en función de la fecha de emisión de la misma, del plazo de crédito concedido al cliente, y del día de pago (he supuesto que hay hasta un máximo de 2 días de pago fijos).

Los datos que deberemos rellenar, son los correspondientes a la celda D4 (el plazo de pago concedido al cliente), y la celda D6 y D7 (los días fijos de pago, aunque si solo hubiera uno, podremos incluirlo indistintamente en cualquiera de esas dos celdas). Para obtener el plazo de crédito real, he escogido un mes cualquiera que tuviese 30 días (he escogido el mes de abril), y he creado una tabla con los distintos días del mes (en este caso, del mes de abril), considerando que tales fechas eran las de emisión de las facturas. A continuación, en la columna adyacente de la derecha, he incluido el vencimiento teórico, que no es otro que la fecha de la celda anterior, más los días de crédito establecidos en la celda D4. En la columna siguiente, he incluido el vencimiento real, que contemplará la fecha fija de pago por parte de nuestro cliente, tomando para ello, los datos de la celda D6 y D7, y analizando cual de ambos (si es que hay dos) es el día más cercano a la fecha de vencimiento teórico. La siguiente y última columna, informa de los días de crédito real, entre la fecha de emisión de la factura, y el vencimiento de la misma.

En esta hoja de cálculo, obtendremos también el plazo de pago medio real (celda D8), a partir de todos los datos anteriores. Esta cifra será la que les interesará controlar, a los director financiero, tesorero, o credit manager de la empresa.



Vamos a verlo con unos ejemplos. Supongamos que le emitimos una factura a un cliente el día 08-04-2009, y que nos paga a 90 días, pero que tiene 2 días de pago fijos al mes, el 10, y el 20. En teoría, la factura vencería el 08-07-2009, pero como el pago lo efectúan solo dos días (el 10 y el 20), el día más cercano es el 10, con lo que el vencimiento real será el 10-07-2009.

Si por el contrario, tuviésemos un cliente que paga solo el día 10 de cada mes, y el plazo de crédito fuese de 90 días, entonces, en el supuesto caso de que le emitiésemos una factura el día 18-04-2009, el vencimiento teórico sería el 18-07-2009, pero claro, como hemos dicho que solo paga el día 10, entonces ese vencimiento se desplazaría hasta el día 10 siguiente, en este caso, el 10-08-2009. Como vemos, en este caso en concreto, el cliente obtiene unos bonitos días extras de crédito.

El código VBA que deberéis incluir en un módulo es este (al final de este artículo podéis descargar el fichero con el ejemplo, y con este código incluido):


Sub calcular_plazo_de_pago()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores que continúe
'on error resume next
'fichamos la celda donde estamos

celda = ActiveCell.Address
'miramos cuantos días de pago hay
'si hay dato en D7 pero no en D6, entonces lo movemos

If Range("D6") = "" And Range("D7") <> "" Then
Range("D7").Cut
Range("D6").Select
ActiveSheet.Paste
dias = 1
'les damos formato a las celdas
Range("D6:D7").Select
Selection.NumberFormat = """Día"" 0"
End If
If Range("D6") <> "" And Range("D7") = "" Then dia1 = Range("D6")
If Range("D6") <> "" And Range("D7") <> "" Then
dia1 = Range("D6")
dia2 = Range("D7")
End If
'Comenzamos poniendo los días del mes
Range("B12").Select
'ponemos el título
ActiveCell = "FECHA IMAGINARIA DE LA FACTURA"
'lo ponemos en negrita
Selection.Font.Bold = True
'bajamos una fila
ActiveCell.Offset(1, 0).Select
'empezamos a escribir los días del 1 al 30
'escogemos el mes de abril, por tener 30 días, para
'calcular medias de vto. en función de esos 30 días

For i = 1 To 30
'escribimos el día
ActiveCell = CDate(i & "/04/" & Year(Date))
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'pasamos a la columna del vto.
Range("C12").Select
'ponemos el título
ActiveCell = "VENCIMIENTO TEÓRICO"
'lo ponemos en negrita
Selection.Font.Bold = True
'bajamos una fila
ActiveCell.Offset(1, 0).Select
'le sumamos los días
dias = Range("D4")
'si el día de pago es múltiplo de 10,
'ponemos el mismo día de pago (número del día)
'y sino, el que corresponda

For i = 1 To 30
'escribimos el día
If dias Mod 10 = 0 Then
'ponemos el día que toque
ActiveCell = CDate(Day(ActiveCell.Offset(0, -1)) & "/" & 4 + (dias / 30) & "/" & Year(Date))
Else
'ponemos el nº de día fijo, con el vto. que toque
ActiveCell = ActiveCell.Offset(0, -1) + dias
End If
'le damos formato de fecha
Selection.NumberFormat = "dd/mm/yyyy"
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'Ponemos el vto. en función del día de pago
Range("D12").Select
'ponemos el título
ActiveCell = "VENCIMIENTO REAL"
'bajamos una fila
ActiveCell.Offset(1, 0).Select
'comprobamos cual es el vencimiento adecuado
'para cada día de vencimiento teórico.
'Si solo hay un vencimiento...

If dia2 = "" Then
For i = 1 To 30
If Day(ActiveCell.Offset(0, -1)) <= dia1 Then
'ponemos la fecha con el día de vto. fijo
ActiveCell = CDate(dia1 & "/" & Month(ActiveCell.Offset(0, -1)) & "/" & Year(Date))
Else
'pasamos al vto. del día, pero ya del mes siguiente
ActiveCell = CDate(dia1 & "/" & Month(ActiveCell.Offset(0, -1)) + 1 & "/" & Year(Date))
End If
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'si hay dos días de vencimiento...
Else
For i = 1 To 30
If Day(ActiveCell.Offset(0, -1)) <= dia1 Then
'ponemos la fecha con el primer día de vto. fijo
ActiveCell = CDate(dia1 & "/" & Month(ActiveCell.Offset(0, -1)) & "/" & Year(Date))
ElseIf Day(ActiveCell.Offset(0, -1)) <= dia2 Then
'pasamos al vto. del segundo día
ActiveCell = CDate(dia2 & "/" & Month(ActiveCell.Offset(0, -1)) & "/" & Year(Date))
Else
'pasamos al vto. del primer día, pero ya del mes siguiente
ActiveCell = CDate(dia1 & "/" & Month(ActiveCell.Offset(0, -1)) + 1 & "/" & Year(Date))
End If
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
End If
'Ponemos los días reales de aplazamiento
Range("E12").Select
'ponemos el título
ActiveCell = "DÍAS REALES DE PLAZO"
'lo ponemos en negrita
Selection.Font.Bold = True
'bajamos una fila
ActiveCell.Offset(1, 0).Select
For i = 1 To 30
ActiveCell = ActiveCell.Offset(0, -1) - ActiveCell.Offset(0, -3)
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'le damos formato a los días
Range("E13:E42").Select
Selection.NumberFormat = "0 ""días"""
'ponemos la media real de plazo
Range("D8") = "=AVERAGE(R[5]C[1]:R[34]C[1])"
'ponemos los días de exceso de pago,
'sobre la fecha teórica

Range("D9") = "=R[-1]C-R[-5]C"
'seleccionamos la celda donde estábamos
Range(celda).Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub


Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.



2 comentarios:

Jorge Albeiro Salazar Agudelo dijo...

He visto muchas paginas de excel en internet ya que soy docente del area.

Lo felicito totalmtne por esta pagina tan pero tan buena.

Gracias y me ha servido en su totalidad.

Nuevamente gracias y continua asi.

Javier Marco dijo...

Muchas gracias por tu comentario :-)