Welcome to Excel and Business Math, video number 25.
Hey in this video, we got to see how to use our percentage
formulas that we've been learning about from videos 18
to 24, to help us calculate a budgeted
forecast for next year.
Now we're going to see the same income statement example
that we saw in video number 22.
But what we're going to do here, is
we're going to take the numbers and use one, two, three,
these three percentage formulas, to help
us estimate a budget, or a forecast, for next year.
Now let's go over to the sheet, CAT forecast.
Now, what do we have on this sheet?
Caterpillar has all of the numbers for the year 2015.
These are the actual numbers.
And we want to use these as an estimate
to figure out what we think our numbers will be for next year.
Now the way we get to do this, or step one of our one,
two, three different steps, is that we
want to compare each one of the expense accounts
to the revenue.
Now we saw this back in video number 22,
this is called a common size income statement.
In that example, we talked about comparing each one
of these parts, to the whole.
And using those percentages to compare between time periods,
and between companies.
But here we're going to use the same exact formula, the rate
formula - part divided by base, to estimate for each expense,
how many pennies for every $1 that comes into the company,
should go to each one of these expense categories.
Now as we talked about back in video number 22,
these are, in essence, consolidated expense accounts.
The company would do this process,
meaning these formulas, here, internally.
And they would have all of the different expense categories.
But the math behind it for this smaller example,
is exactly the same.
All right, so we're going to try our formula.
There's the revenue for the year,
and we're going to compare that number and all
of the other expenses, total expenses, and even our profit,
to the total revenue.
So you're ready?
Equals: Arrow-over, that's the part, divided by Arrow-Arrow.
And I'm going to hit the F4 key, because that's locked.
That's the total, or the base that each one
of the individual parts will be compared to.
Control-Enter 1, because of course, any number divided
by itself, is 1.
But when I copy it down, go to the last cell,
hit F2 to verify the cell references,
Enter and then Delete this one, right here.
Now I have each expense account as a percentage, or a decimal,
of the revenue.
Now really, what this means, and the way
we want to think about this is, if I
do this division, F2, notice, there's the part.
There's the whole.
When I do that division, that means
in the numerator, the top of the division, I get 14.8 pennies.
In the denominator, there's $1 of revenue.
A great way to think of these is,
for every $1 that comes into the cash register for CAT,
how many pennies went to each item?
We'll notice, down here, 5.3-about pennies went
to profit, but the rest of those pennies for that $1,
went to running the business.
Now, guess what?
We're going to use these to estimate next year.
Now, we're going to use the unrounded numbers
for our calculations.
But I do want to add percentage-number formatting.
So I'm going to highlight the column, come up to the Dropdown
and use percentage with two decimals.
Now when I apply that, of course,
we all know by this time in this class,
that that just displays it a certain way.
Those unrounded decimals are still underneath,
and will be used in our calculations.
Now before we can estimate our expenses
using our new percentages, we need
to estimate the revenue for this year.
Now the company went out and estimated
what they think the percentage increase in revenue will be.
This is based on projected demand for their bulldozers
and excavators.
So down here we're going to use the formula
to calculate the end.
That means this is the begin revenue, last year's.
This is the end.
That's the percentage increase.
So we can use 1 plus our rate of change,
there it is, to get our rate.
Equals, and I'm going to click on total revenue
from last year, times, in parentheses, 1 plus.
There is the rate of change.
When I add 1, that's the rate.
And that's what we use to get the end amount.
Begin times rate.
So that will be our total revenue
that we're going to use for all of these calculations.
Remember, what does that mean?
Fourteen point seventy pennies, for every $1 of revenue.
But now we're going to use the revenue from here.
This year's revenue, and we're going to need to lock it,
so as we copy down, every cell looks at this year's revenue.
So I hit the F4 key times, and now I'm going to use how many
pennies for this particular expense,
for every $1 of revenue.
And we're not going to round, formatting
will be sufficient to display down to the dollar,
in our case.
Control-Enter, and watch what happens when I copy this down.
It wrecked the formatting.
Any time you copy, it copies the formatting and the formula.
No problem.
We love this little smart tag, I hover my cursor and click
that Arrow, there, to get my Drop-down.
And I want to say, fill without formatting.
That is beautiful.
Now I want to go to the last cell,
and hit F2 to verify that the cell references are correct.
That's how many pennies per $1 of net income we had last year.
And when we multiply it by our total revenue for this year,
we get our estimate for net income.
All right, so we used our rate formula
to calculate expenses as a percentage of revenue.
Our end formula to calculate the revenue for this year.
And our part, rate times base, to calculate the estimated
expenses for this year.
All right, now let's go look at another example
over on Amazon forecast.
This is the same income statement
we use back in video 22, and create our formula.
Equal sign, there's the numerator,
the part divided by the whole, or the total,
that's the denominator.
I hit the F4 key, Control-Enter, I already
had percentage-number formatting there, and copy it down.
Now I have how many pennies for that particular expense,
for every $1 of revenue?
Now we can calculate the revenue for 2016.
Equals, there's our begin, times, in parentheses, 1 plus.
And look at that, Amazon assumed that revenue
is going to increase by 25%.
Close parentheses, and Enter.
So there's the revenue that we can now use for this year.
Equals, there's the base for this year,
total revenue, F4 to lock it, because we
need that locked all the way down, times,
and there's cost of goods sold, expense
as a percentage of revenue, as a relative cell reference,
Control-Enter.
Copy it down.
Point to the smart tag, fill without formatting,
go to the last cell and hit F2, I'm
verifying that the cell references are working.
Now I'm going to come back over here,
and F2, I didn't verify this.
Sure enough, that's correct.
And I didn't delete this, I don't want that, Delete.
All right, that was how to use our rate, end,
and part formulas to create a budgeted
forecast for next year.
You have one homework problem, it's a Microsoft income
statement, and you're going to forecast the next year's income
statement.
All right, if you're like 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
it's fun, including a quick, short, little hint.
Video 26, about percentage increase and decrease.
All right, we'll see you next video.