WEBVTT
Kind: captions
Language: en

00:00:07.609 --> 00:00:13.440
The real power in Excel comes in its ability
to perform multiple mathematical calculations

00:00:13.440 --> 00:00:17.410
for you.
One of the tools in Excel that you can use

00:00:17.410 --> 00:00:23.300
to perform these calculations is a Data tool
called What-If Analysis.

00:00:23.300 --> 00:00:29.000
What-If analysis allows you to see the effect
that different values have in formulas.

00:00:29.000 --> 00:00:35.440
It sounds complicated, I know, but let's think
about it in real-life scenarios.Maybe you've

00:00:35.440 --> 00:00:42.110
wondered, What interest rate do I need to
qualify for to have a car payment of $400

00:00:42.110 --> 00:00:49.110
on the car I want? Let's say you need a loan
to buy a new car.

00:00:49.180 --> 00:00:54.579
You know how much money you want to borrow
how long of a period you want to take to pay

00:00:54.579 --> 00:01:01.579
off the loan, and how much money you can afford
to pay each month. But what you need to know

00:01:02.899 --> 00:01:09.700
is what interest rate I need to make my payment
$400 a month.

00:01:09.700 --> 00:01:15.700
Right now, I can see that if I didn't have
interest and just broke this $20000 into 60

00:01:15.700 --> 00:01:21.810
monthly payments, I'd be paying about $333.33
a month.

00:01:21.810 --> 00:01:28.750
But, I need to find the interest rate that
will give me a $400 a month payment.

00:01:28.750 --> 00:01:34.560
Rather than performing all these calculations
on your own, you can use the What-If analysis

00:01:34.560 --> 00:01:39.740
tool.
Before we get into that, you may be wondering

00:01:39.740 --> 00:01:45.650
about this function you see in B5.
I've already inserted a function into this

00:01:45.650 --> 00:01:51.600
cell that will calculate this for me.
This isn't part of the What-If analysis, but

00:01:51.600 --> 00:01:56.600
let me just really quickly show you what I
did.

00:01:56.600 --> 00:02:00.820
I needed a formula that would calculate the
monthly payment.

00:02:00.820 --> 00:02:05.740
Instead of writing a big formula myself, I
inserted a function.

00:02:05.740 --> 00:02:12.740
To do this, just select the Formula tab, and
click the Insert Function command.

00:02:13.200 --> 00:02:20.200
From the dialog box that appears, select PMT
(short for payment), and then click OK.

00:02:21.340 --> 00:02:28.340
You're then asked to select cells that will
give the function the needed information.

00:02:28.640 --> 00:02:33.810
This dialog box looks really confusing, but
it really is very easy.

00:02:33.810 --> 00:02:39.230
As you select a field, a description is displayed
below that basically tells you what information

00:02:39.230 --> 00:02:45.220
and cell you need to choose.
Once you've entered all the info, you'll just

00:02:45.220 --> 00:02:49.760
click OK.
Right now, I'll just close this.

00:02:49.760 --> 00:02:54.590
If you're still not comfortable with functions,
you should watch our video lesson about functions

00:02:54.590 --> 00:03:01.590
which will give you some more information.
OK, let's go on to the What-if analysis tool.

00:03:03.300 --> 00:03:09.810
There are actually 3 types of What-If Analysis
that you can use.

00:03:09.810 --> 00:03:16.330
To see these, select the Data tab, and locate
the Data Tools group.

00:03:16.330 --> 00:03:22.250
Click the What-If Analysis command and a list
with the three options appear.

00:03:22.250 --> 00:03:26.980
All three of these are different types of
what-if analysis.

00:03:26.980 --> 00:03:32.760
One of the tools, Goal Seek, is the perfect
tool to help you determine the interest rate

00:03:32.760 --> 00:03:39.170
you must qualify for to meet your needs.
Once you select Goal Seek from the menu, a

00:03:39.170 --> 00:03:45.230
small dialog box appears.
Here, we have to set a specific cell, to a

00:03:45.230 --> 00:03:52.230
specific value, by changing another cell.
In this situation, we want to set B5, our

00:03:53.069 --> 00:03:58.440
Payment cell.
It already appears in the dialog box because

00:03:58.440 --> 00:04:03.760
the cell is selected.
We want to see it at a value of $400.

00:04:03.760 --> 00:04:08.700
Since we're making a payment that will be
subtracted from our loan amount, we have to

00:04:08.700 --> 00:04:14.500
enter that as a negative number.
We'll move down to the next field.

00:04:14.500 --> 00:04:21.500
Then, we need to set the cell that we want
to change that will give us that payment amount,

00:04:26.589 --> 00:04:32.960
so we'll select cell B4, which is the interest
rate.

00:04:32.960 --> 00:04:39.029
Left-click a cell to select it, and it will
appear in the dialog box.

00:04:39.029 --> 00:04:46.029
Now, click OK, and then OK.
Now, we can see that the interest rate appears

00:04:47.710 --> 00:04:54.710
in the cell, and we know that a 7% interest
rate will give us a $400 a month payment on

00:04:55.189 --> 00:05:02.189
a $20,000 loan that is paid off over 5 years,
or 60 months.

00:05:04.099 --> 00:05:09.949
As you can see, in Goal Seek, we gave Excel
the result that we wanted a payment of $400

00:05:09.949 --> 00:05:16.279
a month over 60 months, and it gave us an
input value that would give us that result.