Simple data forecasting using Google Sheets
Hi, my name is David Beckham, you can call me Beckham. I know you are coming from the universe that people called me, was a professional English footballer. But, in my universe, I am a newbie data analyst, nice to know you!
In my universe, we have a company named Apple too, yes, that one, the difference is my boss’s name is Steve Works, and we are selling fruits here. He asked me to forecast our stock price using the data here. If you want to see it, but don’t know what to do to download it, please look here, I’ve downloaded it for us.
As a newbie, I know some simple data forecasting methods that we can execute easily using Google Sheets. I will show you how I’ve done it if you want to know.
Data visualization
Before we dive into how to forecast data in Google Sheets, let’s start with data visualization, so we could know the trend line of the data from 5 years ago until now (the data we are using between 23rd Oct 2017 and 21st Oct 2022.
Some information we’ve got from the visualization above is:
- The price is quite stable between October 2017 and September 2019.
- The price has a positive trend line starting October 2017 until January 2022, even though a drastic decline happened between February 2020 and April 2020.
- The price is stable again but had a declining tendency start January 2020 until now.
- Adding the trend line, we could see it has a positive linear tendency.
Ok, without further ado, let’s start our data forecasting!
FORECAST function
Did you know that we have the FORECAST function in Google Sheets? The common usage of this function is like this:
=FORECAST (x, data_y, data_x)xshould be filled by the date we want to forecast.data_yshould be filled by our historical data (the price).data_xshould be filled by our historical data (the date).
In this case, let’s say we want to forecast the stock price for the date 24th October 2022. If you asked, “Why not 22nd or 23rd October 2022?” The answer is that the stock market is not active on weekends, that’s why we jumped to Monday, which is 24th October 2022.
It’s important to have the domain knowledge to do data analysis, not only technical skills.
As we can see, the value produced by the FORECAST formula starts from $170.33 and keep increasing. It’s a sudden increase from the last data we have that only has a value of $147.27. It happens because FORECAST uses the linear trend in general. In common sense, we can’t say it’s proper to use this result. Let’s try to recreate the forecast, but now using more recent data starting from 1st January 2022, so the forecast can get the current trend line.
The stock price is quite stable, but in general, it has a declining trend line, starting from 1st January 2022 until 21st October 2022.
Nice one! The result of our last forecast I think more reasonable to use rather than the previous one. You can see it by the red line in our graph (the forecast data), it makes a straight line because the FORECAST function in Google Sheets implements linear forecast. I know it’s not a great forecast, but hey, it’s still better to give a piece of information to our working peers that by using the linear forecast method, the data should be like this in a way.
Since the forecasts are not always accurate and simply deduce the data we have (only date and stock price). It doesn’t take into the possibility of trends changing due to unknown factors. We can only use this function as a simple narrative.
Simple Moving Average (SMA) and Simple Moving Median (SMM)
If I’m not mistaken, this is the first forecast method I’ve learned in college. It’s a basic method that we can create easily in Google Sheets. Simply, moving average based on Wikipedia here,
A calculation to analyze data points by creating a series of averages of different subsets of the full data set.
The moving average has many types, such as Simple MA, Cumulative MA, Weighted MA, Exponential MA, and so on. In this case, we are using the Simple MA. Without further ado, let’s proceed to our working sheet here.
As we can see in the table above, we are using indexes of 5, 10, and 15. The reason I’m using these numbers is that stock price only moves 5-working-day a week, that’s why I use a multiplication of 5. And I added a Simple Moving Median (SMM) in case our data have outlier data that could make our average higher than it should be.
As for how to calculate the SMA and SMM above, you could see them directly in the file here to learn more.
Comparing the result
For the final part, we would like to compare our forecast result, using several methods we have done above. I compiled the result here.
As we could see, the highest forecast number is predicted by the FORECAST method and the lowest one by SMA_10, and the middle one by SMM_15. If you realize, I post this article on 23rd October 2022 at 11 pm (UTC+7). Let’s get back to this comparison table on 24th October 2022, usually, the stock market closed at 3 pm (UTC+7). We can compare the result above to the fact data of AAPL stock price to know which method forecast the most accurate price!
UPDATE (26th October 2022)
As we can see here, the AAPL stock price on 24th October 2022 (Close) is $149.45m which means all of the forecast values are lower than the fact. The nearest forecast value is the one using FORECAST $145.93.
Conclusion
- We can do a simple data forecast in Google Sheets by using the FORECAST function and create a Simple Moving Average or Median.
- All the simple methods can’t solely be used as our final decision, only as a basic and simple narrative.
- The longer the data does not always represent our data’s current condition. Sometimes, we have to know which data we could use to increase our forecast accuracy.
I hope by reading this article, you are intrigued to try your simple data forecast in Google Sheets, it’s never hurt to start! Don’t hesitate to leave any input or critics to me if you have any, I would love to hear them, I’m sure it will make me a better writer in the future.
