## Excel Finance Class 05: Percent, Percent Change / Increase / Decrease Percentage Number Formatting

Welcome to Excel and

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

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

click on my [INAUDIBLE] link, and you can download the

workbook Finance and Excel Chapter 00. If you’re in the class, just

go to our class website. In this video, we got to

talk about percent number format, percent number format,

percent, and percent change. Now, one of the number

one mistakes that people make on the planet Earth in

Excel is with this button right here. Either in– I’m in 2010. 2007, it’s in the number group. In earlier versions, it was

on the Formatting toolbar. But here’s what they do. They come over here, and

I’m going to actually type a third number here. I’m going to use– and decrease the column

width here a little bit. So I’m going to do 0.0144. These are decimals. Now, if you were going to

convert this to a percentage by hand, you do two things. The first thing is you

multiply it by 100. So 0.02 times 100

is the number 2. And the second thing you do

is you add a percent symbol. Now, this is the actual number

that calculations are based on. When we add the

percentage number format, that’s just like a

symbolic formatting, like bold or any other

kind of formatting. So let’s just see how it works. This is when most

people– they go, boop, and they’re totally happy. They get 2%. Notice, unlike

our earlier number formatting we talked

about in our last video, it doesn’t show you that

number in the cell, which is part of the reason this

gets people into trouble. We saw earlier with currency,

we could see the currency on the face of the spreadsheet. But in the formula bar, we

could see the unrounded number. But here, look what it does. Let’s go ahead and

try this one, and this is where people get in trouble. They click this, and

sure enough, they see 1%. Now, if you’re smart,

you’ll look up here and you go, oh, it’s not,

it’s actually rounding, but a lot of people just–

they don’t look up here, and they see that, and

they’re freaking out because it was supposed to

be 1.44, or, even worse, they go make all

their calculations and they think that it is 1%. Well, the solution to this

is to increase the decimals. We saw how we added

this button here. Another big mistake is people

actually type the number two in the cell. Well, the number one is 100%. So the number two is 200%. Again, those are

really common mistakes. Now, this is an example of type

the number first, then format. Worked fine here. Worked fine here only after

we increased our decimals. Didn’t work at all here because

what we really wanted was 2%. Now, here’s another

way to do it. You can highlight the

cells and format upfront. Now, I’m going to use Control,

and I basically never use this button right here– I guess sometimes and then

I use this– but Control-1 and you can use the

percentage number format. And then you could increase it

to whatever decimals you want– I guess. OK, so we have that

formatting there. Now, watch what happens. When you format it

first, if I type a two, immediately that

percentage symbol pops up because

it’s pre-formatted. But watch this. This is so weird. I don’t know why

they program it. If you type 0.02, I don’t

see the percentage symbol, so I’m not clued in

that it’s pre-formatted. But when I hit Tab or

Enter, it formats it as 2%. So when you format it

first, you can either type a two or a 0.02. Now, format as you

type is convenient. Sometimes you have

just one interest rate. So you could type 2.00

and then a percent symbol. As soon as you type that in– it’s called formatting

as you type– it will apply the

percentage number format. You can prove it to

yourself by Control-1, and you can see that

it says percentage. So you could type 1.596%. And sure enough, look at that. So formatting as you type– actually, we can

increase it like that. It’ not going to do anything. So we actually have to come up

here and increase the decimals. Percentage number format–

type in the number first and then formatting,

formatting it first, and format as you type. All right, that’s the

percentage number format. Now let’s talk about

percents and percent change. Now, I’m going to

right-click minimize this so we can see this. Percent is just a simple

part divided by the total. So here it is, and when

we get to chapter 2 we’ll do common size balance

sheets and income statements. And one of the

calculations we’ll do is we’ll compare cost of

goods sold to total revenue. So we’ll need to take

455, which is the part, and divide it by

the total revenue. Now, we’re comparing

the part to the whole. Really, think about it. Total revenue is everything. All the expenses are listed,

and then the final thing is our net income or net loss. So we need to make

this division. Cost of goods sold

divided by total revenue equals this divided by this. Now, what we’re doing is

we’re comparing the part to the total, and

so it’ll give us– it’s an easy one. We could probably do this on

our head, or approximately, because this is 1,000– so 0.45. Really, in chapter

3, we’ll learn that for every $1 sold, that $1

coming in, 45.5 cents of that goes to the cost of the item. So that is percent, and that’s

how we calculate percent. Well, wait a second,

this is a decimal. Well, Control-1– and either

way you want it– sometimes it’s appropriate to have the

percentages– but number, percent, two, or

whatever you want. Click OK. All right, now,

percentage change– this is where you have a

starting point and an ending point. And a couple videos

ago, we saw how to derive this

formula right here. And this is the formula we’re

going to use in this class– end divided by begin minus 1. Now, I’m going to do it

both ways this first time. The change is the

end minus the begin. And so here, we start it at 95. We went up to 98.75. So the change would be

the end minus the begin. Well, that gives us the

change, and we compare it to the begin amount,

and that says how much we went up or down. In this case, we went up. So here’s the long formula,

and you gotta open parentheses because you gotta

force end minus begin. That’s a subtraction. We have to force

that before division. The way we’re going to do it in

this class– every time we’re doing percentage change, we’re

always going to say, oh yeah, end divided by begin minus 1. Now, we could leave

it like that or we can go ahead and format it– Control-1. And I’m going to just

do a couple, maybe four. So there we have our

percentage change, percent, and percent number format. Ah, but let’s go ahead

and talk– that’s 1, 2, 3. There’s still two more topics. We want to talk about

percent increase. This just percentage

change, meaning we already have the end, the

begin, and the end value, and we just want to figure

out what percentage change up or down it went. But what if you want to

start with a given number and estimate what the final

number is given a 10% increase? Well, let’s think about this. We have $100. What’s 100 times 10%? Well, it’s $10. Well, then we take that

10, bring it down here. 100 plus 10 is 110. So our goal is to have a formula

here that will give us the 110 quickly. I don’t want to do

two-step calculations. Well, let’s think about this. This right here, 100 times

10%, is equivalent to the 10. So instead of doing this right

here and having that 10 there, let’s just take this whole

thing and bring it down here. So we get 100 plus

100 times 10%. Well, we can rewrite this. Is it not true that 100 is

the same as 100 times 1? Sure. And then we have this here– 100 times 10%. This is the whole

amount, remember, because our goal is

to go from some amount to the total amount

with the increase. So this will give us

the original amount, original amount times 1,

plus the original amount times the percentage

increase or decrease. Well, you could do

something called factoring. 100, 100– that’s the

distributive theory backwards. But factoring means 100, 100. Each one are multiplied

by some number. You can literally take it

out and put it in front– so there it’s sitting alone– and then times and

put in parentheses, and you leave the two little

numbers it was multiplied by. So there you go. That is usually how we

will do it in a formula. However, in this textbook and

in lots of finance accounting classes, they won’t

even give you that. They’ll just say, OK, well,

you should know that once it’s like that you add to get

1.1, and it’s simply that. So in our textbook,

you’ll see this. Mostly when we’re

doing formulas, we’ll have to do that. But that is the kind of math

behind why we can get away with a formula like this. So let’s go ahead and

see how to do this. Equals– now, notice, our

assumption was given as 1.1. So we just say this times this. This is the percentage

change that we calculated in this

example– percent change. But here, when we’re

going from some amount to the end amount

plus the increase, we need to have the 1 and

the percentage change. And again, it’s oftentimes

going to be written just as 1.1. And you should know that

that means an increase. Now, what about a decrease? No problem. What’s a 10% decrease? What’s 1– instead of doing

this right here, 1 plus 10%, we do 1 minus 10%

or 1 minus 0.1. You get 0.9. So a lot of times

when it’s a decrease, you just see that,

that right there, 0.9. So we go equals that times this. And that means we

go down to $90. So percent decrease– when we’re

going from the total amount to total amount– percent

increase, total amount to total amount, percent

is change, percent, and percent number format. We have two more videos

here, cell reference and stylistic formatting. We’ll see you next video.

Great series thus far Mike…

More coming out today or tomorrow, and then still more to come!

So far the episodes covered very basic concepts. However I found some really interesting ideas in them. I wish, I had teacher as You on my studies. You are so passionate in excel :D.

Greetings from Poland š

Everything in the class is basic, however, by the time I finish making this series, we will cover a lot of topics!

I am glad that you like the videos!

I'm just wondering you have Video about "how to created" the W2 form.One of my classmate told me about this since I watch your Video I love it, know they way you explanation is more easy to understand how to use all formula. Please Let me know. Thank you and have a Wonderful and "happy Holy week". God bless you.

I am sorry, but i do not have a video about the W2.

thank your so much, and have a wonderful day. God bless you

Sounds like Ross from friends

You rock man!!!!!!!! I appreciated so much your kindness by sharing your knowledge with us. I'm accountant myself and I'm telling you this is great buddy!!!!!!! My first language is Spanish but watching your videos I'm also improving my English indeed. So, I planning to go throughout all your videos. Again, Thanks a lot and I won't keep this just for me but for my kids.

I watch one of your videos each morning. You're a great teacher–thanks!

Your class is excellent. I've learned a lot.

Thanks for your good work! I have learned alot! One question though, why when i type two, it pops up at 200% instead? I have already pre-formatted the cell as percentage. Thanks!.Ā

I have no idea how this all works and I definitely have to learn it. Can someone please tell me or help me answer this problem. What annual rate is earned on a $1,000 investment when it grows to $2,400 in eight years. I need to know how to solve this in excel or how do I go about solving it. Thanks Guys!!

How do i change expenses by choosing different %. for example the expenses are 2000 and occupy 20% of total available expenses and i need to be able to change the % and the expenses increase or decrease.

Without a compliment, your videos for me are like movies, when I start can't stop

and now I have time to watch them at work because of technics I learned to do my job rapidly

Great!, I studied business and I love accounting but to be efficient I see that the best thing is to be good at excel, this series fit my need

And the cool things answer the questions, thanks Mike

Really I didn't knew that percentages are magically awesome

I am glad to know the methods

It is becoming much more exciting for me to go further

Thumbs š up sir

Loads of love šš