Durante las últimas semanas, hemos visto el aumento del uso y la popularidad de Excel , y he notado que Excel ha hecho incursiones en el análisis. Hoy, analizaremos un tipo de análisis: la planificación y la optimización basadas en escenarios.
Con los complementos de Excel, puede crear un análisis hipotético y una planificación basada en escenarios utilizando la simulación de Monte Carlo. Pero crear este tipo de análisis es tedioso, principalmente porque Excel básico carece de tales funciones integradas. Además, la pantalla aún está en forma de hoja de cálculo en lugar de una pantalla gráfica. Ser capaz de mostrar las entradas gráficamente y sus efectos en la salida, junto con sus interacciones entre sí, es extremadamente valioso para el espectador.
Hace unas pocas versiones, decidimos agregar JMP analytics a las hojas de cálculo de Excel a través de nuestro propio complemento de Excel. Esto amplía el valor de una hoja de cálculo de Excel incorporando gráficos JMP y simulación de Monte Carlo.
Cuando puedo retirarme - un ejemplo
Veamos la diferencia en los dos enfoques utilizando un tema que es cercano y querido para todos nosotros: ¿Cuándo tendré suficiente dinero para jubilarme? A continuación se muestra una hoja de cálculo que creé para mostrar resultados potenciales.
Comienza con mis ahorros hasta la fecha (Balance de apertura), lo que planeo ahorrar cada año, cuánto interés podría ganar con mis inversiones y cuánto más planeo trabajar. Luego calculo el valor futuro de mi saldo de apertura y el valor futuro de las corrientes de ahorro. Solo estoy mostrando las celdas de entrada / salida, pero te darás cuenta de que he ocultado algunas columnas.
Para mostrar cómo se han calculado los números, no he ocultado las columnas (por cierto, ¿es una buena idea poder ocultar cosas en una hoja de cálculo)? Ahora, podemos ver cómo creé el modelo.
Necesitaba hacer un cálculo basado en el número de años que faltaban para la jubilación. Si ese número fuera 10, me gustaría que mis fórmulas de valor futuro tanto en el valor de apertura como en el flujo de ahorros se fijen en 10 años. Lo logré creando 30 celdas (suponiendo que tengo 30 años o menos para trabajar) cada una con la siguiente fórmula:
Las celdas en la columna D contienen = + IF ($ C $ 9> D5, D5 + 1,0)
Las celdas en la columna E contienen = IF (D6 <> 0, E5,0)
Ambos están en términos relativos o absolutos según sea necesario. Esto me permitió crear una matriz del número de años necesarios para mi cálculo.
La celda F10 contiene = FVSCHEDULE (F6, $ E $ 5: $ E $ 54) y
La celda F11 contiene = FV (C8, C9, F7) * - 1
Puede que esta no sea la mejor manera de crear este modelo, pero parece calcular los resultados correctamente. ¿Qué importa si hay una mejor manera? Lo tengo para trabajar, ¿verdad? (Si no está de acuerdo, no es un usuario típico de Excel).
Puedo ajustar las entradas y ver cómo cambian las salidas. Hermoso. Sin embargo, hay ideas potenciales que no son evidentes en la hoja de cálculo. Hay una representación gráfica que proporcionaría una mejor comprensión del modelo.
No entraré en detalles sobre cómo Excel realizaría el análisis basado en escenarios aquí. Eres libre de aprender eso de Excel. Pero quiero mostrar cómo JMP puede proporcionar una nueva perspectiva que Excel no puede.
Abre la hoja de cálculo con el complemento JMP invocado, haga clic en la pestaña JMP y seleccione Crear / Editar modelo:
A continuación, define el modelo utilizando el cuadro de diálogo Crear / Editar:
Durante la fase de definición, haga clic en el botón Elegir para seleccionar las etiquetas de entrada y la celda que contiene la cantidad de entrada. Usted establece valores mínimos, iniciales y máximos para cada celda de entrada, y luego identifica las celdas de salida en la misma forma de apuntar y hacer clic.
Después de definir el modelo, seleccione Ejecutar modelo en la pestaña del complemento JMP. JMP muestra lo siguiente:
JMP mostrará gráficamente las variables que contribuyen al resultado (ahorros futuros). Estas gráficas transmiten mucha más información que las celdas. Debido a que JMP representa gráficamente los valores mínimo / máximo para cada variable, el que tiene la mayor pendiente es el que afectará más a la variable objetivo (ahorros futuros). En este caso, es la cantidad de años que puedo trabajar para permitir que mis ahorros crezcan.
Pero el Perfilador También mostrará cómo cada variable puede afectar a las otras. Puedo usar el mouse para seleccionar la línea roja vertical en el centro de cada gráfico y arrastrarlo hacia la izquierda o hacia la derecha, al aumentar o disminuir el valor de esa variable. Cuando aumente el número de años hasta la jubilación, observe qué sucede con el gráfico de ROI anual:
La pendiente de esa línea aumenta dramáticamente, lo que indica que esta variable ahora tiene un mayor impacto en el resultado. Esto tiene sentido: cuanto más tiempo permita que su dinero gane intereses y crezca, mejor.
Pero el análisis no se detiene ahí. Después de haber establecido cada variable en un valor realista, es posible que desee probar la probabilidad de alcanzar esos valores, es decir, cuál es la probabilidad de que ahorre $ 10,000 al año en promedio o que obtenga un 7 por ciento de retorno en mi cuenta. ¿inversiones? Aquí es donde entra en juego la simulación.
Con JMP, puede definir las variables sobre las cuales ejecutar la simulación de Monte Carlo:
La imagen de arriba muestra la probabilidad de alcanzar una meta de $ 1 millón en ahorros futuros variando aleatoriamente la cantidad ahorrada por año, la tasa de rendimiento y la cantidad de años hasta la jubilación. Una probabilidad del 5 por ciento de alcanzar mi objetivo no me haría sentir bien si estos fueran números reales.
Pero el objetivo de esta serie de blogs no es mostrar cómo aprovechar una hoja de cálculo existente. El hecho es que si hubiera errores en la hoja de cálculo (y, por favor, no encuentre ninguno), el análisis de JMP sería de poco valor y posiblemente conduciría a conclusiones y decisiones incorrectas.
La próxima semana, veremos cómo puedes crear el mismo modelo en JMP.
Nota: Esta entrada de blog es parte de un Serie sobre errores en hojas de cálculo. . Los nuevos mensajes en esta serie aparecen semanalmente los miércoles.
Mike Clayton escribió:
Los ejemplos de ahorro siempre presentan lo que creo que son muy optimistas, dado el ROI dado por los sistemas de ahorro de tipo IRA habituales. Mi ROI en ahorros de 1994 a 2014, un período de 20 años de mucha volatilidad, mostró DESPUÉS DE LAS TARIFAS ROI y ANTES DE LOS IMPUESTOS de alrededor del 4 por ciento. Sin embargo, para sacar ese dinero, tengo que pagar impuestos sobre la renta ... que estoy de acuerdo que evité durante ese período de 20 años de ahorros constantes.
Entonces, ¿qué es real? ¿Cómo incluir las cuestiones fiscales en un análisis de plan de ahorro dado el uso común de IRA. Ese podría ser un modelo interesante para agregar los esfuerzos de Monte Carlo ANTES de registrarse para una IRA ROTA o REGULAR. Sería un buen webinar.
