In Google Sheets, FORECAST gives one projected value and TREND gives a list of them. =FORECAST(x, data_y, data_x) projects a single month, and =TREND(known_data_y, known_data_x, new_data_x) fills several cells at once. Both fit a straight line through your past monthly totals, so they are simple trend fits and not predictions.
How do I set up the totals?
- In A2:A13 put month numbers 1 to 12, and in B2:B13 put the monthly spending totals, taken from a SUMIFS or typed in.
- In D2:D4 put the months you want to project: 13, 14 and 15.
- In E2 type =FORECAST(D2,$B$2:$B$13,$A$2:$A$13) and copy it down to E4.
- In F2 type =TREND($B$2:$B$13,$A$2:$A$13,D2:D4)
Sheets lets an array result spill into the cells below automatically, so the TREND formula in F2 fills F2:F4 with no special key press. If a value already sits in F3 or F4, Sheets reports an error until that cell is cleared.
What do I get with real numbers?
Say your last twelve totals, in made-up rupees, are 25,000; 26,000; 24,000; 27,000; 28,000; 26,000; 29,000; 30,000; 28,000; 31,000; 32,000 and 30,000. They add up to ₹3,36,000, an average of ₹28,000. The line rises about ₹601 a month from a starting point near ₹24,091.
| Month number | FORECAST (₹) | TREND (₹) | Check by hand (₹) |
|---|---|---|---|
| 13 | 31,909 | 31,909 | 24,091 + 601.4 x 13 |
| 14 | 32,510 | 32,510 | 24,091 + 601.4 x 14 |
| 15 | 33,112 | 33,112 | 24,091 + 601.4 x 15 |
FORECAST and TREND agree, as they should, because they fit the same line. The by-hand column uses =INTERCEPT($B$2:$B$13,$A$2:$A$13) and =SLOPE($B$2:$B$13,$A$2:$A$13), which you can type into spare cells to look at the pieces.
How does this compare with a chart trendline?
- Select A1:B13 and choose Insert, Chart. Pick a scatter chart or a line chart.
- Open the Chart editor, go to Customise, then Series, and tick Trendline.
- Set the type to Linear, and under Label choose Use equation. Tick R squared if you wish.
The equation shown should match the SLOPE and INTERCEPT cells. The trendline is good for seeing the shape at a glance; the formulas are better when you need the numbers in cells that feed other calculations.
What goes wrong most often?
- Text instead of numbers in the totals. If a month shows left-aligned figures, convert them to numbers first.
- Ranges of different lengths for y and x. Each must contain the same number of rows.
- Blank cells inside the range, which can distort or break the fit. Fill every month, even with zero.
- Month names used as x. Use plain numbers 1, 2, 3 so the spacing is even.
How wrong can this be?
Test it on your own history. Hide the last three months, fit the line on the earlier nine, project the three, and compare with what happened. If the gap is large, the trend line is a poor guide for your spending, and a moving average or a seasonal look may suit better. A habit that pays off is writing the projection down and comparing it with the actual figure each month.
Next steps
- Decide how many months of history you will use, and stick to it.
- Remove one-off items before fitting.
- Round projections up and treat them as ceilings to test, not targets to hit.
A practical routine: on the first of each month, add the new total to column B, extend the month numbers, and copy the FORECAST formula down one row. Keep each old projection in a separate column so you can later set it beside what actually happened.
If keeping the monthly totals up to date is the part that stalls, SpendixAI can import bank and card statements and produce reports by category, which you can copy into the sheet for the projection.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
