Contenido
Formulación del problema
Supongamos que la empresa en la que trabajas tiene tres almacenes, desde donde van las mercancías a cinco de tus tiendas repartidas por todo Moscú.
Cada tienda puede vender una cierta cantidad de productos que conocemos. Cada uno de los almacenes tiene una capacidad limitada. La tarea es elegir racionalmente de qué almacén a qué tiendas entregar los productos para minimizar los costos totales de transporte.
Antes de comenzar la optimización, será necesario compilar una tabla simple en una hoja de Excel: nuestro modelo matemático que describe la situación:
Se entiende que:
- La tabla de color amarillo claro (C4:G6) describe el costo de envío de un artículo desde cada almacén a cada tienda.
- Las celdas moradas (C15:G14) describen la cantidad de bienes necesarios para vender en cada tienda.
- Las celdas rojas (J10:J13) muestran la capacidad de cada almacén: la cantidad máxima de mercancías que puede contener el almacén.
- Las celdas amarillas (C13:G13) y azules (H10:H13) son las sumas de fila y columna para las celdas verdes, respectivamente.
- El costo total de envío (J18) se calcula como la suma de los productos de la cantidad de bienes y sus costos de envío correspondientes; para el cálculo, la función se usa aquí SUMPRODUCT (SUMAPRODUCTO).
Así, nuestra tarea se reduce a la selección de valores óptimos de celdas verdes. Y para que el monto total de la línea (celdas azules) no exceda la capacidad del almacén (celdas rojas), y al mismo tiempo cada tienda reciba la cantidad de mercancías que necesita vender (la cantidad de cada tienda en el las celdas amarillas deben estar lo más cerca posible de los requisitos – celdas moradas).
Solución
En matemáticas, tales problemas de elección de la distribución óptima de recursos han sido formulados y descritos durante mucho tiempo. Y, por supuesto, las formas de resolverlos se han desarrollado durante mucho tiempo no mediante una enumeración directa (que es muy larga), sino en un número muy pequeño de iteraciones. Excel proporciona al usuario dicha funcionalidad mediante un complemento. Buscar soluciones (solucionador) de la pestaña Datos (Fecha):
Si en la ficha Datos su Excel no tiene ese comando, está bien, significa que el complemento simplemente aún no está conectado. Para activarlo abre Archive, A continuación, seleccione parámetros – Add-ons – Nuestra Empresa (Opciones — Complementos — Ir a). En la ventana que se abre, marque la casilla junto a la línea que necesitamos Buscar soluciones (solucionador).
Ejecutemos el complemento:
En esta ventana, debe configurar los siguientes parámetros:
- Optimizar la función de destino (Establecer tdinero célula) – aquí es necesario indicar el objetivo principal final de nuestra optimización, es decir, el cuadro rosa con el costo total de envío (J18). La celda objetivo se puede minimizar (si son gastos, como en nuestro caso), maximizar (si es, por ejemplo, ganancias) o intentar llevarla a un valor dado (por ejemplo, encajar exactamente en el presupuesto asignado).
- Cambio de celdas variables (By cambio células) – aquí indicamos las celdas verdes (C10: G12), variando los valores de los cuales queremos lograr nuestro resultado – el costo mínimo de entrega.
- De acuerdo con las restricciones (Tema a las restricciones) – una lista de restricciones que deben tenerse en cuenta al optimizar. Para agregar restricciones a la lista, haga clic en el botón Añada (Agregar) e ingrese la condición en la ventana que aparece. En nuestro caso, esta será la restricción de demanda:
y límite al volumen máximo de almacenes:
Además de las limitaciones obvias asociadas a factores físicos (capacidad de almacenes y medios de transporte, limitaciones de presupuesto y tiempo, etc.), en ocasiones es necesario añadir restricciones “especiales para Excel”. Entonces, por ejemplo, Excel puede hacer arreglos fácilmente para que usted "optimice" el costo de la entrega ofreciéndole el transporte de mercancías desde las tiendas hasta el almacén: los costos se volverán negativos, es decir, obtendremos una ganancia. 🙂
Para evitar que esto suceda, es mejor dejar la casilla de verificación activada. Hacer variables ilimitadas no negativas o incluso a veces registrar explícitamente tales momentos en la lista de restricciones.
Después de configurar todos los parámetros necesarios, la ventana debería verse así:
En la lista desplegable Seleccionar un método de resolución, también debe seleccionar el método matemático apropiado para resolver una selección de tres opciones:
- Método simplex es un método simple y rápido para resolver problemas lineales, es decir, problemas en los que la salida depende linealmente de la entrada.
- Método de gradiente degradado general (OGG) – para problemas no lineales, donde existen dependencias no lineales complejas entre los datos de entrada y salida (por ejemplo, la dependencia de las ventas de los costos de publicidad).
- Búsqueda evolutiva de una solución – un método de optimización relativamente nuevo basado en los principios de la evolución biológica (hola Darwin). Este método funciona mucho más tiempo que los dos primeros, pero puede resolver casi cualquier problema (no lineal, discreto).
Nuestra tarea es claramente lineal: entregado 1 pieza - gastado 40 rublos, entregado 2 piezas - gastado 80 rublos. etc., por lo que el método símplex es la mejor opción.
Ahora que se ingresaron los datos para el cálculo, presione el botón Encuentra una solución (Resolver)para iniciar la optimización. En casos severos con muchas celdas cambiantes y restricciones, encontrar una solución puede llevar mucho tiempo (especialmente con el método evolutivo), pero nuestra tarea para Excel no será un problema: en un par de momentos obtendremos los siguientes resultados :
Preste atención a lo interesante que se distribuyeron los volúmenes de suministro entre las tiendas, sin exceder la capacidad de nuestros almacenes y satisfaciendo todas las solicitudes de la cantidad requerida de productos para cada tienda.
Si la solución encontrada nos conviene, podemos guardarla o volver a los valores originales e intentarlo de nuevo con otros parámetros. También puede guardar la combinación seleccionada de parámetros como Guión. A petición del usuario, Excel puede construir tres tipos Informes sobre el problema que se resuelve en hojas separadas: un informe sobre los resultados, un informe sobre la estabilidad matemática de la solución y un informe sobre los límites (restricciones) de la solución, sin embargo, en la mayoría de los casos, son de interés solo para especialistas .
Sin embargo, hay situaciones en las que Excel no puede encontrar una solución adecuada. Es posible simular tal caso si indicamos en nuestro ejemplo los requerimientos de las tiendas en una cantidad mayor a la capacidad total de los almacenes. Luego, al realizar una optimización, Excel intentará acercarse lo más posible a la solución y luego mostrará un mensaje que indica que no se puede encontrar la solución. Sin embargo, incluso en este caso, tenemos mucha información útil; en particular, podemos ver los "eslabones débiles" de nuestros procesos comerciales y comprender las áreas de mejora.
El ejemplo considerado, por supuesto, es relativamente simple, pero escala fácilmente para resolver problemas mucho más complejos. Por ejemplo:
- Optimización de la distribución de los recursos financieros por rubro de gasto en el plan de negocios o presupuesto del proyecto. Las restricciones, en este caso, serán la cantidad de financiamiento y el tiempo del proyecto, y el objetivo de la optimización es maximizar las ganancias y minimizar los costos del proyecto.
- Optimización de la programación de empleados para minimizar el fondo de salarios de la empresa. Las restricciones, en este caso, serán los deseos de cada empleado según el horario de trabajo y los requerimientos de la plantilla.
- Optimización de las inversiones de inversión – la necesidad de distribuir correctamente los fondos entre varios bancos, valores o acciones de empresas para, nuevamente, maximizar las ganancias o (si es más importante) minimizar los riesgos.
En cualquier caso, complemento Buscar soluciones (solucionador) es una herramienta de Excel muy poderosa y hermosa y digna de su atención, ya que puede ayudarlo en muchas situaciones difíciles que debe enfrentar en los negocios modernos.