Controlar vencimientos de facturas y recibos, con descuento comercial

No hace mucho tiempo, vimos una aplicación en Excel, con la que podíamos controlar vencimientos de facturas y recibos. Esta aplicación estaba pensada para controlar nuestra cartera de recibos y/o facturas pendientes de cobro, y para alertarnos llegado su vencimiento.

En el artículo de hoy, hemos añadido una funcionalidad adicional a esa aplicación en Excel, que no es otra que la del control del riesgo bancario por descuento de efectos. No entraré a detallar el código fuente, pues la mayoría de los usuarios harán caso omiso del mismo, ya que lo que buscan es una aplicación que les permita gestionar de una forma medianamente decente su cartera de recibos y de efectos descontados.

Para el que no sepa que es el descuento comercial, simplemente daré unas pinceladas, para comentar que se trata de una fórmula de financiación, por la cual el acreedor de una deuda, puede anticipar el cobro de la misma, normalmente a través de una entidad financiera, y a cambio de un tipo de interés que suele estar en función del plazo de vencimiento de la deuda. Lo normal es que las empresas soliciten a estas entidades financieras, la apertura de una línea de descuento por un determinado importe, de acuerdo a sus necesidades previamente establecidas. Las entidades financieras, una vez analizada la documentación que les solicitarán a estas empresas, determinarán si es factible la concesión de esa línea de descuento, y el importe de la misma. Las condiciones de esta línea de descuento, se deberían negociar de forma individualizada con la entidad financiera.

La aplicación que os presento hoy, y que no es más que una revisión de la que vimos en su día (la he llamado versión 2.0, por darle un toque algo más formal), consta en líneas generales de las siguientes mejoras:

Añade al libro una hoja donde se incluye una relación de las entidades financieras, así como el importe de la línea de descuento de cada una de ellas. Respecto al funcionamiento de la aplicación, en lo que respeta a este límite de descuento, comentar que lo normal es que las empresas no puedan exceder de este límite, pero como no siempre es así y cada empresa es un mundo, ya que a veces las entidades te permiten exceder ese límite, esta aplicación en Excel simplemente informa del importe del riesgo que tenemos en ese momento, del límite de la línea de descuento, y de si estamos excedidos o no, y por qué importe. Será el usuario quien decida a partir de esos datos, si la entidad financiera en cuestión, nos va a aceptar o no algún efecto comercial adicional para su descuento.


En la hoja de facturas, se ha añadido una columna, para informar a través de un desplegable, de la entidad financiera en la que vamos a negociar los efectos. En el encabezado de la columna, aparece el texto "Efecto descontado en", tal y como podéis comprobar en la siguiente imagen (si no lo veis bien, cliquead en la imagen para ampliarla):


Una vez hemos seleccionado una entidad financiera por donde descontar el efecto, veremos un MsgBox, de la siguiente forma:


El MsgBox nos informará de lo siguiente:

- Entidad financiera.
- Riesgo concedido.
- Cantidad descontada (y pendiente de vencimiento).
- Exceso/defecto sobre el límite de riesgo de la póliza de descuento.

Lo podemos comprobar en el siguiente MsgBox:


Finalmente, y una vez hayamos informado de todo lo necesario para el control de nuestras facturas, pulsaremos el botón Previsión de cobros, que nos llevará a la hoja donde tenemos la previsión de cobros, mes a mes. En esta hoja, se ha añadido una tabla en la que se incluye el riesgo por descuento que tenemos en cada entidad, y el mes de vencimiento de ese riesgo. En la tabla superior, como hasta ahora, tenemos la previsión de cobros, que incluye un cambio respecto a la versión anterior de esta misma aplicación. En esa tabla de cobros, evidentemente no aparecerán todos aquellos efectos que hayan sido negociados y por tanto descontados, pues ya habrán sido cobrados (lo cual no quiere decir que el deudor haya pagado).

En la aplicación anterior que no controlaba el riesgo por descuento de efectos, y cuyo enlace incluí al principio de este artículo, solo existía una tabla en la hoja de "Previsión de cobros", pero en esta nueva versión hay dos, una tabla para los cobros pendientes, y otra para los efectos descontados (y por tanto cobrados).


Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011). Espero vuestros comentarios, para saber si os ha sido útil o no :-)

Medias ponderadas

Hoy os presento un artículo, que aunque no es estrictamente algo que podamos considerar como puramente de Excel, creo que le va a servir a muchos usuarios que utilizan esta popular hoja de cálculo. Se trata de obtener lo que se denomina media ponderada.

La Real Academia Española (RAE), define la media ponderada como el "resultado de multiplicar cada uno de los números de un conjunto por un valor particular llamado su peso, sumar las cantidades así obtenidas, y dividir esa suma por la suma de todos los pesos".

¿Está claro, verdad?. Jajajaja. Probablemente leído así a bote pronto, esa definición lo que hace es confundir más que aclarar, pero si lo vemos con un ejemplo, nos daremos cuenta que es algo bastante sencillo de entender.

Vamos a suponer que hoy día 26-01-2011, efectuamos dos inversiones, una que nos rinde el 7% de interés nominal anual, y cuyo vencimiento es el 31-12-2011, y otra que rinde el 1% de interés nominal anual, y cuyo vencimiento es dentro de 5 días, es decir, el 31-01-2011. En ambos casos, supondremos que los intereses nos los retribuyen al vencimiento de cada inversión.

Si pasáramos esta información a nuestra hoja de cálculo de Excel, nos quedaría algo así:


Si alguien nos preguntara, a qué tipo de interés medio nos retribuyen la inversión, podríamos contestarle dos cosas:

a) Al 4,00% de interés medio, pues esa cifra resulta de hacer la meda de tipos de interés: (7%+1%)/2
b) Nos faltan datos para poder dar una respuesta.

Si hemos elegido al respuesta a), sin duda estaremos cometiendo un grave error, porque entre otras cosas, las dos inversiones no tienen el mismo vencimiento. Piensa por un momento que la inversión 1 vence dentro de 339 días, es decir, todavía queda mucho, mientras que la inversión 2 está prácticamente finalizada, por lo que debemos valorar esta circunstancia, para calcular el tipo medio de interés de nuestras inversiones, pues en caso de no hacerlo, estaremos ponderando más de la cuenta el tipo de interés de la inversión 2 (la que está a punto de finalizar). Llegados a este punto, podríamos reformular nuestro esquema en Excel, de la siguiente forma:


Si nos volvieran a preguntar cual es el tipo de interés medio al que retribuyen nuestras inversiones, la respuesta que podríamos dar, sería una de estas:

a) Al 6,91% de interés medio, pues esa cifra resulta de multiplicar el 7% por los días que quedan hasta el vencimiento de esta primera inversión, es decir 339 días, multiplicando nuevamente el 1% por los días que quedan hasta el vencimiento de esta segunda inversión, es decir, 5 días, y dividiendo todo ello entre la suma de 339 y 5. Es decir, ese tipo medio del 6,91% lo obtendríamos de la siguiente fórmula:


b) Nos faltan datos para poder dar una respuesta.

Como en el caso anterior, si hubiésemos dado como respuesta la a), estaríamos cometiendo un error, pues nos falta el último dato, el importe de cada una de las inversiones (la inversión 1, y la inversión 2), y que es condición necesaria para poder determinar el tipo de interés medio al cual nos retribuyen esas inversiones. En ningún momento hemos dicho que ambas inversiones sean del mismo importe, por eso presuponer ese dato sería un error.

Vamos a suponer que la inversión 1 es de 10.000 euros, y que la inversión 2 es de 5.000 euros. Reformulemos por tanto nuestro esquema en Excel, para tenerlo todo más claro, de un vistazo:


Si ahora nos hicieran la preguntita de marras, nuestra respuesta sería que el tipo de interés medio resultante de estas inversiones, es del 6,96%, pues lo obtenemos de resolver la siguiente fórmula:


En realidad, lo que estamos haciendo, es ponderar (sopesar, o medir de forma proporcional) cada tipo de interés, con respecto al importe de cada una de las inversiones, y con respecto a la fecha de cada uno de los vencimientos. En términos matemáticos, lo que hemos hecho no es más que ponderar la media, o como reza el título de este artículo, calcular la media ponderada.

Si volvemos a mirar esta última fórmula donde obtenemos ese 6,96% de interés medio (ponderado), y releemos nuevamente la definición que da la RAE sobre la media ponderada, estoy seguro que ahora tendremos las cosas mucho más claras, y habremos entendido esta definición.

Como colofón de este artículo, vamos a suponer ahora que tenemos 20 inversiones, en lugar de 2, como era el caso que contemplábamos en el ejemplo. En ese caso, en lugar de hacer una fórmula donde el numerador y el denominador van a ser larguísimos, podemos solucionarlo muy fácilmente con el uso de la función SUMAPRODUCTO. Vamos a verlo con un ejemplo, donde podemos contemplar en la imagen, la fórmula que aparece en la barra de fórmulas:


Podemos construir dos columnas auxiliares, pare ver que efectivamente, la función SUMAPRODUCTO nos arroja el mismo resultado, que una fórmula tradicional:


En realidad, la fórmula SUMAPRODUCTO, lo que va a hacer es multiplicar cada elemento de las matrices que le indiquemos, sumando cada una de las líneas, de tal forma que si por ejemplo ponemos esto:

=SUMAPRODUCTO(C5:C24;E5:E24;F5:F24)

...le estaremos diciendo que haga esto:

=(C5*E5*F5)+(C6*E6*F6)+(C7*E7*F7)+(C8*E8*F8)+(C9*E9*F9)+(C10*E10*F10)+(C11*E11*F11)+(C12*E12*F12)+(C13*E13*F13)+(C14*E14*F14)+(C15*E15*F15)+(C16*E16*F16)+(C17*E17*F17)+(C18*E18*F18)+(C19*E19*F19)+(C20*E20*F20)+(C21*E21*F21)+(C22*E22*F22)+(C23*E23*F23)+(C24*E24*F24)

Es decir, tanto la función SUMAPRODUCTO que hemos puesto, y esta otra fórmula tradicional tan larga que acabamos de poner, hacen exactamente lo mismo.

Espero que os haya sido útil este artículo, y que lo podáis aplicar alguna vez en vuestro trabajo. Como siempre, desde aquí podéis descargar el fichero de Excel, con el ejemplo final que hemos visto en este artículo.

Calcular vencimientos

En el artículo anterior, donde os dejé una aplicación para controlar vencimientos de facturas y de recibos, más de uno os habréis dado cuenta de que en una hoja, había una columna donde informábamos del vencimiento de las facturas. Esta columna utilizaba como referencias los nombres de celdas, con lo que intentar descifrar esa fórmula podía ser un trabajo de investigación (nada difícil, pero sí, más complicado de lo normal).

Para solucionar el asunto, vamos a ver ahora en este artículo, esa misma fórmula, pero sin utilizar los nombres de celdas.

Supongamos que tenemos los siguientes datos, en las siguientes celdas:

E5: Aquí tenemos el plazo de crédito a los clientes. Puede ser 30, 45, 60, 90 o los días que hayamos acordado.

E6: Aquí tenemos la fecha de pago fija del cliente. Hay clientes que para centralizar sus pagos en una fecha determinada, suelen pagar sus facturas un único día al mes. Puede ser el día 5, el día 10, el día 15, o cualquier otro día de pago fijo que tenga el cliente. Si nuestros clientes no tienen un día de pago fijo, dejaremos esta celda vacía (sin dato).

B8: Fecha de emisión de la factura.

Si queremos calcular el vencimiento de la factura, deberemos poner esta fórmula en la celda donde queramos informar este dato:

=SI($E$6<>"";SI(DIA(B8+$E$5)>$E$6;MIN(FECHA(AÑO(C8);MES(C8)+1;DIA($E$6));FIN.MES(B8+$E$5;1));MIN(FECHA(AÑO(C8);MES(C8);DIA($E$6));FIN.MES(B8+$E$5;0)));B8+$E$5)

Podemos ver una tabla con esa información, en la siguiente imagen (clic para ampliarla):


Si os fijáis en la tabla de la imagen anterior, la segunda columna muestra el vencimiento teórico, o vencimiento inicial, con independencia de que el cliente tenga o no, un día de pago fijo. En la tercera columna, vemos ya el efecto de desplazar ese vencimiento inicial, a aquel que le correspondería realmente, teniendo en cuenta el día de pago fijo que tiene el cliente. En la cuarta columna se muestran los días de vencimiento real contados desde la fecha de emisión de la factura.

Para que nos funcione la fórmula anterior, donde aparece la función FIN.MES, debemos tener habilitadas las "Herramientas para análisis", en Excel 2003 podemos hacerlo desde el menú Herramientas, seleccionando la opción de menú Complementos. En Excel 2007 deberemos pulsar el botón de Microsoft Office (arriba a la izquierda), seleccionaremos el botón de Opciones de Excel (situado en la parte inferior de la ventana que nos aparecerá), luego seleccionaremos Complementos, y a continuación nos situaremos encima de las Herramientas para análisis. Pulsaremos sobre el botón "Ir", y habilitaremos esta opción marcando la muesca correspondiente.

Si lo que necesitamos es algo más sencillo, como simplemente sumar unos días de vencimiento a una fecha, podemos hacerlo de esta otra forma, mucho más sencilla, tal y como se muestra en la imagen, donde sumamos 30 días a la fecha de factura:


Si os fijáis, en imagen anterior podemos ver que la celda C5 tenemos como fórmula, el valor de la celda C4, y le hemos sumado 30 días. Para que en esas dos celdas los datos nos aparezcan como en la imagen anterior, simplemente deberemos darle formato personalizado a las celdas, con formato dd-mm-aaaa.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. En este fichero encontraréis una tabla con todos los días del año, y el vencimiento de las facturas en función del día de emisión de las mismas, teniendo en cuenta el plazo de crédito que concedamos al cliente, y la fecha de pago fija que tiene (si es que la tiene).

Controlar vencimientos de facturas y recibos

Si tienes cosas que hacer, mejor deja la lectura de este artículo para cuando dispongas de tiempo, porque una vez redactado todo, acabo de darme cuenta que ocupa la friolera de 16 páginas en DIN-A4. En cualquier caso, si prefieres descargarte la aplicación ahora, y leerte el resto más tarde, puedes hacerlo desde aquí: descargar la aplicación para el control de vencimientos de facturas y recibos. Eso sí, al menos léete los párrafos iniciales para saber de qué va esta aplicación en Excel, y los párrafos finales de este artículo, para saber cual es el procedimiento que debes seguir como usuario de la aplicación, para su correcto funcionamiento, y saber como debes introducir los datos, para no obtener errores inesperados.

Son muchas las pequeñas empresas, ya sean talleres, asociaciones, fundaciones, cooperativas, microempresas, e inclusos profesionales o empresarios individuales, que por su volumen de facturación y por su carga de trabajo administrativo, no requieren del uso de complejos programas para llevar el control de sus facturas, y saber cuando tienen que presentar los recibos al cobro, o cuando tienen que llamar al cliente para reclamar el pago de las facturas.

Normalmente estas aplicaciones de control de recibos, forman parte de los propios programas de contabilidad, pero como muchas de esas empresas, probablemente tengan externalizada su contabilidad, delegando este trabajo en una gestoría o asesoría fiscal y contable, he pensado que podría ser de utilidad a más de uno, esta sencilla pero útil aplicación.

Como siempre, se trata de una aplicación en Excel para el control de vencimientos de facturas y recibos, y es de libre distribución, como todo lo que puedes encontrar en este blog de Excel. Es una aplicación que no está protegida, por lo que podéis copiarla, enviársela a un amigo, o simplemente cambiar lo que os apetezca para adaptarla a vuestras necesidades. Respecto a esto último, solo quiero comentar que la aplicación va a servirle al 99,99% de los usuarios (por no decir al 100%), pues no está diseñada para un sector de actividad en concreto, de tal forma que servirá tanto para una empresa industrial como de servicios, ejerciten la actividad que ejerciten.

Voy a explicar un poco por encima, cómo debe utilizarse esta aplicación, para un correcto funcionamiento. No es nada complicado su uso, pues incluso los más vagos pueden saltarse este pequeño tutorial, que no va a tener problemas para hacerse con él, en menos de un minuto :-)

La aplicación consta de 5 hojas. Estas hojas no están ocultas, pero lo que sí que hemos hecho es ocultar las etiquetas de las hojas para que el usuario interactúe con los botones, y no a través de clics en las pestañas. Las hojas en cuestión, son las siguientes:

- Una hoja para el menú principal.

- Otra hoja para dar de alta, modificar, y ver los datos de nuestros clientes.

- Otra hoja para dar de alta, modificar, y ver las condiciones de pago (condiciones de cobro de nuestras facturas).

- Otra hoja para dar de alta, modificar, y ver las facturas que hemos emitido. Las facturas se tienen que generar con otra aplicación informática (programa de facturación), pues esta aplicación en Excel no es un programa de facturación, sino de control de vencimientos de facturas.

- Otra hoja para obtener una previsión de cobros, en función de los datos de las facturas que hemos introducido. Esta previsión de cobros, nos muestra los cobros previstos mes a mes, así como un total general por cliente.

Vamos a explicar para qué sirve cada hoja, y que es lo que encontraremos en cada una de ellas:

Menú principal:

Pocas cosas podemos decir sobre la funcionalidad de esta hoja, porque es más que evidente. Un pantallazo nos sacará de dudas:



El macro que tenemos en esta hoja (la hoja1), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de clientes:

Desde esta hoja, daremos de alta nuestros clientes, con todos los datos fiscales, y de contacto, así como su forma de pago, seleccionándola del desplegable que se genera automáticamente al dar de alta el nombre del cliente. También se generará una validación de datos automática, por lo que si el cliente tiene fecha de pago fija, solo podremos introducir un número entre el 1 y el 31.

Este sería un pantallazo con un ejemplo donde salen nuestros clientes (clic sobre la imagen, para ampliarla):



En esta hoja tenemos dos macros. Uno que se ejecuta al activarse la hoja (la hoja2), y nos sirve para proteger la hoja:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Este otro macro se ejecutará cuando cambiemos un dato de la hoja en cuestión. Si el cambio afecta a la columna A, entonces ocurrirá lo que comentaba anterior mente, es decir, se generará un desplegable de forma automática, y se generará también una validación de datos:

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'ocultamos el procedimiento Application.ScreenUpdating = False 'fichamos la celda donde estamos celda = ActiveCell.Address 'si introducimos datos en la columna A, 'entonces añadimos la validación de datos en 'las dos columnas finales If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'añadimos la lista de validación de las formas de pago Cells(Target.Row, 11).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=FPA" End With 'añadimos la lista de validación del día de pago fijo Cells(ActiveCell.Row, 12).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, Operator:=xlBetween, _ Formula1:="1", Formula2:="31" .ErrorMessage = "El día de pago debe estar entre el 1 y el 31." End With End If 'volvemos donde estábamos Range(celda).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de formas de pago:

Esta hoja nos sirve para dar de alta las diferentes formas de pago, que luego serán las que se muestren en el desplegable al dar de alta los clientes. Un ejemplo de ello es el pantallazo que os presento a continuación (clic sobre la imagen, para ampliarla):



El macro que tenemos en esta hoja con las formas de pago (la hoja3), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Facturas emitidas:

Aquí es donde iremos introduciendo las facturas de nuestra empresa. Solo tendremos que seleccionar el cliente desde el desplegable que nos aparecerá en la columna A. Este desplegable se genera automáticamente al colocarnos en una celda de esa columna, y toma los datos de la hoja de clientes (evidentemente de los clientes que hayamos introducido). Si generamos una nueva factura, antes deberemos dar de alta al cliente, y su forma de pago si es que no la tenemos ya en nuestra hoja de forma de pago.

El aspecto que presenta esta hoja de facturas es similar a la de este ejemplo (clic sobre la imagen, para ampliarla):



En el caso de haber facturas vencidas, la fila nos saldrá de color azul celeste, para tenerlas más a la vista. Solo nos quedará llamar a los clientes que no han pagado todavía, para recordarles que su factura ha vencido, y no hemos recibido el cobro. Una vez las hayamos cobrado, eliminaremos las facturas, situándonos en cualquier celda de la fila, y pulsando el botón "Eliminar fila".

Lo primero que tenemos que hacer para dar de alta un cliente, es seleccionarlo del desplegable, tal y como se muestra en este ejemplo (clic sobre la imagen, para ampliarla):



Una vez seleccionado el cliente, automáticamente nos aparecerán a la derecha una serie de datos, algunos de ellos son propuestas que evidentemente podemos modificar, como el número de factura (nos genera el siguiente nº de factura presuponiendo que el de la fila anterior es el último número de factura), y la fecha de la factura (presuponiendo que es la misma que la fecha de la fila anterior). El vencimiento evidentemente no lo debemos introducir, pues lo obtenemos automáticamente a partir de la fecha de emisión de la factura, y de las condiciones de pago (forma de pago).

Con todo ello, y siguiendo con nuestro ejemplo, al seleccionar en el desplegable, nos aparecerá algo como lo de este ejemplo (clic sobre la imagen, para ampliarla):



Los macros que nos encontraremos en esta hoja (la hoja4), son los siguientes. Uno de ellos, para proteger la hoja, en el momento de activarse:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Otro macro que se ejecuta al seleccionar una celda de la columna A, y que nos sirve para generar el desplegable con los clientes a seleccionar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si seleccionamos una celda de la columna A, 'entonces añadimos la validación de datos en 'la celda en cuestión If Not Application.Intersect(Target, Range("A:A")) _ Is Nothing And ActiveCell.Row >= 5 Then 'añadimos la lista de validación de los clientes Cells(Target.Row, 1).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=CLI" End With End If 'protegemos la hoja ActiveSheet.Protect End Sub

Y este otro macro que se ejecutará cuando cambiemos un dato de la columna A, y que nos generará todos los datos que comentábamos anteriormente (número de factura, fecha de factura, vencimiento, días hasta el vencimiento, y días de exceso sobre el vencimiento):

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si modificamos una celda de la columna A, añadimos 'las fórmulas en las columnas correspondientes If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'si no tenemos cliente o fecha de fra., borramos el vto. si 'lo hay así como los días de exceso, y los días hasta el vto. If ActiveCell = "" Then ActiveCell.Offset(0, 1) = "" ActiveCell.Offset(0, 2) = "" ActiveCell.Offset(0, 4) = "" ActiveCell.Offset(0, 5) = "" ActiveCell.Offset(0, 6) = "" End If 'si seleccionamos un cliente en el desplegable 'añadimos la fra. previsible, la fecha de fra. previsible, 'el vto., los días de exceso, y los días hasta el vto. If ActiveCell <> "" Then 'añadimos el nº de fra. previsible If ActiveCell.Offset(-1, 1) <> "" And ActiveCell.Offset(0, 1) = "" Then ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1 End If 'añadimos la fecha de fra. previsible If ActiveCell.Offset(-1, 2) <> "" And ActiveCell.Offset(0, 2) = "" Then ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) End If 'añadimos el vencimiento If ActiveCell.Offset(-1, 4) <> "" And ActiveCell.Offset(0, 4) = "" Then ActiveCell.Offset(0, 4).FormulaR1C1 = "=IF(VLOOKUP(RC[-4]," & _ "TCLI,12,0)<>"""",IF(DAY(RC[-2]+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0)" & _ ",TFPA,2,0))>VLOOKUP(RC[-4],TCLI,12,0),MIN(DATE(YEAR(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0))+1,DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP(RC[-4]," & _ "TCLI,11,0),TFPA,2,0),1)),MIN(DATE(YEAR(RC[-2]+" & _ "VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0),0))),RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0))" End If 'añadimos los días hasta el vencimiento If ActiveCell.Offset(-1, 5) <> "" And ActiveCell.Offset(0, 5) = "" Then ActiveCell.Offset(0, 5) = "=IF(TODAY()-RC[-1]<0,RC[-1]-TODAY(),0)" End If 'añadimos los días de exceso sobre el vencimiento If ActiveCell.Offset(-1, 6) <> "" And ActiveCell.Offset(0, 6) = "" Then ActiveCell.Offset(0, 6) = "=IF(TODAY()-RC[-2]>=0,TODAY()-RC[-2],0)" End If End If End If 'protegemos la hoja ActiveSheet.Protect End Sub

Previsión de cobros:

Una vez tengamos dadas de alta todas las facturas, simplemente deberemos acceder a la hoja donde se nos genera de forma automática la previsión de cobros. Para ello, pulsaremos el botón "Previsión de cobros".

Lo que obtendremos será algo similar a lo que se muestra en el siguiente ejemplo:



En esta hoja no tenemos ningún macro, pues todo el código que genera la previsión de cobros, lo tenemos en un macro dentro del Módulo1.

Vamos a entrar ahora a comentar los macros que tenemos en el Módulo1. Lo primero que hay es el macro Auto_open(), que como sabéis es el macro que se ejecuta al abrir el fichero. El código de nuestro macro Auto_open() es el siguiente (lo he modificado una vez publicado el artículo, para que se carguen automáticamente las herramientas para análisis, y no tener problemas con la función FIN.MES):

Sub Auto_open() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'activamos las herramientas para análisis, 'para no tener problemas con la función FIN.MES AddIns("Herramientas para análisis").Installed = True 'no mostramos las pestañas (las hojas) ActiveWindow.DisplayWorkbookTabs = False 'buscamos si hay alguna factura vencida, y también 'si hay facturas para vencer en los próximos 7 días Hoja4.Select Range("G5").Select 'si no hay facturas, saltamos a la línea correspondiente If ActiveCell.Offset(0, -6) = "" Then GoTo sinfacturas 'para todo el rango de datos de la columna J For i = 5 To Selection.End(xlDown).Row 'miramos si hay facturas vencidas If ActiveCell > 0 Then vencidas = vencidas + 1 'miramos si hay facturas que 'vencen en los próximos 7 días If ActiveCell.Offset(0, -1) <= 7 And _ ActiveCell.Offset(0, -1) > 0 Then proximas = proximas + 1 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'nos situamos para escribir la siguiente factura ActiveCell.Offset(0, -6).Select 'mostramos un mensaje si hay facturas vencidas If vencidas = 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " factura vencida.") _ , , "Facturas vencidas" If vencidas > 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " facturas vencidas.") _ , , "Facturas vencidas" 'mostramos un mensaje si hay facturas próximas a vencer If proximas = 1 Then MsgBox ("Hay " & proximas & " factura que vence " _ + "en los próximos 7 días."), , "Facturas para vencer" If proximas > 1 Then MsgBox ("Hay " & proximas & " facturas que vencen " _ + "en los próximos 7 días."), , "Facturas para vencer" 'nos situamos en el menú principal sinfacturas: Hoja1.Select Range("E12").Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

No comentaré mucho sobre lo que hace el macro Auto_open(), porque con leer los comentarios del propio macro, lo tenemos todo chupado. Dentro de ese código, hay dos partes importantes que sí que me gustaría recalcar. Una de ellas, es que se nos mostrará un aviso informándonos de las facturas que tenemos vencidas. Este aviso se nos mostrará con tan solo abrir el fichero, siempre y cuando tengamos facturas vencidas, claro está. En el caso de haber facturas vencidas, se nos mostrará un mensaje similar a este que os muestro a continuación, donde nos avisa que hay 9 facturas vencidas:



En el caso de tener facturas que venzan en los próximos 7 días, la aplicación también nos mostrará un aviso como el que se nos muestra a continuación, donde nos avisa que tenemos 1 factura que nos vence en los próximos 7 días:



Aparte del macro Auto_open(), tenemos estos otros macros cuyo código os incluyo a continuación. Un macro para imprimir:

Sub Imprimir() 'Imprimimos la hoja ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Otro macro para guardar el fichero pero sin cerrar la aplicación, es decir, para guardar los datos, y continuar trabajando:

Sub Guardar() 'Guardamos el libro ActiveWorkbook.Save End Sub

Otro macro para ir a la hoja de clientes:

Sub Clientes() 'Vamos a la hoja2 Hoja2.Select Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja con las formas de pago:

Sub Formas_de_pago() 'Vamos a la hoja3 Hoja3.Select Range("B5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja de facturas:

Sub Facturas() 'Vamos a la hoja4 Hoja4.Select Range("A5").Select 'nos situamos en la primera fila libre If ActiveCell.Offset(0, 1) <> "" Then Selection.End(xlDown).Offset(1, 0).Select End If End Sub

Otro macro para volver al menú principal:

Sub Volver_al_menu() 'volvemos al menú, pero dependiendo 'de la hoja donde estemos, ordenaremos 'también los datos If ActiveSheet.CodeName = "Hoja2" Then Ordenar_clientes If ActiveSheet.CodeName = "Hoja3" Then Ordenar_formas_de_pago 'volvemos al menú Hoja1.Select Range("E12").Select End Sub

Otro macro para ordenar alfabéticamente los clientes:

Sub Ordenar_clientes() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("A5:K5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A5") 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="CLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'ahora le ponemos un nombre a toda la tabla Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TCLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C12" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente los clientes.") _ , , "Clientes ordenados" End Sub

Otro macro para ordenar alfabéticamente las formas de pago:

Sub Ordenar_formas_de_pago() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("B5:C5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B5") 'seleccionamos todo el rango continuo 'desde B5 hasta abajo del todo Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C2" 'ahora le ponemos un nombre a toda la tabla Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TFPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C3" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente las formas de pago.") _ , , "Formas de pago ordenadas" End Sub

Tomad un poco de aire, que aún quedan unos cuantos macros más. El siguiente que nos encontraremos es para eliminar clientes:

Sub Eliminar_cliente() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre del cliente a buscar 'y la celda donde estamos cliente = Cells(ActiveCell.Row, 1) celda = ActiveCell.Address 'antes de borrar un cliente, buscaremos que no tengamos 'facturas emitidas a nombre de ese cliente a eliminar Hoja4.Select 'buscamos el cliente, y si existe en las facturas 'creamos una variable If Not Cells.Find(cliente) Is Nothing And cliente <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja2 Hoja2.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar este cliente, debes borrar sus facturas." _ + Chr(10) + "Por favor, accede a la hoja de Faturas, para eliminarlas.") _ , , "Imposible borrar este cliente" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Los datos de este cliente, han sido eliminados.") _ , , "Cliente eliminado" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar formas de pago:

Sub Eliminar_forma_de_pago() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre de la forma de pago a buscar 'y la celda donde estamos formadepago = Cells(ActiveCell.Row, 2) celda = ActiveCell.Address 'antes de borrar una forma de pago, buscaremos 'que no tengamos facturas emitidas a clientes 'con esa forma de pago a eliminar Hoja2.Select 'buscamos la forma de pago, y si existe en 'algún cliente, creamos una variable If Not Cells.Find(formadepago) Is Nothing And formadepago <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja3 Hoja3.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar esta forma de pago, debes borrar o cambiar la forma" _ + Chr(10) + "de pago de los clientes que utilizan esta modalidad de pago a borrar." _ + Chr(10) + Chr(10) + "Por favor, accede a la hoja de Clientes, para editarlos.") _ , , "Imposible borrar esta forma de pago" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Esta forma de pago, ha sido eliminada.") _ , , "Forma de pago eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar facturas:

Sub Eliminar_factura() 'Si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("La factura seleccionada, ha sido eliminada.") _ , , "Factura eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Y el último macro que nos encontraremos, y también el más largo, es este, que nos sirve para generar nuestra previsión de cobros, detallando los vencimientos por meses y por clientes:

Sub Prevision_de_cobros() 'Si hay errores, que continúe On Error Resume Next 'cambiamos el texto del botón de la hoja1 y hoja4 If ActiveSheet.CodeName = "Hoja1" Or _ ActiveSheet.CodeName = "Hoja4" Then 'creamos una variable If ActiveSheet.CodeName = "Hoja1" Then hoja = "menu" If ActiveSheet.CodeName = "Hoja4" Then hoja = "facturas" 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre, y lo ponemos en rojo Selection.Characters.Text = "Procesando..." With Selection.Font .ColorIndex = 3 End With End If 'ocultamos el procedimiento Application.ScreenUpdating = False 'comprobamos que tengamos facturas If Hoja4.Range("A5") = "" Then 'si no hay facturas, mostramos un mensaje MsgBox ("Por favor, revisa todo, para poder continuar.") _ + Chr(10) + Chr(10) + "Al parecer no hay facturas, y por tanto no se" _ + Chr(10) + "puede generar la previsión de cobros." _ , , "Hay errores" 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If Hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf Hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro Exit Sub End If 'fichamos la celda donde estamos celda = ActiveCell.Address 'eliminamos todo lo que haya en la hoja5 Hoja5.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'borramos las filas (los clientes) Selection.EntireRow.Delete 'borramos ahora las fechas Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select 'borramos las filas (los clientes) Selection.EntireColumn.Delete 'seleccionamos la hoja2 Hoja2.Select 'nos situamos en la primera celda con datos Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'copiamos los datos Selection.Copy 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos la hoja5 Hoja5.Select 'seleccionamos la celda A5 Range("A5").Select 'pegamos los datos Selection.PasteSpecial Paste:=xlPasteValues 'fichamos la fila máxima fila_maxima = Selection.End(xlDown).Row 'vamos a la hoja4, donde tenemos las facturas 'y seleccionamos la fecha menor y mayor Hoja4.Select 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FRAS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'seleccionamos todo el rango continuo 'desde D5 hasta abajo del todo Range("D5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="IMPORTES", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C4:R" & ActiveCell.Row & "C4" 'seleccionamos todo el rango continuo 'desde E5 hasta abajo del todo Range("E5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="VTOS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C5:R" & ActiveCell.Row & "C5" 'volvemos a E5 Range("E5").Select 'definimos dos variables (fecha mínima y máxima) minimo = ActiveCell maximo = ActiveCell Do While Not IsEmpty(ActiveCell) 'seleccionamos la fecha mínima y máxima If ActiveCell < minimo Then minimo = ActiveCell If ActiveCell > maximo Then maximo = ActiveCell 'controlamos que no falten datos: nombre del cliente, 'fecha de factura, vencimiento e importe. 'Comenzamos controlando el vencimiento If IsDate(ActiveCell) <> True Then incorrecto = True 'controlamos la fecha de la factura If IsDate(ActiveCell.Offset(0, -2)) <> True Then incorrecto = True 'controlamos el importe If Not IsNumeric(ActiveCell.Offset(0, -1)) Or _ ActiveCell.Offset(0, -1) = "" Then incorrecto = True 'controlamos que exista el cliente If ActiveCell.Offset(0, -4) = "" Then incorrecto = True 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'si hay errores, mostramos un mensaje If Err.Number <> 0 Then 'mostramos un mensaje MsgBox ("Existen errores." _ + Chr(10) + Chr(10) + "Por favor, revisa todo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'si hay errores, mostramos un mensaje If incorrecto = True Then 'mostramos un mensaje MsgBox ("Existen errores en algunas de estas columnas:" _ + Chr(10) + Chr(10) + "- Nombre de los clientes." _ + Chr(10) + "- Fecha de las facturas." _ + Chr(10) + "- Importe de las facturas." _ + Chr(10) + "- Vencimiento de las facturas." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'ahora seleccionamos el primer día del mes del mínimo y máximo minimo = "01" & "/" & Month(minimo) & "/" & Year(minimo) maximo = "01" & "/" & Month(maximo) & "/" & Year(maximo) 'recuerda que esta aplicación ha salido de 'http://hojas-de-calculo-en-excel.blogspot.com 'calculamos la diferencia en meses entre el mínimo y el máximo meses = DateDiff("m", minimo, maximo) 'si la diferencia de meses es superior a 120 'mostramos un mensaje de error If meses > 120 Then 'mostramos un mensaje MsgBox ("Hay más de 10 años de diferencia entre el " _ + Chr(10) + "primer vencimiento, y el último vencimiento." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Vencimientos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'volvemos a la hoja5 Hoja5.Select 'escribimos los meses para planificar los cobros, 'primero escribiendo los meses Range("A4").Select For i = 0 To meses 'seleccionamos la columna de la derecha ActiveCell.Offset(0, 1).Select 'escribimos el mes ActiveCell = DateAdd("m", i, minimo) Next 'escribimos el total ActiveCell.Offset(0, 1) = "TOTAL" 'fichamos la columna máxima columna_maxima = Selection.End(xlToRight).Column 'copiamos el formato de la celda A4 Range("A4").Select ActiveCell.Copy 'seleccionamos todo el rango continuo por 'la derecha, desde la segunda columna Range(Selection.Offset(0, 1), Selection.End(xlToRight)).Select 'pegamos los formatos Selection.PasteSpecial Paste:=xlPasteFormats 'ponemos los formatos de fecha (mes y año) Selection.NumberFormat = "[$-340A]mmm yyyy" 'alineamos esos encabezados a la derecha Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlRight End With 'nos situamos en la celda B5 Range("B5").Select 'escribimos la fórmula ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT((FRAS=RC1)*(MONTH(VTOS)=MONTH(R4C))*(YEAR(VTOS)=YEAR(R4C))*IMPORTES)" 'copiamos y pegamos la fórmula en toda la tabla Selection.Copy Range(Cells(5, 2), Cells(fila_maxima, columna_maxima - 1)).Select ActiveSheet.Paste 'ponemos los totales Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select ActiveCell = "TOTALES" 'pasamos a la siguiente columna ActiveCell.Offset(0, 1).Select 'escribimos las sumas totales de cada mes For i = 2 To columna_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(R[-" & fila_maxima - 4 & _ "]C:R[-1]C)" 'nos movemos a la derecha ActiveCell.Offset(0, 1).Select Next 'volvemos a la última columna, y 'seleccionamos toda la fila ActiveCell.Offset(0, -1).Select Range(Selection, Selection.End(xlToLeft)).Select 'ponemos la fila en negrita, con 'bordes, y con la trama de color amarillo Selection.Font.Bold = True With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With With Selection.Interior .ColorIndex = 36 End With 'nos vamos a la última columna, 'es decir, la de los totales Cells(5, columna_maxima).Select 'escribimos las sumas totales de cada cliente For i = 5 To fila_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(RC[-" & columna_maxima - 2 & _ "]:RC[-1])" 'nos movemos hacia abajo ActiveCell.Offset(1, 0).Select Next 'ponemos todas esas sumas de cada cliente, en negrita Range(Selection, Selection.End(xlUp)).Select Selection.Font.Bold = True 'le volvemos a poner el nombre correcto al botón boton: If hoja = "menu" Or hoja = "facturas" Then 'seleccionamos la hoja1 o la hoja4 If hoja = "menu" Then Hoja1.Select If hoja = "facturas" Then Hoja4.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro, si hay errores If Err.Number <> 0 Or incorrecto = True Or meses > 120 Then Exit Sub End If 'nos situamos en la fila con el rótulo de los 'totales, de la hoja5 Hoja5.Select Range("A5").Select Selection.End(xlDown).Select 'protegemos la hoja ActiveSheet.Protect 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Finalmente comentar que el procedimiento correcto para inicializar la aplicación sería este, y necesariamente en este orden que os incluyo a continuación, pues en caso de no seguir este procedimiento, la aplicación puede responder con errores o de forma inesperada:

1. Dar de alta las formas de pago.
2. Dar de alta los clientes.
3. Dar de alta las facturas.

A partir de ese momento, y si nuestros clientes son los que son, y no generamos nuevos clientes, tan solo tendremos que preocuparnos por introducir las facturas. Si tenemos un cliente nuevo, deberemos darlo de alta previamente, y si su forma de pago es nueva, deberemos dar de alta ésta lo primero de todo, siguiendo el esquema explicado en el párrafo anterior.

Espero que os sea de utilidad esta aplicación. Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011).

Si hacéis algún cambio a la aplicación, os ruego que no me pidáis modificaciones personalizadas para vuestro caso en concreto o el de vuestra empresa, porque mi tiempo es muy limitado, y prefiero utilizarlo en resolver cuestiones de uso común. Si por el contrario, crees que sería interesante introducir alguna mejora en la aplicación, para que sea de uso público y los demás puedan aprovechar esa utilidad, entonces gustoso intentaré darle solución, si mis limitados conocimientos me lo permiten.

Si en vuestra empresa trabajáis con descuento comercial (descuento de efectos), en este otro artículo podréis descargar la aplicación para controlar vencimientos de facturas y recibos, con descuento comercial.

Por cierto, al hilo de esta aplicación para el control de vencimientos de facturas y recibos, solo quiero recordaros a los que realizáis actividades empresariales o profesionales en territorio español, que la reciente publicación de la ley de lucha contra la morosidad comercial, solo permite un máximo de 60 días de crédito para aquellas operaciones que se realicen a partir del 1 de enero de 2013. Es decir, a partir de esa fecha, no podréis darle a vuestros clientes un plazo de pago superior a los 60 días (si los clientes tienen fecha fija de pago, deberéis tener en cuenta esta circunstancia, para adaptar las condiciones de pago, y que no exceda del límite marcado en la ley). No obstante, hasta esa fecha (1 de enero de 2013) uno no puede hacer lo que desee, ya que desde la entrada en vigor de la ley (7 de julio de 2010), existe un periodo transitorio para adaptarnos a ese máximo de 60 días, y no tener que bajar de golpe de los 180, 150, 120, 90 días, o los que vuestra empresa de a sus clientes. De esta forma, todas las empresas competirán en las mismas condiciones crediticias. Algo muy importante que como novedad incorpora la ley (en sustitución de la anterior ley antimorosidad), es que no admite el acuerdo entre las partes, para alargar los plazos de crédito a los clientes, es decir, bajo ningún concepto se podrá superar el límite de los 60 días de crédito comercial, pues no se permite que pactes alargar ese límite con tus clientes.

Préstamos según el método americano

Existe un método de amortización de préstamos, que consiste en liquidar únicamente intereses en cada cuota, excepto en la cuota final, que aparte de los intereses, también amortizaremos el préstamo, y además lo haremos de golpe. Este método de amortización de préstamos, se denomina método americano, y su uso aunque no está muy extendido, siempre puede sernos útil.

Las características básicas de este tipo de préstamos, son las siguientes:

• Tienen casi todas las cuotas constantes (excepto la última).


• El capital se amortiza en la última cuota.


• Los intereses que se pagan, son constantes en cada cuota.

Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo desde el enlace que acabo de incluir, pero es recomendable una lectura rápida, al menos para saber de qué estamos hablando.

Como en el método francés (que es el sistema habitual para el cálculo de préstamos e hipotecas), expliqué detenidamente todo el código fuente, aquí solo incluiré la parte del código que varía con respecto al resto de métodos, que no es otra que las fórmulas de la tabla de amortización que se obtiene.

Los más avispados se darán cuenta que hemos eliminado todo lo relativo a la carencia en este método de amortización de préstamos, pues implícitamente la carencia ya viene incluida en la propia metodología de cálculo del sistema americano, ya que no se amortiza el principal hasta la última cuota.

Antes os mostraré unos pantallazos, tanto del formulario de información del método americano, como del propio formulario donde introduciremos los datos necesarios para realizar los cálculos:




El código fuente correspondiente a la parte más importante (las fórmulas del préstamo americano), es este:

'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=R7C[1]" 'seguimos poniendo el capital amortizado 'en la primera cuota (que es cero) Range("F18").Select ActiveCell = 0 'seguimos poniendo los intereses pagados Range("G18").Select ActiveCell.Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'seguimos poniendo el capital amortizado acumulado Range("H18").Select ActiveCell = 0 'seguimos poniendo los intereses acumulados Range("I18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo la cuota total Range("J18").Select ActiveCell.Formula = "=RC[-4]+RC[-3]" 'seguimos poniendo el resto de datos, es decir 'el capital vivo antes del pago de cada cuota, 'el capital amortizado, los intereses, el capital 'amortizado acumulado, los intereses acumulados, 'y el importe de las cuotas Range("E18").Select For i = 1 To CuotasTotales - 1 'hasta la penúltima cuota... If i < CuotasTotales - 1 Then 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=R[-1]C" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=R[-1]C" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select 'si estamos en la última cuota... Else 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=RC[-1]" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=RC[-2]" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select End If Next

Y el resultado que obtendríamos sería algo como lo que os muestro en este ejemplo:



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

Préstamos con amortización de capital constante

Hoy utilizaremos nuestra potente hoja de cálculo Excel, para montar un sistema de amortización de préstamos, siguiendo el mismo mecanismo y la metodología que ya aplicamos en su momento para el modelo de amortización de préstamos siguiendo el sistema francés, que es el modelo estándar para el cálculo de préstamos e hipotecas. En esta ocasión, lo que haremos será calcular la amortización del préstamo, pero amortizando en cada cuota la misma cantidad de capital, es decir, en esta ocasión trabajaremos los préstamos con amortización de capital constante. Como siempre, tendremos también en cuenta la posibilidad de que exista un periodo de carencia en el que no se amorticen cuotas del principal del préstamo.

Las características básicas de este tipo de préstamos, son las siguientes:

• Los intereses se devengan al vencimiento de cada cuota.


• El capital que se amortiza es constante en cada cuota, es decir, el principal del préstamo que se va pagando, es siempre del mismo importe, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.


• Los intereses van disminuyendo y son menores en cada cuota.


• Las cuotas totales que se pagan, son variables y cada vez menores, debido a que los intereses son cada vez menores.

Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo en cualquier momento, pero es recomendable al menos, una lectura rápida por encima, para hacernos una idea y saber de que estamos hablando.
Lo primero que haremos en nuestra aplicación financiera para el cálculo de préstamos con amortización de capital constante, será construir un formulario con información sobre el préstamo, tal y como aparece en la siguiente imagen:


Para no escribir líneas de código innecesarias, solo añadiré aquí el código fuente (o la parte del código fuente) que sea diferente a la aplicación que ya vimos en su momento cuando estudiamos el método francés de amortización de préstamos.

Para lanzar el formulario con la información sobre este tipo de préstamos, tal y como muestra la imagen anterior, simplemente tendremos que añadir estas líneas de código en un módulo (al formulario le hemos puesto por nombre InfoPrestamoCapitalConstante):

Sub Prestamo_Capital_constante() 'Lanzamos el formulario con info sobre 'el préstamo con amortización de capital constante InfoPrestamoCapitalConstante.Show End Sub

Una vez hayamos lanzado ese formulario, y pulsemos el botón “Si”, nos aparecerá este segundo formulario, para informar directamente de las características del préstamo.


Para no ser excesivamente aburrido, aquí os dejo solo la parte del código que está directamente relacionada con los préstamos con amortización de capital constante, y más concretamente la parte correspondiente a las fórmulas de la tabla resultante:

'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))" 'seguimos poniendo el capital amortizado 'en la primera cuota Range("F18").Select ActiveCell.Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))" 'seguimos poniendo los intereses pagados Range("G18").Select ActiveCell.Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _ "RC[-2]*R8C6/R10C6,0))" 'seguimos poniendo el capital amortizado acumulado Range("H18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo los intereses acumulados Range("I18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo la cuota total Range("J18").Select ActiveCell.Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)" 'seguimos poniendo el resto de datos, es decir 'el capital vivo antes del pago de cada cuota, 'el capital amortizado, los intereses, el capital 'amortizado acumulado, los intereses acumulados, 'y el importe de las cuotas Range("E18").Select For i = 1 To CuotasTotales - 1 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _ "RC[-2]*R8C6/R10C6,0))" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next

Lo que obtendremos será una tabla como la que podemos ver en este ejemplo (aquí solo sale una parte de la tabla). En ella podréis ver que en cada cuota, se amortiza siempre la misma cantidad de capital (del principal del préstamo):



Llegados a este punto, solo me queda por comunicaros a todos los lectores del blog, que desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.