Spreadsheets

How wrong was your forecast? Compare forecast with actual spend

Compare forecast with actual spending: add error columns in rupees and percent, average the misses, and use them to set next month's cushion.

Editorial illustration for How wrong was your forecast? Compare forecast with actual spend

To see how wrong a forecast was, put the forecast, the actual spend and the difference side by side, then average the differences. A forecast you never check cannot improve. The average miss, in rupees, tells you how big a cushion to hold the next time you forecast.

How do I set up the columns?

Use one row per month, starting at row 2:

  • A: month.
  • B: forecast, typed in when you made it, never overwritten.
  • C: actual, from your records once the month ends.
  • D: error in rupees, =C2-B2. A positive number means you spent more than forecast.
  • E: error in percent, =IF(B2=0,"",D2/B2).
  • F: size of the miss, =ABS(D2), which ignores the direction.

Keep forecast and actual in separate columns and lock the forecast column once a month is done. Editing yesterday's forecast to match reality defeats the whole exercise.

What does a worked example look like?

MonthForecast (₹)Actual (₹)Error (₹)Error %
Jun42,00045,000+3,000+7.1%
Jul43,00041,000-2,000-4.7%
Aug44,00049,000+5,000+11.4%
Sep45,00044,000-1,000-2.2%
Oct46,00058,000+12,000+26.1%
Nov47,00048,500+1,500+3.2%

These figures are invented. The signed errors add up to ₹18,500, which is about ₹3,083 a month, so on average the actual spend ran above the forecast. The sizes of the misses, 3,000 + 2,000 + 5,000 + 1,000 + 12,000 + 1,500, add up to ₹24,500, so the typical miss is ₹24,500 divided by 6, about ₹4,083.

Which averages should I calculate?

  • Average signed error: =AVERAGE(D2:D7). It tells you about bias. A steady positive number means your forecasts are too low.
  • Average miss: =AVERAGE(F2:F7), about ₹4,083 here. This is the cushion figure.
  • Median miss: =MEDIAN(F2:F7), which is ₹2,500 here. When it sits well below the average, one month, in this case October, is doing most of the damage.
  • Average percentage miss: =AVERAGE of the absolute percentages. Here it works out to about 9.1 percent.

Using the helper column F matters: =AVERAGE(D2:D7) on its own lets overshoots and undershoots cancel out and can make a poor forecast look accurate.

How do I turn the error into a cushion?

Say your forecast for December is ₹47,500. Adding the average miss of about ₹4,083 gives roughly ₹51,600. That is the amount to be ready for. If you want to cover most months, use something nearer the biggest recent miss; here that would be ₹12,000 on top, or ₹59,500. The right level depends on how much slack your month can take, and a cushion for a festival month can sensibly be larger than for a quiet one.

What should I do if the errors are always positive?

  1. Check whether a recurring cost is missing from the forecast: a subscription, a society charge, an annual premium divided by twelve.
  2. Add the average signed error to your next forecast, here about ₹3,083, as a deliberate correction.
  3. Re-run the comparison after three more months to see whether the bias has shrunk.

How many months do I need?

Six months gives you a first view and twelve is better. Fewer than four months of comparisons can be fooled by a single odd month. Keep adding rows as you go; the table is the cheapest forecasting tool you will ever build.

Percent errors are easiest to compare across months of different size, while rupee errors are easiest to act on. If most of your misses are under ₹3,000, a small cushion is enough. If they are scattered between 1,000 and 12,000, as above, the forecast is telling you that something irregular is missing from it.

Having the actual column ready each month is the dull part. SpendixAI can import your statements and give you monthly totals by category, so you only need to type the forecast and read off the difference.

SPENDIXAI JOURNAL

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

Explore your reports