Herramientas para el Análisis de Hipótesis en Excel

Herramientas para el Análisis de Hipótesis en Excel

1. ANALISIS DE HIPÓTESIS

El análisis de hipótesis es el proceso que consiste en cambiar los valores de un conjunto de celdas para ver cómo estos cambios afectan al resultado de un modelo de cálculo creado en Excel.

Mediante el uso de las herramientas de análisis de hipótesis, podemos probar distintos valores de entrada en una o más formulas y así explorar diversos resultados.

El análisis de hipótesis permite identificar condiciones peligrosas o poco favorables en un modelo de cálculo; que pueden producir una consecuencia indeseable en lo que deseamos obtener con dicho modelo. La idea es ir perfeccionando nuestro modelo matemático sobre la marcha, haciendo diversas pruebas hasta lograr conseguir perfeccionarlo.

Excel proporciona tres herramientas para este tipo de análisis:

• Administrador de Escenarios
• Buscar Objetivo
• Tablas de Datos

1. ADMINISTRADOR DE ESCENARIOS

Un escenario es un conjunto de valores que excel guarda de manera interna y que se pueden reutilizar en la hoja de cálculo. Excel se encargará de colocar cada uno de estos valores en las celdas que determine el usuario. Los escenarios permiten ver cómo responde nuestro modelo matemático cuando le agregamos determinados valores de entrada.

Los escenarios ayudan a explorar un conjunto de resultados, facilitan la recopilación de datos acerca de posibles resultados de varios orígenes y combinarlos. Esta técnica “What If
“ puede ayudarle a decidir entre múltiples cursos de acción o conocer cuáles son las implicaciones de una acción entre varias posibilidades.

Se puede crear un informe resumen de escenario que combine todos los escenarios en una hoja de cálculo. Por ejemplo, puede crear varios escenarios de presupuesto que comparen diferentes niveles de ingresos y gastos y luego crear un informe que le permita comparar los escenarios en paralelo.

Un escenario puede tener un máximo de 32 variables, pero se puede crear tantos escenarios como se desee.

1. CASO PROPUESTO

Tenemos el presupuesto de un trabajador promedio, donde se proyectan sus ingresos y egresos del primer semestre del año.

[pic 4]

Esta hoja de presupuesto permite planificar el ahorro de 3000 soles en seis meses, considerando la estructura de ingresos y gastos estimados. Ahora bien, asume que este plan es tuyo, y te invito responder las siguientes preguntas:

• ¿Lograrías ahorrar 3000 soles, si el saldo inicial de enero, fuese igual a cero?
• ¿Si el seguro de salud no cubre al 100% determinada dolencia o enfermedad, y fuese necesario desembolsar 600 soles? ¿Es posible llegar a la meta de ahorro?
• Si asumimos lo anterior, ¿Es posible ahorrar 3000 soles?, de no ser así, ¿Qué gastos deberías suprimir del presupuesto, para alcanzar el objetivo?

Si analizas este problema con mayor detenimiento te darás cuenta que no estás considerado los descuentos en tu sueldo, o la posibilidad que no obtengas los ingresos esperados mes a mes o que el costo del servicio de agua potable aumente, entre muchas otras posibilidades.

¿Cómo harías para visualizar en tu hoja de presupuesto, cada una de las posibles situaciones de ingresos y egresos?, la respuesta es: mediante el Administrador de Escenarios.

1. CREAR ESCENARIO

Acceder al menú [Datos], [Análisis de Hipótesis] y ejecutar el comando [Administrador de Escenarios].

En la ventana Modificar Escenario, debes proporcionar un nombre para el escenario, las celdas modificables del modelo de cálculo y una breve descripción del escenario (opcional).

[pic 5]

A continuación, debes ingresar los valores que adoptarán las celdas cambiantes en el presente escenario y luego pulsar el botón [Aceptar].

[pic 6]

[pic 7]

De esta manera el escenario podrá ser reutilizado las veces que quieras. Si deseas asignar otros valores para las mismas celdas cambiantes, deberás crear un nuevo escenario.

[pic 8]

1. BUSCAR OBJETIVO

Puedes utilizar la búsqueda de objetivo para averiguar el valor de entrada que permite a un modelo matemático llegar a un resultado concreto. El valor de entrada es la variable que permitirá a la fórmula propuesta llegar al resultado esperado. Esta variable debe estar asociada a una celda, de tal manera que Excel interactúe con esta celda hasta conseguir el objetivo.

Esta herramienta te ayudará a hacer muchas pruebas de valores en una fórmula hasta encontrar el valor exacto que cumpla con las condiciones establecidas. Es decir, si sabemos el resultado de una fórmula, pero no estamos seguros del valor de uno de sus argumentos, entonces buscar objetivo nos ayudará a probar diversos escenarios hasta encontrar el valor exacto que necesitamos.

1. CASO PROPUESTO

Sigamos con el caso propuesto anterior, si asumimos el escenario 1 donde se evita solicitar financiamiento para adquirir ciertos bienes, al término de los 6 meses obtendríamos un saldo de 3,830 soles; eso quiere decir que lograríamos la meta de ahorro de 3000 soles con un excedente de 830 soles.

...