EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Statistical Functions in Excel FORECAST Formula in Excel

FORECAST Formula in Excel

Arun Gupta
Article byArun Gupta
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 9, 2023

forcast formula in excel

Introduction to Forecast Formula in Excel

The forecast formula is used to predict or calculate a future value based on past data in financial modeling. It uses linear regression to predict the value. This is one of the in-built Statistical Functions. It can also be used as a worksheet function in a formula with other functions. The user can use this function to calculate future growth, sales, etc. In Excel, 2013 and earlier versions have the Forecast function, which is now changed to FORECAST.LINEAR function. The forecast formula is still available in Excel 2016 and the latest version with backward compatibility.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Syntax:

FORECAST () – It will return future value based on past data. There are three parameters – (x, known_y’s, and known_x’s).

forecast syntax

An argument in the FORECAST Function

x – It is a mandatory parameter for which the user wants to predict the value.

known_y’s an optional parameter; it is the data range or array of dependent data known.

known_x’s – It’s an optional parameter; it is the data range or array of independent data known.

The equation for Forecast: “y=a + bx ”

where: x and y are the samples means AVERAGE (known_x’s) and AVERAGE (known y’s).

How to Use Forecast Formula in Excel?

Excel Forecast Formula is very simple and easy. Let’s understand how to use the Excel Forecast Formula with a few examples.

You can download this FORECAST Formula Excel Template here – FORECAST Formula Excel Template

Example #1 – Basic Forecast Formula in Excel

There are some known Y and X values, so a user wants to calculate the known Y 30 value for known X based on past data, which is known Y and known X. Let’s see how the Forecast Function can solve this problem.

Open MS Excel and go to Sheet1, where the user wants to calculate a Forecast value for 25.

FORECAST Formula in Excel example 1-1

Create one header for the Forecast result to show the function result in cell A11.

FORECAST Formula in Excel example 1-2

Click on cell B11 and apply Forecast Formula.

FORECAST Formula in Excel example 1-3

Now it will ask for x, which is the user wants to forecast the value; select cell A10.

FORECAST Formula in Excel example 1-4

Now it will ask for a known, which the user already has in column A, and select cell A2 to A9.

FORECAST Formula in Excel example 1-5

Now it will ask for a known, which the user already has in column B. Select cell B2 to B9.

FORECAST Formula in Excel example 1-6

Press Enter key.

FORECAST Formula in Excel example 1-7

Summary of Example 1: The user wants to Forecast the value of 25, which is coming after calculation 10.46332128 based on all given data. Which is available in cell B11 as the Forecast result.

Example #2 – Forecast Formula for Company Future Expenses

There is company data for their earnings and expenses from 2004 to 2018, as shown in the table below, so a user wants to calculate the expense value in the year 2020. Let’s see how the Forecast Formula can solve this problem.

Open MS Excel and go to Sheet2, where the user wants to calculate the expense value for the year 2020.

forecast example 2-1        

Create one header for the Forecast result to show the function result in cell A11 and merge it into cell B11.

forecast example 2-2

Click on cell C11 and apply Forecast Formula.

forecast example 2-3

It will then ask for x, which is the user wants to forecast the value and select cell B10.

forecast example 2-4

Now it will ask for known, which the user already has in column B, and select cell B2 to B9.

forecast example 2-5

Now it will ask for known, which the user already has in column B, select cell B2 to B9, and write the formula in cell C11.

forecast example 2-6

Press Enter key.

forecast example 2-7

Summary of Example 2: The user wants to Forecast the expense value for 2020, which is coming after a calculation of 33333.3333 based on all given data. Which is available in cell C11 as the Forecast result.

Example #3 – Data Calculation and Comparing with the Forecast.Linear Formula

Data Calculation for park visitors in ABC Park and comparison with the Forecast.Linear Formula. There is an ABC Park where some data are given, like park visitors from 2014 to 2018, as shown in the table below, so a user wants to calculate the next 3 years park visitor number each year.

Let’s see how the Forecast Formula can solve this problem. Open MS Excel, Go to Sheet 3, where the user wants to calculate the next 3 years’ park visitor number each year.

forecast example 3-1        

Create one header for the Forecast result to show the function result in cell D5 and merge it to cell D6.

FORECAST example 3-2  

Click on cell D7 and apply Forecast Formula.

forcast example 3-3

It will then ask for x, which the user wants to forecast the linear value and apply the formula in cell E7.

forcast example 3-4

Press Enter key.

 FORECAST example 3-5

Summary of Example 3: The user wants to calculate the next 3 years’ park visitor numbers in each year, 2019 onwards, which is available in the D and E columns as the FORECAST result.

Things to Remember

  • The FORECAST function will return a numeric value based on provided known data known_ys and known_x’s. The length of known_y’s and known_x’s must be the same.
  • If x is a no-numeric value, then the FORECAST formula will return an #VALUE!
  • If there is empty, non-numeric, or zero in the data known_y’s and known_x’s, then the FORECAST formula will return #DIV/0!

Recommended Articles

This is a guide to Forecast Formula in Excel. Here we discuss how to use Forecast Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. INDIRECT Formula in Excel
  2. RIGHT Formula in Excel
  3. Excel Forecast Function
  4. MAX Formula in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download FORECAST Formula Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download FORECAST Formula Excel Template

EDUCBA

डाउनलोड FORECAST Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW