Filtered budget or forecast
A filtered workbook is only one way to filter the data that is displayed in a budget. This method poses a risk, as explained in the troubleshooting section below. Learn about the other filtering methods to see if another one better meets your needs.
The creation of filtered budget gives you a lot of flexibility in how you approach your budgeting and process. You can use filters alongside the copy workbook feature to quickly create multiple budgets from the same database, where the only difference is the filter that is applied. You can then work on each of those budget workflows separately, then publish them all into the same stream, without overwriting any data.
Create a filtered budget
In the budget setup, there are two filtering options that relate to the dimensions and entities in the underlying database: 1. Level filter and 2. Dimension entity filter.
1. Level filter
The level filter applies a filter to one or more entities in a specific dimension level in the budget hierarchy. As a result, the level is displayed in the budget as usual, but only the selected entities are displayed underneath. This type of filter is suitable when you want to budget for a subset of entities.
Example 1: Level filter, single entity
In the following example, a level filter is applied to create a budget workbook for the UK.
Even though this level filter example uses the same scenario as that in the single entity filter example below, it leads to different visual results.
Example 2: Level filter, multiple entities
In the following example, a level filter is applied to create a budget workbook for a Regional Manager who is responsible for only four branches.
2. Dimension entity filter
The dimension entity filter is sometimes called the single-entity filter, budget filter, or budget-wide filter. This filtering method allows you to filter the data in the budget using dimensions that aren't present in the budget itself. In other words, you can filter one or more dimensions that aren't used as levels in the budget hierarchy.
The key points of this filtering method are as follows:
You can add multiple dimension entity filters, but each one is restricted to a single entity.
The filters are applied at the workbook level, so the whole budget is filtered to show only the data for the selected dimension entities. A filter indicator is displayed at the top of the worksheet for reference purposes.
When published, the budget stream includes data for each selected dimension and entity.
If the budget workbook includes a Balance Sheet, the Balance Sheet opening balance will be filtered and any published Balance Sheet stream will include data for the selected dimensions and entities.
As a result, you can use the dimension entity filter as another level in the budget, without the issue of having a level that only contains one value.
This filtering method is used in the consolidated workbook approach.
You can use comparison rows to bring additional information into a filtered budget. For example, if you create a budget for the USA, you might want to add data from the UK or Australia for reference or comparative proposes.
This type of filter is suitable when:
You want to create a simple budget for an entity, such as in example 1 below.
You are budgeting on a project basis. You can create multiple budgets of the same budgeting model, one for each project.
Your organization acquires a new business during the year. You can create a separate budget for that business unit based on your current budget.
Example 1: Single dimension entity filter
Suppose you want to create a Profit & Loss budget containing data for the UK only.
In the budget setup, you have levels for the Category and Account dimensions.
Select the filter checkbox.
Select the dimension (Country) and entity (UK).
In the budget worksheet, you see the Category and Account levels but only data for the UK.
Example 2: Multiple dimension entity filters
Suppose you want to create a Sales budget, but only include sales reps from the USA and products from category A.
In the budget setup, you have levels for the Sales Rep and Product.
Select the filter checkbox.
Select the dimension (Country) and US entity.
Click Add filter, then select the Major dimension and A entity.
In the budget worksheet, you see the Sales Rep and Product levels, but only data for the US and Major A product category.
Use comparison rows in a filtered budget
You can use comparison rows to bring additional information. For example, if you create a filtered budget for the UK, you might want to add data from Australia for reference or comparative purposes.
Clone a filtered budget
If you apply a dimension entity filter, when you clone the workbook, the Select budget filter setting allows you to select a different budget filter (dimension and/or entity) to apply to the new workbook.
If you apply a level filter, after you copy the workbook as usual, the new workbook will have the same level filter applied. You can then edit the setup of the new workbook to change the filter.
Publish a filtered budget
When you publish a budget as a stream, only the relevant filtered dimension will populate (or overwrite) with new data. If an unfiltered workbook is subsequently published, it will overwrite the original filtered workbook stream. It is, therefore, important to make sure all workbooks in a consolidation have the same level of filters applied or that the order of publish is such that you do not loose data.
When you are ready to view the budget data in Financial Statements or combine it into a consolidated workbook, you can publish the budget into a combined stream.
For example, suppose you created three separate budget workbooks using an entity filter on the Country dimension. Now you want to publish them all into a stream called Financial Budgets 2025.
Prerequisite: Ensure the workbooks have the same dimensions and measures.
Publish the other budgets into the same stream that you created for the first budget.
Add the budget stream to Financial Statements, then view the published budget data there.
Troubleshooting: Missing data
While applying a filter in the budget setup is a great way to focus on selected dimension entities, you are effectively removing all the other entities from the budget. As a result, you run the risk of inadvertently filtering out data, which causes problems later on.
For example, suppose you have four warehouses but one of them is not in use. When you create a Sales budget you apply a filter to stop that unused warehouse from being displayed in that budget. In other words, you are saying, "I just want warehouse 1, 2 and 3". Later in the year, if you open a new warehouse (5), it won't show up in the budget, as it has effectively been filtered out.
Problem:
Something is missing from the budget, such as a particular branch, product, or customer.
Resolution:
If you've already started budgeting, edit the filter in the budget setup to include the new item in the budget.
If you haven't started budgeting yet:
A filtered budget mightn't be the best solution for your scenario. Consider using an alternative filtering method.
Delete the dimension entity from the budget. It can then be easily added back in later.
Last updated
Was this helpful?