Build a linear-projection forecasting model

Northwind tracks monthly revenue in a sheet, and at the end of every quarter someone is asked the same question: roughly where are we heading? Eyeballing the numbers gives a vague answer; a spreadsheet formula gives a precise-looking one but hides the maths. A small, transparent forecast you can read and trust sits nicely in between.

This script fits a line of best fit through the revenue history with an ordinary least-squares linear regression, then extends that line three months into the future. It writes the projected months straight back into the sheet so the trend and the forecast sit in one continuous column — easy to chart, easy to explain in a meeting.

What you’ll need

• A Google Sheet whose first sheet holds the revenue history: column A a month date, column B the revenue figure, with a header row. A third column is optional — the script leaves it blank for projected rows.
• At least a few months of history. The more points, the steadier the line — twelve months is a sensible minimum.
• Nothing else. The maths is pure JavaScript; no add-ons or APIs.

The script

// The spreadsheet holding the revenue history.
const REVENUE_SHEET_ID = '1abcRevenueSheetId';

// How many months ahead to project.
const MONTHS_AHEAD = 3;

/**
 * Fits a least-squares line through a revenue history and appends
 * the projected months to the sheet.
 */
function projectRevenue() {
  const sheet = SpreadsheetApp.openById(REVENUE_SHEET_ID).getSheets()[0];
  const [, ...rows] = sheet.getDataRange().getValues();

  // Need at least two points to define a line.
  if (rows.length < 2) {
    Logger.log('Need at least two months of history to project.');
    return;
  }

  // 1. Build the x/y series: x is the row index, y is the revenue.
  const xs = rows.map((_, i) => i);
  const ys = rows.map((r) => r[1]);

  // 2. Fit the line of best fit through the history.
  const { slope, intercept } = linreg(xs, ys);

  // 3. Project the next few months, stepping the date forward each time.
  const lastDate = new Date(rows[rows.length - 1][0]);
  const projected = [];
  for (let i = 0; i < MONTHS_AHEAD; i++) {
    const x = xs.length + i;
    const d = new Date(lastDate);
    d.setMonth(d.getMonth() + i + 1);
    projected.push([d, '', intercept + slope * x]);
  }

  // 4. Append the projected rows below the history.
  sheet.getRange(sheet.getLastRow() + 1, 1, projected.length, 3)
    .setValues(projected);
  Logger.log('Appended ' + projected.length + ' projected months.');
}

/**
 * Ordinary least-squares linear regression. Returns the slope and
 * intercept of the line y = slope * x + intercept best fitting the
 * supplied points.
 */
function linreg(xs, ys) {
  const n = xs.length;
  const meanX = xs.reduce((a, b) => a + b) / n;
  const meanY = ys.reduce((a, b) => a + b) / n;

  // Covariance over variance gives the slope.
  let num = 0;
  let den = 0;
  for (let i = 0; i < n; i++) {
    num += (xs[i] - meanX) * (ys[i] - meanY);
    den += (xs[i] - meanX) ** 2;
  }
  const slope = num / den;
  return { slope, intercept: meanY - slope * meanX };
}

How it works

1. projectRevenue opens the revenue sheet and drops the header row, keeping only the data rows. It stops early if there are fewer than two points — you cannot draw a line through one.
2. It builds two parallel arrays: xs is just the row position (0, 1, 2…) and ys is the revenue from column B. Using the row index as x assumes the months are evenly spaced, which monthly data is.
3. linreg does the actual fit. It computes the mean of each axis, then divides the covariance of x and y by the variance of x — the standard closed-form formula for the slope. The intercept follows from the means.
4. With the line known, it projects MONTHS_AHEAD future points. For each one it advances the date by the right number of months and evaluates intercept + slope * x for the revenue.
5. The middle column is left blank in projected rows so you can tell forecast from actual at a glance — and later type the real figure in beside it.
6. It appends all the projected rows in one setValues call below the existing history, leaving a single continuous series ready to chart.

Example run

Say the sheet holds twelve months of revenue ending in December:

Month	Revenue
2025-01-01	20000
…	…
2025-12-01	31000

If the trend rises by about £1,000 a month, after a run three projected rows are appended:

Month	Revenue	(projected)
2026-01-01	(blank)	32100
2026-02-01	(blank)	33150
2026-03-01	(blank)	34200

Chart column A against the last column and the forecast continues the line straight off the end of the actuals.

Run it

This is a quarter-end job, so run it by hand when you need a fresh forecast:

1. In the Apps Script editor select projectRevenue and click Run.
2. Approve the authorisation prompt the first time.
3. Open the sheet — the projected months are appended below the history.

If you re-run it, delete the previously projected rows first, or each run will stack a new forecast on top of the last one’s projections.

Watch out for

• Linear projection assumes a straight-line trend. It is the wrong tool when revenue is cyclical or seasonal — a December spike will pull the whole line up. For a smarter narrative, see Build an AI weekly-report narrator.
• Re-running stacks forecasts. The script appends without checking, so projected rows from a previous run become input for the next. Clear old projections before each run.
• One outlier month skews the fit. A single unusual figure shifts both slope and intercept — review the history for anomalies before trusting the line.
• Column B must hold plain numbers. A figure entered as £20,000 is text and will break the regression — format the column as a number.
• The model has no confidence interval. It outputs a single point per month with no error band. Treat it as a trend indicator, not a precise prediction.