Curso de Excel Empresarial y...

Documento propiedad de J. David Moreno – Universidad Carlos III de Madrid

© J. David Moreno 1

Curso de Excel Empresarial y Finanzas

SESIÓN 5:

OPTIMIZACIÓN A TRAVÉS DEOPTIMIZACIÓN A TRAVÉS DE EXCEL

J. David Moreno

ÍNDICE

1.1. Operaciones con matricesOperaciones con matricesA li ió Cál l d l R t bilid d d1. Aplicación: Cálculo de la Rentabilidad de una cartera

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

22 Optimización conOptimización con SolverSolver2.2. Optimización con Optimización con SolverSolver1. Aplicación: Maximización de los beneficios de

una empresa

2. Aplicación: Gestión de Carteras. Maximización de rentabilidad y minimización del riesgo



Sección 1

Operaciones Operaciones con con MatricesMatrices

Sección 1

Operaciones con Matrices

Cada elemento de la matriz se identifica por su FILA (i)p ( )y su COLUMNA (j)

Para poder sumar&restarmatrices deben tener el mismo orden o tamaño.La suma y resta de matrices se hace sumando cada elemento en la misma posición.




matricesSeleccionan las matrices

Sumar dos matrices es idéntico a sumar dos números.

al aplicar la fórmula se debe hacer MAYUSCULAS+CTRL+ENTER


Los corchetes nos indican es una matriz




Para multiplicar 2 matrices es necesario que: Ejemplo

El número de columnas de A coincida con el número de filas de B

Cada elemento de la matriz producto se calcula

lti li d d l t

necesario que:

© J. David Moreno (2012)

multiplicando cada elemento de la fila i (en matriz A) por cada elemento de la columna j (en matriz B) y sumándolos.


Funciones matemáticas y trigonométricas.

Aquí hay algunas funciones matriciales, como:• MMULT• MMULT• MINVERSA• TRANSPONER.

Muy importante: Para obtener resultado matricial debemos de seleccionar bien el rango de salida de la operación y al aplicar la fórmula se debe MAYUSCULAS+CTRL+ENTER




Seleccionan las matricesmatrices

Para multiplicar matrices se deben seleccionar las celdas de salida de la multiplicación.


Las siguientes operaciones permiten trabajar con funciones:con funciones:

LA FUNCION MULTPLICAR MATRICES : MMULT

LA FUNCION INVERSA: MIVERSA

LA FUNCION TRASPUESTA: TRANSPONER




Escribiendo directamentedirectamente MMULT en la barra de fórmulas

Para obtener todos los datos debemos hacerMAYUSC+CRTL+ENTER

EJERCICIO

A partir del fichero de datos_matrices.xls debe calcular las siguientes operaciones concalcular las siguientes operaciones con matrices.

A+B A-1

A*B A*A-1

A’ (A+B)*C’



Solución ejercicio

Ejercicio Extra

En el caso de Teoría de Carteras sabemos que podemos calcular el rendimiento de una cartera pde N activos como el producto matricial de los rendimientos medios de cada activo (vector fila) y los pesos (Wi) de cada activo en la cartera (representados en un vector fila).

A ti d l d t d di i t d l fi h A partir de los datos de rendimientos del fichero Ejemplo_Datos_Acciones.xlsx calcule el rendimiento de una cartera equiponderada de los 4 activos.



Solución ejercicio extra

1. Lo primero es calcular rendimientos, ya que en los datos originales nos dan precios.datos originales nos dan precios.

2. Y calcular rendimiento medio.




Ejercicio Extra

En el caso de Teoría de Carteras sabemos que podemos calcular el riesgo de una cartera (σ) como el p g ( )producto de tres matrices.

W tiene tamaño 1XN

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

A ti d l d t d di i t d l fi h A partir de los datos de rendimientos del fichero Ejemplo_Datos_Acciones.xlsx calcule el rendimiento de una cartera equiponderada de los 4 activos.

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


1. Lo primero es calcular rendimientos, ya l d t i i l dque en los datos originales nos dan

precios.

2. Luego calcular la matriz (C) de varianzas y covarianzas.

Ejemplo de Matriz de varianzas y covarianzas (C) para 3 activos

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




Para multiplicar 3 matrices podemos incluir un MMULT dentro de otro MMULTMMULT dentro de otro MMULT.

Sección 2

Optimización con Optimización con SolverSolver

Sección 2



GESTIÓN DE CARTERAS

SolverSolver es una herramienta de OPTIMIZACIÓNOPTIMIZACIÓN muy útilOPTIMIZACIÓNOPTIMIZACIÓN muy útil. Puede no estar instalado en el ordenador Se puede instalar sin necesidad del DVD de Microsoft

Office.

Clic en OPCIONES DE EXCEL


1. Clic en COMPLEMENTOSCOMPLEMENTOS

2. Señalar Solver3. Clic en botón Ir Ir (no en

el botón Aceptar)




1. Señalar Solver2. Clic en Aceptar p

Ahora ya aparece en el Menú DATOSMenú DATOSel Menú DATOSMenú DATOS


OBJETIVOOBJETIVO: Aquí introducimos la celda con la fórmula que queremos Maximizar oqueremos Maximizar o minimizar

MÁXIMO/MÍNIMO: MÁXIMO/MÍNIMO: Señalamos si nuestro problema es maximizar o minimizar.O lograr un valor concreto en la celda objetivo

CELDAS A MODIFICARCELDAS A MODIFICAR: Debemos introducir las celdas que debe cambiar Excel para cumplir el objetivo de maximizar o minimizarRESTRICCIONESRESTRICCIONES: Incluimos todas las

restricciones en el problema. Ejemplo: valores que pueden tomar las variables



Solver en un problema de Maximización de Beneficios Ejemplo 1 de Solver: Suponer una empresa

que quiere maximizar beneficios, y para ello debe decidir la producción de cada uno de los psiguientes tres productos (A, B y C). Si la única restricción es que no se pueden

producir más de 300 unidades al día en total. La solución es muy sencilla→ 100% del producto

con más margen.

Solver en un problema de Maximización de Beneficios

Celda Objetivo: D6

Queremos Maximizar

Cambiando las celdas: B3 a B5

Restricciones: B6 menor o igual a 300

B3 a B5 mayor o igual a 0




Consejo para Consejo para SolverSolver Si en algún momento empieza a dar errores o Si en algún momento empieza a dar errores, o

le da la misma solución cuando cambia varias celdas de objetivo, etc.

Debe reiniciarreiniciar Solver

Clic en Restablecer todoRestablecer todo

Solver en un problema de Maximización de Beneficios Ejemplo 2 de Solver: Partiendo del ejemplo

anterior suponga que tenemos todas estas restricciones:restricciones: Si la única restricción es que no se pueden

producir 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 porque

están vendidas a una entidad pública. Dado que la demanda de C es muy limitada, la

empresa tiene miedo y no desea producir más de 40.

Solución a este ejemplo



Ejercicio Extra

Partiendo del ejemplo anterior suponga que tenemos todas estas restricciones:tenemos todas estas restricciones: Si la única restricción es que no se pueden

producir más de 1300 unidades al día en total. Debemos producir como mínimo 500 unidades de

A, ya que están comprometidas con un cliente. Debemos producir un mínimo de 450 de B

porque están vendidas a una entidad públicaporque están vendidas a una entidad pública. Sabemos que la producción total de A+C no

puede ser superior a 700 unidades, por requisitos técnicos de las materias primas que necesitamos.

Solución Ejercicio Extra



Solver

Podemos hacer click en informesN h j d l l Nos crea nuevas hojas de excel con valores, límites, proceso de optimización…

Solver - Informes



Solver- Aplicación a Gestión de Carteras

Ejercicio: A partir de los datos de precios en el archivo Ejemplo Datos Acciones xlsxarchivo Ejemplo_Datos_Acciones.xlsx determine los pesos de una cartera para obtener la máxima rentabilidad esperada de la cartera.

Asuma que todos los pesos deben ser mayores que cero (wj≥0)

La suma de los pesos debe ser igual a 1 (∑wj=1) Recuerde también que:

Debemos trabajar con Rendimientos y no precios La fórmula matricial de la rentabilidad de una cartera es:

Rp=R*W’

GESTIÓN DE CARTERAS Tenga cuidado al incluir las restricciones, no se

pueden introducir fórmulas en la ventana de restricciones.

Si i i l i l d l d b Si quiere incluir que la suma de los pesos debe ser igual a 1, debe sumar los pesos de las acciones en una celda en el Excel.

Luego poner que esa celda = 1 (en la ventana de restricciones).

Celda de suma de pesos




Solución I ti 100% l i d P i Invertir 100% en las acciones de Pepsi

Ejercicio Extra

Determine los pesos de una cartera para obtener la máxima rentabilidad esperada.máxima rentabilidad esperada.

Utilice los datos de las 4 acciones en el archivo Ejemplo_Datos_Acciones.xlsx

Permita que existan ventas en corto (short-selling) pero nunca más de un 20% para una misma acción (-0.20≤wj)

Asuma que su cliente le ha indicado que no quiere que el peso de ningún activo sea superior al 70% (wj ≤0.70)La suma de los pesos debe ser igual a 1 (∑ 1) La suma de los pesos debe ser igual a 1 (∑wj=1)

Utilice cálculo matricial (E(Rp)=R*W’)




Solución:

Ejercicio Extra

Determine los pesos de la cartera de mínimo riesgo (volatilidad o desviación típica de la cartera).(volatilidad o desviación típica de la cartera).

Utilice los datos de las 4 acciones en el archivo Ejemplo_Datos_Acciones.xlsx.

Permita que existan ventas en corto (short-selling) pero nunca más de un 30% para una misma acción (-0.30≤wj)

Asuma que su cliente le ha indicado que no quiere que el peso de ningún activo sea superior al 70% (wj ≤0.70)La suma de los pesos debe ser igual a 1 (∑ 1) La suma de los pesos debe ser igual a 1 (∑wj=1)

Utilice cálculo matricial (σp=W*C*WT) Donde C es la matriz de varianzas y covarianzas

WT es la traspuesta de la matriz de pesos




CONSEJOCONSEJO: Antes de usar Solver, es conveniente comprobar que las fórmulas están bien introducidas. Para eso poner el peso de uno de los activos en 100% Para eso, poner el peso de uno de los activos en 100%

y el resto en 0% Comprobar que Rp=Rj y Volatilidad(Rp)= Volatilidad (Ri)

W4=100%


Curso de Excel Empresarial y...

Documents

Transcript of Curso de Excel Empresarial y...