← VCE Data Analytics U3O1

Making predictions with FORECAST

Estimates a new y value for a given x by extending the trend of existing data, e.g. predicting next year's sales. It is less reliable the further x is beyond the known data.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 137, 9-10 Digital Technologies: Analysing and visualising data — Module 8, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 48

Understanding Making predictions with FORECAST in VCE Data Analytics U3O1

What it does: The new x is 2025, the known ys are the sales (B2:B6) and the known xs are the years (A2:A6). FORECAST works like extending the line of best fit to x = 30. The ys range comes before the xs range. x is the new input, such as a future year. FORECAST uses the trend in the known data to estimate the y value for it. known_xs is the input values, here the years in column B. The temperatures in C2:C10 are the known_ys. Predictions get less reliable the further x is beyond the known data. Things can change a lot over 36 years.

Common mistakes: The known_ys and known_xs are swapped. The sales (y) range must come first and the years (x) range second: =FORECAST(D2,B2:B6,A2:A6). The x and y ranges are different sizes (A2:A7 has 6 cells, B2:B8 has 7). Each y needs a matching x, so both ranges should end at row 8: A2:A8.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Making predictions with FORECAST to a class →

This module's key terms are also in a printable crossword (Modules 130–148) and word search (Modules 94–148).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
You have years in A2:A6 and sales in B2:B6. Which formula predicts sales for the year in D2?
=FORECAST(D2, B2:B6, A2:A6)=FORECAST(D2, A2:A6, B2:B6)
Matches
=FORECAST(2025, A2:A6, B2:B6)
↓
The known_ys and known_xs ranges are swapped. The years are in A2:A6 and the sales in B2:B6, so the sales range must come first.
Applying
Meanings
Term
=FORECAST(x, known_ys, known_xs)
Meaning
Estimates a new y value for a given x by extending the trend of existing data, e.g. predicting next year's sales. It is less reliable the further x is beyond the known data.
Practice
=FORECAST(2026,B2:B6,A2:A6)
Testing
Gaps
=(2026, B2:B6, A2:A6)
Check Answer
Spots
1A2:A6 = years, B2:B6 = sales
2E2 =FORECAST(D2,A2:A6,B2:B6)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Golden Eagle' badge — High Flyer, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. You have years in A2:A6 and sales in B2:B6. Which formula predicts sales for the year in D2?
A. =FORECAST(D2, A2:A6, B2:B6)
B. =FORECAST(D2, B2:B6, A2:A6) ✓
The order is x, known_ys, known_xs. The sales (y values) come first, then the years (x values).
2. What does the first argument (x) in =FORECAST(x, known_ys, known_xs) represent?
A. The new value you want a prediction for, like next year ✓
B. The total of all the known y values
x is the new input, such as a future year. FORECAST uses the trend in the known data to estimate the y value for it.
3. FORECAST works by doing which of these?
A. Looking up the exact answer that is already in the table
B. Extending the trend line of the existing data to estimate a new value ✓
FORECAST is an estimate based on the pattern in your known x and y data. It is not a lookup, so it can be wrong.
4. Which range is the known_xs in =FORECAST(2030, C2:C10, B2:B10) if column B holds years and column C holds temperatures?
A. B2:B10 ✓
B. C2:C10
known_xs is the input values, here the years in column B. The temperatures in C2:C10 are the known_ys.
5. Your data covers 2018 to 2024. Which prediction is likely to be more reliable?
A. Forecasting for 2025 ✓
B. Forecasting for 2060
Predictions get less reliable the further x is beyond the known data. Things can change a lot over 36 years.

Match the Pairs

=FORECAST(2026, B2:B6, A2:A6) → Estimates the value for the year 2026 using the trend in the sales figures in B2:B6 and the years in A2:A6
=FORECAST(A8, C2:C10, B2:B10) → Predicts a y value for the x value typed in cell A8, based on the pattern in the known data
Predict next year's sales (2025) when the years are in A2:A6 and the sales are in B2:B6 → =FORECAST(2025, B2:B6, A2:A6)
Extending the trendline: estimate the y value at x = 30 using known xs in A2:A11 and known ys in B2:B11 → =FORECAST(30, B2:B11, A2:A11)
=FORECAST(2025, A2:A6, B2:B6) → The known_ys and known_xs ranges are swapped. The years are in A2:A6 and the sales in B2:B6, so the sales range must come first.
=FORECAST(2100, B2:B6, A2:A6) and trusting the answer completely → Predicting far beyond the known data (2100 from five years of data) is unreliable, because the trend may not continue that long.

Key Term

=FORECAST(x, known_ys, known_xs)
Estimates a new y value for a given x by extending the trend of existing data, e.g. predicting next year's sales. It is less reliable the further x is beyond the known data.

Fill in the Blanks

1. =FORECAST(2026, B2:B6, A2:A6)
Hint: Type the function name that estimates a new y value by extending the trend of existing data.
2. =FORECAST(2026, B2:B6, A2:A6)
Hint: Type the new x value to predict for: the year straight after 2025, the last year in the data.
3. =FORECAST(2026, B2:B6, A2:A6)
Hint: Type the known x range. This is the last argument and holds the years in the first column, rows 2 to 6.
4. =FORECAST(A7, B2:B6, A2:A6)
Hint: Type the cell reference that holds the new year, in the row directly below the last year of data.
5. =FORECAST(2035, B2:B6, A2:A6)
Hint: Type an x value ten years after 2025. This is far beyond the known data, so the prediction is less reliable.

Spot the Error

Question 1
A A2:A6 = years, B2:B6 = sales
B E2 =FORECAST(D2,A2:A6,B2:B6)
C D2 = 2024
Line B — The known_ys and known_xs are swapped. The sales (y) range must come first and the years (x) range second: =FORECAST(D2,B2:B6,A2:A6).
Question 2
A A2:A8 = month number 1 to 7
B B2:B8 = savings in dollars
C D2 =FORECAST(8,B2:B8,A2:A7)
Line C — The x and y ranges are different sizes (A2:A7 has 6 cells, B2:B8 has 7). Each y needs a matching x, so both ranges should end at row 8: A2:A8.
Question 3
A A2:A6 = year, B2:B6 = users
B D2 =FORECAST(B2:B6,A2:A6)
C D2 shows the 2025 estimate
Line B — The new x value is missing. FORECAST needs the x to predict for as its first argument: =FORECAST(2025,B2:B6,A2:A6).
Question 4
A A2:A7 = year, B2:B7 = sales
B D2 =FORECAST(B2:B7,A2:A7,2030)
C 2030 is far past 2024: risky
Line B — The arguments are in the wrong order. The new x (2030) must come first, then known_ys, then known_xs: =FORECAST(2030,B2:B7,A2:A7).
Question 5
A F2 =FORECASTS(E2,B2:B9,A2:A9)
B A2:A9 = temperature in C
C B2:B9 = ice creams sold
D E2 = 35, the new temperature
Line A — The function name is misspelt. It is FORECAST, not FORECASTS, so the spreadsheet would show a #NAME? error.

Student with a class code? Join your class →