How to create Budget vs Actuals report in Excel

Budget vs actuals report is a very common need. It could also be target vs actuals or forecast vs actuals. The concept is same. We have two pieces of data. One type is the budget information – which is at product, group, region, location, country or some other level. The actuals information comes from transactional systems and has lots of data. For each item in the budget, there will be multiple rows in the actuals table. The video below will show you how to combine these together and build a dynamic Variance (Budget and Actuals comparison) report. This is a 7 minute YouTube Video.

https://youtu.be/KSlPbt244e8

Budget vs Actuals video – table of contents

1. Introduction – 00:00
2. Import data into data model – 01:17
3. Open data model – 02:46
4. Create relationship between budget and actual data – 03:07
5. Create pivot table from data model – 03:43
6. Create a measure (calculation) for variance or difference – 04:27

Summary

Import the budget and actuals data as separate tables into Data Model.

Create a relationship on the common item – Country in this case.

Create a Pivot Table.

Create a measure which calculates the difference between budget and actuals.

That’s it.

The key aspect is to match the granularity or level of detail of budget and actuals data.

Usually budget is at a higher level of detail, whereas actuals data is at a much lower level of detail.

Therefore, in order to compare the two, we must roll up the actuals data to the same level as that of Budget.

Traditionally, this is done manually with lots of formulas and cumbersome steps.

Using data model and measures, this becomes extremely simple.

What’s more, when the data is updated later, you simply refresh the data model to get the updated report.

Try it out. Post your queries. Also post any other scenarios you may have.

***