Build a Dynamic Rolling Forecast in Excel with FORECAST.ETS

Copilot for Finance's variance analysis feature helps accounting teams understand not just what is happening in forecasts versus actuals, but why it is happening. Users define criteria using natural language input (e.g., 'February forecast varies from actuals more than 10%') or by selecting specific cells. The tool analyzes identified variances and identifies root causes by examining attached supporting documents such as payment lists or cash flow statements. It finds discrepancies like $755,000 differences representing approximately 12% decreases and traces them to specific drivers such as Washington state sales variations. The analysis results can be appended as comments directly to the forecast document cells, providing clear documentation for audit purposes and enabling team members to quickly understand forecast deviations.

Financial baselines capture snapshots of data at approval points. Project managers track latest forecast against approved budget, drill down into cost plan details, and enter actual costs directly. The compare baseline screen tracks variance between latest financials and initial forecasts, identifying whether variance comes from past actuals or revised future estimates. Budget versus cost views enable comparison at fiscal period levels, with ability to modify future planned costs directly to adjust forecasts and bring them within allocated budget.

This section explains variance analysis techniques including Cost Variance (CV = EV - AC) and Schedule Variance (SV = EV - PV). It also covers forecasting methods such as Estimate at Completion (EAC = AC + ETC) and Estimate to Complete (ETC). These tools enable project managers to identify deviations from baseline plans and predict future project outcomes based on current performance trends.

The FORECAST.ETS function in Excel implements the Exponential Triple Smoothing algorithm for predicting future values based on historical data. This statistical method automatically creates formulas that generate forecast values. The function can handle up to 30% missing data by making automatic adjustments, making it robust for real-world datasets that may contain gaps or inconsistencies.

This video teaches how to predict future data values using Excel's FORECAST.ETS function, which leverages the inherent 'seasonality' and 'trend' patterns in time series data. The process involves first checking for seasonality using the FORECAST.ETS.SEASONALITY function, then applying FORECAST.ETS to predict future values, and finally evaluating accuracy through absolute error and percentage error calculations. The demonstration uses fine dust concentration data from 2010-2018 to predict 2019 values, achieving approximately 86% accuracy (14% error rate), which is reasonably good for a basic forecasting method.

This video explains five methods for performing scenario and sensitivity analysis in Excel financial models: (1) Manual scenario selection using data validation dropdowns or combo boxes, (2) Scenario Manager for defining and storing multiple scenarios, (3) Data Tables for one-variable and two-variable sensitivity analysis showing multiple outcomes simultaneously, (4) Goal Seek for reverse calculations to achieve target outputs, and (5) Monte Carlo Simulation using RAND and NORM.INV functions to model probabilistic outcomes based on statistical distributions. Each method has advantages and disadvantages depending on complexity, ease of use, and the type of analysis required.

This video demonstrates three essential Excel tools for financial modeling: Goal Seek helps find single-input values needed to achieve a specific financial target (e.g., how many units to sell for $100K net income); Solver extends this capability to multiple inputs with constraints (e.g., optimizing both price and quantity within limits); and Data Table enables sensitivity analysis by showing how changing a range of inputs (like price and quantity) affects outcomes across an entire matrix. Additionally, the CHOOSE function combined with data validation creates dynamic scenario analysis models that allow users to compare best-case, base-case, and worst-case forecasts through dropdown menus.

This video presents three methods for performing Monte Carlo analysis (stochastic simulation) in Excel: (1) Native formulas approach where simulation templates are copied across multiple rows for thousands of trials, (2) Data tables that automate trial execution by cycling through input values, and (3) VBA macros that programmatically run simulations and generate results; the second part demonstrates sensitivity analysis by adjusting input probabilities and comparing results across scenarios to understand how changes in assumptions impact output metrics, using a ten-pin bowling game as an accessible example.

Scenario analysis examines multiple changing inputs simultaneously (e.g., best case, worst case, base case), while sensitivity analysis focuses on one or two inputs at a time; Excel provides several tools for this analysis including data validation dropdowns for manual scenario switching, combo boxes for visual controls, Scenario Manager for toggling between pre-defined scenarios, Goal Seek for working backwards to find inputs that achieve target outputs, and Data Tables for systematic sensitivity analysis across multiple variables.

Sensitivity analysis covers four specific tools: one-way data table (impact of one variable on output), two-way data table (impact of simultaneous changes in two variables), Goal Seek command (finding input changes for desired output), and Scenario Manager (changing up to 32 variables simultaneously). Excel Solver is used for optimization problems including limiting factor analysis, product mix optimization, production planning, transportation planning, workforce scheduling, and capital budgeting decisions. Macros are introduced to automate repetitive tasks, providing real-life examples of efficient task completion in work organizations.

ARMA (AutoRegressive Moving Average) combines autoregressive (AR) and moving average (MA) components for forecasting. AR(p) models future values based on past p values; MA(q) models based on past q error terms. ARMA requires stationary data (no trend/seasonality). SARIMA extends ARMA with seasonal parameters P, D, Q and frequency to handle seasonal series. AutoARIMA automatically optimizes parameters. For monthly data with annual seasonality, capital P=1 examines values 12 months prior. Comparing ARMA versus SARIMA demonstrates the importance of accounting for seasonality in appropriate models.

ARIMA (Auto Regressive Integrated Moving Average) is a statistical model for time series forecasting that requires the time series to be stationary, meaning its statistical properties remain constant over time. The model consists of three components: AR (Auto Regressive) which uses past values to predict future values with parameter p, I (Integrated) which applies differencing (parameter d) to achieve stationarity, and MA (Moving Average) which uses past forecast errors with parameter q. The process involves exploring the dataset, checking for stationarity using ACF/PACF plots and statistical tests like ADF, determining appropriate p and q values based on these plots, fitting the model using libraries like statsmodels or pmdarima for automation, and evaluating predictions using metrics such as MAE, MAPE, and RMSE.

ARMA models are defined by P (Auto Regressive order), D (Differencing order), and Q (Moving Average order). Implementation involves creating time series objects, splitting data into train and test sets, fitting the model, and predicting for the test period. SARIMA extends ARIMA to handle seasonality by adding seasonal components (P, D, Q for seasonal period, typically 12 for monthly data). ARMAX incorporates external factors like holidays as binary flags. Prophet (Facebook) handles complex seasonality, trend shifts, and holiday effects with minimal manual tuning. Model selection should be use-case dependent: start with simple models (ARMA) to understand baseline performance, progress to more complex models (SARIMA, ARMAX, Prophet) as needed, and compare models using Weighted MAPE on test data.

ARIMA (Autoregressive Integrated Moving Average) is a time series forecasting model that combines autoregressive (AR), differencing (I), and moving average (MA) components to predict future values based on historical data patterns; the model uses parameters p (number of lagged observations), d (differencing order for stationarity), and q (error term count) to capture trends and seasonality, and can be implemented in Python using libraries like yfinance for data retrieval and statsmodels for model fitting and forecasting.