# Excel Financial Modeling | Sensitivity & Scenario Analysis

what's up everyone it's kenji here and in this
video we're going to create sensitivity tables   to see how different price changes affect our
business scenario analysis to see how different   forecasts affect our bottom line and lastly goal
seek and solver to see how much we need to sell   to reach our financial goals and to apply these
the file i'll be working with for free   as i'll leave it in the description below so let's
get into it starting off with goal seek and this   formula is useful when you know the result that
you want but you don't really know what inputs to   put in it to get that result for example maybe we
say that for the lemonade stand we want to make a   hundred thousand in net income but we don't really
know how much we need to sell to reach that number   let's take a look at the excel file over here
and we've got the different assumptions on one   side and the income statement on the other and
so the assumptions here we've got the units sold   so the quantity of lemonade sold the price
for that lemonade the cost for us to make it   the gna has to do with general and administrative
so these are usually things like the the office   rent the salaries and other things like that
that are fixed in nature and then we've got   the tax rate and so what we want to reach is the
net income figure over here of a hundred thousand   now to do that firstly let's fill in the different
income statement lines so we'll go equals for the   revenue it's just the price times the quantity
so the unit sold times the price per unit   for the cogs this is going to be the cost of
goods sold so that's equals to the units sold   times we'll make it a negative here because it's
a cost the cost per unit press enter for a gross   profit you can just press the alt equals that's
a shortcut for that press enter the reason um   this this over here is a sum is because this one's
already a negative so we don't have to do another   minus to it for gna equals negative the gna here
the earnings before tax is just the equal sum   press the tab key and we'll select these two here
and then for the taxes this gets a bit more tricky   that's because if you have a negative earnings
or if you didn't earn any money there's obviously   nothing for there to be taxed and so let's put an
if statement that's going to give us a condition   so we'll go equals if press the tab key so the
logical test is that this figure is going to be   less than zero and if that's the case if the value
if true then we actually don't want it anything so   we're just going to put a zero there in quotations
press the comma key again and the value if false   so if there is an actual profit here on earnings
in this case what we want to do is tax them right   so we'll go get this number here and multiply
by that 21 we actually have to make a negative   again so we'll go do that press enter and so in
this case because we do have earnings before tax   we will have a tax rate to that then lastly we'll
sum these equal sum press the tab key and select   this tool so we've got 988 in net income and we
want to get that to boost all the way to 100 000   one way that we can do that is by changing
the quantity sold so you can either do that   manually say we put 10 000 see what kind of an
effect that has i'll try again with say 50 000   or in the other way the faster more efficient
way to go about it is using goal seek   so for that firstly let's put a set of constraints
here and so what we want to put is essentially   first merge this so it looks just like this
one here so alt hmc and then ctrl b and then   alt h and that's going to give us the blue border
here let's go ahead and select that one i have it   on the recent and then we'll do alt h fc and we'll
put a white background sorry white color here   and the first control constraint is the net income
which we actually want it to be a hundred thousand   press enter and now to get to the actual solver
we'll just go to data what-if analysis and we'll   select goal seek so we want to set this cell here
which is the net income cell 2 the value is going   to be 100 000. By changing cell this is the the
input that we actually want to change which in   our case is the unit sold so we'll press ok and
now you can see that we've reached 100 000 in net   income press the ok key here and that's because
we've actually increased the unit sold to over   60 000 here but that's 60 000 units all that
seems like quite a lot of work for us and we   want to try to limit that to say around 25 000 as
anything more is probably just not worth it for us   now the other variable that we could tweak
here has to do with the price maybe we can   increase the price and so we'll also reach the
net income figure faster that way now to do so   unfortunately if we go back to gold seek over
here you'll notice that it only has one specific   variable that you can change so it says to set
one cell it doesn't let you set multiple cells   and that's when something like solver is going
to come handy now as you can see i have solver   up here but by default it's usually not on excel
so to find it you can go to file you can go under   options down here and then you're gonna go to
add-ins under add-ins you're gonna go to go here   and the one that you want to tick is the solver
add-in over here press ok when you have it and   then it should pop up up here so firstly let's
put the different constraints that we mentioned   firstly we want to put that the unit sold is
going to have a highest of 25 000 because we   said anything above that is really not worth it
for us and let's also put a price here so price   constraint basically puts something like 9.99 as
we don't really think that anyone's going to be   willing to buy a lemonade lemonade for more than
a 10 dollars say so once we have these constraints   we can go up here over to the solver tab and
the set objective like we mentioned earlier   is just a net income figure and we want to
set this to a value of a hundred thousand by changing cells these are the different
variables that we want to change in our case it's   the unit sold as well as the price and then here
subject to the constraints these are the different   constraints that we want to put so let's go ahead
and add the first constraint is that we want to   have the units sold over here to be less than or
equals to the 25 000 press the add and a second   constraint is that we want the price per unit here
to be less than or equals to 9.99 press press the   add key and then you can close that so now we've
got the different constraints over here and you   can just press the solve key press the ok key here
and now you can see that we've got 25 000 units   we've also got the 100 000 in net income and
we've managed to do this by tweaking the price   over here to 8.21 per lemonade so that's
the general idea with goal seek and solver   next up we've got the sensitivity analysis or
table and one of the limiting factors with goal   seek as well as with solver is that we really
only had one final output so we had one price   per unit as well as one quantity sold but now
it'd be nice to have a whole range so what if   the price increased by one what if it decreased
by one then what would happen to our net income   so all these things we can do using the
sensitivity analysis for that go to control   page down and you can find it over here on
the table so this is what we want to fill in   so over here on the left hand side we have the
exact same assumptions that we did previously   let's go ahead and get started with this so price
per unit is going to be this price over here   pressing ctrl c then you can paste it all to hvv
that's going just going to just paste the values   so it's not going to change the formatting and
same thing over here we're going to select it   for the unit sold ctrl c and we'll paste it
over here alt hv all right from there we want   to have different ranges so for the quantity
say by 5000 so equals this one minus 5000   then copy and paste that across same thing
over here so 25 000 plus 5000 and paste it   across as well and for the price per unit same
kind of concept let's go up and down one dollar   so we'll go equals this one minus one and just
paste it across same thing on this side plus one and there you go now once we have this we want to
dynamically link it so we're gonna go up to this   cell over here and go to equals and we're gonna
select the net income from here press enter the   reason we want to select it is so that the whole
table is now dynamic from there we'll go select   everything so ctrl shift down arrow ctrl shift
right arrow then we'll go to the data tab under   what-if analysis again but this time we'll go
for the data table it's going to ask us for a row   input cell that's going to be the 25000 over here
because that's a row and the column input cell is   going to be the 8.21 over here it's important
that you select it from here and not from the   table itself or else it's not going to work press
ok and then that's going to auto populate for us   and now we can see that hey if we sold at six
dollars and we sold 15 000 here's how much we'd   be making and all the way across the whole range
right so it's good to know this and from there we   can format it a bit better by doing the shift up
arrow shift right arrow here and usually i like to   make it something of a light blue see so we'll
go to alt h h and from there we'll select say   this light blue here and just for the middle
value which is the current one that we're   at from here and here so we'll go ahead and alt
h and we'll make that a darker blue say over   here press enter and that's a general idea with
the sensitivity analysis it's also quite common   to do sensitivity analysis like these ones over
here for finance especially to do with the share   price movement and how that might be affected by
different growth rates different discount rates   and other things like that as you can see over
here lastly we've got the scenario analysis and   this is most useful for forecasting in our case
for example we're a lemonade stand we're quite new   so we don't really know what our revenues are
going to look like in the future same thing with   our costs will they go up if so by how much all
of these are questions that we can solve using   scenario analysis so we'll have three different
scenarios a best case a base case and a worst case   so let's go ahead and look at the excel sheet for
that over here down on the bottom on the gray area   we've got the best case which is obviously the
highest revenues as well as the the lowest cost   as well and first let's go ahead and format all of
this so the idea is that these are all forecasted   years or they're in the future let's go ahead and
change that so it reflects it so select them by   pressing the shift right arrow press the control
one key and we're gonna go down to the custom   in here we want to change this so we're gonna
put something like four wise which are going to   stand for the obviously the years year keys and
then next up we're going to put this inverted   sign thing and then press the e press ok
and that's going to show us the estimate   for these following years we don't have a number
yet so we'll go equals e date press the top key   this is a start date and we want to essentially
add one whole year to that so it's 12 months comma   12 press enter and same thing over here once we
have this we actually want to space things out   a bit so ctrl control shift and then ctrl key say
do it three times let's also delete obviously not   the lead by change the the font color here to
a white so no fill alt h and from there you'll   press the n key which is going to show you no
fill and the idea here is that we want to have   a drop down that's going to tell us hey choose a
different scenario and from there you can choose   the first scenario which is the best case all the
way to the worst case so we'll put choose scenario and then we can put something like like a sign
like this and here we're gonna have a one for now   let's go ahead and format this so we're
gonna put borders alt-h-b-a which is   gonna stand for all outside borders and this
one we want to say right align so alt-h-a-r   and now we can get started with this so for the
revenues we'll go equals the formula we'll use is   the choose formula press the tab key and the index
number is going to be the different scenario here   press the f4 key that's going to lock it for us
press the comma key and the values are going to   be in this case the revenue scenarios so this is
the first one comma the second one over here and   then the third one is this one over here and we
can press enter and we can just copy that across   on the call side it's the same thing equals
choose press the tab key index number is this   one press the f4 key comma the values are going
to be this time the course values first one second one and the very last one press
enter and we'll just drag those across   for net for the net income it's just a sum formula
so we'll go alt equals press enter and just drag   that across and once we have this if we change
the scenario say to scenario three press enter   this should be changing dynamically and if we look
say to confirm 2024 should be a 45 000 negative   and that's exactly the case under the
worst case scenario let's also delete   one row here we don't really need it shift space
control minus one thing you'll notice though if   we change this scenario into something that's
not part of the one two three like say a five   all of a sudden everything is broken so let's go
ahead and protect this so it can't happen anymore   pressing ctrl z to go back there what we're
gonna do is go on under the data tab and we'll   go to what's known as data validation which you
can find as this thing over here click on it   what we want to put is a list this is going to be
a drop-down list from which people can select the   one we'll put a comma two and three press okay
and now you see that there's a drop down list   in this case say if somebody comes in and presses
a five all of a sudden they're not allowed anymore   which is how it should be right and just so you
get an idea of how this choose formula works you   can actually go ahead under the formulas tab and
you can go to the trace trace precedence here   that's essentially going to show you where things
are linking from sometimes it's useful to find you   can put the remove arrows to take that out also
sometimes you might find that people like to group   these so for the revenue scenarios you can press
the shift space and then shift down arrow and   you'll do the shift alt and right arrow here and
that's going to group it so it's essentially going   to hide it for you um if you say your excel sheet
is too big for more on excel you can check out   this link over here where we teach people about
excel specifically for those looking to break into   a business or finance role or those in it trying
to level up their excel game alternatively you   can check out this video over here on financial
modeling or this other video over here on awesome   visuals in excel hit that like hit that subscribe
if you liked it and i'll catch you in the next one