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