EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel Forecast Function

By Karthikeyan SubburamanKarthikeyan Subburaman

Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course
Home Excel Excel Resources Statistical Functions in Excel Excel Forecast Function

Forecast in Excel

Forecast Function in Excel(Table of Contents)

  • Forecast in Excel
  • Forecast Formula in Excel
  • How to Use Forecast Function in Excel?

Forecast in Excel

The forecast function available in excel is the simplest ever forecasting function that we could have. This function predicts the selected iteration sequence, but we must have all the rest knowns sequences, and the rest is known values. The forecast function simply uses the Moving average forecast method to predict the next demand. If we do not have known X, then we have entered the number manually, giving 1 for the first value and so on.

Start Your Free Excel Course

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

FORECAST Formula in Excel

Below is the FORECAST Formula in Excel:

FORECAST Formula in Excel

Arguments of FORECAST Function in Excel :

  • X-: This is a numeric value where we need to forecast new y value
  • Known_ Y-: This Known y_values is a dependent array or range of data.
  • Known_ X-: This Known x_values is an independent array or range of data.

Features of Forecasting:

  • This function is normally concerned with future events
  • It is used as a statistical tool & techniques
  • It is mainly used in predicting futures sales data
  • It analyses the present and past data.

Forecasting:

The forecasting function is mainly used in FMCG, financial, accounting and risk management where we can predict the future sales figure; assume that FMCG company has huge sales in order to find out the next month or next year sales this FORECAST function is very useful to predict the exact result which will be useful for the management in analyzing revenue and other promotion reports.

The forecast function will come under the category of a Statistical function here; we will see a step-by-step procedure on how to use it.

  • Go to the formula menu and click the insert function. A dialogue box will be displayed. Choose the category statistically. Once you choose the statistical, you will find a list of a function. Choose forecast function

Forecast Step 1

In the advanced version, we can use the shortcut wherein the formula menu we can see the more function.

Go to formulas–>Choose More Function–>Statistical–>Forecast

 Forecast Step 2

How to use the FORECAST Function in Excel?

FORECAST Function is very simple to use. Let us now see how to use the FORECAST function in Excel with the help of some examples.

You can download this Forecast Function Excel Template here – Forecast Function Excel Template

Example #1

In this example, we are going to use the FORECAST function, which will predict next month sales data. Assume that a popular FMCG company has sales data month-wise, and the management needs to find out the sales forecasting to get the next future month sales. In this scenario forecasting function will be very useful in pointing out future sales.

Here in the below example, we can see that year-wise sales data has given great revenue; now, we will use the forecast function to predict how the sales will be there for 2009.

FORECAST Example 1-1

In the above sales data, we can see the product sales item that has earned revenue Year wise. In order to predict next year data, we use the FORECAST function. For the year 2008 Product Name “ Grommer” has earned sales value of 6000000 In order to predict the next year, i.e. 2009 Sales value we can use the FORECAST in excel which is shown with the below sales data.

FORECAST Example 1-2

So we have to use the FORECAST in excel to derive the next year sales  value where

D12 is x-Numerical Value.

C3:C11 –Know_Y Value.

D3:D11- Known_X Value.

Result:

FORECAST Example 1-3

The Sales revenue for Groomer Product for the Year 2009 is 6222222. So we can easily predict the sales revenue for a given product.

Let see the above sales data in a dynamic graph format so that it can be viewed easily to understand

In order to display the above sales data in graphical chart format, follow the below steps:

  • Select cell from C3:D12.

FORECAST Example 1-4

  • Select Insert Tab, then select Line chart option. In-Line chart option select first option.

FORECAST Example 1-5

  • The chart has been displayed, and the predicted value for the year 2009 has been shown in the below graph format.

FORECAST Example 1-6

Result:

The below sales data of the line chart clearly shows that for the year 2009, we have more revenue where the blue line goes up, indicating the exact prediction of 6222222.

FORECAST Example 1-7

Example #2

In the below example, we are now going to see month wise sales by using the forecast in excel where we have seen year wise sales in the previous example. Consider the below sales, which have month wise sales data; now, we need to predict the next year month wise sales using the forecast function.

FORECAST Example 2-1

Here we changed some sales value figures to get the accurate forecasting result as shown below.

FORECAST Example 2-2

  • Insert forecast function

FORECAST Example 2-3

  • Select x value as B14

FORECAST Example 2-4

  • Select known y’s from C2:C13

FORECAST Example 2-5

  • Select known x’s from B2:B13

FORECAST Example 2-6

=FORECAST(B14,C2:C13,B2:B13)

FORECAST Example 2-7

The Result is:

FORECAST Example 2-8

Similarly, we find other values

FORECAST Example 2-9

We can see that forecast in excel has predicted some sales values for the year 2019 month-wise, which shows that sales will be increased for the corresponding month.

The above sales and forecasting data can be displayed in a dynamic line chart format to reach the management easily.

To insert the line chart, follow the below steps.

  • Select month and sales data column

FORECAST Example 2-10

  • Go to the insert menu. Choose a line chart type

FORECAST Example 2-11

  • Select 3D line chart graph format

FORECAST Example 2-12

Now select the month and sales data column for graph selection; after that, we need to choose the line chart type, then the line graph chart has been displayed as shown below.

FORECAST Example 2-13

In order to display the forecast graph, select the forecast column along with the month and sales data. So you will get the below forecasting chart as shown below.

 FORECAST Example 2-14

In the above graph, we can see the difference where the blue line shows the previous year sales and the orange line shows the future forecasting sales, which denote that growth will be get increased for the next three months compared to the previous year 2018

Advantages of using FORECAST in Excel:

  • Useful in predicting month wise and year wise sales
  • Commonly used in all FMCG, Financial and Investment companies to determine the growth of future sales.

Recommended Articles

This has been a guide to the Excel Forecast function. Here we discuss the Forecast Formula and how to use the Forecast function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. INT Excel Function
  2. SUBTOTAL in Excel
  3. LN Excel Function
  4. Logical Functions in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Marketing Training (8 Courses, 13+ Projects)4.9
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

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

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

EDUCBA

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

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

Let’s Get Started

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

EDUCBA

Download Forecast Function Excel Template

EDUCBA

Download Forecast Function Excel Template

EDUCBA

डाउनलोड Forecast Function 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

Special Offer - EXCEL ADVANCED Training Certification Learn More