Purpose
By the end of this lesson, you will be able to use Excel’s forecasting tools and AI assistance together, while maintaining appropriate skepticism about projections.
Lesson Explanation
Excel’s built-in FORECAST.LINEAR function projects a future value based on a linear trend in historical data: =FORECAST.LINEAR(13,B2:B13,A2:A13) projects the value for period 13, based on the historical relationship between periods (A2:A13) and values (B2:B13) in periods 1 through 12.
The Excel Forecast Sheet feature (Data → Forecast Sheet) goes further, automatically detecting seasonality in historical data and producing a projected chart with confidence intervals – a range showing how much the actual future result might reasonably vary from the point projection.
AI can help interpret a forecast’s output or suggest what additional factors might affect the projection – “given this projected 8% revenue increase, what are three factors that could cause the actual result to differ significantly?” – but the forecast’s actual underlying calculation should come from an established method like FORECAST.LINEAR or the Forecast Sheet, not from an AI’s own unverified numerical projection.
Every forecast, regardless of method, should be presented with its underlying assumptions and confidence range made explicit, not just a single confident-looking number – a common and genuinely important business mistake is treating a forecast’s point estimate as a guaranteed outcome rather than one plausible scenario among a real range of possibilities.
Practice Questions
1. Historical revenue for periods 1 through 12 is in B2:B13, with period numbers in A2:A13. Write a formula projecting the value for period 13.
View Answer
=FORECAST.LINEAR(13,B2:B13,A2:A13)2. What does the Excel Forecast Sheet feature provide that a simple FORECAST.LINEAR formula does not?
View Answer
Automatic detection of seasonality in the historical data, plus a confidence interval range showing how much the actual result might reasonably vary from the point projection.
3. A prompt asks an AI to “predict our revenue for next quarter” directly, without providing any historical data or using Excel’s forecasting functions. What does this lesson say about this approach?
View Answer
The forecast’s actual underlying calculation should come from an established method like FORECAST.LINEAR or the Forecast Sheet feature, not from an AI’s own unverified numerical projection generated without a defined statistical method.
4. What is a genuinely appropriate use of AI in the forecasting process, according to this lesson, even though the numerical projection itself should come from Excel’s methods?
View Answer
Helping interpret the forecast’s output or suggesting factors that could cause the actual result to differ from the projection.
5. A report presents a single forecasted revenue figure with no confidence range or stated assumptions. What genuinely important business mistake does this lesson warn is associated with this kind of presentation?
View Answer
Treating the forecast’s point estimate as a guaranteed outcome, rather than one plausible scenario among a real range of possibilities.
6. An Excel Forecast Sheet produces a projected value of $500,000 with a confidence interval of $420,000 to $580,000. What does this range communicate that the single $500,000 figure alone would not?
View Answer
The genuine uncertainty around the projection – the actual result could reasonably fall anywhere within that wider range, not just precisely at the point estimate.
7. Write a prompt asking an AI to help identify factors that might cause an actual result to differ from a projected 8% revenue increase, following this lesson’s guidance on appropriate AI use in forecasting.
View Answer
Something like: “Given this projected 8% revenue increase based on our historical trend, what are three business or market factors that could cause the actual result to come in significantly higher or lower than this projection?”
8. A forecast is based on 6 months of historical data that included one unusually large, one-time bulk order. What might this lesson’s emphasis on stating assumptions suggest should be noted alongside this forecast?
View Answer
That the historical baseline includes this atypical event, which might distort the projected trend if not explicitly flagged as an assumption or anomaly worth considering.
9. Why does this lesson specifically distinguish between using AI to help INTERPRET a forecast versus using AI to directly GENERATE the numerical projection itself?
View Answer
A statistically-grounded method like FORECAST.LINEAR is based on an established, verifiable calculation from the actual historical data, while an AI generating a number directly (without this grounding) risks producing a plausible-sounding but ungrounded figure, echoing broader concerns about AI-generated numerical claims from earlier in this course catalog.
10. A business presents a revenue forecast to investors, stating the point estimate but not the underlying assumptions or confidence range. What risk does this create if actual results come in notably different from the projection?
View Answer
Without stated assumptions or a confidence range, a difference from the point estimate may look like the forecast was simply “wrong,” when in reality it may have fallen within a reasonable, expected range of uncertainty that was never communicated.
11. A FORECAST.LINEAR formula is applied to historical data that shows a clear seasonal pattern (like predictable holiday spikes), rather than a steady linear trend. What limitation of this specific function does this scenario illustrate?
View Answer
FORECAST.LINEAR assumes a straightforward linear relationship and does not automatically account for seasonality, unlike the Forecast Sheet feature, which is specifically designed to detect and incorporate seasonal patterns.
12. Why might explicitly stating a forecast’s assumptions be considered as practically important as the projected number itself, according to this lesson’s overall guidance?
View Answer
The assumptions define the conditions under which the projection is valid; if those conditions change or don’t hold, the projection’s usefulness changes accordingly – understanding this context is essential for correctly interpreting and acting on the forecast.