A few years ago on road trips I’d happily grab whatever I can find to eat at service stations. That usually includes either McDonald’s, KFC, or Burger King. But today I wouldn’t mind driving a few miles off-route to eat something healthier. If you follow any financial news, you’ll notice that this trend is prevailing. People’re fleeing typical fast food places and filling queues at fast-casual restaurants.

I decided to look at data using Power BI Designer and see if it shows any of this changing trend.

I handpicked a few publicly traded restaurants – excluding casual dining places- and compared the percent change of revenue from previous year. I categorized these restaurants into either fast food (FF) or fast-casual (FC) solely based on my what my gut says. I realize that revenue doesn’t necessarily tell the accurate story because for example, some of these restaurants make money from franchising fees that’re not directly related to sale of goods (food in this case).

Sure enough, as you can see below, fast-casual restaurants posted considerable increase of revenue for each of the last 4 years. Fast food restaurants on the other hand, had a decrease in YOY revenue for 2 out of the last 4 years with only a modest increase in the other two years.

What’s more interesting is fast food group’s change of revenue for the last 3 consecutive years is on a decline. This surely means that people aren’t loving fast food as much as they used to.

Using the built-in highlighting, I can see that Chipotle and Zoes are cashing in on this trend while McDonald’s and Wendy’s are struggling to maintain momentum.

Chipotle: Revenue with Integrity!

Zoes: Fresh = More Customers!

McDonlad’s:

Wendy’s:

Related: Analyzing Baby Names using Power BI

Suggestions for Improvement

I’m not a huge fan of the bar chart at the bottom. I’d want to use a line chart with one line for each company and use sector as the legend. So there’d be a series of lines categorized into two colors, one for each sector. But I didn’t find a way to do this using the current preview version of Power BI Designer. Let me know in comments below if this is in fact possible.

Getting the Data

Here’s the Power Query query to get data used in this analysis. I used data provided by MarketWatch.com.

ReadMarketWatch:

(ticker as text) as table => 
let 
Source = Web.Page(Web.Contents("http://www.marketwatch.com/investing/stock/"&(ticker)&"/financials")), 
Data0 = Source{0}[Data], 
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Fiscal year is January-December. All values USD millions.", type text}, {"2010", type text}, {"2011", type text}, {"2012", type text}, {"2013", type text}, {"2014", type text}, {"5-year trend", type text}}), 
RenamedMetric = Table.RenameColumns(#"Changed Type",{{"Fiscal year is January-December. All values USD millions.", "Metric"}}), 
Removed5YrTrend = Table.RemoveColumns(RenamedMetric,{"5-year trend"}), 
FilteredRevenue = Table.SelectRows(Removed5YrTrend, each [Metric] = "Sales/Revenue") 
in FilteredRevenue

RevPctChange:

let
    Source = Csv.Document(File.Contents("C:\Users\sam\Downloads\rest\rest_list.txt"),[Delimiter=",",Encoding=1252]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each ReadMarketWatch([Ticker])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"2010", "2011", "2012", "2013", "2014"}, {"Custom.2010", "Custom.2011", "Custom.2012", "Custom.2013", "Custom.2014"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.2010", "2010"}, {"Custom.2011", "2011"}, {"Custom.2012", "2012"}, {"Custom.2013", "2013"}, {"Custom.2014", "2014"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Ticker", "Sector"}, "Attribute", "Value"),
    RenamedYearRev = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Year"}, {"Value", "Revenue"}}),
    ReplacedB = Table.ReplaceValue(RenamedYearRev,"B","",Replacer.ReplaceText,{"Revenue"}),
    ReplacedM = Table.ReplaceValue(ReplacedB,"M","",Replacer.ReplaceText,{"Revenue"}),
    GroupedRows = Table.Group(ReplacedM, {"Ticker"}, {{"AllRows", each _, type table}}),
    Rank = (tabletorank as table) as table =>
    let
       #"Added Index" = Table.AddIndexColumn(tabletorank , "Index", 0, 1),
         #"Added Custom" = Table.AddColumn(#"Added Index", "CustomIndex", each if [Index] = 0 then [Index] else [Index] - 1),
       PriorYearRevenue = Table.AddColumn(#"Added Custom","PriorYearRevenue",each #"Added Custom"{[CustomIndex]}[Revenue])
    in
       PriorYearRevenue,
    AddedRank = Table.TransformColumns(GroupedRows, {"AllRows", each Rank(_)}),
    ExpandedAllRows = Table.ExpandTableColumn(AddedRank, "AllRows", {"Sector", "Year", "Revenue", "Index", "CustomIndex", "PriorYearRevenue"}, {"AllRows.Sector", "AllRows.Year", "AllRows.Revenue", "AllRows.Index", "AllRows.CustomIndex", "AllRows.PriorYearRevenue"}),
    ChangedRevenue = Table.TransformColumnTypes(ExpandedAllRows,{{"AllRows.PriorYearRevenue", type number}, {"AllRows.Revenue", type number}}),
    AddedPctChange = Table.AddColumn(ChangedRevenue, "PctChange", each ( ([AllRows.Revenue] - [AllRows.PriorYearRevenue])/[AllRows.PriorYearRevenue] ) * 100),
    RoundedPctDown = Table.TransformColumns(AddedPctChange,{{"PctChange", Number.RoundDown}})
in
    RoundedPctDown

On a Side

This post is a part of my quest to find answers to every-day questions using data. Power BI Designer is a great tool to make it happen. Businesses too can gain insights and tell stories using their data in a similar way. For example, this type of visualization can be used to compare performance – metrics such as sales, profitability, etc., – between two groups.