Escenarios Multi Hipótesis en Excel

7/25/2019 Escenarios Multi Hipótesis en Excel

http://slidepdf.com/reader/full/escenarios-multi-hipotesis-en-excel 1/15

ESCENARIOS MULTI-HIPÓTESIS

Usar el fichero versión 2!" escenarios#$l%i&'ls'

Usar el fichero versión 2(" escenarios#$l%i&'ls

Los escenarios multi-hipótesis: permiten efectuar análisis de sensibilidad de múltiples variables de entrada, frente amúltiples variables de salida. Vamos a realizar un caso con 5 variables de entrada y variables de salida.!eneraremos tres escenarios posibles: pesimista, medio y optimista. "ambi#n veremos análisis de sensibilidadutilizando tablas tipo $ %una variable de entrada& y tablas tipo $$ %dos variables de entrada&.

HO)A *

'n la (o)a* efectuamos el +nálisis de ensibilidad respecto a una variable de entrada. reamos una modelo uenos permite ilustrar este caso. e trata de la cuenta de resultados de una empresa ue efectúa previsiones para los pró/imos a0os: 1*1, 1**, 1*, 1*2 y 1*3.

1

http://trucosexcel.blogspot.com/2009/11/escenarios-multi-hipotesis.html

http://excelavanzado.com/xls/escenariosmulti.xlsx

http://excelavanzado.com/xls/escenariosmulti.xls


http://excelavanzado.com/xls/escenariosmulti.xlsx


http://trucosexcel.blogspot.com/2009/11/escenarios-multi-hipotesis.html



La 4acturación4 %fila 6& se prev# para el a0o 1*1 ue será de *51.111 7, incrementándose para los pró/imos a0osen un porcenta)e ue viene e/presado por la celda amarilla %86&. 's este caso, suponemos ue la facturacióne/perimentará un incremento acumulado del 59 cada a0o. La celda amarilla es sobre la ue efectuaremos elanálisis de sensibilidad. 's nuestra variable de entrada. La celda '6 nos proporciona el incremento, y al copiarlahacia la derecha este incremento se convierte en acumulado afectando a los sucesivos a0os. u e/presión es lasiuiente: =+D7*(1+$B$7)

Los inresos 4+t;picos4 %fila <& parten de un importe inicial de 21.111 7 y se reducen cada a0o un 29. omprobarloen la celda '< ue se copiará hacia la derecha.

=+D8*(1-0,03)

'n la fila = sumamos los $>!?'@ con la función A8"@"+L'.

=SUBTOTALES(núm_función!"#%1, !"#%', )

'sta función es muy versátil y seún el valor ue demos a númBfunción nos permitirá calcular diferentesoperaciones. Veamos alunos valores de n+#,f$nción:

• sumar C D

• contar C

• calcular el má/imo C 3

• calcular el m;nimo C 5

• calcular el promedio C *

La venta)a de sumar con la función A8"@"+L' en luar de hacerlo con la función AE+, es ue si en el ranode valores de la función A8"@"+L' se incluye una fila ue tambi#n emplea esta función, no la incluye dentrodel cálculo, evitando duplicidades. 'n nuestro caso en la fila *5 calculamos el 8eneficio como suma de los

$nresos y de los !astos %con sino neativo&. +l emplear en el cálculo del 8eneficio la función A8"@"+L' yhacerlo tambi#n en el cálculo de los $nresos y de los !astos, no se incluyen estos, evitando duplicar los importes.

@bs#rvese la necesidad de considerar los inresos neativos ya ue la función A8"@"+L' sabe sumar, pero norestar.

Fara los !astos hemos creado únicamente tres posibles conceptos: personal, amortización y aluileres. ada uno deellos contiene unos importes previstos para el e)ercicio 1*1 y unos incrementos acumulados para los pró/imosa0os.

'n la fila * calculamos el 8eneficio. For e)emplo en G*5 la e/presión es: =SUBTOTALES(DD1)

@bserve ue, tal y como hemos comentado, el cálculo es correcto pese a utilizar el rano G=:G*3 ue incluye lafila de $nresos y la de !astos. 'sto es debido a ue no se computan en la suma debido a ue las propias filas de$nresos y !astos se calcularon con la función A8"@"+L'.

reamos un nombre de rano denominado 4beneficio4 ue es el rano G*5:(*5, cuyo ámbito lo hemos restrinidoúnicamente a la (o)a*. Fara hacer esto seuimos estos pasos:

*& eleccionamos el rano G*5:(*5

2& 'n el cuadro de nombres escribimos: H(o)a*IbeneficioH %sin las comillas&. +l anteponer al nombre de rano el

nombre de la ho)a y una admiración hacia aba)o %I&, conseuimos definir el nombre de rano e/clusivamente enel ámbito de actuación de la (o)a*.

2



(& Fulsar '>"'?. 'sto es importante ya ue si despu#s de escrito el nombre de rano pulsamos cualuier celdacon el ratón, sin haber pulsado 'nter, el nombre de rano no uedará rabado.

An m#todo alternativo para nombrar ranos en '/cel 112 se hace con $nsertar, >ombre, Gefinir. 'n '/cel 1116el m#todo alternativo para asinar un nombre de rano se consiue con: órmulas, +sinar nombre a un rano. eobtiene la siuiente ventana:

@bserve ue en 4Jmbito:4 hemos indicado (o)a*. 'sto hace ue el nombre de rano se restrin)a e/clusivamente a la(o)a*. + esta ventana tambi#n podr;amos haber lleado en '/cel 116 con órmulas, +dministrador de nombres, >uevo.

.ila *!" ISS /I#0$es%o 1e Socie1a1es& onsideramos ue es un 259 sobre el 8eneficio. @bserve la fórmula de lacelda G*6, y las de esa fila.

=3.*/nfici

Fara crear la fila *6 seuimos estos pasos:

*& eleccionamos las celdas G*6:(*62& 'scribimos la fórmula: K259beneficio

(& >o pulsamos 'nter. Fulsamos trl M 'nter. Ge esta forma conseuimos ue la fórmula se apliue al todas lasceldas del rano seleccionado.

3



+hora, nombramos el rano G*6:(*6 con el nombre de rano $ restrinido al ámbito e/clusivo de la (o)a*..ila *3& 45I /4eneficio 5es0$6s 1e I#0$es%os& Fara crear esta fila seuimos estos pasos:

*& eleccionamos las celdas G*<:(*<

2& 'scribimos la fórmula: Kbeneficio-$

(& >o pulsamos 'nter. Fulsamos trl M 'nter. Ge esta forma conseuimos ue la fórmula se apliue al todas las

celdas del rano seleccionado.

.ila *7& 5ivi1en1os& onsideramos ue todo el beneficio despu#s de impuestos se reparte en dividendos.

.ila 22& .l$8os 1e Ca8a& uponamos ue este proyecto de inversión para el accionista un desembolso de *11.111 7 %celda & ue se indican con sino neativo por ser un desembolso, y unas recuperaciones positivas ue son losdividendos ue se prev# obtener en los pró/imos a0os. obre estos flu)os de ca)a posteriormente calcularemos elV+> y la "$? ue serán las variables de salida del modelo, y sobre las ue efectuaremos el análisis de sensibilidad.

Frimero vamos a crear el análisis de sensibilidad para analizar la "$?, y de momento no calcularemos el V+>.euiremos estos pasos:

PASO 1

elda G3. 'n esta celda calculamos la "$? de la operación. La fórmula empleada es: =T2(''4'')

'l resultado obtenido es 6,259 anual, ue representa la rentabilidad de este proyecto ba)o el supuesto de ue lacelda amarilla %incremento de la facturación& sea del 59 anual acumulado.

PASO 2

'n el rano 5:25 creamos una serie entre el 19 y el *19 ue representan los posibles valores ueconsideramos puede llear a tomar la celda amarilla, ue es el incremento de la facturación esperada para los

pró/imos a0os. La celda G3 y el rano 3:25 es imprescindible ue se encuentren en esas posiciones relativasuno respecto al otro, ya ue vamos a crear una tabla para efectuar el análisis de sensibilidad.

4



PASO 3

eleccionamos la tabla ue ocupará el rano 3:G25.@bservar ue ueda seleccionada tambi#n la celda verde%G3&.

PASO 4

'n '/cel 112 vamos al menú: Gatos, "abla.

'n '/cel 116 vamos a Gatos, +nálisis N si, "abla dedatos.

@btenemos una ventana ue nos pide ue introduzcamos:

• elda de entrada %fila&

• elda de entrada %columna&

La celda de entrada %fila& la de)amos vac;a, y en la celda

de entrada %columna& introducimos la celda amarilla %86&,ue es la celda amarilla sobre la ue deseamos hacer elanálisis de sensibilidad. +l encontrarnos en el caso de"ablas "ipo $ únicamente analizamos una variable deentrada. Onicamente tenemos una celda amarilla. 'n lastablas tipo $$ ue veremos en la (o)a veremos ue serellenan ambas %la celda de entrada fila y la celda de entrada columna&. PASO 5

Fulsamos 'nter y obtenemos la siuiente tabla, con los valores de la "$? para diferentes tasas de incremento de la

facturación. +plicamos a los valores obtenidos de la "$? un formato de porcenta)e de dos decimales.

PASO 6

+ nivel est#tico ueda horrible ver en la celda verde %G3& un número en luar de un rótulo ue nos indiue ue esacolumna de la tabla nos proporciona la "$?. Vamos a utilizar un truco para ue sin borrar la fórmula de la "$? de lacelda G3, ya ue es imprescindible ue este ah;, podamos ver el rótulo de la "$?.

5



>os situamos en G3 y pulsamos con el botón derecho del ratón eliiendo 4ormato de celdas4, pesta0a >úmero,ateor;a Fersonalizada, y en "ipo escribimos la palabra "$? entrecomillada: 5T25

on este truco veremos ue en la celda verde G3 pone "$?, y ue en realidad siue e/istiendo la fórmula.

PASO 7

$ntente borrar una única celda obtenida, por e)emplo la celda G2*, y obtendrá un mensa)e de error ue le avisa ueno se puede eliminar una única celda. i uiere borrarlo debe borrar el rano G5:G25 completo. La fórmula G2*tiene la siuiente e/presión: 6=TABLA(B7)

'/presión ue no podemos mane)ar.

i cambiamos un valor en el rano 5:25 se recalcula automáticamente la "$? correspondiente. For e)emplo, en25 cambie el *19 por un *9 y observe como cambia la "$? de la celda G25.

Na hemos efectuado el análisis de sensibilidad de una variable de entrada frente a una variable de salida.

• Variable de entrada: la celda amarilla 86. $ncremento de la facturación.

• Variable de salida: la "$?. e obtiene en el rano G5:G25.

+hora podemos plantearnos incorporar una nueva variable de salida, el V+>. Fero hemos de ser conscientes de ueseuimos estando en "ablas "ipo $, ya ue continuamos con una única variable de entrada. "enemos una únicacelda amarilla, aunue ahora vamos a conseuir dos variables de salida %la "$? y el V+>&.

6



PASO 8

Fara a0adir una nueva variable de salida debemos previamente borrar el rano G5:G25. Lueo a0adimos en lacelda '3 una fórmula ue calcula el V+> de la operación al =9.

=+9A(.D''4'')+''

@btenemos un valor de 2.3**,1 7.

in borrar la fórmula del V+> de la celda '3 aplicamos el mismo truco ue utilizamos antes con la "$?, pero enesta ocasión para conseuir ue en esa celda se vea la palabra V+>.

PASO 9

eleccionamos el rano 3:'25

PASO 10

Lanzamos la tabla de datos y en elda de entrada %fila& no ponemos nada, y en elda de entrada %columna&seleccionamos la celda amarilla %86&. ?ecordemos ue aunue tenamos ahora dos variables de salid %"$? y V+>&seuimos estando en "ablas "ipo $ %una única celda amarilla&.

7



PASO 11

Fulsamos 'nter y obtenemos la tabla ue pretend;amos con la "$? y el V+>.

HO)A 2

Las tablas tipo $ nos permite efectuar el análisis de sensibilidad respecto a una variable de entrada, siendo elresultado es una columna de datos %una dimensión&. Las tablas tipo $$ nos permiten analizar dos variables deentrada, siendo el resultado es una tabla de doble entrada.

'n la (o)a realizamos un modelo de cuenta de resultados similar al de la (o)a*, pero en este caso tenemos dosceldas amarillas. 'sto es, el modelo dispone de dos variables de entrada respecto a las ue vamos a efectuar elanálisis de sensibilidad.

Las variables de entrada %celdas amarillas& son:

• elad 86. $ncremento de la facturación

• elda 8**. $ncremento de los astos de personal.

8



'l procedimiento es análoo al descrito en la (o)a*, con la salvedad de ue ahora la celda verde ue contiene lafórmula de la "$? debe encontrarse en el v#rtice de la tabla %celda 3&.

=T2(''4'')

u importe es del 6,259.

9



reamos una tabla de doble entrada con las dos variables sobre las ue deseamos efectuar el análisis desensibilidad:

*& 'n la columna %rano 5:25&, creamos una serie entre el 19 y el *19 para la variable ue representa elincremento porcentual de la facturación.

2& 'n la fila 3 %rano G3:(3& creamos una serie con varios porcenta)es entre el -29 y el *19 ue representanla variación de los astos de personal.

+hora se debe seleccionar toda la tabla %rano 3:(25& y lanzar

las "ablas de Gatos. 'n esta ocasión si rellenamos tanto la 4eldade entrada %fila&4 como la 4elda de entrada %columna&4.

• La elda de entrada %fila& es la celda 8**

• La elda de entrada %columna& es la celda 86

inalmente en la celda verde empleamos el truco visto para poner la palabra "$? sin borrar la fórmula, y hacemosun ráfico.

10



?esumiendo el +nálisis de ensibilidad:*& "ablas "ipo $. Ana variable de entrada. e obtiene una columna de datos. ? *.2& "ablas "ipo $$. Gos variables de entrada. e obtiene una tabla de doble entrada. ? .(& "ablas "ipo $$$. "res variables de entrada. e obtendr;a un cubo. ? 2.

'n '/cel no e/isten las "ablas "ipo $$$. 'n su d;a se pod;an hacer con una ho)a de cálculo anterior a '/cel ue sellamaba Lotus *2. 'sta ho)a de cálculo reinó durante muchos a0os como el l;der indiscutible hasta ue lleó '/cely la desplazó definitivamente. 'n Lotus las "ablas "ipo $$$ se conseu;an utilizando varias ho)as de un libro: (o)a*,(o)a, (o)a2,..., as; se obten;a un cubo en tres dimensiones. Fod;amos eneral por e)emplo un libro de 8onos,donde las tres variables ser;an: cupón, precio y plazo frente a rentabilidad.

Fese a ue en '/cel las "ablas "ipo $$$ no e/isten, rápidamente llear;amos al l;mite de variables de entrada autilizar, ya ue más allá de un cubo %? 2&, no e/iste fiura eom#trica con la ue poder representar cuatro variablesde entrada %? 3&, ni mucho menos cinco variables %? 5&, etc.

La metodolo;a de las "ablas tiene un l;mite eom#trico y no podr;amos pasar más allá de tres variables de entrada.'/cel ha optado por utilizar una herramienta denominada ''>+?$@, ue soporta múltiples variables deentrada y de salida, por lo ue se puede considerarse multi-hipótesis.

HO)A (

'n la (o)a2 traba)aremos con 'scenarios, una herramienta de '/cel ue nos permite utilizar múltiples variables de

entrada y de salida.

'n esta ocasión disponemos en la columna 8 de cinco celdas amarillas ue serán las variables de entrada.?epresentan los incrementos porcentuales acumulados de la facturación, los inresos at;picos, los astos de personal, amortizaciones y aluileres. Fretendemos estudiar dos variables de salida: la "$? y el V+> calculado al=9.

Gefinimos tres posibles escenarios:

*& 'scenario optimista2& 'scenario medio

(& 'scenario pesimista

Fara cada escenario definimos el valor ue tendr;an las variables de entrada. 'sto se puede ver en el cuadro de lasfilas 6 a 21.

11



For tanto, disponemos de 5 celdas amarillas %variables de entrada& y dos celdas verdes %variables de salida&. 'lmodelo esta creado de tal forma ue las variables de entrada y las de salida están vinculadas por fórmulas. 'stouiere decir ue si cambiamos manualmente el valor de aluna o varias de las celdas amarillas el valor calculado enlas celdas verdes cambia automáticamente.

Fara lanzar la herramienta de 'scenarios en '/cel 112 eleimos: (erramientas, 'scenarios.

Fara lanzar la herramienta de 'scenarios en '/cel 116 eleimos: Gatos, +nálisis N si, +dministrador deescenarios.

12



'n la ventana ue sure del +dministrador de escenarios eleimos +rear. omo nombre de escenario escribimos4@ptimista4. Gonde dice 4eldas cambiantes4 se deben poner las amarillas, ue al estar separadas se indicanseleccionándolas con el ratón mientas se mantiene presionada la tecla ontrol.

euidamente se indican los valores ue toman las celdas amarillas para este escenario concreto.

Fulsamos sobre el botón 4+rear4 y procedemos de iual manera con el escenario Eedio y con el escenarioFesimista. +l finalizar la introducción de los valores de las variables para este último escenario pulsamos sobre el botón 4+ceptar4.

13



Fulsamos sobre el botón 4?esumen4 y en la siuiente ventana indicamoscomo 4eldas de resultado4 las dos celdas verdes. omo tipo de informeeleimos ?esumen.

'sto enera un informe en una ho)a nueva denominada 4?esumen deescenario4. Guplicamos esta ho)a para me)orar su aspecto. Faraduplicarla pulsamos la tecla ontrol mientras arrastramos la pesta0a de

la ho)a ue deseamos duplicar. 'sto enerará una ho)a denominada4?esumen de escenario %&4.

'n esta ho)a duplicada eliminamos la columna de valores actuales, ya ue no nos interesa tener a la vista losvalores ue actualmente tienen las celdas amarillas. @tra me)ora consiste en cambiar los nombres de las celdas por sus conceptos: acturación, +t;picos,.... N finalmente realizamos un ráfico ue ilustre los tres escenariosconsiderados.

14



'/cel nos ayuda a estudiar los escenarios, pero ha de ser el usuario el ue defina para cada escenario el valor ue

toman las diferentes variables de entrada. 'sto es lo verdaderamente complicado e interesante, y afortunadamenteaún el ser humano no ha sido desplazado en esa faceta.

Escenarios Multi Hipótesis en Excel

Documents

Transcript of Escenarios Multi Hipótesis en Excel