Making predictions with FORECAST
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. You have years in A2:A6 and sales in B2:B6. Which formula predicts sales for the year in D2?
2. What does the first argument (x) in =FORECAST(x, known_ys, known_xs) represent?
3. FORECAST works by doing which of these?
4. Which range is the known_xs in =FORECAST(2030, C2:C10, B2:B10) if column B holds years and column C holds temperatures?
5. Your data covers 2018 to 2024. Which prediction is likely to be more reliable?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=FORECAST(2026, B2:B6, A2:A6) | A=FORECAST(2025, B2:B6, A2:A6) | |
| 2=FORECAST(A8, C2:C10, B2:B10) | BPredicting far beyond the known data (2100 from five years of data) is unreliable, because the trend may not continue that long. | |
| 3Predict next year's sales (2025) when the years are in A2:A6 and the sales are in B2:B6 | C=FORECAST(30, B2:B11, A2:A11) | |
| 4Extending the trendline: estimate the y value at x = 30 using known xs in A2:A11 and known ys in B2:B11 | DEstimates the value for the year 2026 using the trend in the sales figures in B2:B6 and the years in A2:A6 | |
| 5=FORECAST(2025, A2:A6, B2:B6) | EPredicts a y value for the x value typed in cell A8, based on the pattern in the known data | |
| 6=FORECAST(2100, B2:B6, A2:A6) and trusting the answer completely | FThe 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. |
C
Definitions
=FORECAST(x, known_ys, known_xs)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=________(2026, B2:B6, A2:A6)
2.
=FORECAST(____, B2:B6, A2:A6)
3.
=FORECAST(2026, B2:B6, _____)
4.
=FORECAST(__, B2:B6, A2:A6)
5.
=FORECAST(____, B2:B6, A2:A6)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
A2:A6 = years, B2:B6 = sales
B
E2 =FORECAST(D2,A2:A6,B2:B6)
C
D2 = 2024
Question 2
A
A2:A8 = month number 1 to 7
B
B2:B8 = savings in dollars
C
D2 =FORECAST(8,B2:B8,A2:A7)
Question 3
A
A2:A6 = year, B2:B6 = users
B
D2 =FORECAST(B2:B6,A2:A6)
C
D2 shows the 2025 estimate
Question 4
A
A2:A7 = year, B2:B7 = sales
B
D2 =FORECAST(B2:B7,A2:A7,2030)
C
2030 is far past 2024: risky
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
Answer Key
Making predictions with FORECAST
Section A: Quick Questions
Q1
B — =FORECAST(D2, B2:B6, A2:A6)
Q2
A — The new value you want a prediction for, like next year
Q3
B — Extending the trend line of the existing data to estimate a new value
Q4
A — B2:B10
Q5
A — Forecasting for 2025
Section B: Match the Pairs
Q1
=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
Q2
=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
Q3
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)
Q4
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)
Q5
=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.
Q6
=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.
Section C: Definitions
Q1
=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.Section D: Fill in the Blanks
Q1
=FORECAST(2026, B2:B6, A2:A6)Q2
=FORECAST(2026, B2:B6, A2:A6)Q3
=FORECAST(2026, B2:B6, A2:A6)Q4
=FORECAST(A7, B2:B6, A2:A6)Q5
=FORECAST(2035, B2:B6, A2:A6)Section E: Spot the Error
Q1
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).
Q2
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.
Q3
B — The new x value is missing. FORECAST needs the x to predict for as its first argument: =FORECAST(2025,B2:B6,A2:A6).
Q4
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).
Q5
A — The function name is misspelt. It is FORECAST, not FORECASTS, so the spreadsheet would show a #NAME? error.