viernes, 28 de noviembre de 2014

FUNCIÓN SOLVER. LA PROGRAMACIÓN LINEAL CON EXCEL



                La programación lineal consiste en optimizar una función de varias variables (es decir, obtener su valor máximo o mínimo) teniendo en cuenta una serie de restricciones de sus variables.

                Vamos a poner un ejemplo muy sencillo en dos variables que luego desarrollaremos con esta herramienta de Excel. Este problema está tomado de la asignatura de matemáticas de 2º de bachillerato de ciencias sociales.

Imaginemos que en  una confitería se dispone de 24 kg. de polvorones y 15 kg. de mantecados que se tratan de envasar en dos tipos de cajas de la siguiente forma:

- Caja 1: contiene 200 gr. de polvorones y 100 gr. de mantecados
- Caja 2: 200 gr. de polvorones y 300 gr. de mantecados  

El precio de venta es de 400 ptas. los polvorones y de 600 ptas. los mantecados 

            El problema a resolver consiste en averiguar cuántas cajas de cada tipo se tendrán que preparar y vender para obtener el máximo de ingresos.

                Como se ve se trata de optimizar la función de ingresos, que en nuestro caso será:
I = 400·x + 600·y
Siendo:

  •    I la función de ingresos.
  •    X el número de cajas de polvorones a envasar.
  • Y el número de cajas de mantecados a envasar.

Sin embargo tenemos cuatro restricciones:

1.                  200·x + 200·y <= 24000 a la que denominamos R1. Es decir, el número total de polvorones no ha de superar los 24 kg., o los 24.000 gr.

Cascada en Tam-Dao. Vietnam
2.                  100·x + 300·y <= 15000 a la que denominamos  R2.  En este caso, el número total de mantecados no ha de superar los 15 kg., o los 15.000 gr.

3.                  El número de cajas de polvorones ha de ser mayor que 0.

4.                  El número de cajas de mantecados ha de ser mayor que 0.

La forma tradicional de resolverlo a mano es plantear las inecuaciones, ver el recinto en el que se puede encontrar la solución, encontrar los vértices de ese recinto y determinar el punto en el que la función beneficio se hace máxima. En este caso es relativamente sencillo al tener la función beneficio sólo dos variables y se puede hacer incluso gráficamente, el problema se plantea cuando tenemos más de dos variables.

El problema se puede resolver fácilmente con la función SOLVER de Excel, la cual ha de estar activada.

Las instrucciones de activación y la resolución del problema paso a paso se encuentran en el libro de Excel siguiente:  “PROGRAMACION_LINEAL"

Se ha pretendido hacer un ejemplo sencillo, pero que se puede complicar con más restricciones o haciendo que las funciones no sean lineales. Para la comprensión básica considero que es suficiente.

Espero que se entienda perfectamente, de otro modo no dudéis en consultarme.


lunes, 24 de noviembre de 2014

FORMULAS FINANCIERAS EN EXCEL (I). CÁLCULO DEL TAE.INT.EFECTIVO

      
  Como es sabido existen gran cantidad de fórmulas financieras en Excel,  muchas de las cuales no se usan habitualmente por desconocimiento de las mismas, ya que su nombre no facilita su comprensión para posibles usos.

          Vamos a empezar una serie de artículos que tratarán de explicar la mayoría de ellas. Comencemos con un cálculo muy sencillo que es el de la TASA A NUAL EQUIVALENTE o TAE.(en la literatura anglosajona se conoce como TIN). Para ello vamos a usar la fórmula de Excel denominada:

INT.EFECTIVO

          Se aplica este ratio generalmente cuando los bancos pagan intereses en varios periodos a lo largo del año, con un interés anual nominal. Como veremos no es lo mismo que un banco me pague un interés del 3% anual si me da los intereses al final del año que si me los da repartidos al final de cada trimestre.
            La suma del dinero es la misma, pero se intuye que es más favorable disponer de cada una de esas cuartas partes de los intereses por adelantado.
           La fórmula que se emplea es:



TAE = [(1 + i/(100*n))n - 1] * 100

Siendo:
·         i el interés nominal anual
·         n el número de periodos al año en el que nos pagan los intereses
Presa en Río Ba. Vietnam

A continuación se expone el cálculo para un interés anual nominal de un 1% con los periodos de pago más habituales. Se realiza el cálculo tanto con la fórmula de Excel INT.EFECTIVO como con una fórmula de cálculo preparada por nosotros.
El resultado es el que se muestra a continuación, juntamente con las fórmulas empleadas

El cálculo para otros valores es inmediato. os periodos se han colocado en la columna B, desde la fila 5 a la 10, y el interés anual en la  B12, que es la que se hace fija mediante $.

Se puede acceder a la hoja de cálculo a través del siguiente enlace:



PERIODOPAGOS AL AÑOINT.EFECTIVOFORMULA
MENSUAL121,0046%1,0046%
BIMENSUAL61,0042%1,0042%
TRIMESTRAL41,0038%1,0038%
CUATRIMESTRAL31,0033%1,0033%
SEMESTRAL21,0025%1,0025%
ANUAL11,0000%1,0000%


INTERÉS ANUAL1%




PERIODO
PAGOS AL AÑOINT.EFECTIVOFORMULA
MENSUAL12=INT.EFECTIVO($B$12;B5)=+((1+$B$12/B5)^B5-1)
BIMENSUAL6=INT.EFECTIVO($B$12;B6)=+((1+$B$12/B6)^B6-1)
TRIMESTRAL4=INT.EFECTIVO($B$12;B7)=+((1+$B$12/B7)^B7-1)
CUATRIMESTRAL3=INT.EFECTIVO($B$12;B8)=+((1+$B$12/B8)^B8-1)
SEMESTRAL2=INT.EFECTIVO($B$12;B9)=+((1+$B$12/B9)^B9-1)
ANUAL1=INT.EFECTIVO($B$12;B10)=+((1+$B$12/B10)^B10-1)
INTERÉS ANUAL0,01