Forecasting and Trend Analysis
Transform historical sales, expense, demand and performance data into disciplined forward-looking estimates using trends, moving averages, linear models, seasonal forecasting and accuracy checks.
After This Chapter, You Will Be Able To
Build, evaluate and present practical forecasts rather than merely extending numbers.
Read Trends
Measure direction, growth, seasonality and unusual variations in historical data.
Apply Forecast Tools
Use moving averages, TREND, FORECAST.LINEAR and Forecast Sheet appropriately.
Build Projections
Create future sales, expense, inventory and workforce estimates with assumptions.
Test Accuracy
Compare forecast and actual values, calculate error and communicate uncertainty.
Understand What a Forecast Can—and Cannot—Do
A forecast is an evidence-based estimate built from historical patterns and documented assumptions. It is not a guaranteed future result.
1 Forecasting, Trend Analysis and Business Decisions
Trend analysis studies how a value changes over time. Forecasting uses that pattern, often together with business assumptions, to estimate future values. A trend explains the past direction; a forecast extends the analysis into a future planning period.
Professional Forecasting Workflow
State the business question, forecast horizon and required frequency.
Clean dates, aggregate records and check missing or abnormal values.
Visualize trend, seasonality, volatility and possible structural changes.
Select a suitable moving-average, linear, growth or ETS approach.
Compare forecast with actual values and document limitations.
Practical Experiment 1: Define a Forecast Question
Convert a vague request into a measurable forecast assignment.
Choose sales, expense, stock, customer visits or staffing as the subject.
Define frequency, historical period, future horizon and responsible decision.
Write one clear question, such as “What may monthly sales be for the next six months?”
2 Preparing Time-Series Data
Forecast quality depends heavily on the historical series. Arrange time in one column and the measured value in another. Use real Excel dates, a consistent interval and one record for each period.
| Requirement | Correct Practice | Risk When Ignored |
|---|---|---|
| Timeline | Monthly, weekly, quarterly or daily points at consistent intervals | Functions may misread spacing or produce distorted patterns |
| Values | One numeric result per period | Text numbers, blanks or mixed units create errors |
| Aggregation | Summarize detailed transactions to the forecast frequency | Uneven detail can overpower meaningful patterns |
| History | Use enough relevant observations to expose recurring behaviour | Very short history can create unstable estimates |
| Outliers | Investigate and document exceptional events | One-time events may be treated as repeatable patterns |
Questions Before Forecasting
- Are dates stored as valid Excel dates?
- Are all periods equally spaced?
- Are missing periods genuinely zero, unavailable or accidentally omitted?
- Did a promotion, shutdown, policy change or new branch create an unusual result?
- Does older data still represent the present business model?
Practical Experiment 2: Audit a Monthly Series
Prepare 24 months of sales for forecasting.
Check real dates, monthly spacing, numeric values and duplicate months.
Create a line chart and mark missing, unusual or promotional periods.
Document how every missing value and exceptional event will be treated.
Measure Direction, Growth and Pattern
Trend calculations convert a visual impression into measurable evidence.
3 Growth Calculations and Trend Indicators
Growth can be measured as an absolute change, a percentage change or a compound rate over several periods. Use the measure that best answers the decision question.
=Current-PreviousAbsolute change in units or currency.
=(Current-Previous)/PreviousPeriod-over-period growth rate. Format the result as a percentage.
=(Ending/Beginning)^(1/Periods)-1Compound growth rate across several equal periods.
=SLOPE(values,timeline)Estimated average change in Y for each one-unit increase in X.
Is the series generally increasing, decreasing or remaining stable?
How large and consistent is the change across periods?
How much do actual values move above and below the underlying direction?
Chart Trendlines
Excel charts can add linear, exponential, logarithmic, polynomial, power or moving-average trendlines, depending on the data and chart type. A trendline is helpful for visual analysis, but the selected form must make business sense.
- Linear: suitable when change is approximately constant.
- Exponential: useful when growth or decline accelerates proportionally.
- Polynomial: may describe turning points, but can overfit short data.
- Moving Average: smooths variation to reveal general direction.
Practical Experiment 3: Compare Growth and Trendline Evidence
Analyze monthly sales direction in three ways.
Create month-over-month percentage growth and an overall compound growth rate.
Add linear and moving-average trendlines to the same historical chart.
Write whether recent momentum agrees with the longer-term pattern.
Reveal Underlying Direction with Moving Averages
Moving averages reduce short-term noise by averaging a fixed number of recent periods.
4 Simple Moving Averages
A three-period moving average uses the current and two previous values. As the formula moves down, the averaging window also moves. This creates a smoother series that can be compared with actual results.
=AVERAGE(B2:B4)First three-period moving average. Copy downward as the window moves.
=AVERAGE(B2:B7)Six-period moving average, usually smoother but slower to react.
=AVERAGE(OFFSET(B8,-2,0,3,1))Dynamic three-period example; simpler direct ranges are preferable when possible.
=Actual-MovingAverageDeviation from the smoothed trend.
| Window | Behaviour | Typical Use |
|---|---|---|
| 3 periods | Responds quickly but retains more fluctuation | Short operational monitoring |
| 6 periods | Balances responsiveness and smoothing | Monthly business review |
| 12 periods | Strong smoothing and slower reaction | Annual pattern in monthly data |
Moving Average Forecast
A basic next-period forecast can equal the average of the most recent selected periods. This is practical for relatively stable series without strong trend or seasonality. Select the window using business rhythm and testing, not convenience.
Practical Experiment 4: Test Three Forecast Windows
Compare 3-, 6- and 12-month moving averages.
Create all three moving-average columns beside the monthly actual values.
Chart actuals and the three smoothed lines on the same timeline.
Choose the window that balances responsiveness and stability for the business.
Project a Straight-Line Trend with Excel Functions
Linear methods estimate future values when historical change is reasonably represented by a straight line.
5 FORECAST.LINEAR, TREND, SLOPE and INTERCEPT
Linear forecasting fits a straight line to known X and Y values. The timeline may use period numbers or valid Excel dates. Period numbers are often easier to audit because the unit change is explicit.
=FORECAST.LINEAR(E2,$B$2:$B$25,$A$2:$A$25)Predict one Y value for the future X stored in E2.
=TREND($B$2:$B$25,$A$2:$A$25,E2:E7)Return several projected values for a new range of X values.
=SLOPE($B$2:$B$25,$A$2:$A$25)Calculate the estimated change per timeline unit.
=INTERCEPT($B$2:$B$25,$A$2:$A$25)Calculate the estimated Y value when X equals zero.
How to Interpret a Linear Model
- A positive slope indicates an upward direction; a negative slope indicates decline.
- The projected line assumes the historical linear relationship continues.
- A sudden structural change can make the historical slope inappropriate.
- Forecasting far beyond the observed range increases uncertainty.
- Use actual-versus-forecast testing before relying on the model.
Practical Experiment 5: Six-Month Linear Sales Forecast
Project the next six periods from 24 months of actual sales.
Create period numbers 1–30 beside the monthly timeline.
Use TREND or FORECAST.LINEAR for periods 25–30.
Compare the slope with recent growth and document whether linear continuation is reasonable.
6 Growth-Based and Exponential Projections
Some series grow by a relatively constant percentage rather than a constant amount. In such cases, a growth or exponential model may better represent the historical pattern.
=GROWTH($B$2:$B$13,$A$2:$A$13,E2:E7)Return values along an exponential growth curve for future X values.
=Previous*(1+GrowthRate)Assumption-driven projection using an approved growth rate.
=Previous*(1+MonthlyRate)^MonthsCompound projection across multiple future periods.
=LN(Value)Natural-log transformation can help analyze exponential relationships.
Use Forecast Sheet and ETS for Repeating Patterns
Seasonal methods can capture recurring monthly, quarterly, weekly or other equally spaced patterns.
7 Excel Forecast Sheet
Forecast Sheet creates a new worksheet containing historical values, forecast values and a chart. In supported desktop versions of Excel, it uses exponential smoothing and can display confidence intervals around the forecast.
Standard Procedure
- Arrange one timeline column and one numeric values column.
- Select both series.
- Choose Data → Forecast Sheet.
- Select line or column visualization.
- Set the forecast end date and review options.
- Create the sheet, inspect the generated table and validate the result.
| Option | Purpose | Practical Guidance |
|---|---|---|
| Forecast End | Sets the final future date | Match the business planning horizon |
| Confidence Interval | Shows a range around the point estimate | Communicate uncertainty, not a guaranteed boundary |
| Seasonality | Uses automatic or manually specified cycle length | Manual selection requires real business evidence |
| Timeline Range | Historical dates or numeric intervals | Use equally spaced points |
| Fill Missing Points | Controls treatment of gaps | Confirm the business meaning before accepting defaults |
| Aggregate Duplicates | Combines repeated timeline values | Use an aggregation matching the measure |
Practical Experiment 6: Create a Seasonal Forecast Sheet
Forecast monthly demand from at least two years of history.
Chart the series and identify recurring high and low months.
Create a six-month Forecast Sheet with a confidence interval.
Explain the point estimate, lower bound, upper bound and operational action.
8 FORECAST.ETS and Supporting Functions
FORECAST.ETS predicts a future value using historical values and an equally spaced timeline. It is useful when the series may include seasonality. Optional arguments control seasonality, missing data and duplicate aggregation.
=FORECAST.ETS(E2,$B$2:$B$25,$A$2:$A$25)Forecast the value for target date E2 using automatic seasonality.
=FORECAST.ETS.SEASONALITY($B$2:$B$25,$A$2:$A$25)Return the detected seasonal cycle length.
=FORECAST.ETS.CONFINT(E2,$B$2:$B$25,$A$2:$A$25)Calculate the confidence interval radius for the target point.
=FORECAST.ETS.STAT($B$2:$B$25,$A$2:$A$25,1)Return a selected statistical measure from the ETS algorithm.
Data Requirements
- Timeline and values must contain corresponding observations.
- The timeline should use a consistent step, such as every month or every week.
- Duplicate timeline points require appropriate aggregation.
- Future target dates should continue beyond the historical series.
- Aggregate transaction-level data before forecasting at monthly or weekly level.
Practical Experiment 7: Compare Linear and ETS Forecasts
Apply two models to the same monthly sales series.
Create six future values using FORECAST.LINEAR and FORECAST.ETS.
Chart actual, linear and ETS series together.
Identify which model better represents recurring seasonal movement and why.
Measure Error and Communicate Uncertainty
A professional forecast is tested against known actual values before it is used for planning.
9 Backtesting and Accuracy Measures
Reserve a recent portion of historical data as a test period. Build the model using earlier data, forecast the reserved periods and compare the predicted values with actual results. This is called backtesting.
=Actual-ForecastSigned error. Positive and negative errors may offset each other.
=ABS(Actual-Forecast)Absolute error for each period.
=ABS((Actual-Forecast)/Actual)Absolute percentage error; use caution when actual equals zero.
=SQRT(AVERAGE(ErrorRange^2))Root Mean Squared Error, which gives more weight to large errors.
Model Comparison Rules
- Compare models on the same historical training and test periods.
- Use an error measure suitable for the business scale and presence of zero values.
- Prefer the simplest model that performs adequately and can be explained.
- Investigate large errors instead of hiding them.
- Refresh the model when recent behaviour changes materially.
Practical Experiment 8: Backtest Two Forecast Methods
Use the first 18 months to forecast the next six known months.
Separate training and test periods without changing the historical values.
Calculate MAE and MAPE for linear and moving-average forecasts.
Select the model using accuracy, stability and business interpretability.
10 Forecast Reporting and Governance
Management should receive the forecast together with assumptions, confidence range, historical accuracy, risk factors and recommended actions. Presenting only one future number creates false certainty.
| Report Component | Question Answered |
|---|---|
| Forecast subject and horizon | What is being predicted, at what frequency and for how long? |
| Historical period | Which observations were used? |
| Selected method | Why is this method appropriate for the pattern? |
| Point estimate and range | What is the expected value and how uncertain is it? |
| Accuracy record | How well did the method perform in backtesting? |
| Assumptions and risks | What could cause the forecast to fail? |
| Action and review date | What decision follows, and when will the forecast be refreshed? |
Select a Suitable Forecasting Approach
Use the business pattern and output requirement to receive a practical method recommendation.
Sales and Expense Forecasting Model
Build a management-ready six-month forecast using historical monthly data and validated model selection.
Create 24–36 months of Date, Sales, Operating Expense and key business-event fields.
Calculate growth, create charts and identify trend, volatility, seasonality and outliers.
Create 3- and 6-month moving-average forecasts and one linear forecast.
Use Forecast Sheet or FORECAST.ETS where the data and Excel version support it.
Reserve recent known months, calculate MAE/MAPE and compare model performance.
Show selected forecast, confidence or risk range, assumptions, actions and review date.
Submission Requirements
Save a professional workbook with separate sheets for Raw_Data, Analysis, Forecast_Model, Accuracy_Check and Management_Summary.
Use clear labels, formulas, charts, assumptions and protected output areas.
Include at least two methods and an accuracy comparison.
Write a concise management note explaining the selected method and action.
Complete These Forecasting Tasks
Use a fresh dataset so you can demonstrate independent understanding.
Define subject, timeline, history, future horizon and decision owner.
Check dates, intervals, duplicates, blanks, outliers and structural changes.
Calculate absolute, percentage and compound growth measures.
Create a line chart with one suitable trendline and interpretation.
Compare at least two window lengths and explain the selected one.
Project several future values using TREND or FORECAST.LINEAR.
Create a Forecast Sheet or ETS forecast where available and appropriate.
Calculate MAE and another suitable error measure for two methods.
Present a point forecast together with lower and upper planning values.
State assumptions, recommended forecast, risks and next review date.
Forecasting Errors Students Should Avoid
Most weak forecasts fail because of poor data, inappropriate methods or overconfident interpretation.
Wrong Habits
- Forecasting before cleaning and aggregating the timeline
- Treating a forecast as a guaranteed future result
- Using a linear model for clearly seasonal data without testing
- Choosing the method that produces the most attractive number
- Ignoring unusual events and structural business changes
- Forecasting too far beyond the available history
- Reporting one number without assumptions or uncertainty
- Never comparing forecasts with actual results
Correct Habits
- Define the decision, horizon and data frequency first
- Use equally spaced, validated historical observations
- Explore trend, seasonality and volatility visually
- Compare simple and advanced models objectively
- Backtest using a reserved historical period
- Calculate and monitor forecast error
- Communicate point estimate, range, risks and assumptions
- Refresh the model when actual behaviour changes
Test Your Forecasting Knowledge
Answer all 12 questions and submit the quiz to view explanations.
1. What best describes a forecast?
2. Why should a timeline use consistent intervals?
3. Which formula calculates period-over-period percentage growth?
4. What is a key effect of increasing the moving-average window?
5. Which function predicts one value along a straight-line relationship?
6. What does the TREND function return?
7. When is a seasonal forecasting method most relevant?
8. What does a confidence interval communicate?
9. What is backtesting?
10. Why can MAPE be problematic when actual values equal zero?
11. Which practice gives the fairest model comparison?
12. What should accompany a professional management forecast?
Remember These Forecasting Principles
Review these points before moving to Solver and Optimization.
Start with the Decision
Define subject, frequency, horizon and action before selecting a method.
Prepare the Timeline
Use valid, consistent and documented historical observations.
Explore the Pattern
Measure trend, growth, volatility, seasonality and exceptional events.
Match the Method
Use moving average, linear, growth or ETS according to the data behaviour.
Backtest Accuracy
Compare predictions with known actuals using appropriate error measures.
Report Uncertainty
Present assumptions, point forecast, range, risks, action and review date.