Cambiar entre varios conjuntos de valores mediante escenarios

Existen tres tipos de herramientas de análisis de What-If de Excel: Escenarios, Tablas de datos y Buscar objetivo. Los escenarios y las tablas de datos llevan conjuntos de valores de entrada y se proyectan hacia delante para determinar posibles resultados. Buscar objetivo difiere de escenarios y tablas de datos en que se necesita un resultado y proyectos hacia atrás para determinar los posibles valores de entrada que producen ese resultado.

Cada escenario puede albergar hasta 32 valores variables. Si desea analizar más de 32 valores y los valores representan solo una o dos variables, puede usar tablas de datos. Aunque está limitado solo a una o dos variables (una para la celda de entrada de fila y otra para la celda de entrada de columna), una tabla de datos puede incluir tantos valores variables diferentes como desee. Un escenario puede tener un máximo de 32 valores diferentes, pero puede crear tantos escenarios como desee.

Además de estas tres herramientas, puede instalar complementos que le ayuden a realizar What-If Análisis, como el complemento Solver. El complemento Solver es similar a Buscar objetivo, pero puede adaptarse a más variables. También puede crear previsiones mediante el controlador de relleno y diversos comandos integrados en Excel. Para modelos más avanzados, puede usar el complemento Herramientas para análisis.

Supongamos que desea crear un presupuesto pero no está seguro de sus ingresos. Mediante escenarios, puede definir diferentes valores posibles para los ingresos y, a continuación, cambiar entre escenarios para realizar análisis de hipótesis.

Por ejemplo, supongamos que el peor escenario presupuestario del caso es Ingresos brutos de 50.000 $ y Costos de bienes vendidos de 13.200 $, dejando 36.800 $ en beneficio bruto. Para definir este conjunto de valores como un escenario, primero escriba los valores en una hoja de cálculo, como se muestra en la siguiente ilustración:

Las Celdas cambiantes tienen valores que escribe, mientras que la celda Resultado contiene una fórmula que se basa en el Cambio de celdas (en esta celda de ilustración B4 tiene la fórmula =B2-B3).

A continuación, use el cuadro de diálogo Administrador de escenarios para guardar estos valores como un escenario. Vaya a la pestaña Datos > What-If Análisis > Administrador de escenarios > Agregar.

En el cuadro de diálogo Nombre del escenario, asigne un nombre al escenario Peor caso y especifique que las celdas B2 y B3 sean los valores que cambian entre escenarios. Si selecciona el cambio de celdas en la hoja de cálculo antes de agregar un escenario, el Administrador de escenarios insertará automáticamente las celdas por usted, de lo contrario, puede escribirlas a mano, o usar el cuadro de diálogo de selección de celdas a la derecha del cuadro de diálogo Cambiar celdas.

Protección : también puede proteger los escenarios, por lo que en la sección Protección active las opciones que desee o desactívelas si no desea ninguna protección.

• Seleccione Evitar cambios para impedir la edición del escenario cuando la hoja de cálculo está protegida.

• Seleccione Oculto para evitar que se muestre el escenario cuando la hoja de cálculo está protegida.

Ahora supongamos que su escenario de presupuesto De mejor caso es Ingresos brutos de 150.000 $ y Costos de bienes vendidos de 26.000 $, lo que deja 124.000 $ en beneficio bruto. Para definir este conjunto de valores como un escenario, cree otro escenario, asímbralo Best Case y proporcione valores diferentes para la celda B2 (150 000) y la celda B3 (26 000). Dado que Beneficio bruto (celda B4) es una fórmula, la diferencia entre Ingresos (B2) y Costos (B3), no cambia la celda B4 para el escenario Mejor caso.

Después de guardar un escenario, estará disponible en la lista de escenarios que puede usar en sus análisis de hipótesis. Dados los valores de la ilustración anterior, si decide mostrar el escenario Mayúsculas y minúsculas, los valores de la hoja de cálculo cambiarían para ser similares a la siguiente ilustración:

Puede haber ocasiones en las que tenga toda la información en una hoja de cálculo o libro necesarios para crear todos los escenarios que desea considerar. Sin embargo, es posible que desee recopilar información de escenarios de otros orígenes. Por ejemplo, supongamos que está intentando crear un presupuesto de empresa. Puede recopilar escenarios de diferentes departamentos, como Ventas, Nóminas, Producción, Marketing y Legal, porque cada uno de estos orígenes tiene información diferente para usar en la creación del presupuesto.

Puede recopilar estos escenarios en una hoja de cálculo mediante el comando Combinar . Cada origen puede proporcionar tantos valores de celda como desee. Por ejemplo, es posible que desee que cada departamento proporcione proyecciones de gastos, pero solo necesite proyecciones de ingresos de unos pocos.

Cuando elija combinar, el Administrador de escenarios cargará un Asistente para escenarios de combinación, que mostrará todas las hojas de cálculo del libro activo, así como la lista de otros libros que podría haber abierto en ese momento. El asistente le indicará cuántos escenarios tiene en cada hoja de cálculo de origen que seleccione.

Cuando recopile diferentes escenarios de diferentes orígenes, debe usar la misma estructura de celdas en cada uno de los libros. Por ejemplo, los ingresos siempre pueden ir en la celda B2 y los gastos siempre pueden ir en la celda B3. Si utiliza estructuras diferentes para los escenarios de diferentes orígenes, puede ser difícil combinar los resultados.

Para comparar varios escenarios, puede crear un informe que los resuma en la misma página. El informe puede enumerar los escenarios en paralelo o presentarlos en un informe de tabla dinámica.

Un informe de resumen de escenario basado en los dos escenarios de ejemplo anteriores tendría un aspecto similar al siguiente:

Observará que Excel ha agregado automáticamente niveles de agrupación , lo que expandirá y contraerá la vista al hacer clic en los diferentes selectores.

Aparece una nota al final del informe de resumen en la que se explica que la columna Valores actuales representa los valores de las celdas modificadas en el momento en que se creó el informe de resumen del escenario y que las celdas que cambiaron para cada escenario se resaltan en gris.