análisis de sensibilidad – solver escenarios2
TRANSCRIPT
ANÁLISIS DE SENSIBILIDAD – SOLVER - ESCENARIOS
CAMILLETTI ALICIA
ANÁLISIS DE SENSIBILIDAD
Las herramientas de análisis de sensibilidad nos permiten crear un modelo y, utilizando este como base, realizar cambios a datos claves y verificar como afectan estos cambios al modelo.
Excel nos presenta, básicamente, tres herramientas que podemos ubicar dentro de este grupo:
EscenariosEscenariosBuscar ObjetivosBuscar Objetivos
SolverSolver
ANÁLISIS DE SENSIBILIDAD
Primer paso para reconocer la incertidumbre. Nos interesa examinar como el cambio en una variable afecta a un resultado. Esto permite identificar las variables más críticas o construir escenarios posibles que permitirán analizar el comportamiento de un resultado bajo diferentes supuestos.
ESCENARIOS Podemos utilizar los escenarios para
prever el resultado de un modelo de hoja de cálculo, ante variaciones en los valores de una o más celdas.
Aplicando cada escenario se verá el resultado de usar esos valores.
Esto nos permitirá comparar diferentes situaciones y optar por la mejor.
ESCENARIOS EjemploEconomíadoméstica
(B2/12) (B3-B4)
B7*(B8/12)/(100*(1-(1/(1+((B8/12)/100))^(B9*12))))
(B5-B10)
ESCENARIOS Elegimos la opción
Escenarios del menú Herramientas y agregamos un escenario.
Indicamos las celdas cuyo contenido puede variar:Ingresos AnualesGastos Fijos MensualesInterés
ESCENARIOS
Una vez creados los escenarios podemos utilizar el Administrador de escenarios para aplicar los valores en la hoja de cálculo.
ESCENARIOS
MEJOR CASO
ESCENARIOS
PEOR CASO
ESCENARIOS
Cuando disponemos de varios escenarios, podemos utilizarel Administrador de escenarios para obtener un resumen(o tabla dinámica) que informa de los casos previstos:
SOLVER
El Solver es la herramienta principal del análisis de sensibilidad, optimiza los modelos sujetos a restricciones, como los modelos de programación lineal y no lineales.
Además, se puede indicar que se quiere que el resultado sea máximo o mínimo, en lugar de un resultado concreto.
SOLVER
Ejemplo: Personal de Mantenimiento Cada empleado trabaja 5 días seguidos y descansa 2. El salario, de todos los empleados es de $ 40 diarios La cantidad de empleados necesaria según el día es:
LUNES
MARTES MIÉRCOLES
JUEVES
VIERNES
SÁBADO
DOMINGO
17 12 14 15 18 24 22
Debemos calcular cuantos empleados de cada horario cubren las necesidades de todos los días. Y, también queremos que el gasto de personal sea el menor posible (problema de minimización)
SOLVER Tenemos
siete horarios distintos (identificados de la A a la G). En cada horario se descansa unos días distintos
HORARIOS DÍAS DE DESCANSO
A Lunes - Martes
B Martes - Miércoles
C Miércoles - Jueves
D Jueves - Viernes
E Viernes - Sábado
F Sábado - Domingo
G Domingo - Lunes
SOLVER
Para calcular los empleados que hay en total cada día creamos una matriz de horarios y días que contenga en cada posición un 1 si ese día se trabaja con ese horario y un 0 en caso contrario:HORARIOS LUNES MARTE
SMIÉRCOLE
SJUEVES VIERNES SÁBADO DOMINGO
A 0 0 1 1 1 1 1
B 1 0 0 1 1 1 1
C 1 1 0 0 1 1 1
D 1 1 1 0 0 1 1
E 1 1 1 1 0 0 1
F 1 1 1 1 1 0 0
G 0 1 1 1 1 1 0
SOLVER
A partir de la matriz y el número de empleados de cada horario se calcula fácilmente el total de empleados de cada día:
HORARIOS
DÍAS DE DESCANSO
EMPLEADOS
LUNES
MARTES
MIÉRCOLE
S
JUEVES
VIERNES
SÁBADO
DOMINGO
A Lunes - Martes 4 0 0 1 1 1 1 1
B Martes - Miércoles
4 1 0 0 1 1 1 1
C Miércoles - Jueves
4 1 1 0 0 1 1 1
D Jueves - Viernes 6 1 1 1 0 0 1 1
E Viernes - Sábado 6 1 1 1 1 0 0 1
F Sábado - Domingo
4 1 1 1 1 1 0 0
G Domingo - Lunes 4 0 1 1 1 1 1 0
TOTALES 32 24 24 24 22 20 22 24
SOLVERVALORES A ENCONTRAR
VALOR A MINIMIZAR
RESTRICCIONES
($D$3*E3)+($D$4*E4)+($D$5*E5)+($D$6*E6)+($D$7*E7)+($D$8*E8)+($D$9*E9)
(E11+F11+G11+H11+I11+J11+K11)*D15
(D3+D4+D5+D6+D7+D8+D9)
SOLVER
Haciendo clic sobre el botón Resolver, obtendremos la ventana de Resultados:
SOLVER
Elegimos la opción Respuestas
SOLVER
VALOR ÓPTIMO
SOLUCIÓN ÓPTIMA
En este caso Solver pudo encontrar una solución. En nuestro ejemplo, el informe de respuestas se mostraría así: