Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
What if analysis let's you use the power of computer to do many mathematical calculations for you very quickly. If you know the result that you want from a formula, but not the input values that the formula needs to get that result, you can use the Goal Seek feature. And that's found under the Data tab, in the Data tools area. Excel is fairly powerful and you have three different What-If analysis options, but we're going to concentrate on the Goal Seek function in this movie and in our basics.
EatCake has been doing very well and are thinking of expanding so they want to be able to take a loan out of $100,000, and pay it over 180 months. They have a particular payment in mind, but they want to know if the interest rates are anywhere near the right levels for them to be able to secure a loan of this amount. They're not really sure what interest rates will allow them to do that. So rather then putting in values here and trying to calculate numbers here to work with this particular formula, we're going to use the What-If analysis, in the Goal Seek options.
And I'm just going to move this particular dialog box up here so it's closer to the top of our screen. Basically, what this dialog box is asking us is it's asking us to set a particular cell, which is our final payment, to a value. And we're going to put in a value of $900. We have to put it in as a negative value in this particular instance because the payment that we're looking at comes out as a decrease of our overall payment we need to make.
So I'm going to put it in as a -$ 900 because that's what I can afford. And I want to identify what cell I'm going to change in order to meet that number. Well the cell that I'm interested in changing is the B3, my interest rate, because I'm not quite sure what my interest rate should be. So I put in B3. So basically, we're telling it to set this cell here, B4, where I have my formula, give it the value that you're looking at, and change up this cell here in order to get it for you.
Let's click the OK button and see what happens. Notice that it comes up with a value of 7%. You need an interest rate of 7% on a loan of this size, over this amount of time, in order for your payments to be $900 a month. That was pretty quick. Goal Seeking with cell B4 found a solution. Your target value was -$900 a month, that's what you want to pay, and it would have to get an interest rate of 7%. Now that's too bad in today's environment, but I may want it to be a little bit less, so let's try that one more time.
I'm going to go back up to my What-If analysis. I'm going to go select Goal Seek. I'm going to still set that same final number, B4. This time I'm going to make it $ 600, $600 is what I want to pay. And I'm going to still try to change up my interest rates, and then click it off. Now, it's looking at it here and it's telling me that it only needs an interest rate a 1% in order to be able to pay that off. That looks pretty cool but I don't know if I'll ever be that lucky to get an interest rate of only 1%. But you can see here that the power of this What-If analysis allows you to do many, many calculations, very quickly, in order to find out where you're values are that you need.
So keep this particular formula in mind in the future, when you want a use the power of Excel to save you time doing mathematical calculations.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 75421 Viewers
80 Video lessons · 130168 Viewers
52 Video lessons · 64271 Viewers
59 Video lessons · 50087 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.