Solve Anything FAST with Excel’s What-if Analysis

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

test attribution text

Add Comment