Welcome to Excel and

Finance video number 3. Hey, if you want to download

this workbook and follow along, click on my YouTube channel,

then click on my college website link, and then you can

download the workbook Finance and Excel chapter 00. If you're in the class, just

go to our class website. Hey, in video number 3, we want

to talk about math symbols, and then we want to talk

about the order of operations for calculating formulas

in math and Excel. Hey, we're going to have

to use lots of parentheses in this finance class.

Exponents, caret, which is Shift

6, multiply, which is asterisk, on the number pad–

most convenient– forward slash is

division, add, subtract. We may even have to use

comparative operators. We already use equals

sign to create a formula, because equals sign is the

first character in the cell. And that tells

Excel that you want to do a calculation

or a formula. But you can also

use an equals sign– which we'll see in this video–

as a comparative operator. You can ask the

question, is this thing equal to this other thing? Here are some other

comparative operators. Important to notice that greater

than and equal to, less than and equal to, are two

characters next to each other. It's not like in math. Not, which probably won't

get to use in this class, is those two symbols. And join, which we probably

won't get to use either. All right. Let's see some math examples. Adding. We already know by now– this is

the third time we'll see this– Alt equals.

What does Alt that

plus equals sign do? It is sum. So don't even chance it. Sometimes people want to go

like this for a small one. But I'm sorry,

that takes longer. And if for some reason you

did a structural update, like we saw in our last video,

that formula doesn't work. Alt equals. And it guessed right. So now, last couple of videos

I've been hitting Enter.

But if my goal is to create

formulas and go to the side, I do not put the formula

in with an Enter. I use Tab, the Tab key. Subtracting. I'm going to say equals. Now I'm going to

use my arrow keys. Remember we talked

about last video, you can use your mouse

or your arrow keys. I'm going to click Escape. Arrow keys are much faster

when you're close in like this. So I hit up arrow

twice, and then minus, and then up arrow once. I'm going to hit Tab. So we get negative 3. 2 minus 5, negative 3. All right, dividing. Important thing about dividing. Equals up arrow, up arrow,

division, up arrow once. Notice this is

called the numerator, and this is called

the denominator. This is the top. This is the bottom. If the top, the numerator, is

bigger than the denominator, the number will always

be greater than 1.

The opposite is true. If I make a formula where

the numerator is smaller than the denominator,

this number will always be less than 1. If they're both the

same, then they're 1. So that's division. Multiplying. We could do this times– use the asterisk on the

number pad, and then get that. However, similar to

the sum function, if we have a bunch of

things we're multiplying, you can use the product

function Whoops, product. Product. The word product means in

math, that's the answer. The product– 6 times

6, the product is 36. So you can highlight

it like that. I don't think we'll get

much chance to use this. We'll usually be using

the multiplication symbol. I hit Tab. Exponents. Now we're going to talk about

the whole sheet over here about– we're going to

talk about exponents. But just an exponent. If I take 6 squared or six with

an exponent of 2, equals 6, and you have to do Shift 6. That's a caret symbol. That's the exponent

symbol in Excel. And that means the base is

6, and the exponent is 2. That means take the 6, repeat it

two times with a multiplication symbol in between.

So it's 6 times 6. Again, we will have a bunch

more examples of exponents in just a moment. Now let's look at a

comparative operator, because a few times

in this class, we are going to

have to make what are called logical formulas. They don't give us

a number answer, or they don't give us a word

answer like text formulas do, which we don't do a lot

in this class either. But logical formulas,

occasionally we will do them. We'll need to ask the question– make a formula. So we type in equals sign. Then we want to say, is this

equal to-_ the equals sign is not the first character. That tells Excel, I'm a formula. This is a comparative operator.

So this formula says,

is this equal to this? And this is very

important sometimes, because we may

have discrepancies which we can't detect

because it's so far out in the decimal place. Now, a logical formula

or true false formula only has two results. It's either true or false. Now, before we investigate

why those aren't equal, let's do another one. Equals, is the value in M3

greater than the value in M4? Well, it better say true. Yeah. Now, let's go over here. Almost always– and

this is actually extremely common in Excel,

because number formatting can disguise the number.

And we'll talk about this

in a video coming up. But right now, let's

just investigate this. Now, if you click

in the cells, you can look up in the

formula bars and tell that there is a difference. It means that somehow

that decimal is hiding. So on the surface

of the spreadsheet, our eyes see 10, 10, and

we immediately say ah, the accounts are in

balance or something, or the two dollar interest

amounts are the same. But what's happening here? Well, in this video,

we're just going to see how to

increase the decimals. Now, we added a

decrease the decimal. I'm going to come over to Home. In our first video, we

saw how to add buttons to the [INAUDIBLE].

Now notice what's nice about

the quick access toolbar is if we use increase decrease

decimal or borders or font color all the time, it's

convenient to add these. So I'm going to come over here. Oh, I can't even see this. So I'm going to have to

come to the side and expand. Click the Home. And I'm going to right click

Add to Quick Access Toolbar. In fact, I think I'm

going to do a few more. I'm going to– well,

anyway, not now. For now, we've got those two. All right. Let's go ahead and use it. Let's increase decimal. And if you point your cursor,

and you can't figure out which one is which,

the screen tip will come up,

increase, increase.

And then we can see in

fact what was happening is, the number

formatting– this is called number formatting–

was disguising our numbers. Guess what? And this is one of the

most common mistakes in Excel on the planet earth. People think that what

they see is actually there, and it's not. And that's why this formula is

so good, because it'll never get tricked by formatting. It never looks at the

formatting numbers. Formulas don't look

at the number format. They look at the

underlying number. And that's why that formula

told us there's a problem. So if it was a

problem, we would now know that they're not equal. If they were interest

amounts, and we're saying, OK, this is the greater

interest amount– if it was a mistake, then

you'd go ahead and fix it. Like this was actually

a 0 or something. And then it would

immediately change to true. I'm going to Control Z, because

those are in fact different.

All right. We've got to go look

at order of precedence. Now, Please Excuse

My Dear Aunt Sally. In finance, we have

big, huge formulas that we're going to do. A lot of times

we'll have functions like PMT FVNPT and rate and

all these great built-in Excel functions that

will do the big formula calculations for us. However, even though

there's some cool functions, there's still some big formulas

we're going to have to create.

And if we don't know our order

of operations– or in Excel there's something called

order of precedence, which we don't get to

cover in this class– but here's the complete

list of everything of how Excel calculates formulas. So if you download

this, you can see. You can see that

comparative operators are way down at the bottom. Right? All right. So let's ask this question. All our question is,

is 2 plus 2 times 3 raised to the second

power equal to 144? Or is 2 plus 2 times 3 raised

to the second power equal to 20? Which is it? If we didn't have these

order of operations, people all over the

planet earth would be getting different answers.

Right? All right. I'm going to just take a

guess here, wild guess here. I'm going to say that it's 144. OK, so here's my logic. 2 plus 2 is 4. 4 times 3 is 12. And 12 squared– which

means 12 times 12– is 144. Right? So that's what I think. I'm going to try this. Excel knows the

order of operations. And I got lots of

great questions over the years of people saying,

how come Excel's not working? It's not calculating right. It's really that

the person doesn't know their order of operations,

just like me right here. I don't really know my

order of operations. I'm thinking that the order of

operations is left to right.

But lo and behold, when I

type it in, when I hit Enter, Excel will give you

the right answer. User error here. If I really wanted to

go from left to right, I would have to do something

completely different. Meaning, I'd have to know

my order of operations. All right. So in this case, it's not. The order of operations is

do everything in parentheses first, then all exponents,

then multiplication, division, left to right, and addition

and subtraction left to right. Now watch this. Really, what happens? There's no parentheses,

so we skip on this one. There is an exponent. So what's 3 squared? That means 3 times 3. It's 9. So we get a 9 there. And then we do multiplying next. So 2 times 9 is 18. And then finally, we

do our plus and minus. 18 plus 2 is 20. So sure enough, it did– Excel gave us the right answer. Because Excel knows this

order of operations. Now, sadly, many of Americans

don't know order of operations. So here's the deal. There's only four things.

So go and memorize it. Now, I'm going to copy this. Now, here's something cool. We'll get to see this in a

couple of videos coming up. That little thing

in the corner there is called the fill handle. When you move– this

cursor right here is called the selection cursor, right? But if you move your cursor

right near the fill handle, you see that black crosshair? Bill Gates calls it a crosshair.

I like to call it

an angry rabbit. If you click and drag,

it will copy the formula. All right. Now, I'm going to

change this one. And we're going to make it 144. If you really want

the 2 plus 2 first, that's where the

parentheses come in. I put parentheses right here. that'll force that one. But then I need to

force the multiply next. So I have to put another

set of parentheses. And notice what Excel does. This is cool. It gives you a color coding. I wish that worked when I

drew it on paper like that. So now we have a color coding. The black ones are

on the outside. Well, wait a second. When I hit Enter, that will

show the correct color. Here, I'm going to– here's a keyboard shortcut. Control Enter puts the

formula in the cell and keeps the cell highlighted.

And now I'm going to hit F2. And now you could see– for a moment there, it wasn't

showing the right color. But when I F2 to

put it in edit mode, the green ones are calculated

first, and then the black ones, right? So 4. And then the 4 times 3 is 12. And then the 12

raised to the caret 2, or raised to the

second power, is 144.

Now let's learn how

to evaluate a formula. If you go up to the

Formulas ribbon, formula Auditing, and

then Evaluate Formula. Oops, that's off to the side. Uh oh, my video has been off

the screen this whole time. Formula, Formula Auditing. And you can't see it. It's off the screen. So I'm going to

scoot it over here. Formula, Formula Auditing,

Evaluate Formula. It'll open up this dialog box. And you can click Evaluate. And this will show you

exactly how it calculates 4, and then 4 times 3 is 12,

and then 12 caret 2 is 144. All right. So the lesson here

is, you've got to know your order of operations. So if you don't know

and you're in the class, just four things to memorize. There's our 20 right there. I know that was off the screen. That's too bad. All right. Order of operations. One last topic. Exponents. All right. We talked about this

just a moment ago. If I take 2 raised

to the 6 power– this is the reverse of

the one we did before– the base always means

that's the big thing.

The 6 means repeat– that's the exponent– repeat the

big thing, the base, six times and put a multiplication

symbol in between each one. So six– 2 times 2 times

2 [INAUDIBLE] is 64. Here's an example

of calculations we'll be doing when we get to

cash flow analysis, discounting cash flow analysis, chapters

4, 5, 6, 7, and onward. We'll have formulas like

this, where we have a 1 plus the annual

interest rate divided by the number of compounding

periods per year raised to the compounding

period's time total years. That whole thing we'll

have to calculate. So not only will we

have to do exponents, but we'll have to know

our order of operations.

Exponents. Also in this class, we're

going to have square roots. And square roots–

there's a function in Excel called

square roots, which we'll look at in just a moment. But there's not a

root function where we can do the fifth

root and the sixth root and the seventh root. These two examples are square

roots and how to express them. You can express a square

root as 2 raised to the 1/2. But if it was the

fifth root, you would have to be 2

raised to the 1/5.

And we'll see an example

of that in just a moment. Exponents. When you have a

situation like this, x squared divided

by x squared, you know there's one thing

divided by the other. So that means it's

just 1, right? But in exponential denotation,

you take the 2 on the bottom, and you bring it up to the

top, and put a minus sign in between it. So you get x raised

to the 2 minus 2. So that means any time you see

x to the 0, you know it's 1. So x to the 0 is going to be 1. Again, if you have an x to the

third divided by x to the 3, you could think

about it this way. Cancel, cancel, cancel, cancel. And what's left is an x. But if you do it in

exponent notation, you bring the 2 up here,

and you take 3 minus 2. So 3 minus 2 is x to the 1,

which of course is just x. Finally, x– anytime you see

a negative exponent means you take the big

thing right there, and just put it underneath. Even if it's x raised

to the minus 10, you take the x to the 10 and

put it underneath a division bar and 1 on top.

So it's really the

inverse of whatever it is. So x to the 1, when

you see a negative 1, it means it takes the

inverse of x to the 1. Inverse just means

put a 1 and a slash, and then whatever the

thing is underneath. All right. Let's see some examples here. 2 caret 6. That should be giving us 64. So I'm going to say 2,

and then caret that 6. And we should get 64. Let's go ahead and do

this factor over here. We don't need to know

the meaning of it. We're just practicing

the math of it. All right. Equals, and then 1 plus. And we take our

annual rate divided by our number of periods

per year, close parentheses, and then caret.

And now in math class,

we wrote it like this, and we could get away with it. We could just put the exponent

right next to the parentheses, make it small– a little small

and up like this– and then just write 12 times 30. And everyone knew to

calculate it correctly. But that doesn't work in Excel. And the reason why, is because

the order of operations. If you do– and

multiplication is commutative, which means

you can do it in any order. 12 times 30 is the

same as 30 times 12. But I'll keep it the same here.

12 times 30. The problem here

is that it'll do everything inside of the

parentheses, and then the exponent next. So because multiplication

comes after exponent, and we want it to come first,

we have to go like that. We have to say, hey, no, no, no. Do that multiplication

before the exponent. So really, the last thing

calculated in this formula is that exponent. If we do Formula Evaluator,

I'm going to click Evaluate, and you can see,

it's calculating everything inside that

first parentheses first. There's the plus. It gets that right there. Now it's going to do

everything inside of there. OK. And finally, the

last thing it does is that exponent right there. All right. And exponents. Square root, I mean. And what we want is

the square root of 16. Now, what does square root mean? If you say, what's

the square root of 16? What you're really asking is,

what times itself equals 16? Well, if you know your

multiplication table, you know it's 4 times 4.

Because 4 is the

itself part, right? So what times itself equals 16? So 4 times 4 equals 16. So here's how you do it. You go equals whatever

that cell is, caret, and then we have to, in

parentheses, 1 divided by 2. Right? That's the longhand way. Now, when we're

doing square roots– and in chapter 11 or something,

we have to do square roots– we can use the

square root function. And we'll learn

about that there. And that's just because square

roots are pretty common, right? But if we have to take the

third root of 8, which means– and I wrote it up here– if

you say the third root of 8, third root of 8,

which should be– I don't have it written out.

It should be like this. 8 caret 3. It means what times itself

three times equals 8? Well, 2 times 2 times 2 is 8. So the answer should be 2. But how do you do

a formula for this? The base is 8 caret,

and then in parentheses, 1 divided by– and I'm

going to click on this– 3. All right. And that should tell us 2. And the reason why is

because 2 times 2 equals 8. All right. Exponents. One last topic in this order

of operation and math symbols. We got to talk

about the number 1. The number 1 is a magic number. Anything divided by itself– whoops– anything

divided by itself is 1. 43 divided by 43, 16 divided

by 16, whatever. x squared times y divided by x

squared times y is 1. In this class, we're going to

do financial statement analysis, and we're going to do

lots of ratio analysis, and something called

DuPont analysis.

And we'll see things

like assets divided by assets, which means we're

looking at the balance sheet, and we're going to see

the total asset amount. So what is it? What's assets times assets? Usually we only do numbers. Well, anything divided

by itself is 1. And so in this class, we will

see debt divided by equity, assets divided by net

income, or whatever it is. Sometimes we will see this

strange setup like this. And just realize, it's 1. Anything divided by itself is 1. Look at this. 1 raised to any number is 1. 1 raised to 10,000

or a million is 1. 1 raised to any exponent,

or any root here, equals 1. So 1 has an interesting

characteristic.

We'll see two examples

right now of the use of 1. When we get to

chapter 3, I think it is, we'll do assets

divided by equity, which gives us our leverage. That means how much debt. It's a measure of how

much debt we have. And we'll learn that assets

equals all the debt plus all the equity. So we can have this calculation

right here, D, debt, plus equity, D plus

equity, divided by equity. Well, we can break that apart,

because we're doing addition.

And it has a common denominator. So we can say D over

E plus E over E. And immediately

when you see this, you realize this is a thing

divided by the same thing. So we can convert it to 1. And it's very convenient in

our ratio analysis to do that. But it all stems from

the fact that anything divided by anything is 1. And then, of course, if we

have our debt to equity, we can just add 1,

and immediately we will know our

assets over equity. Another important

example of the number 1– this is for percentage

change formula. And we're going to have lots

of these in this finance class. Let's imagine our stock

on January 1 is $98, and on December 31 it's $102.50. And we want to figure out,

what is the percentage change? Well, we're going to

call the end value– that's the one at the end– end, and the begin value begin.

All right. So the percentage

change formula is– now, this is the long way. And I'll show you both

ways in a video coming up. And this way is a lot longer. But if you know the number

1 trick, we can change this. But here it is. You take the end minus

begin, which just gives us the difference, right? 102.50 minus 98.

That's the change. It went up, right? If it went down, it

would be negative, and it would indicate

a reduction in value. But there it is. That's the change

in the numerator. And you divide it

by the begin amount. So in this case, it

would be 102.50 minus 98, end minus begin, divided by 98. And that would tell us

the percentage change. But look at this. That's a subtraction. And this is a

common denominator. So we can take this and take

end, divide it by begin, minus begin divided by begin. Immediately when we

see that, we say 1. And in statistics

and finance, this is the formula you use for

calculating percentage change. Very rarely do you see this. This is what you usually see. And it's because we know

anything divided by anything is 1.

All right. So that's just a

little bit about 1. We'll see those actual

calculations coming up. In our next video,

we have to talk about the ever elusive and

troubling number formatting. If you know about it, it's easy. And then we'll

talk about percents and a couple other topics. All right. See you next video..