Each new model contains two versions — Actual and Forecast. It's common practice to add further versions to your model to meet the needs of your business use cases.
Sometimes, when you calculate a variance, you also want to have the variance represented as a percentage (Budget / Actual * 100). Adding versions with these formulas is easy, but it does have some impact on the size of your model. (Keep in mind that modules using versions as a dimension includes all versions.)
You may have several modules that need the actual and budget versions data, but don't need the variance and variance percent data. If you only need the variance data in a few instances, you'll want to avoid potentially doubling the size of your model by adding versions. Instead, create line items in your module to calculate this data. This procedure mimics what you would accomplish using versions and avoids the dramatic increase in model size.
Set up the line items described below to derive actual and budget data from another module using the SELECT statement. Having derived the actual and budget values, you can then build simple calculations for the variance and variance percent line items. The other advantage of using line items, rather than adding versions, is the ability to format each line item and use conditional formatting to highlight data.
|Actual||Module.Line Item[SELECT: VERSIONS.Actual]|
|Budget||Module.Line Item[SELECT: VERSIONS.Budget]|
|Variance||Budget - Actual|
|Variance %||Variance / Actual|
You may find you already have line items in the module that you also want to use. To avoid this issue, create a Line Item Subset and use the COLLECT function to pull data from line items in other modules. A line item subset allows you to group line items from different modules together to use as a single general list.
This procedure describes how to create a module that uses a Line Item Subset and the COLLECT function to pull data from line items in other modules and use line items to calculate variance and variance percent.
- Analyze your model to understand which modules and line items require variance and variance percent calculations. This results in a much more effective use of your workspace.
- Once you know the modules and line items you want to group together as a list, create a line item subset. Select the line items, across modules, which you want to use to calculate variance and variance percent.
- Create a new module with these dimensions:
- Columns: Line Items (Data, Actual, Budget, Variance, Variance %)
- In Blueprint, select the data line item.
- In formula edit mode, enter COLLECT() and press ENTER. Data for each item in the Line Item Subset list will be populated with the data from the underlying line item.
- Complete the calculations for the remaining line items, referring to the formula examples in the table above.
- Apply the appropriate format to the line items and any conditional formatting.
- Publish the module to an appropriate dashboard.