EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • 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
  • Log in
  • Sign Up
Home Finance Finance Resources Finance Formula Forecasting Formula
 

Forecasting Formula

Madhuri Thakur
Article byMadhuri Thakur

Updated July 10, 2023

Forecasting Formula

 

 

Forecasting Formula (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Formula
  • Examples

What is the Forecasting Formula?

The forecasting formula, a statistical function primarily used in forecasting quantitative business variables ( generally Sales / Revenue, Profit, etc.), is used to forecast the future value based on the existing or given dataset.

Formula

FORECAST(x, known_y’s, known_x’s)

Where,

  • “x”:  The number for which a forecast needs to be made.
  • Known_y’s:  The group or array of dependent data. It is required in the formula to proceed further.
  • Known_x’s: The group or array of independent data is also mandatory for the results.

Types of Forecasting Formula

There are multiple formulas for Forecast in excel. For example, when we write =Forecast, a series of functions like Forecast appears in the forecast dropdown list, such as FORECAST.ETS, FORECAST.ETS.CONFIDENT, FORECAST.LINEAR. The widely used variant is FORECAST / LINEAR FORECAST. The formula of linear forecast helps find out the result when there is a linear or close to a linear pattern in the data given. The most popular forecast function is the Forecast in excel.

Example of Forecasting Formula (With Excel Template)

Let’s take an example better to understand the Forecasting Formula calculation in a better manner.

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

Forecasting Formula – Example #1

The following data has been taken from a grocery store in California. Find the forecasted data for the 10th year via the Excel Forecast function. 

grocery store in California

Solution:

The above data is given to us, which is complete as it contains the x, known_x, and known_y. So we can move ahead and calculate the forecasted data for the 10th year. Then, we will apply the formula in the cell corresponding to the 10th  year or adjacent to the 10th year.

Forecasting is calculated using the formula given below

FORECAST(x, known_y’s, known_x’s)

Forecasting Formula - 1.2

The Formula applied would be putting = sign in the cell by typing Forecast, a dropdown list would appear, and we will choose the last option stating the only Forecast. The formula or syntax would be (x, known_y’s, known_x’s). Here we will select the cell containing ten as x and the entire array of Revenue( From $100 – $185) as known_y’s. Similarly, we will select the array of Year ( from 1 – 9) and press Enter. In excel, to go to the next part of the formula, (,) is used. The result of $210 is displayed as the forecasted revenue for the 10th year.

Forecasting Formula – Example #2

One of Japan’s companies is considering acquiring an American company based out of Birmingham, Alabama. Therefore, the data for the year 2011 is required for the company’s valuation. So, calculate the forecasted data of Sales and Operating Expenses for 2011 using the Forecasting formula in excel.

companies in Japan

Solution:

We will follow the following steps to determine the forecasted result of Sales and Operating Expenses for 2011.

  • First of all, we will check the completeness of the data. Then, in the given scenario, we have all the required inputs to apply the formula in its entirety.
  • Next, we will use the ( = ) sign in the cell, denoting sales for the 2011 year, and choose Forecast from the dropdown list.
  • After then, we will select the cell containing 2010 as x and the entire array of sales from ($1,00,000 – $2,40,000) as known_y’s and year ( 2000 – 2010 ) as known_x’s to calculate the forecasted figure. Finally, the result will be displayed in the requisite cell after hitting Enter.
  • A similar process would be used for Expenses, but we will choose the array of Expenses in the same way we took for income in an erstwhile way.

Forecasting is calculated using the formula given below

FORECAST(x, known_y’s, known_x’s)

Forecasting Formula - 2.2

Forecasting Formula – Example #3

Similar to the previous example, Calculate the forecasted figure denoted by ( ??) for the year 2020 in the Income and Expenses column.

Forecasting Formula - 3.1

Solution:

We will follow the following steps to determine the forecasted result of Sales and Operating Expenses for 2011.

  • First of all, we will check the completeness of the data. Then, in the given scenario, we have all the required inputs to apply the formula in its entirety.
  • Next, we will use the ( = ) sign in the cell, denoting sales for the 2011 year, and choose Forecast from the dropdown list.
  • After then, we will select the cell containing 2010 as x and the entire array of sales from ($1,00,000 – $2,40,000) as known_y’s and year ( 2000 – 2010 ) as known_x’s to calculate the forecasted figure. Finally, the result will be displayed in the requisite cell after hitting Enter.
  • A similar process would be used for Expenses, but we will choose the array of Expenses in the same way we took for income in an erstwhile way.

Forecasting is calculated using the formula given below

FORECAST(x, known_y’s, known_x’s)

Forecasting Formula - 3.2

Explanation

The formula for Forecasting can be calculated by using the following steps:

If we need to calculate a predictive estimate of future numbers, we could use the Forecast function in the Microsoft Excel program.

Step 1: First of all, we require a complete data set where there are dependent and independent variables of which the forecast would be made.

Step 2: When we have the relevant data, locate the cell where the result would be displayed.

Step 3: Once the cell is identified, apply the Forecast Formula in excel to get the result or forecasted number.

Key Notes:

  • The length of known_x’s and known_y’s should be the same. If that is not the same or one or more arrays are unfilled, we will get an error (#N/A! error).
  • If the cell contained in the known_x’s array is zero, an error is known as #DIV/0! An error would be displayed.
  • The error code, #VALUE! The error will be displayed if the forecasted value is other than numerical.

Relevance and Uses of Forecast Function

The Forecasting function helps calculate future or required data based on past numbers. The Forecast function is generally used in calculating predictions of multiple financial variables such as Sales, Profit, Expenses, Free Cashflows, etc. The results fetched help analysts to value the company, whether listed or not, and make an informed decision.

Recommended Articles

This is a guide to Forecasting Formula. Here we discuss how to calculate the Forecasting Formula along with practical examples. We also provide Forecasting, a downloadable excel template. You may also look at the following articles to learn more –

  1. Calculation of Average Fixed Cost Formula
  2. How to Calculate Bank Efficiency Ratio?
  3. Example of Product Cost Formula
  4. What is Elasticity?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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

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

EDUCBA

Download Forecasting Formula Excel Template

EDUCBA
Free Investment Banking Course

Corporate Valuation, Investment Banking, Accounting, CFA Calculator & 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 Login

Forgot Password?

EDUCBA

डाउनलोड Forecasting Formula Excel Template

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW