Use Excel Copilot to Calculate MAPE and Spot Forecast Outliers

What This Does

Excel Copilot lets you calculate forecast accuracy metrics (MAPE, bias, variance) and identify outliers by describing what you want in plain English — no formula lookup or manual conditional formatting required. For a demand planner managing hundreds of SKUs, this cuts a 60–90 minute weekly exception review to under 15 minutes.

Before You Start

• Microsoft 365 subscription with Copilot (Business Standard or higher)
• Your actuals vs. forecast data loaded into an Excel table (columns: SKU, Actuals, Forecast, or similar)
• Logged in with your Microsoft account

Steps

1. Open Your Actuals vs. Forecast Table

Your data should have at minimum: SKU identifier, Actual sales, Forecast. Ideally also: Category, Period, Region. Format it as an Excel Table (select data → Insert → Table) so Copilot can reference it cleanly.

What you should see: A formatted table with header row highlighted.

2. Open Copilot

Click the Copilot button in the Home ribbon (sparkle icon). The Copilot pane opens on the right.

3. Calculate MAPE

In the Copilot prompt field, type:

Add a column calculating MAPE for each row. MAPE = ABS(Actuals - Forecast) / Actuals. Exclude rows where Actuals = 0. Format as percentage with 1 decimal place.

What you should see: Copilot proposes a new column with the formula pre-filled. Click Insert column to add it to your table.

4. Identify Top Exceptions

In Copilot, type:

Highlight rows where MAPE exceeds 20% in red. Create a separate table showing only these exception rows, sorted by MAPE descending.

What you should see: Red highlighting on high-variance rows and a summary exception table you can review immediately.

5. Calculate Overall Bias

In Copilot, type:

Calculate the overall forecast bias as (sum of forecasts - sum of actuals) / sum of actuals. Show as percentage. Also show average MAPE across all rows with non-zero actuals.

What you should see: A summary row with your headline accuracy metrics — the numbers you'll report in the demand review.

Real Example

Scenario: It's Monday morning. You have 450 SKU rows of weekly actuals vs. forecast data just exported from SAP. You need to identify the top exceptions before the 10am demand review.

What you do: Load the export into Excel → open Copilot → type: "Calculate MAPE for each row, highlight exceptions over 25%, and sort the table by MAPE descending. Show me a summary of the top 10 exception SKUs with their actuals, forecast, and variance."

What you get: In 3 minutes, you have a sorted exception list. The top 10 problem SKUs with their variances are visible immediately. The analysis that used to take an hour is done before your coffee.

Tips

• Use consistent column names (Actuals, Forecast, SKU) — Copilot performs better when headers are clear and unambiguous
• Ask Copilot to add a "Bias Direction" column (Over/Under) alongside MAPE — it helps quickly see if your systematic error is running high or low
• Save your Copilot-enhanced template for reuse each week — the formulas stay, you just paste in new actuals data

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.