To forecast revenue in Excel, first match the method to the pattern in your data: use an average or run rate for stable revenue, a linear or percentage-growth formula for a clear trend, FORECAST.ETS for recurring seasonality, and a driver-based model for operating plans and scenarios. No single method is best for every business. Prepare consistent historical periods, compare more than one forecast, and test the results against actuals before relying on them.
Prepare your revenue data first
Start with a summary table containing one row per period and one revenue value per row. Monthly, quarterly, and annual data should each use consistent intervals; do not mix calendar months, fiscal periods, or partial periods. Keep raw transactions separate from the summary used for forecasting.
| Month | Revenue | Customers | Orders | Average order value |
|---|---|---|---|---|
| Jan-2024 | 42,000 | 420 | 350 | 120 |
| Feb-2024 | 44,500 | 445 | 365 | 122 |
| Mar-2024 | 48,000 | 470 | 385 | 125 |
For the simplest formulas, use a period index in column A and historical revenue in column B. For date-aware forecasts, put real Excel dates in column A and revenue in column B. For example, A2:A25 can contain historical month dates, B2:B25 the corresponding revenue, and A26:A31 the future month dates.
- Chart the history to spot trend, seasonality, outliers, plateaus, missing periods, and sudden changes.
- Investigate large contracts, acquisitions, discontinued products, refunds, and unusual promotions instead of assuming they will recur.
- Exclude a partial current month, model it separately, or annualize it transparently; do not compare it with complete months as if it were a full period.
- Decide whether a missing period means unknown data or genuinely zero revenue. Those cases should not be treated the same.
Microsoft says its Forecast Sheet expects consistent timeline intervals and can tolerate up to 30% missing data points; summarizing data before forecasting generally produces more accurate results. Its duplicate-timestamp options aggregate entries, with averaging as the default, but summing transaction revenue into monthly totals is usually more logical. Review the settings rather than accepting an aggregation that changes what revenue means. See Microsoft’s Forecast Sheet guidance.
Recommended Free Tools
#1 Best Overall
Choose a method that fits the question
| Data pattern or planning question | Method to try | What it assumes |
|---|---|---|
| Stable revenue with little trend or seasonality | Average or run rate | Recent or typical revenue is a useful baseline. |
| Revenue changes by a fairly steady dollar amount | FORECAST.LINEAR |
The trend is approximately a straight line. |
| Revenue changes by a fairly steady percentage | GROWTH |
Growth compounds at a broadly consistent rate. |
| Revenue depends on measurable business inputs | TREND, LINEST, or a driver model |
The selected inputs help explain revenue and can be forecast or controlled. |
| Revenue repeats a seasonal pattern | FORECAST.ETS or Forecast Sheet |
Historical periods are consistent and recurring seasonality is informative. |
| Management needs target, downside, and upside plans | Scenario and unit-economics model | Revenue can be built from explicit operating assumptions. |
A historical forecast estimates what a pattern suggests; a budget may instead encode management targets and decisions. A useful plan can use a statistical forecast as a reference and a driver model to show what has to happen to reach a target.
1. Average or run-rate forecast
An average or run rate is a simple baseline, not a guarantee of accuracy. If historical monthly revenue is in B2:B13, the average is:
=AVERAGE($B$2:$B$13)For a rolling six-month average, use the most recent six months, such as:
=AVERAGE(B8:B13)A latest-period run rate simply carries forward the latest actual:
=B13To annualize a latest monthly run rate, multiply it by 12:
=B13*12This is a mechanical annualization, not a seasonal forecast: it assumes the latest month is representative of the other months. A weighted recent average can give newer periods more influence:
=SUMPRODUCT(B8:B13,{1,2,3,4,5,6})/SUM({1,2,3,4,5,6})In a maintained workbook, store those weights in cells so reviewers can see and change them. Averages are useful for stable recurring revenue, short-term planning, or a benchmark, but they ignore trend and seasonality and can be skewed by abnormal periods. A long historical average may lag a fast-growing business; a run rate may overstate revenue after a temporary spike.
2. Linear forecast with FORECAST.LINEAR
A linear forecast estimates a straight-line relationship between time and revenue. It fits when the change is roughly a constant dollar amount per period, rather than a constant percentage. Microsoft describes the equation as a + bx, with coefficients derived from linear regression.
For example, put period numbers 1 through 12 in B2:B13, revenue in C2:C13, and the next period number in B14. Then enter:
=FORECAST.LINEAR(B14,$C$2:$C$13,$B$2:$B$13)You can use dates as the known x-values instead, provided they represent evenly spaced periods:
Rank #2
=FORECAST.LINEAR(A14,$B$2:$B$13,$A$2:$A$13)See Microsoft’s FORECAST.LINEAR reference. The older FORECAST function remains for compatibility, but Microsoft identifies it as deprecated in Office 2016 and later and recommends FORECAST.LINEAR: Microsoft’s legacy function reference.
Plot actuals with the fitted trend line and test the formula on historical periods before using it forward. A straight line can produce negative revenue, miss seasonal or curved patterns, and extend an old trend through a price change, capacity limit, or market saturation. An outlier can also change the fitted slope materially.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →3. Percentage-growth forecast with GROWTH
GROWTH fits an exponential curve: it assumes revenue changes by a relatively consistent percentage, so dollar increases compound. With period index in B2:B13, revenue in C2:C13, and a future period in B14, use:
=GROWTH($C$2:$C$13,$B$2:$B$13,B14)For multiple future periods, provide a range of future x-values:
=GROWTH($C$2:$C$13,$B$2:$B$13,B14:B19)Current dynamic-array versions may spill results into adjacent cells; older versions may require array-entry behavior. A simpler planning assumption can apply a stated growth rate in F2:
=B13*(1+$F$2)For annual compounding over a specified number of years:
=B13*(1+$F$2)^YearsAheadUse an exponential fit when percentage growth is more meaningful than fixed dollar additions, such as in some early-stage or subscription businesses. Growth rarely stays constant indefinitely, however; a small change in rate can create an implausibly large long-range result. Exponential methods are generally unsuitable when revenue is zero or negative.
4. Regression and driver-based forecasting
Revenue often has measurable operating drivers: customers, orders, traffic, conversion, average order value, price, sales capacity, marketing investment, or churn. A one-driver regression can estimate revenue from a driver. If historical driver values are in B2:B13, revenue in C2:C13, and a future driver value in B14:
=TREND($C$2:$C$13,$B$2:$B$13,B14)For multiple explanatory variables, LINEST can return regression coefficients and statistics. For example, if advertising spend is B2:B13, customers are C2:C13, and revenue is D2:D13:
=LINEST(D2:D13,B2:C13,TRUE,TRUE)The output is an array, so interpreting and applying coefficients requires care. Microsoft’s forecasting functions reference lists Excel’s forecasting functions; LINEST is more flexible but less accessible than a single-variable forecast formula.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Rank #3
For an operating plan, a transparent revenue equation may be more useful than a statistical fit. An ecommerce example is:
=Traffic*Conversion_Rate*Average_Order_ValueA recurring-revenue model might calculate customers first:
Ending_Customers=Beginning_Customers+New_Customers-Churned_CustomersThen calculate revenue from ending customers and average revenue per customer. A driver model helps answer what must change to hit a goal, but it does not make uncertain assumptions certain. Regression shows association, not proof that a driver causes revenue; too few observations, interdependent drivers, changing relationships, or an inaccurate driver forecast can make a precise-looking result unreliable.
5. Seasonal forecast with FORECAST.ETS or Forecast Sheet
Excel’s ETS forecasting uses the AAA version of Exponential Smoothing to account for level, trend, and seasonality. For monthly values in B2:B25, dates in A2:A25, and a future date in A26:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25)If the annual monthly seasonal cycle is known, the optional seasonality argument can specify 12:
=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12)Automatic detection is usually the sensible starting point unless there is a reason to override it. Microsoft documents 1 as automatic seasonality detection and 0 as no seasonality, where the prediction is linear: FORECAST.ETS function reference. Microsoft recommends at least two complete seasonal cycles when seasonality is specified manually. If the algorithm does not detect significant seasonality, its prediction reverts to a linear trend.
Create a Forecast Sheet
- Arrange dates or periods in one column and the matching revenue values in the adjacent column.
- Select both columns, then open Data and choose Forecast Sheet in the Forecast group.
- Choose a line or column chart and set the forecast end date.
- Open Options to review seasonality, confidence interval, missing-point treatment, duplicate aggregation, and statistics.
- Select Create. Excel creates a new worksheet with historical values, predicted values, confidence intervals, and a chart.
Microsoft documents Forecast Sheet for Excel for Microsoft 365, Excel 2024, and Excel 2021 for Windows; availability and menu labels can differ on Mac, the web, or older editions. See Microsoft’s current Windows instructions.
Use the interval and data options carefully
The Forecast Sheet’s default confidence level is 95%. The interval represents a range for future points under the model’s assumptions; it is not a promise that the forecast is correct and does not encompass every management decision or external shock. With the function, calculate a confidence interval using:
=FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25)Subtract that result from the point forecast for a lower bound and add it for an upper bound. Microsoft’s Forecast Sheet documentation says missing values can be interpolated by default or treated as zero; use zero only when the business actually had zero revenue. It also describes a 30% missing-data tolerance. Ensure duplicate timestamps are summarized with an appropriate rule before interpreting the result.
ETS is useful for recurring patterns such as retail, ecommerce, holidays, or weather-related demand, but it can struggle with too little history, inconsistent intervals, major one-off events, or a structural break such as a new pricing model or acquisition. A much longer forecast horizon than the historical record also deserves caution.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.6. Scenario and unit-economics model
A scenario model builds revenue from explicit assumptions instead of extrapolating only from the past. For a recurring business, suppose B2 is beginning customers, B3 new customers, B4 monthly churn rate, and B5 average revenue per customer. A simplified ending-customer calculation and revenue formula are:
=B2+B3-(B2*B4)=(B2+B3-(B2*B4))*B5This assumes churn applies to beginning customers; use a more detailed cohort or timing model if customer additions and losses occur throughout the month. For transaction revenue, calculate orders from traffic and conversion, then multiply by average order value:
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11=Traffic*Conversion_Rate*Average_Order_ValueKeep assumptions visible, identify their owner and update date, and check that drivers are not counted twice. A driver model can omit external demand cycles unless seasonality is explicitly included.
Compare downside, base, and upside assumptions
Use Data → What-If Analysis → Scenario Manager to save and switch between assumption sets. For example, define different customer counts, conversion rates, and average order values for downside, base, and upside cases. Microsoft says a scenario can contain multiple variables but is limited to 32 values; a Data Table can analyze one or two variables. Details are in Microsoft’s What-If Analysis overview.
Work backward from a revenue target
Use Data → What-If Analysis → Goal Seek when you know the desired revenue and want Excel to solve for one input, such as the number of customers required. Goal Seek handles one variable; Solver is the more flexible add-in when multiple variables must change. Scenario and Goal Seek tools help explore assumptions; they do not verify that an assumption is achievable.
Validate before using the forecast
Backtest against periods you already know
Hold out recent historical periods, fit a method using earlier data, and compare the resulting forecasts with the actuals. For example, fit the first 18 months and compare predictions for months 19–24. Calculate absolute error with:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →=ABS(Actual-Forecast)For percentage error, guard against a zero actual:
=IF(Actual=0,"",ABS((Actual-Forecast)/Actual))Mean absolute error is the average of the absolute-error cells:
=AVERAGE(error_range)For root mean square error, a helper column of squared errors is straightforward: calculate =(Actual-Forecast)^2 for each row, average those cells, then take the square root. A higher in-sample R-squared does not establish that a method will predict future periods well.
Compare methods and reconcile to operations
Compare at least a baseline, a statistical method that fits the data, and a driver-based view where business inputs are available. If methods disagree substantially, treat the gap as a prompt to examine their assumptions, not as proof that one is wrong. Check the result against sales capacity, inventory or service capacity, pricing, churn, contract timing, pipeline conversion, marketing spend, product launches, seasonality, and cash-collection timing. Revenue forecasts estimate recognized or earned revenue; they do not automatically forecast when cash will be collected.
Present downside, base, and upside cases where decisions depend on uncertain inputs. An ETS confidence interval describes statistical uncertainty under its model assumptions; management scenarios also reflect decisions, external events, and assumptions that may not be in the time series.
Free tools Windows power users keep installed
One-click scans. No signup required.
When Excel is not enough
Excel is often adequate for a small or moderate forecast that can be reviewed in a workbook. A dedicated planning system may be more appropriate when the organization needs many products or geographies, complex hierarchies, automated data pipelines, multiple contributors, approvals, audit trails, version control, or probabilistic forecasts. Project-based and enterprise businesses with lumpy contract timing may need separate models for bookings, backlog, and delivery rather than a smooth monthly extrapolation. Advanced forecasting systems can offer methods such as ARIMA, Prophet, or XGBoost, but a more sophisticated algorithm does not automatically improve accuracy; the data, assumptions, and validation still matter. See Microsoft’s overview of forecast algorithm types.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
