Vinay Vasudevan

# Introduction to Simple Forecasting Methods in Excel

Updated: Feb 7, 2022

**Forecasting **is one of the most important aspects of a business. We use the techniques to anticipate the future.

In business, forecasting is used to allocate **manpower, budget**, etc. Forecasting is so powerful that all business planning relies on forecasting.

Fortunately, the contact center forecasting comes under the **WFM** bucket, and due to this, WFM is looked at as a very important and pivotal department in an organization.

There are numerous forecasting methods available, and each has its **calculations** and usage.

Today, we will look at some simple forecasting methods and their calculations. We will also look at selecting a best fit model depending upon the **Forecast Accuracy/Error**.

**As always, I have attached an excel sheet at the end**,** which contains detailed calculations of the Simple Forecasting Methods.**

**So, let's begin our quest!!**

**Naive Forecasting Method or Random Walk Method**

The logic of the **Naive Forecasting Method** is that the forecasted values will be equal to the previous period value. The Naive Method is also called as **Random Walk** Method.
For example, if we forecasting January, the **forecasted **value will be equal to December. This is illustrated by a formula as shown below.

where Yt is the period to be forecasted, and Yt-m is the **previous seasonal** period

**Random Walk with Drift**

We know that the Naive and Seasonal Naive is also called Random Walk. We use the **Seasonal Random Walk** in this method, but with an additional component called drift.

For any historical data, we have specific forecast components such as **Level, Trend**,** and Seasonality**. These are called the "Building blocks of Forecasting".

Coming back to the drift. Since we are using Seasonal Random Walk, the **seasonality **part of the forecast is taken care of, the actual historical value acts as a **Level**.

Therefore the only pending component is the **Trend**. To factor this in our forecast, we use the additional **drift** component to our forecasting.

Let's look at the formula below for **Random walk with Drift**.

The formula may look a little overwhelming, but it is straightforward when you do it in excel. **Please refer to the attachment at the end of this article. **

**Moving Average Method**

This is perhaps the most common method used in forecasting. As the name indicates, this is a technique where we consider the **average subset** of previous data as the forecasted value.

As we move towards the forecast period, the subset also moves, therefore the name **Moving Average**.

The Moving Average is denoted by** MA(n)**, and n stands for the subset period. We read it as **Moving Average of Order n**.

For example, if we have** monthly call volume data** for a year and we are trying to forecast the value for the next 4 months, we can use the average of the first four months of previous data for Month 1. When we move to Month 2, the average also moves to the following four-month, **excluding** the 1st month and including the 5th month. Thus the average move as we progress to our forecast.

In the excel file attached below, I have considered the subset to be 4, therefore the moving average is denoted as **MA(4).**

The key to getting the best forecast accuracy is by changing the size of the subset and finding the best size by doing a **trial and error** method.

Below is the formula for **Moving Average**.

**Weighted Moving Average Method**

In the moving average, we gave **equal importance** to all the values in the subset. However, there are instances where the most recent historical value has some external factor to it, such as Marketing, New Product, etc.

In this scenario, we cannot give equal weights to all the values in a subset. Therefore we use the **Weighted Moving Average Method**.

The Weighted Moving Average is denoted by** WMA(n)**, and n stands for the period of the subset. We read it as **Weighted** **Moving Average of Order n**.
The most recent **historical value** gets more weightage, and the weightage reduces as we go further to the past. However, all the weights added together should equal 1 or 100%.
In the excel file attached below, I have considered the subset to be 4 and the weights as 10%, 20%, 30%, and 40%, therefore the weighted moving average is denoted as **WMA(4).**
The key to getting the best forecast accuracy is by changing the size of the subset and changing the weights, and finding the best size and weights by doing a **trial and error** method.

## How to find the best fit model?

We have looked at different simple forecasting models above, but ** how do we decide which model **best fits

*our data?*To understand this, we have to look at the different ways of looking at the forecasting error. So let us first look below at a few terms required to calculate the **forecast accuracy.**

**Error:** The difference between the forecasted value and the actual value.

**Absolute Error:** The absolute difference between the forecasted value and the actual value.

**Square of Error:** The square of the Absolute Error.

Once we understand the above terms, we will proceed to calculate three different types of **forecasting error**.

: Average of the Absolute error for all the time periods.*MAE (Mean Absolute Error)*: Square Root of the Average of the Squared Error for all time periods.*RMSE (Root Mean Square Error)*: Average of Absolute Error divided by the average of actual value for all the time period*MAPE (Mean Absolute Percentage Error)*

With the example in the attached excel file, below are the values of **MAE, RMSE**,** and MAPE**.

By looking at the above numbers, it is very obvious that the Seasonal Naive has the lowest error for the given data set. Therefore the **Seasonal Naive Method or Random Seasonal Naive** is the best fit model for forecasting.

Note: These methods are tried and tested in R and Python

Attachment: Simple Forecasting Model Thank you for Reading😊😊

### Check out the weWFM Podcast on Apple or Spotify

Spotify:

__https://spoti.fi/3J5gsJh__Apple:

__https://apple.co/3HskI58__