Calcula y Optimiza Costos de Transporte con Excel

Los costos de transporte son un componente crucial en la estructura de gastos de cualquier cadena de suministro. Para los gerentes de logística y cadena de suministro, identificar y aprovechar las oportunidades de reducción de costos en esta área es fundamental. En este artículo, exploraremos uno de los casos fundamentales de asignación de mercado y suministro: el conocido como el Problema de Transporte. Nuestro objetivo es doble: satisfacer la demanda de manera eficiente y sensible, al mismo tiempo que minimizamos los costos de transporte.

Una asignación adecuada de las fuentes de suministro a los destinos tiene un impacto considerable en el rendimiento financiero y operativo de su cadena de suministro. Al hacerlo correctamente, podrá gestionar la producción y el inventario de forma efectiva, acelerar los procesos y mejorar el servicio al cliente. Excel, con su potente complemento Solver, se convierte en una herramienta invaluable para abordar este desafío.

¿Qué es el Problema de Transporte?

El Problema de Transporte es una subclase de los problemas de programación lineal cuyo objetivo principal es transportar productos almacenados en una instalación (por ejemplo, un almacén o centro de distribución) a diferentes destinos o mercados. Esto se realiza de tal manera que se minimice el costo total de transporte, al tiempo que se satisfacen todas las restricciones de suministro y demanda. Esencialmente, busca la forma más económica de mover bienes desde donde están disponibles hasta donde se necesitan.

Para resolver un problema de transporte, se necesita la siguiente información clave:

• La demanda de producto en cada instalación de destino.
• Las ubicaciones de las instalaciones de origen y destino, así como las distancias entre cada par posible.
• El costo por kilómetro recorrido (o por unidad por kilómetro).
• La capacidad máxima de almacenamiento o suministro de cada instalación de origen.

Imaginemos una red de cadena de suministro simple con varias etapas: centros de distribución que proveen a múltiples tiendas. El reto es determinar cuántas unidades enviar desde cada centro de distribución a cada tienda para satisfacer la demanda total de las tiendas sin exceder la capacidad de los centros de distribución, todo ello con el menor costo posible.

Preparando los Datos en Excel para el Análisis

Una vez que hemos definido el problema y comprendido la red de nuestra cadena de suministro, el siguiente paso es organizar los datos necesarios en una hoja de cálculo de Excel. La clave aquí es la claridad y la estructuración para que Solver pueda interpretar correctamente la información.

Paso 1: Ingresar la Demanda y la Capacidad

Comience por registrar la demanda de cada tienda. Esto le dará la demanda total que debe ser satisfecha. Luego, ingrese la capacidad de almacenamiento o suministro de cada centro de distribución. Es útil comparar la demanda total con la capacidad total para asegurarse de que hay suficiente oferta para cubrir la demanda del mercado.

Paso 2: Calcular Distancias y Costos por Kilómetro

Las distancias entre todos los orígenes (centros de distribución) y destinos (tiendas) son cruciales. Estas distancias deben ser precisas. Si obtiene distancias de ida y vuelta, recuerde dividirlas por la mitad para obtener la distancia de un solo trayecto. El costo por kilómetro recorrido es otro dato fundamental. Este valor puede ser una constante predeterminada (como $0.9 por kilómetro para camiones grandes en algunos software) o un valor ajustado basado en su propia investigación de costos de transporte.

Paso 3: Definir las Variables Cambiantes

Nuestro objetivo es encontrar las cantidades de entrega desde los centros de distribución a las tiendas que minimicen los costos de transporte, al mismo tiempo que satisfacen la demanda de las tiendas. Por lo tanto, las variables cambiantes en nuestra hoja de cálculo serán el número de unidades que se moverán en cada ruta posible. En Excel, estas celdas se suelen marcar con un color distintivo, como el amarillo, para identificarlas fácilmente. Aunque podríamos dejarlas en cero inicialmente, a menudo se les asigna un valor de uno para asegurar que las sumas de filas y columnas se calculen correctamente desde el principio.

Las celdas de variables cambiantes formarán una matriz donde las filas representan los centros de distribución de origen y las columnas representan las tiendas de destino. A la derecha de esta matriz, se calculará la cantidad total entregada a cada tienda (suma de columna), y debajo de la matriz, la cantidad total que cada centro de distribución transferirá (suma de fila). Es útil también tener una copia transpuesta de la matriz de capacidades de los centros de distribución para facilitar la adición de restricciones en Solver.

La Fórmula del Costo de Transporte en Excel

Antes de configurar y ejecutar el modelo de Solver, debemos definir la fórmula del costo total de transporte. Aquí, la elección de la fórmula dependerá de cómo se manejen los costos y la capacidad de los vehículos.

Opción 1: Costo por Kilómetro y Volumen de Carga del Vehículo

Si solo se tiene el costo por kilómetro recorrido ($/Km), y los vehículos tienen una capacidad de carga máxima, la fórmula del costo total de transporte será el costo por kilómetro multiplicado por la distancia de cada ruta, y luego multiplicado por el número de entregas necesarias para transportar la cantidad de unidades. Para determinar cuántos camiones (o entregas) se necesitan para una cantidad específica de unidades, debemos dividir la cantidad de unidades por el volumen máximo de carga de un camión y redondear ese número hacia arriba (ya que no se puede enviar una fracción de camión).

En Excel, la función para redondear hacia arriba es REDONDEAR.MAS. Si, por ejemplo, un camión grande tiene un volumen máximo de carga de 110 unidades, el número de entregas necesarias para una matriz de unidades entregadas (por ejemplo, `I15:K22`) sería =REDONDEAR.MAS(I15:K22/110;0). Este cálculo se aplicaría a cada celda de la matriz de unidades entregadas.

La fórmula completa para el costo total de transporte, utilizando la función SUMAPRODUCTO de Excel, sería:

=SUMAPRODUCTO(MatrizDistancias;REDONDEAR.MAS(MatrizCantidadesEntregadas/VolumenCamion;0))*CostoPorKm

Donde:

• MatrizDistancias: Es el rango de celdas que contiene las distancias entre cada origen y destino.
• MatrizCantidadesEntregadas: Es el rango de celdas que contiene las variables cambiantes (unidades a entregar).
• VolumenCamion: Es la capacidad máxima de unidades por camión (ej. 110).
• CostoPorKm: Es el costo por kilómetro recorrido (ej. $0.9).

La función SUMAPRODUCTO es ideal aquí porque multiplica los componentes correspondientes de las matrices dadas y devuelve la suma de esos productos.

Opción 2: Costo por Kilómetro por Unidad

Si se tiene el costo por kilómetro por unidad ($/Km/unidad), la fórmula se simplifica considerablemente, ya que no es necesario considerar la capacidad del camión directamente en el cálculo del número de entregas. En este caso, el costo total sería el costo por kilómetro por unidad, multiplicado por la distancia de cada ruta, y luego multiplicado por el número de unidades enviadas en esas rutas.

La fórmula en Excel sería:

=SUMAPRODUCTO(MatrizDistancias;MatrizCantidadesEntregadas)*CostoPorKmPorUnidad

Es importante tener en cuenta que, en este problema de transporte simple, el costo por kilómetro o el costo por kilómetro por unidad no cambiará la solución óptima (es decir, qué rutas se deben usar y cuántas unidades), sino que solo afectará el costo total resultante.

Creando y Ejecutando el Modelo de Excel Solver

Una vez que los datos están organizados y la fórmula del costo total ha sido definida, es el momento de utilizar el complemento Solver de Excel. Si aún no lo tiene instalado, puede encontrar instrucciones en línea sobre cómo activarlo desde las opciones de complementos de Excel.

Definiendo la Función Objetivo

La función objetivo de nuestro modelo es minimizar el costo total. En la ventana de Solver, en la sección 'Establecer objetivo', seleccionará la celda que contiene la fórmula del costo total que acabamos de crear (por ejemplo, la celda I25 si sigue el ejemplo de la fuente). Asegúrese de seleccionar la opción 'Min' para minimizar.

Definiendo las Variables Cambiantes

En la sección 'Cambiando las celdas de variables', seleccionará el rango de celdas que contiene sus variables cambiantes, es decir, la matriz de unidades a entregar desde cada centro de distribución a cada tienda (las celdas que marcó en amarillo).

Estableciendo las Restricciones

Las restricciones son las condiciones que el modelo debe cumplir. Haga clic en el botón 'Agregar' para añadir cada una de ellas:

1. Restricciones de Demanda: Para satisfacer la demanda, la cantidad total entregada a cada tienda debe ser igual a la demanda de esa tienda. Esto se configura seleccionando el rango de celdas que representa la suma de las entregas a cada tienda y estableciéndolo como igual al rango de celdas que contiene la demanda de cada tienda.
2. Restricciones de Capacidad: La cantidad total entregada desde cada centro de distribución no debe exceder su capacidad unitaria. Esto se configura seleccionando el rango de celdas que representa la suma de las entregas desde cada centro de distribución y estableciéndolo como menor o igual al rango de celdas que contiene la capacidad de cada centro de distribución.
3. Restricciones de Enteros y No Negativas: Las variables de decisión (las cantidades entregadas) deben ser números enteros y mayores o iguales a cero. Esto se configura marcando la casilla 'Convertir variables sin restricciones en no negativas' y añadiendo una restricción para que el rango de las variables cambiantes sea 'entero'.

Una vez que todas las restricciones estén añadidas, en la sección 'Seleccionar un método de resolución', elija 'Simplex LP' (Programación Lineal Simplex), ya que nuestro modelo es lineal. Finalmente, haga clic en el botón 'Resolver'. Excel Solver calculará la solución óptima, mostrando las cantidades de unidades a transportar y el costo total mínimo.

Interpretación de Resultados y Análisis Adicional

Después de que Solver ha encontrado una solución, es crucial interpretar los resultados y verificar si se cumplen todas las demandas y si se respetan las restricciones de capacidad. La solución mostrará las cantidades óptimas a enviar por cada ruta y el costo total mínimo. En un ejemplo típico, se podría obtener un costo total aceptable.

Sin embargo, la interpretación va más allá de solo el número final. Observe cómo se utilizan las capacidades de los centros de distribución. Por ejemplo, si un centro de distribución utiliza su capacidad máxima, otro el 60%, y un tercero solo el 10%, surge una pregunta estratégica: ¿realmente necesitamos operar los tres centros de distribución para el próximo período?

Aquí es donde la cadena de suministro se convierte tanto en ciencia como en arte. Si bien las cálculos numéricos encuentran formas de reducir costos, el juicio profesional es vital para identificar oportunidades de crecimiento y optimización más allá de la solución numérica inmediata. Operar con menos instalaciones podría reducir los costos fijos de operación, pero podría aumentar los costos de transporte si las rutas se vuelven más largas o complejas. Para encontrar la solución verdaderamente óptima, es necesario comparar el costo total de diferentes escenarios.

Análisis de Escenarios con Simulaciones

Crear copias de su modelo de cadena de suministro original (o su hoja de Excel) y modificarlas para reflejar diferentes diseños potenciales es una práctica excelente. Por ejemplo, puede comparar un escenario donde la empresa utiliza todos sus centros de distribución y paga todos sus costos fijos, con un escenario donde opera solo dos centros de distribución y cierra el tercero. Ejecutar simulaciones para estos escenarios le permitirá ver cómo funcionan, generar datos de rendimiento y comparar informes de ganancias y pérdidas (P&L) e indicadores clave de rendimiento (KPIs).

Este proceso de simulación y comparación, combinado con la revisión y el aporte de las partes interesadas relevantes, permite seleccionar el diseño de la cadena de suministro que mejor satisfaga las necesidades de la empresa en una situación dada. Las situaciones cambian de mes a mes; la solución óptima de este mes puede convertirse rápidamente en un gran error el próximo mes. Las empresas trazan su rumbo cada mes combinando cálculos numéricos con juicio profesional para navegar con éxito en un mundo en constante cambio.

Preguntas Frecuentes (FAQs)

¿Qué es el Problema de Transporte en logística?

Es un tipo de problema de programación lineal que busca la forma más económica (minimizando costos de transporte) de mover productos desde múltiples puntos de origen (oferta) a múltiples puntos de destino (demanda), cumpliendo con las restricciones de capacidad y demanda.

¿Para qué sirve el complemento Solver en Excel?

Solver es una herramienta de optimización en Excel que permite encontrar el valor óptimo (mínimo o máximo) de una celda objetivo, ajustando los valores de otras celdas (variables cambiantes) que están relacionadas con la celda objetivo, y respetando un conjunto de restricciones.

¿Qué información necesito para resolver un problema de transporte en Excel?

Necesitará la demanda de cada destino, la capacidad de suministro de cada origen, las distancias entre cada origen y destino, y el costo de transporte por unidad de distancia (ej. costo por km).

¿Cómo se maneja la capacidad de los vehículos en la fórmula de costo?

Si el costo se basa en el número de viajes de vehículos con capacidad limitada, se usa la función REDONDEAR.MAS (ROUNDUP en inglés) para calcular el número de camiones necesarios para transportar una cantidad dada de unidades (cantidad de unidades / capacidad del camión, redondeado hacia arriba).

¿El costo por kilómetro afecta la solución óptima o solo el costo total?

En un problema de transporte simple, el costo por kilómetro (o por unidad por kilómetro) no cambia la asignación óptima de rutas y cantidades. Solo escala el costo total; la solución de *qué* enviar y *a dónde* sigue siendo la misma, pero el valor final del costo total sí se modifica.

¿Por qué es importante considerar escenarios alternativos después de usar Solver?

Solver proporciona una solución matemáticamente óptima bajo las condiciones dadas. Sin embargo, en la realidad, hay factores estratégicos y operativos (como costos fijos de instalaciones, flexibilidad, servicio al cliente) que no siempre se incluyen en el modelo inicial. Comparar escenarios alternativos permite una toma de decisiones más holística y estratégica, combinando la optimización numérica con el juicio profesional.

Si quieres conocer otros artículos parecidos a Calcula y Optimiza Costos de Transporte con Excel puedes visitar la categoría Cálculos.