# Solve Anything FAST with Excel’s What-if Analysis

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