Learn Excel – Budget versus Actual – Podcast 2016

Learn Excel – Budget versus Actual – Podcast 2016

Learn Excel from MrExcel podcast, episode
2016 – Top-Down Budget vs Bottom-Up Actuals! Hey, I’m podcasting this entire book, click
that “i” on the top-right hand corner and follow the playlist. Hey, I’m going to interrupt this, this is
Bill Jelen from 15 minutes from now. I realize now this is an incredibly long podcast,
and you’re tempted just to click right through it, but let me just give you this short of
it. If you’re in Excel 2013, and you have ever
had a small budget table and a massive actuals table, and you need to map them together,
this is an amazing new ability that we have in Excel 2013, that not many people have explained,
and you probably don’t know about it. If this is you, you’re in 2013, and you need
to map these two data sets, take the time, maybe today, maybe tomorrow, maybe add it
to the watch list, it’s worth it, it’s an amazing technique. Alright, here’s what we have, on the left-hand
side we have a budget, this budget, it’s done at the top-level, top-down, right for each
product line, for each region, for each month, there’s a budget. Not many records here, count of 55, on the
right-hand side we’re trying to compare this to actuals. The actuals are coming from an invoice register,
so we have Region, Product, and Revenue, but they’re individual invoices, a lot more
data here, we’re already halfway through the year, and I already have 423 records. Alright, so how do you map these 55 to these
423? Might be tough to do with VLOOKUP, you’d have
to summarize first, but thankfully in Excel 2013, the data model makes this really, really
easy. What we need to allow this big massive table
to communicate with this tiny table are intermediaries, I call them joiners. Tiny little tables, Product, Region, and Calendar,
we’re going to join the budget to these three tables, we’re going to join the actual to
these three tables, and miraculously the Pivot table will work. Alright, so here’s how we do that. First off I need to create the joiners, so
I take this Product field from column A, and I copy it over to column F, and then Data,
Remove Duplicates, click OK, and we’re left with a tiny little table, 1 heading 3 rows. Same thing for Region, take the regions, Ctrl+C,
go over to column G, Paste, Remove Duplicates, click OK, 3 rows 1 header, alright. Now for the dates, the dates are not the same,
these are month ending dates, they’re actually stored as month ending dates, and these are
weekdays. I’m going to take both lists, Ctrl+C the second
list and paste it here, Ctrl+V, then I’m going to take the shorter list, copy that, and paste
it down below, alright. And it’s really annoying that, even though
these are stored as dates, they’re appearing as months, and Remove Duplicates won’t see
them as the same. So before I use Remove Duplicates, I have
to change it to a short date. Choose that data, Data, Remove Duplicates,
click OK, and then a little sort here to get it to work. Alright, now I don’t want to report by daily
date, so I’m going to add a column here, a lookup column that says Month, and this will
be equal EOMONTH that date, ,0, which will get us out to the end of the month. It will format that as short date, and copy
that down, alright. Now, we need to make each of these into a
Ctrl+T table, so from here Ctrl+T, My table has headers, beautiful. The small ones, it doesn’t realize those are
headers up there, so we have to make sure to check mark
that and Ctrl+T, alright, and they call these tables Table1, Table2, Table3, really boring
names, right? So I’m going to rename these and call it the
BudTable, the ProdTable, RegTable, my CalTable, and then the ActTable, alright. We start from the very first table, and by
the way we’re not going to use PowerPivot today, we’re going to do all this with the
data model. So, Excel 2013 or newer, you have this Insert,
PivotTable, we’re going to check the box for “Add this data to the Data Model”, click
OK, and we get our field list with the magic All button, that lets me choose from all five
tables in the workbook, Actual, Budget, Calendar, Product, Region. Alright, so the numbers are going to come
from the Budget table, I’ll put the budget in there, and from the Actual table I’ll put
the actual in there, but then here’s the thing for the rest of the Pivot table. Any other text fields that we’re going to
put in the row area or the column area or as slicers, they have to come from the joiners,
they have to come from those tables between the tables. Alright, so from the Calendar table we’ll
take that Month field and put it across the top, we’re going to ignore other relationships
right now. I will be creating the relationships, but
I want to create them all at once. And the Region table, put the regions down
the side. I could put products down the side, but I’m
actually going to use the Product table as a slicer, so Analyze, Insert Slicer, again
you have to go to All if you haven’t used the Product table yet. So go to All, and you’ll see that the Product
is available to create as slicer from the products, like that. Alright now, at this point we haven’t created
relationships, so all of these numbers are wrong. And the relationships we have to create, we
have to create 3 tables from this little budget table, one to the products, one to regions,
one to calendar, that’s 3 relationships. And then we have to create relationships from
the Actual table to the Product region in Calendar, so a total of 6 tables. And yes, this would definitely be easier if
we had PowerPivot, but we don’t or let’s assume we don’t. And so I’m going to use the old-fashioned
way, the Create dialogue here, where we have the Budget table on the left, and we’re going
to use the Region field and relate that to the Region table, the Region field. Alright, 1/6 are created. I’ll choose Create, again from the Budget
table we go to the Product, and then link that to the Product table, to the Product,
click OK. From the Budget table the Date field, we go
to the Calendar table, and the Fate field, click OK, we’re halfway there, alright. From the Actuals table, we go Region, to the
Region table, click OK, from the Actuals table to the Product, and from the Actuals table
to the Calendar. I’m actually going to take the Values and
make it go down the side, alright. Design, Report Layout, Show in Tabular Form
to get a view that I prefer, Repeat All Item Labels, alright, this is absolutely amazing! Now we have this tiny little table, a 50-some
records in this table of hundreds of records, and we’ve created a single Pivot table thanks
to the Data Model. For each where we can see the Budget, we can
see the Revenue, it’s broken out by Region, it’s broken out by Month, and it’s sliceable
by Product. Now this concept came to me from Rob Collie
who runs Power Pivot Pro, and Rob has created a lot of books out there, his latest one is
“Power Pivot and Power BI”. I think this one was actually in the “Power
Pivot Alchemy” book, it’s the one that I saw this and I said “Well this, even though
I don’t have millions of rows to report through Power Pivot, this is one that would have made
a HUGE difference in my life, having two data sets of mismatched sizes, and needing to report
from both of them.” Well this example and many others are in this
book, I will eventually get the entire book podcast, that looks like it’s going to take
two and a half months. But you can get the whole book today, same
time, go there, buy the book, $10 for the e-book, $25 for the print book, and you can
have all those tips at once. Alright, a really long episode here: we have
a small top-down Budget and a bottoms up Actual, they’re different sizes, but using the data
model in Excel 2013… And by the way if you’re in 2010, you could,
in theory, do this by getting the Power Pivot add-in, and go through all these steps back
in 2010. Make both datasets into a Ctrl+T table, and
then join your tables for anything you want to report on, in the row label, or the column
label, or the slicers, so copy those values over and Remove Duplicates for the dates. I actually took values from both tables, because
there were some unique values in each, and then I used the EOMONTH to get out there,
make those joiner tables be controlled tables. It’s optional, but I named all 5 tables, because
easier when you’re setting up those relationships, rather than having be called Table1, Table2,
Table3. And so, start from the Budget table, Insert,
PivotTable, check the box for Data Model, and then building a Pivot table using Budget
and Actual. Everything else comes from the joiner tables,
so Region and Month in the row and column area, slicers came from the Product table. And then we had to create 3 relationships
from the Budget to the joiners, 3 relationships from the Actual to the joiners, and we have
an amazing Pivot table. Now tomorrow we’ll take a look at using the
Power Pivot tab and creating some additional calculations. So all of this is possible, it’s when we want
to insert a calculated field, that’s when you have to pay the extra $2 a month to get
the Pro Plus version of Office 365. Well hey, thanks to Rob Collie from Power
Pivot Pro for this tip, and thanks to you for stopping by, we’ll see you next time for
another netcast from MrExcel!

12 thoughts on “Learn Excel – Budget versus Actual – Podcast 2016

  1. I used to do something similar by making two pivot tables, a combined column with the keys from both without duplicates and then VLOOPUP from the Pivottables. I think you are able to do more analyzes with the result. I need to add filter or pivottables on top of the result.

  2. It is truly amazing what the new "Power" tools in Excel can do!!! They all make Excel even more important than ever!!! Thanks for the Budget Actual video!!!!

  3. Cool feature. However in your particular example, I would think it would be a lot easier to do two much shorter steps.

    1) Add a column to your actuals table with a formula that returns time in the same format as budget (i.e. "March 2018" instead of 3/8/2018).

    2) Add a column to your budget data that performs a SumIF's to the actuals table based on the same parameters of Product/Region/Date (as a month)/Sales. Your budget table will now function as a single data table that would dynamically update with actuals and budget.

    Regardless, keep up the good work Bill. I enjoy your work.

Leave a Reply

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