Uploaded September 2026 | Updated November 2026, 2 days ago
How to forecast in Excel when every account needs a different method.
π Get MindsHub free including 5 million tokens every month to get started: bit.ly/mindshub-moth
β¬οΈ Download the example file here and follow along: bit.ly/forecast26file
Extending one trend line across every row is where most Excel forecasts go wrong. Sales might follow a growth rate, purchases move with revenue, and one odd month can drag a trend somewhere the business would never go. So this forecast uses nine methods, picked by what drives each account, and the whole thing recalculates when next month's actuals arrive.
Online sales grow at a set percentage each month. Wholesale sales, vehicle expenses, interest and bank fees follow a linear trend, using FORECAST.LINEAR wrapped around FILTER so only the actual months feed the calculation. Shipping revenue, packaging materials and merchant fees are a ratio to sales, worked out from the historical relationship rather than typed in. Inbound freight and duties are a ratio to purchases. Salaries, superannuation, rent and depreciation come straight from the budget with XLOOKUP. Software subscriptions use a three month average built with LET, FILTER and TAKE. Utilities are the average of every actual month. Insurance, travel and repairs each use an average that excludes one outlier month, done by multiplying two logical tests inside FILTER. Professional fees repeat the same month from the previous quarter, because they spike quarterly.
What holds it together is row 3. Every month is flagged Actual or Forecast, and every formula filters on that flag. Paste in next month's figures, change the flag, and the rest of the forecast recalculates on its own without a single formula being rewritten.
The last section replaces that repeated logical test with a named formula called status, and Find and Replace swaps it into 101 instances at once. Worth knowing that formulas hidden in the Name Manager are harder for a colleague to audit, so think about who else opens the file.
Built in Microsoft 365. FORECAST.LINEAR, FILTER, LET and XLOOKUP also work in Excel 2021, but TAKE does not, so the three month average needs a different approach there.
LEARN MORE
===========
π° EXCEL NEWSLETTER - join 450K+ subscribers here: myonlinetraininghub.com/excel-newsletter-sign-up
π― FOLLOW me on LinkedIn: linkedin.com/in/myndatreacy
π¬ EXCEL QUESTIONS: Get help on our Excel Forum: myonlinetraininghub.com/excel-forum
β² TIMESTAMPS
==============
0:00 Why one forecast formula doesn't fit every account
0:56 Setting up the forecast sheet
1:32 Growth rate: 3% month on month
1:53 Linear trend with FORECAST.LINEAR and FILTER
3:28 Ratio to sales
5:06 Ratio to purchases
7:12 Ratio formulas for the remaining accounts
8:13 Straight from the budget with XLOOKUP
10:19 Three month average with LET, FILTER and TAKE
11:48 Average of all actual months
12:18 Average excluding an outlier month
13:09 Same month last quarter
14:33 Adding next month's actuals
15:15 Simplifying every formula with a named formula
#Excel #ExcelForecast #ExcelFormulas
How to forecast in Excel when every account needs a different method.
π Get MindsHub free including 5 million tokens every month to get started: bit.ly/mindshub-moth
β¬οΈ Download the example file here and follow along: bit.ly/forecast26file
Extending one trend line across every row is where most Excel forecasts go wrong. Sales might follow a growth rate, purchases move with revenue, and one odd month can drag a trend somewhere the business would never go. So this forecast uses nine methods, picked by what drives each account, and the whole thing recalculates when next month's actuals arrive.
Online sales grow at a set percentage each month. Wholesale sales, vehicle expenses, interest and bank fees follow a linear trend, using FORECAST.LINEAR wrapped around FILTER so only the actual months feed the calculation. Shipping revenue, packaging materials and merchant fees are a ratio to sales, worked out from the historical relationship rather than typed in. Inbound freight and duties are a ratio to purchases. Salaries, superannuation, rent and depreciation come straight from the budget with XLOOKUP. Software subscriptions use a three month average built with LET, FILTER and TAKE. Utilities are the average of every actual month. Insurance, travel and repairs each use an average that excludes one outlier month, done by multiplying two logical tests inside FILTER. Professional fees repeat the same month from the previous quarter, because they spike quarterly.
What holds it together is row 3. Every month is flagged Actual or Forecast, and every formula filters on that flag. Paste in next month's figures, change the flag, and the rest of the forecast recalculates on its own without a single formula being rewritten.
The last section replaces that repeated logical test with a named formula called status, and Find and Replace swaps it into 101 instances at once. Worth knowing that formulas hidden in the Name Manager are harder for a colleague to audit, so think about who else opens the file.
Built in Microsoft 365. FORECAST.LINEAR, FILTER, LET and XLOOKUP also work in Excel 2021, but TAKE does not, so the three month average needs a different approach there.
LEARN MORE
===========
π° EXCEL NEWSLETTER - join 450K+ subscribers here: myonlinetraininghub.com/excel-newsletter-sign-up
π― FOLLOW me on LinkedIn: linkedin.com/in/myndatreacy
π¬ EXCEL QUESTIONS: Get help on our Excel Forum: myonlinetraininghub.com/excel-forum
β² TIMESTAMPS
==============
0:00 Why one forecast formula doesn't fit every account
0:56 Setting up the forecast sheet
1:32 Growth rate: 3% month on month
1:53 Linear trend with FORECAST.LINEAR and FILTER
3:28 Ratio to sales
5:06 Ratio to purchases
7:12 Ratio formulas for the remaining accounts
8:13 Straight from the budget with XLOOKUP
10:19 Three month average with LET, FILTER and TAKE
11:48 Average of all actual months
12:18 Average excluding an outlier month
13:09 Same month last quarter
14:33 Adding next month's actuals
15:15 Simplifying every formula with a named formula
#Excel #ExcelForecast #ExcelFormulas










