Free Excel templates

A free forecast bias and accuracy calculator in Excel

Mean error, standard deviation and their ratio, the standard error of the bias, mean absolute error as submitted and after removing the bias, and the next forecast corrected with an honest band: three sheets, no macros.

The file measures a quarterly revenue forecast against eight quarters of actuals. Six times out of eight the forecast was high. The mean error, the bias, is 1.86% against a standard deviation of 2.03%: the bias is almost as large as the noise. Subtracting it, and nothing else, cuts the mean absolute error from 2.41% to 1.49%, a 38% improvement from no new information. Applied to the fourth-quarter forecast of 236.0 million, it brings it down by 4.3 million, to 231.7 million, inside a one-standard-error band 3.3 million wide.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Forecast_Bias_Calculator.xlsx (11 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Financial Planning and Analysis. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Three sheets. Inputs are the forecast errors and the forecast to correct; everything else is a formula.

Inputs
The error of each quarter's forecast against its actual, the forecast to correct, and the third-quarter actual and July forecast.
Accuracy
Observations, how many were high, mean error, sample standard deviation, their ratio, the standard error of the mean, mean absolute error before and after removing the bias, and the corrected forecast with its band.
Checks
Every figure the book prints against the live formula: all pass.

What the worked case returns

MeasureValue
Observations (forecast high)8 (6)
Mean error, the bias1.86%
Standard deviation, the noise2.03%
Ratio of bias to noise0.92
Standard error of the mean0.72%
Mean absolute error, as submitted2.41%
Mean absolute error, bias removed1.49%
Improvement38.0%
Q4 forecast corrected for bias231.7m (from 236.0m)

A forecast that is merely noisy is still a forecast. One whose mean error is nearly as large as its noise is telling you something about the process that produced it, usually the incentive to submit a number that will be beaten. Eight observations are a thin sample, which is why the file shows the band and not just the correction.

How to use it on your own forecast

  1. Enter the percentage error of each past forecast against its actual on Inputs, forecast minus actual over actual.
  2. Read the ratio of bias to noise first: near one, the bias is not an accident.
  3. Compare mean absolute error before and after removing the bias.
  4. Enter the forecast you are about to submit and read the corrected figure and its band.
  5. Add observations as they come in; the standard error narrows with each one.

Questions people ask about it

Is this forecast accuracy calculator really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: averages, standard deviations and absolute errors over the input range.

Does it calculate MAPE?

It calculates the mean absolute error of percentage errors, which is MAPE when the errors are expressed against actuals, and it calculates it twice: as submitted and with the bias removed.

Can I use it on my own forecasts?

Yes, by replacing the inputs. The company and its figures are fictional.

The rest of the files

This template is one of the four companion workbooks for Financial Planning and Analysis: the others read one quarter against five comparators, bridge price, volume and mix, and allocate the corporate pool two ways. Free, like this one.

Open the companion files →

Other free templates

This template comes from Financial Planning and Analysis. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.