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 
concepts we'll be using our latest startup idea   a lemonade stand and you can download 
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

test attribution text

Add Comment