Excel Excellence: Unleashing the Potential of Dynamic Arrays

Foreign. We'll get started here in a few minutes. If you're, if you're out there already, I know we got a couple minutes before it gets starts. Please let, let me know where you're coming from. I've seen a few, few people say, hi, I'd love to know where you're coming from today. And then let me know what you know about dynamic arrays. We'll probably get started in about five minutes or so. In the meantime, I'm going to share a presentation for those who do not have the file. People who registered prior to today. I sent out a file this morning, and so what I'm going to do is I'm going to share the file here. Give me one moment. All right, Give me just a moment here. All right, you should be seeing a presentation. And for anyone who doesn't have the file we're going to talk about today, feel free to download it. I'm going to try to make this as interactive as possible, even though I know people can't directly chat with me. So feel free there to download the Excel file. We'll get started here in a few minutes. It's not quite 10am yet, so if you're out there, you can hear me. Please let me know where you're coming from. I know we have people in Streamyard. We have some that have joined via LinkedIn and others who have joined via YouTube. So just let us know where you're coming from, where you're located. We got about 14 of you out there right now, and we'll get started in a few moments. Hey, welcome, Pablo. Thank you for the compliment. And while we're waiting to get started, I'm gonna share a couple Excel jokes, because that's what I do. All right, so if anyone knows the answer, you're welcome to put this in the chat. What's a good one? Does anyone know what happens when a bar chart works out? The screen is all black for you, Marie. That's not what I'm showing on my end. If anyone else is seeing an all black screen, please let me know. You should be seeing a screen that says download the Excel file there. And let's see, we have people coming from New Jersey, Los Altos, California. Another one from Jersey, Eric Ohm. Lambda Lovers. Thank you for joining us, Eric. You should be teaching this course, but does anyone know what happens when a bar chart works out? It becomes stacked. Get it? All right, let's see. We'll do one more as we're waiting for people to join, and then we'll get started. Why did the spreadsheet hate his job? Too many functions. All right. And Excel keeps coming out with more and more. So I want to thank everybody who's out there and who's joined me today. It looks like we have about 30 of you out there now. You know, please feel free to ask questions. If you want to download the file we're going to talk about, you can go ahead and scan the QR code here as well as anyone who registered. As of this morning, I emailed the file out. You don't have to follow along, but I know if it was me, I appreciate being able to follow along. As we talk about Excel and dynamic arrays, I'm excited for who we have in the audience. Anyone who knows Eric Ohm, he's out there. He is an expert when it comes to comes to Excel. I've had the opportunity to listen to him speak and I know he knows his stuff. So let's jump into it. What I want to cover today is this is really helping people get more familiar with dynamic arrays. If you already know dynamic rays, you've used them, you probably won't learn a lot from this. This course, hopefully I thought this course, but this webinar, hopefully you do get something out of it. But my goal is to help people realize the benefits. How dynamic arrays can change the way you work in Excel. And I can tell you, anyone who's used them and uses them knows they can change the way you work in Excel. Yeah, that I, I see your note mirror that that's fine. That it's all black. I understand what you're talking about. Not a problem. There's a couple sheets in that file. When you open the file, the first sheet may be all black. That's just a divider sheet. All right, so we're going to do a brief overview of what dynamic arrays are. We'll probably spend about 10, maybe 15 minutes in PowerPoint. Then we're going to jump into the Excel file. What we're going to do is I thought it would be fun. I've had a ton of people ask me about the dynamic sensitivity table, so I'm going to walk through with you. That exercise, that's something I do normally. It's in my dynamic arrays course that's getting ready to come out. If anyone's not aware, I was fortunate enough to be selected for the New Wharton Online and Wall Street Prep FPA certificate to provide four hours of Excel training. And so I do I touch on tables, dynamic arrays and power query because I believe as an FPA and a finance professional. If you learn those three three things well in Excel, you'll be far ahead of 95% of the people you work with. Add in being able to build a good three statement model and use design principles, you're probably ahead of 98, 99% of the people out there. All right, so, and then we'll just do a quick wrap up, a key takeaway. I'll share a couple of my other resources, some things that are coming for, for all of you. So let's go ahead and jump in here. If anyone's not familiar with dynamic arrays, they're they're a feature for Excel 365. They're also in Excel 2021, but really 365. And what they allow you to do is a single formula can return multiple values in the grid. So up until roughly 2019 outside of using Control Shift Enter, if anyone remembers the squirrely brackets, I had the book, it was written by Mike Girvin called Control Shift Enter and that was the only way you could do arrays and they weren't dynamic. Today Excel automatically works in multiple cells within the grid. So you could write a formula in A1 and depending on the inputs it could be an A1 through C10. Another time, if an input changed, it might be A1 through D7. And I'll show you how that works. So what that means is they spill. That completely changes what you can do in Excel. It revolutionizes the way you can work in Excel. So I, I love this quote. I love Excel. Jet Dave Bruins is one of my favorites out there. Whenever I want to see how a formula works. He's one of the first websites I go to and what he said is dynamic arrays are the biggest change to Excel formulas in years, maybe the biggest change ever minute. And since dynamic arrays have come out, they've come out with things like lambda, which are another game changing thing. I'm not a lambda user, I'll admit it, I've used some other people's lambdas. You get into a little more programming, but it opens up a whole new world in Excel. And then one other quote here. So this is Mike Thomas, he's an Excel expert. He's like, Excel today is not the Excel of yesterday. Dynamic array functions are one of the relatively new must know game changing features. Give me one second here. Gotta turn something off. Something came through on my phone or my computer. Dynamic array functions will not only simplify the way that you build formulas, but they will also simplify the way you build and Work with Excel files. And you know, if any of you are using dynamic arrays today, feel free to put in the comments how they've worked for you. Have they revolutionized the way you do some things? They have for me, you know, I still do many things the way I used to, but there's a lot of things I do differently now. So here, as we look at dynamic arrays, this is a list of the functions. Obviously we're not going to go through all these functions, but there's about 30 new functions and I'm not even counting what's called all the lambda helper functions. Some of the ones I'm really excited about that are in Beta. There's a new pivot by function and group by, so you don't even need to create a pivot table. You could create it all with functions and have the, you know, data automatically update off your table and not have to hit refresh. Now, there's still benefits to a pivot table, but this creates additional functionality. There's xlookup, which is probably the one many of people have heard of Unique filter and many others Sequence. There's sort, sort by and and they just keep coming and they all allow us to work off multiple columns, multiple rows with that one formula in different ways. And if you're using dynamic arrays today, please feel free to throw it in the comments. I'd love to know what's your favorite? Do you have a favorite dynamic array formula? I'd say the one I probably use the most. I don't know if it's probably a toss up between unique and xlookup for me, but if any of you are using them, I'd love to know your thoughts as well. All right, so let's talk a little bit about the benefits of dynamic arrays before we walk through how they work so you can simplify formulas. If any of you ever had to write a formula to try to get a unique list from something, maybe filtering it by a name. I still remember was working at a company for sales commissions. I found this aggregate formula that Mike Girvin had written and it was super long and it helped me create this list that I needed to pull out certain records from a list for this commission file I was doing. And then they came out with dynamic arrays and the formula went from like this long to. To like this. It was like a filter, unique. And I'm done. It was super simple. So I'm seeing some comments come in. Mark Proctor, he should be teaching this course too, him and Eric. But I use them all the Time filter is the one I use the most. Yeah, filter's a great one. I'm probably unique, but I'm with you. Let's see. Unique and filter is Rick. So we got a few people commenting. All right. So it really does simplify a lot of formulas that can be really hard to write before. Next one is dynamically update, right? You change an input and you don't have to worry about, oh, I now need to go drag it down. It needs to go another 15 rows or needs to go over two more columns. Just can automatically spill into that part of the grid. So that's a really, really cool feature that we'll get into next. There are cases where you get improved calculation speed. Now, there are some cases where xlook example xlookup can be slower than Vlookup because it has six arguments. Vlookup has three. Right. Just because on the whole, there are areas where it improves calculation speed, there's less formulas doesn't mean in every situation it's going to be faster or it's the right formula to use, but there are definitely cases where it helps improve things, being able to use dynamic formulas. Next, Microsoft continues to come out with new functions. As I like to say, if you're using the Excel of 10 years ago, you're selling yourself short. You need. Ideally you need 365. If your company doesn't have 365, you're limiting what you can do in Excel and especially with Python coming and just all the changes that are happening. And then last, I believe reduced errors, you're doing le, less copying and pasting. Right. Like dates where you. Maybe you copy a date across or do something wrong. If you can write a formula that automatically adjusts all of that, there will be less of a chance for error because the less formulas you have that you have to write and that somebody has to review, the less chance for error. It's not going to eliminate it. We're human. Nobody's going to eliminate errors in Excel. If you're able to do that, more power to you. But I haven't seen it. I know pretty much every file I create somewhere probably has an error. My goal is to make sure they're not material and that we have checks to make sure we cover as many of them as we can. So what are the challenges? Obviously, dynamic arrays are not perfect. They're not to be used in every situation. So what's some of the issues with them? There's a couple I'd like to call out. Probably the first and biggest Which I think is going away more and more is compatibility issues. If you're on 2019, 2016, 2010, they're not going to work. Simple as that. So if you're sending files to somebody that has an earlier version of Excel, don't use dynamic arrays. Sorry, that. That's why I recommend 365 Excel tables. You cannot use dynamic arrays inside a table. You can reference a table with dynamic formulas, but you'll get a spit what's called a spill error if you try to use them in tables. And we'll show a little bit here soon. And then next, financial models. It's really difficult to build complex financial statements using fully dynamic arrays, particularly Corkscrew. There are some things that have been done with Lambda to solve that, if you're interested. If anyone's interested in that, check out Craig Hatmaker, 5G. Also Lance Rubin, who's worked with Craig on that. They're finding ways to solve that. And I think, you know, we're a little ways away, but in the future we'll get to that point someday. Models will probably be all dynamic. We're not there yet. I don't build fully dynamic models. I've dabbled with it a little bit on what other people have done, but I definitely use dynamic race. So that's one area to caution. And then there's a learning curve, just like with anything. I don't know that that's necessarily an issue. It's more of a challenge. You have to be willing to educate yourself and invest in yourself. And frankly, you need to do it. Here's the reality. Even if you are not using dynamic arrays in anything you do, sooner or later somebody's going to send you a file that uses dynamic arrays and you're going to need to understand the basics to understand the file. So at this point, at a minimum, you need to at least learn them, invest the time to understand them. Thanks for joining, Caroline. I see another one in there. And then formatting challenges. One thing I hope they'll address in the future, and I'm sure anyone who uses dynamic arrays have dealt with this. I have a dynamic array. It's 10 rows. I changed the formula, it's now 11 rows. My formatting doesn't adjust. So if I formatted those first 10 cells, now that it's 11, I gotta go change the format and we'll show an example of that. But that's a challenge, one that kind of drives me crazy that I gotta remember, oh, I gotta go in and adjust my formatting. And so here's a list. I'm not gonna read through all of these. We kind of covered this in before. But when to not use and when to use. So compatibility Excel tables. You know, I think when it makes it easier to follow the model, when it makes it easier to understand your formulas, I think a good example is unique or filter when referencing an Excel table. They're fabulous for data validation dropdown lists instead of using offset. I use unique all the time with data validation dropdown lists and we'll show an example of that. So those, those are some cases, like I said, creating dynamic lists is another great area to use them. So here, let's just. I'm going to show one example in here and then I'm going to open up the Excel file and we'll walk through a few examples. So here's an example of a dynamic array formula. And what we mean by dynamic, just to kind of explain is this formula lives in cell A2, but it's spilling into cells A3 and A4 in this example. And you can see the formula, it's referencing something else in this workbook. But you see that little blue line? That's what we mean by dynamic. The formula lives in one cell and it spills versus non dynamic. You see the 1, 2, 3, it's referencing multiple cells. Each cell has its own formula. So that one is referencing E2, the two is referencing E3. Where here it's one formula. That's. That's a dynamic array. What, what we're going to do now, instead of going over this terminology here is I'm going to ask everybody if they have the Excel file to go ahead and open it with me. And I'm just going to walk through a couple examples to get started here. This is the model I built. And I'm sure there are some things I could do to improve it. But I wanted, I wanted to show what you can do with dynamic arrays. This is a simple investment model. So here I have a bunch of assumptions. The model years, the discount rate, what's my revenue in year one, what's my growth rate, what's my expenses, and my initial investment. Pretty simple. Down at the bottom, I have some calculations. Net present value, my internal rate of return, the different methods you might use to evaluate a project. I get this is really simple. But what I want to show is by using dynamic arrays, I could come in and say, well, what is the. What does the project look like if I have 20 periods? Notice, I did nothing but change that. And that all spilled. So now I can See, oh, hey, my net present value is much higher, higher rate of return and I can look how it changes. Well, what if it is, you know, five years for the project and you know, a 15 interest rate? Okay, well, now the net present value is negative. All right, you know, turnover rate of return shows 1% there. And you can see the different methods. And I can go through and play with this. And it automatically adjusts without me having to do anything that is dynamic. Someone's saying the slides are not clear. You should be seeing Excel right now. Is that clear to everybody? Or let me know if it's not clear and I can try resharing if needed. All right, not seeing any other comments, but please keep the comments coming. If it's not clear to anyone, let me know. Here. Here's another example of dynamic arrays here. So what I have. Oops, I just made that a little too large. Sorry about that, Wanted to make it a little larger. All right, give me one second here. So what I have is I have this list here in a table where I have 10 sales reps, three months of data from each sales rep using dynamic arrays. This is one formula here. It starts in cell B9 and it's spilling to B20. And the way you could tell, it's one formula. Let's say I change the title here to just be sales person instead of sales rep. We'll start there. Notice that automatically updated as well as let's say I add sales rep 11. We hired a new sales rep in April, 50,000. And they've, they're added. You'll notice the formatting though, didn't adjust. Right. I want that bold to be the total. So I'd have to go through and then I'd have to fix that. But that's One example using VStack and HStack of a dynamic list that have created with dynamic arrays. There's other ways this could be done. This could have been done kind of filter. What I like about this one is you're getting the headers, you're getting the total and each individual all in one formula. So it was pretty complex. Again, something I learned from Mike Girvin, Excel is fun. Excuse me. And then the next one is the sensitivity analysis. And let me just enlarge these a little bit. All right, so what I have here is I have a basic sensitivity analysis. And you know, with sensitivity you're often looking at different prices. So, so let's say I really want 10, I want to look at it every 20 cents, but I want to look at it at a two dollar range. So I change this to 10 and you can see that automatically adjusted there. It's going from 8:30 to 10:10. Then I have my cogs and I could see my margin for each of them. So this is all dynamic. Let's say I want to look at cogs for maybe 15 values that automatically adjusted and I can come in and adjust any variable. How many units I sold, what is my worst case price, what's my base price? So that's kind of what's my midpoint. And I can see all those and play with them. You know, what's my price increments, what happens at every 10 cents instead of 20 cents, know what happens at every 5 cents and it automatically updates. That's another example of the power of dynamic arrays. I'm not dragging formulas anywhere. I'm changing an input and it's automatically adjusting. So those, those are three examples for dynamic arrays. I mean there's obviously more we could do. Now let's walk into a little bit of how they work. So if you have the workbook with me, go ahead and open it to the sheet called how dynamic Arrays work. And I'm going to show example here. So what I have is you see the numbers here? It says 1, 2, 3, 4, 5, 6. Those are referencing a range over here. So that this is, this is dynamic in that it spills. We'll show more of formulas that dynamically adjust. But what I want you to see is you'll tell. So you can tell something is spilling and it's dynamic because one, it's gonna have this blue box around it. If you look at that, you'll see a blue outline. The second thing you're gonna notice that I want you to be aware of here is the formula lives in one cell. You'll see I did this B8 pound. I'll explain the pound in a minute. Don't, don't worry about that at the moment. But what I want you to see again, that because as that pound, that's a dynamic formula in that it has that blue outline, right? You can see it's going down all these cells. But the formula only lives in the first cell. When I type formula text, you can see that because 2, 3, 4, 5 and 6 are N A. You can also see it if you come in here. You'll see K8 to K13. Now go to the next line. It says K8 to K13, but I can't change it. It's grayed out. This is grayed out. Grayed out, grayed out. So like, you know, if I at. Let's say I added seven over here and I change this range, right, it's going to reference 7, you can see that updated and that one's grayed out here. You know, the old way of Excel and we won't get into what's called implicit intersection. But basically if I would have referenced this range on this row here, I would have got a one returned. If I want something to be non dynamic, Excel created the at symbol so that you could say just give me a single value from a range. And so that's the first thing I want you to see with dynamic rays. First concept to understand is you're no longer working in one cell. It's not just one value. And give me one second here, I'm just checking. So Badre, if you have Excel 365, you have Dynamic arrays. If you're running an earlier version, 2019 or earlier, you do not have dynamic arrays. So if anyone's wondering what version of Excel they're running, you'll want to go to file and I believe is it account or options? Yeah, under account. So see right here, mine says Microsoft 365 Apps for Business. So if you go look at your account, if you're not on 365 or 2021, you do not have dynamic arrays. All right, so let me go back here. The second example I want to share with dynamic arrays is they can operate on multiple cells at once. So what I mean by that is I've written a. This isn't how I would do this today, but this is for examples. I've written a left formula here with a fine. Hey, find the space, give me the person's first name. Right? In the old days, here's what you would have done. Before dynamic arrays, I would have referenced the first one and then I would have came in and dragged down my list. But dynamic arrays can take multiple values in their inputs and pretty much, you know, almost everything can now. So I can reference the entire range and you'll notice it's giving me an error because there's data in there. We'll talk more about that in a minute. But notice that formula there took the entire range of values and processed them all at once, so to speak. So that's kind of the second example, operating on multiple cells at once. And feel free to, you know, kind of try that yourself. Right? Maybe I really only wanted to operate on two for some reason. I could change this. There's ways you could make make that all dynamic. Oh, I referenced oops B2 to B22. We'll fix that there to kind of show an example. The third thing I want you to see is how the, how you can adjust the size of an array based on the inputs. So one of my favorite array formulas, especially for dates, is sequence. Yeah, B. If you there is, you'd have to go out and get it from Microsoft. If you don't have it right now, the only way you could activate it is by getting a 365 subscription. So I just, I saw that com comment come in. Keep the comments coming and I'll do my best to monitor those throughout the session. So here what I want to show is kind of this third thing to be aware of is dynamic array formulas can adjust the size based on the inputs. So what I mean by that is here I'm using a sequence formula and we'll talk a little bit more about that. But what a sequence does is it just sequences a range of values. So it could be a sequence from 1 to 4 or 1 to 8. In this case, what it's saying is give me three rows of data with two columns. So you can see here, this formula has three rows, two columns. Start with the number 2 and then this should not. This should be the increment. This is the step, not the stop. Let me fix that there. And it increments it by three. So you can see it goes 2, 5, 8, 11, 14, 17. But watch, if I change an input here to four rows, it's now four. Let's say I want to start at one and increase it by one. Right. You can see 1, 2, 3, 4. If I add three columns, I now get 12 values. So by changing an input, that formula can automatically adjust its size. Right. I could do five rows. If I do eight columns. Oh, now it's going all the way over to 48 times five. Five rows, eight columns increasing one by one. So that's, that's the third three. That thing. And then the last example I want to show here, and I'm going to make this a little bit smaller, we're just going to do two columns for the moment is how you reference a dynamic array formula. So if you have all these dynamic arrays, what if you want to use it elsewhere? What if you want to reference a formula that's dynamic? How do you do that? So we'll come here and notice if I just come and reference this right here, it only references that one cell. No spilling occurs. Even though this formula is in 10 cells. Right now it's returning one cell. If I want to return that entire formula. Notice when I hit the pound at the end. I said that earlier. The pound symbol is to reference a dynamic formula. Foreign. Melissa, how would you use sequence in modeling? Probably the most, the most common place I use sequence, there's two use cases. I use it for drop down lists. Sometimes I use it for dates in particular, like if I'm coming in. And every month when I drop a new data, I want to add a new month. I can dynamically sequence the months using sequence with the date formula. I will also show an example for the sensitivity analysis. But I'll show one thing real quick. This sensitivity analysis is using the sequence formula. It's sequence, sequence and one other formula. This whole thing is only through sometimes I use it for dates in particular, like if I'm coming in and every month when I drop a new data, I want to add a new month. I can dynamically sequence the months using sequence with the date formula. I will also show an example for the sensitivity analysis. But I'll show one thing real quick. This sensitivity analysis is using the sequence formula. It's sequence, sequence and one other formula. This whole thing is only three formulas, sip and then the inputs. And so sequence is the key to these sensitivity tables. And I'll show a real world example of that at the end if you just bear with me. Yeah, Jared, great example. A debt amortization table, much like the sequence, the sensitivity table, same type of idea. So I had someone ask a question here that it doesn't feel dynamic. Yeah. So your, your thing there where they're referencing B22. Yes. This is not dynamic. What I'm showing here is more the idea that you can input multiple values. If you remember Vlookup used to only be able to take one input. It can now take multiple inputs. That's what's, that's part of the overall dynamic arrays. It doesn't mean it automatically spills. Like if I add a new name down here. This is not smart enough to say, oh, there's something new there. Now move it to 27. So inputs, it can take a range of inputs. That's part of dynamic arrays unless that input is dynamic. So unless it has a pound at the end, because that original list is dynamic, you would have to update the range of inputs. So there's two things to think about with dynamic. Any formula that's dynamic, you can use that as an input. Anything that's not dynamic. There are things that weren't. You just didn't set them up in a dynamic way for various reasons. You're referencing a table, whatever it may be. You can reference a range, but unless there's a way for that range to automatically update, like a table, it's not going to pick up that new row because the formula that the inputs is coming from, the inputs themselves are not dynamic. So I hope that helps answer your question. I think it was LinkedIn user there on that example back there with B22. We're going to keep going and I think you'll see more and more of that. So right as we were talking about, the pound symbol is critical to use with dynamic arrays. Use it all the time because then it references that entire range. So every time that range changes, the formula changes, we go to the next sheet. I want you to understand what are called spill errors. So I have again a simple sequence formula. We're just doing a list here. Change this first number to be 30, or basically any number over 20 should work, but I'm going to change mine to 30. I've got a spill error. The spill error has the pound symbol, the word spill, an exclamation point at the end. It also can be identified by the fact it has a light blue outline around the entire range. So instead of being dark blue, it's light blue. What that means is, look, I can't spill into all the cells you want me to. There's something in the way within Excel. In this case, you see in row 22, there's the word Joe. So take out the word Joe here. Now it's spilled down. So that's the second thing to keep in mind if you work with. Not second, but another thing to keep in mind when you work with dynamic arrays is if you have a formula, if you have a. A file where you need things in certain sheets, you need to make, or not certain sheets, but certain cells, certain areas, you need to make sure you're not putting dynamic arrays in an area where they'll spill into those cells, because they'll just give errors. So this makes it even more important to think through your design. Design should always be important. I think it's the most important thing in financial modeling to get right is your design. But with dynamic arrays, you also need to think about the spilling behavior and make sure you don't give yourself spill errors throughout the file because you didn't design things right. And then later you went to do an update and it didn't work. All right, so look at, let's look at adding here together. This, this this first example, after we talked about errors, just to show some use case examples, and I'm going to show two here. So what I'm going to do is I'm going to delete this and I have form one, form two, form three. I want to get the totals of each of these. Unfortunately, I can't come in here and sum this and expected to be smart enough to be like, well, I want you to sum each individual column. Now what I could come in and do is I could do a D. I can have this column dynamically spill here by doing A plus, because it's saying, hey, I'll take everything in that first one second, one third one. And you could see that work alternatively, if you're familiar with what are called lambda helpers. And we won't get into this beyond just showing this example because you. It takes a little while to learn this, but there's something called by call where I'm entering the entire range here. And what I'm doing is I'm saying, hey, I want you to use the sum formula over this range by column. My call is just this data range. So every time I say my call, it's referencing C, G7 is G G9. So what I've done here is I've said, hey, go ahead and look at this and sum each individual column dynamically. And so now I got that all in one formula there versus, you know, referencing each individual row, I've referenced that entire range. Those are the type of things you can do with dynamic arrays. And, and adding. Now let's look at a simple multiplication example. Sorry, I think I made that a little bigger than I wanted. All right, so what I have here is I have quantity, I have price. So what. What I can do is I can reference those entire ranges and it's going to calculate it all here, right? So I got the 1 times the 10 versus. If we think about how we used to do it, I'd say 95% of us come in you times. Oh, not, not combine you times it. You drag it down, right? Instead of doing that, I could now just reference each range and this formula dynamically spills. These ones are not dynamic, right? If I added a seven here and the way I've built this there, there are some things you could do to solve that. But I could come in here and I would have to manually adjust to add that new one. But this formula spills. And that's what I mean by kind of the, the array formula. If there's any questions, you know, please just Let me know as I go through this. I'm trying to monitor my other screen for questions. So that's an example of multiplication. Now I want to show one more. Let's look at Vlookup. And I'm just going to show a couple of examples here, right? So in most cases, when we talked about Vlookup in the past, we would reference one column. So here I have this bonus rating. I'm looking it up in a table and I drag it down again. As I showed earlier, even if you're not using xlookup, you can still take advantage of the fact that you can now input multiple values here in your lookup value. Really, this should probably say lookup value and then open parentheses with an S values, because you can do either. And now it spills, right? And, and so that's an example. You can use it with almost any of your old formulas, some ASV lookups, many, many formulas, not every single one, but most formulas you can enter those dynamic multiple values that can change the way you work. That can save you a lot of time, especially if you're referencing a table where it automatically ju adjusts or a range that automatically adjusts, because then this formula will automatically adjust as well if it's referencing an object that can adjust. And so even though you know this input value right now is not dynamic, there are ways to, you know, make it so that when you add new records, those records will get updated. For example, let's just say I put this in a table. And so instead of this, I'm going to reference this now if I come down here and add a bonus rating of 2, that automatically updated if I add a bonus rating of 1, so on and so forth, you know, 6, it couldn't find. I could change. I could do some things there, but you get the idea. So now this has become dynamic because of the object, right? Tables, objects automatically adjust their size and it's referencing the table. So I think that gets back to the earlier question from someone who asked a little bit about that. So I'm seeing a few questions. I'm going to take a minute and discuss those. So give me just one minute here. So Joseph said, I think I read somewhere that we won't need to call the lambda function anymore when using simple functions like sum. You're correct, Joseph. That's in beta. Right Now I, I know I called. I think they called it lambda, like eta or something like that, if I remember right, Joseph. And I'm not sure when it will come out. So the things I know of that are in beta. And if anyone else knows, feel free to chime in. Python's in beta, group buys in Beta, Pivot, Bison Beta percent of those three functions and the lambda. I think it's eta where you don't have to reference it anymore. I think that's what they call it. I believe all of those are in beta right now and I haven't seen a release date for them. Joseph, if you want to try using them and you have your own account, you can sign up for the beta channel. No guarantee you'll get all the newest functions because they roll it out at different times, even among the beta channel. But that's what I'd recommend there, if you want to try that. And then I see ETA reduced lambda. Thank you. I knew it was eta. I couldn't remember the full name. Mark. So there you go. ETA reduced lambda. That's what happens when you have an MVP here. Mark knows a lot more about Excel than I do, so. And then one other person asked, will this be uploaded to YouTube? Yes, it's being shared to YouTube right now. It will be available there. You can also come back to Streamyard and watch it as well. All right, so what I want to do is we have about 20 minutes left. I'm going to walk you through how we built, how we build a sensitivity analysis just so you could see how one's built. Because I. What I found is everybody loves to see that. I think it's a common use case in finance. So go with me. I'm not going to go through any specific formulas today we will build a simple, a simple sequence and I'll walk you through that. Really today was more about just helping you get familiar with how dynamic arrays work so you can go out and learn on your own. All right, so let's go ahead. We're. Here's a sensitivity I built previously. This one was looking. Oh, sorry. That just got way larger than I expected. Let me fix that. This, this one here is something I did for Tesla a while back. Kind of looking at their cars and just seeing how sensitive they are to cogs and prices. Because what anyone who's followed Tesla knows the last year has not been good for them. They're dealing with inflation, decreasing prices. Right. Obviously margins aren't going to be very well in that. In that case. Plus they're seeing struggling cells, they're not getting the growth they used to. Combine that all together and obviously not in an ideal situation. So this is one I just kind of played with. So let me Walk you through what the sensitivity is doing, then we'll go to the next sheet and we're going to build the basic formulas. So what I've done here is I have, hey, how many units are sold? And this is using data drop down, so I can adjust this to any number in this range. I'll show that in a minute. I have a base price here. So let's just say the base price is $50,000. And you'll notice with every single one of these the model's gonna update. I have a gross margin that I'm using. So this is my starting my base gross margin anywhere from 15 to 29%. Obviously you know the numbers will adjust accordingly. I have price increments. You'll see right here it's 49,000, 50,000, 51, 52. This is changing at a thousand. Then I have how many increments I want. So in this case I'm looking at 10 different prices, 15 different rows for cog. So I'm saying, hey, start at 42,000, go up by a th 15 times, start at 49,000, go up by a thousand 10 times. And then the what I have here is this base case price is what's the worst case? How much worse do I think it can get? And I have this range here that I use to calculate where I want to start. So I can say, hey, I think the worst case is 80% of this 50. And so it's going to start with the 40 and build from there. And then I did the same with cogs. So that's basically the inputs here. Before we build this, I do want to show one other thing. Anyone who's done data validations, these are all lists. Every single one of these in yellow is a list. But what I want you to notice, and this will be real small. So I'm going to copy it out here so you can just kind of see it. And of course I'm going to get a spill error. So let me just copy that again. That list right here is using a pound symbol. So it is a dynamic list. So what I did to create all my lists and this gets back to sequence, which someone asked about earlier. Eric said, yeah, but the Cyber Truck is epic show of hands. Put it in the chat. I'm not a huge fan of the Cyber Truck. What's epic about it, Eric? I think it's a big ugly still truck. I it doesn't do it for me, but maybe it does for you. I can't afford it anyway. But I'm not A huge cyber truck fan, but if you are, let me know in the chat. So what I've done here, and this is a very practical use case, if anyone used to have to create dynamic lists, you'd see offset and some other things done prior to dynamic arrays. In this case, every single one of these is a sequence. Now, if I was building this as something I was going to use for a boss, every one of these would be inputs. I wouldn't do this all in the formula. I am hard coding. I'm violating rule number one of modeling. So please go ahead and share it on LinkedIn. Tell everybody I'm a bad modeler, but that's what I've done here. So I have this list that says, hey, give me 22 rows, one column, start with 340,000 units and go up by 5,000 units. And each of those validations, there's eight of them. 1, 2, 3, 4, 5, 6, 7, 8 are the same thing. They're all just sequences. This one says, hey, I want 20, start with 500, increment it by 500. So that's the price increments. If I wanted my price increments to be a thousand, I could do a thousand. And I could start with, you know, I could start with a different number if I wanted. That's a hundred, not a thousand. But, you know, if I wanted a thousand, I could do that. And so I can come in and adjust any of these increments and dynamically, all of my dropdowns are automatically going to adjust. So every single one of these is just referencing a different list. This one's G4 pound, right? If I look at this one, I think it's I4 pound, right? If I look at I4, that's this list. So that's the first, the first formula I've used here. And for simplicity, what I'm going to do is I'm going to copy and you can do that with me. Because I don't want to take the time to focus on just entering all these lists here is I'm going to start by copying this. So then what we're going to do is we're just going to create this sensitivity table here. So what I want you to see is the first thing we're going to create is this price, right? So what we need is we need to reference the number of rows we want, which in this case is one. Then we're going to reference the number of columns we want, which is our price increment. How many times do we want it to increment. Then we want our starting value, which is our worst case price. And then last, we want our increment. How much should it go up by? Every. Every time. So we're going to write that formula. So if we come here, we're going to put it right here. We're going to say equals sequence. We want one row, our columns is our price increment right here, total price increments. So we're going to select that our starting value is our worst case price. Then what we need is how many, you know, what do we want it to increase by each time? What's our step? That's our price increments. Close that. That's now dynamic, right? If I look back here, I can see that's the same. We're going to ignore this part for right now. I'm just. I'll leave that there for now. But the next thing we need to do is we need the dynamic list for our cogs, right? So what I'm going to do for cogs is I want everybody to come in here and just type sequence. It's the same idea. In this case, how many rows do I want? Because I want this to spill down rows. That's going to be my cogs increments. So it's going to spill down 15 rows. I don't need. I only need one column. So I'm going to go ahead and enter one here. I could even skip it and by default it would assign one. If I skip. Skip it. Oops, sorry about that. You'll notice I skipped it there and by default it assumed everything was one. So you can do that. But we'll go ahead and enter a one because I think it's easier when you're explaining to see each part of the formula, even though some of them are optional. So now the third part, where do I start? Well, I start here and this is with my base case cogs. So we're going to start there, C12. And the last thing I want to add is I want my increments in this case, I want my cogs increments. So I'm going to select that. So now it goes from 42 to 56, right? Now what's missing is the formula in here, right? So if we think about this, if I want to show the margin, if I assume there's one unit, right? If I do 40, minus 42 for that one unit, it's minus $2,000, right? You can see that. Let me make it a little bit bigger. You can see the 2000 there. Now, if I wanted to figure out what is it for 365,000 units, I would just times it by that. But the problem is that lives in one cell. So that's not what we want. What we want to do is we want this all to be dynamic. So this is the step where we bring it all together. So go ahead and go equals and select C17 pound minus this right here, minus your COGS pound. Now if I do that, I got my table, but it's only for one unit, right? I need to know what it is for all the units. So now all I need to do is I need to times it by this. So what we could do is, if we're worried about it, we'll go ahead and do this. So it subtracts them all times is it for each unit. And now I have an adjustable dynamic array. I've just created that first sensitivity analysis. And all this is right here is just conditional formatting. Right. And so it's just using a color scale. I could change it to whatever color scale I want. I just need to make sure the color scale is as big as the range could be. So in this case, my range could go up to 25 price increments, which means it could go over 25 columns. It could go down 25 columns. So I would want to make sure my conditional formatting goes over 25, down 25. So no matter how big I make this, it adjusts. I would also want to make sure my columns are big enough. So I'd want to resize all these columns. But you know, the reality is, if you get into that many rows, you're dealing with billions of dollars. Not very practical for somebody to see all of it. Right? You know, if you're dealing with smaller numbers, it's easier, but that's something to keep in mind. But that is an example. Then down here I said, well, I want to figure out what the percentages are. So all I've done here is, I've said, I've done two things. First, I referenced this up here. There's no reason I need to create a separate formula here. I said, just give me the price from above. Give me the cogs from above. So I've referenced C17 pound, B18 pound. And then in here I've said, oh, give me C18 pound, which is my gross profit divided by C45, which is my price times my units sold. So take that gross profit and tell me what the percentage is. And so down here I have another table that's automatically going to adjust anytime I change this. That second table also adjusts and someone asked will I share a recording? It will be available on YouTube. You can also come back to Streamyard where you registered and watch it on demand. It will be available there for people who missed it. That covers kind of the sensitivity analysis example. I'm going to go back to the PowerPoint deck and just share one or two more things. We're going to wrap up and then I'll leave time for a couple of questions. So let me, let me get this up here. Everybody should be seeing the deck. Let me find the right slide. One moment here. Yeah. So you know, kind of takeaway is my view is dynamic arrays, I think most people would agree have changed the way we use all functions and formulas in Excel for the better. The reality is we should be better at Excel if we're using dynamic arrays. Jared Correct. You don't need to anchor cells in dynamic arrays. I still find myself doing it sometimes, but it's not necessarily needed. All right, so here's the key takeaways. You can work in more than one cell at a time. They have the way, they have the potential. And we're and what I mean by potential is we have to take advantage of them to change the way we work in Excel, reducing the number of formulas and the complexity. Remember, they're only available in 365 and 2021. Building fully dynamic financial models with dynamic arrays is still difficult. Corkscrew is an area where without using lambdas, I haven't really seen people solve with just dynamic arrays. So a hundred percent three statement model is still difficult to do. But you definitely should be using dynamic arrays in my view. Here's a couple resources. First, the only book I've seen that's 100% dedicated dynamic arrays actually own it is by Abbott Katz. It's called up, up and Array. Very creative name. There's a lot of other great resources out there. I know Mark's on the call. I know he has stuff in his on his website around dynamic arrays. So feel free to check that out. I've also listed some Microsoft resources so there's four or sorry three different articles here about dynamic arrays spilled Array Behavior Preview, xlookup and they have others but there's there's a few Reese's resources for you. There's lots of YouTube channels and I will be sharing my course that I've just developed the modules that I had that I are going to be used in the new Wharton FP and a certificate I will be releasing those in the coming weeks as my own course. And then I will over time release an extended version of that because I was limited in time on how much I could do for the Wharton. And there are some concepts I'd like to add. So that will be coming out here shortly. So keep your eyes open for that. And then I just want to share two things that I'd love for any of you that are interested and if you're not interested, that's fine too. But I have a planning and analytics community. If you're a planning professional, there's a code there to sign up for today. It's called Plan Buddies. You can find it at plan plan-beddies.com we have a free mentoring program. A couple of my courses are there. The Modeling, Design and my Excel Tables course will be there soon. You can connect and get answers to your FPA challenges. We have about 100 people in the community today. We have monthly events. We do some masterminds, Excel Hour, other things like that. And if you join using the code, the FPNA guy, you'll get a 25% discount on that. So yeah, Eric just shared and this was I mentioned earlier. Thank you, Eric. I'm just going to show this real quick. Craig Hat maker has some corkscrew Lambdas. He calls them 5G. And so you don't even have to build the Lambda, you can use his Lambdas to help you build those models. Thank you for bringing that back up, Eric. I'd mentioned that at beginning, but good to highlight that. So anyone who's interested in Plan Buddies, there's the QR code. We'd love to have you join. And then the last thing I wanted to share is I have a best practice FPA course coming up in June. We just finished the last session. My partner and I, Ron, we've trained over a hundred people. We have. Not counting all the corporate people we've trained, you can't all that. It's over thousands. But just this virtual FPA course and we have four sessions. It's ten hours of instruction. We're offering it in June, the middle of June. If you sign up this week and use the code dynamic arrays, you'll get a hundred dollars off. We do limit seats on that. We generally keep it to no more than 25 to 30 students is kind of where we cap that. So if any of you are interested in any of those things, take advantage of them. I will get the deck out in the next day or two. To everybody who registered on Streamyard for this if you watched it on YouTube or LinkedIn and you want the deck, feel free to reach out to me and I'll get that to you. But I don't have a registration list if you watch this on YouTube or LinkedIn, so I hope you found that valuable today. I'll pause here. We have, oh, let's see, one minute left. I thought we had a little bit more than that, but any questions? And if there's not, we'll just let everybody go. But I can hang on for a minute or two if anyone has a question. But that completes the webinar here, so I'll stop sharing. Thanks, Raj. I'm glad you enjoyed the session. Thanks, Rash. See, there's some notes there. All right. Are you planning to do chart G? Bet you mean chat GPT. Is that what that's supposed to be? I'm not sure I understand that note. When you go to pay, you should be able to submit a code. Melissa, send me a note, show me what you're seeing, and I'll. I can walk you through that. And thank you, everybody. Really appreciate it. Like I said, if you have any questions, let me know. We'll close this just here in a minute or so. So, yeah, for advanced financial planning. I don't know if I'll. I don't know if I'll do something on the advanced financial planning. We'll see what comes next. I. I don't have my schedule figured out, but I am going to go here. I do got a call coming. But thank you everybody for joining. I really appreciate you carving out some time today. We'll get the deck out there and feel free to work to reach out. Thank you, everyone.