Excel Financial Modeling | Sensitivity & Scenario Analysis - YouTube

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