Cómo utilizar las herramientas de análisis hipotético en Microsoft Excel

Cuando analice sus datos en Microsoft Excel, es posible que desee hacer algunas comparaciones, como «¿Qué pasa si elijo la opción A en lugar de la opción B?» Con las herramientas de análisis hipotéticas integradas en Excel, puede comparar números y cantidades más fácilmente, por ejemplo, para evaluar salarios laborales, opciones de préstamo o escenarios de ingresos y gastos.

Las herramientas de análisis de hipótesis en Excel incluyen el administrador de escenarios, la búsqueda de objetivos y la tabla de datos. Para explicar mejor el propósito de estas herramientas, veamos ejemplos de cada una.

Administrador de escenarios

Usando el Administrador de escenarios, ingrese valores que puede cambiar para ver resultados variables. Como beneficio adicional, cree un Informe de resumen de escenario para comparar las cantidades o los números uno al lado del otro.

Por ejemplo, digamos que está planeando un evento y decidiendo entre algunos temas que tienen diferentes costos. Configure los precios de cada tema para ver cuánto costarán para compararlos.

Cree varios escenarios para diferentes situaciones para ayudar en su toma de decisiones.

Cómo usar el administrador de escenarios

Si está listo para comparar diferentes situaciones, como el ejemplo anterior, siga estos pasos para usar el Administrador de escenarios en Excel.

• Ingrese los datos para su primer escenario en su hoja. Usando el ejemplo anterior, estamos comparando los costos del tema para nuestro evento e ingresando los gastos para nuestro tema de playa en las celdas A2 a A6 y sus costos en las celdas B2 a B6. Estamos sumando los precios en la celda B7 para ver el costo total.

• Para agregar estos detalles al Administrador de escenarios, vaya a la pestaña «Datos» y la sección «Pronóstico» de la cinta. Abra el menú desplegable «Análisis hipotético» y elija «Administrador de escenarios».

• Haga clic en «Agregar».

• Asigne un nombre a su escenario (estamos usando «Tema de la playa») e ingrese las celdas que ajustará en el campo «Cambio de celdas». Alternativamente, arrastre el cursor a través de las celdas de su hoja para completar ese campo. Opcionalmente, ingrese un comentario que no sea el predeterminado y haga clic en «Aceptar».

• Los valores en el campo «Cambio de celdas» deben coincidir con los de su hoja, pero puede ajustarlos aquí. Haga clic en «Aceptar» para continuar.

• Ahora que ha agregado el primer escenario, lo verá en la lista del Administrador de escenarios. Seleccione «Agregar» para configurar su próximo escenario.

• Ingrese los detalles para el segundo escenario como lo hizo para el primero. Incluya el nombre, las celdas cambiantes y el comentario opcional, luego haga clic en «Aceptar». En nuestro ejemplo, estamos ingresando «Vegas Theme» y el mismo rango de celdas, B2 a B6, para ver fácilmente una comparación en el lugar.

• Introduzca los valores para su segundo escenario en la ventana Valores de escenario. Si está utilizando las mismas celdas que la primera, las verá completas. Ingrese los que desea usar y haga clic en «Aceptar».

• Elija el escenario que desea ver de la lista en la ventana Administrador de escenarios y haga clic en «Mostrar».

• Los valores en su hoja se actualizarán para mostrar el escenario seleccionado.

• Continúe agregando y mostrando escenarios adicionales para ver los valores actualizados en su hoja. Una vez que encuentre el que desea conservar en su hoja, seleccione «Cerrar» para salir del Administrador de escenarios.

Ver el resumen del escenario

Vea el Resumen de escenarios para ver todos sus escenarios a la vez para realizar una comparación en paralelo.

• Regrese a «Datos -> Análisis hipotético -> Administrador de escenarios», luego haga clic en «Resumen».

• Elija el tipo de informe que desea ver: «Resumen de escenario» o » Informe de tabla dinámica de escenario». Opcionalmente, si desea mostrar su resultado, ingrese la celda que lo contiene y haga clic en «Aceptar».

En nuestro ejemplo, estamos seleccionando «Resumen del escenario», lo que coloca el informe en una nueva pestaña de hoja. También notará que el informe puede incluir opcionalmente la agrupación de celdas para ocultar ciertas partes del informe.

Tenga en cuenta que si ajusta los detalles en el Administrador de escenarios, el informe no se actualiza automáticamente, por lo que debe generar un nuevo informe.

Buscar objetivo

La herramienta Goal Seek funciona de manera algo opuesta al Scenario Manager. Con esta herramienta, tiene un resultado conocido e ingresa diferentes variables para ver cómo puede llegar a ese resultado.

Por ejemplo, tal vez venda productos y tenga una meta de ganancias anuales. Quiere saber cuántas unidades necesita vender o a qué precio para alcanzar su objetivo. Goal Seek es la herramienta ideal para encontrar respuestas.

Con Goal Seek, solo se puede usar una variable o valor de entrada. Use esto para aquellos escenarios en los que tiene los valores restantes por adelantado.

Cómo utilizar la búsqueda de objetivos

En un ejemplo de la herramienta Goal Seek, tenemos 1500 productos para vender y queremos obtener una ganancia de $52 000. Estamos utilizando Goal Seek para determinar a qué precio debemos vender nuestro producto para alcanzar ese objetivo.

• Comience ingresando los valores y fórmulas en su hoja, según su escenario. Usando nuestro ejemplo, estamos ingresando la cantidad actual en la celda B2, el precio estimado en la celda B3 y una fórmula para la ganancia en la celda B4, que es =B2*B3.

• Vaya a la pestaña «Datos», abra el menú desplegable «Análisis hipotético» y elija «Buscar objetivo».

• Ingrese los siguientes valores, luego haga clic en «Aceptar»:
  • Establecer celda : la referencia de celda (que contiene la fórmula) para el valor que desea cambiar para alcanzar el resultado deseado. En nuestro ejemplo, esta es la celda B4.
  • A valor : el valor de su resultado deseado. Para nosotros, esto es 52000.
  • Al cambiar de celda : la referencia de celda que desea cambiar para llegar al resultado. Estamos usando la celda B3, ya que queremos cambiar el precio.

• Haga clic en «Aceptar» para ver la actualización del cuadro «Estado de búsqueda de objetivo» para mostrar una solución y su hoja cambia para contener los valores ajustados. En nuestro ejemplo, debemos vender nuestro producto por $35 para alcanzar nuestra meta de $52,000. Seleccione «Aceptar» para conservar los nuevos valores en su hoja.

¿Sabes ? Hay muchas cosas que puedes hacer en Microsoft Excel, incluida la inserción de minigráficos y minigráficos .

Tabla de datos

Use una tabla de datos en Excel para ver un rango de posibles situaciones numéricas.

Para un ejemplo ideal, puede estar revisando las opciones de préstamo. Al ingresar diferentes tasas de interés, puede ver cuál sería su pago mensual con cada una. Esto le ayuda a determinar qué tasa comprar o discutir con su prestamista.

Con una tabla de datos, solo puede usar hasta dos variables. Si necesita más, use el Administrador de escenarios.

Cómo usar una tabla de datos

Siga los pasos a continuación para usar una tabla de datos, la tercera herramienta de análisis hipotético. Tenga en cuenta la configuración de datos.

Por ejemplo, estamos usando una tabla de datos para ver cuánto serían los pagos de nuestro préstamo con diferentes tasas de interés a través de los siguientes datos:

• Tasa de interés, número de pagos y monto del préstamo en las celdas B3 a B5.
• Una columna Tasa con las tasas de interés para explorar en las celdas C3 a C5.
• Una columna de Pago con la fórmula para el pago actual en la celda D2.
• Las celdas de resultado debajo de la fórmula en la columna Pago, ingresadas automáticamente usando la herramienta Tabla de datos. Esto nos muestra los montos de pago por tasa de interés.

Cuando ingrese sus datos y fórmula en su hoja, tenga en cuenta lo siguiente:

• Utilice un diseño orientado a filas o columnas. Determinará la ubicación de su fórmula.
• Para un diseño orientado a filas, coloque su fórmula en la celda una columna a la izquierda del valor inicial y una celda debajo de la fila que contiene los valores.
• Para un diseño orientado a columnas, coloque su fórmula en la celda una fila arriba y una celda a la derecha de la columna que contiene los valores.

En nuestro ejemplo, usamos una sola variable (la tasa de interés) en un diseño orientado a columnas. Tenga en cuenta la ubicación de nuestra fórmula en la celda D2 (una fila arriba y una celda a la derecha de nuestros valores).

• Ingrese sus propios datos y seleccione las celdas que contienen la fórmula, los valores y las celdas de resultados. En nuestro ejemplo, estamos seleccionando las celdas C2 a D5.

• Vaya a la pestaña «Datos», abra el menú desplegable «Análisis hipotético» y elija «Tabla de datos».

• Ingrese la celda que contiene la variable cambiante para sus datos en el cuadro Tabla de datos. Para un diseño orientado a filas, use la «celda de entrada de fila» y para un diseño orientado a columnas, use la «celda de entrada de columna». En nuestro ejemplo, estamos usando este último e ingresando «B3», que es la celda que contiene la tasa de interés.

• Después de hacer clic en «Aceptar» en el cuadro Tabla de datos, debería ver que las celdas de resultados se llenan con los datos que espera. Nuestro ejemplo incluye el monto de nuestro pago para cada tasa de interés diferente.

Tenga en cuenta que puede usar dos variables en su tabla de datos en lugar de una, probar el diseño orientado a filas o ver más detalles y limitaciones de esta herramienta de análisis hipotético en la página de soporte de Microsoft para la característica .

Preguntas frecuentes

¿Cómo edito un escenario existente en Excel?

Puede cambiar el nombre y los valores de un escenario mediante el Administrador de escenarios. Abra la herramienta seleccionando «Datos -> Análisis hipotético -> Administrador de escenarios». Elija el escenario de la lista y haga clic en «Editar» a la derecha. Realice sus cambios y seleccione «Aceptar» para guardarlos.

Si creó un informe de resumen de escenario inicialmente, deberá volver a generar el informe para ver los detalles actualizados.

¿Puedo evitar que Excel vuelva a calcular una tabla de datos?

Si su libro de trabajo contiene una tabla de datos, Excel vuelve a calcular automáticamente esa tabla de datos, incluso si no hay cambios. Sin embargo, puede desactivar esta opción si lo desea.

Vaya a la pestaña «Fórmula», abra el menú desplegable «Opciones de cálculo» en el grupo Cálculo y seleccione «Automático excepto para tablas de datos».

Para recalcular su tabla de datos manualmente, seleccione la(s) fórmula(s) y presione F9.

¿Qué otras herramientas de análisis ofrece Excel?

Excel proporciona muchos tipos diferentes de herramientas de análisis de datos, según lo que necesite. Para nombrar algunos, puede usar el formato condicional para resaltar datos específicos, Quick Analysis para dar formato, gráficos y tablas, y Power Query para un análisis de datos sólido.

También puede usar funciones básicas de Excel, como filtros , para restringir sus datos, segmentaciones para filtrar tablas y gráficos, y la herramienta Analizar datos para obtener respuestas a preguntas sobre sus datos.

Para obtener asistencia adicional sobre estas funciones y más, diríjase a la pestaña «Ayuda» en Excel en Windows, o use la opción de menú «Cuéntame» en Excel en Mac.

Crédito de la imagen: Pixabay . Todas las capturas de pantalla por Sandy Writerhouse.