Los escenarios, a grandes rasgos, permiten realizar una previsión de un conjunto de situaciones posibles sin alterar los datos originales de la hoja de cálculo. Cuando se emplean adecuadamente, permiten hacer comparaciones tomando como base los mismos cálculos, pero con distintos valores. Las auditorías son otra herramienta de Excel que permiten localizar de forma rápida aquel dato que afecta de forma negativa una fórmula o función, la forma en que se marca el error consiste en que Excel muestra una flecha directamente sobre el dato erróneo para una mayor rapidez en su localización.

Administrador de escenarios

Utiliza un análisis de hipótesis para modificar valores de una serie de celdas y de esta forma ver cómo afectan estos cambios el resultado en una hoja de cálculo. Es decir, con la creación de escenarios podrás visualizar este comportamiento a partir de los valores de entrada. Si utilizas escenarios en una situación particular, debes de tomar en cuenta que éstos permiten un máximo de 32 variables, y eso posibilita que puedas crear tantos escenarios como sean necesarios.

Si necesitas crear un escenario para analizar distintas alternativas, esta herramienta la encuentras disponible en: ficha Datos > grupo Previsión > Análisis de hipótesis > Administrador de escenarios.

Para crear un escenario debes tomar en cuenta los siguientes pasos:

1. Crear una hoja de cálculo que va a contener el escenario, en ella debes introducir la información, fórmulas y dejar en blanco los valores que van a constituir el escenario.
2. Ir a la ficha Datos > Previsión > Análisis de hipótesis > Administrador de escenarios.
3. En el cuadro de diálogo Administrador de escenarios debes hacer clic en Agregar.



4. Se abre el cuadro de diálogo Agregar escenario, y debes escribir el nombre de tu escenario.
5. En el campo Celdas cambiantes debes introducir las referencias de las celdas que deseas cambiar.
6. En la sección Comentario, puedes incluir una breve descripción de tu escenario.
7. En la sección Protección, selecciona la opción deseada.



8. Haz clic en Aceptar para abrir el cuadro de diálogo Valores del escenario.
9. En Valores del escenario, introduce los valores que deseas para las celdas cambiantes.



10. Para crear el escenario, haz clic en Aceptar.
11. Si deseas incluir otro escenario, debes hacer clic en el botón Agregar del cuadro de diálogo Valores del escenario.
12. Cuando termines de crear los escenarios requeridos, debes hacer clic en Aceptar y en el botón Cerrar del cuadro de diálogo Administrador de escenarios.

El administrador de escenarios tiene los siguientes elementos:

• Agregar: esta opción te permite crear un escenario.
• Eliminar: de una lista de escenarios creados, es posible realizar la eliminación presionando este botón.
• Modificar: cuando tienes creado un escenario o más, utiliza esta opción para hacer un cambio que no consideraste al inicio de su creación.
• Combinar: esta opción te permite hacer una combinación de escenarios que fueron creados en el mismo u otro libro a través de sus hojas de cálculo.
• Resumen: te permite mostrar los escenarios en forma de resumen eligiendo dos opciones, resumen y tabla dinámica.
• Celdas cambiantes: es la celda o grupo de celdas que especificarás que sufrirá un cambio en cada escenario creado.
• Comentario: este espacio te ayuda a documentar el propósito de tu escenario.

Ejemplo: en una tienda departamental desea adquirir un refrigerador y esta tienda ofrece un plan de pago de 6, 12 o 18 meses.

1. En una hoja nueva de Excel ingresa la siguiente información:



2. Introduce en la celda B4 un plazo de 6 meses.
3. Introduce en la celda B5 la función pago con los siguientes argumentos:

=-PAGO(B3/12, B4, B2)



4. Crea un escenario con el nombre de Refrigerador Plazos 6 meses y configúralo de la siguiente manera:





Como se te mencionó anteriormente, en la sección Protección tienes dos opciones: Evitar cambios y Ocultar. La primera te ayuda a evitar que el escenario pueda ser modificado, y la segunda no te mostrará el escenario en la lista de los escenarios siempre y cuando la hoja de cálculo se encuentre protegida.

5. Haz clic en Aceptar para incluir los valores del escenario que cambiarán, la celda sufrirá cambio en el plazo elegido de 6 meses.





6. Haz clic en “Agregar” y repite los pasos 4 y 5 para continuar creando los siguientes dos escenarios que corresponden a los plazos 12 y 18.





7. Ya has terminado con los tres escenarios respecto a los plazos otorgados para la compra del refrigerador, haz clic en “Aceptar” y observa como el “Administrador de escenarios” contiene ya los tres escenarios previamente creados:





8. Ahora toca el turno de revisar que todos los escenarios creados te muestren la cantidad a pagar mensualmente en función del plazo elegido. Para ello, en el “Administrador de escenarios” en la sección “Escenario” elige el escenario “Refrigerador Plazo 12 meses” y posteriormente haz clic en el botón “Mostrar”.





Observa que el resultado que te muestra corresponde al pago que efectuarás para un plazo de 12 meses.

El Administrador de escenarios contiene otras opciones:



Hacerlo por cualquiera de estas opciones te permite visualizar con más precisión la diferencia entre cada escenario y elegir el más conveniente.

9. Elige la opción Resumen, en el cuadro de diálogo Resumen del escenario y verifica que la celda que aparece en el campo Celdas de resultado, sea donde se va a mostrar el resultado y haz clic en Aceptar.



10. Excel te despliega todos los escenarios creados para así tener una visión más amplia y elegir el más conveniente de adquirir a un plazo específico:

Considera que la opción Combinar del cuadro de diálogo Administrador de escenarios, te ayuda a realizar una integración de los diversos escenarios creados y así elegir, de todas las posibilidades, la más conveniente.

Auditoría de fórmulas

Utiliza la herramienta Auditoría de fórmulas para analizar un conjunto de celdas y valores que son utilizados de forma directa en una fórmula o función. Esta herramienta permite localizar de una forma rápida los datos que afectan de forma negativa a una fórmula. En hojas de cálculo complejas es indispensable utilizar esta herramienta para rastrear qué celdas participan en el cálculo, o bien, qué ha provocado el error y, en otros casos, darle un seguimiento a la ejecución de una fórmula que está funcionando correctamente.

La herramienta auditoría de fórmulas la encuentras en la ficha Fórmulas > grupo Auditoría de fórmulas.

En este grupo dispones de distintas formas de realizar un seguimiento:

• Rastrear precedentes: muestra flechas de color azul que apuntan a las celdas y en donde los valores afectan a la fórmula.
• Rastrear dependientes: realiza la operación similar a rastrear precedentes, sólo que aquí debes tener el cursor en la celda para que se revele qué fórmulas utilizan su valor para calcular.
• Quitar flechas: elimina las flechas de la hoja de cálculo.
• Mostrar fórmulas: muestra las fórmulas existentes en las celdas en lugar de sus resultados.
• Comprobación de errores: este comando te despliega varias opciones relacionadas con la inspección de errores en la hoja. Al ejecutar este comando, tienes las opciones de rastrear error y referencias circulares. El primero te mostrará las celdas que contienen un error en una fórmula y, para ello, debes colocar el cursor en la celda para activar la opción; la segunda, muestra aquellas fórmulas que no pueden resolverse porque se emplea en ella la misma celda en la que se va a calcular el resultado.
• Evaluar fórmula: muestra un informe paso a paso sobre el error de una fórmula escrita en la celda.

Ejemplo:

Para este ejemplo, vas a retomar el reto que realizaste en la lección 5 de este curso.

Realiza las siguientes operaciones:

1. Utiliza la opción Rastrear precedentes en la celda H3 y observa cómo la flecha azul se incluye en la celda que afecta el valor de la celda (B2).





2. Al utilizar la herramienta “Rastrear dependiente”, Excel te confirma a través de un cuadro de diálogo que no encuentra dependencias de celdas que tengan una relación con la celda G11.





3. Utiliza el comando Mostrar fórmulas para desplegar todas las fórmulas contenidas en la hoja de cálculo activa.





4. Utiliza la herramienta comprobación de errores para visualizar qué está ocasionando la inconsistencia en la fórmula de la celda H4, la fórmula fue cambiada a la siguiente manera:



=$B$2-SUMA($F2:F$3)



5. Observa cómo en el cuadro de diálogo Comprobación de errores tienes varias opciones y solo debes elegir una de ellas para corregir la inconsistencia encontrada, haz clic en el botón Reanudar y observa lo siguiente.





Las opciones que tienes son las siguientes:

• Copiar fórmula de arriba: Excel detecta que la fórmula de la celda de arriba esta correcta, por lo que al elegir esta opción hará la modificación correspondiente.
• Ayuda sobre este error: tendrás acceso a la ayuda online de Microsoft para buscar y corregir el error marcado.
• Ignorar error: esto hace que no consideres la advertencia o el error de la celda y sigas trabajando en la hoja de cálculo activa.
• Modificar en la barra de fórmulas: esta opción permite que, si con tu experiencia has detectado el error, modifiques lo que está ocasionando la inconsistencia para que, posteriormente, al ejecutar esta herramienta, no encuentre el mismo error que ha sido corregido.
• Anterior o Siguiente: te permite avanzar entre las celdas del conjunto de datos y así detectar otras posibles inconsistencias con las fórmulas.
• Opciones: al hacer clic en él, te lleva a las distintas opciones que Excel te ofrece, es decir, es equivalente hacerlo desde la ficha Archivo > Opciones.


6. Elije la opción Modificar en la barra de fórmulas, la celda H4 queda de la siguiente manera.



=$B$2-SUMA($F$3:F4)

7. Una vez hecho el paso 6, debes de hacer otro cambio en la misma celda H4 y así poder utilizar la herramienta Rastrear error.



=$B$2-SUMA($F$3:F4)






Observa que, con este cambio, la opción Rastrear error, muestra algunas flechas en azul en las celdas que intervienen en las fórmulas. Si haces nuevamente clic en la opción aparecen más flechas azules y así, por cada clic que des, irán apareciendo flechas azules enlazando resultados con las celdas precedentes hasta que ya no haya más celdas qué asociar.

Para ir eliminando grupos de flechas precedentes, debes de utilizar el comando Quitar flechas y la opción Quitar un nivel de precedentes y, si definitivamente deseas eliminar todas las flechas mostradas, debes utilizar la opción Quitar todas las flechas.






8. Utiliza la opción Evaluar fórmulas para que el asistente paso a paso te ayude a encontrar el error o inconsistencia de una forma más detallada, utiliza como ejemplo el error de la celda H4.
9. En el primer cuadro de diálogo Evaluar fórmula, te aparece en subrayado el primer argumento de la fórmula y debes continuar con el asistente dando clic en la opción Evaluar.







Observa que en el último cuadro de diálogo de Evaluar fórmula aparece el resultado que se muestra en la celda de la hoja de cálculo, y que finalmente esta opción te ayuda a analizar paso a paso los valores que va considerando cada argumento de la función o fórmula y, de este modo, identificar el error. Para terminar con el asistente sólo debes hacer clic en Cerrar.

La opción Referencias circulares sólo aparecerá disponible cuando Excel detecte que en tu hoja de cálculo existe una de ellas.





Una referencia circular es aquella que hace referencia a sí misma dentro de la fórmula o función. Esto es porque se produce un bucle o iteración en donde Excel posee un valor de iteraciones por defecto, lo cual impide que un cálculo se ejecute indefinidamente a consecuencia de este error.



10. Utilizando nuevamente la celda H4 como ejemplo, ahora modifica la fórmula como sigue.





11. Observa que te aparece el cuadro de diálogo en donde se te advierte de la referencia circular, al hacer clic en Aceptar para probar o quitar la referencia encontrada, el comando Referencia circular se habilita y de esta forma Excel muestra la celda en donde existe la referencia circular.





12. Posteriormente, lo que toca hacer, es corregir los valores de la celda H4 para que no se siga provocando la referencia circular.

Las anteriores pantallas se obtuvieron directamente del software, para fines educativos.

Asegúrate de:

• Realizar escenarios y elegir el más factible para la toma de una decisión.
• Utilizar la herramienta “Rastrear precedentes” para “Corregir errores” a través de la herramienta “Auditoría de fórmulas”.
• Utilizar la herramienta “Rastrear dependiente” para Mostrar las dependencias de celdas.

Revisa la sección de recursos, en donde encontrarás contenido multimedia que te ayudará a reforzar tu aprendizaje.