Excel Forecasting and Data Analysis

Linear Regression 

Discussion 

Linear Regression analysis is a statistical technique for estimating the relationships among variables. In other words, how do the sales figures change over time? 

If the goal is prediction, or forecasting, linear regression can be used to fit a predictive model to an observed data set of y and x values or known actual data (y) over time (x) (time series data). After developing such a model, if an additional value ofx (a new period) is then given without its accompanying value of y, the fitted model can be used to make a prediction of the value of y.  

The sales figures (y) are known for a number of periods (x) it makes it possible forecasting sales (y) for future periods (x). 

In Excel the linear regression can be calculated using the Forecast function, The Trend function, the Fill-Handle, by calculating the equation: Y = mX + c, and by adding a Trendline to a chart.  

The Data Analysis Tool Regression is an analysis tool to return important information if you are working with Linear Regression such as the Slope, the Y-Interceptor,R-square, and other statistical information. The different terms will be explained later in this workbook.    

You can also calculate the Slope and the Y Interceptor using the functions Slope and Intercept. 

The Forecast function 

Discussion 

Microsoft Excel provides a variety of functions you can use to calculate forecasts. One of them is the Forecast function. The Forecast function can calculate trend, a linear forecast, or linear regression for as many future periods needed based on data from previous periods.   

The Forecast function consists of three required arguments, in the following order: X, Known_y’s, and Known_x’s. X is the period for which you want to calculate the forecast.  Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). 

Procedures 

The Trend function 

Discussion 

The Trend can calculate trend, a linear forecast, or linear regression for as many future periods needed based on data from previous periods.   

The Trend function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range. 

The Trend function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and Const.  Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Trend function is used for forecasting. Const is a logical value specifying whether to force the constant b to equal 0. 

Procedures 

The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets { }.  

If the Trend function is used to forecast: 

Procedures 

The Slope and Intercept Function 

Discussion 

To make it easy to calculate trend or forecast using the equation Y = mX + c you need the slope and y interceptor.  

 The Slope function consists of two required arguments, in the following order:  Known_y’s, and Known_x’s. Known_y’s  is the array with the known values. Known_x’sis the array with the known periods (must be a numeric range and not dates, months or years). 

Procedures 

Procedures 

Procedures 

. 

Exponential Regression 

Discussion 

Sometimes the growth in a model is not linear, but it is exponential. If the growth is exponential Excel has forecasting tools to replace the Forecast and Trend function.  

In the example below the sales has an exponential growth rate. The Forecast function forecast period 11 to 2005333.333. This is not a realistic forecast, because the known sale for period 10 is already £ 2,200,000.00. 

In this section, you will see how you can forecast an exponential growth. 

The Growth function 

Discussion 

The Growth function can calculate exponential growth and exponential growth forecast. The Growth function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range. 

The Growth function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and Const.  Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Growth function is used for forecasting. Const is a logical value specifying whether to force the constant b to equal 0. 

Procedures 

The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets { }.  

If the Growth function is used to forecast: 

Procedures 

If you compare the Growth function with the Forecast function in a chart it is obvious that in this example you get a much more accurate forecast using the Growth function.