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 Using The Forecast And Trend Functions

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:

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.

Forecast worksheet

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.

Trend worksheet

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.
Trend worksheet result
This is the end of the part 2 of this tutorial chapter (chapter 3). 

Recommended MS Excel Textbook

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.                                                 

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!