INDEX and XMATCH for locating recent financial results - Microsoft Excel Tutorial

- [Instructor] In FP&A, the ability to reference and extract data isn't nice to have. It's the foundation of almost every decision we make. In this lesson, I'm going to illustrate two new examples of how X match can be used. In one. I'll pull recent financial results and create a dynamic reports, and in the other, I'll show you how you can perform approximate matches to calculate escalating commissions for the sales team. In this example, you can see that I have a forecast of sales and accounts receivable. My sales are coming from my sales commission schedule, and my collections are based upon the terms within my AR aging. You'll also note within the financial forecast, I have this area down here. These are my collection terms on my new sales 30 days. Now, as someone focused on FP&A, I want to know the month in which certain activity occurs. One, I would like to know the last month of my collections on aged AR, and two, I'd like to know if and when my ending accounts receivable balance goes to zero. Now, if you've ever gone through any of my other LinkedIn learning courses, you may have seen me use this technique. I call it model mapping. Down below my field, I use mapping for recurring formulas and calculations because I don't want to nest these lookups within my formulas up here. Let's keep it clean. So here in column V, what I want to be able to do is use X matches to determine the dates of activity. I'm going to put my cursor here at V13 and type in equals index. For my array, I'm going to go up to these dates. Why? Because I want to pull the date at which a certain activity occurs. For row number, I only have one row selected, so I'm going to put in a one. For my column lookup, I'm going to type in X match. My lookup value, I'm going to type in the word true. And then for the range, I'm going to select this entire range of data. But what I want to be able to do is to determine where within that range the following expression is true, less than, greater than, zero. So in other words, I want to know what location within this range, these values are not equal to zero, and that is true. Comma zero for exact match, and then I'm going to start from last to first to negative one. When I close this up, not surprisingly, I get a return of February, 2025. Why? Because it's starting right here. It's taking a look backwards, zero, zero, zero, zero, zero, until it comes here and observes that this is the first instance of a non-zero number. This is taking place in February, and it gives me the return. Because I'm thoughtful of the way that I build my formulas, I'm going to lock this, I'm going to lock this, and when I close this up, I can now copy above, copy below, and copy below. So what is this doing? It's telling me that I have one instance right here. The first non-zero is February, but this, it never hits zero. My sales never stop. My collections never stop, and my aging never stops. But if I were to put this, let's say to zero and across, take a look at what happens. This is now telling me that the last instance of my sales took place in the month of August, and my AR balance goes to zero after the month of August. Finally, right here, my last date of collections takes place in September, 2025. But that's not all. If I go over to my summary page, I have three different elements. One is a dynamic tax box. If I put my cursor here, you're going to note that I've used this connection over to financial forecast B33. When I go over to B33, I have all of this detail that says what's going on with sales receipts and my balances. So what this is doing is, it is a dynamic tax box saying that sales have not terminated. The final instance of receipts is on February, 2025 right here, receipts on new sales have not terminated, and my air ending balance is still outstanding. I also have a dynamic camera view, which I'll introduce to you later in the course. You're definitely not going to want to miss that. This allows me to view what's going on elsewhere in my model without having to jump from one worksheet to the next. If I want to see what this relates to, here's my formula, and all I need to do is double click to take me there. And then finally, I have scenario assumptions where I can flex whether my customers are paying on time or whether we're going to grant them extensions. For example, if I go over here to Summit Sportswear and I say, we're on 60 day terms, all of this information is coming from my AR aging table. And again, I'm using an X match to determine what should those terms be. But if I wanted to go over and say, let's delay this by 60 days, watch what's going on in my camera view window. When I select active, you can now see that I'm delaying some of these collections from month one over here to month three, and my forecast updates accordingly. And when I do, the dynamic text box in the comment down here automatically updates as well. Now it says the final instance of receipts on age AR occurs in March, 2025. If I take this off, it now goes back to February. Don't worry, we'll talk about check boxes and scenarios later in our course. But let's go over to our second example. I'm going to bring you over here to bonus tiers. Here, I have a dynamic table that shows a range of revenues from zero to a million, five tiers, and five different commission percentages. Hypothetically, let's assume that we have an employee that has made $600,000 in sales, but when I take a look over here, you can see that I'm using all of these traditional match functions. It's not giving me the appropriate tier three and 10% commission that I need. But using an X match, this is. It's telling me that when I have somebody who has sold $600,000 in revenue or sales, they should be in this range, tier three, 10%. So let's go over to the sales commissions. I have these eight employees, and these are the sales that they're making every single month. In the next range, I have the cumulative sales month to date. And finally down here, from row 29 below, you can see that these are the various commissions that these sales people should be earning, in this case, pointing to Jill Christensen. She is not earning any commission on her sales until the month of June. Why? Because when we take a look up here, she finally hits the threshold $250,000 in June. Let's take a look, $250,000, she should now be earning 5% on her sales, which of course she is. But throughout the year, she starts selling more and more and she finishes the year at 15%. Finally, when we go down here to the monthly commissions, let's take a look at how this is being calculated. All I'm doing is I'm multiplying the appropriate commission by the cumulative sales to date. In Jill Christensen's example, she has sold $312,000. She should be earning 5% commission. She has earned or accrued $15,613. Now, why does this matter for FP&A? Because if FP&A is working with the accounting team on accruals and booking those accruals on commission, we want to know what that looks like every month, every quarter of the year, until we get to December when we're going to be paying out $888,000 of commissions. Finally, let me bring your attention over here. I'm again using the X match to determine the appropriate month. I'm saying X match where D45 to P45 is not equal to zero. Where is that true? I'm saying, let's take a, not exact match, but I'm saying exact or next larger item. So in other words, I'm trying to identify where the number goes from zero to something above. And then finally, I'm saying positive one, start from first and go to last. In other words, for Jill Christensen, her commissions start accruing on June, 2025, and I have this date here that says, start accruing it in June, 2025. You now know the power of one of the most current lookup combinations. It's not index match, it's index X match, and many different applications of what you can do for applications in FP&A.