UNIVERSIDAD MARIANO GALVEZ

MAESTRIA EN DIRECION Y GESTIÓN DEL RECURSO HUMANOCURSO MODELO PARA LA TOMA DE DECISIONESLIC. RODRIGO ZEBADÚA

SESIÓN 1TEMA: V Aplicación de Excel Solvert

ALUMNA: Danesy Maritza Maderos CARNET: 6028054172

Chiquimula 12 de marzo de 2014.

INTRODUCCION

La relevancia de los problemas de optimización en el mundoempresarial ha generado la introducción de herramientas deoptimización cada vez más sofisticadas en las últimas versiones delas hojas de cálculo de utilización generalizada. Estas utilidades,conocidas habitualmente como «solvers», constituyen unaalternativa a los programas especializados de optimización cuandono se trata de problemas de gran escala, presentado la ventaja de sufacilidad de uso y de comunicación con el usuario final. FrontlineSystems Inc es la empresa que desarrolla el «solver» de Excel, si bienexisten asimismo versiones para Lotus y Quattro Pro con ligerasdiferencias de uso. En su dirección de internet (www.frontsys.com)se puede obtener información técnica sobre las diferentes versionesde dicha utilidad y diversos aspectos operativos del programa,algunos de los cuales se comentan a continuación.

CONSTRUCCIÓN DE UN MODELO DE OPTIMIZACIÓN.

La introducción de un modelo de optimización, un programa lineal en nuestro ejemplo, se puede sintetizar en cuatro fases:

1. Organizar los datos del modelo en la hoja de trabajo. Si bien son múltipleslas posibles formas de diseñar el formato y colocación de los datos deentrada, es recomendable seguirlos mismos principios que en todaaplicación con hoja de cálculo: pensar en la hoja como un informe queexplique el problema, identificar los datos introducidos, colocarcomentarios, introducir todos los datos iniciales del problema y construir apartir de los mismos el modelo de optimización con el objeto de facilitar elanálisis de sensibilidad, utilizar técnicas de diseño para presentar elmodelo, etc. Por otra parte, interesa organizar el programa según elformato del gráfico I con el objeto de ilustrar la propia estructura delmodelo.

2. Reservar una celda para cada variable de decisión. Siguiendo el esquemade un programa matemático, es recomendable que inicien la hoja detrabajo. Deberán estar vacías o con datos numéricos, nunca fórmulas, y aser posible con notas o comentarios.

3. Crear una celda para la función objetivo próxima a las que recogen lasvariables. La fórmula que incorpora deberá crearse a partir de las celdasdescritas en el punto anterior.

DISEÑO DEL MODELOPara la utilización del software se debe diseñar un modelo del problema a resolver en la hoja de cálculo. Donde se especifique todos los parámetros necesarios para obtener una solución optima. Dicho modelo se presenta a continuación.

4. Para cada restricción, crear una celda que recoja la fórmula de su parteizquierda, y a la derecha de dicha celda colocar el términoindependiente. La estructura recomendable es la que se recoge en elgráfico I dado que permite reducir el trabajo en la fase de introduccióndel problema, facilita la detección de errores y simplifica su resolucióncon el «solver».

Luego se definen los parámetros del solver como se detalla en el grafico;de acuerdo al diseño elaborado en la hoja de cálculo.

Donde la función a optimizar puede ser del tipo maximizar ó minimizar ysu fórmula es la suma producto de la fila de los coeficientes decontribución multiplicado por la fila de las variables cambiante.

En las restricciones se agrega cada una de las restricciones definidas en elmodelo como aparece en el grafico y se agrega la restricción de nonegatividad para que los valores obtenidos sean positivos y se obtenganvalores negativos característica de estos modelos de programación lineal.

El anterior modelo que se debe plantear en la hoja de Excel es el que se debe llevar al solver para ser solucionado. Una vez se tiene el modelo se procede a abrir el solver:

Luego que se tiene definidos los parámetros procedemos a resolver elmodelo por medio del solver obteniéndose el siguiente resultado:

Al darle resolver sale el anterior grafico donde se detalla; primero que elsolver a encontrado una solución al problema satisfaciendo todas lascondiciones del modelo planteado, si no hubiese resultado favorablehabría sacado un mensaje de que el diseño del problema no teniasolución y habría que ajustarlo.

Segundo al tener solución presenta la opción de utilizar la solución óvolver al planteamiento inicial.

Tercero, si utilizamos la solución se presentan tres informes respuestas,sensibilidad y limites.

Los resultados de aplicar el solver son:

Donde la función objetivo su valor máximo optimo fue de $135.87 y losvalores de las variables para lograr esta solución fue de X1 = 5.2051 yX2 = 0; X3 = 12.7179 unidades del producto que se esté analizando enla solución, en cuanto a las restricciones tenemos que en la utilizaciónde los recursos se obtuvieron los siguientes resultados; del recurso b1= 85 se utilizaron todos los disponibles en el proceso de producción delrecurso b2 = 97 tan solo se utilizaron 46 existiendo un excedente de51 unidades de ese recurso que la empresas lo podrá utilizar encualquier otro proceso. En cuanto al recurso b3 = 74 se utilizo todo lodisponible por la empresa para el proceso de producción.En cuanto a los informes analizaremos primero el de resultados:

En cuanto a los informes analizaremos primero el de resultados:

El valor de la función objetivo es de $135.87 aparece valor original decero cuando se partió de un vértice inicial, y se llego a un valor final de$135.87, y en las celdas cambiantes para X1=5.205 y X2=12.7179 quese interpreta según problema pueden ser unidades a producir u otrotipo de producto.

En cuanto a las restricciones tenemos que el recurso b1=85 en el Excel2007 saca un mensaje en estado de obligatorio significa ello que esterecurso es indispensable para la solución obtenida;

en el caso del Excel 2010 se tiene que el mensaje estado es vinculantesignifica lo mismo que se explico para la anterior versión del Excel.Explica ello como se utilizaran los recursos durante la ejecución delproceso, implica que habrá necesidad de ajustar los recursos b2 porqueestos hay un excedente de 50.7443 unidades. En cuanto a los valores delcoeficiente de contribución de la función objetivo estos no permitenvariación a lo establecido para los productos X1 y X3 pero para elproducto X2 se puede reducir hasta cero, teniendo en cuenta que este noes demandado por los consumidores.

En cuanto al análisis de sensibilidad tenemos:

En la versión Excel 2007 se tiene los siguientes resultados como análisis desensibilidad. Donde el valor del coeficiente de contribución de la variableX1 es de 5.205 y se puede reducir hasta cero su costo, según Excel 2010 elvalor de ese coeficiente puede llegar a 9 y su aumento permisible es hasta10.818 y lo reducción permisible de 6.2. Para el producto x2 su coeficientees de cero teniendo en cuenta que no se produce y su costo se puedereducir hasta -3.051 el valor objetivo es de 5 quiere ello significar que laflexibilidad de su precio es nula, también podemos observar que su valorde aumento permisible 3.051 y su reducción es bastante significativa.

En cuanto a la variable X3 se tiene que su valor final del coeficiente decontribución es de 12.7179 y su costo de reducción es cero, su valor puedellegar a 7 y su aumento permisible puede ser 15.5 y su disminuciónpermisible 2.0169 ello demuestra la flexibilidad de este coeficiente acambios futuros.

En cuanto a los recursos disponibles se tiene lo siguiente:Para el recurso b2 se tiene que se utilizaron 46.2564 de los 97 que teníadisponible la empresa, que para dichos recursos no se tiene precio sombrapor cada unidad adicional que se produzca; en igual forma se tiene quedicho recurso se puede aumentar permisiblemente todo lo que sea, yreducir hasta 50.7435.

Del recurso b3 se utilizo todo lo disponible, sin embargo se tiene unprecio sombra por cada unidad adicional que se produzca comomínimo de 0.923 significa que si la empresa desea producir unidadesde ese producto debe como mínimo cobrar por cada una de estasunidades $0.923 pesos; este recurso se puede aumentar hasta 67.6666y disminuir hasta 55.1111 con el recurso b1 se utilizo todo en elproceso y se tiene como precio sombra por cada unidad adicional quese produzca de $0.7948 pesos; el aumento permisible de este recursoes de 123.6875 y la disminución permisible es de 40.6.

En cuanto a los límites tenemos:

Al observar este informe podemos observar que si no se coeficientede contribución a la variable X1 se tendría que la función objetivovariaría entre 89.02564 y si se considera se tendría un valor de$135.8717, en igual forma para las otras variables, como puedeobservar en el grafico del Excel 2010.

CONCLUSIONES

• Tal como lo menciona Joseph F. Aieta, “el algoritmo Microsoft ExcelSolver es una poderosa herramienta para la optimización y asignacióneficiente de recursos escasos (tierra, trabajo, capital, capacidadgerencial, etc.)”. Dicha herramienta permite conocer el mejor uso de susescasos recursos de tal manera que se cumplan las metas deseadas,tales como la maximización de los beneficios, o la minimización de loscostos.

• Los casos mas comunes trabajados con solver son:

CASOS DE MERCADEO PROYECTOS DE INVERSION CASOS DE PRODUCCION

RECOMENDACIONES

Esta herramienta fue creada para dar un gran

beneficio a todas las personas que necesiten

conocer resultados en pequeña escala; la cual es

de suma importancia el conocimiento, tanto en la

micro, nano y macro empresas.

APLICACION

La utilización de solvers es muy vasta, ya que nos ayuda aresolver un sin numero de casos o problemáticas tanto en locotidiano como en otros casos como por ejemplo:

CASOS DE MERCADO donde se muestra las ventas en funciónde los gastos en publicidad y de un factor de temporada .

PROYECTOS DE INVERSION donde se busca seleccionar dentrode un grupo de proyectos independientes, aquellos quemaximicen el valor presente neto, sin sobrepasar supresupuesto.

CASOS DE PRODUCCION donde una fábrica que producediferentes productos, que utilizan la misma mano de obra ymateriales pero en diferentes proporciones.

BIBLIOGRAFIA

ABADIE, J.(1978): «The GRG Method for Nonlinear Programming»en Greenberg, H.J.(Ed), Design and Implementation ofOptimization Software, Sijthoff and Noordhoof, pp. 325-363.

GOULD, F.J., G.D. EPPEN y C.P.SCHMIDT (1992): Investigación deoperaciones en laciencia administrativa. Prentice-Hall. México.

LASDON, L.S. y A.D. WAREN(1978): «Generalized ReducedGradient Software for Linearly and Nonlinearly ConstrainedProblems» en Greenberg, H.J.(Ed), Design and Implementation ofOptimization Software, Sijthoff and Noordhoof, pp. 363-397.

LASDON, L.S., A.D. WAREN, A. JAIN y M. RATNER(1978): «Designand Testing of a Generalized Reduced Gradient Code forNonlinear Constrained Programming», ACM Transactions onMathematical Software, vol. 4, pp. 34-50.

PLANE, D.R.(1994): Management Science. A spreadsheet Aproach.Body and Fraser. Danvers (Massachussetts).

RÍOS, S. (1988): Investigación operativa. Centro de EstudiosRamón Areces. Madrid.