Regression Analysis - Using The Forecast And Trend Functions

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)

The FORECAST function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• 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). 

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: 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.