jueves, 23 de septiembre de 2010

EJEMPLOS Y APLICACIONES

A continuación se encuentran algunos ejemplos de cómo llevar una expresión matemática a su correspondiente fórmula en Excel. Es el usuario del Excel quien debe encontrar la forma correcta de expresar la fórmula para luego ingresarla al Excel, ya que éste solamente se encarga de realizar las operaciones o cálculos, según se lo indique el usuario mediante la fórmula ingresada, y en ningún momento el Excel le va a indicar al usuario que el valor obtenido está correcto,  es decir,  que este valor sería exactamente igual al  que se obtendría al calcular de forma manual la expresión matemática  correspondiente.
a.  Escribir la fórmula para  la siguiente expresión matemática e indique el orden cómo  se debe evaluar dicha fórmula para  calcular el valor correspondiente. La  fórmula en Excel se debe  ingresar en la celda D3, con el valor de A en la celda D1,  el valor de  B en la celda E1 y el valor de  C en la celda F1.
         
Como primer paso, antes de ingresar la fórmula en la hoja de cálculo, el usuario debe escribir está expresión matemática como una fórmula que se pueda utilizar en la mayoría de los lenguajes de programación o en la mayoría de los programas de aplicación como  es el caso del Excel. Siendo La primera dificultad  escribir la fórmula en una sola línea,  donde no se refleja el orden en que estamos acostumbrados a realizar las operaciones cuando el cálculo lo realizamos de forma manual. Entonces, la fórmula la podríamos escribir como:

            A*B/RAIZ(C)/C-1/B-2*C/A

 Si en esta fórmula analizamos  el orden de evaluación que se tiene establecido para cada uno de los operadores[4], evaluando los diferentes operadores de izquierda a derecha de la fórmula, y aplicando para cada operador el orden que se tiene establecido para cada uno de éstos, como se muestra en la tabla de precedencia de los operadores que se encuentra  en la página 4. Además se debe tener presente que cuando en una fórmula se tienen varios operadores del mismo orden jerárquico, como por ejemplo dos o más multiplicaciones o una multiplicación y una o más  divisiones, primero se realizará el cálculo que se corresponde al operador mas a la izquierda y luego se continuará con los otros operadores.

En nuestro caso, al aplicar el procedimiento descrito anteriormente,  tenemos lo siguiente:

            A*B/RAIZ(C)/C-1/B-2*C/A
         2  3      1    4     5    . . . . . . . . .

Vemos que el resultado que se obtendría al calcular la fórmula, tal cual se ha escrito, estaría errado; ya que la división entre C, indicada por la operación 4, se estaría realizando fuera de orden y, por lo tanto, alteraría  el resultado; por lo que se deben insertar paréntesis para cambiar este orden (primero se debe  calcular  C-1/B y luego  realizar la división). Entonces al agregar los paréntesis, la fórmula quedará como:

            A*B/RAIZ(C)/(C-1/B)-2*C/A
        4  5    3       6   2  1  9 7  8

En esta fórmula, al realizar el usuario la evaluación del orden de los operadores, primero se evalúan todos los operadores que están dentro de los paréntesis y después los operadores que están fuera de los paréntesis. Esta fórmula, tal cual está escrita, calcula correctamente el  valor de la expresión matemática, ya que las operaciones se realizan en el orden requerido o necesario, que se corresponde con uno de los  varios ordenes en que se pueden realizar los cálculos de forma manual. Una expresión matemática para ser utilizada en el computador puede ser escrita de varias formas equivalentes, ya que producen el mismo resultado final cambiando solamente el orden en que se realizan las operaciones.

En este ejemplo podemos tener las siguientes formas de escritura de la fórmula que calculan correctamente el valor correspondiente a la expresión matemática indicada.

 Forma 1:                 A*B/RAIZ(C)/(C-1/B)-2*C/A           (forma básica)
                             4  5    3       6   2  1  9 7  8
Forma 2:                 (A*B/RAIZ(C))/(C-1/B)-2*C/A
                             2  3     1        6  5  4  9  7  8
Forma 3:                 (A*B/RAIZ(C))/(C-1/B)-(2*C/A)
                             2  3     1        8  5  4  9   6  7
Forma 4:                 ((A*B/RAIZ(C))/(C-1/B))-(2*C/A)
                               2  3     1       6   5 4   9   7  8

Como se indicó en el enunciado del ejercicio, la fórmula en Excel correspondiente debe ser ingresada en la celda D3, con el valor de A en la celda D1,  el valor de  B en la celda E1 y el valor de  C en la celda F1. Al hacer los cambios aquí señalados, podemos entonces escribir las  correspondientes fórmulas en Excel.

Forma 1:                         =D1*E1/RAIZ(F1)/(F1-1/E1)-2*F1/D1
Forma 2:                         =(D1*E1/RAIZ(F1))/(F1-1/E1)-2*F1/D1
Forma 3:                         =(D1*E1/RAIZ(F1))/(F1-1/E1)-(2*F1/D1)
Forma 4:                         =((D1*E1/RAIZ(F1))/(F1-1/E1))-(2*F1/D1)

Sí ingresamos en la celda D3  la  primera fórmula, utilizando 1 como valor de A (celda D1), 3 como valor de B (celda E1)  y  4 como valor de C  (celda F1), se obtiene como resultado    el valor   -7,59090909

Si se ingresan en otras celdas de la hoja de cálculo las otras tres fórmulas (por ejemplo en las celdas D4, D5 y D6), se observará que el  resultado que obtiene el Excel es el mismo para las cuatro fórmulas. Entonces, la única diferencia que hay entre estas cuatro formas de expresar correctamente la fórmula en Excel correspondiente a la expresión matemática que se está estudiando,  es el orden en que el Excel realiza los cálculos en cada una de ellas.

Para observar el orden que sigue el Excel en el cálculo de las fórmulas, se utiliza la herramienta Auditoría de fórmulas opción  Evaluar Fórmula, siguiendo  el siguiente procedimiento:

-          Seleccionar la celda donde está la fórmula
-          Luego abrir el menú Herramientas
-          Seleccionar la opción Auditoría de fórmulas
-          Del nuevo menú que se presenta seleccionar Evaluar fórmula
-          Para que se vaya realizando cada  operación paso a paso presionar el botón Evaluar
-          Para finalizar presionar el botón Cerrar

En nuestro caso si se manda a Evaluar las fórmulas  en cada una de las celdas donde se ingresaron, se observa que el orden en que el Excel realiza los cálculos es diferente en cada de estas fórmulas, siendo la forma 4,  la que tiene un orden de cálculo mas semejante al orden que nos sirvió para llegar a la escritura correcta de la fórmula.

La utilización de la herramienta Auditoría de fórmulas opción  Evaluar Fórmula, es muy útil cuando se está implementando una expresión matemática compleja, ya que permite observar como Excel  está realizando los cálculos y, a partir de esto, determinar si una operación se está realizando fuera de orden lo que llevaría a cálculos errados.


b.  Escribir  la fórmula para  la siguiente expresión matemática e indique el orden cómo  se debe evaluar dicha fórmula para  calcular el valor correspondiente. La  fórmula en Excel se debe  ingresar en la celda E5, y que  la celda E1 contiene el valor de A, la celda E2 el valor de B, la celda E3  el valor de  C.
           

Aplicando el mismo procedimiento que se utilizó en el ejercicio a, llegamos a las siguientes fórmulas que  se corresponden con la expresión matemática indicada.

Forma 1:                    (2*A-B/RAIZ(C))/(C+1/B)*B/4         (forma básica)
                                     2   4 3     1        7   6 5    8 9   
Forma 2:                   (2*A-B/RAIZ(C))/(C+1/B)*(B/4)
                                    2   4 3     1        8   6  5   9  7
Forma 3:                   ((2*A-B/RAIZ(C))/(C+1/B))*(B/4)
                                     2   4 3     1        7   6  5    9   8

La fórmula en Excel correspondiente debe ser ingresada en la celda E5, con el valor de A en la celda E1,  el valor de  B en la celda E2 y el valor de  C en la celda E3.

Forma 1:                      =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*E2/4

Forma 2:                       =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*(E2/4)

Forma 3:                       =((2*E1-E2/RAIZ(E3))/(E3+1/E2))*(E2/4)


Al ingresar estás formulas en una hoja de Excel, utilizando 2 como valor de A, 5 como valor de B y 9 como valor de C, obtenemos para las tres formas de escribir correctamente la fórmula el valor   0,31702899
c.  La expresión matemática para el cálculo del monto de la cuota  postpagable de un préstamo (la cuota se paga al final del período), viene dada como:
                                            
Escribir la fórmula  correspondiente a esta expresión matemática e indicar el orden cómo  se debe evaluar dicha fórmula para  calcular el valor de la cuota postpagable:

 Forma 1:      VA*T*(1+T)^N/((1+T)^N-1)                (forma básica)
                           6  7   1    5  8     2   3  4   
 Forma 2:      VA*(T*(1+T)^N)/((1+T)^N-1)                
                           7   3    1   2    8   4    5  6   
 Forma 3:      VA*(T*(1+T)^N/((1+T)^N-1))                
                           8   6    1   5  7    2    3  4   
 Forma 4:      VA*((T*(1+T)^N)/((1+T)^N-1))          
                           8    3    1   2    7    4    5  6   

Si la celda B5 contiene el valor de la variable VA (valor actual), la celda B6 el valor de la variable T (tasa de interés), la celda B7  el valor de la variable N (número períodos),  la celda B8 el valor de la variable VF (valor final) y la celda B9 el valor de la variable TIPO.  Escribir la fórmula en Excel correspondiente a esta expresión en la celda B11.

Forma 1:     en B11:          =B5*B6*(1+B6)^B7/((1+B6)^B7-1)

Forma 2:                            =B5*(B6*(1+B6)^B7)/((1+B6)^B7-1)

Forma 3:                            =B5*(B6*(1+B6)^B7/((1+B6)^B7-1))

Forma 4:                            =B5*((B6*(1+B6)^B7)/((1+B6)^B7-1))

Se debe ingresar la primera forma de la fórmula anterior en la celda B11 y asignarle los valores que se indican a cada una de las variables.

VA = 2.000.000     T = 1,5%      N = 72      VF = 0      TIPO = 0

Se obtiene el resultado    de la celda      B11 =    45.615,58

Para observar que la única diferencia que hay entre estas 4 formas de escribir correctamente la fórmula en Excel de la expresión matemática es el orden en que se realizan los cálculos, se deben ingresar en una hoja de Excel las cuatro fórmulas, en diferentes celdas,  donde se observa que el resultado es el mismo; y para observar cómo realiza los cálculos el Excel, se utiliza la opción  Evaluar Formula, siguiendo  el siguiente procedimiento:

-          Seleccionar la celda donde está la fórmula
-          Luego abrir el menú Herramientas
-          Seleccionar la opción Auditoría de fórmulas
-          Del nuevo menú que se presenta seleccionar Evaluar fórmula
-          Para que se vaya realizando cada  operación paso a paso presionar el botón Evaluar
-          Para finalizar presionar el botón Cerrar
Microsoft Excel tiene incorporada  la función PAGO para el cálculo del monto de la cuota para el pago de un préstamo, basándose en pagos constantes y en una tasa de interés constante. La función PAGO  tiene la sintaxis siguiente:
PAGO(tasa;nper;va;[vf];[tipo])    
tasa      es el tipo de interés del préstamo.
Nper    es el número total de pagos del préstamo.
Va        es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros, también
             se conoce como el principal.
Vf         es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago.   
             Si el argumento vf se omite, se asume que el valor es 0 (es decir, el valor futuro de un
             préstamo es 0).
Tipo     es el número 0 (cero) ó 1 e indica el vencimiento de los pagos, 0 u omitido para el pago al
             final del período y 1  para pago al inicio del período.

Al ingresar en la celda  B20  la función de Excel que calcula la cuota postpagable, el 5º argumento correspondiente a Tipo es igual a 0 (cero).

En la celda  B20:   =PAGO(B6;B7;B5;B8;B9), la cual produce como resultado   =  (Bs. 45.615,58). Esta fórmula también puede ser ingresada como =PAGO(B6;B7;B5;0;0)  ó    =PAGO(B6;B7;B5).

El valor obtenido con esta función es igual al que se obtuvo con la fórmula en Excel que se determinó a partir de la expresión matemática, lo que nos lleva a concluir que la implementación que se hizo de esta expresión es correcta.

Cómo la función PAGO es una función financiera, Excel le aplica, automáticamente, a la celda en la cual está la función  el formato moneda indicado por las siglas Bs., u otro formato de moneda según sea el caso, antes del valor numérico; además, este valor se muestra como negativo.  Si el resultado se necesita que aparezca como positivo, se podría utilizar uno de los cambios siguientes:

En la celda  B20:       =-PAGO(B6;B7;B5;B8;B9) 
ó
En la celda  B20:       =PAGO(B6;B7;-B5;B8;B9)

Utilizar la misma fórmula para calcular el valor de la cuota postpagable para los siguientes valores:

VA =    2.000     T = 2,5%     N = 48    VF = 0     TIPO = 0    

Para realizar el nuevo cálculo de la cuota postpagable no es necesario hacerle ningún cambio a la fórmula, sólo se requiere introducir los nuevos valores en las celdas respectivas, 2000 en la celda B5, 2,5% en la celda B6 y 48 en la celda B7. Con los  valores indicados los nuevos  resultados  son entonces:           

B11 =    72,0119876

B20 =    (Bs 72,01)

Los cuales eran de esperar.
d.   La expresión matemática para el cálculo del monto de la cuota  prepagable de un préstamo (la cuota se paga al comienzo del período), viene dada como:
                                            
Escriba la fórmula  correspondiente a esta expresión matemática e indicar el orden cómo  se debe evaluar dicha fórmula para  calcular el valor de la cuota prepagable:

 Forma 1:      VA*T*(1+T)^N/((1+T)^(N+1)-(1+T))                (forma básica)
                           8  9   1    7  10  2    5   3    6  4 
 Forma 2:      VA*(T*(1+T)^N)/((1+T)^(N+1)-(1+T))             
                           9   3    1   2  10    4   7    5   8   6 
 Forma 3:      VA*((T*(1+T)^N)/((1+T)^(N+1)-(1+T)))           
                          10   3    1   2   9     4   7    5    8   6 

Si la celda D5 contiene el valor de la variable VA (valor actual), la celda D6 el valor de la variable T (tasa de interés), la celda D7  el valor de la variable N (número períodos),  la celda D8 el valor de la variable VF (valor final) y la celda D9 el valor de la variable TIPO.  Escriba la fórmula en Excel correspondiente a esta expresión en la celda D11.

Forma 1:      =D5*D6*(1+D6)^D7/((1+D6)^(D7+1)-(1+D6))         
                           
 Forma 2:     =D5*(D6*(1+D6)^D7)/((1+D6)^(D7+1)-(1+D6))            
                     
 Forma 3:      =D5*((D6*(1+D6)^D7)/((1+D6)^(D7+1)-(1+D6)))           
                       
Ingrese la primera forma de la fórmula anterior en la celda D11 y asígnele  los valores que se indican a cada una de las variables ¿ Qué valor calcula y asigna Excel a la celda D11 ?

VA = 2.000.000     T = 1,5%      N = 72    VF = 0     TIPO = 1

Se obtiene como  resultado  en la celda        D11 =        44.941,46

Al Ingresar en la celda  D20  la función de Excel que calcula la cuota prepagable, el 5º argumento correspondiente a tipo es igual a 1.  

En la celda      D20:       =PAGO(D6;D7;D5;D8;D9), la cual produce como resultado                                    (Bs. 44.941,46)

Utilizar la misma fórmula para calcular el valor de la cuota prepagable para los siguientes valores:
VA =    2.000     T = 2,5%      N = 48      VF = 0      TIPO = 1   

Para realizar el nuevo cálculo de la cuota prepagable no es necesario hacerle ningún cambio a la fórmula, sólo se requiere introducir los nuevos valores en las celdas respectivas: 2000 en la celda D5, 2,5% en la celda D6 y 72 en la celda D7. Con los  valores indicados los nuevos  resultados  son:

         D11 =     70,2555976

         D20  =    (Bs 70,26)
e. Escribir en Excel la fórmula correspondiente a la función de densidad de la Distribución Normal, 
    la cual está dada por:

  
          

 donde:

 µ      (mu)  es la media
 σ      (sigma) es la desviación estándar

  
Al llevar esta expresión matemática a una fórmula en Excel, tendremos lo siguiente:

      1/σ*RAIZ(2*PI())*EXP(-(x-μ)^2/2*σ^2)
                   3     2  1               5  4    6        7

Al analizar el orden de evaluación de los operadores que se encuentran en esta primera      aproximación a la fórmula requerida, encontramos que este orden no se corresponde  con el orden en que se deben realizar los cálculos que lleven al resultado correcto, ya que como operación 4 se realizaría el cálculo de (x-μ) y después como operación 5 el cambio de signo, lo que llevaría que al realizar la operación 6 este valor obtenido se eleve al cuadrado lo cual cambiara el resultado. Por lo tanto, se deben colocar entre paréntesis  las operaciones que requieren cambio en su orden de evaluación, obteniendo las tres formas siguientes para escribir la  expresión matemática de la función de densidad de la Distribución Normal.

Forma 1:                           1/(σ*RAIZ(2*PI()))*EXP(-((x-μ)^2/(2*σ^2)))
                                          12 4      3     2   1  13  11 10  5   8 9   7   6
Forma 2:                           (1/(σ*RAIZ(2*PI())))*EXP(-((x-μ)^2/(2*σ^2)))
                                           5    4     3      2   1   13  12 11  6   9 10 8  7
Forma  3:                          (1/(σ*RAIZ(2*PI())))*EXP(-(((x-μ)^2)/(2*σ^2)))
                                           5   4      3      2   1   13   12 11  6   7 10 9  8

Si utilizamos la celda B3 para contener el valor de μ, la celda B4 para contener el valor de σ y la celda B6 para contener el valor de x;  entonces, las fórmulas en Excel correspondientes a las formas como podemos escribir la fórmula que calcula el valor de la función de densidad de la Distribución Normal  serán:

Forma 1:                           1/(B4*RAIZ(2*PI()))*EXP(-((B6-B3)^2/(2*B4^2)))
                                         
Forma 2:                           (1/(B4*RAIZ(2*PI())))*EXP(-((B6-B3)^2/(2*B4^2)))
                                          
Forma  3:                          (1/(B4*RAIZ(2*PI())))*EXP(-(((B6-B3)^2)/(2*B4^2)))
                                          

Luego, para probar la fórmula encontrada, escribimos en la celda B10 cualquiera de estas tres  expresiones,   e ingresamos para μ el valor 1 (celda B3), para σ el valor 2 (celda B4) y para  x el valor 0 (celda B6), entonces  Excel va a mostrar  el valor  0,176032663 como resultado de esta fórmula.
Microsoft Excel tiene incorporada  la función DISTR.NORM para el cálculo del valor de la función de densidad de la Distribución Normal. La función DISTR.NORM  tiene la sintaxis siguiente:
DISTR.NORM(x;μ;σ;[Acum])    
X          El valor particular de la variable X al cual se le calcula la función de densidad
μ          La media aritmética de la distribución
σ          La desviación estándar de la distribución, la cual debe ser un número positivo
Acum   Un valor lógico que permite seleccionar el cálculo de:
              -  El valor de la función de densidad acumulada cuando este valor es VERDADERO
              -  El valor de la función de densidad en un punto cuando este valor es FALSO

Al ingresar en la celda C10  la fórmula  =DISTR.NORM(B6;B3;B4;FALSO), el Excel obtiene como resultado para  esta celda  el valor  0,176032663, el cual es el mismo que se obtuvo con la implementación de la expresión matemática de la función de densidad.


f. Escribir en la celda D5 una fórmula que calcule la sumatoria de los valores que están en el rango D1 hasta M1,  dividido entre el promedio de los valores que se encuentran en el rango D3 hasta M3,  y multiplique el resultado anterior por la raíz cuadrada del mayor valor que se encuentre en el rango D1 hasta M1.

La Expresión matemática de este problema  podría ser escrita como:

             

Si se ingresan los valores requeridos en una hoja de cálculo de Excel en los rangos indicados, entonces en la celda D5 se  puede ingresar la siguiente fórmula:

En D5:   =SUMA(D1:M1)/PROMEDIO(D3:M3)*RAIZ(MAX(D1:M1))


g. Escribir en la celda C20 una fórmula que promedie los valores que están en las celdas C1 hasta C18,  multiplique este resultado por la sumatoria de los valores que se encuentra en la celda D1 hasta D18, y dividida todo entre la raíz cuadrada de la suma de los valores que se encuentra en la celda D1 hasta D18 .

Si se ingresan los valores requeridos en una hoja de cálculo de Excel en los rangos indicados, entonces en la celda C20  se  puede ingresar la siguiente fórmula:



En C20:    =PROMEDIO(C1:C18)*SUMA(D1:D18)/RAIZ(SUMA(D1:D18))

No hay comentarios:

Publicar un comentario