Analisis Excel What-If : Cómo utilizar el Administrador de escenarios

El Administrador de Escenarios es una excelente, pero a menudo se pasa por alto la función de Análisis de Qué-Si de Excel que le permitirá intercambiar múltiples conjuntos de datos en una hoja de cálculo e incluso compararlos lado a lado. Esta técnica puede ayudarle a decidir entre múltiples cursos de acción o cuáles son las implicaciones entre varias posibilidades.

Por ejemplo, digamos que somos promotores de conciertos y queremos producir un show. Tenemos que decidir qué lugar usar porque eso determinará los costos, ingresos, ganancias o pérdidas, y el talento a contratar.

En este ejercicio, utilizaremos el Administrador de escenarios para comparar cuatro conjuntos de números: ubicaciones pequeñas, medianas, grandes y muy grandes y sus costos e ingresos asociados, asumiendo que cada programa se vende.

Screencast

Excel What-If Analysis: How to Use the Scenario Manager

Vea el tutorial completo arriba, o siga leyendo a través del paso a paso la siguiente versión escrita, pero primero descargue la hoja de práctica para que pueda trabajar a través de los ejercicios.

Descargue la hoja de práctica

Puede volver a crear la hoja a continuación o descargar practice-files.zip, que contiene la hoja de cálculo scenarios.xlsx a continuación y una hoja de cálculo del ejercicio Administrador de escenarios de Excel completado.

Esta hoja muestra actualmente el más pequeño de los lugares, que tiene 300 asientos. Los números en cajas anaranjadas se calculan, por lo que no los ajustaremos en los escenarios. Estas son las fórmulas que usan los números calculados:

• B13: Costes totales agrega los costes de las celdas arriba.
• B19: Venta de entradas multiplica el número de asientos x precio del billete (B4 * B17).
• B20: Merchandising (camisetas, recuerdos) asume que los clientes compran un promedio de $ 5 / asiento (5 * B4).
• B21: Alimentos y bebidas asume que los clientes compran un promedio de $ 15 / asiento (15 * B4).
• B22: El ingreso total suma los ingresos de las celdas anteriores.
• B24: El beneficio o pérdida sustraen el costo total del ingreso total (B22-B13).

Consejo: presione Ctrl + `(marca de acento) para mostrar todas las fórmulas de la hoja a la vez. Presione Ctrl + ` nuevamente para volver la hoja a la normal. Este acceso directo es idéntico en Windows y Mac.

1. Haga su primer escenario

Paso 1: Configurar el primer escenario

Ahora vamos a profundamente un análisis What-If en Excel . Abriremos el gestor de escenarios y comenzaremos:

1. Primero, seleccione todas las celdas que cambiarán. Para ello, haga clic en B4, mantenga pulsada la tecla Ctrl (tecla de comando en el Mac) mientras arrastra de B6 a B12, luego presione Ctrl + clic (Comando + clic en Mac) B17.
   
2. En la cinta de opciones, seleccione la pestaña Datos > Análisis de hipótesis > Administrador de escenarios.

Esto muestra el cuadro de diálogo Administrador de escenarios. Puesto que no hemos creado ningún escenario todavía, dice que no hay ninguno definido.

Cada escenario será un conjunto de las celdas que acaba de seleccionar, que contienen valores únicos. El primer conjunto serán los valores actuales.

Paso 2: Crear el primer escenario

1. En el cuadro de diálogo, haga clic en Agregar.
2. Introduzca el nombre Original values.
3. Las céldas que cambian son las que seleccionaste. Si ha seleccionado diferentes celdas por error, puede introducir las correctas aquí (ver imagen a continuación).
4. Introduzca un comentario si lo desea. Esto es opcional.
5. Las casillas de verificación para Protección son sólo si desea proteger la hoja de los cambios. No haremos eso en este ejercicio, así que ignore estas opciones.

Haga clic en Aceptar. El cuadro de diálogo Valores de escenario muestra una lista de todas las celdas del escenario y sus valores actuales. Tenga en cuenta que no puede cambiar el tamaño de este cuadro, así que utilice su barra de desplazamiento para ver todos ellos.

Por ahora, no hay nada que cambiar, pero tenga en cuenta el botón Añadir. Una forma rápida de crear varios escenarios uno tras otro es hacer clic en este botón Añadir después de introducir valores. Esto mostrará inmediatamente la pantalla Añadir escenario.

Por ahora, haga clic en Aceptar. Esto trae de vuelta el cuadro de diálogo principal del gestor de escenarios, mostrando el primero listado.

El Administrador tiene botones para agregar un nuevo escenario, eliminar uno, editar uno, fusionar en un escenario de otro libro abierto y crear un resumen. El resumen es la parte más genial, y lo haremos a continuación.

2. Crear escenarios adicionales

Paso 1: Agregar más escenarios

Haga clic en Agregar. Esto es lo mismo que hacer clic en el botón Agregar en el paso anterior.

Cree 3 escenarios más utilizando los datos de la tabla siguiente. El concepto general es que los lugares más grandes tendrán costos más altos – no siempre en proporción– junto con la capacidad de cobrar precios más altos del boleto dando por resultado mayores réditos. Por la simplicidad, suponga que si un concierto tiene más de una actuacion, se combinan en la categoría Artista.

La manera más rápida de ingresar los números es no usar el ratón. Simplemente escriba un número, presione la tecla Tab, escriba otro número, presione la tecla Tab, y así sucesivamente.

Después de entrar en el último escenario, haga clic en Aceptar para volver a la pantalla principal del Administrador de escenarios. Debe tener un aspecto como este:

Paso 2: Cambiar Entre

La hoja todavía muestra los valores originales, así que aquí está la primera característica interesante: Haga doble clic en uno de los nombres de escenario de la lista. La hoja se actualiza con esos valores.

Paso 3: Ver todos los escenarios a la vez

1. Haga clic en el botón Resumen.
2. Eso confirma que desea crear un resumen, no una tabla dinámica, así que deje el botón de opción predeterminado seleccionado.
3. También confirma que la celda de resultado principal es el Beneficio o Pérdida en B24.

Haga clic en Aceptar. Esto crea una nueva hoja en el libro, denominada Resumen de escenario.

Paso 4: Uso del resumen del escenario

Esto muestra los valores que la hoja muestra actualmente (podría haber cambiado estos manualmente) así como los conjuntos de números de los cuatro escenarios.

Observe los pequeños símbolos más y menos en los márgenes. Éstos son parte de la característica del grupo y del esquema de Excel, que puede utilizar por separado del Administrador de escenarios.. El botón Esquema también se encuentra en la pestaña Datos de la cinta, hasta el final.

Haga clic en cualquiera de los signos menos para contraer la hoja, de modo que sólo muestre datos de resumen o haga clic en los signos más para expandir y mostrar detalles.

Paso 5: Dos cosas para ser conscientes

1. Ninguno de los valores son dinámicos. Si cambia los datos subyacentes en la hoja original, los valores de esta hoja no cambiarán. Deberá crear un nuevo resumen.
2. En columna C, ve listas las referencias de celda, no sus etiquetas (artista, alquiler de sede, etc). Si desea ver las etiquetas, estire la columna C y escríbalas manualmente.

Conclusion

La próxima vez que desee comparar varios conjuntos de datos, tal vez para decidir entre varios cursos de acción, dar a Análisis de Excel What-If - Administrador de escenarios una prueba. Podría mostrar exactamente lo que necesita para tomar una decisión.