Spreadsheets

FORECAST.LINEAR for next month's spending in Excel

Use FORECAST.LINEAR in Excel on twelve months of totals to project next month's spending, with a worked example and a warning about lumpy months.

Editorial illustration for FORECAST.LINEAR for next month's spending in Excel

FORECAST.LINEAR draws the straight line that best fits your past monthly totals and reads off where that line sits next month. The syntax is =FORECAST.LINEAR(x, known_y's, known_x's): the month you want, your totals, and the month numbers. It needs Excel 2016 or later, and older files use FORECAST, which gives the same answer.

What does linear mean here?

It means the forecast assumes spending moves up or down by about the same amount each month. It does not know about Diwali, school fees or a new EMI. It simply finds the line that stays closest to every point and extends it one step.

How do I set it up?

  1. Put the month numbers 1 to 12 in A2:A13. Using numbers rather than dates avoids the small distortions that unequal month lengths introduce.
  2. Put the twelve monthly totals in B2:B13.
  3. In D2 type the month number you want to predict, 13.
  4. In E2 enter =FORECAST.LINEAR(D2,$B$2:$B$13,$A$2:$A$13)

You can see the line's building blocks with =SLOPE($B$2:$B$13,$A$2:$A$13) for the change per month and =INTERCEPT($B$2:$B$13,$A$2:$A$13) for the starting point. The forecast is the intercept plus the slope times 13.

What does a worked example give?

Say your totals for the last twelve months, in made-up rupees, are 40,000; 42,000; 41,000; 44,000; 43,000; 46,000; 45,000; 48,000; 47,000; 50,000; 49,000 and 52,000. They add up to ₹5,47,000, an average of about ₹45,583. For these twelve points the slope is about ₹1,003 a month and the intercept about ₹39,061, so:

  • Month 13 forecast = 39,061 + 1,003.5 x 13, which is about ₹52,106.
  • That is close to the 52,000 of month 12, because the series has been drifting up steadily.

What happens when one month is lumpy?

Now change month 9 from 47,000 to 77,000, because an annual insurance premium was paid that month. Nothing else changes. The twelve totals now add up to ₹5,77,000 and the slope rises to about ₹1,528 a month, which moves the forecast:

VersionMonth 9 total (₹)Slope per month (₹)Forecast for month 13 (₹)
Ordinary year47,0001,00352,106
With insurance month77,0001,52858,015

One payment moved next month's forecast up by about ₹5,900, even though the premium will not recur next month. This is the main weakness of a line fitted to raw totals.

How far ahead should I trust it?

  • One month ahead is reasonable. Six months ahead is a rough guide at best.
  • Twelve points is a small sample. If six months are missing, forecast only after you have filled them from old statements.
  • A forecast for a festival month, a trip month or a school-fee month will be too low if the pattern is seasonal. See your history for that month before accepting the number.
  • A forecast of a bill you can control is not a plan. Treat it as a question: why is the line rising, and do I want that?

Is there a quick way to check it visually?

Chart the twelve totals as a scatter plot, then right-click a point and choose Add Trendline, Linear. Excel draws the same line. Ticking Display Equation on Chart shows the slope and intercept so you can compare them with the SLOPE and INTERCEPT cells.

Before you use the number

  • Make sure the twelve totals are complete months, with the current part-month left out.
  • Round the result up to a figure you can live with, such as ₹52,500, and treat it as a limit to test against.
  • Compare it with the real total next month. Comparing each forecast with the actual figure afterwards will tell you how much cushion to add.

The forecast is only as good as the totals fed into it. If your monthly totals come from hand-sorted statements, SpendixAI can import the statements and produce category reports you can copy into the sheet.

SPENDIXAI JOURNAL

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

Explore your reports