El poder de la hoja de calculo de Excel
El poder de la hoja de calculo de Excel
Indice
1. Introducción
2. Ejemplo de cómo usar "SOLVER"
1. Introducción
![](\"multimedia/hojaexcel01.gif\")
En estos tiempos donde se habla de la tecnología, información, sociedad del
conocimiento, etc., aprovecho la oportunidad de describir lo poderosa que es la
hoja de cálculo de excel, pero voy a referirme en particular a una de las
herramientas la cual se denomina Solver, y se puede ubicar en el menú principal
en la opción Herramientas, al pulsar este icono aparecerán varias opciones y ahí
encontraran dicha instrucción, ella resuelve problemas lineales y enteros
utilizando el método más simple con límites en las variables y el método de
ramificación y límite, implantado por John Watson y Dan Fylstra de Frontline
Systems, Inc. Es de hacer notar que estos problemas se presentan en las ciencias
administrativas y es requisito indispensable en casi todas las áreas de ciencias
sociales, ingeniería, y en cualquiera de las carreras universitarias como
Ciencias Estadísticas, Economía, Administración, entre otras, allí se estudia en
una cátedra llamada Investigación de Operaciones, en ella se construyen modelos
para el análisis y la toma de decisiones administrativas, los cuales en tiempos
remotos se utilizaban algoritmos muy complejos entre ellos el del método simplex
y el dual, estas técnicas manualmente son complejas, pero con la tecnología
aparecieron softwares para resolver sendos problemas entre ellos se encuentra el
más conocido que es el "LINDO", pero hoy tenemos la oportunidad de resolverlos
muy fácilmente mediante la hoja de cálculo de excel y el paquete agregado
llamado "SOLVER" que optimiza los modelos sujetos a restricciones, como los
modelos de programación lineal y no lineales, la cual permite obtener las
soluciones óptimas para un modelo determinado, y dependiendo de los niveles de
la organización se tomen las mejores decisiones para resolver los conflictos de
una empresa.
2. Ejemplo de cómo usar "SOLVER"
Andrés Z. Es presidente de una microempresa de inversiones que se dedica a
administrar las carteras de acciones de varios clientes. Un nuevo cliente ha
solicitado que la compañía se haga cargo de administrar para él una cartera de
100.000$. A ese cliente le agradaría restringir la cartera a una mezcla de tres
tipos de acciones únicamente, como podemos apreciar en la siguiente tabla.
Formule usted un modelo de Programación Lineal para mostrar cuántas acciones de
cada tipo tendría que comprar Andrés con el fin de maximizar el rendimiento
anual total estimado de esa cartera.
Acciones
Precio ($)
Rendimiento
Anual Estimado por Acción ($)
Inversión
Posible ($)
Navesa
60
7
60.000
Telectricidad
25
3
25.000
Rampa
20
3
30.000
Para solucionar este problema debemos seguir los pasos para la construcción
de modelos de programación lineal (PL):
1.- Definir la variable de decisión.
2.- Definir la función objetivo.
3.- Definir las restricciones.
Luego construimos el modelo:
MAX Z = 7X1 + 3X2 + 3X3
S.A.:
60X1 +25X2 + 20X3 <= 100.000
60X1 <= 60.000
25X2 <= 25.000
20X3 <= 30.000
Xi >= 0
A continuación se construye el modelo en una hoja de cálculo de excel de la
siguiente manera:
![](\"multimedia/hojaexcel02.gif\")
En la fila 2 se coloca la variable de decisión la cual es el número de
acciones y sus valores desde la B2 hasta la D2.
En la fila 3 el rendimiento anual y sus valores desde B3 hasta D3.
En la celda E3 colocaremos una formula la cual nos va indicar el rendimiento
anual total, =sumaproducto($B$2:$D$2;B3:D3).
Desde la fila B5 hasta la D8 colocaremos los coeficientes que acompañan a las
variables de decisión que componen las restricciones.
Desde la E5 hasta la E8 se encuentra la función de restricción (LI) y no es mas
que utilizar la siguiente formula =sumaproducto($B$2:$D$2;B5:D5) la cual se
alojaría en la celda E5, luego daríamos un copy hasta la E8.
Desde la F5 hasta F8 se encuentran los valores de las restricciones.
Desde la G5 hasta G8 se encuentra la holgura o excedente.
Una vez completada la hoja de cálculo con el modelo respectivo ¡GRABE SU HOJA!,
y seleccione "Solver?" en el menú de "Herramientas", ahí tendrá que especificar
dentro del cuadro de dialogo de Solver:
- La celda que va a optimizar
- Las celdas cambiantes
- Las restricciones
Así tendremos la siguiente pantalla
![](\"multimedia/hojaexcel03.gif\")
Como se puede observar en la celda objetivo se coloca la celda que se quiere
optimizar, en las celdas cambiantes las variables de decisión y por último se
debe de complementar con las restricciones. Una vez realizado estos pasos deben
pulsar el icono de "Opciones" y debe hacer clic en "Asumir modelo lineal" y
enseguida el botón de "Aceptar". Luego haga clic en el botón de "Resolver" para
realizar la optimización, lea detenidamente el mensaje de terminación de Solver
y ahí observará si se encontró una solución o hay que modificar el modelo, en
caso de haber encontrado una solución óptima usted podrá aceptar o no dicha
solución, luego tendrá oportunidad de analizar un informe de análisis de
sensibilidad para luego tomar la mejor decisión.
![](\"multimedia/hojaexcel04.gif\")
En nuestro ejemplo el máximo rendimiento anual fue de 12750$, y la cantidad
de acciones a comprar serían 750, 1000 y 1500 para Navesa, Telectricidad y Rampa
respectivamente. De está forma podemos observar la potencia que tiene el solver,
para mayor información sobre el tema, en la ayuda de la hoja de cálculo de excel
o en libro de Investigación de Operaciones en la Ciencias Administrativas,
autor: Eppen quinta edición, Editorial Prentice Hall tendrán una mayor
explicación.
Trabajo enviado por:
Fredy A. Zavarce C
Estadístico
fgzavarce@yahoo.es