Excel: Operaciones sencillas - Ejemplo de ponderación

Empezamos con un caso práctico que puede interesaros, en el cual introducimos funciones y conceptos que más adelante iremos analizando. Vamos a ver:

Tenemos una hoja de cálculo muy sencilla en la que vamos introduciendo la fecha de la compra de un determinado valor, el importe en euros y la cotización de compra (€ por valor).

Evidentemente, el valor de nuestra cuenta será función del coste ponderado, por lo que habremos de hacer los cálculos necesarios para que esta operación se nos mantenga actualizada según vayamos usando la hoja. Para ello, crearemos una columna auxiliar cuyo título será “Ponderante”, y cuyo contenido será el importe por la cotización:

 Fijaros en que hemos puesto referencias relativas, por lo que por supuesto podremos pinchar la celda y repetir el cálculo en las siguientes:

Las funciones que aquí veis (Maximo, media, minimo) las veremos en otra entrada y os explicaré por qué ponemos la línea 11 en negro)

Fijaros que las unidades de “Ponderante” serán:

Importe: €

Cotización compra: € / participación

Y el producto será €^2/ participación. O sea, euros al cuadrado dividido entre participación. 

Veamos por qué hacemos este razonamiento:

Si calculamos la suma de ponderante nos dará €^2/participación

Y lo dividimos entre la suma de importe, en €,

Obtendremos €/Participación, que es el importe ponderado. Las unidades nos ayudan a comprobar que la operación es correcta.

Vamos a hacerla:

Primero, vamos a cambiar a azul nuestras áreas de introducción de datos, columnas importe y cotización de compra, de acuerdo con nuestros criterios de estética nemotécnica explicados en la entrada anterior. Luego seleccionamos el área: D5:D10 (Recordad la diferencia entre área y lista)

Ahora, en la zona de nombres escribimos el que vamos a dar a este área, y en un rasgo de original le llamaremos “Ponderante”.

Y vamos a hacer lo mismo con los importes: (Veréis que mi pulso con el ratón es fatal)

En próximas entradas os hablaré de la gestión de áreas y listas (rangos en general) por nombres. Ahora fijaros en que si pos ponéis en cualquier punto del libro y buscáis “Ponderantes” o “importes” automáticamente se os posiciona en tal área seleccionada.

Lo genial es que en cualquier posición (En este caso en B13) yo puedo hacer la operación suma(Importes) y me dará el mismo resultado que si hago suma(B5:B10)

Lo mismo pasa con suma (Ponderante):

Ahora bien, si dividimos la suma de ponderantes entre la suma de importes, obtendremos la COTIZACIÓN PONDERADA DE COMPRA, como hemos visto antes.

De esta forma tendremos ajustado a la realidad el valor de nuestra cartera, de nuestro almacén o de lo que más convenga.

Este procedimiento nos permite tenerel valor de la compra en cada fecha.  Si ese dato no interesase, se podría llegar también a la cotización ponderada con la función: = sumaproducto(importe; cotizacióncompra) / suma(importe) que veremos en su momento.  En siguientes entradas comentaremos los conceptos que han ido apareciendo.

Espero que os haya parecido útil.