Join Rudolph Rosenberg for an in-depth discussion in this video Adding consolidation levels, part of Financial Analysis: Analyzing the Top Line with Excel.
Now that we know how to use sumifs and pivot tables, there is one more step we need to go through to make our set of data complete. And that's by adding what I call, consolidation levels. What I mean by that is that sometimes the information you have gathered could be hard to analyze. Because it's not aggregated enough, and there are ways to make it simpler. Let's for example that your smallest level of detail in the data set is the product. And so you have one line in your database for each product sold in every invoice you issue.
Let's imagine now that you sell a lot of different products, maybe dozens of different products. Then when you start analyzing your data, it might be difficult to spot trends relating to product performance simply because of the large number of different product which will each have a low volume of sales and therefore not the real big picture for you to look at. A great way to solve that problem is by adding a consolidation level. Which is nothing less than inventing a category in which you will classify your products. So, for example, let's say you have 100 different products.
You might be able to categorize them into five families. If you're selling books, you could selling five copies of 100 different books. Which is hard to analyze since there is so little volume for each book. By categorizing them by families, such as fiction, education, biographies, and so on, you will then have the ability to know if you're selling much more fiction than anything else, and therefore, should focus your efforts in having a wider variety of books from the family on your shelves.
One of the issues with selling small volumes of a product is that you cannot really rely on the data to make important decisions. Selling two books, where in the past you sold one, means you have doubled your sales. But given the small numbers, it's really just selling one more book. Doubling your sales to 100 or thousands of books sold means something. It's a real sign that there's something going on. So in the same way, you can create as many consolidation levels or families as you see fit.
To name just a few, you can do it for products, as we have just seen. Customers, based on their industry, geographical location, international versus domestic. Or timeframe, such as a fiscal year. Month or week, even though those are self-explanatory based on the invoice date. You could have a fiscal year that is not aligned with the calendar year. The other reason for having time frame consolidation levels is that this is not handled as well as we'd like by Excel, especially older versions.
Order size, if you have large deals, and small day to day business, you could be interested in having different families for those two. You could actually create families based on your selling pattern. So, if you know for example, that you have a huge business of orders below $5, it could be interesting to create an order size family to include those. So that in future analysis you could focus on them, or exclude them from your analysis. The good news with consolidation levels is that they are very simple to implement at a future time.
Once you have your database, you can start working and if you realize down the road that you need a new consolidation level to make the data more insightful, it is very easy to do. The more difficult task is if you're missing details and want to add it in the deficit in the future. Then there is a good chance you will have to start over with the data collection process. So always give some thought upfront to the type of consolidation you would like to get in the end. Once you've done this a couple of times, you'll get the hang of it.
Let me show you now how to add consolidation levels. So let's see now how we can add consolidation levels to our database. First let me illustrate why it's so important to add consolidation levels for some of our data elements. If we go to the pivot page. And let's remove our city and product name information from the pivot table. Let's now add in the invoice date. You can see that here all the dates are put in our table and there's no real easy way to say what's the total number for February or for March and so on.
Or even how much revenue we've been generating in 2013 or 2014 because you have the revenue here the way it's displayed on the other page summarized by day. So what we want to do is add information in our data set so that we can have these consolidated at the level that we want to analyze. So let's go back to the data page and let's look into this invoice date issue. Let's add two columns here, just before the invoice date column and we will add here, one column for the month and one column for the year.
So let's name the first column invoice month and let's calculate what is the amount for this date. And there is a formula in Excel to do that, just type equal month, open a parenthesis, and then click on the date on the same line, close the parenthesis and press enter. You will see that Excel automatically identified that this invoice is from February. And so it put a number two for February in the cell.
Let's now double click on the bottom right corner and the formula is extended to all of our rows. Let's now go to the next column and do the same for the year. Let's put here, again there is a formula in Excel for the year. You type equal year open parentheses, click on the date, close parentheses and press enter. And you have the year here automatically calculated. Then we double click again. And as you can see, we have the year 2013, 2013, and 2014 here.
And the same for all of our rows. Now that we have done that, let's go back to our pivot table and let's refresh the table. So that means the table will go back and retrieve again all of the information and put it in summarized format, so let's right click on the table and you can see here an option that says refresh. Let's just click that. What this has done is that on the right side now you can see that there are two new fields that have been created for invoice month and invoice year. So let's just remove the invoice date information that we had earlier and instead let's drag into it the year. And you can see now we have the total revenue for both 2013 and 2014. And let's drag now the month. And you can see we have the detail of the revenue that has been generated for each month during which we invoiced a customer. The major benefit of adding those consolidation levels is that you can easily decide what you want to analyze and add that information into your dataset. So that then it gets automatically put into your pivot table and then you can start analyzing everything that you want to know. For example, now that we have this mountaineer information consolidated. We can simply add, for example, the product name information, drag it down, and see for each month of the year, how much revenue we have generated for each of our products
Also check out the companion course, Financial Analysis: Analyzing the Bottom Line with Excel.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Preparing your revenue information
- Comparing past performance
- Analyzing customer data
- Analyzing product information
- Identifying exceptional revenue