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