Analisis y Si - Solver


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.


Algoritmos y métodos utilizados por Solver

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


Analisis y Si - Tabla de Datos

Análisis y Si

Tabla de datos

Una tabla de datos es un rango de celdas que muestra cómo el cambio de una o dos variables en las  fórmulas, afectará los resultados de las mismas. Las tablas de datos constituyen un método rápido para calcular varios resultados en una operación y una forma de ver y comparar los resultados de todas las variaciones diferentes en la hoja de cálculo.

Por ejemplo, se puede usar una tabla de datos de una variable para ver cómo diferentes tipos de interés afectan al pago mensual de una hipoteca mediante la función PAGO (PMT). Si se tienen los valores de variables en una columna o fila, los resultados aparecerán en la columna o fila adyacente.

Siguiendo el ejemplo del pago mensual de una hipoteca, se tienen los siguientes datos:
 Figura 1. Tabla de datos de una variable

1.    En la celda D2, copiar la fórmula de pago =PMT(B3/12,B4,-B5), que hace referencia a la celda de entrada B3.

·         Una tabla de datos de una variable tiene valores de entrada que se enumeran en una columna (orientado a columnas) o en una fila (orientado a filas). Las fórmulas que se utilizan en una tabla de datos de una variable deben hacer referencia a sólo una celda de entrada, en este ejemplo, se refiere a la celda B3 (celda de entrada: celda en la que se sustituye cada valor de entrada de una tabla de datos. Cualquier celda de una hoja de cálculo puede ser la celda de entrada. Aunque no es necesario que la celda de entrada sea parte de la tabla de datos, las fórmulas de las tablas de datos deben hacer referencia a la celda de entrada.).
·         Si la tabla de datos está orientada a columnas (los valores de variables se encuentran en una columna), escribir la fórmula en la celda situada una fila más arriba y una celda a la derecha de la columna de valores. La figura 1 de la tabla de datos de una variable está orientada a columnas, y la fórmula está en la celda D2.
·         Si la tabla de datos está orientada a filas (los valores de variables se encuentran en una fila), escribir la fórmula en la celda situada una columna a la izquierda del primer valor y una celda más abajo de la fila de valores.

2.    Escribir la lista de valores que se desea sustituir en la celda de entrada, en una columna o en una fila. Para mostrar los resultados, se deben dejar unas cuantas filas y columnas vacías en cualquiera de los lados de los valores.

3.    Seleccionar el rango de celdas que contiene la fórmula y los valores que se desean sustituir. Según la figura 1, este rango es C2:D5.

4.    En el grupo Herramientas de datos (Data Tools) de la ficha Datos (Data), hacer clic en Análisis Y si (What-If Analysis) y, a continuación, en Tabla de datos (Data Table). Para este ejemplo, que la tabla está orientada a columnas, la celda de entrada es B3.

5.    Hacer clic en Aceptar (Ok).



Agregar una fórmula a una tabla de datos de una variable

1.    Las nuevas fórmulas que se agreguen en una tabla de datos de una variable deberán hacer referencia a la misma celda de entrada:

·         Si la tabla de datos está orientada a columnas, escribir la fórmula nueva en una celda en blanco a la derecha de una fórmula existente en la fila superior de la tabla de datos.
·         Si la tabla de datos está orientada a filas, escribir la fórmula nueva en una celda en blanco debajo de una fórmula existente en la primera columna de la tabla de datos.

2.    Seleccionar el rango de celdas que contiene la tabla de datos y la fórmula nueva.

3.    En el grupo Herramientas de datos de la ficha Datos, hacer clic en Análisis Y Si y, a continuación, en Tabla de datos.

4.    Seguir uno de los procedimientos siguientes:

·         Si la tabla de datos está orientada a columnas, escribir la referencia de celda para la celda de entrada en el cuadro Celda de entrada (columna).
·         Si la tabla de datos está orientada por filas, escribir la referencia de celda de la celda de entrada en el cuadro Celda de entrada (fila).



Tabla de datos de dos variables 

Una tabla de datos de dos variables usa una fórmula que contiene dos listas de valores de entrada,  puede utilizarse para ver cómo diferentes valores de dos variables en una fórmula cambiarán los resultados de la misma. La fórmula debe hacer referencia a dos celdas de entrada diferentes.

Por ejemplo, se puede usar una tabla de datos de dos variables para ver cómo diferentes combinaciones de tipos de interés y términos de préstamos afectarán al pago mensual de una hipoteca.

En la figura 2, la celda C2 contiene la fórmula de pago =PMT(B3/12,B4,-B5), que usa dos celdas de entrada: B3 y B4.

Figura 2. Tabla de datos de dos variables

Siguiendo el ejemplo del pago mensual de una hipoteca con dos condiciones de entrada:

1.    En una celda de la hoja de cálculo, escribir la fórmula que haga referencia a las dos celdas de entrada.

En el ejemplo, los valores iniciales de la fórmula se especifican en las celdas B3, B4 y B5. En la celda C2, escribir la fórmula =PMT(B3/12,B4,-B5)

2.    Escribir una lista de valores de entrada en la misma columna, debajo de la fórmula.

En este caso, escribir los diferentes tipos de interés en las celdas C3, C4 y C5.

3.    Especificar la segunda lista en la misma fila que la fórmula, a la derecha.

Escribir los términos de préstamo (en meses) en las celdas D2 y E2.

4.    Seleccionar el rango de celdas que contiene la fórmula en C2, tanto la fila como la columna de valores (C3:C5 y D2:E2) y las celdas en las que se desea los valores calculados (D3:E5).

En este caso, seleccionar el rango C2:E5.

5.    En el grupo Herramientas de datos de la ficha Datos, hacer clic en Análisis Y Si y, a continuación, en Tabla de datos.

·         En el cuadro Celda de entrada de fila (Row input cell), escribir la referencia de la celda de entrada para los valores de entrada de la fila. Para este ejemplo, sería la celda B4.
·         En el cuadro Celda de entrada de columna (Column input cell), escribir la referencia de la celda de entrada para los valores de entrada de la columna. Para  este ejemplo, sería la celda B3.

6.    Hacer clic en Aceptar.



Cálculos de las tablas de datos  

Las tablas de datos se actualizan cuando se actualiza una hoja de cálculo, aunque no tengan cambios. Para acelerar el cálculo de una hoja de cálculo que contenga una tabla de datos, pueden cambiarse las opciones de calcular para que se actualice automáticamente la hoja de cálculo pero no las tablas de datos.

Para acelerar los cálculos:

1.    Hacer clic en el botón de Microsoft Office, luego en Opciones de Excel (Excel Options) y, a continuación en la categoría Fórmulas (Formulas).

2.    En la sección Opciones de cálculo (Calculation Options), bajo Cálculo de libro (Workbook Calculation), hacer clic en Automático excepto para tablas de datos (Automatic except for data tables).

Cuando se selecciona esta opción de cálculo, las tablas de datos se omiten al actualizar el resto del libro. Para actualizar manualmente las tablas de datos, seleccionar las fórmulas y, a continuación, presionar F9.


Nota: Se adjunta archivo con ejemplo de Tabla de datos con una y con dos variables.

Analisis y Si - Busqueda de Objetivo

Búsqueda de objetivo

Con la Búsqueda de objetivo, se puede determinar cuáles deben ser los valores de entrada de una fórmula para obtener un resultado específico.

Tomando como ejemplo el caso de pedir un préstamo, se puede partir de que se conoce la cantidad de dinero que se desea, el período en el cual se devolverá el préstamo y cuánto se puede pagar cada mes.  La función Búsqueda de objetivo podría utilizarse para determinar qué tipo de interés debe aplicarse para cumplir con lo planteado para el préstamo.
Para el ejemplo, se tienen los siguientes datos:


·   Cantidad de dinero que se desea: 100000 dólares
·   Período en el cual se devolverá el préstamo: 180 meses
·         Pago por mes: 900 dólares


Para determinar el tipo de interés, utilizando la función Búsqueda de objetivo, se debe ingresar en la pestaña Datos (Data), en el grupo de Herramientas de datos (Data Tools) y  hacer clic en Análisis y Si (What-If Analysis), luego clic en Búsqueda de objetivo (Goal Seek).


En el campo Definir la celda (Set Cell), ingresar la referencia para la celda que contiene la fórmula que se quiere resolver. En el ejemplo, es la celda B4, que contiene la fórmula PAGO(B3/12;B2;B1), para Excel en inglés, la fórmula es PMT.

En el campo Con el valor (To value), ingresar el resultado final que se quiere que dé la fórmula. Para este ejemplo es -900. En este caso, es negativo pues representa un pago.

En el campo Para cambiar la celda (By changing cell), ingresar la referencia de la celda que contiene el valor que se quiere ajustar, en este caso, la celda B3.

La búsqueda de objetivo se genera y produce un resultado, como se muestra en la siguiente figura:





NOTA: La Búsqueda de objetivo funciona con un solo valor de entrada de variable. Si se desea determinar más de un valor de entrada, por ejemplo, la cantidad del préstamo y el importe de pago mensual del préstamo, se deberá usar el complemento Solver.

Introduccion a Analisis y Si

En Excel, se incluyen tres tipos de herramientas de Análisis y Si: Escenarios, Tablas de datos y Búsqueda de objetivo.

El Análisis y Si es el proceso de cambiar los valores de las celdas para ver cómo los cambios afectarán el resultado de fórmulas de la hoja de cálculo.

Los Escenarios y las Tablas de Datos toman conjuntos de valores de entrada y determinan posibles resultados. Una Tabla de Datos funciona únicamente con una o dos variables, pero puede aceptar muchos valores diferentes para estas variables. Un Escenario puede tener muchas variables, pero puede acomodar hasta 32 valores.

La Búsqueda de Objetivo funciona de forma distinta a los Escenarios y las Tablas de Datos ya que toma un resultado y determina los valores de entrada posibles que producen ese resultado.

Además de estas tres herramientas, se pueden instalar complementos que ayudarán a realizar Análisis y Si, como el complemento Solver. Este complemento es similar a la Búsqueda de Objetivo, pero pueden ingresarse más variables.

En este tip, se describirá la herramienta Escenarios, en las próximas semanas se verán las otras herramientas.


ESCENARIOS
Esta herramienta permitirá analizar las variaciones de determinadas variables sobre unos resultados finales o intermedios dentro de un informe.
Un Escenario es un conjunto de valores que Excel guarda y sustituye en un informe. Se utilizarán los escenarios para observar la variación en el resultado de un modelo.
Con esta herramienta, se tiene la posibilidad de crear y guardar diferentes grupos de valores, en distintos escenarios y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados.
Se ingresa a esta herramienta desde el menú Datos (Data), Herramientas (Data Tools) > Escenarios (What-If Analysis), activando el Administrador de escenarios (Scenario Manager). Lógicamente, el paso previo será disponer de un modelo en la hoja de cálculo sobre el que analizar la variación de algunas celdas resultantes según la variabilidad de otras celdas vinculadas.
Se propone el siguiente modelo presupuestario, sencillo, para explicar el uso de los escenarios (se adjunta el archivo con el ejemplo).
En este ejemplo se dispone de una distribución de gastos clasificados como fijos y variables, así como unos ingresos que dependen del número de unidades vendidas y su precio unitario, todos ellos estimados para un ejercicio. Seguramente cualquier financiero necesitara analizar en qué forma varían el total de gastos o ingresos, o simplemente el resultado final de acuerdo a la modificación de algunas de las premisas establecidas en este primer modelo.
En primer lugar se activará el Administrador de Escenarios y se creará un Escenario que tendrá el nombre de Inicial, y que resume la situación tal cual se ha planteado, es decir, se considerarán los valores asignados en el informe como una Situación inicial.
Es muy importante tener claro desde un principio cuáles serán las variables a estudiar que en uno u otro escenario, actual o futuro, se vayan a generar; ya que es muy útil para realizar las comparaciones entre los diferentes escenarios.
Desde el administrador, se podrán agregar todos los Escenarios que se necesiten (se podrá ejecutar este administrador de manera independiente en cada Hoja de cálculo del libro).
Los pasos son:
1.     Dar un nombre al Escenario.
2.     Identificar las celdas cambiantes, es decir, cuáles son las variables de estudio.
3.     Detallar, si es necesario, una descripción del escenario creado.
4.     Y por último asignar valores a esas variables.




Del mismo modo, se agregan al Administrador tantos Escenarios como sean necesarios.


Para este ejemplo, se crearán cuatro escenarios, un Escenario Inicial, un Escenario Optimista, un Escenario Pesimista y un Escenario Realista; en todos ellos se analizará cómo cambian los resultados en función de la variación de los Costos Fijos del 3T/2010 y del precio unitario de cada unidad vendida del mismo trimestre.
Una vez generados los Escenarios, se podrán visualizar sobre el modelo la variabilidad definida.
Por último, es posible ver en una hoja resumen todos los Escenarios con sus distintos valores por cada variable definida, y cómo afecta a las celdas de resultado seleccionadas. Se podrá tener acceso a esta opción desde el Administrador de Escenarios, al presionar el botón Resumen (Summary).
Es posible aprovechar la funcionalidad de asignar nombres a rangos para dar nombre a las celdas cambiantes (variables) y las celdas de resultado, con el fin de identificar en la hoja resumen qué información corresponde a que. Existen dos tipos de informes, sin embargo, el que más puede aclarar las situaciones, es el tipo Resumen, en vez del tipo Tabla dinámica.