Rolling forecasts are perfect for those looking to maintain an up-to-date view of their financial outlook. This guide will show you how to incorporate your Cube data and our powerful time shortcut feature into a template that uses trailing and next 12-month rolling views and can be adapted to fit your planning cycle. Use this rolling forecast template to quickly adjust your forecasts to reflect the latest data, ensuring your strategic decisions are always based on current information.
What you'll need:
- Download our template, or bring your own.
- Up-to-date actuals data. Learn more about importing here.
- A forecast scenario that has up-to-date data. Learn more here.
- If you plan on publishing data from this template to a new plan, have a new scenario ready to capture that data for you.
Explore the Drivers
Many of our templates use Drivers tabs to dynamically switch between subsets of data while maintaining row and column dimension name alignment with Cube. This is useful for quickly switching time periods, departments, or scenarios and for bringing in any additional drivers for calculations in your workbook, such as exchange rates.
The drivers in this workbook power the lists in column C, using defined ranges and data validation. This allows you to quickly change scenarios, departments, or which month you'd like to pull Actuals data through.
The Scenario dropdown is connected to cells in W9:AI9 with the formula =$C$3 so you can easily change which scenario to use for planning.
The Department dropdown is connected to column D, which is likely hidden. Unhide it to see the formula used =$C$5.
The dropdown that allows you to select the month to pull Actuals through connects to a number of date headers throughout the sheet to make them dynamic. Unhide the Actuals historical data columns, and explore the formulas in row 10 to better understand how these work.
Explore the pre-defined ranges in the Drivers tab of the workbook or by opening them in your spreadsheet editor.
In Google Sheets
Click Data, then Named Ranges.
In Microsoft Excel
Click Formulas, then Name Manager in the ribbon.
These ranges are tied to the dropdown cell using data validation. Learn more about how to set this up here.
You will need to update the drivers and named ranges in this template to match your Cube dimensions before using the Spreadsheet add-on.
Time shortcuts
This template uses two time shortcuts to quickly determine the last and next 12 months without using spreadsheet formulas. T12M in cell I10 summarizes the last 12 months of actuals data, and N12M in cell W10 summarizes the next 12 months of the selected budget or forecast scenario.
If you use rolling forecasts to plan from a different number of months, you can adjust these time shortcuts and the date columns to make this template better fit your needs.
Learn more about using time shortcuts in your spreadsheets.
Fetch data
This spreadsheet is now ready to use with your Spreadsheet add-on. Select the range by highlighting cells D9:AI52 and, in the Spreadsheet add-on, click Select, then Select Range. Now you can fetch your data from Cube.
Why are there two columns with account dimension names? This is just for formatting purposes so we could have an empty column C below our dropdown selectors. You may also need an additional account dimension column if you use different names for your accounts than Cube recognizes. You can have as many duplicate columns as you need as long as they stay outside of the range you select and use with Cube.
When you fetch data, the chart at the top of the template will also populate with data.
If you changed the number of months this template uses, be sure to review W55:AI57 so that the chart stays properly connected.
Publish data
Now that this template is set up and fetching data from Cube, you can use it to publish data to be stored in a rolling forecast scenario. You might choose to do this to make reporting and planning easier by having all of your data stored in the Cube cloud. This rolling forecast scenario could be fetched into another template or visualized in our dashboards for easy communication.
Watch one of our experts set this up in Cube with a similar template:
Or follow the steps below:
1. Create a new scenario to hold the data from the Scenarios tab of your Web portal. This could be a copy of an existing scenario or a new one.
2. Adjust the Actuals data through date in your template to match your planning cycle and select a scenario to fetch data from.
Initially, this might be a budget or forecast scenario, and then you might use your Rolling forecast scenario to update it regularly. Then Fetch data to your template.
3. Prepare your template to publish by adjusting filters to point to the lowest level dimensions instead of parent dimensions. Cube stores data at the lowest cross sections of dimensions so we can't publish to All Departments or All Markets. Learn more about Cube's data model.
Update the columns D, F, G, and H if you are using our template to list a child dimension to publish to such as a specific department, entity, product and market. Alternatively, you can delete these columns and add them to your range filters instead by reselecting your range.
4. Enter the data in your sheet that you would like to save to the Rolling forecast scenario. Maybe this is one additional month of data or multiple.
When you're finished, you're all set to publish!