#nominaenexcel #nominadepago #nominadepagoenexcel #formatodenominaenexcel #basededatosenexcel #roldepagosiess #roldepagos #roldepagosexcel #descargarbasededatosenexcel
Hola emprendedores y entusiastas productivos. En este artículo vamos a realizar un formulario con VBA que calcule de forma automática los valores monetarios para el rol de pagos de la nómina de trabajadores. Pero primero, observemos la definición de este concepto.
Nómina o rol de pagos
Según Pérez J. & María M. (2016), editores del portal web Definición.DE es aquel registro que lleva una compañía para detallar los pagos y descuentos que se hacen al trabajador durante un periodo de tiempo (lo habitual es mensual). Entonces, se estructuran por un lado los ingresos, y por otro lado los descuentos.
Luego de este rol de pagos "general" existe uno particular o individual, el cual se imprime para dejar constancia del pago al trabajador.
Estructura del rol de pagos
Como se observó anteriormente en la teoría, el rol de pagos presenta una estructura que tiene dos divisiones: Ingresos y descuentos. Por lo tanto, podemos detallar los mismos sin que estén supeditados o puedan estar limitados a:
Ingresos
* Sueldo o salario.
* Bono únicos.
* Horas extraordinarias y/o suplementarias.
* Viáticos.
* Fondos de reserva.
* Comisiones.
* Provisiones extra I (ejemplo en Ecuador: décimo tercer sueldo).
* Provisiones extra II (ejemplo en Ecuador: décimo cuarto sueldo).
Descuentos
* Seguro social.
* Impuesto sobre la renta.
* Préstamo compañía.
* Anticipos.
* Multas por atrasos injustificados.
* Multas por faltas injustificadas.
* Otras multas.
Bien, con la estructura definida, vamos a profundizar en los campos que hayamos establecido para integrarlos y generar un cálculo automático del rol de pagos. Entonces vamos a ello:
INGRESOS
* Sueldo o salario.
Según Pérez J. & María M. (2016), editores del portal web Definición.DE , es aquella remuneración que recibe el trabajador según lo establecido por la ley o por un acuerdo con el empleador.
- Para generar este dato, se puede establecer un campo dentro de un formulario, para observar el procedimiento completo, se puede ir al siguiente enlace: Base de datos en Excel. Nómina.
* Bonos.
Aquel pago que se realiza una vez, o aquel pago que se genera por cumplir con ciertos objetivos de productividad.
- Para generar este dato, ya que es único y subjetivo, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Horas extraordinarias y/o suplementarias.
Según Pérez J. (2013), editor del portal web Definición.DE es aquel tiempo adicional, fuera del horario normal, que dedica el trabajador para ejercer una actividad laboral.
- Para generar este dato, se puede establecer un formulario completo, para observar el procedimiento detallado, se puede ir al siguiente enlace: Cálculo de horas extraordinarias de forma automática en Excel y VBA.
* Viáticos.
Según Pérez J. & María M. (2016), editores del portal web Definición.DE, es aquel dinero que una empresa entrega a un trabajador(a) para sustento de un viaje. En este sentido, el dinero puede entregarse antes o después del viaje.
- Para generar este dato, ya que puede ser subjetivo a las diversas situaciones, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Fondos de reserva.
Representa aquel beneficio que tienen los trabajadores al acumular años de trabajo. En el sentido particular, de Ecuador; de acuerdo con Álvarez V. & Cazorla A. (2012), representa el 8.33% sobre la remuneración dada en el periodo (ej: mes), que se paga después de 1 año de servicio.
- Para generar este dato, se debe realizar dos pasos:
1. Habilitar al trabajador(a) para recibir el pago por concepto de fondos de reserva. Para la legislación ecuatoriana, este se recibe una vez cumplido 1 año de servicio en la empresa. Para ello, se puede establecer una fórmula sencilla que puede ser contenida en código VBA a través del formulario de nómina; si se desea realizar este formulario se puede ir al enlace descrito en "Sueldo o salario" de este apartado. Observemos la fórmula:
Datos:
* fechaentrada: corresponde a la fecha de entrada del trabajador.
=SI(SIFECHA(fechaentrada;HOY();"M")>=12;"FONDOS DE RESERVA ACTIVADOS";"AÚN FALTA")
2. Calcular los fondos de reserva por una simple fórmula, que igualmente puede ser contenida en código VBA en el formulario de nómina. Observemos la fórmula:
Datos:
*trabajador: Aquel trabajador que se busca para determinar si tiene los fondos de reserva activados.
* Nomina: matriz de la nómina de trabajadores.
*fr: porcentaje de fondos de reserva, en este caso, 8.33%.
*sb: sueldo base.
=SI(BUSCARV(trabajador;Nomina;25;FALSO)="FONDOS DE RESERVA ACTIVADOS";fr*sb;"SIN FONDO")
* Comisiones.
Aquel pago que se realiza por cumplir con ciertos objetivos de ventas.
- Para generar este dato, ya que puede ser subjetivo a las diversas situaciones, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Provisiones: Décimo tercer sueldo.
En el caso particular de Ecuador, es también llamado bono navideño, pues debe pagarse hasta el 24 de diciembre. Y, de acuerdo con Álvarez V. & Cazorla A. (2012), "es la suma de todos los valores percibidos durante el año, esto es, remuneraciones mensuales, horas suplementarias y extraordinarias, comisiones, viáticos y cualquier otra retribución dividido para 12".
- Para generar este dato, se puede establecer una fórmula para realizar el cálculo de forma automática dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Provisiones: Décimo cuarto sueldo.
En el caso particular de Ecuador, es también llamado bono escolar, pues debe pagarse en el diferente régimen escolar del territorio ecuatoriano, las cuales comprenden dos:
Región Costa - Insular
De Marzo 1 del año anterior a Febrero 28 del siguiente año. Con una fecha máxima de pago: del 15 de marzo.
Región Sierra - Oriente
De Agosto 1 del año anterior a Julio 31 del siguiente año. Con una fecha máxima de pago: del 15 de agosto.
Y, de acuerdo con Álvarez V. & Cazorla A. (2012), es el salario básico unificado (SBU) vigente dividido para 12, multiplicado por los meses trabajados dentro del periodo de cálculo. Entonces, si un trabajador que labora más de un año en la empresa, recibirá cada año de trabajo un SBU.
- Para generar este dato, se puede establecer una fórmula para realizar el cálculo de forma automática dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
DESCUENTOS
* Seguro social (IESS).
Según Pérez J. & María M. (2008), editores del portal web Definición.DE, es aquel programa social administrado por el gobierno, cuya función es resguardar a los trabajadores por los imprevistos del trabajo, enfermedades, muerte u otros. El seguro social, es un derecho de carácter irrenunciable y obligatorio de generar para las empresas privadas.
El cálculo del mismo es el siguiente, tanto para empleado y empleador:
Seguro social individual
- Se debe sumar todos los ingresos que el trabajador tiene (sueldo, horas extras, los bonos y comisiones) y se multiplica por 9.35%, que corresponde al seguro social para el empleado(a).
Seguro social patronal
- Se debe sumar todos los ingresos que el trabajador tiene (sueldo, horas extras, los bonos y comisiones) y se multiplica por 11.15%, que corresponde al seguro social para el empleador(a).
- Para establecer este dato, se puede desarrollar una fórmula para realizar el cálculo de forma automática dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Impuesto a la renta.
Según la página web Esfbs.com, es aquel cargo anual sobre las ganancias. Existen dos tipos:
1. Impuesto a la renta personal, aplicado a los ingresos individuales.
2. Impuesto a la renta corporativa, aplicado a los ingresos de las organizaciones.
En este artículo, se hablará exclusivamente sobre el impuesto a la renta personal, por lo tanto, observemos su definición.
Impuesto a la renta personal
Según la página web Esfbs.com, es aquel impuesto que se aplica al ingreso total de una persona por el periodo de un año, el mismo que es regulado por el gobierno.
En Ecuador el cálculo es como sigue:
- Determinar el ingreso total (base imponible mensual), que es aquel líquido total percibido por el trabajador menos todos los descuentos que hayan sido imputados.
- Determinar la base imponible proyectada, que es aquella que resulta de multiplicar la base imponible mensual x 12.
- Establecer el impuesto a la renta a través de una tabla dada por el SRI.
- Para establecer este dato, se puede desarrollar una tabla, dentro de una hoja en Excel que calcule de forma inmediata el impuesto a la renta, y cuyo procedimiento pueda ejecutarse mediante código VBA dentro del mismo formulario de rol de pagos, el cual se observará posteriormente. De momento, vamos a establecer una tabla hecha en Excel para realizar este cálculo, como se observa a continuación en la siguiente figura:
Por medio de esta tabla, se puede determinar rápidamente el cálculo del impuesto a la renta. Basta con digitar el ingreso percibido mensualmente, y los descuentos. Si se desea descargar este libro, basta con ir al siguiente enlace: Cálculo del impuesto a la renta en Excel.
* Préstamos.
Aquella operación en la cual la empresa procede a prestar dinero en beneficio del trabajador(a) y bajo un contrato, en la que se menciona las características del mismo.
- Para generar este dato, ya que puede ser subjetivo a las diversas situaciones, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Anticipos.
Aquella operación en que la empresa adelanta dinero en beneficio del trabajador(a), a petición de este.
- Para generar este dato, ya que puede ser subjetivo a las diversas situaciones, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
* Multas por atrasos y faltas injustificadas.
Son aquellos descuentos que se realiza al trabajador básicamente por concepto de irresponsabilidad, aunque también exista la posibilidad del descuento, resultado de un conflicto mal intencionado por parte del empleador.
- Para generar este dato, se puede establecer un procedimiento completo, el mismo que puede estar asociado o integrado a un formulario de control de asistencia, para observar el procedimiento detallado, se puede ir al siguiente enlace: Control de asistencia, multas y justificaciones en Excel. En los puntos 4, 5 y 6 se detalla el proceso de multas y su generación automática por medio de VBA.
* Otras multas.
Son aquellos descuentos que se ejecutan de forma esporádica y por diferentes eventos que perjudican directamente al trabajador, y que, generalmente debe ser por una falta del mismo.
- Para generar este dato, ya que puede ser subjetivo a las diversas situaciones, se puede establecer un campo dentro del mismo formulario de rol de pagos, el cual se observará posteriormente.
Con esta introducción, vamos a realizar un procedimiento para generar un cálculo automático para los valores del rol de pagos o nómina. Entonces, vamos con ello:
1. Creación de formulario
Vamos ahora con la elaboración de un formulario para asignar los diferentes campos del formulario de rol de pagos, como se observa en la gráfica siguiente:
Si aún no conoces cómo construir un formulario con VBA puedes seguir el enlace a continuación: Base de datos en Excel y VBA ; en el punto número 3 se muestra el proceso detallado para construir un formulario de manera rápida y sencilla.
Bien, vamos a detallar el formulario que se ha desarrollado:
* Fecha inicio y fin: Se coloca las fechas que van a delimitar el periodo de cálculo.
* Nombres: Es una lista desplegable donde se encuentran los nombres de la plantilla de nómina, la misma puede establecerse a través de un rango dinámico, si deseas observar la ejecución detallada de este proceso, puedes ir al siguiente enlace: Rango dinámico y formularios en Excel y VBA.
* Sueldo base: Aquel sueldo definido entre ambas partes (empleador y empleado), cuyo dato que se si se encontrase en una matriz dentro del libro de Excel, se podría buscar de forma sencilla, una de las formas de hacerlo es mediante código VBA, como sigue a continuación:
- Hoja donde se encuentra la matriz de búsqueda: BD.
- Matriz de búsqueda: nomina.
- Nombre de la nómina (objeto a buscar): ComboBox1.
- Columna sueldo base dentro de la matriz: Columna número 20.
Private Sub ComboBox1_Change()
' Copiar dato de sueldo base
ComboBox1.Text = ComboBox1.Value
Set busco = Sheets("BD").Range("nomina").Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
sueldo.Text = busco.Offset(0, 20)
End Sub
* Cargo: Puesto asignado a ese trabajador; valor que puede encontrarse dentro de la matriz de nómina, y puede extraerse mediante el mismo código anteriormente expuesto, obviamente, con el detalle de modificar la columna que se desea buscar.
*F.R.: Aquellos fondos de reserva, valor que puede extraerse de la misma forma expuesta con anterioridad.
* Faltas: Las faltas, o inasistencia que ha incurrido el trabajador durante el periodo, y si estas se encuentran en una base de datos, pueden ser fácilmente extraíbles por el método anterior.
*Ingresos: Campos subjetivos, y que por su naturaleza deben anotarse manualmente.
*Descuentos: Campos subjetivos, y que por su naturaleza deben anotarse manualmente.
2. Codificación VBA
Con los datos precedentes, previamente organizados y dispuestos en el libro de Excel, es posible determinar de manera automática los valores de rol de pagos para el personal; basta con ingeniarse una manera y convertirla en un proceso automático a través de código VBA.
Los formularios deben contener controladores de error para guiar al usuario y generar una correcta entrada de datos. Los casilleros de búsquedas automáticas (nombres, cargo, sueldo, f.r.) deberían estar bloqueados contra escritura, y serán o bien de lectura, o bien para realizar los diferentes cálculos. Los diferentes códigos se anotan con diferentes eventos dentro del formulario. Para ir dentro del formulario y colocar el código completo asociado al mismo basta con hacer click derecho en el formulario o UserForm y click en ver código. Bien, observemos parte del código que contendrá este formulario, específicamente cuando se necesite añadir los campos del formulario a la base de datos, y un cálculo para el rol de pagos, sobre los días laborables:
Private Sub CommandButton7_Click()
'Optimizar macro
Application.ScreenUpdating = False
' Copiar fechas (inicial y final), nombre, cargo, sueldo y fondos de reserva
If fechainicio.Text <> "" And fechafin.Text <> "" And ComboBox1.Text <> "" Then
Sheets("ROL").Select
For i = 4 To 100
If Cells(i, 1).Value = "" Then
Cells(i, 1).Value = Format(fechainicio.Text, "mm/dd/yyyy")
Cells(i, 2).Value = Format(fechafin.Text, "mm/dd/yyyy")
Cells(i, 3).Value = ComboBox1.Text
Cells(i, 4).Value = cargo.Text
Cells(i, 7).Value = diasjustificados.Text
Cells(i, 10).Value = CDbl(sueldo.Text)
Cells(i, 11).Value = CDbl(bonos.Text)
Cells(i, 12).Value = CDbl(comisiones.Text)
Cells(i, 13).Value = CDbl(viaticos.Text)
Cells(i, 19).Value = CDbl(prestamos.Text)
Cells(i, 20).Value = CDbl(anticipos.Text)
Cells(i, 23).Value = CDbl(multas.Text)
Exit For
End If
Next
'Optimizar macro
Application.ScreenUpdating = False
' Calcular días laborables
lastrow = Sheets("ROL").Range("E" & Rows.Count).End(xlUp).Row
Sheets("ROL").Range("E4:E" & lastrow + 1).formula = "=NETWORKDAYS(RC[-4],RC[-3],fechasfiesta)"
'Otros cálculos I
lastrow = Sheets("ROL").Range("R" & Rows.Count).End(xlUp).Row
Sheets("ROL").Range("R4:R" & lastrow + 1).formula = "AAAA"
'Otros cálculos II
lastrow = Sheets("ROL").Range("O" & Rows.Count).End(xlUp).Row
Sheets("ROL").Range("O4:O" & lastrow + 1).formula = "BBBB"
'Otros cálculos III
lastrow = Sheets("ROL").Range("P" & Rows.Count).End(xlUp).Row
Sheets("ROL").Range("P4:P" & lastrow + 1).formula = "ZZZZ"
'Datos sin fórmulas
Sheets("ROL").Select
Range("A" & Rows.Count).End(xlUp).Select
Range(ActiveCell, Cells(ActiveCell.Row, 30)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Escape
Application.CutCopyMode = False
Range("A1").Select
MsgBox ("Datos de ROL almacenados con éxito. Cálculos realizados satisfactoriamente."), vbInformation
Else
MsgBox ("Rellenar todos los campos"), vbCritical
End If
End sub
Como se ha observado en el código VBA con anterioridad, en la parte de "Cálculos I, II y III" se puede ir anotando los diferentes cálculos que requiera nuestra base de datos de nómina o rol de pagos bajo ese mismo procedimiento; entonces, si tenemos una fórmula que realice la ejecución de un cálculo, basta con copiar la fórmula con Ctrl + C, ir a la pestaña Desarrollador y grabar macro, copiar la fórmula con Ctrl + V, pegar la fórmula y detener la grabación de la macro, copiar la fórmula codificada e indexarla donde se coloca "AAAA" o "BBBB" o "ZZZZ".
Eso sería el procedimiento para tener un rol de pagos automatizado con Excel y VBA. Si deseas visualizar la ejecución del programa para el formulario de nómina o rol de pagos puedes observar el siguiente vídeo:
Para la generación del rol de pagos individual bastaría con desarrollar una plantilla acorde con los datos que se desee reportar; después indexar fórmulas de tipo matricial, dado el caso que se utilice este tipo de organización de datos, y finalmente elaborar un botón para llamar a la acción de imprimir; para esto último se puede ir al entorno de VBA, en la pestaña insertar, click en módulo, y en la hoja en blanco escribir el siguiente código:
Sub Imprimir()
'Imprimir (vista previa)
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Luego, de ello, salir del entorno VBA, elaborar cualquier objeto en la hoja donde se desarrolló la plantilla para el reporte del rol; y desde la pestaña insertar, click derecho en el objeto, asignar macro y colocar la recién elaborada. Si deseas observar el procedimiento para realizar esta acción, puedes visualizar el siguiente video:
Ahora, si deseas realizar tu propia base de datos de nómina y rol de pagos, con todos los cálculos que han sido elaborados aquí y los controladores de error necesarios para una óptima entrada de datos, puedes considerar adquirir nuestro sistema a un costo bastante accesible, el cual muestra todo el código empleado para los formularios en cuestión con un único pago y licencia perpetua, el mismo te permite editar todo el código sin ningún tipo de restricción, y muchos otros beneficios y funcionalidades que puedes observar en esta web en el apartado Descripción - Funciones.
Por mi parte, me despido, sin antes desearles un genial día. Y, no se olviden de sonreír, para recordar que la vida sonríe siempre.
Referencias bibliográficas:
Julián Pérez Porto (2008). [En línea]. Extraído de la página web: https://definicion.de/seguro-social/
Julián Pérez Porto (2013). [En línea]. Extraído de la página web: https://definicion.de/horas-extras/
Julián Pérez Porto (2016). [En línea]. Extraído de la página web: https://definicion.de/viaticos/
Álvarez V. & Cazorla A. (2012). Instructivo para el pago de nóminas en la empresa Tubería Galvanizada Ecuatoriana S.A. Universidad de Cuenca. Cuenca, Ecuador.
Anónimo (2010). [En línea]. Extraído de la página web: https://esfbs.com/glossary/income-tax-33