Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
In the final exercise of this call, so we're going to practice some of the skills that we've learned
related to what if analysis.
So we've got a few different exercises in this workbook.
And first, I'd like you to start out by practicing constructing a panty formula and also using goal
seek.
So remember, PMT is going to allow you to calculate the monthly payments on a loan using the loan amount,
the rate and the term.
I'd like you to ensure that the monthly payment is a positive value and not a negative one.
I then like you to take that monthly payment and use Gulf C to find out how much extra money you could
borrow if you were making payments of $3000 a month.
Next, I'd like you to practice using scenario manager, and we want to use scenario manager to work
out which wedding venue will be the most cost effective.
So I'd like you to set up three different scenarios venue one, which is going to be our original data
venue two and Venue three.
Now, if a venue one, I'd like you to leave all of these values as they are for Venue two, I'd like
you to modify the venue calls to $12000 and the food cost to two thousand five hundred of a venue.
Three.
I'd like you to modify the venue cost to 10000, the food cost to three thousand five hundred and the
flowers costs to 1000.
And remember a quick tip here.
If you want to make your life a little bit easier, remember to name your ranges in the next part of
the lesson.
I'd like you to practice creating a one variable data table.
So we're going to be using a PM team calculation again, and this is going to help us calculate the
monthly loan payments at a variable interest rate.
So we want to make sure that we have all of our different monthly payments listed down here in Column
C. Once we've done that, we can then move on to the two variable data table.
And this is going to calculate the monthly payments at a variable interest rate and a variable loan
amount.
So quite a lot of work to do in this particular exercise.
So now is the time to pause the video and give this a go.
If you'd like to see my answers, then please keep on watching.
So the first thing we're going to do here is we're going to add in our PMT calculation.
The rate is going to be this cell just here.
But remember, this is a rate per annum, so we need to divide by 12 to get the monthly rate.
The number of periods is the terms.
And again, this is in years.
So I need to multiply by 12 to get that into months.
And then our final argument is the present value, which is the loan amount.
And remember, if you want this to be a positive value, you need to add a negative symbol in front
of the present value.
Close the bracket, hit enter and I can now see my monthly payment under these conditions.
Now looking at this, maybe I say to myself that I can actually afford $3000 a month, so I'm going
to use Goal C in order to work out how much extra money I can borrow.
So after data into what if analysis, I'm going to choose Go sake.
Now we're going to set sail.
Let's delete that b eight to the value 3000.
I'm going to do that by changing the loan amount, which is sell b five.
Click on OK.
And we get a kind of crazy number in here, but all we need to do is take those decimal places down
and then I can add a currency symbol in here.
So that is how much I can now borrow.
Moving along?
Let's take a look at the next one.
And this is using scenario manager.
So the first thing I'm going to do here is I'm going to hold down control and I'm going to name all
of my ranges that's go up to formulas and create from selection.
I'm going to use the label in the left column for these ranges.
Now let's just check to make sure they're there.
Yes, there they are.
So now let's create three scenarios up to data into what if analysis and scenario manager.
Now our first scenario here is venue number one.
This is our original scenario.
We're going to call this venue one and our changing cells are going to be this cell and these cells
just here because the other two are calculations based on what we have above.
Let's click on OK, and this is the original scenarios, and nothing is going to change here.
Let's click on, OK, let's add scenario number two.
So this is for Venue two, the same cells could change.
That's click on OK.
Now this time we want to modify the venue cost to $12000.
So we're going to go down here and change this to twelve thousand.
And we're also going to modify the food cost to two thousand five hundred.
So quite a bit cheaper.
And click on.
OK, let's add a third scenario.
Venue three.
Click on OK, now for this one, we want to modify the venue cost to 10000 or this one actually already
is ten thousand.
We're going to modify the food cost to three thousand five hundred.
And also, if we scroll down, we're going to modify the cost of the flowers to one thousand.
And click on.
OK, so now we have our three different scenarios.
I can click on the show button to view those scenarios.
Alternatively, I can click on the summary button and view the summary report.
So by looking at this, I can see that Venue three is going to give me the most amount of money left
over.
In the next part of this exercise, we're going to create a one variable data table.
So once again, we need a party calculation in here.
So our rate is this we're going to divide it by 12 to get it monthly.
The number of payments is this, and this is in years, so we need to multiply by 12 and loan amount.
I'm going to put a minus sign.
Is this just here?
Close the bracket.
Enter.
So now that I have this calculation, I can create my one variable data table and my one variable is
the rate.
Now, the first thing I need to do here is link to my PMT calculation so that I can easily select everything
in this table.
Let's go to what-if analysis and data table, and this time I'm only going to use the column input cell
and my column input cell is going to be the right click on.
OK, now I have some crazy decimal places going on here, so let's just take those all the way down
and apply some currency formatting.
And there we go.
Simple as that.
And finally, we have our two variable data table, which works along the same lines.
So let's quickly add in our PMC calculation again, we're going to do our right divided by 12.
We want a number of periods multiplied by 12.
And then we want our present value, which is up here.
Like, so let's link to party calculation.
And now we can create add two variable data table up to the data tab into what analysis data table.
And because we have two variables, we need to fill out both of these.
So our row input sell this time is the loan amount and the column input is the rates.
Click on OK.
Let's select these values and just sort out those crazy decimal places.
Let's bring those all the way down, and we can also add some currency formatting.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.