6. Finding Trends in Data
A basic knowledge of statistics and some business intuition can help us identify trends in our datasets. Find out how to do so in this lesson.
Finding trends in data
1 Combine your business intuition with some helpful Excel tools to initially identify trends:
--- Use conditional formatting on a dataset
--- Graph the data on a line-chart or barchart
--- Change the data from its original state - this could be switching dates to days, adjusting for inflation or dividing by another metric (e.g. area)
2 Use statistics to quantify trends (mean, median, standard deviation)
=TEXT(1/4/2013,"dddd"): Converts 1 April to day of the week, i.e. Monday
ALT + H, L: Access conditional formatting menu
CTRL + →: Move to end of data region
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select adjacent cell
CTRL + SHIFT + ENTER: Implement array formula
ALT + E, S, F: Paste formulas
CTRL + SHIFT + %: Convert into % format
The last couple of lessons have shown various ways of viewing and measuring our data’s distribution.
However, they don't necessarily tell us what trends or variables are affecting the revenue of Virtuoso Sports Bar.
Statistics are great for helping us identify these variables, but they must be combined with some business intuition for the process to be successful.
One of my favorite ways of identifying trends on small data sets is to use conditional formatting.
Let's select the revenue column, and then we'll type Alt + H L to bring up conditional formatting.
I'll press S for Color Scales and select the first option.
If I skip back up to the top, you can see that this simply color codes my cells for red at the lower numbers, and green at the higher numbers.
Immediately, we can see that every five days or so a spike in revenue occurs.
My basic business intuition tells me that this is most likely due to the weekend when people are more likely to go out drinking.
Let's now check this by finding the average revenue for each day of the week.
So I'll skip into my analysis panel, I'll type Day of the week.
I'll type Average, and then I'll type each day of the week starting with Monday, and I'll auto fill by dragging on the bottom right hand corner.
And then we'll use the AVERAGE and IF functions to calculate the average for each day of the week.
So I'll write “=average”, create an IF statement, and then convert the Dates column into the days of the week using the TEXT function.
So my input will be the Dates column and the format will be the full day, which is four D's.
I'll check if this is equal to Monday, and if it is, I'll include the revenues, and if it isn't, I'll include a blank string.
I'll then close the brackets and Ctrl + Shift + Enter to perform the array formula.
I'll then copy this formula and paste for the remaining days.
And sure enough, it works out that Friday, Saturday and Sunday are by far the biggest revenue days.
Let's now figure out the percentage revenue numbers on an average week.
So we'll first calculate the total, by summing up these numbers, and then we'll create percentages by dividing each day's total by the week total.
And I'll anchor this cell with F4.
I'll then copy and paste for the remaining days.
And we'll convert this into a percentage format with Ctrl + Shift + %.
And these figures tell me that the first three days of the week only contribute 21% of the total revenue, but the last three days of the week contribute a whopping 67%.
So what should Virtuoso do with this information? Well it's clear the company isn't getting much from the bar Monday to Wednesday.
In fact, it's probably losing money on these nights, if costs are deducted from the revenue figures.
In response, the company has a couple of options.
It could reduce the number of staff working Monday to Wednesday to save on costs, it could close the bar on these nights if they're generating big losses.
Alternatively, they could run special promotions Monday to Wednesday to attract more bar visitors, or they could even start serving food on these nights, and change the focus from drinking to dining.
By using statistics in this way, the company can make informed business decisions based on trends in the data.
Other ways of spotting trends include graphing the data or even changing the nature of it.
For example, if I had switched the Dates column to the days of the week, I would've quickly spotted this trend as well.
Try to use conditional formatting and some of these other techniques when identifying trends in your own data sets.
You might be surprised by what you find.