Instructor-led training -

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Excel Forecasting and Data AnalysisExcel Forecasting and Data Analysis

Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.

Designed for Excel 365

London and UK wide.

Free manuals

We are providing a range of our course manuals free of charge.

Why not share this resource with your friends and colleagues?

Training manual sample

Below are some extracts from our Excel training manuals.

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 forecastKnown_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 

 
  1. To use a Forecast function, first create a data range containing known factual data and responding periods. 

  1. Click in the cell where you want to place the function. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

 

 
 

 

  1. In the Insert function dialog box, locateStatistical category in the Or    select a category: box. 

  1. Click on Forecast. 

  1. Click on the OK button. 

 

 
 

 

  1. In the Function Arguments dialog box, click in the X box. 

  1. Enter the cell reference for the cell with the period information. 

  1. In the Known_y’s box, select the cells containing the known values.  

  1. Make the cell references absolute (Press F4 or add the $ signs) 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. Make the cell references absolute (Press F4 or add the $ signs) 

  1. Click OK. 

 

 
 

 

 

  1.  

     
    Copy down the Forecast function to get the forecast for known periods and unknown periods. 

     

 

 
 

 

 

 

 

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 ConstKnown_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 

 
  1. To use a Trend function, first create a data range containing known factual data and responding periods. 

 

 
 

 

  1. Select the range where you want the result of the function. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

 

 
 

 

  1. In the Insert function dialog box, locateStatistical category in the Or select a category: box. 

  1. Click on Trend. 

  1. Click on the OK button. 

  1. In the Function Arguments dialog box, click in the Known_y’s  box. 

  1. Select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. Press Ctrl Shift Enter. 

 

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 

 
  1. To use a Trend function for forecasting, first create a data range containing known factual data and responding periods and enter the periods for which you want to forecast. 

 

 
 

 

  1. Select the range where you want the result of the function. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

  1. In the Insert function dialog box, locateStatistical category in the Or select a category: box. 

  1. Click on Trend. 

  1. Click on the OK button. 

  1. In the Function Arguments dialog box, click in the Known_y’sbox. 

  1. Select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. In the New_x’s box, select the cells containing the unknown periods. 

  1. Press Ctrl Shift Enter. 

 

 
 

 

 

 

& 

The Trend function will return exactly the same result as the forecast function.  

 

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 

 
  1. Select the cell where the result is to be displayed. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

  1. In the Insert function dialog box, locateStatistical category in the Or select a category: box. 

  1. Click on Slope. 

  1. Click on the OK button. 

  1. In the Known_y’s box, select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. Click on the OK button. 

 

 
 

 

The Intercept 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’s is the array with the known periods (must be a numeric range and not dates, months or years). 

 

 

 

Procedures 

 
  1. Select the cell where the result is to be displayed. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

  1. In the Insert function dialog box, locateStatistical category in the Or select a category: box. 

  1. Click on Intercept. 

  1. Click on the OK button. 

  1. In the Known_y’s box, select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. Click on the OK button. 

 

 
  

 

Now you can calculate the trend and forecast using the trend/forecast equation: 

 

 

Procedures 

 
  1. Select the cell where the result is to be displayed. 

  1. Type =( click in the cell with the slope. Press F4 to lock the cell reference. 

  1. Type * click in the cell with the period number (x). Type ) 

  1. Type + click in the cell with y interceptor. Press F4 to lock the cell reference. 

  1. Press enter. 

  1. Copy down the equation. 

 

 
 

 

The Forecast function, the Trend function and the trend/forecast equation will return exactly the same result and it does not really matter which method used. 

 

 
 

 

. 

 

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 ConstKnown_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 

 
  1. To use a Growth function, first create a data range with data values you know. 

 

 
 

 

  1. Select the array where you want the result of the function. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

  1. In the Insert function dialog box, locateStatistical category in the Or  

               select a category: box. 

  1. Click on Growth. 

  1. Click on the OK button. 

  1. In the Function Arguments dialog box, click in the Known_y’s  box. 

  1. Select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. Press Ctrl Shift Enter. 

 

 
 

 

 

 

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 

 
  1. To use a Growth function for forecasting, first create a data range with periods you want to forecast. 

 

 
 

 

  1. Select the array where you want the result of the function. 

  1. Click on the Formulas tab. 

  1. In the Function Library group, click on the Insert Function button. 

 

 
 

 

  1. In the Insert function dialog box, locateStatistical category in the Or select a category: box. 

  1. Click on Growth. 

  1. Click on the OK button. 

  1. In the Function Arguments dialog box, click in the Known_y’s  box. 

  1. Select the cells containing the known values. 

  1. In the Known_x’s box, select the cells containing the known periods. 

  1. In the New_x’s box, select the cells containing the unknown periods. 

  1. Press Ctrl Shift Enter. 

 

 
 

 

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. 

 

 
 

 

 

 

Thanks. Your download will begin shortly.

Please help us

Share or create a link to this manual today!

Just follow these simple instructions...


Server loaded in 0.61 secs.