hi everyone kevin here today i want to show you how you can use both goal seek and also solver in microsoft excel these are two different tools that you can use with data analysis so what can you actually do with them well with goal seek you can solve for an individual variable what does that mean well let’s say at the kevin cookie company let’s say i want to figure out how many cookies do i have to bake on a daily basis to break even i can use goal seek to solve for that so what is solver it does everything that goal seek does but it does even more it solves for multiple variables and you can even add constraints so what does that mean well let’s say at the kevin cookie company we make lots of different types of cookies we have chocolate chip we have fortune we have white chocolate macadamia what combination of cookies should i bake to maximize my profit given that my oven will only bake so many cookies per day i can use solver to solve for that if any of this sounds confusing we’re going to walk through this step by step and i’ll show you exactly how you can take advantage of these different tools in fact if you want to learn i think the best way is to actually do it i’ve also included the sample sheet so you can follow along with this video alright well let’s jump on the pc and let’s start solving here i am now in microsoft excel and once again if you want to follow along i’ve included a link to this spreadsheet in the description of this video you can simply click on that that’ll open this up and you can follow along with every single thing that i’m doing i think that’ll really help you remember this content even better today we’re going to start with goal seek this is the easier of the two and this allows us to solve for one variable at a time now this might bring back some memories of algebra class back in middle school or in high school and hopefully those memories are all very positive to get started on the bottom let’s click into the worksheet called goal seek and this will drop you on this page right here and we’re going to focus most of our time over on the left hand side i know these cookies are very distracting i probably shouldn’t have put them in but just try your hardest to look over to the left this is subliminal messaging for the kevin cookie company hopefully by the end of this tutorial you’ll want to buy some cookies now i have a few different business questions that i need to answer today and i have a hunch that goal seek can help me with this right up here i want to know how many cookies do i need to sell to break even on a daily basis and along with that once i break even once i hit that milestone how many cookies do i need to sell to become a cookie millionaire and i might be getting a little greedy now but how many cookies do i need to sell to become a cookie multi-millionaire we’re going to use goal seek to answer all of these questions right down below i have a table and i want to quickly just orient you to it just so you understand what’s happening here right up on top i have the number of cookies sold so how many cookies do i need to sell to be able to meet each one of these up here so this is called my input i’m going to input in different values for each cookie sold i make five dollars of revenue or basically this is my price for this cookie and now i can’t make cookies for free of course there are ingredients they’re different costs involved and so my unit cost is two dollars and fifty cents to make each cookie and also it’s not just the ingredients but there are also a lot of overhead costs for example i have a commercial kitchen i have an oven these are all expensive and so my fixed costs are ten thousand dollars so to calculate my profit let’s click down here into the bottom cell and here you can see that to calculate the profit here i take the number of cookies sold times the unit price and that’s my revenue and then i subtract all my costs so here i’ll subtract my unit cost times my cookies sold and then i’ll subtract the fixed costs and that’ll give me my total profit now right now i don’t have anything entered into cookies sold so i basically sold zero cookies and you see what happens when i’m not selling i’m not earning so right down here with zero cookies sold i basically lose ten thousand dollars because i have all of these fixed costs now let’s say i sell one cookie so i just sell one cookie yay i got a sale so when i sell one cookie i make two dollars and fifty cents right my revenue is five i subtract 250 i’ve made 250 but then i have this massive overhead cost and so i’m still losing a lot of money now remember my first question is how many do i need to sell to break even now you might think well i could just enter in different numbers until i find the breakeven point here for example what if i sell 3 000 cookies up nope i’m still losing money or what if i sell 5000 cookies oh there i’ve made some money but what is the break-even point now i could just go around type in different numbers until i find it but that takes a really long time instead we should rely on microsoft excel to figure this out for us this is where we can use goal seek to figure out what is the number that brings us to the break-even point to access goal seek let’s go up to the top tabs on top and click on data this opens up the data ribbon and way over here on the right hand side there’s an option called what if analysis goal seek is part of what if analysis when we click on this within this menu the second option is goal seek let’s click on this this opens up goal seek and we have a few different options in here the first one says set sell now i want to set cell right down here i want to set the profit to zero because if i’m breaking even basically i’m not making anything but i’m also not losing anything so i want to set this cell and i want to set it to zero that’s my breakeven point and then here’s another option that says buy changing cell well the cell that we’re going to change is how many cookies are sold so i’ll click here and then let me go over and select cookies sold so right now i have all of these defined let’s now click on ok and here goal seek now has gone through and it’s calculated the optimum value so here if i sell 4 000 cookies my total profit will be zero that’s a lot of work i have to make 4 000 cookies and i don’t even make profit wow i don’t know if this is the best business once i’m all done i can click on ok alright well that’s kind of depressing that it’s going to take that much work and i don’t even make money but i’m in this business for the long haul so let’s figure out what it’s going to take to become a cookie millionaire once again just like we did before let’s go back up to data and then click into what-if analysis and then select goal seek once again this opens up goal seek and just like we did before let’s go through and set this up first off i want to set cell total profit so let’s click here and i’ll select this cell next i need to say what value i want to set it to now once again i want to become a cookie millionaire so i’ll type in one and then this is always tricky but you got to type in six zeros we’re working with big numbers now so here i have a million and i want to change the number of cookies sold so once again i’ll click up here so i have all of these defined let’s click on ok it’ll go through now and calculate how many cookies i need to sell to make a million dollars a profit and here it looks like i need to make 404 000 cookies that is a lot of cookies but if that’s what it’s gonna take i’m up for the task and lastly we can also calculate what it’ll take to become a cookie multi-millionaire once again let’s go up to what-if analysis click into goal seek and just like we did before here i’ll set this cell down here and a multi-millionaire is at least two million so as long as you’ve got two you qualify and right down here once again we want to change the cookie sold so i’ll select that and then let’s click on ok and here it turns out i have to sell 804 000 cookies to make 2 million dollars that’s a lot of work but that’s how goal seek works so you can solve for value and it works fairly well and it works with one variable at a time but what if you have multiple variables or what if you have constraints this is where solver comes in let’s jump over to the next sheet called solver and we’ll start with the simple example first i’m now on the next sheet and we’re going to run through the exact same example again but this time instead of using goal seek i’ll show you how you can use solver to get the same answer now right up here on data click on that tab and within the data ribbon all the way over on the right hand side you should see an option called solver and don’t worry if you don’t see it by default it’s because it’s probably not yet turned on solver is an add-in for microsoft excel and by default it’s turned off so we have to turn it on first how do you turn it on well let’s go over to the left hand corner up in the top click on the file menu and then go all the way down to options within excel options let’s click on the one called add-ins and this will show us all the different active or inactive add-ins and right over here we’ll see the solver add-in here it says it’s excel add-in that’s the type of this add-in so right down here there’s a drop-down list that shows you different add-in types and we see excel add-ins here let’s select this option and then click on go this opens up another prompt where we can see all of the different excel add-ins and you should see the option for solver make sure to check that box and then click on ok once you check that box on the data tab on this ribbon all the way over on the right hand side you should now see a new category for analyze that includes solver if you went through all those steps and you now have it congratulations you just solved how to add solver let’s now click into solver this opens up solver and here we can enter in all of the different solver parameters and at first glance compared to goal seek this probably looks a lot more complicated and it is a little bit more complicated you have a lot more options however we can duplicate what we did in goal seek exactly right up here we have the option to set the objective and first i want to see what is the break-even point so my objective is right here in this cell the total profit i want to know what will it take to get to zero so here i’ll click here and i’ll set this as my objective next i have a few different options and we’ll go into more details on these but we have three different options you can set the max the min or a value of so let’s say i’m running through and i want to solve what will it take to let’s say maximize my profit i would select max or what if i want to minimize my costs i could select min or over here just like in goal seek i can set it to a specific value and here i want to set my total profit to zero because i want to know the break-even point so i’ll set this to zero now right over here i can change variable cells and that’s basically what value do i want to experiment with to get to that break-even point well just like before i want to see how many cookies i need to sell to get to this break even point so i’ll click here and let’s select cookies sold and i now have everything defined that i need now there’s a lot more richness here compared to goal seek for example we have the max the min here you can choose variables and you’re not just limited to one variable you can set multiple variables in here and down here we can even set constraints and in a moment we’ll come to this but i want to keep it simple for now and let’s see if we could duplicate the results so everything else here for now is fine and don’t worry we’ll come back and we’ll go through it but for now let’s click on solve and look at that solver has found a result and here if i move it over here just like before i can see that i need to sell 4 000 cookies to get to the break-even point once again that’s a lot of work and it’s really starting to settle in now how many cookies i’m going to have to make i’m not excited about that but here it looks like solver has completed successfully i’ll click on ok and this looks great i was able to use solver to do exactly what i did with goal seek now of course why use something more complex when you have goal seek let’s see a true example where solver really comes in handy and for that let’s go down to the bottom worksheet called solver advanced and click into this and wow this looks a lot more advanced than the previous example now this might start to look a little bit scary but when you see how solver works it’s actually pretty beautiful how it could let you find or help you get to the optimal answer so right here i have another business question that i want to answer i want to know what types of cookies should i bake to maximize my profit like i said in the intro at the kevin cookie company we have lots of different cookie types all listed right here for each cookie type i need to decide how many cookies to bake once again my kitchen can only bake so many cookies my oven capacity can only make up to 15 000 cookies per day and so i need to carefully decide which cookies are most profitable for me but along with that i also have some constraints i have the oven capacity constraint i also have demand constraints too i can’t just make all chocolate chip cookies because people only want so many of them also over here i can see how much profit i make per cookie so some cookies are more profitable than others in fact chocolate chip cookies are a lot more profitable than fortune cookies they just don’t have the same margins and over here i can see the total profit per cookie type and also the total profit overall so let’s take a very quick example let’s say i decide to bake one chocolate chip cookie i’ll enter in one over here so here i’ve made a dollar fifty and my total profit is a dollar fifty if you look across all of these different cookie types you might notice that chocolate chip has the highest profit margin here it’s a dollar fifty so why not just make all chocolate chip cookies and forget the rest of the cookies who needs those but one thing to remember is people only want so many chocolate chip cookies so let’s say my oven capacity is fifteen thousand what if i said hey let’s make fifteen thousand chocolate chip cookies well people aren’t gonna eat them all because people don’t want fifteen thousand chocolate chip cookies i’d be left over with five thousand cookies and that’d be bad for business i don’t wanna have any leftovers so instead i want to use solver to help me figure out the optimum combination of cookies to make now one thing to call out i do want to show you how you can do this without solver just so you understand how it works and then when we run through solver it’ll make a lot more sense so here in this example well chocolate chip is my most profitable and demand is only 10 000. so here i’d want to make let’s say 10 000 cookies this way i get 15 000 profit if i go down this list i see that my next most profitable cookie here is the oatmeal raisin so here let me max out the demand there as well and now i have 14 000 baked and i have a thousand left and here i see that my next most profitable cookie is a white chocolate macadamia so then i’d make a thousand of these so this will get me to the maximum total profit but let’s say i had a lot more cookies or maybe the example had more constraints it would become very difficult to do this manually so let’s go to solver and see how it can help us once again to access solver let’s go up to the top tabs click into data and all the way over on the right hand side of the ribbon let’s click on solver this opens up solver and now we’re going to use more of the capabilities compared to the previous example here we can set the objective and i want to make as much profit as possible i’m a true capitalist here so let’s move this over to the left hand side and for the objective it’s going to be this total profit cell here i’ll click on this and then let me select this cell next i need to decide well what do i want to do with this do i want to set a max a min or value of now once again i want to maximize my profit so over here i can click on max and this will find all the different variables that lead to the maximum profit now over here i can define what cells do i want to change so before we always changed one cell but once again one of the values of solver is that i can change multiple variables so here i’ll click over here i want to figure out the combination of cookies that i should bake so here i’ll select all of these different cells and then let’s click on ok next i want to add some constraints right here for the quantity that i bake i don’t want to bake any fractional cookies i need them all to be integers so that’s going to be my first constraint let’s go over here and click on add this opens up a prompt where i can add my first constraint and once again i don’t want any fractional cookies so i’m going to select all these values here this is going to be how many cookies i’m baking and right here in the drop down list i can select integer so my constraint is all of these have to be an integer once again they’re not going to be fractional cookies once i’m done i’ll click on add next i want to make sure that the cookies that i bake don’t exceed my oven capacity right down here i have a cell called total baked and this is simply a sum of all of the cookies that i make and i need to make sure that this does not exceed fifteen thousand in fact i want it to be exactly fifteen thousand i wanna use the maximum capacity of the oven i don’t want to go below and of course i can’t go above so right here i’ll click on the total baked cell then i’m going to select the equal sign and my constraint right here is going to be 15 000. next let’s click on add and lastly i want to add one more constraint i want to make sure that however many cookies i decide to make for each one of these cookie types doesn’t exceed the overall demand for that cookie type so here i’m going to select all of these different cookie quantities that i’m going to bake and i need it to be less than or equal to the total demand so here i’ll select the total demand and that looks good so i’ll click on ok this opens up the solver window again and here i can see all the different values that i input i could also see all of my different constraints down below so all of these constraints look good i’ve defined them all now and down below i have a few more options and i do want to take a moment just to look at these right here there’s a check box that says make unconstrained variables non-negative what the heck does that mean basically as it’s going through and setting these different variables i want to make sure they stay positive i don’t want to bake any negative quantities of cookies unless of course maybe i’m starting up a weight loss program that might make sense but for now i only want to make whole cookies so let’s leave that box checked and down below we have a few different methods for solving this when i click on this drop down we have grg non-linear simplex lp and evolutionary what are these and which one should you choose let me show a quick example of what these mean the first one is called grg non-linear what does that even stand for it’s generalized reduced gradient and that probably doesn’t tell you too much more about what it does basically if your data is in a non-linear form so here i have an example it’ll find the maximum point or maybe it’ll find the minimum point or it’ll find a specific value now of course there is a downside with that approach let’s say it’s going through and it finds the maximum point this is referred to as the local maximum as soon as it finds something that seems like a high point and it starts going down again it’ll select this as the best option however as you go a little farther along it might find another value that’s even higher and this is where you can use the evolutionary model it’ll find the what they call the global maximum or the global min however the one downside with this approach is it also takes a lot more compute power and it takes a lot longer and i’m not kidding when i say it takes a long time i have a powerful computer and even with this simple example it took a long time lastly there’s also the simplex linear model right here and if your data forms a linear model this is the best and quickest one to select to find the optimum value so now that we went back to school for a moment which one should you actually choose well there’s a reason that microsoft excel makes grg non-linear the default this one will not only solve non-linear models but it’ll also solve linear models so even if you have this selected it’ll still solve for linear so it’ll do what the simplex lp does however it might take a little bit longer then you also have the evolutionary and especially with that two hump example the grg might miss that but in most cases it goes so much quicker it’s worthwhile going with this one and if you’re truly worried about that double hump issue that i showed you can click into options and yes you have a lot more options if you thought the previous screen was intimidating this one is even more so here you have tons of different options but here if we click into grg non-linear you have the option to use multi-start so this way it’ll search at multiple points and so if there are let’s say two humps or three humps chances are it’ll find that optimum point that you’re looking for so here i’ll just set it to use multi-start also here for the evolutionary model you can also configure this as well to maybe try to speed it up a little bit so you have lots and lots of different options that you can play with but chances are if you’re simply running solver you should be able to get by by just selecting grg non-linear and if once again you’re worried about the double hump simply select that multiple start option once we’re all set to go let’s click on solve and let’s see what this gives us and check that out it looks like it came up with a solver solution here within the prompt i can choose to keep the solver solution here you see that it automatically enters the solution into my table or i could have it restore the original values but once again the original values were all just zeros that’s not going to help me much all of this now looks good i could save the scenario if i want to come back to it but for now i’m good i have all the values here so i’ll click on ok so here now you see that solver automatically calculated the quantity to bake so here i should max out my chocolate chip cookies followed by oatmeal raisin and then followed by white chocolate macadamia so i’m sorry for all of the sugar snickerdoodle and fortune cookie fans but unfortunately they just don’t make as much money and with limited oven capacity i need to focus on these to give me the highest amount of profit alright well that’s how you can use both goal seek and solver in microsoft excel if you were able to solve some of these cookie problems please give this video a thumbs up to see more videos like this in the future make sure to hit that subscribe button also if you want to see me cover any other topics in the future leave a note down below alright well that’s all i had for you today i hope you enjoyed and as always i hope to see you next time bye [Music] you