Regression analysis helps you to use the relationship you
create to predict for future development and growth. The FORECAST and TREND
functions are used to perform linear regression analysis. These functions find
the straight line relationship between one or more independent variables and a
single dependent variable.

Independent variables are the variables needed to determine
a prediction. The dependent variable is the variable for which you have current
information or values but wish to predict its future state or values.

Regression analysis can only be useful when you believe that
changes in one set of variables is caused by the interaction between the
dependent and independent variables.

This is the second part of this tutorial chapter. In this part, I will explicitly explain aand illustrate the business applications of the FORECAST and TREND function.

It is advisable
that you first read the part 1:

PRACTICAL AND BUSINESS APPLICATIONS OF BUILT-IN FUNCTIONS IN MS-EXCEL. Also see the

**full tutorial course content of this Microsoft Excel Tutorial Series**.##
**HOW TO USE THE FORECAST
FUNCTION TO PREDICT FUTURE SALES IN EXCEL**

Forecasting is the use of the known values, events or
information about an event to predict its future occurrence or state. With
MS-Excel, you can easily apply sophisticated statistical analysis techniques to
your data. You can also use these techniques to stock prices, project expenses
and other business trends.

The FORECAST function calculates, or predicts, a future
value by using existing values. The predicted value is a y-value for a given
x-value. The known values are existing x-values and y-values, and the new value
is predicted by using linear regression. You can use this function to predict
future sales, inventory requirements, or consumer trends.

The syntax is:

**FORECAST(x, known_y's, known_x's)**

•

**X**(required) is the data point for which you want to predict a value.

•

**Known_y's**(required) is the dependent array or range of data.

•

**Known_x's**(required) is the independent array or range of data.

#### NOTES:

• If**x**is nonnumeric, FORECAST returns the #VALUE! error value.

• If

**known_y's**and

**known_x's**are empty or contain a different number of data points, FORECAST returns the #N/A error value.

• If the variance of

**known_x's**equals zero, then FORECAST returns the #DIV/0! error value.

##
**PRACTICAL APPLICATIONS
OF FORECAST FUNCTION IN SALES IN MS-EXCEL**

Like I said earlier, the FORECAST function can be applied in
various field. I will illustrate how the function is used to predict Sales and
how to call up the function.

###
**QUESTION:**

The table shown in the spreadsheet is a Sales Record of
Joe-links Services for the year 2016. Use the data in the figure below to
predict the sales for the month of August.

###
**SOLUTION TO THE
FORECAST QUESTION**

Prepare your worksheet as shown above and type the following
formula in cell C10:

**= FORECAST(B10,C3:C9,B3:B9)**press Enter.

Excel does the underground calculation and produces a value

**17885.71429**as the result.##
**HOW TO USE THE TREND
FUNCTION TO PREDICT FUTURE SALES IN EXCEL**

A sales trend determines whether sales will continue to
increase or decrease and how fast (i.e. rate of increase or decrease). The
TREND function is used to predict the results for the next several months even
if you don’t have reliable results from the previous months, unlike the
FORECAST function which is used to determine for the immediate next month.

The function returns values along a linear trend. Fits a
straight line (using the method of least squares) to the arrays

**known_y's**and**known_x's**. Returns the**y-values**along that line for the array of**new_x's**that you specify.
The TREND function Syntax is:

**TREND(known_y's, [known_x's], [new_x's], [const])**

The TREND function syntax has the following arguments:

•

**Known_y's**(required) - The set of y-values you already know in the relationship y = mx + b.
If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable.

If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.

•

**Known_x's**(required) - An optional set of x-values that you may already know in the relationship y = mx + b.
The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector (that is, a range with a height of one row or a width of one column).

If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.

•

**New_x's**(required) - New x-values for which you want TREND to return corresponding y-values.
New_x's must include a column (or row) for each independent variable, just as known_x's does. So, if known_y's is in a single column, known_x's and new_x's must have the same number of columns. If known_y's is in a single row, known_x's and new_x's must have the same number of rows.

If you omit new_x's, it is assumed to be the same as known_x's.

If you omit both known_x's and new_x's, they are assumed to be the array {1,2,3,...} that is the same size as known_y's.

•

**Const**(optional) - A logical value specifying whether to force the constant b to equal 0.
If const is TRUE or omitted, b is calculated normally.

If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx.

#### NOTES:

• You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A.

• Formulas that return arrays must be entered as array formulas.

• In Excel Web App you cannot create array formulas.

• When entering an array constant for an argument such as known_x's, use commas to separate values in the same row and semicolons to separate rows.

##
**PRACTICAL APPLICATION
OF TREND FUNCTION IN SALES**

The TREND function is more complicated than the FORECAST
function. I will use a simple case of a sales record to illustrate the
application of the TREND function.

###
**QUESTION:**

The table shown in the spreadsheet is a Sales Record of
Joe-links Services for the year 2016. Use the data in the figure below to
predict the sales for the months of September to December.

###
**SOLUTION TO THE TREND
QUESTION**

Prepare your worksheet as shown above and type the following
formula in cell C11:

**= TREND(C3:C10,B3:B10,B11)**press Enter.

Use the fill handle to get the values for the subsequent
months.

Excel does the underground calculation and returns a set of values
in the appropriate cells as shown below.

This is the end of the part 2 of this tutorial chapter (chapter 3).

##

Now go over to the Part 3 of this Excel tutorial chapter:

## Recommended MS Excel Textbook

##
Get this book (Kindle format): **Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.

Click **Here** to know more about the book.

Now go over to the Part 3 of this Excel tutorial chapter:
Get this book (Kindle format):

**Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.**Here**to know more about the book.

**BUSINESS APPLICATIONS OF EXCEL SUMIF, COUNTIF AND CONCATENATE FUNCTIONS**

If this tutorial post helped you, then it will also help
your friends. Kindly click the one of the share buttons to share this post with
your friends.

Click

**Here**to subscribe to get our latest tutorial post delivered to your email.
## No comments:

## Post a Comment

WHAT'S ON YOUR MIND?

WE LOVE TO HEAR FROM YOU!