Trend Analysis in Excel - YouTube

In this lesson, I will provide a practical example of how trend analysis can be used

to uncover patterns in a company’s data.

To make this exercise as close to real-life as possible, I’ll use some Excel formulas,

so I will have to assume some pre-existing Excel knowledge.

However, if you feel that you’re unable to follow along, please consider going through

our Excel lessons.

Now, having said that, let’s go ahead and engage with the trend analysis task.

Here’s the database we will work with.

This is a company that operates in 4 countries – the USA, Canada, Mexico, and Brazil.

What we have is monthly data for the following financials and KPIs - revenue, cost of goods

sold, advertising spend, staff costs, headcount, employee engagement score, and website traffic.

And we can see that each row shows the data for the respective month.

When we talk about trend analysis, we know we’ll have to deal with dates, right?

Basically, we want to study how a variable or two evolved over a given time span.

To be able to do that, I will add a new column right after the ‘year’ field.

And I’ll name the new column ‘date’.

Now, in this case, we can apply the DATE function, which results in a date output understandable

by Excel.

This function is based on three inputs – year, month, and day.

The first two inputs are in columns B and C, but we do not have a cell, which shows

the respective day because this is monthly data.

So, let’s type 1 in brackets.

The date we obtained is the 1st of January 2019.

However, the financials shown here are for month-end, so, to avoid any confusion, I will

add the EOMONTH function right before the date function.

This would allow me to obtain the last date of the respective month.

In this instance, EOMONTH needs only two inputs – a starting date, which is the date we

defined with the DATE function, and a months input standing for ‘’the number of months

before or after the start date’’.

The input we need is 0, as we want to obtain the last day of the current month and hence

there’s no need to create a time lag with a number different than 0.

Very well – the 31st of January.

The formula worked!

Let’s copy and paste to the bottom of the table.

Perfect.

The next thing on my to-do list is to fill in the empty table in the trend analysis sheet.

In this case, the trend analysis we will carry out will be a comparison between the development

of revenues and cogs in the three years under analysis.

Let’s use a SUMIF function to calculate the amount of revenue and cogs the company

had in each month.

The SUMIF function requires three parameters – range, criteria, and range to be summed.

The range we will use as criteria is in column D of the database sheet.

This is the ‘date’ column we just created.

I’m fixing its references to allow easier pasting of the function afterwards.

The criterion is the date that we have in the trend analysis sheet.

I’m fixing the row reference.

And finally, the range to be summed is the ‘revenue’ column in the database sheet.

Ok.

Great.

We have the first revenue figure.

Now, to adjust the function for cost of goods sold, I’ll go ahead and replace column F

with column G in the SUMIF function.

Column F is where we have revenues and column G is the one where we have Cogs in the database

sheet.

This isn’t the most elegant way to do this in Excel, but I’d prefer for us to stick

to a rather simple solution.

I’ll even add a minus sign in front of Cogs.

The reason I am doing this is that I want Cogs figures to be shown as positive here

because this will facilitate the visualization which we need for a good trend analysis.

Now, we can copy and paste the functions and fill in the entire table.

Excellent!

All that’s left to do is to create a regular line chart for the two variables we would

like to compare.

I’ll go to ‘Insert’ and will select 2-dimensional line charts, creating the simplest

type of chart.

Some additional formatting is needed here, of course.

I’m not sure why but default Excel charts continue to be horrendous even with the latest

versions of Excel.

We’ll need to adjust the title.

Perhaps the colours of the two lines?

And some additional work on the vertical axis as well.

Ok.

This is much better.

Overall, what we can see from the chart is that Revenue and Cogs didn’t increase proportionally.

One can probably conclude that Cogs for this business have a prevalently fixed component,

and some additional analysis would be needed to understand the reason for that.

But at first glance, we can say that this is a business that can benefit greatly from

economies of scale and higher revenues.

The trend that we see here is that the gross profit margin improved dramatically because

cogs didn’t increase in line with revenues.