Book Image

Data Forecasting and Segmentation Using Microsoft Excel

By : Fernando Roque
Book Image

Data Forecasting and Segmentation Using Microsoft Excel

By: Fernando Roque

Overview of this book

Data Forecasting and Segmentation Using Microsoft Excel guides you through basic statistics to test whether your data can be used to perform regression predictions and time series forecasts. The exercises covered in this book use real-life data from Kaggle, such as demand for seasonal air tickets and credit card fraud detection. You’ll learn how to apply the grouping K-means algorithm, which helps you find segments of your data that are impossible to see with other analyses, such as business intelligence (BI) and pivot analysis. By analyzing groups returned by K-means, you’ll be able to detect outliers that could indicate possible fraud or a bad function in network packets. By the end of this Microsoft Excel book, you’ll be able to use the classification algorithm to group data with different variables. You’ll also be able to train linear and time series models to perform predictions and forecasts based on past data.
Table of Contents (19 chapters)
Part 1 – An Introduction to Machine Learning Functions
Part 2 – Grouping Data to Find Segments and Outliers
Part 3 – Simple and Multiple Linear Regression Analysis
Part 4 – Predicting Values with Time Series

Chapter 1: Understanding Data Segmentation

Machine learning has two types of algorithms depending on the level of adjustments that you require to give a response:

  • Supervised
  • Unsupervised

Supervised algorithms need continuous improvement in the form of the data used to train them. For example, a supervised machine learning function of a linear model needs a starter group of data to train and generate the initial conditions. Then, we have to test the model and use it. We need continuous surveillance of the results to interpret whether they make sense or not. If the model fails, we probably need to train the model again.

Unsupervised algorithms do not require any previous knowledge of the data. The unsupervised machine learning process takes data and starts analyzing it until it reaches a result. Contrary to supervised linear regression and time series, this data does not need a test to see whether it is useful to build a model. That is the case with the K-means algorithm, which takes unknown and untested data to classify the values of the variables and returns the classification segments.

In this book, we will cover three different topics of machine learning:

  • Grouping statistics to find data segments
  • Linear regression
  • Time series

For grouping statistics, we will use an add-on for Excel that will do the classification automatically for us. This add-on is included with the book, and we will learn how to use it throughout this book. For linear regression, we will use Excel formulas to find out whetherthe data can be used to make predictions with regression models and forecasts from the time series.

We need a machine learning algorithm to classify and group data for the following reasons:

  • A large amount of data is difficult to classify manually.
  • Segmentation by observing a 2D or 3D chart is not accurate.
  • Segmenting multiple variables is impossible because it is not possible to do a chart of multiple dimensions.

Before we do group segmentation using K-means clustering, we need to find the optimal number of groups for our data. The reason for this is that we want compact groups with points close to the average value of the group. It is not a good practice to have scattered points that do not belong to any group and that could be outliers that do not perform like the rest of the data, as they could be anomalies that deserve further research.

The K-means function will also help to get the optimal number of groups for our data. The best-case scenario is to have compact groups with points near their center.

We will review the basic statistical concepts to work with data grouping. These concepts are as follows:

  • Mean
  • Standard deviation

In the data grouping segment, the mean is the center, or centroid, of the group. The best case is that the values are compact and close to the segment's centroid.

The level of separation of the values within a group from its centroid is measured by the standard deviation. The best case is to have compact groups with values close to the group's mean point with a low standard deviation for each group.

When we have values and segments that are scattered with a large standard deviation, that means they are outliers. Outliers are data that behaves differently from the majority of other segments. It is a special kind of data because it requires further research. Outliers could indicate an anomaly that could grow and cause a problem in the future. Practical examples of outliers that require attention are as follows:

  • Values that are different from the normal transaction amounts in sales and purchases. These could indicate a system test that could lead to a bigger issue in the future.
  • A timeline of suspicious system performance. This could indicate hacking attempts.

In this chapter, we will cover the following topics:

  • Segmenting data concepts
  • Grouping data in segments of two and three variables