This post will serve as a little guide for those trying to compare departmental budgets to the current project budgets and forecasts. I often hear that there is no place to store next years budgets in Project Online, and by budgets, we are talking about the top-down approach to setting aside money for the project portfolio(s).
In most cases, I see organizations build up reports that pull out project data from Project Online. From these data feeds, almost all financial dimensions can be extracted, except for the budgets, that are not related to a project. In other words, the reports I often see, are the simple sum of the projects already approved. But how does these budgets align with the organizational budget planning? The simple answer is that you don’t know until you ask a controller to merger some data.
This post will show how simple you can set up some top-down portfolio or departmental budgets directly in your PowerBI or Excel reports. These top-down budgets rarely change, so I would go for the simple solution, as shown is this guide:
Step 1: Getting started
In this case, I am using Power BI Desktop to build my reports. I have connected to my Project Online data feed, and selected some basic information like Project Name, Total Budget and the associated Business Unit. Remember that “Business Units” will be the categories, I will later add a top-down budget to. It might be that you would rather plan your budgets from a portfolio perspective. In this case, ensure your projects are linked to a specific portfolio e.g. using a project level custom field.
Step 2: Build your initial financial report
Quite quickly you can set up some nice looking charts comparing project budgets, actuals and forecasts to the related Business Units. In my case, the budget is the sum of an CAPEX and OPEX input from a simple “Project Detail Page”. It might as well have been data from the cost resources within Microsoft Project Professional or it could have been timephased cost data from another source. As you can see, you might have your departmental names but no budget for them (top down wise) – lets fix that problem!
Step 3: Adding a new Department Budget dimension
Go back to the data area of Power BI Desktop. Now select “Enter Data”.
Step 4: Add the Business Unit budgets
Now create two columns and add some budgets to the Business Unit. Simply enter the data directly into the grid. Once done, simply click “Load”. Its very important that your Business Unit names are 1:1 with the names in Project Online. The same would of course apply if you had linked your projects to a portfolio instead of a business unit.
Step 5: Combine your new budget dimension to your existing data
In the top left corner click on “Manage Relationships” in order for the data to be joined up. Also remember to change your currency format e.g. to USD like in my case. It just looks better if you want to show the data values in your charts.
Step 6: Create the relationship
As you can probably guess, the connection between the two data sources is the “Business Unit”. Select the Business Unit column in both connections and hit “OK”.
Step 7: Adding the budget to your report
We are now ready to add the new Business Unit Budget dimension to our report. In the right side pane you can see the new data available “Budget” and “Business Unit”. Simply drag´n´drop your data to e.g. to the top left chart.
Step 8: You are now ready for budget controlling
With the new Business Unit Budget dimension, you charts can now show how much your projects are burning the approved portfolio or business budgets. In the case below you will see two different ways of showing this. There are thousands of cool ways to show this information in Power BI and Excel, the below examples are simply the most often used.
Example 1:
The Business Budget has been added as a line within the column chart. The columns represent the approved Project Budgets, Project Actual Cost and Project Forecast (Actuals + ETC)
Example 2:
Same data as above but instead of a line, the Business Unit Budget is shown as column bar next to the other cost dimensions.
As the example shows, all Business units have a project cost forecast higher than the allocated cost (top down) for each Business Unit (with the exception of the RD department).
In my opinion, this tracking is absolutely needed in an organization running a lot of projects, and especially in IT, where projects are often a delivery towards to the business departments from a fixed amount of effort and budgets planned a year ahead.
You can use the same method of adding top down budgets for all of your project data and portfolios e.g. controlling your budgets using Gartners “Run, Grow and Transform” dimensions.
I hope you found the guide useful and let me know in case you run into issues when trying it yourself.
Tip:
If you want your management to manage the budget data without opening up Power BI or Excel, a solution could be to add a SharePoint list holding the budget information, and simply connect to that from Power BI. This way, managers can use a SharePoint interface to change and add their budget information.