Trazar un rango de derrame

Las últimas publicaciones han ilustrado varias formas de aplicar fórmulas de matriz dinámica y sus rangos de derrame. Kent preguntó si podíamos utilizar rangos de derrame para crear un gráfico dinámico para visualizar el valor futuro de una cartera de inversiones. Por ejemplo, comience con el valor actual de la cartera de alguien y luego aplique una tasa de crecimiento supuesta y un monto de inversión anual. Esta publicación demuestra cómo calcular el valor futuro anual y enviar el rango de derrame resultante en un gráfico de barras. Además, cuando se modifica algún supuesto, incluido el número de años, el gráfico se actualiza automáticamente. ¡Gracias por esta gran idea Kent!

Objetivo

Demos un paso atrás y confirmemos nuestro objetivo aquí. Nos gustaría que el usuario pudiera ingresar algunas suposiciones clave, a saber, la cantidad de años en el análisis, el valor actual de la cartera, el monto de la inversión anual (adición a la cartera) y la tasa de crecimiento. Los ingresaríamos en algunas celdas como esta:

Luego, queremos que el valor futuro de cada año se muestre en un gráfico como este:

Luego, si el usuario cambia algunos valores, tal vez el número de años a 20, queremos que el gráfico se actualice en consecuencia:

Construiremos esto siguiendo los siguientes pasos:

• Calcular los valores futuros.
• Crear un nombre definido
• Construye el gráfico

Hagámoslo.

Nota: las matrices dinámicas y los rangos de derrame están disponibles de forma limitada en el momento en que escribo esta publicación. Por lo tanto, es posible que su versión de Excel no tenga estas capacidades. En el momento en que escribo esto, están disponibles para los suscriptores de O365 con el canal Insiders Fast. Si es suscriptor de O365, es posible que pueda cambiar al canal de actualización Insiders Fast. Consulte el sitio web de Microsoft para obtener más información.

Calcular los valores futuros.

Lo primero es calcular los valores futuros de la cartera, dada la tasa de crecimiento supuesta y la inversión anual adicional. Podemos utilizar la función de valor futuro (FV) de Excel con la ayuda de la función SECUENCIA.

Echemos otro vistazo a los supuestos para que podamos ver sus referencias de celda:

Ahora, la función FV calcula el valor futuro, dada la tasa de interés (C7), el número de períodos (C4), el monto del pago periódico (C6) y el valor presente (C5). Entonces, comenzamos traduciendo nuestros supuestos en argumentos FV. Por ejemplo, escribimos la siguiente fórmula en B12:

=FV(C7, C4, C6, C5)

Pero esa fórmula devuelve un solo valor, el valor futuro al final de 10 años. Lo que queremos ver en cambio son los 10 valores futuros… una fila para cada año. ¿Cómo podemos hacer esto?

Bueno, podemos usar la función SECUENCIA. Esta función devuelve un número determinado de filas (y/o columnas) en función de sus argumentos. Entonces, solo necesitamos hacer un pequeño ajuste a nuestra fórmula para que la función SECUENCIA devuelva 10 valores… 1 hasta el número de años en C4:

=FV(C7,SECUENCIA(C4),C6,C5)

Dado que la función FV opera en un modelo de flujo de efectivo, devuelve un valor negativo dados los supuestos anteriores. Entonces, necesitaremos voltear el letrero, lo cual podemos hacer de varias maneras, incluido agregar un guión al frente, como este:

=-FV(C7,SECUENCIA(C4),C6,C5)

Actualizamos la fórmula en B12 y pulsamos enter, bam:

Hasta ahora, todo bien. Ahora solo necesitamos enviar estos resultados al gráfico. Pero primero haremos un desvío rápido y estableceremos un nombre definido.

Crear un nombre definido

En el momento en que escribo esta publicación, algunas funciones de Excel (por ejemplo, gráficos) no son totalmente compatibles con las referencias de rango de derrame, como B12#. A menudo, podemos solucionar esta limitación utilizando en su lugar un nombre definido. Solo necesitamos crear un nuevo nombre definido que haga referencia al rango de derrame y luego usar el nombre definido en lugar de la referencia de derrame.

Para crear el nombre definido, abra el Administrador de nombres ( Fórmulas Administrador de nombres ). Luego, haga clic en Nuevo para revelar el cuadro de diálogo Nuevo nombre . Ingresamos el nombre deseado, como ChartData , y luego seleccionamos la celda de fórmula y escribimos # al final para hacer referencia al rango de derrame, así:

Haga clic en Aceptar y cierre. Con nuestro nombre configurado, estamos listos para crear el gráfico.

Construye el gráfico

Ponemos en marcha nuestro gráfico inicial seleccionando cualquier celda en la columna FV y luego insertando el gráfico deseado ( Insertar Gráfico de columnas agrupadas ).

Excel inserta un gráfico básico y parece que hemos terminado:

Pero… en cuanto cambiamos el número de años, vemos que nuestro gráfico no se actualiza. No te preocupes… sólo necesitamos hacer una pequeña actualización al gráfico para que use nuestro nombre definido.

Para hacerlo, haga clic en el comando Herramientas de gráficos Seleccionar datos . Aparece el cuadro de diálogo Seleccionar fuente de datos , así:

Haga clic en el botón Editar en el lado Entradas de leyenda (Serie) para revelar el cuadro de diálogo Editar serie :

Solo necesitamos cambiar la referencia del rango de estilo A1 ($B$12:$B$21) a nuestro nombre definido ChartData, así:

Ahora estamos listos para comenzar. Podemos cambiar cualquier suposición, incluida la cantidad de años, y el gráfico se actualiza en consecuencia.

Si tiene otros consejos sobre derrames, compártalos publicando un comentario a continuación… ¡gracias!

Archivo de muestra: ChartSpill.xlsx