Soluciones dinámicas
Soluciones dinámicas pretende u herramientas basadas en análisis de sensibilidad para optimizar la toma de decisiones, el análisis de datos, la comparación de información con el objetivo de encontrar mejores resultados.
Buscar Objetivo
Buscar objetivo permite que un valor de una celda cambie a un valor exacto modificando otro valor.
Ejemplo: En este modelo se realizó una función financiera (Pago) para hallar el valor de la cuota de un préstamo.
La intención es poder definir el valor de la cuota en 400.000 (es un valor exacto), pero esto es posible alterando otro valor que para el ejemplo puede ser el Monto, Plazo o Interés.
Cambiando el monto el resultado sería:
Utilizar Buscar Objetivo
Crear una tabla con la valores que al cambiar afecten el resultado de las formulas o simplemente un tabla donde cambien los valores.
Antes de utilizar buscar objetivo es importante saber que la celda objetivo debe estar formulada con otras para que de esta manera Excel pueda realizarlo
1. Seleccionar la celda con el valor a cambiar. Para el ejemplo la celda donde esta 569.757.48 que es la celda que tendrá el valor exacto de 400.000
2. Hacer clic en la ficha datos, grupo herramientas de datos opción
Análisis y si y buscar objetivo.
b. Con el valor: Es el valor exacto para ajustarse.
c. Para cambiar la celda: Es una celda que ajustara su valor para que la celda objetivo se cumpla. Para el ejemplo es el monto 15.000.000
Al pulsar Aceptar Excel genera un reporte indicando en cuanto quedo el valor del objetivo, si el usuario pulsa aceptar los cambio se realizan directamente para evitar que los cambio se realicen, pulsar Cancelar.
Escenarios
Los escenarios en Excel permiten guardar un conjunto de datos y volver a cargarlo de forma automática, se utiliza para comparar valores utilizando una misma tabla.
El siguiente ilustra una cotización con tres proveedores utilizando la misma tabla para determinar la mejor opción.
Excel guarda los valores por unidad de cada producto y el nombre del proveedor, la tabla contiene las formulas y totaliza a medida que el valor cambia.
Crear un escenario
1. Crear una tabla con la valores que al cambiar afecten el resultado de las formulas ó simplemente un tabla donde cambien los valores.
Al cambiar las celdas con valores diferentes, se modifica el resultado. 2. Hacer clic en la ficha datos, grupo herramientas, análisis y si y a
continuación Administrador de escenarios.
3. Hacer clic en botón Agregar.
Nombre del escenario: Definir el nombre que identifique los datos
actuales.
valores que cambian para cada caso.
4. Pulsar Aceptar y Excel muestra una ventana con la referencia de las celdas cambiantes y sus valores actuales.
En este ejemplo no muestra referencias de celdas sino el nombre de cada producto, esto se debe a que cada celda cambiante se nombró como el producto de la izquierda.
5. Una vez agregado el escenario pulsar cerrar.
6. modificar las celdas cambiantes con valores nuevos.
Mostrar escenarios
Una vez se han creado varios escenarios, es posible cargar los valores de cada uno utilizando la misma tabla de Excel.
1. Hacer clic en la ficha datos, grupo herramientas de datos y a continuación Análisis y Si, elegir la opción administrador de escenarios.
2. Seleccionar el escenario que desea pre visualizar y pulsar el botón mostrar ó 2 clic sobre el nombre del escenario.
3. Excel muestra directamente en la tabla los datos del escenario.
Modificar o eliminar escenarios
Es posible modificar los valores de un escenario o eliminar escenarios por completo.
1. Ingresar al administrador de escenarios.
2. Seleccionar el escenario y pulsar el botón eliminar, el escenario desaparece de la lista y no existe forma de recuperarlo.
3. Para modificar los datos se debe seleccionar el escenario 4. Pulsar el botón modificar
5. Seleccionar las nuevas celdas o pulsar aceptar para modificar los valores de forma manual.
Resumen Escenarios
Excel puede generar un resumen automático en el que compara todas las tablas una lectura fácil y rápida.
2. Ingresar al administrador de escenarios. 3. Pulsar el botón Resumen.
4. Elegir el tipo de informe, el tipo resumen muestra una tabla en la que compara todos los escenarios incluyendo detalles mientras que el informe de tabla dinámica compara los valores únicamente.
Solver
Es una herramienta que permite en una celda con fórmula buscar un valor óptimo según un grupo de celdas relacionadas con la fórmula, de esta forma se halla la mejor solución a un problema mediante
restricciones. Solver entonces ajusta las celdas cambiantes para dar el
resultado especificado en el valor objetivo, teniendo en cuenta todas sus restricciones.
Antes de utilizar solver es necesario para algunos computadores instalar el complemento
Habilitar Solver
Solver es un programa que está disponible cuando instala Microsoft Office o Excel. Sin embargo, para ser utilizado en Excel primero se debe cargar. Para ello, seguir los siguientes pasos:
1. Hacer clic sobre la ficha Archivo y luego clic en el botón opciones.
2. Hacer clic en Complementos, en el cuadro Administrar, seleccionar
4. En el cuadro Complementos disponibles, activar la casilla de verificación Complemento Solver, a continuación, hacer clic en
Aceptar.
5. La opción Solver aparecen en la ficha datos, grupo Análisis y solver.
Utilizar Solver
Para utilizar Solver es necesario tener un problema planteado y conectado con fórmulas para que Excel lo pueda resolver:
Ejemplo:
Este es un consolidado de ventas de un almacén que mantiene el mismo promedio de ventas para los últimos tres meses, la empresa necesita aumentar sus utilidades a 100.000.000 para lo cual pide un plan de acción.
Los planteamientos para resolver el problema pueden ser varios:
Disminuir Costos Mercancía.
Disminuir Gastos Administrativos.
Aumentar las unidades vendidas o el valor unitario.
Todos en diferentes proporciones.
1. Es necesario tener un esquema de fórmulas en las que se relacionan todos los valores que pueden cambiar y un valor objetivo.
2. Ingresar a la herramienta Solver (Ficha datos – Grupo Análisis – Solver)
Establecer Objetivo: Especificar una celda con fórmula a la que se
desee calcular el mínimo valor, el máximo valor o un valor específico.
Valor de: Activar el botón de opción de acuerdo al objetivo, si se desea
calcular un valor específico, digitarlo en el cuadro de texto.
Estas celdas deben estar relacionadas directa o indirectamente en la fórmula de la celda objetivo, y deben ser celdas sin fórmulas.
Sujeto a las restricciones: Muestra el listado de las restricciones
agregadas. Para agregarlas, dar clic en el botón Agregar y especificar la restricción.
Agregar: En Referencia de la celda, definir la celda a la que se va a
aplicar la restricción, a continuación definir el operador de comparación, <=, =, ó >= y especificar la restricción en el cuadro, ya sea una celda o un valor.
Para cambiar una condición, se debe seleccionar y dar clic en el botón
cambiar, y de la misma manera, seleccionar una restricción y dar clic en
el botón eliminar, si se desea quitar una restricción.
Resolver:Pulsar este botón una vez esten definidos los parametros del
Conservar Solución de Solver: Al dar clic en Aceptar con ese botón
de opción activo, Solver modificará los valores en la hoja de cálculo sin poder deshacerse.
Restaurar valores originales: Solver mantiene los valores iniciales en
la hoja de cálculo.
Guardar escenario: Los nuevos valores sugeridos por solver serán
guardados como escenario, de manera que puedan ser analizados más adelante.
Informes: Existen tres tipos de informe, informe de respuesta,
sensibilidad y límite. Para el informe de respuesta, solver crea una nueva hoja mostrando los valores originales y los valores con la solución tanto
cambiante y de la celda objetivo, y además, muestra el valor del límite inferior y límite superior de cada celda cambiante y cuál sería en tal caso, el valor objetivo.