in this video we'll look at how to solve any optimization problem using goal seek and solver
and we'll start off with some basic examples and progressively work towards some harder ones
so let's get into it suppose we're analyzing apple and we're trying to assess how financially viable
the next iphone is going to be and for this we have this simplified set of assumptions where it
says the quantity sold so how many iphones we were expecting to sell the price per unit so how much
you're going to be selling for the variable cost which is going to be the cost that's associated
with each sale so for example the packaging or the raw materials and then we have the fixed
costs which are not going to vary depending on how much we sell so for example the cost of renting
the warehouse is going to stay the same regardless of how many iphones we sell with these assumptions
our manager would like to know how many iphones we need to sell in order to break even
so to reach a profit of zero so for this first we need to calculate all the different line items
and this is the variable that we're going to be able to tweak the quantity sold which is the one
that he wants to find out so firstly for a total revenue that's just going to be the quantity sold
multiplied by the price per unit similarly for the variable cost it's going to be negative as
it's equal so put a negative sign there it's just going to be the variable cost per unit
multiplied by the quantity sold and for the fixed cost it's just going to be negative
and just that flat fixed fee the profit is going to be the sum of all these as these are already
negative so just go to alt equals and hit enter and now we can start to play with the quantity
sold so for example if we put 7000 up here you can see that we're still at the loss how about if we
put 8 000 and you can see that's that's slightly too high now this is the manual way to go about
it which isn't so great so let's use goal seek instead for this go under the data tab go under
what-if analysis and here we want to go to goal seek so what we want to do is set the cell so we
want to set the profit cell to a value of exactly zero and the cell that we want to change so it's
a quantity that's going to be up over here so just go ahead and select that one and hit ok
and so now you can see that it's done this calculation for us we'll put ok there
you can see that we have a profit of exactly zero if we sell this many so 7600 so we report this to
the management team and now they also want to know how much we would need to sell to reach 1
million in profit so for that let's go back to the what-if analysis and we'll go to gold seek it's
pretty much the same concept so we want to set the profit in this case the value is one million
so one followed by six zeros and then the cell we want to change is still the quantity and hit ok
hit okay there again and there you go but taking a closer look at the quantity sold you can see over
here that it's actually got some decimals which intuitively doesn't make much sense you can't
really sell 0.7 of an iphone you actually have to sell it all or the customer is probably not going
to be very happy if it's missing the camera or something so instead what we're going to do here
at the very front we'll go equals round up press the top key and this is essentially going to round
it up to a whole number and then press a comma at the end and we're going to put zero digits because
we want the whole number close the brackets and hit enter this way you can see that the profit's
actually gone up by a bit so it's not exactly a million but it makes more sense in that you're
not going to be delivering a portion of an iphone to a customer right that's the idea with goal seek
now unfortunately it does have some limitations for example what if we wanted to increase the
price as well to reach that one million in profit faster in that case as you can see over here you
can only really tweak one variable so it would have to be the quantity or the price you can't
really work with both that's when solver comes handy so let's look into it now solver is not on
excel by default so for it you're just going to have to go to file then go under options
and from here we want to go to add-ins go under manage excel add-ins and hit go
and from there you want to click on the solver add in there and then hit ok once you do that if you
go under the data tab you should be able to find it over here where it says solver to use solver
let's go to control page down and our manager is still insisting on us reaching 1 million in profit
so for this we do have a few different constraints this time so firstly we have a maximum capacity
so basically the warehouse can only produce so many iphones and that maximum is at 9 000.
similarly management team told us that we can increase the ma the maximum price per unit so the
iphone price we can increase it to 1099 given that inflation's been very high recently
similarly though the variable cost has actually gone up as you can see it's gone up from what we
had previously that might be because of supply chain issues so given these new constraints we
want to try to find the profit over here so for this we're going to be able to use
solver if we use just goal seek we wouldn't be able to account for three different constraints
so go under data solver and the objective that we want to set over here is going to
be the profit and we want that to be exactly at 1 million so we'll put a value of 1 million
and the variables that we want to change are going to be all of these up here which are going to be a
quantity the price and the variable cost then we want to add a few different constraints which are
all the ones that the management team gave us so we'll go under add here and let's go one by one so
the first one is going to be that the quantity sold should be less than or equals to the 9000
we'll hit add there secondly we have that the price per unit should be less than or
equal to the 1099 hit add there and clearly we've got that the variable cost should
actually be greater than or equal to the minimum variable cost per unit constraint here hit add
and one final one that we could put here is that the quantity sold should actually be an integer
so let's go ahead and click on it and then we're just going to put integer and hit add
the reason we want it as an integer is because you can't really sell half an iphone right
but for the price and the quantity those can obviously be in decimals so we'll go cancel
there to get out of that and now we can just go to solve so it says that solver found a solution
and we can just put okay there and now you can see all of the solutions that we've got in to
reach one million so we've got a quantity sold of 9000 which is exactly at the maximum capacity
but the prices are over here is actually less than the maximum price that we had in mind
and then lastly we have the variable cost which we meet exactly at the minimum two that's a
simple example of solver so let's now look at a more complex scenario that you might encounter
but before we get into that this video is brought to you by crisp if you ever find yourself having
to take business calls in a noisy place crisps ai powered technology will cancel out any noise
for example i've got a vacuum cleaner here in the background and it's probably pretty noisy
and here's the difference when i turn her spawn so right now i'll go under microphone and just turn
it on from there and you can probably hear me a lot better right now even though there's a vacuum
cleaner in the background and so when i turn this back off now then all of a sudden you can hear the
background noise again and the same thing goes for the speaker side where depending on how much noise
they have in the background you can go ahead and change that too you can download the technology
for free using the link in the description below and have it synchronized with zoom slack facetime
or any other video conference software that you might use so if you're interested in checking out
crisp go to the link in the description below it's completely free alright back to excel
here under the advanced solver tab we've got the labor cost in us dollars where we have all the
different products and how much it's going to cost for us to make them when it comes to labor cost
by country as well so for example to produce an iphone in japan it's going to cost us 34 dollars
of labor cost similarly for germany that's going to be slightly less at 29 and so the idea is to
try to optimize that down over here under the units produced so we want to make it such that
the total labor cost that we're going to have down over here is going to be as low as possible
that said the management team is also telling us to consider a set of constraints so over here
you can see that we've got the minimum country production of 20 000 units produced in japan 15
000 in germany etc and the reason for this might be that they don't want to lay off the people in
japan even though say the labor might be slightly more expensive there than it is in germany and
similarly you don't really want to lay off the specialists in iphones if you stop producing them
and so you have a minimum threshold when it comes to item production as well so to make this dynamic
firstly we're gonna have to go under the total item production and we're gonna do a sum press the
tab key and we wanna sum all of the iphones over here hit enter and then we're just gonna drag that
down all the way to the bottom here similarly over here for the total country production
we'll go equal sum and we'll select all of these over here up until there and we can just drag that
along as well and so now when you add say one over here it should auto populate over under
the total country production as well as the total item production and now for the total labor cost
this is this should be the multiplication of the one in japan times the labor cost in japan and if
we have another one over here that's going to be the same thing so i should sum the multiplication
so for this it's probably best to do a sum product so we'll go sum product press the tab key from
there the first three is going to be all of the wages here press the comma key and the secondary
is going to be all of the units produced down over here then close those brackets and hit enter
now you can see that we've got one one in german one in japan for iphone sorry and another one
for an ipad and so we've got these two sums which equate to the total labor cost over here
now that we understand what we're looking for let's open to solver so go under data solver there
and this objective that we want to set is the labor cost that we want to actually have
a minimum value not necessarily a specific value but just a minimum so we can select that and the
variables that we can change are going to be all of these over here which are the units produced
then we have a set of constraints and the first constraint is going to be that
let's say this column over here so that the total country production
should be greater than or equals to all of the constraints here so this is the minimum so it
needs to make more than the minimum obviously and same thing over here so we need the total item
production to be greater than or equals to the minimums over here press the alt key and lastly
we also need them to be integers so we need these units produced so let's go ahead and select them
to be integers hit add and then hit cancel to get out of that and so now now that we have all of
these here we can just go to solve and it should be able to solve it for us it might take a bit
of time there we go solver found a solution hit okay so we've got a total labor cost of around
2.5 million and you can see that japan seems to produce a lot of different items while germany for
example is a specialist in iphone probably because they make it for cheapest there now to make sure
that everything is optimized let's make a few checks here so firstly we'll put a check sign
over here so we'll just put check like that and what we want to make sure is that equals the total
country production is going to be greater than or equals to the minimum country production hit enter
and if it's true that obviously is good so we'll do the same thing over here so we'll go
up here we'll go check and we'll go equals that the total item production over here should be
greater than or equal to the minimum production here and that seems to be the case as well so nice
that's looking good there similarly we also want to make sure that the matrix is working correctly
so we want to see that the sum of these over here is going to be equal to the sum of these over here
now to do so we can do a sum of swords so we'll go equals sum press the tab key and first we'll sum
all of these up over here press the f4 key twice such that we can then drag it across
for the totals as well close those brackets and then we'll go equals
the sum of these ones over here we'll select all of them and press the f4 three times there
then we'll close those brackets hit enter it says that it's true and now we can just copy it across
to this one over here and it says that it's true as well so it all seems to be looking good there
as you might imagine the solver tool is very useful for different businesses for example in
hospitality say in a restaurant or a hotel you're going to have the constraint which are going to be
the number of rooms or the number of tables and so you want to try to optimize that maybe you want to
prioritize people that are staying in a suite or those that are staying for multiple nights
similarly in more of a warehouse environment you want to try to optimize your inventory such that
it's there for the least number of days so that you can sell it and make money off it for more
on excel you can check out this video over here on forecasting specifically using sensitivity and
scenario analysis or this other link over here to check out some of our courses all right that's all
for this video hit that like hit that subscribe if you liked it and i'll catch you in the next one