For spending that jumps around, a moving average is usually the steadier forecast, and a linear trend is better only when your spending is drifting in one direction. The reason is that a linear trend treats a one-off spike as a sign of direction, while a short moving average forgets the spike after a few months. The example below puts both on the same twelve months.
What are the two methods doing?
- Moving average: the forecast for next month is the average of the last few months, for example =AVERAGE(B11:B13) for the last three. It ignores direction and follows the recent level.
- Linear trend: the forecast is the end of a straight line fitted through all the months, for example =FORECAST.LINEAR(13,$B$2:$B$13,$A$2:$A$13). It follows direction and weights every month equally.
What do they say on the same twelve months?
Say months 1 to 12 in column B are, in made-up rupees: 38,000; 40,000; 39,000; 41,000; 70,000; 40,000; 42,000; 41,000; 43,000; 42,000; 44,000; 43,000. Month 5 is the one where an annual insurance premium of about ₹28,000 was paid. Everyday spending crept up only slowly.
| Method | Calculation | Forecast for month 13 (₹) |
|---|---|---|
| Last 3 months average | (42,000 + 44,000 + 43,000) / 3 | 43,000 |
| Last 6 months average | (42,000 + 41,000 + 43,000 + 42,000 + 44,000 + 43,000) / 6 | 42,500 |
| All 12 months average | 5,23,000 / 12 | 43,583 |
| Linear trend, all 12 months | Line fitted through months 1 to 12 | 44,424 |
All four land between about ₹42,500 and ₹44,400, because by month 12 the spike is far enough back. The picture changes if you forecast at the wrong moment.
What happens just after the spike?
Now stand in month 6, the month after the premium, and forecast month 7 using only months 1 to 6, which were 38,000; 40,000; 39,000; 41,000; 70,000 and 40,000. The real month 7 turns out to be ₹42,000.
| Method at month 6 | Forecast for month 7 (₹) | Actual (₹) | Error (₹) |
|---|---|---|---|
| Last 3 months average (41,000 + 70,000 + 40,000) / 3 | 50,333 | 42,000 | +8,333 |
| Linear trend through months 1 to 6 | 54,867 | 42,000 | +12,867 |
Both overshoot, but the trend overshoots more, because it reads the spike as the start of a climb. The moving average will also remain wrong for three months, until the spike falls out of its window. That is the price of a short window, and the reason many people use a longer one, or take the spike out first.
Which one should I trust?
- Spending is roughly level with occasional bumps: use a moving average of three to six months.
- Spending has climbed or fallen steadily for most of the year, with no big one-offs: use the linear trend.
- One or two large annual items sit in the data: remove them from the monthly series, forecast the rest, then add them back as yearly cost divided by twelve.
- Strong festival or school-year pattern: neither method sees it, so compare the same months across years instead.
How do I stop one lumpy month from steering the forecast?
- Add a column C called "One-off" and enter the part of any month that is not everyday spending, such as the ₹28,000 premium.
- Add column D, "Everyday", with =B2-C2.
- Forecast from column D. Month 5 becomes 70,000 minus 28,000, which is 42,000, in line with its neighbours.
- Keep a separate yearly list of one-offs and divide its total by twelve for the monthly cushion.
After that clean-up, the three-month average gives (42,000 + 44,000 + 43,000) / 3 = ₹43,000 for the everyday part, and ₹28,000 / 12, about ₹2,333, covers the premium. Together that is roughly ₹45,333 a month to plan around, with the premium already paid for.
What not to do
- Do not pick whichever method gives the lowest number.
- Do not forecast from fewer than six months.
- Do not treat any of these as a budget: a forecast is what the past suggests, a budget is what you choose.
Separating one-off items from everyday spending is easier when each transaction already has a category. SpendixAI lets you set categories and fix wrong ones, so the lumpy items stay visible instead of vanishing into a monthly total.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


