Welcome to Excel & Business Math video number 21.
Hey, we have to talk about, just like last video, the percent
formulas.
But in this video, we'll use those formulas to solve
business math word problems.
We have two awesome examples.
And there's three fun homework problems
at the end of this workbook for you to practice.
Now let's go remind ourselves of an awesome trick
we learned last video.
I'm going to click on the sheet percent formula memory trick.
Now here's the one, two, three, formulas.
But guess what, I'm going to slide this out of the way.
You actually don't need that if you have this awesome circle
diagram.
Part equals base times rate.
Remember you take the hand and any part
you're solving for, if you're solving for part,
put your hand over the part you're solving for.
And this tells you base times rate.
If you're solving for the rate, put your hand over rate
and instantly you know the formula, part divided by base.
If you're solving for base, no problem.
Use the hand, part divided by rate is the formula.
All right, so let's go over to the sheet Sales.
All right, here is our problem.
The nightly news reported that GE Company has sales this year
that were 87.5 percent of last year's sales.
If last year sales were $5,350,500,000,
what were this year's sales?
Now when I read something like this
I'm tempted to just copy the text
and translate it word for word, not only into the numbers,
but the math symbols.
Let's try that.
I'm going to cheat.
I'm going to come up to sell A4 and in Edit mode
I'm going to highlight sales.
All the way to the end here, Control C Enter.
Come down here paste it, Control V.
And I'm going to break this apart
into the parts of the formula including the math operators.
In Edit mode I double click.
I'm going to highlight just sales this year, Control-c,
and paste it down in cell A13, double click.
That were, highlight that, Control-C, down here, paste--
87.5.
And watch this, that is text right now, Control-C,
but when I paste it into the cell,
Excel will know what to do with it.
It is literally formatting the number as we type.
So when I hit Enter, it interprets it
properly as a percent- of, I'm not even
going to copy that one.
I'm just going to type of tab.
And then over here last year sales.
Copy, Control-C and then Control-V down here.
Now look at this, this year's sales
were some percentage of last year's sales.
Well we already know that this is the rate.
We know that of is multiplying.
And since there's a rate of, we know that's the base.
Without even looking at what the content of it is,
I was able to extract from this the parts of our formula.
That would make were, that's a synonym for is.
And guess what, I can't type an equal sign like that
because Excel will think it's a formula.
So backspace lead apostrophe will force anything
that you put in a cell to become text.
So that way we can put an equal sign.
And then of course this is the part.
I'm going to highlight all these, Control-B, right click--
red font.
Now this year sales, well we don't know
what that is, the actual goal.
Calculator this year sales, so I'm
going to put some question marks here.
That is our equal sign, the rate.
Well that is Equals Up Arrow, Tab.
And last year sales, they give us that.
So we're going to enter that.
Actually, let's do our same trick.
I can copy this, without the comma at the end,
because then Excel wouldn't know that this
is a number, Control-C. And watches this in edit mode.
I'm going to Control-V and Enter.
Now, of course, that means the formula we're going to use,
part equals rate times base.
Rate equals base equals and the part equals.
I'm going to put the actual amount down here,
so there's that 87.5.
The base-- I'm going to bring that down with the formula.
I want to see it all down in my solving area.
This will be the formula.
I'm going to add some fill and some border.
And that means the part, which is this year's sales,
equals the base times the rate.
And you know GE's not too happy.
If this is last year's sales, and we're
trying to get to this year sales,
and the percentage is less than 100%,
that means this number is going to be smaller.
The part is smaller than the base.
This is actually considered a decrease problem.
We have some starting point last year's sales,
and here's the ending point, this year's sales.
So when I hit Enter, they're bummed out.
Only $4.7 billion instead of last year's
$5.3 or $5.4 billion.
You can think of this rate--
you can think of it as number of pennies for this year sales
compared to one dollar of last year's sales.
Now I'm going to Control-Enter, increase the column width,
above to Home, Wrap Text.
So we're given this percentage.
And the statement tells us the relationship,
this year's to last year's, and there's the percentages.
Remember our ratio trick.
Leave a 1 in the denominator.
That's what we did down here.
So even though we're given this as a percentage,
we can interpret it from how it's written.
That's 87.5 pennies of sales this year right there
for every $1 last year--
last year sales.
Now we can check.
We calculated the part.
We can either use the formula for rate or for base.
I'm going to come over here and check.
I want to use the part.
I'm going to choose the formula, solve
for base part divided by rate.
I'm going to Right-Click Format Painter and click.
Equals, there's the part we just calculated,
divided by the rate.
That better give us, when we hit Enter, $5 billion, 350 million,
$500,000.
Check.
And remember our trick for putting a check mark.
Totally unnecessary, but kind of cool.
Insert, over to Symbol, and you have to look through.
We found it in Arial last time we used it.
I have in my recent.
Click Insert.
Click Close.
Now we can create a written statement.
This year's sales are $4,681,687,500 and Enter.
If you want to get fancy, you could write,
given the Nightly News GE figures,
this year's sales were 87.55 of last year's.
We calculated this year's sales to be $4,681,000,000 and so on.
All right.
So that's example number one.
Let's go on to example number two.
We want to go over to the sheet's stock market.
Our problem reads, the Nightly News
reported that the stock market rose by $78.25.
If the stock market started at $26,083,
what was the fractional percentage increase in tenths?
All right.
Let's list our facts.
It looks like if it rose by that much.
and that's the starting point, that would be the base
and this is the part.
I'm going to start off part equals amount
that stock market rose, Tab, and I'm
going to format as I type. $78.25 and Enter.
Base-- that's where the stock market started.
$26,083 and Enter.
Rate, that's what we're solving for.
We're trying to get the fractional percentage
increase--
Tab.
I'm going to put question question.
For formula, well, let's see.
We have part and base.
If we think about our hand, we simply cover up the rate
and we're left with part divided by base.
Rate equals part divided by base.
And it looks like we have a requirement.
And enter.
Our goal-- I'm going to cheat.
I'm going to come up here.
It says, what was the fractional percentage increase in tenths.
Control-C. Right here I'm going to Control-V. Maybe
add a capital W.
Now I want to relist some of this information down here.
I should have shown this before.
This is a great trick.
Watch this.
I can click on this cell right there.
It's not a number.
It's some text.
Control-Enter and I can copy it to the side.
Look at that.
And I can copy it down.
And I have my two bits of information.
Over here I could say, hey, $78.25 Enter,
equals that $26.83 and Enter.
I can do the same thing.
I actually should have copied it straight down.
Watch.
Highlight, boop.
Remember, that's really what?
F2, a relative cell reference.
So I literally pull down all of my labels
down into my calculation area.
The advantage of that too, is if I come up and change this--
let's double click rows and type Increase and Enter.
Look at that.
Everything is consistent now.
All right.
So our formula-- we're calculating rate
equals the part divided by the base.
When I hit Enter, I get a decimal.
Now remember our trick.
That second zero right there is the penny position,
or the 1% position.
So if it was formatted as a percent,
that three would be the tenths.
Now actually this label is wrong.
Watch this.
Here's another trick.
We learned about Angry Rabbit for copying.
We learned about selection.
But if you point in that cursor right there is the move cursor.
I'm going to click and drag.
So I point it right to the edge with that cursor
to move it down.
This should really be decimal increase.
I'm going to type rounded to a tenth of a percent, Tab.
Now we can preformat this with our percentage right there.
It doesn't show any preview because there's nothing there,
but this will always format with two decimals.
Now I'm simply going to equals Up Arrow and Enter.
All right.
So it looks like if we were to increase the decimals a bunch,
we can see those extra decimals, but how do we round?
Pick the position we want to round to.
That's the tenth of a percent.
Look to the right.
It's 4 less, so we hack everything off and leave it
as a three.
Oh, but wait a second.
We don't have to do it manually.
We can get the round function to do it.
Remember if we're looking at this, we would think it's one,
but we got to look at the underlined number
one, two, three.
So ready, F2.
This would be Round, Tab, and comma.
The number of digits is three.
Close parentheses.
Control, Enter.
Now we have to ultimately equals Up Arrow, Control, Enter.
It pulled the formatting.
Control-1.
We want to definitely come down to Custom
and do our fractional percentage.
Question mark, slash, and it's in tenths,
so I'm going to type in the denominator a tenth,
and then percentage symbol to slide 2 to the right.
Now I click OK and there our 3/10 of a percent.
Now just out of curiosity notice we have the stock market start
and the amount of the increase.
We could actually calculate stock market end value.
That would be Alt Equals for sum.
That's totally wrong, but no problem.
Dancing ants are dancing so I simply redirect it.
Add those two together and Enter.
Now it didn't ask for that, but we calculated it anyway.
All right.
So with our unrounded rate, solve for rate.
So we can pick either base or part
to check our rate calculation.
I'm going to take the base and multiply it by the rate,
and hopefully I'll get that $78.25.
Part equals base times rate, Tab, equals.
There's the rate, the unrounded one times there's the base.
When I Control-Enter I better get $78.25 and sure enough
I do.
Now I can come down and write my answer in words.
The stock market rose by 3/10 of a percent and Enter.
All right.
That was a little fun with percent formula formulas
and business word problems.
There's one, two, three homework problems and the answers.
In this video, we saw how to solve for rate
by taking part and dividing by base using all five
steps for our word problems.
Over on sales, we saw how to take part divided by rate
and use our five word problem steps
to solve a business problem.
And don't forget, if you liked that video,
be sure to click that thumbs up, leave a comment, and sub,
because there's always lots more videos to come from Excel
is Fun, including next video, video number 22.
We'll see how to create a common sized financial statement where
we use the rate formula in a really cool Excel trick
to create many formulas quickly.
All right we'll see you next video.