5 STEPS TO BUILDING A SIMPLE PREDICTIVE MODEL IN EXCEL WITH AI

The quickest and simplest way to build a predictive model in Excel using AI-powered features is through the Forecast Sheet tool, which utilizes the Exponential Smoothing (ETS) algorithm.

5 Steps to Building a Simple Predictive Model in Excel with AI

Here are the 5 steps:

• PREPARE CLEAN, TIME-SERIES DATA: Organize your historical data into two corresponding columns: one for dates or time entries (the independent variable, or X-axis) and one for values (the dependent variable you want to predict, or Y-axis). Ensure the time intervals are consistent (e.g., the 1st of every month). AI Tip: While not a required step for this tool, for maximum accuracy, use features like Power Query to clean and fill any gaps in your data beforehand.

  
  

• ACTIVATE THE FORECAST SHEET TOOL: Select both of your data series. Click the Forecast sheet button in the Forecast group on the Excel ribbon. Excel will automatically launch a dialog box that contains a preview of your forecast.

  
  

• SET THE FORECAST END DATE: In the dialog box, use the Forecast End dropdown menu to select the desired date for your prediction to stop (e.g., six months or one year after your last data point). Excel uses an AI-driven algorithm to analyze the historical trend and seasonality up to this date.

  
  

• CUSTOMIZE AND REFINE OPTIONS: To refine your model, click Options at the bottom of the dialog box. Here are the key settings:

  Seasonality: Excel usually auto-detects the seasonal pattern (e.g., 12 periods for monthly data). You can manually set this number if needed.

  Confidence Interval: Check this box to include the upper and lower bounds for your forecast, which indicates the potential range of error and the model's reliability.

  
  

• CREATE AND ANALYZE THE MODEL: Click Create. Excel instantly generates a new worksheet containing two main elements:

  A Table: This includes your historical data plus two new columns: the Forecasted Values and the Confidence Bounds.

  A Chart: This visually displays your historical trend line and the predicted trend, with the confidence interval shaded.

  
  

The resulting worksheet is your simple predictive model, driven by AI algorithms. You can now use the forecasted values to inform your planning and decision-making.