Solver forma parte de una serie de
comandos denominados herramientas de análisis
Y si. Con Solver,
puede buscarse el valor óptimo para una fórmula de una celda, denominada celda objetivo, en una
hoja de cálculo. Solver trabaja con el grupo de celdas que estén relacionadas,
directa o indirectamente, con la fórmula de la celda objetivo. Esta función
ajusta los valores de las celdas variables que se
especifiquen, denominadas celdas ajustables, para obtener el resultado
especificado en la fórmula de la celda objetivo. Pueden aplicarse condiciones
para restringir los
valores que puede utilizar esta función en el modelo y las restricciones pueden
hacer referencia a otras celdas a las que afecte la fórmula de la celda
objetivo.
Ejemplo de utilización de
Solver en un estudio de mercado:
Para cada trimestre, se
tienen los factores de temporada:
Trimestre 1: 0,9
Trimestre 2: 1,1
Trimestre 3: 0,8
Trimestre 4: 1,2
Y los siguientes datos:
Unidades vendidas = 35*factor de temporada *
(publicidad+3000) ^ 0.5
Ingresos por ventas = precio del producto *
unidades vendidas.
Costo de las ventas = costo del producto *
unidades vendidas.
Margen bruto = Ingreso por ventas -
Costo de las ventas
Costo personal = 8000 los dos primeros
trimestres y 9000 los dos últimos.
Publicidad = 10000 {éstas serán las
variables controlables}
Costos fijos = 0.15 * Ingresos por
ventas
Costo total = Costo personal +
Publicidad + Costos fijos
Beneficio = Margen bruto - Costo
total.
Margen de beneficio = Beneficio / Ingresos por
ventas.
El precio de venta y el
costo son constantes a lo largo del año:
Precio del producto = 40
Costo del producto = 25
El objetivo es maximizar el
beneficio.
Estos datos se introducen en
Excel, como se muestra en la figura 1 (Ver también archivo adjunto, hoja con el
nombre Solver_situacion inicial)
Figura
1. Situación inicial sin utilizar la función Solver
Para optimizar la función
objetivo, se seguirán los siguientes pasos:
1. En la ficha Datos (Data), en el grupo Herramientas de Datos (Data Tools) en Análisis (What-If
Analysis),
hacer clic en Solver.
2. Si el comando Solver no está disponible, se deberá cargar el programa de
complemento (complemento: programa suplementario que agrega funciones o
comandos personalizados a Microsoft Office.) Solver. Para cargarlo, seguir los
siguientes pasos:
·
En Excel, hacer
clic en el botón
de Microsoft Office en la parte superior
izquierda, luego, hacer clic en Opciones de Excel (Excel Options) y, a continuación, hacer
clic en la categoría Complementos (Add-Ins).
·
En la parte
inferior de la pantalla, en la lista desplegable del campo Administrar (Manage), hacer clic en Complementos de Excel
(Excel Add-Ins) y, a continuación, en Ir… (Go…).
- En el cuadro
Complementos disponibles (Add-Ins
available), activar la casilla de
verificación Complemento
Solver (Solver Add-In) y,
a continuación, hacer clic en Aceptar (Ok).
3. En el cuadro Celda objetivo (Set Target Cell), escribir la referencia a
la celda F15 o seleccionar la celda. Siempre la celda objetivo debe
contener una fórmula.
4. Seleccionar la opción Máximo (Max), que es la que se quiere para este ejemplo, pero puede
seleccionarse cualquier otra según corresponda con lo que se desea.
5. En el cuadro Cambiando celdas (By Changing Cells), escribir B11:E11, que son
las celdas cambiantes para este ejemplo (el presupuesto publicitario de cada
uno de los cuatro trimestres que serán los valores que se quieren utilizar para
maximizar el beneficio). La función Solver permite hasta 200 celdas
cambiantes.
6. Si se activa la casilla de verificación Adoptar un modelo lineal
(Assume Linear Model) en el cuadro de diálogo Opciones (Options), no habrá límite en el
número de restricciones. En problemas no lineales, cada celda ajustable puede
tener hasta 100 restricciones, además de límites y restricciones enteras en las
variables.
7. Hacer clic en Resolver (Solve) y seguir uno de los siguientes procedimientos:
§ Para mantener los valores de la solución en la hoja de cálculo,
hacer clic en Utilizar solución de Solver (Keep Solver Solution) en el cuadro de diálogo Resultados de Solver
(Solver Results).
§ Para restaurar los datos originales, hacer clic en Restaurar valores
originales (Restore Original Values).
§ Para crear un informe basado en la solución, después de que Solver
encuentre una, se puede hacer clic en un tipo de informe en el cuadro Informes (Reports) y, a continuación, hacer
clic en un tipo de informe. El informe se crea en una nueva hoja de cálculo del
libro. Si Solver no encuentra una solución, la opción de crear un informe no
estará disponible.
§ Para guardar los valores de celda ajustados como un escenario que
se puedan mostrar más tarde, hacer clic en Guardar escenario (Save Scenario) en el cuadro de diálogo Resultados de Solver y, a continuación, escriba
un nombre para el escenario en el cuadro Nombre del escenario (Scenario Name).
El resultado de esta
optimización sin restricciones muestra que se pueden aumentar los beneficios
durante el año a $79705,62 si se gastan $89705,59 en publicidad durante el año,
distribuido a en los trimestres, como se muestra en la figura 2.
Figura 2. Solver aplicado a la función objetivo
Partiendo del ejemplo
original, según la figura 1, es posible suponer que se desea mantener el
presupuesto original de publicidad en $40000. Para agregar esta
restricción:
1. Ir nuevamente a la ficha Datos (Data), en el grupo Herramientas de Datos
(Data Tools) en Análisis (What-If Analysis), hacer clic en Solver.
2. En el cuadro de diálogo, en la opción de restricciones, se pueden
realizar tres tareas: agregar una restricción, modificarla o eliminarla.
Al seleccionar
Agregar
(Add),
se puede seguir uno de los procedimientos siguientes:
·
Para aceptar
una restricción y agregar otra, hacer clic en Agregar.
·
Para aceptar la
restricción y volver al cuadro de diálogo Parámetros de Solver (Solver Parameters), hacer clic en Aceptar.
·
Solamente
pueden aplicarse las relaciones int y bin en las restricciones en celdas ajustables.
3. Hacer clic en Resolver (Solve). Las otras opciones del cuadro de diálogo, aplican también para
las restricciones.
Figura
3. Solución de Solver con restricción
Desplazarse por las
soluciones de prueba en Solver
La función Solver permite
desplazarse en las diferentes soluciones que brinda para un determinado
problema, para verlas, seguir el siguiente procedimiento:
1. Después de definir un problema, hacer clic en Opciones (Options) en el cuadro de diálogo Parámetros de Solver.
2. En el cuadro de diálogo Opciones de Solver (Options Solver), activar la casilla de
verificación Mostrar resultado de iteraciones (Show Iteration Results) para ver los valores de
cada solución de prueba y, a continuación, hacer clic en Aceptar.
3. En el cuadro de diálogo Parámetros de Solver, hacer clic en Resolver (Solver).
4. En el cuadro de diálogo Mostrar solución de prueba (Show Trial
Solution),
seguir uno de los procedimientos siguientes:
·
Para detener el
proceso de solución y ver el cuadro de diálogo Resultados de Solver, hacer clic en Detener (Stop).
·
Para continuar
el proceso de solución y ver la siguiente solución de prueba, hacer clic en Continuar (Continue).
§
Cambiar la forma en que
Solver encuentra soluciones
1. En el cuadro de diálogo Parámetros de Solver, hacer clic en Opciones.
2. En el cuadro de diálogo Opciones de Solver, elegir una o más de las
siguientes opciones:
Tiempo de
solución e iteraciones
·
En el cuadro Tiempo máximo (Max Time), escribir el número de
segundos que se desea permitir como tiempo de solución.
·
En el cuadro Iteraciones (Iterations), escribir el número máximo
de iteraciones que se desea permitir.
Grado de
precisión
·
En el cuadro Precisión (Precision), escribir el grado de
precisión que se desee. Cuanto menor sea el número, mayor será la precisión.
Tolerancia
de enteros
·
En el cuadro Tolerancia (Tolerance), escribir el porcentaje de
error que se desea permitir en la solución.
Grado de
convergencia
·
En el cuadro Convergencia (Convergence), escribir la cantidad de
cambio relativo que se desea permitir en las últimas cinco iteraciones antes de
que se detenga Solver con una solución. Cuanto menor sea el número, menor será el
cambio relativo que se permite.
3. Hacer clic en Aceptar.
4. En el cuadro de diálogo Parámetros de Solver, hacer clic en Resolver (Solver) o en Cerrar.
La herramienta Microsoft
Office Excel Solver utiliza el código de optimización no lineal (GRG2). Los
problemas lineales y enteros utilizan el método más simple con límites en las
variables y el método de ramificación y límite
0 comentarios:
Publicar un comentario