Dynamic data tables for FP&A modeling - Microsoft Excel Tutorial

- [Instructor] Why do Dynamic Tables matter for FP&A? Well, remember, in contrast to non-FP&A business modeling, we often have data that's going to need to be updated. Our backend ERP system is going to have new information for new transactions and new dates. And if we want to bring all of that new information into our business or operating model, we're going to want to do that easily and error free. The best use of your time is not spent on data entry and updating formulas, the best use of your time is spent interpreting and acting upon what your data and business models are telling you. This technique that I'm going to show you here is something that I call mapping to the intermediary. It's a three-step process, let's explore what it's all about. On the backend, whether from an ERP or another source, we have recurring data that is going to be imported into the business model. We can do this manually or we can use a tool that Microsoft calls Power Query. In this first example, I'm going to show you how this can be done manually. And in the next video, I'll show you how you can do this automatically through the use of Power Query. Let's begin. In this dataset, one of the first things I'm going to do is I'm going to select the entire range of data. I'm going to use the keyboard shortcut, Ctrl + Shift + Space bar. And you'll note that it selects all of my data from row one all the way down to row 4977. Next, I'm going to go up here to my insert ribbon. And on insert, I have this icon that says table. When I put my cursor over it, it says, create a table to organize and analyze related data. Tables make it easy to sort, filter and format data within a sheet. When I select that icon, this window pops up that says, create a table. Where is the data for your table? And of course, it highlights from A1 all the way down to M4977. This last piece here is actually very important. It says, my table has headers and it's checked. If I were to check off of that box, it would ignore all of these various names and labels that I have in row one, but these are going to be very important for me to be able to capture all of the data that falls below. I'm going to select my table as headers and click OK. When I do, you'll now note that I have this nice, clean aesthetic visual of blue, white, blue, white, alternating lines. You'll also note that when I click on the data, I end up with this new ribbon up here that says table design. On the right hand side, I can change the color of my alternating bands. Over here, I can change the table style options to add in a header row, banded rows, add in a total row, and then over here on the left, you'll now note that when I add in a table, it declares it a table name. In this case, it has named it table three, because in prior days, I may have added in table one or table two, and Microsoft's approach is that we'll continue to add in table names in sequential order. But as you'll recognize, table three is not a great name. I'm going to select here, and I'm going to call it Company_Sales. When I hit Enter, this is now declared Company_Sales. Another way that I might be able to go about naming a table is if I go to my formula ribbon and I click here on my name manager, and when I do, I get this window that pops up and it says Company_Sales. If I wanted to go in and change the name or what it refers to, I could click my edit, go here, change the name or what it refers to Raw_Data_FULL A2 to M4977. One last piece that I want to share with you. You'll note that up here I have a white box that when I put my cursor over it, it's actually called a name box. When I select the entirety of my data, in fact, it discloses the name Company_Sales. All right, well, this is where things get interesting. I'm going to go all the way to the right and all the way down to the very bottom of my table. I'm going to put my cursor here on that SUMIFS formula. But before I do, I want to bring your attention to this right here. You'll notice that there is a little bit of a wedge at the very bottom and far right part of this table. This is something that I call the data wedge, and it indicates that this is as far as the data goes, any data that's going to come below or over to the right is not going to be captured under the name Company_Sales, but anything that is above and to the left is. At M4980, you'll note that I have that SUMIFS function, and it goes from M2 all the way down to M4977, exactly the same range that it went to in one of our prior videos. The next argument goes from C2 all the way down to C4977, the same range that it went down to in our prior video. And then of course, this last piece is it says in quotes, "Retail." So this is no different than what we saw in our prior example, this is in fact not a dynamic range. So if we were to put data down below this Dynamic Table, it is not going to get captured. So my question that I'm going to pose to you is, well, what can we do to change this static range to one that is perhaps a little bit more dynamic? The first step that I'm going to take is I'm going to go up here where it says M2 to M4977, and I'm going to actually type in the words, Company_Sales, and you'll notice that when I do, this name of the table that we created appears. I can even go backwards and just type in the word company, identify that Company_Sales pops up, hit my Tab, and it will know exactly the Dynamic Table range that I'm wanting to capture. Now, the next piece is I want to add in my brackets. When I add in my brackets, it's going to prompt me for all of these various headings of my Dynamic Table. This is why it's so important that when you insert a Dynamic Table, you declare that it has headers. Order total, this is what I want to capture all the way here at the end, this is what is in column M. I'm going to hit my Tab, and I'm also going to put in a close brackets at the end. So what is this doing? It's saying SUMIF Company_Sales Order Total, it is going to take a look at column M and say, that is the range of values that I want to sum. Next, instead of having this be fixed at C2 to C4977, I'm again going to type in the word company, hit Tab, it's going to grab the Dynamic Table, Company_Sales, hit my opening brackets, and in this case, I want to go to column C. Well, what that is is going to be, let's take a look, OrderType. I'm going to Double Click OrderType, close those brackets, and when I hit Enter, let's take a look at this formula. Instead of the static formula where it went all the way from the top of column M all the way down to 4977, this in fact is capturing the entire range of data all the way from the top, all the way to the bottom of this Dynamic Table, which means that as I start to add in more data at 4978, 4979 and below, this formula does not need to change. The same is true if you take a look at Company_Sales[OrderType]. As I continue to add in more data under column C, at 4978, 4979 and below, this formula will not need to update. Let's take this one step further. I'm going to put my cursor over here at B4979, and I'm going to write in OrderType and to the right, I'm going to insert what we call a data validation. I'm going to go to my data ribbon. I'm going to go over here to this cell that looks like a checkbox and a do not enter sign, and it says data validation. When I go here, it's going to say, allow what type of value? And right now, by default, it says, any value. I want to change this to a list. And finally, I'm going to type in the words retail, wholesale, and hit Enter. Let's highlight this light blue to indicate that this is in fact an input. When I select this date of validation dropdown arrow, it gives me one of these two options, either retail or wholesale, because you'll notice that all of these values up above in column C, read retail or wholesale. Let's go over here and select retail. Finally, I'm going to go over here to my SUMIFS formula, because you'll note that this is a hard coded text ring that says retail, but I want to create a little bit of dynamic functionality that allows me to select whether I want to capture the sums for retail or whether I want to capture the sums for wholesale. I'm going to go over here, select this dropdown, C4979, hit Enter, and you'll now note this is in fact still 782420. If I just zoom out a little bit, you can see what my data validation is going to look like, I'm going to change this to wholesale. And when I change this to wholesale, this now shows that I have a total of $6.116 million. Clearly, this business is doing a whole lot more business in wholesale than it is in retail. Now, the last piece I'm going to share with you that's very important is I'm going to actually insert a handful of new lines, and then I'm going to grab some of this data. Remember, my total for the wholesale is 6.116. If I add in new data down below, you'll now note I go all the way down here. You'll see that this now goes all the way down here to line 4984, my data wedge goes down with it, and my totals are 6.120 versus what they were before. And if I were to move this down and say, let's change wholesale over to retail, this also goes up now to $793,000. So whether you insert five new lines or 15 new lines, or 150 new lines, or maybe even 250,000 new lines, the formula that you have here, taking a look at the name of the Dynamic Table, the bracketed columns, it never needs to change. So whether you add in new data manually as we've just done, or through Power Query, which we'll talk about shortly, this maintains its integrity and continues to be dynamic. This table can be referred to not just on this worksheet, but throughout my entire model as well as possibly other files as well. So I would challenge you if you want to get started in dynamic functionality and explore Dynamic Table functionality, which exists on your insert ribbon, it is one of the easiest ways to get started.