[MUSIC PLAYING]
Welcome to MSPTDA video number 25.
Yes, Microsoft Power Tools for Data Analysis.
And we need to talk about how to compare budget and actual.
And in this video 25, we're going
to see how to use an Excel formula to create our solution
using sumifs and end of month.
And then in the next one, 26, we'll
see how to do it with DAX and the data model.
Now, you can download this file in the link below the video.
Now, for this budget example, the business
creates a budgeted number for a particular product
for the month.
That means we have two attributes,
the month end product and the grain is at the month level.
This is our budget number.
But over here we have a transactional table.
This is the product and the particular transactional date.
So the grain of this is at the transaction level.
Over here, it's at the month level.
No problem.
In our actual sales, we can pull an add using
the sumifs function, based on product,
looking through this column, and the date.
Now we're given for each row in this table
the end of the month.
So inside our formula, while we have the end of the month,
that will be the upper limit.
But we'll have to derive inside of our formula
the first of the month.
That will be the lower limit.
Those two conditions will be used on this column.
With those three conditions and the sumifs function,
we can calculate actual sales.
Then we can compare budget and actual.
Equal sumifs, the sum range, if I
click in the top cell, Control Shift
down arrow, F4, that locks the range, because we're copying it
and jumps back to the active cell.
Now, I'm looking at the screen tip.
Comma, criteria, range 1.
We have 1, 2, 3 different ranges we need to put in.
The first one we're going to put in is product.
Control Shift down arrow F4 comma.
Criteria 1, well, that means we have
to match up whatever the condition for this row
is to find it in this column.
That's a relative cell reference.
Now I type a comma.
Criteria range 2, well, guess what?
We have an upper and lower limit we
need to compare for each row in this table
to pull the right sales and then add.
So we are going to have to repeat the day column twice,
Control Shift down arrow, F4.
Now, comma, the first condition is hey,
dates in that column right there, how many of you
are greater than or equal to the lower limit?
Now comparative operators and sumifs
have to be put in double quotes, greater than or equal to,
two different symbols, and double quote.
Then we have to join it to the actual lower limit date.
That's the first of the month.
Ampersand, shift 7, that's our joint operator.
Now I can't just click there because that's
the end of the month.
But guess what?
I'm going to use the end of the month function
to look at that date, comma, and for months I say, minus 1.
That means for this row right here.
It will see December 31, 2018.
When it gets down to the next row,
it will see, January 31, 2019.
Close parentheses.
Now, wait a second.
We don't want to include the last day
from last month, 12-31-2018, so I'm
getting rid of the equal sign.
Now that's the lower limit.
I come to the end comma.
I repeat the date column, watch this, I'm going to cheat.
I'm going to come over to criteria
range 2, click, Control C to copy, click on criteria range
3 control v comma.
Now I see criteria 3.
This is the upper limit.
So in double quotes, I have to say, less than
or equal to end double quotes, ampersand.
And now I simply click as a relative cell reference
on end of the month.
And that formula will work.
To go and summarize from our transactional table
at the day product grain and summarize it
at the correct grain, the month product level.
Close parentheses control enter.
Now we can double click and send that formula down.
Control down arrow to jump to the last cell,
and I'm going to hit F2 just to verify that all the cell
references are pointing in the correct place.
And they are.
Escape Control Home to jump to cell A1,
now we can calculate our variance.
Equal sign left arrow, that's the actual amount of the sales.
Minus left arrow left arrow, that's
how much we budgeted for.
In this case, it will be positive
because the sales were greater than the budgeted sales.
Control enter and double click and send it down.
Sometimes we're below target, 260,000 is less than 266,000.
Sometimes were above.
Now, percentage variance equals, left
arrow, that's the amount of the change divided
by the actual budgeted amount.
Now, I control enter.
Double click and send it down.
If you want to format this as a percent,
I am going to use my drop down and say, hey,
percentage with two decimals.
And there we have our percent variance actual to budget.
We went from a transactional level
table, or a transactional grain, summarized the values
and compared to our product month level or grain.
Now, this video we used sumifs and end of month
in an Excel worksheet formula.
Next video, we'll see how to bring these tables
into the PowerPivot, or Power BI data model,
build relationships and the correct measures
to compare in a pivot table, budget, and actual.
All right, if you liked that video,
be sure to click that thumbs up.
Leave a comment, and subscribe because there's always
lots more videos to come from excelisfun.
All right, we'll see you next video.