¿Cómo Utilizar Solver en Excel?

Solver es una “herramienta de análisis de hipótesis que busca el valor óptimo de una celda objetivo cambiando los valores de las celdas usadas para calcular la celda objetivo”. (Microsoft Excel 2010). En otras palabras, Solver es útil para resolver problemas matemáticos, sujetos a restricciones, los cuales pueden ser expresados en Excel.

 

¿Cómo habilitar Solver en Excel 2010?

Ir a “Archivo” y seleccionar “Opciones de Excel” (en algunos casos aparece sólo “Opciones”), donde se desplegará una ventana, en la cual deben hacer click en Complementos y luego ir a Complementos de Excel como se muestra en la figura. 

Luego, aparecerá una nueva ventana donde deberán seleccionar Solver.

Para poder utilizar la herramienta, deben ir a datos y al final de la fila en “Análisis” encontrarán Solver.

¿Cómo usar Solver?

Antes de poder utilizar la herramienta, se debe tener claro cuál es el problema que se quiere resolver, cuáles son las restricciones y cómo se debe expresar en Excel. Además, de poder distinguir entre los parámetros del problema (valores fijos) y las variables (que cambian según los parámetros, y sus valores influyen en el valor final de la función objetivo).

Por lo tanto, para poder simplificar la explicación de cómo usar Solver, utilizaremos un ejemplo de una empresa que vende dos productos y que enfrenta el siguiente problema de Maximización de Utilidad:

Donde X1 y X2 son la cantidad de cada productos.

El cual se puede expresar de la siguiente manera en Excel:

Dado que la incógnita que debe resolver la empresa es cuál es la cantidad necesaria que se debe producir por cada producto para maximizar la utilidad, estas celdas deben permanecer vacías, dado que Solver “trabajará” en ellas para poder encontrar una solución óptima.

Luego de escribir el problema en nuestra Hoja de Cálculo, vamos a la herramienta de Solver para “decirle a Excel” qué rol cumple cada una de nuestras celdas.

Para poder agregar una nueva restricción, se debe hacer click en “Agregar” y aparecerá una nueva ventana, en donde se debe señalar la celda a la cual se hace referencia, la relación (menor, mayor, igual, etc…) y cuál es el valor de referencia que debe cumplir, el cual puede ser numérico o una celda, como muestra la figura. Sin embargo, es recomendable hacer la relación a una celda que posea el valor numérico para hacer análisis de sensibilidad con mayor facilidad.

Luego de agregar todas las restricciones, se debe seleccionar qué Método de Resolución utilizaremos, lo cual estará definido según el problema que queramos resolver:

• ·         Para problemas Solver No Lineales Suavizados, se debe utilizar la opción GRG Nonlinear

• ·         Para problemas Solver Lineales, se debe utilizar la opción Simplex LP. (Nuestro caso)

• ·         Para problemas Solver No Suavizados, se debe utilizar la opción Evolutionary.

Es importante destacar que Solver no asume la No Negatividad de las Variables, por lo que es recomendable agregarlo como restricción o seleccionar la opción Convertir las Variables Sin Restricción en No Negativas.

Y finalmente, ates de resolver el problema, se debe ir a Opciones, donde se desplegará la siguiente ventana:

Donde le puedes pedir a Solver cómo funcionar, es decir, número de iteraciones a realizar (intentos de solución), precisión de las restricciones, tiempo máximo que se puede demorar en resolver el problema, etc…

Luego de haber especificado todo lo necesario, hacemos click en Resolver, y nos aparecerá una venta como la figura.

Para poder conocer el valor de esta solución, seleccionamos Conservar Solución de Solver. Además, podemos guardar el escenario, es decir, la solución que encontró Solver en este primer intento. Y finalmente, Solver da la opción de generar informes, para lo cual se debe seleccionar la opción de Informes de Esquema.

Finalmente, luego de seleccionar lo que se necesite, se debe hacer click en Aceptar, y Solver mostrará la solución en nuestra Hoja de Cálculo, tal como lo muestra la figura.

Limitantes de Solver

Solver sólo puede resolver problemas de hasta 200 variables de decisión (celdas vacías), 400 restricciones de cota (inferior, superior, igualdad, etc…) y 100 restricciones explícitas, lo cual debe tomarse en cuenta a la hora de querer resolver un problema.

Función Objetivo v/s Solver

Antes de continuar, cabe destacar que la función Buscar Objetivo, cumple una función similar, sin embargo, cuando se trabaja con más de una variable es  recomendable trabajar con Solver.

Links Recomendados

Les recomiendo estos dos videos (http://www.youtube.com/watch?v=400NVJF80b4  y http://www.youtube.com/watch?v=j_nS6YReiN0&feature=related ), donde el segundo muestra las diferencias entre Solver y la Función Objetivo, los cuales son herramientas útiles para resolver problemas, sin embargo, dependiendo de la situación una es mejor que la otra. Los dos videos explica muy bien el procedimiento que se debe seguir para resolver un problema con Solver.

Además, les dejo un link de Bit Uchile (http://bituchile.com/2012/04/nuevo-apunte-de-solver-bit/) donde se explica cómo utilizar Solver con un ejemplo más largo y más detallado.

Leer más...

Como se puede usar Solver para determinación de cartera financiera

Solver para determinación de cartera financiera

¿Qué es solver?

Solver es una herramienta que ayuda a resolver y optimizar ecuaciones mediante el uso de métodos matemáticos, basandose en la programacion lineal

Primero que nada es necesario agregar o activar el complemento SOLVER, ya que en la mayoría de los excel no viene activado, por lo que se debe hacer lo siguiente:

Hacer click en el botón de Microsoft Office y, a continuación, hacer click en Opciones de Excel.

Hacer click en Complementos y, en el cuadro Administrar, seleccionar Complementos de Excel.

Hacer click en Ir.

En el cuadro Complementos disponibles, activar la casilla de verificación Complemento Solver y, a continuación, hacer click en Aceptar.

• Sugerencia, si Complemento Solver no aparece en la lista del cuadro Complementos disponibles, hacer click en Examinar para buscar el complemento.
• Si se indica que el complemento Solver no está instalado actualmente en el equipo, hacer click en Sí para instalarlo.
• Una vez cargado el complemento Solver, el comando Solver estará disponible en el grupo Análisis de la ficha Datos.

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

Ejemplo para ver cómo usar "SOLVER"

Pepito es presidente de una empresa de inversiones que se dedica a administrar las carteras de acciones de varios clientes Un nuevo clientes 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

Ahora veremos cómo crear un modelo de programacion lineal para mostrar cuántas acciones de cada empresa tendría que comprar Pepito con el fin de maximizar el rendimiento anual total estimado de esa cartera, es decir el optimo.

• Para solucionar este problema debemos seguir los pasos para la construcción de modelos de programación lineal:
  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

• 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 ponermos 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 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 copiamos 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.

Qué especificar dentro del cuadro 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 se debe apretar en el icono de "Opciones" y hacer clic en "Asumir modelo lineal" y enseguida el botón de "Aceptar". Luego hacer clic en el botón de "Resolver" para realizar la optimización. Hay que leer mensaje 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 se podrá aceptar o no dicha solución, luego se podrá analizar un informe de análisis de sensibilidad para  tomar la mejor decisión de la cartera de inversiones que estamos evaluando.

Finalmente vemos que en el optimo, Pepito debría comprar 750 acciones de Navesa, 1000 acciones de Telectricidad, y 1500 acciones de Rampa. generando una utilidad de 12750

Como conclución podemos ver que excel es una rápida y eficaz herramienta para poder analizar la compra de activos de renta variable para poder optimizar nuestra cartera de inversion, a través de la cual podemos obtener que cantidad de acciones o que porcentaje de nuestro capital invertir en cada una de ellas.

Leer más...

Diseño y gestión de plantillas para formularios e informes.

Los formularios son las interfaces que se utilizan para trabajar con los datos y, a menudo, contienen botones de comando que ejecutan diversas tareas. Presentan todos los datos de tablas o consultas de un registro en forma de ficha, de esta manera se pueden realizar todas las operaciones habituales con registros, como añadir, modificar o eliminar datos de una manera más cómoda.

Los informes sirven para resumir y presentar los datos de las tablas y consultas de forma personalizada, en vez de entregarlos tal como se tienen almacenados. En ellos se pueden incluir gráficos y totales automáticos. Cada informe se puede diseñar para presentar la información de la mejor manera posible. Un informe se puede ejecutar en cualquier momento y siempre reflejará los datos actualizados de la base de datos. Los informes suelen tener un formato que permita imprimirlos, pero también se pueden consultar en la pantalla, exportar a otro programa o enviar por correo electrónico.

Es posible encontrar las opciones asociadas a cada uno en la pestaña de crear, como se ve a continuación:

Ya fueron vistos los casos de generación de informes y formularios, así como su personalización a niveles empresariales. Pero cuando se desea crear un formulario o informe sin utilizar un asistente, Access utiliza una plantilla para definir las características predefinidas del formulario o informe.

¿Qué es una plantilla de Access? Es un archivo que, al abrirla, crea una aplicación de base de datos completa. La base de datos está preparada para usarse y contiene todas las tablas, formularios, informes, consultas, macros y relaciones que necesita para empezar a trabajar. Debido a que las plantillas están diseñadas como soluciones de base de datos completas de principio a fin, ahorran tiempo y esfuerzo, y permiten comenzar a usar directamente la base de datos. Después de crear una base de datos mediante una plantilla, puede personalizarla para adaptarla a sus necesidades, como si la hubiera creado desde cero.

Access ofrece varias plantillas de bases de datos diseñadas de manera profesional. Cada plantilla crea una solución completa descentralizada que puede usar sin modificaciones o personalizar para adaptarla a sus necesidades. Además, es posible descargar de forma fácil otras plantillas del sitio web de Microsoft Office Online haciendo clic en los vínculos dentro de Access. Después de seleccionar una plantilla y de personalizarla para adaptarla a sus necesidades, puede agregar datos y comenzar la navegación por los registros.

La plantilla determina qué secciones tendrá un formulario o un informe y define además las dimensiones de cada sección. La plantilla también contiene todos los valores predeterminados de las propiedades del formulario o informe, así como sus secciones y controles. Sin embargo, una plantilla no crea controles en un nuevo formulario o informe.

La plantilla predeterminada de los formularios e informes se denomina Normal. Sin embargo, se puede utilizar cualquier formulario o informe ya existente como plantilla. También puede crear un formulario o informe para utilizarlo como plantilla. El cambiar la plantilla no tiene ningún efecto sobre los formularios o informes existentes.

Access guarda los valores para las opciones Plantilla para formulario y Plantilla para informe en el archivo de información del grupo de trabajo de Microsoft Access, no en su base de datos de Microsoft Access (el archivo .mdb) o proyecto de Microsoft Access (el archivo .adp). Cuando se cambia un valor de una opción, el cambio se aplica a cualquier base de datos o proyecto de Access que se abra o se cree.

Si las plantillas no están en una base de datos de Access o en un proyecto de Access, Access utiliza la plantilla Normal para cualquier formulario o informe de nueva creación. No obstante, los nombres de las plantillas aparecen en las opciones Plantilla para formulario y Plantilla para informe de cada base de datos o proyecto de Access del sistema de base de datos, incluso aunque las plantillas no estén en todas las bases de datos o proyectos de Access.

Para crear una plantilla:

• Crear una nueva base de datos
• Importar o crear los objetos que se deseen incluir en la plantilla

Luego de incluir los objetos que se desee en la plantilla, deberá guardarse en una ubicación específica.

1.- Hacer clic en el botón de Microsoft Office y, a continuación, seleccione Guardar como.

2.- En Guardar la base de datos en otro formato, haga clic en el formato de archivo que desee para la plantilla.

3.- En el cuadro de diálogo Guardar como, vaya a una de estas dos carpetas de plantillas:

• Carpeta de plantillas del sistema Por ejemplo, C:\Archivos de programa\Microsoft Office\Plantillas\3082\Access
• Carpeta de plantillas personales Por ejemplo:
• En Microsoft Windows Vista c:\Users\nombre de usuario\Documents
• En Microsoft Windows Server 2003 o Microsoft Windows XP C:\Documents and Settings\nombre de usuario\Application Data\Microsoft\Plantillas

4.- En el cuadro Nombre de archivo, escriba el nombre que desee y, a continuación, haga clic en Guardar.

Ahora que la nueva plantilla tiene una ubicación, los objetos de la plantilla se incluirán de forma predeterminada en todas las bases de datos que cree.

Leer más...

Cómo imprimir informes en Access

Lo más sencillo sería presionar el botón imprimir de la barra de acceso rápido, o del menú de Microsoft Office. Pero les tengo una nueva forma, a través de macros y botones en el mismo informe, que hará más sencilla la impresión.

Una vez que ya tenemos nuestros informes, el primer paso es crear la macro de lo que queramos hacer, que en este caso es imprimir.

 Como ven, podemos predefinir el rango de páginas, si queremos páginas entremedio del informe, la calidad de impresión y el número de copias.

Luego vamos a nuestro informe y seleccionamos la vista de diseño

Ya en la vista diseño,  seleccionamos botón de los controles.


 Aparecerá un puntero de arrastre. Arrastramos y formamoes el botón, al cual nombramos "IMPRIMIR"

Ahora hacemos click con el botón derecho del mouse y seleccionamos construir evento

 Aparece una ventana que nos da opciones de lo que qeuremos construir. Escogemos la opción de Macro

 Nos aparece el constructor de macros. en las opciones de la acción, escogemos Correr macro. Abajo nos da a elegir la macro que qeuremos correr, y obviamente elegimos IMPRIMIR.

Guardamos, cerramos, y volvemos al informe.
Debemos volver a la vista Informe

Le damos al botón IMPRIMIR y... voilá! Se comienza la impresión.

Me imagino que este proceso ha resultado largo y no se le ve la utilidad...
Pero si que la tiene.
Imaginen ahora otro informe, el cual queremos imprimir con los mismos atributos que el anterior. Aquí se ve la utilidad.
Abrimos el nuevo informe, seleccionamos la macro Imprimir de la columna izquierda, la arrastramos al informe (siempre en vista diseño para agregar y/o quitar cosas), volvemos a la vista informe, le damos al botón imprimir y se imprimirá con las mismas características que el anterior.

Así podemos hacer con todos los informes, creando distintas macros para cada necesidad de impresión, haciendo así más eficiente el proceso de imprimir en vez de seleccionar cada vez las propiedades de impresión.

Leer más...

Personalizacion de Informes a niveles empresariales

Personalización de Informes a niveles empresariales

Ya visto la personalización de informes según el requerimiento de cada usuario, según el nivel de datos en los cuales puede tener acceso (pinchar acá) es también importante fijarnos en la estética del mismo, ya que un informe correctamente legible y con un diseño amigable, genera un valor agregado, tanto para quien lee este informe y para quien lo realiza.

Para la creación de informes es recomendable ver el siguiente link (http://www.computacionynegocios.info/2012/04/generacion-de-informes-en-access.html), en donde ya se ha logrado generar el informe necesario según los requerimientos especificados por los interesados en el reporte.

Comenzando la personalización del informe

Para comenzar, una vez realizado el informe, debe ser cambiado en su diseño desde la sección de informes, y específicamente en diseño de informe.

Luego de ello, es que tenemos el formato base de cómo está realizado el informe en cuestión, desde su nombre hasta los datos que han sido requeridos dentro del mismo. El informe es dividido en tres partes.

• Encabezado
• Datos o cuerpo del informe 
• Pie del informe.

Encabezado: en el encabezado se encuentra principalmente el titulo del informe, pero además existen distintas opciones las cuales nosotros podemos ir personalizando, aquí presentare algunos.

  Logotipo: esta opción nos sirve para poder insertar un logo, el cual nosotros deseamos,principalmente podemos colocar el logo de la empresa o de la universidad (por mencionar algunos)

 

Foto de busqueda de imagen

  Logo insertado

Si bien es el elemento más importante o destacado a incluir en esta sección, también tenemos la posibilidad de insertar otros elementos como botones, checkbox, etc.

Cuerpo: aquí es el elemento principal son los datos a entregar y donde no es necesario caer en detalle al ver que ocurre cuando se cambian los datos a presentar, para poder ingresar datos, podemos ir a la pestaña que nos dice “agregar campos existentes”. También aquí podemos cambiar el orden de presentación. Nota: al cambiar el cuadro derecho, si aparece el texto “independiente”, no se mostrara el campo requerido al momento de entregar el informe.

Pie: al igual que el encabezado, no es una sección en donde se puede “jugar” mucho con el core del informe, los datos. Pero también es necesario mencionarla ya que tiene elementos relevantes como son el numero de pagina y la fecha de generación del reporte (se incluye la fecha de la ultima modificación)

Auto Formato: en esta opción de auto formato, nosotros podemos generar diseños ya incluidos en Access, con colores, letras y tamaños de las mismas previamente definidas.

Otras funcionalidades y características, es poder modificar la orientación de la página (vertical u horizontal), modificar los márgenes entre otras cosas.

Finalmente, podemos decir que si bien es algo muy simple de realizar y que para lograr una mayor expertiz en la modificación de informes, es necesario ir descubriendo todas las cosas que nos puede ofrecer Access, que si bien a primera vista es algo “tosco” e incluso poco entendible, esconde detrás una gran potencialidad en cuanto a la gestión de información en las organizaciones

Leer más...