All language subtitles for 5. Exercise 12

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.