A hypothetical coffee chain, El Café Grande, is losing money and it’s my job to find out why. Let’s take a look at the data and see if I can pinpoint some reasons.

I uploaded the mock data set (from 2015 + 2016) into Tableau. The problem is already established, so my next step is to search for the reasons as to why El Café Grande is losing money.

To do this, I utilized Tableau’s many features to visualize and interpret the coffee shops data.

There are four product categories: Coffee, Tea, Herbal Tea, and Espresso. Within these categories, there are several products within them, such as Earl Grey for Tea and Amaretto for Coffee. The chain is located in 20 states, and I can filter by region or market size. I also have access to quantitative data, which includes total sales, marketing budgets, and more.

To kick things off, I broke down the four product categories: Coffee, Tea, Herbal Tea, and Espresso, by sales. The following chart represents such.

Note: The data combines both 2015 and 2016 totals. Assume as such unless indicated otherwise.

‍

‍

To represent the four product categories, I used a packed bubbles graph. The sales of each product’s category are indicated by the size of the bubble, as well as the number inside the circle. I used a color mark to easily distinguish between the different circles, which you will find me doing often.

Out of the four categories, the graph indicates tea is lacking. This is a great start and I will dive deeper into the tea category. We can also see that Espresso and Coffee are clearly popular, with Herbal Tea not too far behind. The difference between Espresso and Tea is just over $50,000.

For the next graph, I wanted to transition from sales to profit. After discovering Tea’s low sales total, I wanted to go a step further and see each product’s profits broken down within their respective category.

‍

‍

Here we can see each products profit by product type. Profit is indicated by the number in the middle of the rows and the color scale where the darker blue represents higher profits. On the left, we simply have products broken down by their product type.

As we noticed with the first graph, Tea was severely behind the other three product categories in terms of sales. When I go a step further, we discover Green Tea is in the negatives, with a profit total of -$231 indicated by the tan color. However, Darjeeling and Earl Grey have excellent profits. It appears the cause of Tea’s low sales is due to Green Tea.

There are two other products, Amaretto Coffee and Mint Herbal Tea, that haven’t eclipsed $7,000 in profit. I will keep an eye out on them as I go on. Now that I’m fairly certain Green tea is causing an issue, I want to know which states are being affected the most.

‍

‍

For this graph, I used a signal map with products and state. The color mark indicates the total profit every product made in each state.

Right away I spotted five darker red rectangles. They represent Amaretto and Decaf Irish Cream in California, Caffe Mocha and Mint in New York, and Green Tea in Nevada. There are plenty of product/state combos on the fringe of not making a profit, like Regular Espresso in New Hampshire. There are also a few product/state combos that are barely losing money, such as Lemon Herbal Tea in Missouri.

This chart helped me identify the weakest product/state combo. Let’s dive deeper into Nevada’s Green Tea situation.

‍

‍

To see the impact of Green Tea in Nevada, I created a line graph. I filtered out the excess states and products until I got Green Tea in Nevada. On the Y-axis, we have profits and on the X-axis, we have the years. I forecasted the expected sales and profit of Green Tea in Nevada. Profit is indicated by the number above the plotted points, while the color indicates the level of sales.

Yikes.

You can see just how bad the current sales and total profit is and what it’s forecasted to be. Profit dropped nearly $2,000 from 2015 to 2016. On top of that, sales are only forecasted between $270–$310. Green tea in Nevada is clearly a problem and should stop being served.

I would also make similar forecasts for Amaretto and Decaf Irish Cream in California, as well as Caffe Mocha and Mint in New York. These 4 products brought in massive negative profits. Forecasting for these products in their respective states will allow us to see if there is a chance for them to rebound or if they should simply not be served.

‍

Problem 1: Green Tea’s loss in Nevada.

‍

Now, let’s take a step back and focus on the regions these states are in. We saw how products were performing in each state in a previous graphic, but now let’s look at the greater picture.

‍

Here we have the profit of each product indicated by color, while the graph is sorted by sales in each region. The number marks inside the boxes are profits. It’s also split up by market size; major or minor. I used a stacked bars graph to illustrate this

.

We don’t see much green here at all. In fact, most products have decent sales but aren’t bringing in a profit. This is a big issue, but I will get to that later.

Based on this graphic, the south, in general, isn’t profitable. It looks like other regions are hardly making a profit as well. Let’s take a further look into this and see if I can pinpoint a specific state.

‍

‍

Using a signal map, we can see the profit in each state. This is another way to look at the profit per state, without the products involved. The color and number marks represent profit.

Just over one fourth (six) of states are in the green. Despite California and New York having two products that brought in negative profits, they remain among the leaders in terms of profit. Texas is right on the border (Ha!).

Plenty of states are lacking in profits, but somehow no state is losing money. Immediately, you can tell New Mexico is the state causing the most issues. New Mexico barely made a profit, only reaching $799. Missouri and New Hampshire have lower than average profits as well.

‍

I’ll breakdown these three states and see if I can identify a problem.

‍

‍

This graph shows the negative profits from the three worst performing states sorted by product. I filtered the states to just show New Mexico, Missouri, and New Hampshire, and filtered the profit to only show the products that suffered a loss. The colors are states, the number marks are the loss.

Now we can pinpoint the products in these states causing the biggest issues, like Lemon in Missouri and Decaf Irish Cream in New Mexico.

Problem 2: New Mexico, Missouri, and New Hampshire have minimal profits because of several products negative profits.

Earlier, I mentioned the lack of profits for products despite strong sales. This didn’t make much sense and I knew I had to dig into the expenses to uncover why. I have a hunch there is a problem lying within specific products expenses causing a massive loss of profit.

‍

‍

This text chart is very ugly, I apologize. However, it’s a simple way of showing the profit formula. The key here is how it’s made. I created a ranking calculation for sales, expenses, and profit. In other words, I assigned a rank for each product based on how it compares to other products. I sorted this chart by the sales ranking column.

Ideally, products should have better sales than expenses like Decaf Espresso or identical sales and expenses like Colombian. I’m not too concerned if expenses are greater than sales by one ranking. That’s probably because of the actual sales and expense numbers are off by a small margin.

The products with the most sales have the most expenses, but they still make larger profits than others. The top three products in terms of sales also have the top three most expenses.

My eyebrows raised when I saw, Decaf Irish Cream and Green Tea. Green Tea’s sales are the third worst, yet its expenses are two rankings higher, causing a lower profit. Decaf Irish cream has the fifth highest expenses, but only has the eighth highest sales. We have already found out that Green Tea is making a loss, especially in Nevada. But Decaf Irish Cream is new.

However, I spotted an even bigger problem when I focused on profits. Take a look at Caffe Mocha. It has the third most sales and second most expenses, but only the seventh most profits. What in tarnation is going on!? Let’s take a different approach at this chart.

‍

‍

This side-by-side bar graph shows the difference between the sales of each product by its total expenses. I have the profit ranking on top of each bar, as well as the total profit in color. I used a 4 stepped color scale to easily identify the differences in color. Let’s pay close attention to Caffe Mocha.

Caffe Mocha does indeed have the third highest sales and second most expenses. However, in terms of profit, the lighter green color indicates that six products have a better profit. This isn’t adding up, so I need to dig deeper into the cause.

‍

‍

Here we have each products inventory levels on the Y-axis and their sales totals on the X-axis. I used both a color and shape mark to represent the product on this scatter plot. I specifically highlighted Caffe Mocha on the top.

We can see that despite Cafe Mocha’s strong sales, the forecasted amount was way too high leading to a large supply of inventory that didn’t get sold. This would make sense as to why sales and expenses were high, but profit was low because of the left-over inventory.

Problem 3: El Café Grande forecasted a large quantity of Caffe Mocha inventory that ended up getting unsold.

After going down that rabbit hole, I was curious as to what can potentially drive sales. Then my marketing senses went off. I wanted to know if marketing had an influence on sales.

‍

‍

I used a bar graph to show the comparison between sales and marketing dollars. I created another ranking calculation for marketing and added it to the bottom graph. The sales ranking is on the top graph, indicated by the numbers on top.

Here I found three products that have spent more on marketing while seeing a lack of a return on sales. Ranking the products in terms of sales and marketing helped me establish which products this happened to, as denoted by the products highlights. Once again, Green Tea pops up, as does Caffe Mocha which we just discussed.

However, Decaf Irish Cream makes another appearance as a product with a problem. We noticed this when we did our profit ranking. Decaf Irish Cream has the fifth most dollars spent on marketing, yet is making the eighth most in sales. Let’s dig deeper.

‍

‍

For this graphic, I used a horizontal bar chart to break up states by their marketing budget for Decaf Irish Cream. I filtered out the other products to only show Decaf Irish Cream. The colors represent the states, which makes it easier on the eyes. Immediately, you can see that marketing is heavy in Washington and California, with Ohio rounding up the top three states.

Now that we know what states are getting targeted the most, we should see the same top three states in terms of sales, right? Wrong.

‍

‍

This treemap shows the top states in terms of sales from Decaf Irish Cream. Once again, I filtered out other products to only show Decaf Irish Cream. Both size and color represent the number of sales, and the numerical value equals marketing dollars.

In the previous chart, Washington, California, and Ohio were the top three states in terms of marketing. When it comes down to sales, neither of these states are in the top three of sales. Washington ranks fourth in Decaf Irish Cream sales, while California is eighth and Ohio is eleventh. Promoting Decaf Irish Cream in these states isn’t affecting sales and overall, isn’t resonating well with their customers.

Problem 4: Spending too much money marketing Decaf Irish cream and not seeing a proper return in sales

Let me recap the four findings as to why we are losing money.

‍

1. Green tea isn’t making a profit, probably because of the massive loss in sales in Nevada.
2. Three states have terrible sales; New Mexico, Missouri, and New Hampshire.
3. El Café Grande forecasted a large quantity of Caffe Mocha inventory that ended up getting unsold.
4. They are spending too much money marketing Decaf Irish cream and not seeing a return in sales.

‍

And there we have it. Four potential reasons as to why El Café Grande is losing money. It was fun using Tableau and trying out new things. Creating the ranking calculated field and forecasting was a bit difficult, but great features of Tableau.

I’m sure there were other ways of going about this. If you spot something that can be improved, please let me know! I always appreciate feedback.

‍

Connect With Me: LinkedIn | Twitter | Website

Thanks for reading. If you enjoyed this article, please share it with a friend!