In this screencast, I'm going to show you how we can use the Goal Seek and Solver tools in Excel for targeting problems. First of all, let's define what a targeting problem is. A targeting problem in Excel is when you have some sort of input cell and an output cell and the output cell depends upon the input cell through some sort of mathematical relationship. And this will become more clear here in a minute when I work through an example. We want to change the input cell such that the output cell meets a particular target. For example, the target could be a 0 or it could be a number like 5. So in a targeting calculation that we're going to use the Goal Seek and Solver tools in Excel to perform, we wish to change the input cell until the output cells equal to our specified target. I've got a really easy example here of a targeting problem. We want to solve the equation 4 + y = 12. The solution is y = 8. When y = 8, then that equation is true. We can use Excel to do this. We have an input cell which is going to be our variable y, and I just put in an initial guess. These targeting problems require an initial guess. We can just put in something that makes sense. So I'm just going to put in a 5 here in the input cell. And then we have an output cell. The formula in the output cell is equal to 4 + y. And right now, with our input cell of 5, our output cell is equal to 9. And what we're going to do then is we're going to have Excel, the Goal Seek tool or the Solver tool vary the input cell until the output cell is equal to our target of 12. This is in a file called targeting problems. I've got three examples each on Sheets 1, 2, and 3. The first one here is this very easy example. I've got cell B5. I have a value of 5 in there, it's named y. And I'm just going to put a formula in here, 4 + y, and that equals 9. The target for our output cell is 12, right? We want to adjust y until we solve this equation. And so what we're going to do is we're going to vary the input cell until the target cell equals 12. There are two tools built into Excel to do this. The first one, we can go up to the Data tab and we can go over here to What-if-Analysis and click on that. And it's called the Goal Seek tool. So let's click on the Goal Seek tool. It has a Set cell field, a To value field and a By changing cell field. We want to set our cell, this first field is the target. So I want to set cell B7 equal to a value, we can put a value in here. I want that to be 12. By changing cell, we want to change the input cell. So we're varying input cell. We want to set cell B7 equal to our target of 12. And when I press OK, it goes through this, and it finds the solution. So 8 is the solution to this equation. So this is very easy targeting problem. We can also use something known as the Solver tool. If we go over the Data tab and way over here on the right, it should show up as a Solver tool. If the Solver tool is not showing up, which is probably the case for most of you, you can go over here to the File menu, go down here to Options, and then we're going to go into Add-ins. And at the bottom, go ahead and click Go, and make sure that the Solver Add-in is selected, is checked, and then we can click OK. And it should show up now on the data tab in the Analyze group over here on the right. So I'm going to put my guess back to what we had originally, it was a 5. I'm going to click on the Solver tool. The Solver tool is a lot more sophisticated. Its more common for use in optimization problems in which you're trying to minimize and maximize something. But we can also use it for targeting problems. So our objective, our objective here is our target, that's going to be cell B7, then click on that in the set objective. We can select to a of Value Of, and I'm going to put in 12. We want to set that target cell to our target of 12. And by changing, you notice that one of the differences between the Solver tool and the Goal Seek tool, is the Solver tool, you can change multiple cells. We have variable cells. We only have one cell, one input here. I can click on cell B5. I'll show you in a subsequent screencast how we can perform Solver calculations with constraints. So you can add in constraints here. But let's just go ahead and click Solve for now, and it goes through, and it finds a solution, and we can click OK. So that's how we can use the Goal Seek and Solver tools for a very easy targeting problem. Let's go to the second example on Sheet 2. Recall from earlier in the course, we talked about how we can use the NPER function to calculate the number of payments if we know the interest rate, the principal, and the payments that we're making the amount of the payments. The NPER function actually is an iterative calculation. It's actually doing similar calculations to what the Goal Seek and Solver tools are doing but it's just kind of doing it inside the function. Let me show you how we can arrive at at the same conclusion as the NPER function by using the Goal Seek tool. So what we're trying to do is we're trying to determine the number of payments if we can only make payments of $350,000 a month. So I'm going to go over here, and I have a guess. So this is our input cell for the Goal Seek tool. I'm just putting in a guess of 15. And down here, I'm going to put in the payment function. The rate is going to be our rate over here on the left divided by 12. The number of periods is going to be our guess. So I'm just going to click there, and I've named that cell n. So we're calculating the payment based upon my guess. The present value is going to be our principal. And I can go ahead and close the parentheses and press Enter. Now, if I had guessed the right number of payments, then this should be -350. But right now, it's 551. I can use the Goal Seek tool or the Solver tool. We want our payment here to be a value of -350 by changing our guess. So, the Goal Seek tool is going to change the number of payments until the payment amount is 350. And so, I can click OK. And it goes through here, and it finds a solution of 24.7, which is exactly the same as the NPER function is doing. So that's another way that we can use the Goal Seek tool. So let me put this guess back to 15. I'm going to show you how we can do this using the Solver tool. So I'm going to click on the Solver tool. Our objective is cell H7, and we're going to set that to an objective of -350 by changing the variable cell n, the number of payments, that's H5. So I'm going to go ahead and click Solve, and it finds a solution, and it's finding the same thing that we got using the Goal Seek tool.
