馃攳
One Variable Data Table in Excel Tutorial - One dimensional What-if analysis - YouTube
Channel: WallStreetMojo
[9]
hello friends welcome to Wallstreetmojo Investment Banking tutorial
[14]
today's topic is one variable data table
and we will see why it is important from
[19]
the point of view of calculating
sensitivity analysis in excel
[23]
sensitivity analysis is a very important
concept and we frequently use that in
[28]
various valuation models including the
dividend discount model the formula here
[33]
in front of us is known as the Gordon
growth formula this is nothing but a
[37]
dividend discount model where we
calculate the evaluation of the stock
[41]
using constant growth so the formula
here looks like a bit complex but it's
[46]
very very simple
value of the stock is nothing but d 0
[51]
into 1 + g / ke minus g d0 is the value
of the dividend which is received at
[59]
this year G is the constant growth rate
in the dividend d1 is the value of the
[66]
dividend to be received next year and ke
is the discount rate so d1 is nothing
[73]
but the dividend which is received this
year multiplied by 1 + growth rate in
[78]
the dividend so if you are worried about
the mechanics of this this formula you
[83]
can go ahead and look at our tutorial on
dividend discount model in detail but
[88]
the context of the discussion now is
about one variable or data tables so
[95]
many a times what happens is when we
calculate valuations we would like to
[99]
change the discount rate or maybe the
growth rate and see what's the effect on
[104]
the valuation of the stock so in that
context we'll take this basic example
[110]
where we have this constant growth model
price the stock we need to find out we
[116]
have been provided with the country a
dividend and we are also given the
[120]
expected growth rate that's the growth
rate G that's 4% and the expected return
[126]
on the cost of equity that's 10% so I'll
do that right away in an excel so we
[131]
have been provided with this basic
example where
[133]
currently dividend is dollar three
expected growth rate is 4% and the
[137]
expected return is 10% now let's find
the value of the stock on the basis of
[143]
Gordon growth formula so the Gordon
growth formula essentially said that
[148]
this is a value of the stock is the
dividend in the country ax x 1 + growth
[156]
rate divided by the costs of equity the
or the expected return minus the growth
[165]
rate alright so this comes out to be
fifty two point zero so this is the fair
[170]
value of this talk which represents the
currency our dividend and there's a
[175]
dividend growth of four percent each
year right so let's move forward so what
[180]
are we trying to achieve when we talk
about one variable data table so let's
[186]
create something I what if the expected
returns change from let's say 10% to 12%
[196]
12% 14% or maybe otherwise so how will
the value of the stock shift when the
[203]
expected returns change so that's where
one variable data table can be extremely
[208]
useful so let's say the expected return
is 6% or 7% or we can probably create
[216]
one whole table of this and see what are
the corresponding changes in terms of
[223]
valuation so as we can see here there's
one variable that changes the variable
[228]
that is changing here is this expected
return okay now when this variable is
[233]
changing there are two ways to find out
the expected output or the value of the
[237]
stock here the first one is the donkey
work obviously the donkey work is
[241]
something which we don't want to do but
for argument's sake I'll just show what
[246]
it means so here it means that we
rewrite the whole formula again and
[251]
again like this one plus growth rate
divided by the
[259]
expected return - the growth rate
alright so we get the answer as 156 but
[265]
what about the value when the expected
return changes to 7% we have to rewrite
[270]
the formula again so this means
rewriting the formula by only changing
[275]
one variable this is from 6% it now
becomes 7% and the value changes so we
[281]
can do that again and again again and
again and I don't want you to actually
[286]
do this instead I would like you to do a
smart work that's called as the usage of
[291]
something called data tables to perform
sensitivity analysis so here the basic
[298]
approaches and here this is very
important please please listen care the
[302]
first step is when you write this
variable in this standard format okay
[308]
above that please link your initial set
of variable what's the initial set of
[315]
variable you said that visit at 10% what
is your expected output the value of the
[322]
stock is 52 so think of this as inputs
which you have to give to the table the
[329]
data table okay at 10% the value of the
stock is 52 please don't write it
[336]
manually always link these are the
inputs all right now choose these from
[343]
top to bottom but these as weakened as
of now go to data go to what-if analysis
[351]
go to data tables all right now there
are two inputs which are required now
[360]
this since this is a one variable data
table you are expected to write only one
[365]
input now this is a column input because
as you can see it's in a single column
[371]
so the column input is what the expected
returns these returns which are changing
[377]
is the column input now this original
input was written here right so that's
[384]
where you need to link it from okay so
once you do that and you press ok you'll
[389]
find that all the answers
which were desired are already there so
[394]
if you see inside these it gives us
table bracket this is row and this is
[402]
column all right so what this data table
does is it goes and sees your initial
[408]
inputs and the expected output it traces
the expected output to the original
[414]
formula and recalculates and does it the
same way for the other set of input
[419]
variables so that's how this one
variable data table actually works you
[422]
can also do the same for the other set
of variables so say for example here
[426]
there was this variable called expected
return that change right here you can
[431]
also make the growth rate change and see
what's the expected output so let's try
[436]
this growth rate and as you now may
remember that we need to provide the
[444]
input here the input is now 4% alright
and what's the output the output is $52
[452]
all right so these this is the input
this is the output is the way you know
[456]
you need to create the data table now
let's change this growth rates 5% 6% 7%
[464]
8% right now let's assume that this is
the table that we have and we want to
[471]
see the results on these growth rates
okay so first thing again you need to
[479]
choose the whole table alright go to
data to do what if analysis go to data
[485]
tables so is this a row input or a
column input as discussed this is the
[490]
column input because it's in a single
column H all right so go there your
[497]
original input variable sits here so
that's where you need to connect it from
[502]
and then press ok so you'll be blessed
with the final answers and it will help
[508]
you with lot of time-saving activities
Most Recent Videos:
You can go back to the homepage right here: Homepage