Spreadsheets

Moving average or linear trend: which forecast suits bumpy spending?

Moving average or linear trend for bumpy monthly spending? Both run on the same 12 months with one insurance spike, with results and a rule of thumb.

Editorial illustration for Moving average or linear trend: which forecast suits bumpy spending?

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.

MethodCalculationForecast for month 13 (₹)
Last 3 months average(42,000 + 44,000 + 43,000) / 343,000
Last 6 months average(42,000 + 41,000 + 43,000 + 42,000 + 44,000 + 43,000) / 642,500
All 12 months average5,23,000 / 1243,583
Linear trend, all 12 monthsLine fitted through months 1 to 1244,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 6Forecast for month 7 (₹)Actual (₹)Error (₹)
Last 3 months average (41,000 + 70,000 + 40,000) / 350,33342,000+8,333
Linear trend through months 1 to 654,86742,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?

  1. 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.
  2. Add column D, "Everyday", with =B2-C2.
  3. Forecast from column D. Month 5 becomes 70,000 minus 28,000, which is 42,000, in line with its neighbours.
  4. 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.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports