6. Finding Trends in Data

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

  1. Conditional Formatting (00:04)

    Conditional formatting formats cells according to their value. It’s a good way of identifying trends in small data sets. In this lesson, we demonstrate color scales. These color cells according to their value. High values have a green color, and low values have a red color. To add these color scales, we select the cells we want to format, then use the shortcut Alt, H, L, S.

  2. Analyzing Revenue by Day of the Week (00:53)

    Studying the color scales for our sports bar suggests that the business has a significant jump in revenues at weekends. To investigate this, we find the average revenue for each day of the week. We do this using an AVERAGE function and an IF function, with the TEXT function identifying the day of the week from our collection of dates.

    When we complete this calculation, we find that the majority of the bar’s revenue is generated on Friday, Saturday, and Sunday. Only a small percentage of revenue is generated on Monday, Tuesday and Wednesday. The company can use this information to decide how they might improve their profitability on those days.

Transcript

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. In this lesson, we'll learn several techniques that can be used to identify trends in our dataset. One of my favorite ways of identifying trends on small datasets is to use conditional formatting. Let's select the revenue column, and then we'll take Alt H L, 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 overfill by dragging on the bottom right hand corner.

And then I'm gonna use the average and if functions to calculate the average for each day of the week. So I'll write equals 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 right 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 the cell with F4.

I'll then copy and paste for the remaining days.

These figures tell me that the first two 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 of Monday to Wednesday. In fact it's probably loosing money on these nights if cost 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 cost. 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 an 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 have quickly spotted this trend as well. Try to use conditional formatting and some of these other techniques when identifying trends in your own datasets. You might be surprised by what you find.

Excel Excel for Business Analytics Learning Plan
Introducing Statistics

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial