Trend Analysis with Excel

What is trend analysis and how do you predict the value of a commodity, measure or quantity? In this article, we cover some critical aspects of what is needed to generate trends. Our initial emphasis is on helping you identify and manipulate data that can be visualized as trends. This sometimes involves a bit of data cleaning and data transformation. As Data Scientists and analysts, we will examine how to do this using Microsoft Excel and visualize the results in a dashboard.

As we proceed in the article, we will walk you through a hands-on project from data ingestion to data visualization and predicting food prices in Nigeria and subsequently TESLA (TSLA) stock prices. But first, let’s begin with the basics.

What is Trend Analysis?

Trend analysis is the widespread practice of collecting information and attempting to spot a pattern. Although trend analysis is often used to predict future events, it could be used to estimate uncertain events in the past.

Trend analysis is the process of trying to look at the current values of particular metrics in order to predict future ones. This can be stock prices, sales figures, customer service calls, healthcare checkups, etc.

In addition, it can include attempting to determine whether a current market trend, such as gains in a particular market sector or business area, is likely to continue, as well as whether a trend in one market area could result in a trend in another.

Types of Trends

There are three basic types of trends. We have to be cognizant about the types of trends in order to better communicate our insights across to our end-users and audience.

There are three (3) types of trends:

1. Up Trend Analysis
2. Down Trend Analysis
3. Sideways Trend Analysis

Let’s take an in-depth glimpse into each of the trend types.

1. Up Trend Analysis

An uptrend is marked by an overall increase in the measured metric. Nothing moves straight up for long, so there will always be undulations, but the overall direction needs to be higher.

2. Down Trend Analysis

A downtrend occurs when price or the quantity you’re measuring moves lower over a period of time. While the price may move intermittently higher or lower, downtrends are characterized by lower peaks and slopes over time.

3. Sideways Trend Analysis

This occurs when the horizontal movement of price maintains a particular trajectory without deviating too far downwards or upwards. This indicates stability.

The Importance of Trend Analysis

Trend analysis can improve your business by helping you identify areas within your organisation that are doing well, as well as the not-so-efficient areas or processes needing improvement. It provides valuable evidence to help make better decisions around your longer-term strategy as well as ways to futureproof your business.

The trends most organisations normally zone in on are focused around:

• Key performance indicators (KPIs):
  Tracking against what you as a company has identified as your own measures of success.  This could be anything from increased sales, to a reduction in manufacturing costs or even improved market share.  
  
• Financials:
  There is a definite need to track the total amount of money being transferred into and out of your organization whether on a month-on-month or year-on-year basis in order to spot cash flow and liquidity issues.

As with any other type of prediction (calculated guess), the success of trend analysis hinges on the basic principle of “what you put in is what you get out”. 

The key considerations here are the accuracy and completeness of the data, the frequency of data collection, and having the data in an easily accessible and usable format.

Essentially, to perform qualitative trend analysis, the availability of clean, structured and accurate data should not be overlooked.

Now that you understand the basics of trend analysis, let’s take a practical look at how to apply the technique to everyday economics.

Performing Trend Analysis of selected food prices in Nigeria

Food and water consumption is essential to human life. A sizeable chunk of the household income is spent on food consumption, especially for low and middle-income earners.

Historically, the prices of food items have been increasing in direct response to inflation on the costs of agricultural inputs – fertilizers, machinery, transportation to market, fuel and food processing.

When prices of food rise, the poorest households, which spend a huge chunk of their income on food, are forced to reduce their consumption and in the worst cases, the quality of food consumed.

Due to the continuous surge of the mind-boggling rise of prices of food in Nigeria, I decided to analyze trends and identify the trajectory of selected food prices across the country from the year 2017 to 2020.

We will use Microsoft Excel tools such as power query and pivot table to clean and articulate the data. The video below details the techniques used to clean and transform the raw data of the selected food prices into a proper dataset that can be manipulated, explored, and visualized.

Stage 1: Download the dataset

In order to tag along, ensure to download the dataset sourced from the National Bureau of Statistics below:

Stage 2: Clean and transform the dataset for trend analysis

The step-by-step video below will explain how to transform and prepare the dataset for trend analysis in Excel.

Stage 3: Build a dashboard for trend analysis

We will perform trend analysis on some selected food prices in Nigeria by following these steps:

Step 1: Insert pivot table

To insert a Pivot table, follow these steps;

1. Click on any cell within the Data set
2. Click the Insert Tab
3. Click on the PivotTable option

Step 2: Fill the dialog box

A Dialog box will appear. Select Ok for the Pivot Table to be created on a new sheet.

Clicking on the Ok button will create a Pivot Table cache and Pivot Table fields panel in a new sheet as shown in the diagram below:

Step 3: Insert fields into pivot table

To insert fields follow these guidelines;

1. Click on the date
2. Drag the date to the Rows pane
3. Click on the prices
4. Drag the prices to the values pane

The prices aggregation is by default a count but we will be using the average of prices instead.

To change the aggregation, follow this type;

1. Click the drop-down arrow in the values field
2. Then click on Value Field Settings
3. Change the aggregation to average

The result of your output should have a semblance of the image shown below:

Step 4 : Insert formatting

To insert formatting to the Pivot Table, follow these steps;

1. Click any cell within the Pivot table
2. Right-click or click the number format option in the home tab
3. Click on more Number formats option
4. In the format dialog box, select the currency option
5. Search for the Nigerian currency symbol
6. Click ok

The result of your output should have a semblance of the image below:

Step 5 : Insert line chart for trend analysis

To insert a line chart, follow these steps:

1. Click on any cell
2. Go to the Insert tab
3. Select 2-D Line chart in the charts command tools

Step 6 : Insert slicer

To add a slicer, follow these steps:

1. Navigate to the insert tab
2. Click on the slicer icon.
3. In the Insert slicers dialog box, tick the options for both State and Food item

Step 7: Insert background

To insert a background, follow these steps:

1. Insert a new sheet tab
2. Go to page layout tab
3. Click on Background
4. Select your preferred your image

Step 8 : Move chart and slicer

To move the slicer and the chart to the new sheet tab with the image background, follow these steps:

1. Copy both the slicer and chart
2. Navigate to the new sheet
3. Paste both the chart and slicer respectively

Your final output should have a semblance to the image below:

Stage 4: Perform trend analysis of selected foods

We will analyze a select crop of foods sold in the bustling city-state of Lagos, Nigeria.

Food type : Chicken wings

You can see that from 2017 to late 2019, the price of chicken wings remained fairly stable (sideways trend) until 2020 when the price has consistently been rising.

Food type : smoked Catfish

Looking at the trend analysis of smoked catfish, what observation can you make of its trajectory?

Trend analysis using Moving Average

What are moving averages?

Moving averages are simply a way to smooth out fluctuations in the movement of prices or the quantity measured. It is a totally customizable indicator, which means that you can freely choose whatever time frame you want when calculating an average.

The most common time periods used in moving averages are 15, 20, 30, 50, 100, and 200 days. The shorter the time span used to create the average, the more sensitive it will be to price changes. The longer the time span, the less sensitive the average will be.

We will perform trend analysis using moving average by following the steps below:

Insert moving average to a trend line chart

To insert a moving average on the line chart for the selected food prices in Nigeria, follow these steps:

1. Right-click on the line graph
2. select Add Trendline
3. In the format trendline pop-up prompt, select moving average
4. Select 10 days (or points) as the period interval

The final result of the moving average of the food prices in Nigeria is shown in the image below and its indicated with the red line:

This is the crescendo on how to perform Trend Analysis using Microsoft Excel. The sequel of this article will involve the use of Power BI to perform trend analysis on TESLA share prices whilst showcasing top-notch visuals.

We encourage you to practice the steps shown in this tutorial and tinker around with the moving average periods and come up with your own analysis.

---

We appreciate our readers and learners and would love to get your feedback.

Don’t forget to like, share the article and subscribe for more exciting content.

---

Excel Crash Course – Data Analysis, Dashboards and Heatmaps

To find out more about what we do, subscribe to our insights below. You can reach out to us directly for training and analytics consulting needs.

Receive Fresh, Weekly Analytics Content

Join 1K+ subscribers to receive original, freshly curated content every week.

First Name

Leave this field empty if you're human:

Learn something new – Visualizing WhatsApp Data using Python and Power BI

Exciting tutorial – Visualizing The Top 1000 Lifetime Movies