After your Cube is set up and dimensions established, you're ready to automate your existing reports, such as your Budget vs. Actuals (BvA), create your own templates, or get started with one of ours.
What does it mean to automate your reports?
Connecting your workbooks to your Cube database yields faster, more dynamic reporting by creating a bridge between your data and your spreadsheets. Establishing this connection requires the naming conventions of items within your report to match the dimensionality of your Cube.
Here's why connecting reports to Cube is vital:
The Cube spreadsheet App acts as a bridge between your workbook and the Cube cloud. When you command the App to exchange data — pushing data to Cube or fetching from it — the Spreadsheet App scans the selected rows and columns in your workbook, looking specifically for dimension names. These names are then communicated to the Cube cloud for either storage or retrieval of associated values.
If there's a mismatch or unfamiliar dimension names, the Cube cloud struggles to pinpoint the exact location of the data value you're trying to access or save. This is why it's essential to match your reports to your Cube structure — it ensures seamless communication between your workbooks and the Cube cloud, making your FP&A operations smooth and error-free.
Automate your spreadsheets
In this article, you'll learn how to automate an existing report using a Budget vs Actuals (BvA) report as an example. To illustrate this, see the video below or keep reading.
Don't have a Budget vs. Actuals report you use regularly? In another article, we will cover how to use Cube to build a BvA report from scratch.
1. Check for repeat dimensions
To properly connect your reports to Cube, you will need to ensure that the same dimension and dimension members do not appear in both the rows and columns. For example, if you have financial accounts from your P&L down the rows of your report and you also have a header that says “Revenue” in your columns, Cube will not be able to connect to your report.
For a classic BvA report, you will most likely set up your account structure down the rows. In the column headers, you will reference your Actuals and Budget Scenarios and a time period, such as a year or one month.
Additionally, to the right of the Actuals and Budget columns, you will also establish variance columns using either spreadsheet-driven formulas or built-in Cube formulas. We will explain how to set up these formulas later in this article.
Example BvA report structure below:
2. Select and validate ranges
Select range
When your general report structure is compatible with Cube, you are ready to select your range.
- Click on the Cube App icon in Excel (or the Cube Extension dropdown in Google Sheets) to open Cube.
- Highlight your entire report, ensuring that you've captured all of your row and column headers.
- In Cube, click Select
- Then Select Range to initiate the Cube connection. Learn more about selecting a range.
The Spreadsheet App will now scan the row and column headers to determine which data to fetch from the cloud.
Validate and fetch data
Click Validate to verify that Cube recognizes all of your row and column headers. The Spreadsheet App will list any dimension members that need to be corrected. Learn more about validating dimensions.
When you have corrected any misaligned dimensions, click Fetch Data in the Spreadsheet App. Learn more about fetching data from Cube.
Typically, users choose to leave the “Exclude zero rows” toggle off when they are working with existing templates because each account, whether it contains data or not, must be included in their standard reporting packages. The toggle is typically used for ad-hoc reporting, which you can read more about here.
3. Create a Drivers tab (optional)
If this is a frequently used template, consider using your Cube dimensions to create a Drivers tab. This will help you easily update the data by period, scenario, or other dimension, and communicate the information this workbook contains if you share it with non-Cube users.
4. Formulas
Determine which formulas to leave in your sheet and which to bring into Cube.
Account formulas
As a best practice, we recommend using Cube Formulas for common calculations such as Gross Profit, Gross Margin, or Net Revenue. These formulas can be set up within the properties of your dimension members in the Workspace of Cube. See the example for Gross Margin below:
After you have set a formula on a dimension member, fetching it in a spreadsheet will render a value, not a spreadsheet-driven calculation.
Scenario formulas
Cube formulas can also be used to calculate variances between Scenarios, such as your Actuals and Budgets. Instead of calculating a BvA variance with spreadsheet formulas, you can create a new Scenario in Cube with a formula set on its properties, subtracting the Budget Scenario from the Actuals Scenario. See example below:
After you have set a variance formula on a Scenario, fetching that Scenario in a spreadsheet will render a value, not a spreadsheet-driven calculation.
Spreadsheet formulas
One-off, irregularly used formulas, or formulas that are specific to a particular workbook are best left in your spreadsheet. In the context of a BvA report, it might be beneficial to calculate Scenario variances manually in the spreadsheet because it will keep your report more dynamic. You can more easily compare your Actuals against another Scenario, such as your Forecast, without having to build an FvA Scenario in the backend of Cube.
Example below of spreadsheet-driven variance calculations in a BvA report:
4. Review report formulas and error detection
Report formulas
Updating the rows and columns in the previous steps may have disrupted existing formulas in your workbook. Review them and add any new formulas needed to complete your planning and analysis.
To ensure that Cube does not disrupt any of your existing formulas, check that there is not a valid dimension name in either the row or column of that cell.
For example, if you have a manual Revenue sum that you want to keep in my spreadsheet, but you also have an account in Cube called Revenue, Cube will retrieve the corresponding Revenue data it can pull from the cloud and your formula will disappear. If you instead call that row “Revenue Total”, Cube will not find a match in the system and it will leave your formula in the spreadsheet.
Error detection
You may also want to review or add formulas for error detection throughout your workbook to ensure the balances are correct.
Use your spreadsheet with Cube
You can now use the Spreadsheet App to fetch and publish your data in this automated workbook. Want to try automating one of our templates? Find them here.
Learn more about using the Spreadsheet App or about maintaining your data in Cube.