How to Do Forecasting on Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

This article aims to show you four methods you can use to forecast in Excel. If you own or operate a business, this is a common scenario where you are worried about the future performance of your business. In this article, I will show you four techniques you can use to get some idea about the future performance of your business if the current trend continues by forecasting in Excel.


How to perform Forecasting on Excel: 4 Easy Ways

We use a dataset of distance and the amount spent on that distance. We want to know the future value for the miles driven of 127. The predicted amount will be calculated using some useful functions.

Dataset

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Applying FORECAST Function in Excel

The FORECAST function usually calculates or predicts the future value based on a given value. Calculating the existing value this function makes a trend and predicts the future value.

📌 Steps:

  • Initially, select cell F5 and write down the formula.
=FORECAST(E5,C5:C15,B5:B15)

Here,

E5=The driven mile value for which we want to predict the amount

The syntax FORECAST(E5, C5:C15, B5:B15) calculates the future value for the input value with a linear progression and predicts a value for the inserted value.

  • Press ENTER.

Applying FORECAST Function in Excel

  • Finally, it will show you the predicted amount for your inserted distance.

Applying FORECAST Function in Excel


2. Using TREND Function for Forecasting in Excel

The TREND function calculates the values of a given set of X and Y and returns additional Y-values by using the least square method based on a new set of X-values along with a linear trend line. Follow the steps to calculate new Y-values for the new set of X-values.

📌 Steps:

  • At first, select cell F5 and write the formula
=TREND(C5:C15,B5:B15,E5:E8)

The syntax TREND(C5:C15, B5:B15, E5:E8) mainly reads the data of C5 to C15 and B5 to B15 and finds out the relation. Then for the new set of values in E5 to E15, it predicts the new amount spent.

  • Press ENTER.

Using TREND Function for Forecasting in Excel

  • Finally, it will show you the new amount spent for your inserted values.


3. Implementing LINEST Function for Forecasting in Excel

The LINEST function is a statistical function that delivers a linear regression for dependent values with respect to some independent values. This function mainly introduces a line with a regression equation. The simple linear equation for the line is:

y = mx + b (for single range of x)

📌 Steps:

  • Firstly, select the cells E5 and F5 combined and insert the formula
=LINEST(C5:C15,B5:B15)

Here, you have to select the entire value of C5 to C15 and B5 to B15. Now, LINEST will use the least squares procedure and show the slop and Y-axis intercept.

Implementing LINEST Function for Forecasting in Excel

  • Finally, your result will look like the below.


4. Forecasting in Excel Using Fill Handle Tool

First, I am going to show you how you can use Excel’s Fill Handle tool to forecast future results. I hope you know how to use Excel’s Fill Handle tool. But for those who don’t know how to use it, here is the process stated below

📌 Steps:

  • First, select the sales data for the years 2005 to 2007.
  • When this data range is selected, you will find a little green square box at the bottom right corner of the selection. This square box is actually the Fill handle This tool is used to fill cells in a worksheet. That is why it is named Fill Handle Tool.

Forecasting in Excel by Using Fill Handle Tool

  • Hover your mouse pointer over the square box and you see the mouse pointer turn into a black and sharp plus sign. Click and hold your mouse pointer and drag it down.
  • Your result will look like this,

Forecasting in Excel by Using Fill Handle Tool

  • From the comprehensive data, it is apparent that the sales data has an upward trend. I create an XY chart using this data, you see from the chart that the data is in the uptrend. Though there are some trend breakouts in some places; the overall trend is up.


Practice Section

We have provided a Practice Section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. And these are some easy ways to forecast on Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo