If your manager ever asks you some of these questions luckily you can solve all of them
and more using the what-if analysis tool on Excel so let's take a look let's suppose we
work a Coca-Cola and our manager is asking us a set of questions so over here we've been given
this file and we've got the quantity sold and the price per unit so with those two we can calculate
the revenue which is simply equals to the quantity multiplied by the price now our manager would like
to know how much we need to sell in order to reach 10 000 in Revenue so we can actually go ahead and
change the quantity as that's a variable we want to change let's say three thousand
or to four thousand but actually an easy way to go about that is using the what-if
analysis tool so let's take a look at that we'll go over to data what if analysis and
over here we're gonna go to Gold seek as we're looking for a specific code here
so what we want to set is the cell which is the total revenue to a value of exactly 10 000. and
we want to change the quantity sold in our case as that's a variable that we're able to change so
we'll hit OK there and you can see hit OK again and it's going to calculate exactly how much
you need now one thing to keep in mind is that as you can see here the quantity is in decimals and
you can't really sell half a bottle of coke maybe you can but the customer is not going to be very
happy so what we're gonna do is use The Round Up formula to round it up to a full number so equals
round up hit the top key when you find that one and go towards the very end comma as the number of
digits we want zero as we want a whole number and we'll close the brackets and hit enter so now we
know we need to sell exactly this many so we tell our manager the quantity but now he also wants to
find out the profit now for that we're gonna need the cost right so let's take a look over here and
let's suppose we've calculated the variable costs and the fixed costs now with that we can go ahead
and find the profit but first we're gonna need to calculate a few things the revenue is simply
going to be the price times the quantity then just below that the variable cost it's going to
be the variable cost per unit multiplied by the quantity as it's supposed to scale and finally
the fixed costs are going to be fixed so it's just gonna be that 10 000. hit enter there and then the
profit is going to be equals to the revenue minus all of our costs so minus the variable minus the
fixed and we'll hit enter there so we tell our manager that we have a negative profit and he's
not very happy with that so he wants to find out how much we're gonna need to sell to get 10 000 in
profit so again this is similar to the previous one except we have a few more things with the
costs but we're still going to be able to go to data under what-if analysis go to goldseek again
and this time around we want to set the profit not the not the revenue anymore to a value of 10
000. the cell that we want to change is going to be the quantity again
and we'll hit OK there and hit OK again and just like so we know how much we're gonna
need to sell and again we're gonna need to round this one up so equals Roundup
and we're gonna put zero digits again like before close that and hit enter awesome so our manager
goes to see the marketing team with these numbers and they're like no way we can't sell Coke for two
2.99 as Pepsi is just gonna look cheaper they're gonna take all our market so now we have what's
known as a constraint and that constraint is gonna be that we can't raise the price over
2.5 dollars so our manager is still asking for the ten thousand dollars in profit even if we
do have these constraints now because of this we can actually use gold seek anymore instead
we're gonna use a more advanced tool called solver now solver by default is not going
to be in your Excel so what you want to do is go to file then towards the bottom go under options
and don't worry this is completely free then we're gonna go towards add-ins
and then towards the bottom here where it says manage find Excel add-ins and we'll hit go
then we're going to want to take on the solver add-in I already have it ticked as I already have
it activated I will hit OK from there what you should notice is that under the data tab towards
the very right you're gonna find the solver tool right there so let's go ahead and click and click
on that let me move it a bit to the side here and first the objective is going to be the profit
and we want to set that to an exact value so a value of 10 000. by changing variable cells so
this is what we can change in this case it's going to be we can change both the quantity and the
price because we need it to be 2.5 or less right then from here the constraints so we mentioned
earlier that the marketing team set a Max price of 2.5 so we're gonna have to add that the cell
reference is that we need this price so the price per unit to be less than or equal to the 2.5
hit add there and then we can close out of that now that we have it all set up we're just gonna
have to click on solve there and Excel is going to do the work for us we'll hit OK
and just like so you can see that it's now changed the price per unit to 2.5 and it's managed to find
our profit and again we're gonna have to round this one up so equals round up the number of
digits is going to be zero close those brackets and hit enter and speaking of financial statements
if you want to learn more about the topic I'd recommend checking out Financial Edge which
provides certified online finance courses and is also sponsoring this video their best selling
invest in Banker course goes over all the relevant skills to succeed in an analyst role these include
creating a three-statement financial model the key evaluation methods such as the discounted
cash flow and more and if you're interested in other areas of Finance they also offer a ton
of other courses including some on private Equity trading portfolio management and more so if you're
interested you can get 40 of all of their courses up until the 31st of December so go ahead and
click that link in the description below for that all right back to the video so we tell our manager
the new quantity sold but now he also wants to see a whole range so instead of having one specific
number he wants to see how it would change if we change the price Say by a few decimals now for
this unfortunately it's all very can't do it as it only gives us one output so instead we're gonna
use a sensitivity analysis also known as a data table as you can see over here we have the the
table with the quantity sold on one side alongside the price per unit on the other and the idea is
that as the price changes over here we're gonna have different profit amounts so for this first
let's set it up so for the price per unit it's gonna be that 2.5 there and you need to type it
you can't just link it there so 2.5 and same thing with the quantity sold we've got 17 392. hit enter
there and these ranges are basically gonna go down or go up depending on what we're interested in
so let's suppose we're interested in having 0.5 to that so equals to this one plus 0.5 hit enter
there same thing below so Ctrl C Ctrl V same thing over on this side so equals 2.5 minus 0.5 this
time and again we're just gonna copy and paste it across there and same kind of concept with
the quantity soul but this time let's suppose we go up and down by 500. so on this side equals this
figure minus 500 same thing over here so just copy and paste that and on this side it's Plus 500.
and again like so and one final thing we need to do before we run the data table is actually link
over here on this cell The Profit amount which is this one here and hit enter once we have all of
this set up we're gonna select all of it so Ctrl shift down control shift right go under the data
tab what if analysis and we're gonna go to data table here it's going to ask us for the row input
cell which in this case is the quantity sold and make sure we select it from this table over here
for the column it's going to be the price so this price per unit will hit OK there and just like so
you can see how everything is gonna Auto populate and if we look at the current stats so if we look
at 2.5 and this quantity we should have the exact same profit amount as that seems to be the case
as for this cell over here which is currently um kind of in the way you can go ahead and actually
change the color to white for example that's one way to get rid of it if you want to take
an image of it or something like that and for all these numbers for them to be easier
to read some people like to do a conditional formatting so Ctrl shift down Ctrl shift right
under conditional formatting here you can for example make it on a color scale kind
of like so where the negative is red all the way to Green for more on Excel check
out this video over here learn how to make a initial model or this other link over here to
learn more about visuals hit the like and that subscribe and I'll catch you in the next one