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.