Unlimited Powerpoint templates, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Microsoft Excel
Business

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

by
Difficulty:IntermediateLength:ShortLanguages:

Spanish (Español) translation by Elías Nicolás (you can also view the original English article)

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

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.

Excel Scenerio Manager Practice Worksheet
Administrador de escenarios de análisis de Excel de Excel - Hoja de trabajo de práctica

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.

Display formulas using Excel Keyboard Shortcut
Mostrar fórmulas mediante el método abreviado de teclado en Excel

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.
Excel What-If Analysis Scenario Manager
Analisis Excel What-If : 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.

Excel Scenario Manager dialog box
Cuadro de diálogo Administrador de escenarios

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.
Excel Scenario Protection options
Opciones de escenarios: protección

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.

Scenario Values dialog box
Cuadro de diálogo Valores de escenario

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.

Scenario Manager dialog
Volver al cuadro de diálogo Administrador de escenarios principal

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.

Descripción

Valor

Nombre del escenario

Lugar medio

B4 (# de asientos)

800

B6 (artista)

7500

B7 (alquiler de local)

1000

B8 (amplificación)

600

B9 (iluminación)

350

B10 (venta de entradas)

250

B11 (seguridad)

300

B12 (seguro)

250

B17 (precio del billete)

35

Nombre del escenario

Lugar Grande

B4 (# de asientos)

1500

B6 (artista)

12000

B7 (alquiler de local)

3500

B8 (amplificación)

1000

B9 (iluminación)

700

B10 (venta de entradas)

700

B11 (seguridad)

1000

B12 (seguro)

500

B17 (precio del billete)

50

Nombre del escenario

Lugar muy grande

B4 (# de asientos)

5000

B6 (artista)

25000

B7 (alquiler de local)

10000

B8 (amplificación)

2500

B9 (iluminación)

2000

B10 (venta de entradas)

500

B11 (seguridad)

2500

B12 (seguro)

2500

B17 (precio del billete)

50

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:

Scenario Manager screen
Pantalla del gestor de escenarios

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.

Updated Values
Valores actualizados

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.
Profit or Loss Cell Result
Celda de Resultado: Pérdidas y ganancias

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

Scenario Summary worksheet
Hoja de trabajo de resumen de escenarios

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.

Outline feature
Características de Esquema

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.
Scenario Summary Issues
Resumen de escenarios

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.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.