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
