Looking for a dynamic way to manage your 1+11, 2+10, 3+9 actualized planning templates? Follow the steps in this article to build a dynamic template to use with Cube.
If you don't need to do this planning in a spreadsheet and instead prefer to manage your actualized budgets or forecasts in Scenario dimensions, you can do so in the Web portal.
What you'll need:
- Download our template or bring your own.
- Up-to-date actuals data. Learn more about importing here.
- A budget 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
We use Drivers tabs in our templates to power dynamic reporting by linking the rows and columns to your dimensions in Cube. Learn more about creating a Drivers tab.
The drivers in this workbook power the dropdowns in the top left of the P&L trend tab. The selections made here will dynamically change which months fetch from your actuals or your plans, the date range, entity, and departments.
Explore drivers in this workbook by opening the named ranges already defined.
In Google Sheets
Click Data, then Named Ranges.
In Microsoft Excel
Click Formulas, then Name Manager in the ribbon.
Before using this template to fetch and publish with Cube, you will need to update the drivers and named ranges to match your Cube dimensions.
Dynamically determine the cutover date
This report needs a combination of our actualized data up to a certain month and budgeted data through the rest of the year, and we want to dynamically manage this cutover.
We'll add hidden formulas in cell D5, and rows 7 and 8, as well as a list from our Drivers tab to help us. If these are hidden in your spreadsheet, ensure all columns are visible, and change the text color of these cells to black.
First, the date in cell D5 is based on the month selected in cell G3 using the below formula. We need this formula because automatic date formatting will typically interpret our MMM-YY format as the MMM-DD of the current year.
=DATE(RIGHT(G3,2)+2000, VLOOKUP(LEFT(G3,3),Drivers!D116:E127,2,FALSE),1)
The drivers portion of this formula references a table of month names and associated numbers:
Next, use the below formula to populate the dates in row 7 of the report. This formula tells these cells to populate with the first day of each month and to match the year from our driver selection in cell G3. This formula also references a row of numbers in row 8 to increment the months for each column.
=DATE(RIGHT($G$3,2)+2000,F$8,1)
Then, compare the resulting dates against the date in cell D5 to determine whether values from actuals or the selected plan should be fetched from Cube. This formula will display "Actuals" or the text from the Scenario dimension selector (G5) in row 9.
=IF(F$7<=$D$5,"Actuals",$G$5)
Finally, display the range of time dimensions in row 10 from the year selected using the date format Cube will recognize.
=TEXT(DATE(YEAR(F$7),F$8,1),"mmm-yy")
After hiding cells again or changing them to white font, the report uses the drivers and looks clean.
Fetch and publish data
Your template is now ready to use! Open the Spreadsheet add-on, select the range, and fetch your data.
If you would like to save the data to a new scenario by publishing it back to Cube, you can temporarily change the headers from Actuals & Budget to the new scenario name you created at the beginning of this guide and publish.
If publishing, be sure to select child dimensions in your dropdowns, as you won't be able to publish to "All Departments" or other parent dimensions.