Use the Forecast feature to extend the time horizon of a planning sheet by creating a visual measure for future periods. For example, if a planning sheet contains data for 2024 and 2025, create a forecast measure to project values for 2026. You can visualize future-period projections alongside historical or existing planning data without modifying the underlying planning data.
In this article, you learn to:
Create a forecast using historical actuals as a baseline
Configure forecasts using historical actuals as the starting point for future periods. This approach carries forward historical trends and values to provide a baseline for forecasting, which can then be adjusted in response to expected business changes.
In the Model ribbon, select Forecast.
The default measure name is set to Forecast. Set a custom name if required, then select the future period to forecast. Plan automatically populates the forecast period based on the existing data. Select Next to configure closed periods.
Closed periods represent past periods for which actual data is available. Forecast values for closed periods are locked and cannot be edited. Use an existing measure or formula to populate closed forecast periods.
To use existing measure values for closed forecasts, select Link to Measure and choose the measure from Source Measure. In this example, you populate closed periods with values from the Actuals measure. Select Next to configure open periods.
To manually enter forecast data, select Data Input and retain the default None option for Default Value.
To pre-populate open forecasts from historical data, expand the Pre-fill Open Periods section.
To initialize forecast values from a specific historical period:
Configure Copy from with the measure that contains the source values.
Set Operation to Period Range
Define the periods to copy by specifying the Source Range as the historical period and the Target Range as the future period. Plan copies the values from the source range to the corresponding periods in the target range to initialize the forecast.
In this example, you initialize the 2026 Budget with the 2025 Actuals.
After saving the configuration, plan creates the Budget measure as a time extension for 2026. Notice that Actuals are not available for the forecast period.
The Budget measure is locked for 2023, 2024, and 2025 and populated with Actuals according to the configuration in Step 3.
The 2026 Budget remains available for forecasting.
To analyze specific periods in a forecast, select Period from the Model ribbon. Select the Calendar icon to define the required time range. To focus only on actual and forecast values, unselect Show Closed Periods to hide locked historical values from the view.
In Step 6, you selected Period Range to initialize the open forecast periods. Based on this configuration, plan creates the 2026 Budget by copying the corresponding Actuals values from 2025. This provides an initial forecast for 2026 using the values from the selected historical period.
Use zero-based forecasts to enter new forecast values based on current assumptions, targets, or business expectations.
To create another forecast measure, expand the Measures pane and select Add new measure.
Follow the same steps as in the previous section to configure the forecast timeframe and closed periods.
In the Open Period configuration, ensure that you select Data Input. Leave the other options unchanged. Select Save.
The screenshot shows the Budget sourced from Actuals alongside the zero-based Forecast. The budget values are initialized using the corresponding actuals, while the forecast starts with zero values for the open forecast periods.
Top-down and bottom-up allocation provide complementary approaches to distributing forecast values across business dimensions.
Top-down allocation starts with a high-level forecast and distributes it to lower-level entities, while bottom-up allocation builds the forecast from detailed inputs and aggregates them to higher levels.
Before allocation, in the Planning ribbon, change the scaling to None.
For top-down allocation, double-click the grand total cell for Budget and enter the new value.
The value entered is automatically allocated to child nodes.
You can enable subtotals before bottom-up allocations. In the Planning tab, set Column Subtotal to Left. This action shows the 2026 Total column.
Select a leaf node or subtotal node and enter the required value.
The value entered is aggregated to the grand total Budget.
Lock values entered in a planning sheet to prevent further edits and preserve approved planning data.
In the Model ribbon, go to Rules > Locking Rule.
To lock edits to the Budget measure, set Apply to Measures to Selected Measures, and then select Budget from Choose Measures. Select Apply to Children to prevent editing child nodes.
Select Create. This rule locks the Budget measure.
Create statistical forecasts based on seasonality and trends#
Statistical forecasting uses historical data and statistical models to identify patterns and trends, and generates forecasts without manual input. For more information about Predict, see Generating statistical forecasts.
Before using Predict, in the Model ribbon, go to Period to display historical data from 2023 and 2024. Hide closed forecasts to focus on historical data and the periods available for forecasting.
Predict works on any hierarchy level. In this example, select the grand total Forecast, then select Predict on the Model ribbon. Plan displays the selected row and measure.
Set Evaluation to Bottom Up. This option generates the predicted future value based on the trend of individual leaf nodes; in this example, the chart of accounts,
The Trend Decomposition forecasting algorithm is selected by default. For more information about algorithms, see Statistical forecasting algorithms. Select Year for Set Seasonality and select Run Forecast.
Preview the predicted forecast values in a graph to visualize the expected trends and patterns across future periods.
Scroll down to view the actual predicted values and the confidence interval and range for each value.
Select Save Forecast. Review the forecast measure and period, and then select Save to apply the predicted values in the planning sheet.
Deviation shows how actual or forecasted results differ from the original budget or target. It helps identify where performance is above or below expectations and highlights areas that may require corrective action.
In the Planning ribbon, select Forecast. Set the Measure Name to Deviation. Retain the default forecast period for Jan - Dec 2026.
In the Closed Period configuration, select Formula and enter the formula to calculate the deviation between Budget and Forecast.
Use the same configuration for open periods. Select Save.
This action creates the deviation measure.
Enterprise planning, Integrated with PowerTable and Intelligence, native to Microsoft Fabric. Co Engineered with Lumel.
