Alright. I'm going to explain what an optimization problem is in this screencast. Engineers, and scientists, and mathematicians oftentimes are trying to optimize the process and optimizing either means maximizing or minimizing. So, let's go through exactly what an optimization problem is. So, similar to a targeting problem, an optimization problem has an input cell and an output cell. So, for example, we might have an input of 2.5 and somehow, through a mathematical relationship, the output cell depends directly on the input cell. So, it's some function, the output cell is some function of the input cell. So, we might have x and f of x. Now, the target is the output cell and that's what we're trying to optimize. We are either trying to minimize that or maximize that. So, the goal is to maximize or minimize the target which is f of x. Now, we can do this in Excel using the solver tool. I showed in a previous screencast on targeting problems that we can use the solver to set a value equal to some target, but we can also minimize or maximize a certain cell with a given input. So, let's go through an example. I have an example here, we wish to find the max and min of this function, f of x in the range zero to 10. Similar to a targeting problem, we need to come up with some sort of initial estimate or initial guess. So, I've got this problem setup in Excel, we're going to need to start with an initial guess. So, I'm just going to say five and we're going to calculate the minimum and maximum. Now, before we do this, it's nice to make a plot of your function. So, let's go ahead and do that for zero to 10. So, I've typed in my function here as a function of x and when I press enter, then that gives me my function value. I can double-click here to bring that down. I'm going to go ahead and select all of these and go into insert chart and I'm going to just make a simple line graph. So, this is kind of showing us our function. Now, we want to find the minimum and maximum in this region. Now, the minimum is going to be here at about 2.5, x equals 2.5. The maximum, depending upon how we do things could either be here at 5.5, or it might find the maximum of zero which is the lower end of our domain. So, in my min cell here, I'm just going to go ahead and type in the function. So, I typed in my function referencing the x and right now, at a value of five, we are calculating and I'm just going to change this actually do f of x. So, our function value is 0.24 and now we want to find the minimum. Now, the solver tool, just like the Goal Seek tool needs a initial guess which we've already got. So, I'm going to go ahead and go into data solver, and I'm going to set objective which is going to be f of x and we're going to set that to a minimum by changing variable cell B2. So, let's go ahead and do solve and it churns through and finds that there's a minimum at about x equals 2.51 which is the y value of the function value of about negative 0.6. I want to show you how we can change the minimum that we find by changing the initial guess. So, you see there's another minimum here at about x equals nine. If we guess around nine, then we should get the minimum closest to nine. So, I'm going to change my initial guess to nine. We can go back into the solver tool. The nice thing about the solver tool is that it remembers scenarios. So, it remembers everything we did. So, I just have to do solve, it cranks through and finds the minimum closest to nine which is 8.8 with a y value of about negative 0.17. Now, let's use the solver tool to find the maximum and I'm going to put in a value of six here and we're going to run the solver tool. Instead of calculating the minimum, we're going to select max and we click solve, it cranks through and it finds that maximum at about 5.65. Now, if we put the initial guess very close to zero. So, let's let's do this. Let's do closest to zero. Let's do our solver tool, click OK, and it finds a value of zero. The reason that it did this it found for the value where x equals zero, is right now in our solver tool, I have this box here, selected that says make unconstrained variables non-negative. So, if I deselect that then, I can allow for negative values of x. So, let's go ahead and rerun it. So, now it goes through and if I would have plotted negative numbers here, there would have been another max at about somewhere at about x equals negative 0.62, alright? So, that's what it's going to find. Now, if we wanted to force a certain domain into our solver box, I can go into solver and this is where we can add in our constraints. So, I can click on add. We want to make it such that B2, has to be less than or equal to 10. So, that's our upper domain. So, I can click add and then, my B2 also has to be greater than or equal to zero. So, that's how you can add constraints into your solver scenario and now, I can click run and you see now with negative values of x aloud and adding a constraint that x has to be between zero and 10, it's finding that the maximum value closest to zero is about 0.91. Let's go through another somewhat creative example. You have just escaped from prison, so you are right here at the 0,0 position, and you've got a getaway car waiting for you. That's one mile north and one mile east of where you are. So, you got to get a getaway car and a friend maybe right there. You've got a swamp that you have to travel through and you can only go two feet per second through the swamp, and you can only go four feet per second through the forest. So, you can go twice as fast through the forest and so obviously, depending upon the angle that you choose to begin with, that's going to govern the time required to get to the getaway car. So, if you can go very slowly through the swamp, then maybe you want to make this distance pretty short. So, you might want to make this as short as possible, but the shorter you make this leg of the trip than the longer, the second leg of the trip is going to be. So, this is an optimization problem and this is a problem that I give to my students every year. If you set up an equation for this, you end up with the following and this is just through simple geometry. The time it takes for you to get to the getaway car is a function of the angle which is the independent variable. The angle is expected to be between 30 and 90 degrees. If you guess a theta, then you can plug it into the right-hand side and that'll give you the time required at that chosen theta that it will take you to get to the getaway car. So, this is an optimization problem that we can solve using Excel. So, I've set up my spreadsheet here. Solver needs an initial guess. So, I'm just going to choose something between 30 and 90 degrees, a nice round number of 45 and now, I can use this equation here to calculate the time in seconds that is going to take to get to the getaway car. So, I've got my guess of 45 degrees. I've set my equation here and note that I have used the radians function because cell B2 or angle is in degrees, we have to convert that to radians. A good way to do that is to use the radians function because the sign in tan functions in Excel need radians as their units. So, I've got this in here, I press enter. So, if you go at an angle of 45 degrees, it's going to take you 2,800 seconds. Let me quickly convert this two minutes by dividing by 60. We can use the solver tool. Now, we're going to set the objective cell B3 to a minimum value by changing cell B2. When I click solve, it churns through and it determines that if you go at an angle of 65 degrees, that's going to get you to the getaway car the fastest after about 44.4 minutes.
