Excel Financial Modeling | Sensitivity & Scenario Analysis #Finance

what’s up everyone it’s kenji here and in this CashNews.co 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 bold; color: #1a73e8; text-decoration: none;">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

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance role or those in it trying to level up their excel game alternatively you can check out this CashNews.co over here on financial modeling or this other CashNews.co 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