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.

Related Content you might be interested in

Current Title:

Small book image
Data Forecasting and Segmentation Using Microsoft Excel
Fernando Roque
May, 2022 | 324 pages
Small book image
Building Statistical Models in Python
Paul N Adams | Stuart J Miller | Huy Hoang Nguyen
Aug, 2023 | 420 pages
Small book image
Regression Analysis with R
Giuseppe Ciaburro
Jan, 2018 | 422 pages
Table of Contents (19 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Share Your Thoughts
1
Part 1 – An Introduction to Machine Learning Functions
Part 1 – An Introduction to Machine Learning Functions
Free Chapter
2
Chapter 1: Understanding Data Segmentation
Chapter 1: Understanding Data Segmentation
Segmenting data concepts
Grouping data in segments of two and three variables
Summary
Questions
Answers
Further reading
3
Chapter 2: Applying Linear Regression
Chapter 2: Applying Linear Regression
Understanding the influence of variables in linear regression
Projecting values from predictor variables
Summary
Questions
Answers
Further reading
4
Chapter 3: What is Time Series?
Chapter 3: What is Time Series?
Technical requirements
Understanding time series data
Designing the time series data model
Doing the forecast
Summary
Questions
Answers
Further reading
5
Part 2 – Grouping Data to Find Segments and Outliers
Part 2 – Grouping Data to Find Segments and Outliers
6
Chapter 4: Introduction to Data Grouping
Chapter 4: Introduction to Data Grouping
Technical requirements
Grouping with the K-means machine learning function
Finding groups of multiple variables
Understanding outliers
Summary
Questions
Answers
Further reading
7
Chapter 5: Finding the Optimal Number of Single Variable Groups
Chapter 5: Finding the Optimal Number of Single Variable Groups
Technical requirements
Finding an optimal number of groups for one variable
Running the K-means function to get the centroids or group average
Finding the groups and centroids of one-variable data with K-means and Excel
Summary
Questions
Answers
Further reading
8
Chapter 6: Finding the Optimal Number of Multi-Variable Groups
Chapter 6: Finding the Optimal Number of Multi-Variable Groups
Technical requirements
Calculating the optimal number of groups for two and three variables
Determining the groups and average value (centroids) of two and three variables
Using the Elbow and K-means functions with four variables
Summary
Questions and answers
Answers
Further reading
9
Chapter 7: Analyzing Outliers for Data Anomalies
Chapter 7: Analyzing Outliers for Data Anomalies
Technical requirements
Representing the data in a 3D chart
K-means data grouping
Pivot analysis of the outliers
Summary
Questions
Answers
Further reading
10
Part 3 – Simple and Multiple Linear Regression Analysis
Part 3 – Simple and Multiple Linear Regression Analysis
11
Chapter 8: Finding the Relationship between Variables
Chapter 8: Finding the Relationship between Variables
Technical requirements
Charting the predictive model's regression variables
Calculating the linear model confidence percentage
Statistical significance of the slope
The regression model's value ranges
Summary
Questions
Answers
Further reading
12
Chapter 9: Building, Training, and Validating a Linear Model
Chapter 9: Building, Training, and Validating a Linear Model
Technical requirements
Calculating the intercept and slope with formulas
Computing coefficient significance – t-statistics and p-value
Getting the residual standard error
Calculating the r-squared
Calculating the f-statistics
Training and testing the model
Doing prediction scenarios with the regression model
Summary
Questions
Answers
13
Chapter 10: Building, Training, and Validating a Multiple Regression Model
Chapter 10: Building, Training, and Validating a Multiple Regression Model
Technical requirements
Exploring the variables with more influence
Calculating t-statistics and p-values
Calculating residuals standard error and f-statistics
Training and testing the model
Summary
Questions
Answers
Further reading
14
Part 4 – Predicting Values with Time Series
Part 4 – Predicting Values with Time Series
15
Chapter 11: Testing Data for Time Series Compliance
Chapter 11: Testing Data for Time Series Compliance
Technical requirements
Visualizing seasonal trends
Researching autocorrelation – past values' influence over present values
Performing the Durbin-Watson autocorrelation test
Summary
Questions and answers
Answers
Further reading
16
Chapter 12: Working with Time Series Using the Centered Moving Average and a Trending Component
Chapter 12: Working with Time Series Using the Centered Moving Average and a Trending Component
Technical requirements
Calculating the CMA
Producing the forecast – season and trend line
Summary
Answers
Further reading
17
Chapter 13: Training, Validating, and Running the Model
Chapter 13: Training, Validating, and Running the Model
Technical requirements
Training the model
Building and training the forecast model
Testing the forecast model
Doing the forecast
Summary
Questions
Answers
Why subscribe?
18
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Representing the data in a 3D chart

The first example is to apply our knowledge of grouping to find the outliers as possible candidates for research on fraud. For example, we could identify outliers with small amounts of spending and very early transaction hours across 6 consecutive days. This behavior will probably not correspond to the average amount of and typical working hours of transactions.

Kaggle credit card fraud dataset

The credit card fraud data has several columns:

  • The number of seconds since the first transaction was recorded in the dataset.
  • The amount expended by the cardholder.
  • The V1 to V12 columns represent encrypted data to protect the original information. These are numerical fields, and the K-means algorithm can classify these values into groups.

The only true values of the data are seconds and amount. The V1 to V2 fields have data alteration with encryption techniques for privacy measures.

We are going to perform statistical grouping...

Previous Section
End of Section 3
Next Section