El Poder de la Hoja de Cálculo de Excel

Indice
1.
Introducción
2. Ejemplo de cómo usar
"SOLVER"

1.
Introducción

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.

Proceso de construcción de modelos
1- Definir variables de decisión
2- Definir la función de
objetivos
3- Definir las restricciones

Utilidad o perdida = PX – CX – F
MAX Z = PX – CX – F
S.A

Donde:
P= Precio
C= Costo
X= Utilidades vendidas
F= Costo
fijo

X<= U
X<= D
X<= O

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.

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:

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:

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.

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.

Autor:

Fredy A. Zavarce C

Estadístico