Master Excel Copilot for Forecasting & What‑If Analysis

Forecasting and what-if analysis are two of the most practical uses of Excel for finance teams, analysts, and small business owners. Copilot can speed up both jobs, but it is not a replacement for Excel’s analytical toolkit. It can summarize trends, generate helper formulas, build charts, explain assumptions, and, in some cases, even create more advanced analyses with Python in Excel. At the same time, Excel’s native tools still matter. The Forecast Sheet is designed for time-series forecasting, while Data Tables and Goal Seek remain the most reliable ways to test assumptions and achieve target outcomes.

In this tutorial, we will show how to use Copilot in Excel for forecasting and what-if scenario analysis. We will combine both Copilot and Excel analysis tools.

Part 1: Getting Copilot to Understand Your Data

Before running any analysis, convert your data into a table. Copilot works better with structured data.

Preparing Data:

• Select your data range
• Go to the Insert tab >> select Table or press CTRL+T
• Go to the Table Design tab >> in the Table Name box >> type BusinessData
• Copilot navigates named tables far more reliably than unnamed ranges

Using Copilot:

• Go to the Home tab >> select Copilot
• Type or insert prompts

Prompt Template: Initial Data Orientation

"I have a table called BusinessData. Please summarize the key financial trends,
calculate average monthly profit margin, and flag any months where profit margin
dropped below 30%."

What Copilot does: It reads the entire table, applies conditional logic, and returns a plain-English summary alongside a highlighted result. Click Done to accept the result.

What to check manually: Confirm the month count (should be 50) and spot-check one margin figure against the formula in column G. This takes 30 seconds and builds trust in everything that follows.

Part 2: The Core Scenario: Raw Material Costs +15%

Consider a scenario where you want to know: “What happens to profit margin if raw material costs increase by 15%?” The workbook has already pre-calculated data, but there are two ways to use Copilot here: direct analysis and a comparative summary.

Approach A: Direct Copilot Analysis

Prompt Template: Direct Scenario Query

"Using the BusinessData table, compare the original ProfitMarginPct column against
the AdjProfitMarginPct column (which reflects a 15% increase in raw material costs).
Tell me: (1) the average margin under each scenario,
(2) how many months fall below a 25% margin threshold under the adjusted scenario versus
the original, and (3) which specific months are most at risk."

What Copilot does: It calculates both averages, counts threshold breaches in each column, and names the at-risk months. Copilot surfaces exactly which months and quarters bear the most exposure.

Follow-up Prompt:

"Create a column chart comparing original vs. adjusted profit margin by month.
Label the 25% threshold line."

Approach B: Using Copilot to Set Up a Data Table

Excel’s Data Table (under What-If Analysis) can show you profit margin across a range of raw material cost increases — say, 10%, 20%, 30%, and 35% — in a single matrix. Copilot can build the structure so you don’t have to remember the exact setup steps.

Prompt Template: Data Table Setup

"I want to build a one-variable Data Table to see how profit margin changes as raw material
costs increase by 10%, 20%, 30%, and 35%. My base profit margin formula is in cell G3.
Walk me through exactly where to place the input values, what the Row Input Cell should be,
and how to run the Data Table from the Data tab."

What Copilot does: It gives you step-by-step instructions, places your percentage values in a column, puts a reference to the base margin formula one row above, and one column to the right.

Using What-If Analysis Data Table:

• Go to the Data tab >> select What-If Analysis >> select Data Table
• Follow the instructions on the sheet

The dialog takes 20 seconds once you know the right cell references. Excel populates the full sensitivity table instantly.

You’ll see exactly at what rate the cost increase falls below your minimum threshold. For this dataset, a 25% increase in raw materials combined with flat revenue pushes most months into the danger zone.

Part 3: Forecasting Future Revenue with Forecast Sheet

Excel’s Forecast Sheet uses exponential smoothing (ETS) to project future values based on historical patterns — no statistics degree required. With 50 months of revenue history in the dataset, the model has enough data to produce a meaningful 6- to 12-month projection.

Generating the Forecast Sheet:

• Select the Month and Revenue columns in BusinessData
• Go to the Data tab >> select Forecast Sheet
• Set the Forecast End date (e.g. December 2027) >> click Create

Excel produces a new sheet with projected revenue plus upper and lower confidence bounds.

Prompt Template: Forecast Interpretation

"My Forecast Sheet projects revenue growing from February 2027 to December 2027.
My raw material cost has historically averaged 35% of revenue. Under (a) the base forecast,
(b) the upper confidence bound (assume +8%), and (c) the lower confidence bound (assume −8%),
what would annual gross profit and profit margin look like if raw material costs also increase
by 15% versus staying flat? Keep labor at $105,000/month and overhead at $44,000/month."

What Copilot does: It calculates six scenarios in one response — three revenue levels × two cost assumptions — and presents them as a comparison table. This is the kind of sensitivity matrix that used to take an analyst an afternoon to build manually.

Prompt Template: Challenging the Forecast Assumptions

"The Forecast Sheet is picking up a strong Q4 seasonal spike each year.
But I believe next year's Q4 will be weaker because we're losing a major contract in October.
How should I adjust the revenue forecast for Q4 2027 to reflect a 20% reduction from
the model's projection, and what does that do to annual profit margin under a
15% raw material cost increase?"

What Copilot does: It advises you to manually override the forecast values in the Forecast Sheet tab for the relevant months, then recalculates the adjusted scenario. This kind of qualitative override — “I know something the model doesn’t” — is exactly where Copilot earns its place, by doing the arithmetic on your judgment call instantly.

Part 4: Goal Seek: Working Backwards from a Target

Goal Seek answers the reverse question. Instead of “what is the margin if costs go up?”, it asks: “How much can costs increase before the margin falls below 25%?”

Running Goal Seek (Native Excel):

In your BusinessData sheet, pick a single representative month — let’s use the most recent full month (January 2027). Then:

• Go to the Data tab >> select What-If Analysis >> select Goal Seek
  • Set cell: the Profit Margin % cell for that month (column G)
  • To value: 0.25
  • By changing cell: the Raw Material Cost cell for that month (column C)

Excel iterates in under a second. The result is your raw material cost break-even threshold — the exact dollar amount at which the margin hits 25%.

Prompt Template: Goal Seek Interpretation

"Goal Seek tells me that raw material cost for January 2027 can rise to $234,652 before
profit margin falls below 25%. My current cost for that month is $197,356. Express this
as a percentage increase above current cost, and tell me how many months of typical cost
inflation at 1.5% per month it would take to reach that threshold from today."

What Copilot does: This gives you a concrete planning horizon: you have just over a year before margin pressure becomes critical, assuming current revenue holds.

A second useful Goal Seek: Run the same exercise but change the variable to Revenue instead of Raw Material Cost. This tells you the minimum revenue needed to maintain a 25% margin even under a 15% cost increase. Copilot can interpret that result the same way.

Prompt Engineering Principles for Financial Analysis

These patterns consistently produce better results when working with Copilot on quantitative tasks:

• Reference column names exactly: Copilot is far more accurate when you use the actual header names from the table rather than descriptions.
• State what stays fixed: Always specify which variables are held constant — “Assume LaborCost and Overhead remain at their current values.” Copilot doesn’t know your business constraints unless you tell it.
• Specify your output format: “Show me as a table,” “give me the Excel formula I can paste into a cell,” “write this as bullet points for a slide.” Copilot responds to format instructions precisely.
• Add “show your working”: On any calculation, appending “explain how you calculated this” forces Copilot to show the arithmetic. This is essential for audit trails and catching errors before they reach a stakeholder.
• Break complex questions into steps: Rather than one mega-prompt, ask Copilot to (1) add the adjusted columns, (2) calculate new margins, (3) identify at-risk months, (4) summarize in a table — sequentially. Each step is checkable and correctable.

Conclusion

Excel’s native What-If tools and Copilot as the analytical layer on top represent what modern financial analysis actually looks like in practice. Copilot reduces setup friction, surfaces interpretation, and handles the prose. The native tools — Forecast Sheet, Data Tables, and Goal Seek — remain the computational engine. And the analyst’s judgment — which months to override, which scenarios to present, how hard to push on risk language — stays exactly where it belongs: with you. That combination gives you the best of both worlds: AI-assisted setup and interpretation, with Excel’s proven analytical tools underneath.