Actual vs. Budget comparisons in Power Pivot using a scenario table

if you work with the counting or financial data it's quite likely you'll end up at some point comparing actual to budget and possibly throwing out a variance percentage to budget there are several ways to model this in PowerPivot you can have actual and budget as two separate measures or you can have them as members of a dimension or lookup table as you might expect there are pros and cons to doing it either way and this video is going to look at some of those pros and cons so what do I mean by having a dimensional lookup table to list the scenarios well here's the scenario table I've created and Excel to link table so I have my scenarios as actual budget and ever also have something called best-case budget and obviously if you've got a worst case budget it's rather in there as well I'll come back to that in a minute let me show you what data I've got in this workbook I've got some monthly balances think of these as having been downloaded from an ERP or accounting system there are three months January February March of 2014 and these are P&L account items income statement item I've also input a budget for 2014 in a fairly typical budget template format with months across and accounts down on the next tab over I have another budget again 2014 first three months and this is the best-case budget whereby the expectation of revenues is a little bit higher the expectation for expenses is a little bit lower note that while this layout is a very common layout for inputting budgets into a spreadsheet it's not the right format to enter the data into PowerPivot for PowerPivot we want a skinny format a narrow format whereby the months are on rows going down the page not across the columns I was able to convert the budget input data to this format using the power query add-in with Excel 2010 in Excel 2016 it is part of the data tab and it's referred to as get and transform data note that I added a column called scenario with the word budget in it again I did that with power query I also so did a query for my best case budget and in that case I did a column called scenario with best case budget in it finally I appended the one budget to the other on a worksheet called all budgets and again I did that with power query power query is great I don't want to go into more details on that here I also don't want to get hung up on this whole power query and transformation process other than to say that the budget format needs to be in this narrow form and in this particular example where I'm showing a scenario dimension I want to use a particular case where there is more than one budget looking at some of the other tables now we've already looked at the scenario table there is a very simple time table with the three months and I've created a time key off of these just so they can get these to sort in the right order and not come out alphabetically I also have a chart of accounts table here as I said I'm focusing on the income statement items I've got some detailed accounts and then some of these accounts are actually summarized into one heading such as revenues I have a report sign which flips the sign of accounts that naturally have a credit balance where they might be stored as a negative makes it positive so that they show up on reports the flow or balance you can ignore that column and one other thing as you may know budgets are prepared at a more summarized level than actual data is captured at to reflect that in this particular example budgeting has been done at the account group level and there is a table called budget accounts which has those just those group accounts in it let's take a look at the power pivot model the tables in the power pivot model are pretty similar to the Excel tables I've seen reality your account balances will be coming from an accounting or ERP system and wouldn't be pasted into Excel the way I've done it for this example there are some calculated columns these ones in darker green this is a time key which is simply the the year times 100 plus the month number so Jan 2014 gives you 201 4:01 and I use this time key to join two other tables as you'll see in a minute I've also put it in a scenario column which is just actual here's the amount and this number the value is really what ends up on reports as you can see revenues are stored as negatives we want them to show up on a report typically as positives so we multiply the amount by the report sign from the child of accounts which is related to this table the all budget table also has a calculated column for time key otherwise the rest of it's the same the budget accounts table scenario time and chart of account table are the same as the Excel tables let's look at how these tables are related to each other these are the two data tables this one with actual this one with budgets and these are the look-up tables in a data warehousing scenario these would be referred to as fact tables and these as dimension tables each of the fact tables refers to each of the dimension tables so for example the actuals refers to the time table via the time key and the budget table likewise and both of those refer to the scenario table in the case of the chart of accounts table things are a little bit more complicated the actuals are related to the child of accounts via the account in the case of the budget data it's related through the budget account to this field here called budget accounts and these two are connected from account group to budget accounts and in a minute you'll see that enables us to use the budget accounts table to control both the actuals and the budget while we're in the PowerPivot model we'll look at some of these measures I've created in the case of actuals I've created a measure called actuals and it's just the sum of the value column in this table there is a similar one for budgets and if you're wondering why we would want to sum up both budgets together well when we filter them it'll only show one or the other you can create explicit measures on any table you want I actually put mine on the budget accounts table there's a measure that I've called actual versus budget it is actual x' plus all the budget talk about that in a second budget is the sum of all the budgets where the scenario is budget and by that we mean our standard regular budget and best-case budget is the same except the scenario is best-case budget finally there is a variance percent to budget which is basically actuals minus budget divided by budget I'm using the divide function so that if budget is zero it will put in a blank and variance percent to best-case budget is similar so what are the advantages of having your scenarios actual and budget as dimension members rather than as measures if you have them as measures you can't put them on record filters or on slices whereas if they are dimension members you can put your scenario as for example in this case a slicer and then you can choose your actual budget and various flavors of budget if you want to add additional budgets such as a worst case budget it's generally easier to do that by adding a new dimension member than it is to add more measures although you'll likely need to add additional variance calculations for each of your budgets this measure actual versus budget we looked at a few minutes ago and he was defined as all of the actuals plus all of the budgets which didn't seem to make a whole lot of sense but if you think about it you're only ever looking at one piece of that at a time in this case it's actual we can click and select budget best-case budget it's only looking at one piece at a time it doesn't obviously make any sense to add all three together here's an example with actual and budgets going across columns the accounts on rows here are the budget accounts if we substitute instead of that if we use account then we get meaningless numbers in the budget columns because of the way the tables are related I said earlier that we would use the budget account to be common to both the actuals and the budgets and that's what we need to use the difficulty with this type of model is that it's not easy in a pivot table to add the variance percentages if you add a variance percent to budget for example then you end up with a report that looks quite ugly and has some meaningless columns in it however because of the way we've built our model we do have measures for actual and budget so if I go to this report you can see that I want actuals and budget variance for Center budget best-case budget and variance for Center best-case budget these are all measures and they sit nicely side-by-side finally I have a cube formula report that I've created which for the actual and budget uses the scenario dimension but for variance Center budget uses measures and because it's a cube formula report it gives me more flexibility to mix and match my measures with my scenario dimension members so to sum up then if your business model calls for just actual and budget then it makes sense probably to handle those with measures if you have multiple flavors of budget consider using a scenario dimension and cube formula reports also handle scenario dimensions well and it's easy to add your new flavor of budget copy the existing columns and change the headings this video has shown you one way that you can model actual versus budget comparisons in PowerPivot using a scenario dimension or lookup table for more information go to excel craft calm and thank you for watching

Channel: ExcelcraftDotCom

Views: 101,230

Rating: 4.8362203 out of 5

Keywords: Excel, PowerPivot, Power Pivot, PivotTable, cube function, cube formula report, actual vs. budget, perfomance measurement

Id: P5yH2zIHl9g

Length: 10min 44sec (644 seconds)

Published: Fri Jan 29 2016