The Planning Mode in the Datarails Flex Add-in for Excel enables users to manage forecasting and budgeting periods dynamically. It allows users to define actuals and forecast periods, apply default formulas, and seamlessly roll forward scenarios. This guide explains how to configure Planning Mode and use the Roll Forward option from the forecast module to update financial models efficiently.

Learn more about the forecast module here.

Understanding Dynamic Ranges

Before using the Planning Mode or the Roll Forward Module, it is essential to have a single dynamic range in the Excel file.

• A Dynamic Range is a structured data range that expands or contracts based on data updates.

• Formulas referencing the dynamic range must be aligned with Excel dynamic arrays.

Learn more about the dynamic ranges here.

Setting Up Planning Mode

The option to set a Planning Mode is available when the file scenario is either Forecast or Budget.

To set up Planning Mode, follow these steps:

Step 1: Define the Forecast Months

Prerequisites: 

• Ensure that the file you are using is connected to a Filebox in Datarails, with Scenario = Forecast or Budget.

• Ensure that your forecast and actuals months are part of a dynamic range.

• The system assumes a 12-month forecast or budget model.

Steps to set Planning Mode:

• Locate the Dynamic Range dropdown under "Reports Tools" in the Datarails Flex ribbon and select "Manage Dynamic Ranges".

    

• In the Dynamic Ranges dropdown, select the dynamic range where your forecast and actuals months appear.
  

• Locate and click the "Planning Mode" button.
  
  

Step 2: Setting the Actuals and Forecast Months

• In the modal that opens, assign an Excel column to each month.

• For example, if Excel column F is the first month in the Actuals & Forecast area, assign it accordingly.

  Important Notes:

  • The months do not have to be consecutive. You can include "Total" or comparison columns (e.g., quarterly summaries). However, avoid selecting columns that are not actuals or forecast months in the modal.

  • Forecast models follow an Actuals + Forecast structure (e.g., 3+9 means 3 months of actuals and 9 months of forecast).

  • Budget scenarios behave like 0+12 forecasts, with 12 months of projections.

  • Regardless of the actuals/forecast split, all 12 months must be listed in the Planning Mode.

  • You cannot assign the same column to two different months.

  Once all 12 months are listed, click "Next".

Step 3: Applying Default Function

After setting up the forecast months, you need to define a default function.

What is a Default function?

• The default function is automatically inserted into all actual months.
• It ensures that actual periods have consistent formulas, while forecast periods retain their calculations.

Syntax Rules for Default Formulas

• The default function must be written as if pasted into the first row of the first month.

• Example: If the first month is in column F, and the dynamic range spans rows 8 to 35, then the function should be written as it would appear in cell F8.

• Use absolute references ($A$1, $A1, A$1) where necessary to prevent shifting.

• The only allowed special characters are: $ and [ ].

Formula Application Options

You can apply the formula in three different ways:

1. Actuals – Inserts the formula into all actuals months.
2. All 12 Months – Applies the formula across all months, regardless of the actual/forecast split.
3. New Actuals Only – Applies the formula only to newly designated actual months.

When you are done with the function setting, click "Set Planning mode", and in the dynamic raneg modal, click "Done" to save your changes. 

Important:

The system provides an option to disable category range updates, preventing the dynamic range from expanding automatically.

4. Submitting and Rolling Forward the Forecast

Once the Planning Mode is configured, users can submit and roll forward the file.

• Click Submit to finalize the Planning Mode settings.

• If the system detects a formula mismatch (e.g., F8 cell content differs from the Planning Mode definition), a confirmation modal will appear. Users should verify and correct the formula before proceeding.

Rolling Forward the Forecast

1. Navigate to the Forecast module on Datarails Web and select the scenario to roll forward.

   • Example: Rolling forward from 3+9 (March 2024) to 6+6 (June 2024).

2. Click Roll Forward and wait for the update to complete.

3. Download the newly rolled-forward file.

Results After Rolling Forward

• The default function is automatically applied to the first six months (e.g., F to L).

• Forecast calculations remain unchanged, ensuring that financial models are preserved.

Key Takeaways

✅ Ensure that your dynamic range includes your forecast months before enabling Planning Mode.
✅ List all 12 months, regardless of the actual/forecast split.
✅ Map forecast months to the correct columns in the formula range.
✅ Use default formulas to automate actual period updates.
✅ Roll forward scenario cycles without affecting forecast calculations.

By following these steps, users can streamline their forecasting process, ensuring accuracy and efficiency in financial planning within the Datarails Flex Add-in.

Updated April 06, 2025 13:18