Time Series Forecasting: Don’t forget Seasonality!

Why is Seasonality important

Seasonality is in everything we do, we even considering unconsciously, for example when we leave for work early to beat morning traffic, or when we book our Summer holidays early to avoid peak prices.

If you are running a Winter attire business, you might only sell a few pieces during summer but your sales might boom when the weather gets colder, therefore requiring additional resources such as Inventory, Staff Availability… etc.

Seasonality is therefore a very important component of Planning and especially in Forecasting.

How to calculate Seasonality

Seasonality is usually calculated using the Time Series Decomposition Method.

This method assumes the Signal can be broken down in 3  components:

  • The Trend: is your signal flat? increasing or decreasing?
  • The Seasonality: does your signal show peaks and drops at specific Time periods (for example peak of Sales for Christmas in December)
  • The Noise: this is the part of the signal that can not be explained. If the Signal is well decomposed, the Noise component should be a process of mean = 0
  • Sometimes a Cycle component is also added. We will assume there is no Cycle going forward

 Multiplicative Time Series Decomposition

Signal(t) = Trend(t) * Seasonality(t) * Noise (t)

Additive Time Series Decomposition

Signal(t) = Trend(t) *+Seasonality(t) + Noise (t)

At AnalystMaster we generally prefer to consider Times Series as Multiplicative (and that is what we will use going forward). In that case, Seasonality for each time period could be seen as a weight and the Sum of Seasonality for all components is equal to 1 or 100%.

 Calculate Seasonality in Excel

You can also use the attached Excel Model to calculate your Monthly Seasonality in Excel.

This model works with 24 months of Historical data

  1. It first evaluates the Trend using a Centred Moving Average (only possible from time bucket 6 to 18), and extrapolate this trend linearly for time buckets 1 to 24.
  2. Then the trend is removed (we divide the original signal by it as we consider the Time Series as Multiplicative), in order to leave only the Seasonality and Noise as the only components of the Time Serie.
  3. Finally, Seasonality coefficients from both time periods are averaged

Calculate Seasonality with R

R is a great tool to calculate the Seasonality of a Time Series. You can use the following piece of code to read a monthly time serie from a data.csv file and return the Seasonality coefficients.

R will return you the Seasonal, Trend and Random components from the Multiplicative Time Series decomposition.

library(gdata)
> library(forecast)
> library(tseries)
> mydata = read.csv(“data.csv”)
> signal<-ts(signal ,start = c(2014,1), end = c(2017,6), frequency = 12)
> seascoef<-decompose(signal, type=”multiplicative”)

season_r

 The next levels

Including  Seasonality in your planning will dramatically improve your planning accuracy. However you might find out that only considering the Seasonality at Monthly level is not good enough and that you need to also include seasonality at a more detailed level to maximize your planning accuracy.

Seasonality exists also at a more detailed level: weekly, daily, hourly… for example

  • Weekly seasonality: if you are a Retailer: although December is a peak month, not all the December weeks are equal. The seasonality is much stronger on the last week before Christmas and failing to anticipate this can result in shortages of capacity
  • Daily seasonality: if you own a shop or a restaurant, Seasonality is usually stronger on some days of the week: Saturday for example
  • Hourly seasonality: peak times also vary hour by hour. If you are running a call centre you need to plan your capacity accordingly. Or if you are going shopping at Harrods’s, you might want to go when the store is less crowded according to the chart below which is available on Google

 

harrods

Sometimes Seasonality can even be more complex as it does not necessarily follow a regular Month-Week-Day-Hour pattern.

A well-known example by Retailers is Chinese New Year, which follows the Chinese Moon calendar and will therefore fall on a different week and month every year. Easter or Ramadan are also moving holidays and seasonality can be hard to evaluate.

chinese

How far should I go?

Seasonality is important and should be included in your forecasting activities, however you need to keep it at a level which is both relevant for your activity and simple enough to implement. For example if you plan Production at a Monthly level, keep your signal at a Monthly level and evaluate Seasonality at this level too. If you are planning a Warehouse Capacity at Weekly level, then get your Signal and Seasonality at Weekly level, and if you are planning how to staff a Call Centre or a Shop on an Hourly basis, then plan and measure Seasonality hour by hour. But do not introduce unnecessary complexity in getting a signal at hour level if you only expect a monthly plan

AnalystMaster

Too big for Excel…

We have seen in our PowerBI Best Practice Article that it is best to work with data at maximum level of detail. Well the problem is that sometimes it can be a lot of data… like really a lot…

One day I wanted to extract a lot of information from an ERP system. Not only the data was way above 1m rows Excel capacity, but I was also limited by how much information I could extract from this ERP at at time.

Crazy isn’t it? You know the information you need is out there but it’s LOCKED… What is the point of owning a Ferrari if you’re only going to drive it in first gear?

So what can you do? If you have read this article, you know that PowerBI is the right tool for you. We will show you how to use it depending on how much resources you may have or support from our IT department.

Regarding your data, your have essentially 3 options:

  • Use PowerBI to connect directly to the database
  • Make it fit
  • Work around it

Use PowerBI to connect directly to the database

Good news #1: we know a very cool tool: PowerBI who is capable of handling large amount of data, this is the way to go!

Good news #2: it is wired with tons of connectors to big databases (SAP, BW, Hadoop…)

If you get sufficient support from your IT department you should be able to activate these connectors and work without any issue with your large dataset

However if you are reading this blog, we do not expect you to get this kind of resources or too much support from your IT department. Your are left with the last 2 options:

Make it fit

At the other end of the spectrum, you can obviously try to make your data fit by reducing the amount of information you bring into PowerBI. This can essentially be done by

  • filtering: by filtering out some of your data, based on some attributes or combinations of attributes that you are less likely to need later you can make your dataset smaller and eventually make it fit within your capacity requirements. You can also limit your time horizon
  • aggregating: by giving up strategically on some levels of details that will be less likely to be useful in your analysis later on. For example instead of brining data at a daily or weekly level of detail you might consider monthly.

In our opinion, the Make It Fit option should be activated only in last resort. If you go for it, since you will obviously lose some detail in your information, be strategic about it. Think wisely about which levels of details are less likely to add value to your analysis and take decision on which data to leave out accordingly

Thankfully even with limited IT resources, we do not take NO for an answer and there is a last option:

Work around it

This sounds absurd: we don’t have the tools to manage (extract or store) our data with maximum level of detail, but we don’t give hope that we will import it into PowerBI anyway. How? by slicing it and re-aggregating it later!

  • Slicing: First you need to slice your data into slices that fit within your extraction capacities. For this you can refer to the filtering strategy of the “Make it Fit” section.
    Define some filters so that your extraction is manageable and run them one after the other so that you get the full data set.
    Obviously the less slices the better, so don’t necessarily limit yourself with the storage capacities of Excel or Access. Just focus on your extractions capacities and Go with Text (.TXT) files for Storage if necessary
  • Re-aggregating: this can be managed with PowerBI using the Append Query function in the Query Editor
    This works seamlessly if you have a few slices to re-aggregate however if you have many you might want to run a batch to manage this aggregation outside PowerBI

Back to my ERP extraction problem, unfortunately there was no direct connector for PowerBI that I could use so I got the IT department to extract the past information for me into a giant text file, and to run a script every day to extract the new daily data and drop it into another TXT file. All TXT files were in a folder that I could access.

I was left with a bunch of TXT files with the same format, the only thing I had to do is aggregate them together. A quick Python script later (that I’m giving to you below) and I was done. I managed to get all my data with maximum detail out of this ERP and play with it in PowerBI!

Never give up!

AnalystMaster

Appendix: how to aggregate text files using Python

Text files are not limited in row capacity and could be a efficient low cost way to store large datasets.

One of the best tool to work with text files is Python.

If you do not have Python you can download it for free from the Official Python website

Here we suppose that:

  • you have a file called Startfile.txt at the path Path/Startfile.txt which contains the headers (and only the headers) of all your text files (which are all the same and in the same order)
  • that all the text files that you want to append together are in a folder called Path/Files ; and that they are the only text files in this folder
  • you have a file called Finalfile.txt that will contain the results of your appending of all the text files containing your data. This file is at the path Path/Finalfile.txt

What you need to do:

  1. open your notepad and copy paste the script below
  2. replace Path by the actual path where your files will be stored (and only these files), create the Startfile and drop your other text files in the Path/Files folder
  3. save this script as a .py extension.
  4. Run it, that’s it! You can now open your Finalfile.txt or connect it to PowerBI to read your data

import glob

g = open(‘Path/Finalfile.txt’,’w’)

g.truncate

s = open(‘Path/Startfile.txt’,’r’)

g.write(s.read())

g.close()

g =open(‘Path/Finalfile.txt’,’a’)

path= ‘Path/Files/*.txt’

file=glob.glob(path)

for fle in file:

with open(fle) as f:

text = f.read().splitlines(True)

g.writelines(text[1:])

g.close()

Convert Matrix into Flat File format ( Reverse Pivot Table)

We have seen in this article the importance of working with Flat File. This is especially true for working with PowerBI but not only

Reminder: what is a Matrix and what is a Flat File format

Matrix Format

Date 1 Date 2
Product 1 1000 1100
Product 2 500 450

Flat File Format

Products Dates Sales
Product 1 Date 1 1000
Product 1 Date 2 1100
Product 2 Date 1 500
Product 2 Date 2 450

Converting from Matrix to Flat File

We have seen that converting from Flat File to a Matrix is generally seemless. Excel Users know how to do it well using Pivot Tables for example.

However the opposite is not straightforward (at least used to be…). This is annoying since the Matrix format is one of the most used by Excel users and I have no doubt you will receive most of your data in this format.

So how do make this conversion from Matrix to Flat File? Here are some options:

  • With PowerBI: We have told you you need to stop Excel and switch to PowerBI a long time ago. PowerBI’s Query Editor handles this conversion just fine:Just import your data and select ALL YOUR ATTRIBUTES columns (i.e the ones you do NOT want to “unflip”), then right click (your mouse cursor needs to be on the header of one of the attributes) and select Unpivot Other Columns
    BOOOM! Job done!

Before

After.png

  • With Excel 2016 / PowerQuery: Similary Excel 2016 comes with PowerPivot and PowerQuery built in. You just have to follow these steps to convert your Matrix into a Flat file, it’s very similar to the PowerBI technique
  • With old versions of Excel:  this post will show you how to do it

Enjoy!

AnalystMaster

 

 

Forget Excel – Meet PowerBI

The tool that will kill Excel… and change your life!

In my analyst life, there was a before and an after I discover PowerBI

Before with Excel: My go to tool was generally Excel. It’s a very powerful tool for Reporting and Ad-Hoc analysis, however it has a:

  • lack of connectivity: despite some basic connectors (Access…), data rarely comes to Excel by itself for analysis. You generally have to extract it manually from your systems (like SAP…), copy paste it manually, or VLOOKUP it which is a hassle
  • lack of capacity: Although an Excel spreadsheet fits 1m rows which is enough for most users, this sounds prehistoric at the age of Big Data.
  • lack of scalability:  analysis and reports are rarely carved in stone, you constantly want to add on to it: new features, new drill downs, new information that you did not think you needed before… although this is not the weakest point of Excel, it can be quite a hassle to make a big Excel report evolve without having to destroy and rebuild big part of it
  • lack of sharing options and governance: when an Excel analysis is done, how do you share it? Usually you will attach it and send it by email, or drop it on a shared drive for people to access. Again in 2017 that sounds a little outdated… but what if you would like to share only some part of the data to some users and some other part to other users? (for example you are have a global role and did a global analysis but you would like to share the US results with the US team only and the Asia ones with the Asia team only). Are you going to build 2 separate Excel files (hard coded I mean because they would easily access the global data if you just provide them with a Filter / Slicer)?

Don’t get me wrong, Excel is still an awesome tool and I still use it a great deal. The great news about PowerBI is that is that despite my provocative title it’s totally Excel compatible. As a matter of fact it is a Microsoft tool which started as an Excel add-in (for those who would remember PowerPivot) and evolved into a stand-alone BI tool

After with PowerBI:  you will:

  • connect to your data source seamlessly: there are plenty of connectors (Excel, Access, SAP, Hadoop…) available which enable you to be plugged directly on the data source without having to extract or copy paste it manually
  • transform your data seamlessly: PowerBI comes with a built in ETL (Extract-Transform Load) tool which allows to transform and prepare your data to be used, but also to memorize each step of the transformation for next time
  • manage lot of data: there is no capacity limit to the number of rows per table PowerBI can process, so it works well on very large databases
  • scale easily: the data modeling tool in PowerBI will allow you to scale up easily if you need to add more information to your initial model. It’s very easy to add tables, connect them together, and then add a few more calculations
  • build great visuals: charts and graphics are worth 1,000 words and great analysts know that it is important to have beautiful visual outputs to present your data. PowerBI has lots of it: line chart, bar charts, funnels, combo charts, pie / donut chart, maps with geographic location management… you can hardly do better. And when you think you’ve exhausted all options you can go online to the PowerBI visual gallery and download cool custom visuals developed by the PowerBI community
  • explore with dynamic drill down: This feature exists in Excel with regular Pivot Tables, however you will find out that PowerBI takes it to a whole new level
  • share and govern like a pro: with PowerBI you can share data online and users can access it online as well or via apps on their smartphone or tablet. when sharing you can define profiles and assign each user to a profile. Governance you said? Sorted!
  • have fun! Whereas building an Excel report can be quite boring, you’re likely to discover that doing the same thing with PowerBI is fun, playful and exciting! No doubt you will wow your coworkers, boss and IT department when they see you displaying your slick reports on your smartphone!

 

Or because pictures speak 1,000 words that’s how my reports looked

Before with Excel

monthly-operational-budget-report

After with PowerBI

8507.LiveReportTile

OK Excel is so 2000… but why not use a different BI tool?

If you have access to them (because they are not free…), there are a great deal of BI tools available today (Tableau, Qlik… to name a few), which if you master them will give you equivalent functionalities as PowerBI. You are more than welcome to use them especially if your company or organization provides them, however as we are supporting the masses of analysts out there who are left out with only Excel at their disposal, we will cover mostly PowerBI in this blog for the following reasons:

  • It’s free! The Desktop version of PowerBI can be downloaded for free from the PowerBI Desktop download page. And if you want to use the cloud service to publish your reports or for other user to consume the content you will publish out there, you can get Free licenses.
  • It’s the best! and we are not the only ones to think that way. PowerBI ranked #1 BI tool in the famous Gartner Magic Quadrant in 2016 and 2017
  • It’s almost like Excel! Did I mention that PowerBI is the evolution of successive upgrades from ExcelExcel -> Excel + Pivot Tables -> Excel + PowerPivot -> PowerBITherefore Excel users will find it very natural and easy to transition from Excel to PowerBI. It will make it very easy to teach your coworkers about it.For example for 2 of the main functions you will be using:

Excel: VLOOKUP (Value, Table, ColumnNumber to look up)
PowerBI: LOOKUPVALUE (Column to look up, Table, Value)

or

Excel: SUMIF ( Column to sum, Filter)
PowerBI: CALCULATE ( SUM (Column to sum), Filter)

  • It’s getting better every month: The PowerBI teams releases monthly upgrades to their online service and to PowerBI Desktop. The pace of innovation is quite crazy, think about it: it’s as if you were getting a new version of Excel every month! you can discover more about the different updated on the PowerBI blog
    PowerBI is also a tool built based on the feedback of a community of thousands of users who are proposing ideas and voting for the best ones so that they can get picked by the PowerBI team to develop

So believe us, download PowerBI and start playing with it (we feel the word is not too strong). Surely you will soon be migrating all your Excel reports into it and next thing you know when asked for a quick ad-hoc analysis you will be using it as well and discover that Excel is really a tool from the past and that your life has changed…

We will cover more in detail PowerBI in next posts, stay tuned!

AnalystMaster