Spot financial trends using this MS Excel tool and make advanced predictions for investing

Forecasting or prediction is a critical aspect of financial planning that helps in achieving goals through active management and informed decision-making.

Prediction typically uses analysis of historical trends and patterns, also known as trend forecasting. Additionally, associations between variables are used for prediction. Interlinked variables in the macroeconomy and financial markets help predict the value of one variable based on changes in another.

Examples of such interlinkages include the impact of interest rates on currency, where rising rates make a currency stronger. Similarly, inflation affects household expenditure, with rising prices leading to increased household spending. Changes in bond yields impact debt fund returns, with rising bond yields reducing returns.

Conceptually, making forecasts involves applying various statistical techniques, which can overwhelm non-technical investors. However, MS Excel simplifies these calculations with its built-in functions, requiring minimal inputs and providing instant forecasts. One well-known function for prediction in Excel is the FORECAST function. It uses linear regression for prediction and is best suited to forecast general trends or directions. However, it ignores other data patterns or variations.

In real life, data exhibits patterns beyond the long-term trends, such as seasonality. It’s characterised by regular, predictable patterns that recur over specific periods (weekly, monthly, quarterly, or yearly). Identifying such patterns improves the overall accuracy by indicating when variables will deviate from the general trend. Seasonality is commonly observed in corporate sales, which tend to spike during festivals, and household expenditures, which increase during holidays. Other examples include an increase in hotel bookings during holiday seasons, as well as spikes in electricity consumption during the summer months.

To incorporate such seasonal patterns, MS Excel introduced the FORECAST.ETS function with the 2016 version. The refined function calculates forecasts using the concept of exponential smoothing, where recent observations are given more weightage. The older FORECAST function is now known as FORECAST.LINEAR in the newer Excel versions.

FORECAST.ETS is best suited to non-linear data sets with seasonal or other repetitive patterns. It is capable of automatically detecting seasonal pattern. However, if it fails to detect any seasonal or non-linear pattern in a given dataset, the function reverts to linear forecast. Another advantage of the refined function is the treatment of missing data and duplicate data in the historical time series dataset.

Let us consider a hypothetical example to demonstrate the FORECAST.ETS function. The quarterly household expenditure from the March 2021 quarter to March 2024 quarter is given in the Excel screenshot. Since the dataset is small, one can easily see that the expenditure is the highest in the fourth quarter of every calendar year, which is the festive season quarter. The overall trend seems to be rising and there is a presence of seasonality in the December quarter.

The function’s forecasts are not always 100% accurate, but provide a basic idea of the future. For reliable results, historical data should include at least 30 observations, spanning weeks, months, quarters, or years.

FORECAST.ETS (new) versus FORECAST.LINEAR (old)
The forecasts using the refined function are in column C. Providing the inputs as shown in the top box on the right, the FORECAST.ETS generates Rs.156 thousand forecasts for expenditure for the June 2024 quarter. The ‘Target_date’ is the period for which the forecast is needed (June 2024 quarter). ‘Values’ are the historical expenditure values and ‘Timeline’ is the historical time frequency given in the dataset. There are other inputs like ‘Seasonality’, ‘Data_completion’ and ‘Aggregation’ that are optional and can be left blank by the user.

In the top box, FORECAST.ETS, changing the ‘Target_date’ to September 2024 quarter, that is, A16 cell, and keeping other inputs constant, the forecast obtained for the quarter works out to Rs.149 thousand. Similarly, other forecasts in column C are worked out.

In the box at the bottom, FORECAST. LINEAR, function is used for obtaining the forecasts of household expenditure on the same data in column D. The input requirements are similar. However, the name of the headers differ here.

X is the period for the forecast. ‘Known_ys’ is the historical expenditure data, ‘Known_xs’ is the historical time period provided in the dataset.

For the June 2024 quarter, the linear forecast obtained is Rs.155 thousand. Changing the value of X while keeping other inputs constant, other linear forecasts are obtained in column D.

Actual vs Forecasts
One can look at the chart (left) to see the superiority of FORECAST.ETS. The blue line shows the historical values, the red line shows the forecasts obtained using the linear function, whereas the brown line shows the forecasts obtained using the exponential function. The exponential function can predict and detect seasonality, whereas the linear function is only predicting the direction or the long-term trend.