Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Profitability Analysis and Finding Anomalies
Profitability Analysis and Finding Anomalies
Learn more about profitability analysis and finding anomalies.
Hello, welcome back. Now I remember that actually all the dashboarding that I did, and all [INAUDIBLE] the goal was to present this data to our new sales manager Lucy. So I did that. I went to Lucy, showed her my work. And she was really happy about it. She was somewhat familiar with the pivot tables from her previous job. But I was able to show her a lot of new things that she didn’t know and was happy about. But like always, once she was happy with this, she wanted more. She wanted more sophisticated analysis.
And especially, she was– she wanted to see an analysis of profitability, not because all the charts that I created so far and all the pivot and everything was showing just the revenue. But revenue is not everything. We also want to know about profit. So we have the cost. We have the revenue. And between them, we can calculate the profit. So I promise to go back to my lab to create some profitability analysis and come back with the results. So let’s see how I was able to achieve that. So here we are back into our Excel. And I created just one large pivot just to experiment with it. I left aside for a moment the previous dashboard.
So we have a pivot here that have states and rows. It has the revenue and the cost on columns. And it has the proper product category as a philtre that I can go ahead and just philtre one or just leave it as all product categories. Now I want to calculate the profitability. So one way to do that, I was thinking to myself, is I can go to my original table and start adding some calculated columns. So I can say, I want to calculate profit and say that profit will be equal to the revenue minus the cost. All right, so I have here this column. I go back. I go back to my– no, not this one– this pivot.
I want to add the profit column to the pivot. Remember, that in order to do that, I need to click refresh so that the pivot will see the new column that I created. Here it is, profit. Again, I rather would like to format correctly. So from here I can [INAUDIBLE] currency. All right, and everything looks good. So here, in this column, I have 1,200 minus 1,000. It’s 211. In some cases, I actually have negative profitability. Everything is fine. But profit is one part of the thing that I want to present, one part of the analysis. Another one is the profit margin, the percent.
So again, I was thinking to myself, I can go and add another column that had a margin. And in the margin, I will say that the margin is equal to the profit, that we just calculated, divided by the revenue.
And again, I’m going back to my pivot, refresh, doing the same thing. And I’m pretty sure that I’m almost done. But if you– and I can actually go ahead and make this into a percent because this is what I was planning to show is a percent. So number format, that’s a percent.
And immediately, it’s very obvious that something is very, very wrong here. Notice that I have here 154,000% and 1,800% and all kind of crazy numbers. They do not make any sense as a percent. I would expect a percent to be between, I don’t know, minus 1 and plus 2 or something. So minus 100% to plus 200% something. Definitely not thousand and thousands of percentages. And the reason for this is that if I go back to my table, I have this as a column. And the pivot doesn’t know that this is a very special calculation that’s a ratio between these two other columns. So when the pivot uses this column, it’s actually created.
And the name it gave to this column tells us something about the wrong way that it actually was treated. It says some of margin. It will go ahead and sum all the values for the percentage. And this is obviously wrong. You cannot add percentages. You have to do it in a different way. So I will just go ahead and right click, Delete, [INAUDIBLE] column. This obviously didn’t work. I go back to my pivot. By the way, I click Refresh, see what happened. You see what happened? When the margin column disappeared from the table and I clicked refresh, it just gracefully removed from the pivot table because it’s no longer available. So I have to do it in a different way.
And this way is using a feature of the pivot, which is called calculated field. So from the Analyse part of the ribbon– let’s make this whole thing a little bigger. It’d be easier for you to see. So the field items in sets option in the ribbon for the field has something called calculated field. So I’m going to create a calculated field called margin. And I will say that the margin is equal to– and here, you see the list of all the fields. So it’s equal to the profit divided by the revenue.
And I have to format it because by default, it got the format of the elements it was created from, which was currency. And it’s obviously not currency. It’s a percent. So I go to number format and say this is actually a percent. And now I see the percentages the way I expected them to be. I see them as regular percentages. And the reason is that the calculated field is calculated at the level of the aggregates. So first, the pivot aggregates the value for revenue and for cost and then and for profit. And then it applies the calculation of the margin. And that’s why it’s correct now.
It wasn’t correct when it was actually adding up the margins from the detailed table. Now, as I told you, we have another course following this one, which is already– was released before that, before this one. And in this other course, you will learn much more powerful ways to create calculated fields which are in this case called the measures. And you’ll see that we really can achieve much more with those measures. Calculated fields are somewhat limited. The syntax there is not very powerful. It is a subset of the calculations available with Excel. If I want to go, for example, and edit this, I have to go to calculate fields, look for it here, margin, see the expression here and fix it.
It’s not really modern UI, and very easy UI. But still, it’s a very important part of the functionality of the pivot table. Without it, we will not be able to calculate something as simple as the margin, which was one of the initial requirements. Now, another– once we have the margin, I think it’s a good place to introduce another element that can actually add a lot of power to the pivot table. And this is conditional formatting. So conditional formatting is something that exists for an Excel for a long time. But with pivot tables, it has some extra meaning and extra power. So it’s from the Home tab. I see conditional formatting.
And the way to use it on a pivot table is special. You select the conditional formatting you want. And there are different ones for this column like a revenue. I will choose something like a data bar. And any moment– now, at the beginning, it looks stupid. It just gives me a conditional formatting for just this cell, which is not really very interesting. But from here, I have this little icon here that I can open the options and say, do we want to apply this condition on just the selected sales? No, I want to apply it on all sales showing some of revenue for the level of state.
And the reason I say for the level of states will be– you know what? Let me just do it on every value. So now you see that it actually was applied to also the grand total. So the grand total is obviously much, much larger than each one of the individual elements. So it actually defeats the whole point to see because the purpose of these bars was to show in the sale the relative size of each element. So I would just undo it and apply again the conditional formatting. But this time I will say that I want to apply it only for the level of state.
So if I have some other subtotal, and other levels of the grand total I don’t want to apply. And now you see that the background of each sale shows the relevance, the size is based on the size of the value in it. What about the margin? And the margins even more powerful. I can apply conditional formatting which are based on colour, called colour scales. So I can say I want to apply colour scale. And here, I have no problem saying that I want to apply it on all the values.
And now you see that the default behaviour is to apply a colour scale between green, and red that will show the small numbers as red, the big numbers as green, and the middle numbers as kind of yellowish to orange. And there’s a whole scale. And again, in the exercise, we will go into more detail and also about examples of how to deal with examples that have outliers with numbers, which are way, way above or below the regular range of numbers. But immediately, you can see that this is a very good way to detect anomalies and like states, in this case, which have much lower profitability or much higher profitability.
And when I philtre it, you can see that it’s, of course, it responds immediately. And it’s very, very easy to detect which are the good and the bad and the average. The grand total is no problem. I actually applied it to the grand total because the grand total is kind of the average margin for all the data that we see. So we covered some of the really powerful additions to the pivot table by applying calculated fields and conditional formatting on the pivot. See you on the next one.
Up to now, we have mostly explored ways to use Excel to present data. Excel is also a useful tool for analysing data. When using calculated fields in pivot tables and conditional formatting, data can easily be analysed and compared.
In this video tutorial, you will learn how to analyse the profitability of businesses and detect anomalies in data using calculated fields in pivots and conditional formatting.
Calculated Columns
Similar to the standard calculations in Excel, you can create calculated columns.
One way of creating a calculated column is by simply adding a column to your Excel table with a standard calculation. For example, if you want to calculate profit, you will calculate revenue minus cost. Remember to refresh the data in your pivot table to display and visualise the newly added data. It is important to note that this method will not work for all calculations.
Another way of creating a calculated column is by using the Calculated Fields function from the Analyse section of the ribbon. This function allows you to select the fields you want to use in your calculated column and set a format or data type, for example, percentages.
Conditional Formatting
Conditional formatting adds a lot of power to pivot tables. It allows you to apply specific formatting to cells that meet certain criteria in your pivot table.
The following resources provided an in-depth overview of how to calculate values in pivot tables as well as how to use conditional formatting in pivot tables.Click on these links to be taken to the resources.
Conditional Formatting on the Table and Pivot Table
When analysing large volumes of data, Conditional Formatting is, as you can see, a handy function for the detection of patterns and trends and funding anomalies. Having said that; problems do sometimes occur, and when this happens, it is useful to systematically work your way through the error, to solve it.
The following are examples of errors that might occur when dealing with Conditional Formatting on the table and pivot table. Take a moment to work your way through the points listed below to get a sense of how to approach a Conditional Formatting error.
Conditional Formatting on the Pivot
-
Apply conditional formatting rule on the revenue column at the level of countries as blue bars.
-
Apply a condition formatting rule on the revenue at the level of countries as green bars.
-
Apply a rule on all cells with Margin% value as colour scale.
-
Edit the table and enter a value of 999999 on the first row in the cost column. Refresh the pivot.
What happened?
- The margin calculation returned the wrong value.
- The error in the cost value in one row caused a very low value of Margin% and in comparison, all real values seem average.
This is a common problem with outlier values. Fix it by using manage rules /edit rule and changing the default settings for Minimum and Maximum settings to 10th percentile and 90th percentile respectively.
Conditional Formatting on a Table
-
Create a rule that will highlight the Cost column where The value in the cost column is less than 10.
-
Change the style to the first style on the left in the first row, in this way the style will not interfere with the rules.
-
Create a rule that will apply to the Unit Price column. Choose the top/bottom rules and create a rule to highlight the top ten values.
-
Filter the column by colour and selected only the highlighted rows with light red cell fill.
-
Create a rule that will be highlight the unit cost column if the unit cost column is larger than the unit price column.
-
Filter by colour, how many rows are filtered (7712).
-
Apply the rule so that it will colour the entire row:
What is the expression used for the rule so that it will affect the entire row? Drop your answer in the comment section below.
Join the discussion
So far we’ve mostly asked you to answer questions, but we’d like to learn from you as well. Do you have any personal tips or tricks for managing sets of data in Excel that you would like to share with us, and your fellow learners?Use the discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to check the step off, then you can move to the next step.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
