#basededatosenexcel #basededatos #sistemamrp #planificaciónderecursosmateriales #programaparagestióndeinventarios #controldeinventarios #gestióndeinventarios #gestióndeinventariosenexcel #ordenesdeproducción #ordenesdeproducciónexcel #gestordeinventarios
Hola emprendedores y entusiastas productivos. En este artículo vamos a realizar un procedimiento para asignar inventarios terminados a partir de órdenes de producción. Una vez se determinen los inventarios, los mismos servirán para conocer el stock de producto terminado, el stock de componentes, determinar indirectamente la venta de artículos, entre otros.
Como es usual, primero vamos a observar la teoría respecto al tema.
Inventarios
Según Muñoz D. (2009), existen varios tipos de inventarios, aunque los inventarios de productos terminados son los que presentan mayor valor, pues contienen el valor agregado dado por la compañía. Este tipo de inventarios puede administrarse bajo dos mecanismos de control:
- Sistema de revisión continua. Revisión del nivel de inventarios cuando se hace un pedido si el nivel alcanzó el punto de reorden. Este sistema exige registros continuos de entradas y salidas de inventarios mediante sistemas de información.
- Sistema de revisión periódica. Menos precisa, aunque más económica.
Órdenes de producción
Será referenciado como aquellas órdenes de producción a ser elaboradas asignadas a un cliente específico para una fecha determinada. En este sentido, debemos aclarar que no se trata de un sistema por órdenes de producción, el cual según Alfredo R. (2007) se caracteriza porque cada uno de los costos incurridos se puede identificar directamente en el producto y se le asigna a la orden que lo genera.
Bajo la premisa anterior, el registro de órdenes de producción no tendrá los apartados de costo unitario y costo total, característica importante de un sistema por órdenes de producción. Sin embargo, en el sistema desarrollado, los costos unitarios, totales y precios se muestran en la factura a ser emitida para un cliente.
Ahora bien, vamos a entablar un proceso sencillo para enlistar inventarios de productos terminados a partir de órdenes de producción.
1. Generar formulario para órdenes de producción (sin costos)
Podemos desarrollar un formulario para asignar órdenes de producción 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 algunos campos del formulario que se ha desarrollado:
# Orden: calculada de forma automática a partir de las órdenes de producción que se presenten en la base de datos.
Producto: Artículo a elaborar.
Cantidad de pedido: Cantidad de artículo a elaborar.
2. Codificación VBA
Los formularios deben contener controladores de error para guiar al usuario y generar una correcta entrada de datos. 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 podría tener este formulario, específicamente cuando se envíen los productos agregados del list box a un formato establecido en una hoja de Excel:
Private Sub CommandButton3_Click()
'Definir variables
Dim fila As Integer
Dim final As Integer
'Prueba lógica
If txtapellidos.Text <> "" And txtci.Text <> "" And txtemail.Text <> "" And fechaentrega.Text <> "" And orden.Text <> "" Then
'Añadir datos del cliente
ActiveWorkbook.Sheets("PEDIDOS").Cells(4, 5) = orden.Text
ActiveWorkbook.Sheets("PEDIDOS").Cells(5, 5) = Format(fechaentrega.Text, "mm/dd/yyyy")
ActiveWorkbook.Sheets("PEDIDOS").Cells(8, 5) = txtapellidos.Text
ActiveWorkbook.Sheets("PEDIDOS").Cells(9, 5) = txtci.Text
ActiveWorkbook.Sheets("PEDIDOS").Cells(10, 5) = txtemail.Text
Else
MsgBox ("Rellenar todos los campos")
End If
'Añadir datos del producto
'Buscamos la última fila
fila = 14
Do While Worksheets("PEDIDOS").Cells(fila, 2) <> ""
fila = fila + 1
Loop
final = fila
'Agregamos el bloque de items
For i = 0 To Me.ListBox1.ListCount - 1
Worksheets("PEDIDOS").Cells(final, 2) = Me.ListBox1.List(i, 0) 'Código
Worksheets("PEDIDOS").Cells(final, 3) = Me.ListBox1.List(i, 1) 'Producto
Worksheets("PEDIDOS").Cells(final, 4) = Me.ListBox1.List(i, 2) 'Cantidad demandada
final = final + 1
Next
ListBox1.Clear 'Limpiamos el listbox
i = 0 'Reiniciamos el índice de registros del listbox
ComboBox1.SetFocus 'Enviamos el foco al control
End Sub
Si deseas visualizar la ejecución del programa para el formulario en cuestión puedes observar el siguiente vídeo:
3. Guardar las órdenes de producción en una base de datos
Con la orden de producción generada, vamos a proceder a almacenarla en una base de datos para su uso posterior, para ello, o bien podemos asignar un botón que realice este procedimiento automáticamente o podemos hacerlo manualmente. Vamos a observar la forma automática, por medio de la ejecución de la siguiente macro:
Sub GuardarOrdendeProduccionFull()
' Datos otros valores complementarios de la factura
Dim UltimaFila As Long
Dim Hoja As Worksheet
Dim ultFila As Long
Dim Rng As Range
Dim Rng2 As Range
'Evitar código duplicado (controlador de error)
codigo = Sheets("PEDIDOS").Range("E4").Value
contarcodigo = Application.WorksheetFunction.CountIf(Sheets("BD").Columns(210), codigo)
If contarcodigo > 0 Then
MsgBox ("Código de número de orden duplicado"), vbCritical
Exit Sub
End If
'Controlador de error 2: NO hay datos de producto
If Sheets("PEDIDOS").Range("B15") = "" Or Sheets("PEDIDOS").Range("C15") = "" Or Sheets("PEDIDOS").Range("D15") = "" Then
MsgBox ("Faltan datos del producto"), vbCritical
Exit Sub
End If
'Controlador de error 3: NO hay datos de cliente
If Sheets("PEDIDOS").Range("E4") = "" Or Sheets("PEDIDOS").Range("E5") = "" Or Sheets("PEDIDOS").Range("E6") = "" Or Sheets("PEDIDOS").Range("E8") = "" Or Sheets("PEDIDOS").Range("E9") = "" Or Sheets("PEDIDOS").Range("E10") = "" Then
MsgBox ("Faltan datos del cliente"), vbCritical
Exit Sub
End If
'Optimizar tiempo de ejecución de la macro
Application.ScreenUpdating = False
'Asignar PRIMER órden de producción para evitar error de Referencia
If Sheets("BD").Range("GZ7").Value = "" Then
With Sheets("BD")
UltimaFila = 1 + .Cells(Rows.Count, "HF").End(xlUp).Row
.Cells(UltimaFila, 208) = Sheets("PEDIDOS").Range("E5").Value 'Fecha de entrega
.Cells(UltimaFila, 209) = Sheets("PEDIDOS").Range("E6").Value 'Semana calendario
.Cells(UltimaFila, 210) = Sheets("PEDIDOS").Range("E4").Value 'Código de número de órden de producción
.Cells(UltimaFila, 211) = Sheets("PEDIDOS").Range("E8").Value 'Cliente
.Cells(UltimaFila, 212) = Sheets("PEDIDOS").Range("E9").Value 'Cédula/RUC
.Cells(UltimaFila, 213) = Sheets("PEDIDOS").Range("E10").Value 'E- mail
End With
'Copiar datos de código, descripción y cantidad
Sheets("PEDIDOS").Select
Range("B15:F51").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("BD").Select
Range("HF1000000").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
'Guardar
ActiveWorkbook.Save
Sheets("PEDIDOS").Select
Range("B14").Select
MsgBox ("Datos de Orden de Producción almacenados con éxito"), vbInformation
Exit Sub
End If
'Optimizar tiempo de ejecución de la macro
Application.ScreenUpdating = False
With Sheets("BD")
UltimaFila = 1 + .Cells(Rows.Count, "HF").End(xlUp).Row
.Cells(UltimaFila, 208) = Sheets("PEDIDOS").Range("E5").Value 'Fecha de entrega
.Cells(UltimaFila, 209) = Sheets("PEDIDOS").Range("E6").Value 'Semana calendario
.Cells(UltimaFila, 210) = Sheets("PEDIDOS").Range("E4").Value 'Código de número de órden de producción
.Cells(UltimaFila, 211) = Sheets("PEDIDOS").Range("E8").Value 'Cliente
.Cells(UltimaFila, 212) = Sheets("PEDIDOS").Range("E9").Value 'Cédula/RUC
.Cells(UltimaFila, 213) = Sheets("PEDIDOS").Range("E10").Value 'E- mail
End With
'Copiar datos de código, descripción y cantidad
Sheets("PEDIDOS").Select
Range("B15:F51").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("BD").Select
Range("HF1000000").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
On Error GoTo Errores
'Segmento de código para copiar celdas anteriores y poder buscar en el inventario
'Establecer variables
Set Hoja = Sheets("BD")
With Hoja
'Encontramos la última Fila con valores
ultFila = .Range("HF" & .Rows.Count).End(xlUp).Row
'creamos el rango
Set Rng = .Range("HB7:HB" & ultFila)
End With
'Copiar Celdas anteriores
Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'Establecer variables
Set Hoja = Sheets("BD")
With Hoja
'Encontramos la última Fila con valores
ultFila = .Range("HF" & .Rows.Count).End(xlUp).Row
'creamos el rango
Set Rng2 = .Range("HC7:HC" & ultFila)
End With
'Copiar Celdas anteriores
Rng2.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'Libera variables de objeto
Set Hoja = Nothing
Set Rng = Nothing
Set Rng2 = Nothing
Errores: Range("A1").Select
'Guardar
ActiveWorkbook.Save
Sheets("PEDIDOS").Select
Range("B14").Select
MsgBox ("Datos de Orden de Producción almacenados con éxito"), vbInformation
End Sub
La ejecución del procedimiento anterior se puede visualizar en el video previo.
4. Generación de formulario para asignar Inventarios de artículos terminados
Bien, vamos a detallar algunos campos del formulario que se ha desarrollado:
Buscar orden de producción: se coloca el número de orden producción para generar la cantidad de producción, si no conoce el número de orden se pulsa el botón ver, el cual muestra un cuadro de búsqueda por número, nombre de cliente o código de orden.
Aceptar: Almacena la cantidad real elaborada (inventario de producto terminado) de una orden producción dada.
5. Codificación VBA
Los formularios deben contener controladores de error para guiar al usuario y generar una correcta entrada de datos. 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 podría tener este formulario, específicamente cuando se realice una búsqueda de una orden de producción que ya haya sido generada al list box del formulario:
Private Sub CommandButton6_Click()
'Cuadro de diálogo para buscar vacío o buscar valor
If Me.buscarorden.Value = Empty Then
MsgBox "Escriba una orden de producción o a su vez el nombre de un cliente para buscar"
Me.ListBox1.Clear
Me.buscarorden.SetFocus
Exit Sub
End If
Application.ScreenUpdating = False
Me.ListBox1.Clear
'Seleccionar Hoja de BD
Sheets("BD").Select
'Asignar variable items
items = Sheets("BD").Range("ordendeproduccion").CurrentRegion.Rows.Count
'Fila desde que empieza a contar
For i = 7 To items + 4
'Busco x # de órden de producción
If LCase(Cells(i, 210).Value) Like "*" & LCase(Me.buscarorden.Value) & "*" Then
Me.ListBox1.AddItem Cells(i, 210) '# de órden de producción
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Cells(i, 211) 'Nombre del cliente
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Cells(i, 215) 'Producto
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Cells(i, 216) 'Cantidad
End If
Next i
Me.buscarorden.SetFocus
Me.buscarorden.SelStart = 0
Me.buscarorden.SelLength = Len(Me.buscarorden.Text)
'Seleccionar Nuevamente la hoja de factura
Sheets("PEDIDOS").Select
Exit Sub
End Sub
Eso sería el procedimiento para generar inventarios de producto terminado a partir de órdenes de producción con VBA y Excel. Si deseas realizar tus propio formularios para que puedas ejecutar este tipo de cálculos rápidamente a partir de nuestro código ya construido, 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.
Si deseas visualizar la ejecución del programa para el formulario en cuestión puedes observar el siguiente vídeo:
Espero que este artículo te sea de ayuda o de guía. 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:
Alfredo Ricardo R. (2007). Sistemas de costos. Un proceso para su implementación. Universidad Nacional de Colombia. Manizales, Colombia.
Muñoz David F. (2009). Administración de operaciones. Enfoque de administración de procesos de negocios. Cengage Learning. México.
Comments