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.