By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 15, 2026
Forecasting note: Weighted pipeline bookings are not automatically recognized revenue. Model delivery timing separately and avoid adding overlapping forecast sources.
You can build a defensible revenue forecast in Excel today by using a bottom-up weighted pipeline for deal-driven businesses, or Excel’s FORECAST.ETS and Forecast Sheet when you have clean monthly history with seasonality. Layer in FORECAST.ETS.CONFINT for confidence bands, SUMPRODUCT and SUMIFS for pipeline rollups, and a simple moving average as a backup check. This guide walks through each method with a workbook layout you can replicate.
TL;DR:
- Use FORECAST.ETS for seasonal history; it handles up to 30% missing data, but needs consistent timestamps and a full seasonal cycle.
- For deal driven revenue, multiply each deal’s amount by its probability, then use SUMIFS to total weighted values by expected close month.
- Use FORECAST.ETS.CONFINT for its default 95% interval, which assumes a normal distribution, and adjust the range for sales events absent from history.
- Run a hindcast from an earlier date and compare predictions with actuals; track MAPE by averaging each period’s absolute percentage error.
Table of Contents
- Which Excel forecasting method fits your data?
- How to format and clean your historical sales data
- Running Forecast Sheet and FORECAST.ETS step by step
- Building a weighted pipeline forecast from the bottom up
- Creating scenario bands and reading confidence intervals
- Structuring a reusable forecast workbook
- Measuring and improving forecast accuracy
- Quick checklist before you present your forecast
- Why a spreadsheet-first approach still works for small teams
- Templates and support that get your forecast running faster
- FAQ
- Sources
Which Excel forecasting method fits your data?
The right method depends on how much history you have and whether your revenue comes from a pipeline of deals or a steady stream of transactions.
- Moving average: works when sales are stable with little seasonality; build it with AVERAGE or OFFSET across a trailing window.
- Weighted moving average: gives recent months more influence using SUMPRODUCT against a weights array, useful when momentum matters more than history.
- Linear trend (FORECAST.LINEAR): fits a straight line through steady growth or decline without seasonal swings.
- FORECAST.ETS and Forecast Sheet: best for regular time series with seasonal patterns. According to Microsoft’s documentation, the function handles up to 30% missing data and can detect seasonality automatically.
The trade-off is simplicity against seasonal accuracy. Simple averages are easy to audit but flatten real patterns, while FORECAST.ETS needs consistent timestamps to work correctly.
How to format and clean your historical sales data
First aggregate recognized revenue by month: multiple legitimate invoices in a month must be summed, not averaged. Reconcile to the general ledger and distinguish missing months from genuine zero-revenue months. Check credit notes, one-off sales and currency conversions.
Your formulas are only as reliable as the data feeding them, so start with a clean layout before touching any forecast function.
- Use two required columns: Date (consistent monthly or weekly steps) and Value (sales or revenue), with Excel recognizing the date format rather than reading it as text.
- Flag or fill missing periods before running statistical models. FORECAST.ETS supports up to 30% missing data, but flagging gaps helps you interpret results honestly.
- Decide whether to forecast at the SKU or product level versus a single blended time series, depending on how granular your decisions need to be.
- Remove duplicate timestamps and confirm a constant step size. Inconsistent intervals are the most common cause of #NUM! or #VALUE! errors.
Running Forecast Sheet and FORECAST.ETS step by step
Exact desktop Excel formula: With monthly dates in A2:A25, revenue in B2:B25 and forecast month in A26, use =FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,1,1,1). The optional arguments select automatic seasonality, interpolated missing points and average aggregation. For multiple transactions per month, aggregate revenue by SUM before applying ETS. These ETS functions are unavailable in Excel for the web and mobile apps.
Once your history is clean, Excel’s built-in tools do most of the heavy lifting.
- Select your date and value columns, then go to the Data tab and choose Forecast Sheet. Pick a line or column chart, set the Forecast End date and Confidence Interval, then click Create. Microsoft notes that starting the forecast before your last historical point gives you a sense of accuracy before trusting future predictions.
- For formula-level control, use
FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]). The target_date is the period you want to predict, values and timeline are your historical ranges, and seasonality set to 1 tells Excel to auto-detect the pattern (or you can specify 12 for a clear monthly cycle). - Pair this with FORECAST.ETS.CONFINT to get interval widths around each forecasted point, which you will use for scenario bands later.
FORECAST.ETS calculates future values using an exponential smoothing algorithm and requires a consistent timeline step to run correctly, per Microsoft. Mismatched ranges or an inconsistent timeline are the usual cause of errors.
Building a weighted pipeline forecast from the bottom up
Working Excel pipeline formulas: With a structured tblDeals, set WeightedBookings per row to =IF([@Status]="Open",[@DealValue]*[@WinProbability],0). For the month starting in A2, expected bookings are =SUMIFS(tblDeals[WeightedBookings],tblDeals[ExpectedCloseDate],">="&A2,tblDeals[ExpectedCloseDate],"<"&EDATE(A2,1)). That is a bookings forecast. Map those deals into a separate expected revenue-recognition schedule and exclude won/lost opportunities already accounted for elsewhere.
Critical correction: Deal amount × win probability estimates expected bookings—not automatically recognized revenue. A $24,000 deal with a 75% probability yields $18,000 of weighted bookings; if its services are delivered evenly over three months starting in November, an illustrative expected revenue schedule is $6,000 per month for November–January. Actual revenue recognition must follow the applicable performance obligations, and expected close dates alone are insufficient.
When revenue comes from discrete deals rather than a steady transaction stream, a bottom-up pipeline is often more defensible than a time-series projection. Financial modeling practice treats bottom-up approaches, built from unit-level drivers like deals or SKUs, as well suited to granular rollups such as per-product revenue.
- Build a Pipeline sheet with one row per deal: Stage, Amount, Expected Close Date, Probability, and a calculated Weighted Amount (Amount multiplied by Probability).
- Use SUMIFS to roll weighted amounts into monthly buckets based on Expected Close Date, giving you a Forecast Month total for each period.
- Use SUMPRODUCT when you need to apply scenario adjustments across multiple criteria at once, such as weighting by both stage and region.
This approach fits best when you have an active sales pipeline or a set of SKU-level forecasts you need to aggregate into one number.
Creating scenario bands and reading confidence intervals
Do not confuse statistical intervals with management scenarios. FORECAST.ETS.CONFINT returns an interval radius. If the point forecast is in C2 and the radius in D2, the interval endpoints are =C2-D2 and =C2+D2. A model-based 95% interval does not guarantee outcomes under structural changes. A ±10% management scenario is a separate assumption, not a 95% confidence interval.
A single forecast line rarely satisfies a stakeholder who wants to know what could go wrong.
- Build quick scenario bands by applying a chosen percentage above and below your base forecast to create Low and High rows.
- Use FORECAST.ETS.CONFINT for a statistical alternative: a 95% confidence interval, the function’s default confidence level, describes a model-based range under its assumptions, not a guarantee that 95% of future business outcomes will fall inside it—especially after structural changes.
- Combine the statistical interval with judgment. If your sales team expects a slow quarter that the model has not seen in history, adjust the band rather than presenting the raw output as final.
Structuring a reusable forecast workbook
Recommended nine tabs: Guide, Settings, History, Pipeline, Revenue Schedule, Forecast, Backtest, Dashboard and QA Checks. Keep editable assumptions separate from formulas. The Revenue Schedule bridges expected deals into months of service delivery; the Backtest stores historical forecasts as they were originally made. A production version should also reconcile actuals to the accounting ledger.
Prevent double-counting: The companion SAMPLE forecast combines an existing contracted revenue base with incremental open-pipeline deals. Do not add an ETS forecast of total sales to those same deals if the historical forecast already includes comparable pipeline demand.
A forecast built in a single messy tab is hard to maintain. A workbook structure with separate sheets for assumptions and outputs makes updates easier.
- Settings: central inputs like growth rate, confidence band percentage, seasonality flag, and currency that drive formulas on every other sheet.
- History: a consistent history of actuals by product or as a single total, formatted with the clean date and value columns described earlier.
- Forecast: formulas pulling from History, using either a growth rate or FORECAST.ETS depending on your method.
- Pipeline: deal-level inputs feeding the weighted bottom-up forecast when that method applies.
- Dashboard: charts for monthly trend, annual totals by product, and forecast versus prior year, alongside key metrics like the forecast and weighted pipeline total.
Measuring and improving forecast accuracy
MAPE warning: ABS((Actual-Forecast)/Actual) is undefined when Actual is zero and unstable when Actual is very small. Also report MAE (average absolute error), WAPE (sum of absolute errors divided by sum of actuals, when meaningful), and signed forecast bias. Store a frozen, out-of-sample forecast created before the actual period occurred; do not grade a model on data it has already seen. Compare accuracy by forecast horizon.
A forecast nobody has checked against reality is just a guess with extra steps.
- Add an Error % column calculated as
ABS((Actual - Forecast)/Actual)for each historical period where you have both numbers. - Calculate MAPE with
AVERAGEacross that Error % column, giving you a single accuracy score you can track over time and compare across methods. - Run a hindcast by starting your forecast a few months earlier than today, then compare the predicted values against what actually happened. Microsoft’s own Forecast Sheet guidance recommends this exact approach to build a sense of accuracy before trusting future predictions.
- Confirm no stockout months are left unflagged, units are consistent, and currency formatting matches across every sheet before you share results.
Quick checklist before you present your forecast
Illustrative Excel dashboard results · USD
November recognized revenue: $79,200 ($70,000 existing contracts + $9,200 from eligible open-pipeline opportunities).
Six-month forecast: $453,039. Weighted open bookings: $48,400, recognized over the relevant delivery months rather than all at close.
Out-of-sample example: MAE $4,833 and WAPE 5.41%. Nine formula and data-control checks passed in the companion SAMPLE workbook. Figures are synthetic.
Run through this list before sending your forecast to anyone outside your spreadsheet.
- Confirm consistent date steps and no duplicate timestamps in your History sheet.
- Document your missing-data policy and double-check seasonality settings match your actual sales pattern.
- Verify pipeline probabilities are current and run your MAPE check one more time.
Pro Tip: If FORECAST.ETS returns a #NUM! or #VALUE! error, check for inconsistent timeline steps or mismatched range lengths first, those two issues cause most failures.
Why a spreadsheet-first approach still works for small teams
Spreadsheets let you iterate on assumptions in minutes without waiting on a new platform license or a training cycle. For many small and mid-size businesses, that speed matters more than the extra automation a dedicated FP&A tool offers. Consider upgrading once your SKU count, deal volume, or number of collaborators makes a single workbook hard to maintain.
Templates and support that get your forecast running faster
Choose by scope: Sales CRM Excel Template tracks deal pipelines; it does not automatically recognize revenue from contracts. The SaaS Metrics Dashboard tracks MRR, churn and other metrics, but is not a complete revenue-recognition model. For a tailored 12-month budget and forecast, Budget and Forecast Build offers specified scope and service tiers. For complex recognition or integrations, scope a custom one-time finance project.
Building a forecast workbook from scratch takes time most owners do not have between running the business and watching the numbers. We built templates to shortcut that setup:
- Sales CRM Excel Template: a template to help you organize your sales pipeline.
- ERP Excel Template: an Excel template for managing business information.
- SaaS Metrics Dashboard Excel Template: a spreadsheet template for working with business metrics.
Before importing CRM exports, reconcile opportunities and validate deal status, expected close month, win probability and duplicate records. A churn-prediction tool is not a substitute for a controlled revenue forecast. When you want the whole workbook built and maintained for you, our one-time finance projects cover everything from a cash flow forecast to a full KPI Dashboard, and our GROWTH finance support can support your forecasting.
FAQ
Are weighted pipeline bookings the same as recognized revenue?
No. Expected bookings use deal value and estimated win probability. Recognized revenue depends on delivery, contract terms and applicable accounting policy. A revenue schedule bridges the two.
What if actual revenue is zero when calculating MAPE?
The percentage error is undefined for that period. Mark it N/M rather than returning 0%, and use MAE or WAPE when appropriate.
Does FORECAST.ETS work in Excel for the web?
No. These ETS forecasting functions require a supported desktop Excel version; a simpler compatible method or a separate model is needed in web or mobile environments.
What is the best Excel function for revenue forecasting?
FORECAST.ETS is generally the strongest built-in option when you have consistent monthly history with seasonal patterns, since it auto-detects seasonality and handles some missing data. For deal-driven businesses without long time series, a weighted pipeline rollup using SUMIFS is often more defensible.
How do I calculate a confidence interval in Excel?
Use FORECAST.ETS.CONFINT alongside FORECAST.ETS to get the interval width around a forecasted value. The function’s default confidence level is 95%, but the resulting range is model-based and can miss structural shocks, promotions or other changes not represented in the data.
What is MAPE and how do I calculate it in Excel?
MAPE stands for Mean Absolute Percentage Error, a common way to score forecast accuracy. Calculate an Error % column as ABS((Actual - Forecast)/Actual) for each historical period, then average that column to get your MAPE score.
Do Frac CFO templates include revenue forecasting built in?
Our Excel templates can support spreadsheet-based financial work. For a fully custom build, our Custom Finance Project service sets up the entire workbook around your specific data.
How much historical data do I need before forecasting in Excel?
The amount of history needed depends on the data and whether it captures a full seasonal cycle before relying on FORECAST.ETS. With less history, a simpler method like a weighted moving average or a bottom-up pipeline rollup tends to produce more trustworthy results.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources
Technical reference: Microsoft Support – FORECAST.ETS and Microsoft Support – FORECAST.ETS.CONFINT. The worked sample uses hypothetical financial data and an explicitly simplified revenue-recognition schedule; it is not an audited forecast.


8 Responses