Modernization Hub

Modernization and Improvement
Excel Finance Class 05: Percent, Percent Change / Increase / Decrease Percentage Number Formatting

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.

17 comments on “Excel Finance Class 05: Percent, Percent Change / Increase / Decrease Percentage Number Formatting

  1. 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 šŸ˜‰

  2. 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!

  3. 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.

  4. 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.

  5. 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!.Ā 

  6. 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!!

  7. 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.

  8. 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

  9. 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

  10. 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 šŸ˜€šŸ˜€

Leave a Reply

Your email address will not be published. Required fields are marked *