ICAEW.com works better with JavaScript enabled.
Exclusive

Exploring Charts (graphs) in Excel blog series

Exploring Charts (Graphs) in Excel - Part 11: Adding Trendlines through data series and using the functions =FORECAST, =TREND, =SLOPE and =INTERCEPT

Author: John Tennent

Published: 10 Feb 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

In previous blogs we have focused upon displaying factual data, in this blog we will explore a few features and functions that assist in predicting future values.

The financial services standard disclaimer is important to bear in mind ‘Past performance does not guarantee future results’.  Therefore, in creating forecasts extrapolated from past data can be a useful contributory factor, but obviously cannot be relied upon.

Trendlines

To illustrate some of the features and functions we will use the following data series with the aim of forecasting the final months of the year.

Excel screenshot

First plot the data on a 2D line chart (See Blog 3 for how to complete this).

Excel screenshot

We can now add a Trendline to find ‘the line of best fit’ through the data.  Right click on the line and on the menu the option Add Trendline appears.

Excel screenshot

Several features are available.  First choose the profile of the data you want to trend.  We are using a simple Linear relationship in this example, but on the menu above you can see five other alternatives. For financial data the other option commonly used is Moving Average (see below).

The Linear Trendline uses least squared regression.  This is great for data that has an obvious trend.  If the data has outlying values these can distort the ‘line of best fit’ and produce a somewhat meaningless forecast.

In the Forecast options the number of Forward and Backward periods can be entered.  On the chart the X axis will be extended to display the extrapolated Trendline over these periods.

At the bottom of the dialog box there is an option to display the equation for the ‘line of best fit’ on the chart.

Excel screenshot

To extract the formula for the equation can be achieved with the following Excel functions:

=SLOPE(Known Ys, Known Xs)

Gradient of the trendline line

=SLOPE(B4:I4,B3:I3)

11.083

=INTERCEPT(Known Ys, Known Xs)

Where the trendline line crosses the Y axis

=INTERCEPT(B4:I4,B3:I3)

-3

The function =LINEST can also be used to derive both values in adjacent cells (the result spilling one cell to the right of where the formula is written).

=LINEST(Known Ys, Known Xs)

=LINEST(B4:I4,B3:I3)

11.083

-3

Using this formula, we can derive a forecast for period 9:

(11.083 x 9) – 3 = 96.75

The forecast results can also be derived using two other functions:

=FORECAST(X, Known Ys, Known Xs)

=FORECAST(J3,B4:I4,B3:I3)

96.75

=TREND(Known Ys, Known Xs, X)

=TREND(B4:I4,B3:I3,J3)

96.75

Moving Averages

To use the Moving Average Trendline you will need monthly data rather than cumulative.

The same data set as above, but now displayed monthly would be as follows:

Excel screenshot

This Trendline using a four-period average (to smooth the peaks and troughs).  The default when the dialog box is first opened is set to two.  There is no equation available to show on the chart hence that area of the dialog box is greyed out.

Excel screenshot

Another way to derive moving average data that provides greater clarity on the values calculated and displayed is by using the Data Analysis function (this option has a host of other statistical data options too).

On the Data Ribbon there is a section called Data Analysis, but this is only available if the Analysis Toolpak has been enabled.

To enable the Analysis Toolpak go to the File Menu, select Options and select Add-ins.  At the bottom of this dialog box will be a section titled Manage: in the drop down select Excel Add-ins and press the button Go…  finally check the box for Analysis Toolpak.

Excel screenshot

Once opened from the right-hand side of the Data Ribbon the Moving Average calculation can be set up as follows:

The Input Range is the data, Interval is the 4-period calculation, and the Output Range is where the data is to be placed once calculated.

Don’t be put off by the # signs, it is in the first three periods as it has insufficient data to calculate a 4-period moving average for these months.  These points will not be displayed when plotted (more on this in the next blog).

In our final blog we will round off the series by covering a range of useful features including: controlling zeros and missing data, hanging lines using =NA, using conditions to highlight attributes (peaks, troughs), saving a chart as a picture to use in other applications.

 

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250