-
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
2/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 2
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.
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
3/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 3
Operaciones con MatricesmatricesSeleccionan lasmatrices
Sumar dos matrices esidéntico a sumar dosnúmeros.
al aplicar la fórmula se debe hacerMAYUSCULAS+CTRL+ENTER
Operaciones con Matrices
Los corchetes nosindican es una matriz
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
4/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 4
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.
Operaciones con Matrices
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
5/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 5
Operaciones con MatricesSeleccionan lasmatrices
Para multiplicar matrices sedeben seleccionar las celdas desalida de la multiplicación.
Operaciones con Matrices
Las siguientes operaciones permiten trabajar
LA FUNCION MULTPLICAR MATRICES :MMULT
LA FUNCION INVERSA:MIVERSA
LA FUNCION TRASPUESTA:TRANSPONER
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
6/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 6
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’
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
7/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 7
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/19/2019 curso completisimo Matrices y Optimizacion en excel
8/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 8
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
9/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 9
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
10/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 10
Solución ejercicio extraPara multiplicar 3 matrices podemos incluir un
.
Sección 2Optimización conOptimización con Solver Solver
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
11/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 11
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)
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
12/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 12
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
13/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 13
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.
Solver en un problema deMaximización de Beneficios
Celda Objetivo: D6Queremos Maximizar Cambiando las celdas: B3 a B5Restricciones:
B6 menor o igual a 300B3 a B5 mayor o igual a 0
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
14/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 14
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
Solver en un problema deMaximización de Beneficios
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
15/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 15
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
16/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 16
Solver Podemos hacer click en informes
os crea nuevas o as e exce con va ores,límites, proceso de optimización…
Solver - Informes
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
17/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 17
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
18/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 18
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’)
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
19/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
© J. David Moreno 19
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
-
8/19/2019 curso completisimo Matrices y Optimizacion en excel
20/20
Documento propiedad de J. David Moreno – UniversidadCarlos III de Madrid
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