Promedio ponderado en Excel

El promedio ponderado (también conocido como media ponderada) es un cálculo que proporciona un promedio donde cada valor no tiene el mismo impacto en el resultado final. El uso de la función PROMEDIO de Excel en estos escenarios daría un resultado engañoso. Afortunadamente, también podemos calcular fácilmente el promedio ponderado en Excel.

Tabla de contenido

• ¿Cuándo utilizar un promedio ponderado?
• Cálculo básico
• Promedio ponderado en Excel
• Promedio ponderado para calcular los resultados del examen.
• Promedio ponderado para calcular los plazos de pago medios
• Costo de capital promedio ponderado (WACC)
• Errores comunes
• Conclusión

¿Cuándo utilizar un promedio ponderado?

No siempre es fácil saber cuándo calcular un promedio versus un promedio ponderado. A continuación se muestran algunos ejemplos que le ayudarán a ver dónde un promedio ponderado es el cálculo correcto.

• En educación, no todas las evaluaciones y exámenes tienen el mismo impacto en la nota final. Por ejemplo, en la universidad, mi trabajo final contaba como el 40% de mi resultado general, y todos los demás exámenes tenían el mismo valor. Por lo tanto, necesitamos calcular el resultado correcto utilizando un promedio ponderado.
• Observar las condiciones de pago promedio de los proveedores puede ser una forma de comprender el flujo de caja. Por ejemplo, si una empresa tiene 10 proveedores con plazos de pago que varían de 7 a 60 días, puede resultar tentador utilizar un promedio. Sin embargo, si un proveedor representa el 85% del gasto total y solo ese proveedor tiene plazos de 7 días, entonces es necesario utilizar un promedio ponderado. Ese proveedor tendrá un impacto enorme en el resultado final.
• Una empresa puede calcular su costo de capital promedio ponderado y utilizarlo como tasa de descuento para evaluar proyectos/inversiones. La idea es que si el capital cuesta el 4%, entonces necesitamos proyectos que generen un rendimiento superior al 4%. De lo contrario, el costo de financiar el proyecto es mayor que el retorno del proyecto, por lo que sería mejor no invertir nada.

Los cálculos anteriores demuestran claramente la importancia de seleccionar el cálculo correcto. Los cálculos promedio serían matemáticamente precisos; sin embargo, serían una medida equivocada para la toma de decisiones.

Cálculo básico

Aquí hay un escenario básico. Para una calificación, el 40% del Año 2 y el 60% del Año 3 cuentan para la calificación final. Los resultados de un estudiante son los siguientes, ¿cuál es su nota final?

Para calcular un promedio ponderado:

• Paso 1: Cada valor se multiplica por el peso correspondiente
  20 x 0% = 0
  25 x 40% = 10
  85 x 60% = 51
• Paso 2: Calcula la suma de cada elemento del Paso 1
  0 + 10 + 51 = 61
• Paso 3: Divida el resultado del Paso 2 por la SUMA de los pesos totales.
  61 / (0% + 40% + 60%) = 61%

El resultado final es 61%.

Promedio ponderado en Excel

Podemos utilizar funciones sencillas para calcular el promedio ponderado en Excel.

Excel tradicional

=SUMPRODUCTO(pesos,valores)/SUM(pesos)

Excel habilitado para matriz dinámica

=SUMA(pesos*valores)/SUM(pesos)

Para usar estas fórmulas, solo necesitamos insertar los pesos y valores de nuestros datos. Veamos esto con los 3 ejemplos que presentamos anteriormente.

Promedio ponderado para calcular los resultados del examen.

En este escenario, 10 exámenes y un trabajo escrito contribuyen al resultado final. El trabajo escrito vale el 40% del resultado final; por lo tanto, cada examen sólo vale el 6% del resultado.

La fórmula en la celda C16 es:

=SUMAPRODUCTO(D3:D13,C3:C13)/SUM(D3:D13)

La función SUMAPRODUCTO logra los pasos 1 y 2 del cálculo; multiplica cada valor con cada peso correspondiente y agrega el total. Finalmente, el paso 3 calcula como el resultado de SUMPRODUCTO dividido por la SUMA de los pesos.

Los resultados del examen son un escenario razonablemente sencillo de entender. Los pesos totales suman 100%, por lo que podemos tener una intuición sobre si el total parece exacto.

Promedio ponderado para calcular los plazos de pago medios

En este escenario, tenemos un proveedor con plazos de 7 días que cubre el 85% del costo, y luego otros 7 proveedores con plazos que varían de 30 a 60 días.

Los valores se ingresaron dentro de una tabla de Excel y estoy usando una versión de Excel habilitada para matrices dinámicas. Por lo tanto, puedo utilizar un cálculo sólo con funciones SUMA.

La fórmula en la celda C13 es:

=SUM(Proveedores[Pagos]*Proveedores[Términos])/SUM(Proveedores[Pagos])

La primera función SUMA logra los Pasos 1 y 2 (es decir, la multiplicación y agregación inicial). Luego, en el Paso 3, el resultado se divide por la suma de los pesos.

Si no tenemos una versión de Excel habilitada para matrices dinámicas, aún podemos usar este método. Presione Ctrl + Shift + Enter para confirmar la fórmula. Esto rodeará la fórmula entre llaves, como se muestra a continuación, y calculará el valor correcto en todas las versiones de Excel.

{=SUM(Proveedores[Pagos]*Proveedores[Términos])/SUM(Proveedores[Pagos])}

Costo de capital promedio ponderado (WACC)

Para este último ejemplo, calculamos un requisito comercial común: el costo de capital promedio ponderado en Excel.

El costo promedio ponderado del capital (WACC) tiene la complejidad adicional de que el costo de la deuda es deducible de impuestos, mientras que el rendimiento del capital no lo es. En términos prácticos, significa que la tasa de deuda publicada debe ajustarse a los impuestos antes de incluirse en el cálculo.

Las cifras siguientes se basan en un artículo de investopedia.com . El capital social de la empresa de 394 mil millones de dólares tiene un costo del 4,99%, mientras que la deuda de 44 mil millones de dólares tiene un costo del 3,99%. La tasa de deuda se ajusta a una tasa impositiva del 33%.

La fórmula en la celda E7 es:

=SUMPRODUCTO(F3:F4,E3:E4)/SUM(F3:F4)

Este ejemplo demuestra que en algunos escenarios, es posible que necesitemos ajustar valores cuando hay factores que afectan algunos números pero no otros. Queremos calcular de forma consistente.

Errores comunes

A continuación, se muestran algunos errores comunes que puede encontrar al calcular el promedio ponderado en Excel:

• En la multiplicación, el #VALOR! Se devolverá un error si los rangos o matrices son de diferentes tamaños. Esto es cierto para el enfoque SUMPRODUCT o SUM.
• SUMPRODUCT y SUM tratan el texto como ceros, por lo que si sus rangos contienen texto, es posible que no calcule el resultado correcto

Conclusión

No existe una función de promedio ponderado en Excel, pero con dos funciones simples, SUMAPRODUCTO y SUMA, podemos calcularla fácilmente nosotros mismos.

---