How to Build a Forecast Model in Excel

How-to basics · Updated November 20, 2026

To build a forecast model in Excel, list historical results by period, add a clearly labeled assumption for future growth, and use formulas to extend the results into future periods. Start with a simple monthly revenue forecast, check the assumptions, and compare the forecast with actual results as new data arrives.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The formulas and basic worksheet steps below also work in recent Excel versions.
  • Historical data for the item you want to forecast, such as monthly sales, expenses, customers, or units sold. Three periods are enough for the worked example, but more history generally helps you judge patterns.
  • A forecast period and a reasonable starting assumption. The 3% monthly growth used below is illustrative, not a recommendation.
  • A blank workbook or a copy of the workbook containing your source data. Keep the original data unchanged so you can check your model against it.

Step-by-Step

  1. Choose one measure and a useful time period. Decide what the model will predict and whether the data should be monthly, quarterly, or annual. Keep the periods consistent: do not mix monthly actuals with annual forecasts in the same series. This example forecasts monthly revenue for 2025. A revenue model can be a starting point for a broader business forecast, but it does not automatically forecast costs, cash, or profit.
  2. Set up the worksheet with separate inputs and outputs. In a blank sheet, enter Month in A1, Actual revenue in B1, Forecast revenue in C1, and Growth assumption in D1. Put the first day of each month from January to December 2025 in A2:A13, then format those cells as dates such as mmm-yy. Enter historical revenue of 10000, 11000, and 12100 in B2:B4 for January, February, and March. Leave later actual cells blank, and leave forecast cells blank for the historical months. The separation makes it clear which figures are recorded results and which are estimates. Format the revenue columns as currency or number with a consistent number of decimal places.
  3. Review the historical pattern before choosing an assumption. In this example, revenue rises from 10000 to 11000, then to 12100. To calculate the growth from January to February, with January revenue in B2 and February revenue in B3, enter this in an empty cell: =B3/B2-1. Format the result as a percentage; it returns 10%. The same calculation for February to March also returns 10%. Real data may be less consistent, so consider several periods and investigate one-off events before using a growth rate as a forecast assumption. Do not treat a short historical run as proof that the same rate will continue.
  4. Enter assumptions for the forecast months. Put 3% in D5:D13 for this worked example, representing an assumed month-over-month growth rate from April through December. Label the assumption in the column heading and, if helpful, add a note explaining where it came from. Use a different rate when your evidence supports one; for example, a seasonal business may need different assumptions for different months. Make assumptions visible and editable rather than burying them inside formulas. You can enter 3% once and fill down through D13, or type it into each cell.
  5. Calculate the first forecast period. April is row 5, March actual revenue is in B4, and the April growth assumption is in D5. In C5, enter: =B4*(1+D5). With the example inputs, the result is 12463: 12100 multiplied by 1.03. This formula starts with the latest actual value and applies the assumption for the first forecast month. Confirm that the number is plausible before continuing; a reference to the wrong month or a growth rate entered as 3 instead of 3% can produce a misleading result.
  6. Extend the forecast through December. For May and later, carry forward the prior forecast rather than returning to the March actual. With the April forecast in C5 and May’s assumption in D6, the May formula is: =C5*(1+D6). It returns 12836.89 using the example assumptions. Copy C6 down through C13 with the fill handle, or select C6:C13 after entering the formula in C6 and use Ctrl+D on Windows. Excel adjusts the row references as it fills. On Mac, the same formulas work; use the fill handle or the equivalent Fill Down command from the Home tab if your keyboard shortcut differs. The December forecast is about 15787.76 when each monthly assumption is 3%.
  7. Add a forecast total and check it. With the April-to-December forecasts in C5:C13, enter this formula in a suitable summary cell: =SUM(C5:C13). It returns approximately 126612.94 for the example. The total covers nine forecast months only; it does not include the January-to-March actuals. Check the total against the monthly figures and ensure blank or extra rows have not been included by mistake. If you later extend the forecast, update the total range as needed.
  8. Make the model readable and explain its limits. Use a distinct fill color or font style for assumptions, and keep formulas visually separate from typed inputs. Add a short note stating the forecast period, the source of the actual data, and any important assumption. You can select the month and revenue columns and choose Insert > Recommended Charts to create a line or column chart; include actual and forecast series so the transition is visible. Label the forecast clearly. A chart helps communicate the direction, but it does not make the assumptions more reliable.
  9. Refresh and compare as new actuals arrive. When April actual revenue is available, enter it in B5, then decide whether the forecast for May onward should roll forward from that new actual. If so, update the May starting formula to use the latest actual and keep the later forecast formulas linked to the preceding month. Preserve a copy of the previous forecast or record its date before changing assumptions. Comparing forecast and actual values over time helps show whether the model is consistently too high or too low.

Common Mistakes

  • Using an unexplained growth rate. A convenient percentage is not automatically a sound assumption. Note whether a rate is based on historical performance, a plan, or a deliberately chosen scenario, and revise it when evidence changes.
  • Mixing actuals and forecasts without labels. Keep actuals, assumptions, and calculated forecasts in identifiable columns. Otherwise, users may mistake an estimate for a recorded result.
  • Applying the growth rate to the wrong base. Each forecast month should generally build on the prior month in this example. Pointing every formula to March would apply growth from the same starting value rather than compound month by month.
  • Entering percentages at the wrong scale. Enter 3% or 0.03 for a three-percent assumption, not 3. A value of 3 means 300% when treated as a percentage in the formula.
  • Ignoring seasonality, price changes, or one-off events. A single constant growth rate may be unsuitable when sales rise and fall by season, prices change, or an unusual event affects the historical data. Document these effects and use period-specific assumptions where justified.
  • Overwriting source data or hard-coding results. Keep raw historical figures intact and let formulas calculate the forecast. Hard-coded forecast numbers are harder to audit and update.

Tips

  • Keep units consistent. If actual revenue is in dollars, do not enter forecast assumptions in thousands without making that scale explicit.
  • Use a base case plus separate upside and downside versions of the assumptions. For example, compare a lower, central, and higher growth rate in a small scenario table; do not present any scenario as a certainty.
  • Use Excel’s formula bar or select a forecast cell to inspect its references. A quick check of the first and last forecast formulas can catch a fill error.
  • Save dated versions when you make meaningful changes. Forecast accuracy is easier to review when you can see what the assumptions were at the time.
  • For a time series with a clear trend and seasonal pattern, Excel for Microsoft 365 on Windows also has Data > Forecast Sheet. Review the generated forecast and settings, and compare them with your assumptions rather than treating the automated output as a guaranteed result. The feature and its options can vary by Excel version.

Troubleshooting

  • The formula displays as text. The cell may be formatted as Text, or the entry may start with an apostrophe. Change the cell format to General, re-enter the formula, and press Enter. Check that the first character is an equals sign.
  • The forecast is far too large or too small. Check whether the growth assumption is stored as 3% or 0.03, whether revenue is on the intended scale, and whether each formula points to the previous period. Inspect the formula in the first incorrect cell.
  • You see a circular reference warning. A formula may refer to its own cell, directly or through another formula. For this layout, the April calculation should start from B4, while later months should refer to the forecast cell immediately above. Correct the reference rather than enabling iterative calculation unless the model specifically requires it.
  • Some months are missing or out of order. Check that A2:A13 contains one date per month and that dates are real Excel dates, not text that only looks like a date. Re-enter a sample date and apply a date format to confirm.
  • The total does not match expectations. Verify that the total formula includes exactly C5:C13, that each forecast cell contains a number, and that the formula was copied through December. A blank or text value can make the displayed series look complete when a period is not calculated.

Frequently Asked Questions

How much historical data do I need?
There is no universal minimum. Three periods are enough to demonstrate the mechanics, but more representative history usually makes it easier to spot trends and seasonal changes. Use data that matches the forecast frequency where possible.

Can Excel forecast automatically?
Yes. Excel for Microsoft 365 on Windows includes Data > Forecast Sheet for time-based data. It can generate a forecast visualization and worksheet, but you should inspect its settings, check the input series, and compare its output with business knowledge.

Should I forecast revenue, profit, or cash flow?
Start with the measure that answers your planning question. Revenue is relatively simple to demonstrate, while profit and cash flow require additional assumptions such as costs, payment timing, or other inputs. Build and check those relationships separately rather than assuming revenue growth automatically means cash or profit growth.

How often should I update a forecast?
Update it when new actual data arrives or when a material assumption changes. A monthly model is often reviewed monthly, but the appropriate schedule depends on how quickly the underlying activity changes. Keep prior versions if you want to measure forecast accuracy.

What is the difference between a forecast and a budget?
A budget is commonly a planned target or allocation for a period. A forecast is an estimate of what may happen given current results and assumptions. Organizations use the terms differently, so label the purpose of your workbook and explain how its figures should be interpreted.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.