excel Archivos - Página 3 de 19 - Gestión de Operaciones

Ejemplo del Cálculo del Punto de Equilibrio

En todo negocio un aspecto imprescindible consiste en evaluar la ganancia potencial de un producto o servicio, ya sea nuevo o existente. Se considera que los costos asociados a la producción de un producto o prestación de un servicio se puede dividir básicamente en 2 categorías: costos fijos (independientes del volumen de producción dentro de un rango de producción relevante) y costos variables (que varían directamente con el volumen de producción, asumiendo una relación lineal o proporcional). En este contexto el punto de equilibrio determina cuál debe ser el número de unidades vendidas que permite equiparar los ingresos totales con los costos totales, es decir, aquel volumen de ventas que evita pérdidas y ganancias.

Dado lo anterior queda de manifiesto la importancia de la evaluación del punto de equilibrio. El análisis se enfoca a responder preguntas del tipo:

¿Las ventas pronosticadas resultan ser suficientes para evitar pérdidas?
¿Cuánto debe bajar el costo variable unitario para alcanzar el punto de equilibrio, dadas las condiciones actuales de precios y proyecciones de ventas?
¿Cuál es el impacto del precio unitario en la obtención del punto de equilibrio?
¿Cuánto deben bajar los costos fijos para estar en una situación sin ganar o perder?

Sea $CT=F+cQ$ el costo total de producir un bien o prestar un servicio, donde $F$ es el costo fijo y $cQ$ los costos variables ( $c$ es el costo unitario y $Q$ la cantidad vendida). Adicionalmente sea $IT=pQ$ el ingreso total, donde $p$ es el precio unitario. El punto de equilibrio en términos de las unidades vendidas esta dado por:

Ejemplo Cálculo del Punto de Equilibrio

Una clínica esta evaluando un nuevo examen que reportará ingresos de $200 por paciente. El costo fijo anual será de $100.000 y los costos variables son de $100 por paciente. ¿Cuál es el punto de equilibrio para este servicio?.

Al evaluar en la fórmula anterior obtenemos lo siguiente:

Es decir, si se realizan 1.000 exámenes (asumiendo un examen por paciente) los ingresos totales igualan a los costos totales, evitando tanto pérdidas como ganancias. De forma complementaria con la ayuda de Excel se puede evaluar de forma sencilla tanto los ingresos como costos totales para distintos niveles de actividad (en este caso número de exámenes o pacientes). La línea azul representa el ingreso total en miles de $ (eje vertical) para distintos valores de números de pacientes (eje horizontal). La línea roja representa el costo total donde resulta de particular interés observar que su valor es de $100 (mil) en el caso de cero pacientes (costo fijo).

Una representación alternativa del ejemplo anterior hemos desarrollado con Geogebra, la cual se muestra a continuación. El área achurada de color rojo representa la pérdida, es decir, cuando el número de pacientes es menor al punto de equilibrio, por el contrario el área achurada de color verde representa la ganancia, en la cual se incurre cuando el nivel de pacientes supera el punto de equilibrio.

Cómo obtener la Ruta Crítica de un Proyecto (CPM) con OM Explorer en Excel

El el artículo Cómo obtener la Ruta Crítica de un Proyecto (Critical Path Method) describimos de forma detallada cómo las holguras de las actividades de un proyecto y la ruta o camino más largo (no necesariamente único) que determina la duración de un proyecto. Como complemento a lo anterior a continuación presentamos cómo desarrollar este procedimiento de forma sencilla haciendo uso de OM Explorer. Para este propósito utilizaremos como ejemplo un proyecto que considera un total de 12 actividades que se muestran a continuación junto al tiempo esperado para completar cada una de las actividades (en meses) y la relación de predecesores.

En primer lugar y una vez instalado el complemento OM Explorer en Excel ingresamos a Solvers (como se aprecia en la esquina superior izquierda en la imagen a continuación) y luego seleccionamos Project Management y Single Time Estimate.

Luego en la pestaña Inputs del archivo se ingresan las actividades, el tiempo requerido para completarlas y las relaciones de predecesores. La plantilla permite implementar hasta 4 predecesores por actividad lo que es claramente suficiente para nuestro ejemplo.

Una vez incorporadas la totalidad de las actividades en Inputs podemos revisar los resultados obtenidos en la hoja Results. Se observa el tiempo para completar el proyecto (15,5 meses) y con color rojo se destacan las actividades críticas (con holgura igual a cero), a saber, A-B-D-G-H-I-K-L, las que en dicho orden determinan la ruta crítica del proyecto (en este ejemplo única). Adicionalmente tanto para las actividades pertenecientes a la ruta crítica como aquellas con holgura mayor a cero se detalla el inicio más cercano (Early Start), término más cercano (Early Finish), inicio más lejano (Late Start) y término más lejano (Late Finish).

OM Explorer entrega adicionalmente una Carta Gantt donde se observa las actividades críticas en color rojo (con holgura o slack igual a cero), el tiempo de las actividades no críticas en color amarillo y el tiempo holgura de las actividades no críticas en color celeste, lo cual permite interpretar de forma intuitiva los resultados obtenidos.

Formulación de un Problema de Programación de Explotación Forestal resuelto con Solver de Excel

En el artículo Problema de Planificación Forestal resuelto con Graphic Linear Optimizer (GLP) describimos un problema de explotación forestal reducido en términos de la complejidad de un caso de esta naturaleza (de modo de representarlo gráficamente), el cual a continuación extenderemos a través de la incorporación de una serie de decisiones en el tiempo respecto a la actividad de producción, planificación de personal, gestión de inventarios, compra, entre otros. En este contexto considere el caso de una compañía forestal que cosecha (tala) árboles los primeros meses del año. La compañía tiene una serie de pedidos que debe satisfacer cada mes. Estos datos se resumen a continuación:

Al 1 de Enero hay un total de 40 trabajadores y no hay árboles en inventario. La jornada laboral es de 40 horas semanales y 4 semanas laborales al mes. Para cosechar un árbol se requiere 4 horas hombre. Independiente de lo anterior la forestal tiene una capacidad de cosecha de 3.000 árboles mensuales lo cual está dado por la maquinaria disponible.

El sueldo mensual de cada trabajador es de M$400 (el sueldo se paga de forma íntegra ante todo evento, es decir, trabajando la totalidad de horas al mes o menos). La política de la gerencia es no utilizar horas extraordinarias pero si podría comprar árboles a otra forestal cercana a un costo unitario de M$18. Adicionalmente se ha convenido no contratar trabajadores por una fracción de una jornada de trabajo normal (160[horas/mes]). Esto implica que si se contrata un trabajador debe ser por 160[horas/mes] a un costo de M$400 pero no es válido, por ejemplo, contratar un trabajador por 80[horas/mes] a un costo de M$200. El costo de contratar un trabajador es de M$200 y el costo de despedir un trabajador se estima en M$600.

Almacenar un árbol en bodega tiene un costo de M$10 de un mes a otro. Sin embargo, en la bodega no hay espacio para almacenar más de 500 árboles.

Formule y resuelva un modelo de Programación Entera para este problema que permita hallar una política óptima de explotación para la forestal. Indique claramente las variables de decisión del modelo y detalle explícitamente la función objetivo y cada una de las restricciones del modelo.

Variables de Decisión:

Donde t=1,…,6 con t=1 Enero y t=6 Junio.

Función Objetivo: Minimizar los costos durante el período de planificación asociado a las remuneraciones, contratación, despido, compra y mantenimiento de inventario (respectivamente).

Restricciones:

Balance de Trabajadores: Por ejemplo la cantidad de trabajadores disponibles al final del mes de Marzo para labores de cosecha son aquellos que terminaron trabajando al final del mes de Febrero, más los contratados en el mes de Marzo y menos los despedidos en Marzo.

Satisfacer Demanda de Árboles: Donde $D_{t}$ representa la demanda (parámetros) de árboles para el mes t.

Capacidad Tala (Mano de Obra): Talar cada árbol requiere 4 horas hombre y un trabajador aporte 160 horas hombre en un mes. Luego, cada trabajador puede talar como máximo 40 árboles mensuales.

Capacidad Tala (Máquinas): Se puede talar como máximo 3.000 árboles mensuales dada la capacidad de las máquinas.

Capacidad Bodega: La bodega tiene una capacidad máxima de almacenamiento de 500 árboles.

No Negatividad y Enteros: Se deben satisfacer las condiciones de enteros para las variables de decisión no negativas.

Al implementar en Solver de Excel el modelo anterior se alcanza la solución óptima (celdas en color amarillo) con un valor óptimo de M$152.360.

Se recomienda al lector verificar que la solución alcanzada satisface las restricciones anteriormente expuestas. Notar adicionalmente que el plan óptimo actual no despide trabajadores durante la planificación y contrata trabajadores en Febrero y Abril (11 y 19, respectivamente), los mismos meses donde adicionalmente compra árboles (10 y 110) a la forestal cercana. Naturalmente al final de la planificación no existen incentivos para mantener árboles en bodega.

¿Quieres tener el archivo Excel con la implementación computacional de este ejemplo?

[sociallocker]

MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO

[/sociallocker]

Simulación de una Línea de Espera M/M/1 (Teoría de Colas) en Excel

Un sistema de espera M/M/1 es aquel que considera un servidor, con tiempos exponenciales de servicio y entre llegadas de clientes. La implicancia que los tiempos de servicio se distribuyan exponencial es que existe una preponderancia de tiempos de servicio menores al promedio combinados con algunos pocos tiempos extensos. Un ejemplo de ello es lo que sucede en las cajas de los bancos donde la mayoría de las transacciones requieren poco tiempo de proceso por parte del cajero, no obstante algunas transacciones más complejas consumen bastante tiempo. Por otra parte afirmar que los tiempos entre llegadas se distribuyen exponencial implica una preponderancia de tiempos entre llegadas menores que el promedio en combinación con algunos tiempos más extensos. Lo anterior tiene relación con la aleatoriedad del proceso de llegada de clientes que permite establecer la Propiedad de Falta de Memoria o Amnesia de la Distribución Exponencial y con los conceptos presentados en el artículo Qué son las Líneas de Espera (Teoría de Colas), donde queda en evidencia que la formación de las colas o filas esta asociada a la variabilidad del sistema.

En este contexto consideremos la siguiente notación, donde valores usuales para A y B son M (distribución exponencial) y G (distribución general).

El siguiente ejemplo disponible en el artículo Qué es la Ley de Little y su aplicación en el análisis de Líneas de Espera, nos permitirá ilustrar la simulación en Excel del comportamiento de un sistema de espera M/M/1.

Simulación de una Línea de Espera M/M/1

Ejemplo: Un pequeño banco está considerando abrir un servicio para que los clientes paguen desde su automóvil. Se estima que los clientes llegarán a una tasa promedio de 15 por hora (λ=15). El cajero que trabajará en la ventanilla puede atender a los clientes a un ritmo promedio de uno cada tres minutos (es decir, la capacidad promedio del servidor es de µ=20). Suponga que el patrón de llegadas es Poisson y el patrón de servicios es Exponencial.

Al hacer uso de la Planilla de Fórmulas de Sistema de Espera, se alcanzan los resultados que se resumen en la imagen a continuación.

Con esto la utilización promedio del servidor es de un 75%, el número esperado de clientes en el sistema Ls es 3, el número esperado de clientes en la fila Lq son 2,5, el tiempo promedio que un cliente permanece en el sistema Ws (espera más atención) son 0,2 horas (0 12 minutos) y el tiempo promedio que un cliente permanece en la fila Wq (esperando su atención) es de 0,15 horas (o 9 minutos).

Otra alternativa es simular el comportamiento de la línea de espera con configuración M/M/1 haciendo uso de Excel. Para ello ingresamos en la planilla Queueing_Simulator el número de servidores (1), la distribución para el tiempo entre llegadas (exponencial con media de 4 minutos, esto es, si llegan en promedio 15 clientes por hora, entonces en promedio llega un cliente cada 1/15 de hora o equivalentemente cada 4 minutos) y una distribución para el tiempo de servicio también exponencial con media de 3 minutos. Finalmente ingresamos el número de llegadas que se desea simular (arbitrariamente se ha seleccionado 100.000 llegadas para evaluar un comportamiento del sistema en el largo plazo) y luego Run Simulation.

Se puede apreciar que los resultados obtenidos en la columna F son (aproximadamente) similares a los obtenidos utilizando los resultados que establece la Ley de Little. Por ejemplo, el número esperado de clientes en el sistema L es 3,0157; el número esperado de clientes en la fila Lq es 2,2665; el tiempo esperado que un cliente permanece en el sistema W son 12,0612 minutos y así sucesivamente.

Importante: Los resultados mostrados anteriormente corresponden a aquellos obtenidos con una simulación tipo. Si una vez alcanzados los resultados presionamos nuevamente Run Simulation obtendremos cambios en los resultados los cuales de todos modos deberían aproximar los resultados de la Ley de Little bajo el supuesto de considerar un número significativo de llegadas a simular.

¿Quieres tener el archivo Excel con la Simulación de una Línea de Espera M/M/1 utilizada en este ejemplo?

[sociallocker]

MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO

[/sociallocker]

Cómo Calcular Cp y Cpk con el Complemento SPC for Excel

El Cálculo de los índices Cp y Cpk en el Control Estadístico de Procesos permite evaluar que tan bien el proceso se apega a las especificaciones técnicas deseadas o equivalentemente determinar si el proceso cumple el objetivo funcional para el cual fue diseñado. En el siguiente artículo mostraremos cómo obtener de forma rápida y sencilla dichos indicadores haciendo uso del complemento SPC for Excel, el cual puede ser descargado en el enlace anterior por un período de prueba de 20 días. Una vez que el complemento SPC for Excel haya sido correctamente descargado y activado será visible en en una de las pestañas del menú de Excel como se muestra (en un extracto) a continuación:

Para ilustrar su utilización consideremos la información relativa a un proceso del cual se tienen 15 muestras, cada una de ellas con 4 observaciones, donde se ha registrado la información de las lecturas en OHMS de cierto dispositivo electrónico. La especificación nominal o deseada del dispositivo es de 1.000 OHMS y se permite una variabilidad de +- 25 OHMS con lo cual se definen los límites de especificación.

A continuación seleccionamos la opción Cpk del menú de SPC for Excel (visible en la imagen anterior) y seleccionamos el rango de los datos. Notar que en el ejemplo la información cuantitativa de las muestras esta contenido en el rango que conforma la matriz de la celda C3 a la F17 (es decir, 60 celdas: 15 filas y 4 columnas). Adicionalmente ingresamos el Límite de Especificación Inferior (LEI) o Lower Specification Limit (LSL) de 975 OHMS (1.000 – 25) y Límite de Especificación Superior (LES) o Upper Specification Limit (USL) de 1.025 OHMS (1.000 + 25). Recordar que la especificación nominal es de 1.000 OHMS.

Una vez ingresados los datos atingentes al ejemplo y habilitando los resultados de capacidad (en su opción Yes) seleccionamos OK. Esto dará origen a una nueva hoja en nuestro archivo Excel con los resultados del análisis de capacidad tal como se muestra a continuación:

Observar que los resultados son consistentes con los alcanzados preliminarmente en el artículo al cual hacemos referencia al inicio de este tutorial. Adicionalmente se obtienen una serie de estadísticas complementarias que permite tener una visión más general del comportamiento del proceso.