≡ Menu

How to do Forecasting in Excel

In this tutorial I will teach you how to use do forecasting in excel by using forecast functions. Using this function, excel 2013 can actually predict some values based on the historical data.

This function calculates and make advance analysis to your data.

Forecast function is under the statistical section in the formula tab in excel.

The syntax of Forecast function looks like:

=Forecast(x,known y’s,known x’s)

Where:

X: for which you are looking for some value.

Known Y’s: historical values which you know.

Known X’s: historical arguments which you know.

Having explain the syntax of the formula seems to be very difficult but I will give some examples to this and things will be starting to make sense.

Example 1: Sales data for 3 years and we want to predict net income for the next 3 years.

We will start by getting the data and applying the forecast function in cell D9.

X is the Sales amount you want to the prediction for. In this case its $600,000.

Known y’s is the range of the values you already know with regards to net income. In this case its cell D6 to D8.

Known x’s is the range of the values for the independent variable from which you want the prediction for. In this case its sales data with range C6 to C8.

After applying the formula, following would be the result.

We will use the rest of the prediction in the same manner.

The values return would be the following:

Example 2: Forecasting a date of a company based on historical data, when the company will achieve 1 million sales.

Example 3: Dates forecasting

You can also forecast dates. Here’s an example.

In this table you have data of sales in the end of each month. You want to know where you will have 1 000 000 $ of sales.

x is 1 000 000$

known y’s is historical dates

known x’s is historical sales

Formula is: =FORECAST(C10;D6:D9;C6:C9)

The end result would not be in the date format. You have to convert it to date format.

To do this, right click the cell and click on “Format Cells”

Under Category section, click on “Date” and press Ok.

The end result will give you the date in which your company will achieve 1 million sales.

General Tips and Error Cases

If the x argument is a text or not numerical, the FORECAST function will return the #VALUE! Error.

If known_y’s and known_x’s are left blank or contain a different number of data points, the FORECAST function will return the #N/A error.

If the variance of known_x argument is zero, the FORECAST function will return the #DIV/0! Error.

{ 0 comments… add one }

Leave a Comment