curso completisimo matrices y optimizacion en excel

8/19/2019 curso completisimo Matrices y Optimizacion en excel

1/20

Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid

© J. David Moreno 1

Curso de ExcelEmpresarial y FinanzasSESIÓN 5:

EXCEL

J. David Moreno

ÍNDICE

1.1. Operaciones con matricesOperaciones con matrices1. p cac n: cu o e a en a a e una

cartera

2. Aplicación: Cálculo de la Varianza de unacartera

.. 1. Aplicación: Maximización de los beneficios de

una empresa2. Aplicación: Gestión de Carteras. Maximización

de rentabilidad y minimización del riesgo


2/20



Sección 1OperacionesOperaciones concon MatricesMatrices

Operaciones con Matrices

Cada elemento de la matrizse identifica or su FILA iy su COLUMNA (j)

Para poder sumar&restar matrices deben tener elmismo orden o tamaño.La suma y resta de matricesse hace sumando cadaelemento en la mismaposición.


3/20



Operaciones con MatricesmatricesSeleccionan lasmatrices

Sumar dos matrices esidéntico a sumar dosnúmeros.

al aplicar la fórmula se debe hacerMAYUSCULAS+CTRL+ENTER


Los corchetes nosindican es una matriz


4/20



Operaciones con MatricesPara multipl icar 2 matrices esnecesario ue: Ejemplo

El número de columnas de Acoincida con el número defilas de B

Cada elemento de la matrizproducto se calcula

© J. David Moreno (2012)

mu p can o ca a e emen ode la fila i (en matriz A) porcada elemento de la columna j (en matriz B) y sumándolos.


Funciones matemáticas ytrigonométricas.

Aquí hay algunas funcionesmatriciales, como:

• MINVERSA• TRANSPONER.

Muy importante: Para obtener resultadomatricial debemos de seleccionar bien elrango de salida de la operación y alaplicar la fórmula se debeMAYUSCULAS+CTRL+ENTER


5/20



Operaciones con MatricesSeleccionan lasmatrices

Para multiplicar matrices sedeben seleccionar las celdas desalida de la multiplicación.


Las siguientes operaciones permiten trabajar

LA FUNCION MULTPLICAR MATRICES :MMULT

LA FUNCION INVERSA:MIVERSA

LA FUNCION TRASPUESTA:TRANSPONER


6/20



Operaciones con MatricesEscribiendodirectamente MMULT en labarra defórmulas

Para obtenertodos los datosdebemos hacer MAYUSC+CRTL+ENTER

EJERCICIO

A partir del fichero dedatos_matrices.xls debe

matrices.

A+B A-1 A*B A*A-1 A’(A+B)*C’


7/20



Solución ejercicio

Ejercicio ExtraEn el caso de Teoría de Carteras sabemos quepodemos calcular el rendimiento de una carterade N activos como el producto matricial de losrendimientos medios de cada activo (vector fila ) ylos pesos (Wi) de cada activo en la cartera(representados en un vector fila ).

par r e os a os e ren m en os e c eroEjemplo_Datos_Acciones.xlsx calcule elrendimiento de una cartera equiponderada de los4 activos.


8/20



Solución ejercicio extra1. Lo primero es calcular rendimientos, ya que en los

datos ori inales nos dan recios. 2. Y calcular rendimiento medio.

Solución ejercicio extra


9/20



Ejercicio ExtraEn el caso de Teoría de Carteras sabemos queodemos calcular el ries o de una cartera σ como el

producto de tres matrices.

W tiene tamaño 1XNC tiene tamaño NxN – C es la matriz de varianzas y covarianzas

par r e os a os e ren m en os e c eroEjemplo_Datos_Acciones.xlsx calcule el rendimientode una cartera equiponderada de los 4 activos.

Recuerde calcular la desviación típica ( σ ), no la varianza.

Solución ejercicio extra

1. Lo primero es calcular rendimientos, yaque en os a os or g na es nos anprecios.

2. Luego calcular la matriz (C) de varianzasy covarianzas.

Ejemplo de Matriz de varianzas ycovarianzas (C) para 3 activos

Es más sencillo calcularla con Análisis de Datos- Covarianzas


10/20



Solución ejercicio extraPara multiplicar 3 matrices podemos incluir un

.

Sección 2Optimización conOptimización con Solver Solver


11/20



GESTIÓN DE CARTERASSolver Solver es una herramienta de

.Puede no estar instalado en el ordenador Se puede instalar sin necesidad del DVD de MicrosoftOffice.

Clic en OPCIONES DEEXCEL

GESTIÓN DE CARTERAS

1. Clic en

COMPLEMENTOSCOMPLEMENTOS2. Señalar Solver 3. Clic en botón IrIr (no en

el botón Aceptar)


12/20



GESTIÓN DE CARTERAS1. Señalar Solver 2. Clic en Aceptar

Ahora ya aparece en

GESTIÓN DE CARTERASOBJETIVOOBJETIVO: Aquí introducimosla celda con la fórmula que

minimizar

MÁXIMO/MÍNIMO:MÁXIMO/MÍNIMO:Señalamossi nuestro problema esmaximizar o minimizar.O lograr un valor concreto en lacelda objetivo

CELDAS A MODIFICARCELDAS A MODIFICAR:Debemos introducir las celdasque debe cambiar Excel paracumplir el objetivo demaximizar o minimizar RESTRICCIONESRESTRICCIONES: Incluimos todas las

restricciones en el problema.Ejemplo: valores que pueden tomar lasvariables


13/20



Solver en un problema deMaximización de Beneficios

Ejemplo 1 de Solver : Suponer una empresaque quiere maximizar beneficios, y para ellodebe decidir la producción de cada uno de lossiguientes tres productos (A, B y C).

Si la única restricción es que no se puedenproducir más de 300 unidades al día en total.La solución es muy sencilla→ 100% del productocon más margen.


Celda Objetivo: D6Queremos Maximizar Cambiando las celdas: B3 a B5Restricciones:

B6 menor o igual a 300B3 a B5 mayor o igual a 0


14/20



GESTIÓN DE CARTERASConsejo paraConsejo para SolverSolver

,le da la misma solución cuando cambia variasceldas de objetivo, etc.Debe reiniciar reiniciar Solver

Clic enRestablecer todoRestablecer todo


Ejemplo 2 de Solver : Partiendo del ejemploanterior suponga que tenemos todas estas

Si la única restricción es que no se puedenproducir más de 300 unidades al día en total.Debemos producir como mínimo 50 unidades de

A, ya que están comprometidas con un cliente.Debemos producir un mínimo de 40 de B porqueestán vendidas a una entidad pública.Dado que la demanda de C es muy limitada, laempresa tiene miedo y no desea producir más de 40.

Soluci ó n aesteejemplo


15/20



Ejercicio ExtraPartiendo del ejemplo anterior suponga que

Si la única restricción es que no se puedenproducir más de 1300 unidades al día en total.Debemos producir como mínimo 500 unidades deA, ya que están comprometidas con un cliente.Debemos producir un mínimo de 450 de B

.Sabemos que la producción total de A+C nopuede ser superior a 700 unidades, por requisitostécnicos de las materias primas que necesitamos.

Solución Ejercicio Extra


16/20



Solver Podemos hacer click en informes

os crea nuevas o as e exce con va ores,límites, proceso de optimización…

Solver - Informes


17/20



Solver- Aplicación a Gestión de Carteras

Ejercicio: A partir de los datos de precios en el _ _ .

determine los pesos de una cartera paraobtener la máxima rentabilidad esperada de lacartera.

Asuma que todos los pesos deben ser mayores que cero(w j≥0)La suma de los pesos debe ser igual a 1 ( ∑w j=1)

Recuerde también que:Debemos trabajar con Rendimientos y no preciosLa fórmula matricial de la rentabilidad de una cartera es:

Rp=R*W’

GESTIÓN DE CARTERASTenga cuidado al incluir las restricciones, no sepueden introducir fórmulas en la ventana derestricciones.

qu ere nc u r que a suma e os pesos e eser igual a 1, debe sumar los pesos de lasacciones en una celda en el Excel.Luego poner que esa celda = 1 (en la ventana derestricciones).

Celda desuma depesos


18/20



Solución Ejercicio ExtraSolución

nver r en as acc ones e eps

Ejercicio Extra

Determine los pesos de una cartera para obtener lamáxima rentabilidad es erada.

Utilice los datos de las 4 acciones en el archivoEjemplo_Datos_Acciones.xlsx

Permita que existan ventas en corto (short-selling) pero nuncamás de un 20% para una misma acción (-0.20 ≤w j)Asuma que su cliente le ha indicado que no quiere que elpeso de ningún activo sea superior al 70% (w j ≤0.70)

a suma e os pesos e e ser gua a w j=Utilice cálculo matricial (E(Rp)=R*W’)


19/20



Solución Ejercicio ExtraSolución:

Ejercicio Extra

Determine los pesos de la cartera de mínimo riesgovolatilidad o desviación tí ica de la cartera .

Utilice los datos de las 4 acciones en el archivoEjemplo_Datos_Acciones.xlsx.

Permita que existan ventas en corto (short-selling) pero nuncamás de un 30% para una misma acción (-0.30 ≤w j)Asuma que su cliente le ha indicado que no quiere que elpeso de ningún activo sea superior al 70% (w j ≤0.70)

a suma e os pesos e e ser gua a w j=Utilice cálculo matricial (σ p=W*C*WT )

Donde C es la matriz de varianzas y covarianzasWT es la traspuesta de la matriz de pesos


20/20


GESTIÓN DE CARTERASCONSEJOCONSEJO : Antes de usar Solver, es convenientecomprobar que las fórmulas están bien introducidas.

,y el resto en 0%Comprobar que Rp=R j y Volatilidad(Rp)= Volatilidad (Ri)

W4=100%

Solución Ejercicio Extra

curso completisimo matrices y optimizacion en excel

Documents