Hello and welcome to another D Brown Consulting Excel and Power BI Webinar Series. This is always on the third Thursday of every month from 9 a.m. to 10 a.m. Central African Time and it's sponsored by D Brown Consulting. So today we're going to talk about XLOOKUP. XLOOKUP and how it retires VLOOKUP. And I'm also going to give you a methodology for extracting any data from any table in Excel. Very efficiently, very cool methodology that uses dynamic arrays, uses the spill, you know the spill trick for dynamic arrays. And basically the modern Excel, how you should use Excel in a modern way. So this is going to be fun. We're welcome and let's push on. So also please join us in our Power BI user groups, Power BI user groups live lab, which we have every third Saturday of the month from 9 a.m. to 12 noon. That's the third Saturday of every month. You check out www.pbiusergroup.com. So check that out and join us. We have every third Saturday of the month. Talk about Power BI for three good hours. Bring your laptops with Power BI desktop installed. If you have Excel as well, you can bring it. You could use Power BI on Excel, which is really Power Pivot, Power Query, DAX and M. You can also move along and follow along with us. So join us. Any one of those thoughts are Saturdays. We're lucky to be sponsored by Lagos Business School. So that's our latest sponsors. So you could join us there and we'll have fun. So I'm David. I'm David Brown. I'm the managing partner of D Brown Consulting, also an international consultant to the World Bank. I'm a Microsoft MVP on data analytics, and I have over 20 years working with Excel, data, reporting, accounting, finance, and I'm also a master trainer. So I do a lot of training. We also check out officetraininghub.com. If you check that out, you see some of our online courses there as well. Also a chartered accountant and have some other certifications. So that's about me, David Brown. You can follow me on Twitter at D Brown Analyst. That's my Twitter handle. And I'll see you and continue the conversation online. So what are we doing today? We're talking about XLOOKUP. So let's just jump straight into the demo. So XLOOKUP. So I'll just kind of remind you of what VLOOKUP used to be. So VLOOKUP. We have an exact match. We're trying to get an exact match. So here I want DANGOTE's eBit. So this is a table, a typical table, and I want to extract DANGOTE here, the eBit, which is this value. So in our typical VLOOKUP, we just do equals to VLOOKUP with tab. And VLOOKUP says, hey, what are you looking for? First, the lookup value. That's DANGOTE. I put a comma. Then table. The table we are looking up. So the entire table. Yeah, looking up the entire table. Put another comma. Then what column are you looking up? Well, the column I'm looking up is the column that has eBit. So that's where is eBit. eBit is here. So that's one, two, three, four. So usually we type four. That's bad practice, I know. But anyway, let's move on. And then is it an approximate match? You see the default of VLOOKUP is approximate match. Well, I really need an exact match. So that's one of the differences you're going to see. And then I close my bracket and I enter and I get to my 108. So how do you do with index? Index, if you type index, index and I tab, you see index has two versions. Index has array, row number, column number. Then you have reference, row number, column number, area number. We're using the first version of array of index. So the first thing the index says is, hey, show me the entire table you want to work with. So this is the entire table. Then the next thing it asks for is what is the row number of what you're looking for? Row number. How do I get that? Well, if you see done, go te. Let's count. You have one, two, three, four, five, six, seven, eight. Eight, eight. So that's eight. So obviously typing eight and counting doesn't really make sense, right? That's where a match comes in. Use a match to do that. But let's, let's, let's move along. So, so dango is on the eighth row. What about the columns? I put a comma. It says, what is the column number? So that says EBIT, which column is EBIT? One, two, three, four. So my table has five columns and EBIT is number four. So I put four, I close my bracket and I enter. And yep, I got it with index. What about XLOOKUP? So XLOOKUP. Let's have a look at XLOOKUP. So XLOOKUP, look at the syntax here. The syntax is lookupValue, lookupArray, returnArray, matchMode, searchMode. So that's XLOOKUP. LookupValue, lookupArray, returnArray, matchMode, searchMode. Let me give you a shortcut to understand how to use XLOOKUP. XLOOKUP is what are you looking for? Where exactly are you looking for it? So what are you looking for is lookupValue. Where are you looking for it is lookupArray. So which was the column that you're looking for this thing from, right? Then returnArray is okay. So which column contains what you want to return, right? And then matchMode is okay. Are you looking for an exact match or not or what exactly are you looking for? And searchMode, that one is special. We're going to see that later, right? So lookupValue, this dangote, right? Put a comma. Where am I looking for it? Well, I'm looking for it in this first column here. So I can highlight the entire column. Let's say this, yeah. So that's where I'm looking for it, right? Now, of course, if it was XLOOKUP, it would be horizontal. And XLOOKUP works whether H or vertical or horizontal. It works fine. So it replaces XLOOKUP as well. So this is the lookupArray, yeah? Then if I put a comma, what's the returnArray? What are you trying to return? I'm trying to return something in EBIT, the EBIT. So I'm going to highlight the EBIT column. And that's it. And then if I put a comma, you see match type. Exact match is the default match. So unlike VLOOKUP, exact match wasn't the default type. In XLOOKUP, exact match is the default type. If I put a comma, these are the world cards. And not world cards, really. These are the search. This is the search criteria, which is, here you have search first, last. We'll see that very soon. So really, I don't need, since the default is exact match, I really don't need to specify anything. I can just stop right there and enter. And I get my value. So that's a simple way of using XLOOKUP. Let's start the exact match way. Let's look at approximate match. Let's say approximate match. How would you use VLOOKUP to get approximate match? So let's look at that now. So approximate match, you have sales amount over 17,500. And I have a sales table here. So this is my sales table. And if this is my sales table, we have, we need to, anybody that sells between 0 and 10,000 has a rating of 0%. Well, you sold between 0 and 10,000. We should try harder. No bonus, no vacation for you. You sell between 10,000 and 15,000. You get a 1% commission. And you can go on vacation in Abuja. You have 15,000 and 25,000. One and a half percent and oil. So what is 17,500? 15,500 is somewhere in here. So 75 is in between this somehow. Then you get a 1.5. So if you want to do a range lookup in, whether VLOOKUP or XLOOKUP, if you want to do a range lookup, you need to modify your table a little bit. You need to put the lower end of your range here. So I'm going to put the lower end of my range, thousand and one and 15,000 and one. And the lower end. So I'm just typing the lower end of my range here. So this is 25,000 and one. 40,000 and one. And then 60,000 and one. So those are the lower ends of my range. Then I can do my VLOOKUP. So the normal VLOOKUP, let's see VLOOKUP tab. So lookup value. Again, this is the amount of lookup value. Then we have a comma. Then your table array. Table array, what's the table array? Well, this is the table I'm looking up from, this table. Then I put a comma. Then the next thing is column index num. What index was the column number you're trying to pull out from? So this first, second, third. So it's the third column. Three. Then if you look at the comma, the next thing is range lookup. And the range lookup has two options. Approximate match is the default. So since approximate match is the defaults, and that's what I need an approximate match, I don't need to put anything. I just close my bracket, enter, and I get 1.5. So this is the old way. This is the lookup, the old way of doing it. So the new way, if I do equals to x lookup, x lookup, x lookup again, lookup value, comma. What is the lookup array? Now the lookup value, this 17.5, we're looking for it in this single array. This array, right? Comma, what is the return array? Well, this is what I want to return. So I just highlight. Now if I put a comma, you'll see that exact match is the default. But right now I don't want an exact match. What I want is the next smaller item, or is it the next larger item, or is it just a wild card? I'm looking for a wild card character, right? So you see, I have more options, not just exact match and approximate match. The approximate match we're seeing, are we moving from smaller to higher? So is it exact match next smaller item? So obviously 17.5 is not here. The next smallest item to 17.5 is 15,001. Which is what VLOOKUP does, but this is such a clear definition, right? So that's really what I want. Do I have to specify that? Now I don't need to specify search mode, or I can leave that out, right? So I close my bracket and that is how I use x lookup. Now one big difference between x lookup and doing approximate match is that VLOOKUP does something called binary matching. Binary matching, which means it needs to go one at a time and this list must be sorted from ascending to descending, or sorted ascending, right? So it needs to be sorted. So if I mess up this list sorting, x lookup will break. So let's test that. I'm going to sort by this column. Let's see data up here, A to Z. Guess what? Look at x lookup. Now it's getting the wrong percentage, but that's VLOOKUP, sorry. VLOOKUP is getting the wrong percentage, but x lookup hasn't budged, but x lookup is getting the correct percentage. Whether your list is sorted or not, x lookup will find the correct approximate match. That's cool. That's just super, right? So that's x lookup with approximate match, far better than VLOOKUP, for sure. Let's look at last match. So what if I want to get a last match? So I want to find what do I mean by last match? Well, if you look at this, I have some months, I have some products, and then I have the sales for those products of that month. And typically when you grow tables, it's usually the very last data, maybe that has what you're looking for. So here I have data and you can see, okay, this June is the last data and I'm looking for actually I want the last sales for Milo. So I have 554. 554 is the last sales for Milo. What was the first sales? First sales is 268. When it comes to lookup, you usually just get only the first item. So I may use the old way to get the first sale. The first sale for Milo is 268. So I use my typical VLOOKUP. We say VLOOKUP. What is our lookup value? We're looking for Milo, comma. Where are we looking for it? Well, it's this table here, right? So I have to highlight. I can't highlight from here. I have to highlight from here. Yeah, because the first column must contain what you're looking for for VLOOKUP to work. So if I highlight this table all the way up there, yeah, and then I do a comma. And what I want to return is the second column and comma zero because I need an exact match, right? So I close my bracket. Now zero or false, right? Zero or false is the same thing. Zero and false actually work. Same thing, right? Zero and false. Right. So when I enter, it finds 268. And what I need to do since I'm going to drag this down to the right, I'm going to drag it down. I need to lock.  So a small trick is to click on this to highlight. I need four to lock. I'm locking that up. Yeah, because I need to drag all the way down. Right. So last sale. What I want, I don't want the first sale. I want the last sale of Milo. You can't really do that with VLOOKUP. You just can't do that. So I need to pick this up. I need to pick this guy up. How do I pick that up? Really can't work. So let's use XLOOKUP. So XLOOKUP first sale. Let's find the first sale for XLOOKUP. So the first sale. Bring this up. So the first sale will be equal to XLOOKUP. Same as VLOOKUP. I'm looking for this guy, comma. Where am I looking for it? The lookup array. That means I'm going to look for it at this list from the top to bottom. Should lock.  So I'm pressing F4 to lock it up. Top to bottom. Then I put a comma. Then the next thing is the return array. Return array that is from here all the way to the end. I lock that up as well. So I just pressed an F4 key. So I'm back up here. So this is the formula. This is my formula. Let's move this out of the way. So you can have a look. So this is the formula. I lock close my bracket. So this is XLOOKUP. Looking at this in a lookup array in the return array. When we enter, we get the same 268. Come to the end. We double click. And there we have it all the way down. XLOOKUP can do also. It can do a last lookup. Basically looking at the last match. So lookup again. XLOOKUP exactly the same thing. Just take this down a bit. So it's exactly the same thing. It's looking at that. Where is it looking for it? It's looking for it in this product list from top to bottom. Right. I can lock it as well. I'm going to press my F4. Put a comma. Then where is it looking? That is your lookup array. My return array is all the way here. From this all the way down to the end. That's my return array. That's what I want to return. F4 to lock again. If you look at the formula again, that's it. Then I close my bracket. So here we're looking up this. We're looking it up in this list. Which has all the values. And then I want to return this list or this array. Something from there. So if I enter. You get what it is. Which is first match, isn't it? But that's not what we want. We don't want first match. If you put a comma. You see the exact match. Are we looking for an exact match? Or we're trying to match the next smaller item or the next larger item. Well, we're looking for an exact match. So it's exact match. In fact, I can even ignore it. Since it's the default, I can ignore it. You can just put the next comma. Now this is where the magic is. This search first to last. First to last is usually the way we do it. I mean you're searching from the top to bottom, right? What about if I do last to first? If I do last to first and I close my bracket. What I've just told VLOOKUP, not VLOOKUP. XLOOKUP to do is find the last one. 554. If you look down here. You'll see that Milo is 554. Maggie is 666. And then Nescafe is 355. So if I come down here. And drag this down. So if I come here and drag this down, double click. You see it finds the last sale. So this is the first sale. This is the last sale. And then you can do kind of a growth formula and stuff like that. You can do that with VLOOKUP. That's not possible. Let me show you an even more modern way to do what I just did there. So I'm going to try and replicate this all just using three formulas. So regardless of how long this list. Let's say I just need a list, a product list. Now in your new Excel, your new Excel, which is an Office 365. You don't have Office 365. And you need to have updated the latest Office 365. You will have what we call dynamic arrays, which is really the new Excel calculation engine. So if I say equals to unique, if you don't have this function, that means you don't have this unique. You don't have dynamic arrays on your system. You haven't set it up. It doesn't update it. So unique. What does unique do? Unique is so cool. Unique is a new dynamic array. If I come and highlight all my products. And what I want is a unique list from those products. So look at it. I'm just highlighting all the products and closing my bracket. And then I just enter and guess what happens? It just spills a unique list for me. Just spills it. Excellent. Now I mean you could even go in. So this is formula in a cell. If I come here and type maybe something to obstruct, guess what happens? It says, hey, I can't spill my answer. Yeah, yeah, disturbing me. And I can't delete any one of these because really it's just a spill. Look, I'm trying to delete it. It can't go. The only thing I can do is delete the first one and then everything goes, right? I'm going to undo that. And then I could actually come into the beginning of this formula and say, hey, I'm going to sort you. I need this list sorted. So I'm going to do sort. I say sort, which is another dynamic array function. And I enter. And that's it. Sort. But the thing about these arrays is that every function is an array function. Every formula now in Excel is an array formula. So let me explain that with X lookup. So if I do an X lookup, right? And in the X lookup, I say, hey, what's the lookup value? This is the lookup value. But really when I don't just want to look this up, I want to look everything up and I want you to spill. So if you see lookup value and I want to look up all the values and allow it to spill, what do you do when you click on this? Because you know this was a dynamic arrays. You put a hash key at the end. Just a hash. Just put hash. Just look at that. So put in hash. See what it did? It just spilled, right? Kind of highlighted everything, knowing fully well that this is a dynamic array function or formula in there. So if I put a comma and the lookup array is just like the same thing. Lookup array. I'm going to highlight this guys. I like all the way down, right? You can lock it. Press F4. So that's my lookup array. Yeah. Then I put a comma and then my return array. Return array. Come in here. Lock this. Right? F4. Right? So that's F4. I lock it. Now if you look at the formula, everything, you're looking this up. In fact, this up I'm going to lock. I just want to lock the column so that I can take it to the right. I'm looking the column and I'm looking this up, which is everything here in this list of products and I'm returning the sales. So if I come to the end and I close my bracket, when I enter it's going to spill. It spills all the answers. Everything in one, right? It spills everything. So water. I have water there. You can see water is 534. Water is 534. So I can copy this formula, which is in a single cell and paste it here. It's going to spill the same thing. But the difference is if I come to the middle of the formula here, I delete this. I put a comma. Since, hey, do you want an exact match? I want the next smaller or the next larger item. Well, I want an exact match. So I'm just going to ignore that. That's the default. Then I don't want first to last. I want last to first. So I want you to look up last to first. So I'm going to put a tab. That's a minus one. I close my bracket and I enter. And now it's looking at the latest sales. This is the last sales. This is the first sale, last sale. Latest sales. So that's exactly like this one I had up here. But only difference is this is now sorted. So if I take this sort, I could copy. If I could copy this formula here, this sort formula, I could come in here and do a sort as well. If I click on this and delete this and I come here and say equals to sort the unique list. So sort the unique list of my products. I highlight all my products, close my bracket and enter. Look at that. So sort the unique list. And now it's sorted. Now you can see that exactly the same thing. The only difference is I didn't make this dynamic. So I kind of had to drag it down. But this one, the whole thing is written in three cells. If I delete these three cells, everything goes. If I delete that, everything goes. So this is modern. This is really modern. Dynamic arrays using dynamic formulas and functions X lookup all working wonderfully together. Right. So next, what are we going to do? The next thing and the last thing we're going to do is build a dynamic lookup table. We're going to build a dynamic lookup table, pull out a whole mini table from a big table from a big table of data. We're going to pull out a subset of this table automatically using X lookup. I'm going to use X lookup on some interesting tricks and techniques for it to automatically pull out a smaller table from a larger table. So we'll see that coming next. Great. So we're now at the last stage of our webinar where we talked about various aspects of how you use X lookup. But the last stage is we're going to build a dynamic lookup table. What I mean by dynamic lookup table is like a mini table looking up a major table. So usually when we have a big table of data, our reports are really a small version of that big table. So I want to give you a very cool dynamic way of building that out using X lookup and dynamic arrays. But before we do that, let me recap what we've gone through with this X lookup function, which is the new V lookup replacement. In fact, it's the new V lookup, H lookup, index and match replacement. Right. There's also X match, by the way, which kind of does something that X lookup does. So X lookup already has X match in it, but I'll explain what I mean. Great. So the first thing we did is the simple exact match. Right. So we checked and saw, okay, what is the exact match? What do we mean by exact match? So that's what index and V lookup does, right? So index will look at the table and say, hey, I need to get dangote's ebit, right? To where is dangote's ebit in this table? If I look at the table, you can see dangote down here. And where's the ebit? Ebit is right here. So there's 108.9, and that's how we got this. Index says, hey, give me the entire table and tell me the exact row to go to and the exact column to go to. That's how index works. Exact table, which row? One, two, three, four, five, six, seven. Well, let's say seven. No, one, two, three, four, five, six, seven, eight. You can count, okay, eight. And then four is the column. So one, two, three, four. So that's how index works. Nice, index is pretty good. V lookup, you lookup value is dangote. The lookup array or table lookup or table index looks at the table after you lookup value and say, okay, which table am I looking for this thing from? And the table is the entire table. And so in this table, which column should I go to? And that's four. So one, two, three, four, column four. And then false is you have to put false there because the default, the default match type is that it doesn't look for an exact match. It looks for an approximate match, which is one of the flaws of the lookup because most people want an exact match. X lookup, very similar. The syntax is your lookup value. What are we looking at? Up, dangote. Where are we looking for it? Well, you give it an array, the lookup array. So this is the where I should lookup dangote from just this column. Then you now give it a return array. So the return array is what it should return. The column where the data contains, the column that contains the data that you want to return. So that's how X lookup works. Yep, just looks at your lookup value, lookup array, return array, very simple. There's also match mode and search mode. So that's a simple use of X lookup. So approximate match, here we're saying, okay, this guy got sales of $17,005. What commission should he get? If you look at this table, look at range in any particular order, we saw that $17,005 is between $15,000 and $25,000. So it's due to get 1.5%. X lookup gets that correct, but Vlookup doesn't. Because Vlookup requires that this table be sorted in ascending order, right? So if I click here and I come to data and I sort in ascending order A to Z, then Vlookup works. But X lookup doesn't care, it will always get you the right value. So that's X lookup far better than Vlookup when it comes to approximate match. And how does X lookup do that magic? Well, after you do your normal X lookup that I just showed you, you now put a comma and then it says, okay, exact match, or you want exact match or next smaller item, exact match or next larger item or wild card match. Well, we still want an exact match, so I'll leave the default there. But then, yeah, so we want an exact match, but actually it's the next smaller item we want. Yeah, so to say it's next smaller item. We don't need the last argument yet. So it's exact match and the next smaller item you close your bracket and enter, and that's how you get it, right? Super. So the next thing we also looked at is last match. Last match, what do we mean by last match? So sometimes most people have data, you have data on products, and your data keeps growing. So the last data you have is really the latest data. This is the latest sales data, for example. This is the oldest sales data and the latest sales data. So if you want to find out how you've grown between January to June, well it's good to get the data for January and then get the data for June and see your growth rate, right? So in VLOOKUP, you can only get the first sale. You can't get the last sale. VLOOKUP always gets the first item. XLOOKUP can get the first item as well as the last item. So what's the magic that it does to get the last item? Well, after your normal XLOOKUP, you put a comma. Yes, you require an exact match. Then another comma and this is now the search mode, right? So you're basically saying, hey, am I searching for the first to last? Am I searching for the last to first, right? First to last is what VLOOKUP does by default. Last to first is the looking at the last item. So you're looking at the last occurrence of that item or that product. Now binary search does the same thing, sort ascending, sort descending, but this is how VLOOKUP works. But we don't need that as in what we need and the way match works, but what we need is that search from last to first, right? Tab, I get minus one there and I close it and that's the code and that's what works, right? So we also looked at, after looking at the last match, we also had a look at a really cool way of doing this, which is using dynamic arrays. So using dynamic arrays, this entire thing we just did, we can do it by using dynamic arrays. And in dynamic arrays, you could just say, hey, let me just delete that. If I delete this, you know that it's just three formulas we wrote here and everything spilled. How is that possible? Well, look at the formula. This formula is a new formula, sorting a unique list of our products. Sorting a unique list of our products. It's a new formula, a new function for only people with dynamic arrays. If you have the latest version of Office 365, you should have these functions. We got to a release recently. Xlookup, well, most people will not have Xlookup, but it's also available. It's going to be available very soon to everybody. So once I get a unique list, if I type something here, you see it can spill as an error. It's trying to spill its answer. Then what about here? So here we're doing the same Xlookup, but we now have this magical hash key. This hash key tells us that, hey, this cell that contains our lookup value contains a dynamic array. So instead, if you look at this, if I delete this hash, you see all it's looking at is just Maggie. But if I want to look at all at the same time so it can spill, I just need to put the hash. But if I leave it like this and enter, it won't spill. If I want it to spill, I come here and put a hash. Of course, I only put a hash if this already had a dynamic array. So I now put my hash key there and then I enter and it spills. Same thing here. This is the last cell using that minus one as our search mode. So that is Xlookup. Absolutely wonderful. So we're going to use the more advanced version of Xlookup. Another advanced version of Xlookup where we have a dynamic lookup. We're trying to build a mini table from a major table. Let's see that next. So a dynamic table. Now I'm going to show you a methodology for how you build this table. So we're going to build out a mini table. And this mini table is going to have dynamic headings. So we can change the headings to anything we want and the tables gets the information for us. We could change maybe the key value. Let's even put something here. Let's say we want a company name. We want the name of the company we're analyzing. We have company names here. We pick them dynamically as well. And then we put headings here and the headings can change at any time and our data should update. So we're going to use Xlookup to do it. Just Xlookup. And let's see how that works. So let's have a look at our data. So this is our data. This is our data with all the headings. This is comparable data on food and confectionary companies in emerging markets. We've got that from sources Damodaran. Damodaran's website. Google Damodaran. You see most people that work as financial modelers or valuation experts, you would know Damodaran. So if you check the headings, we have all these metrics, all these metrics for companies, total equity, revenue, EBIT, EBITDA, to beta, all these data for various companies. How many companies do we have here? Let's see. We have about 260 companies. So what we intend to do is pull a mini table out of this major table. And how do we do that? Well, to do that, we're going to use probably short names. Any name we pick from a list in this short name, and any column we pick in our table, we'll just pull out the data. How can we accomplish that? So if I come to the company, I want to have a list of companies. That's the first thing. So to get a list of companies, I'm going to use data validation. But I want to do it efficiently. I want to do it in a way that this table can grow and everything works perfectly. So what we could do is we come down here and we get a company list, a list of all the company names. And we put it in a single formula. How do we do that? Well, there is in the new Excel calculation engine, we can do equals to unique. There's a new function called unique. And what does unique want? It just wants an array. We can ignore this other two for now by column and of course once. Let's leave that. We just want an array. What's the array we want? It will be the list of companies in our data. But before we pull up the list of companies, if you really want to work efficiently, what I advise is this. You need to name your data. The three very important names you need to give to your data. The very first name is the headings. So the headings of your data give it a name. The key column, the column that contains your unique identifier that you want to use to pull out information, the name, we're going to call this one probably K, right? So in fact, I can call that now. I'm going to pick from the very first name here, short name, the first name on the list, highlight, control shift down arrow key. I come to my name box at the top left here and I call that K. I just call it K. K for key column, right? K. You could call it K underscore something. Then I highlight the headings from serial number, control shift all the way to the right and I'm going to call this headings H, right? Then I enter. Then the entire table, that's the entire table starting from the very first transaction here. I highlight the whole thing, control shift right and control shift down arrow key. There's the whole table. I'm going to call it D and enter. So do this first, then you will really maximize the use of X lookup because now you have references to the entire table. D, you have H and you have K, right? So every single table you have mentioned. So you could say, for example, this is D underscore metric, H underscore metric and K underscore metric and then another table can be D underscore sales, H underscore sales and K underscore sales. So you get what I mean. I'm just using simple DKH here, DHK, right? Great. So once defined, we don't really need this table anymore. We're going back to the dynamic lookup and now you'll see how I can use that DKH definitions of our table to fully build this wonderful dynamic array table. Now we have companies. I want a list of companies. See how easy it's going to be. Company lists, I come here and say, I need a unique list of companies, unique K. All I need is a unique K. What do I mean by unique K? Well, K was the column that contained our key identifier, which is companies, right? So if I say unique K, it spills a unique list of companies. This is the spill to spilled out all the company names. If I highlight this and you check down here, you see count is 260, all the 260 companies, all listed here. Let's go a step further just to make life easier for us to pick a company. Let's say we're going to sort it. So we're going to put a sort. So sort is another array function. When I sort an enter, it's a sort of the entire thing. Remember, this is spill. So everything is written in a single cell. If I type in here and enter, you see it can spill. It's a new error type. Yeah. So if I delete this, it spills. While we're at it, let's also create a unique list of headings, right? A unique list of headings. So let's say unique, unique headings. Remember, we called headings H. We named our headings H in the table. So if I say unique H, you see oops, it's spilling to the right. Obviously, because our headings are to the right, right? But that's not what I want. I don't want this. All I want is for it to spill down. So I need to do something else. I need to say transpose another function. Transpose my spill, right? Transpose my unique list. And I just enter and it transposes it all the way down. Let's even go step further and let's sort it as well so that we can have a nicely sorted list. So let's say sort. So I sort my transpose of my unique headings and I close my bracket at the end and enter. Now it's sorted. So all my headings are now listed and sorted fully. And of course, if I update my table till update here, now that I have my company list and headings, I can now build my dynamic array. So that is how you create sort to just create a dynamic list using the unique function and the sort function. Super. Now to implement my table, I can highlight these four cells come to data. Now I'm going to put a data validation says under data tools. So the data validation is going to be a list validation and what list should I use here? Obviously in the old excel I would have to do this. I'll come here highlight like this. That's old school old school. Don't do that. All I need to do is select the cell, the single cell where I knew I had a dynamic array. So I had a dynamic array there and I just put a hash. See this hash. This is the magic hash. Once you click on the cell that you know you typed a dynamic array into a dynamic array formula. Once you put the hash, what you're telling it is, hey, spill. I want you to use the spill. So click OK. And what happens if you come here? Now I have a data validation that has the entire list. Let's pick some companies. So let's pick various companies. Let's see. Let's go down pick some more companies. Pick this pick company. Yep. Another company. So I'm just picking companies at random. Let's pick the last company. Hmm. Let's see. Sam Young. Interesting companies. Let's see who else. I don't know any of these companies. So that's it. So now I need to populate this with information, right? So what I want to do is I'm going to also do a data validation at the top here. So data validation we have a data. So we have data. I will come to my data validation. Then I'm also needing a list, but this time my list, obviously the source of my list is what is going to be my headings and the source of my list is here. So I click on the first cell, which I know has my dynamic array and I put my hash key. So put the hash key and I say OK. And when I say OK and I come back down, you'll see that now we have data validations. Containing a list. So I'll put a blue box symbol here on the blue box symbol. And then I want, maybe I'll see beta. Maybe not beta. Let's see what do we want? We want eBit. I want to see what else do I want to see? Let's say, like, Bidda is always good. Let's see which metric? EV to a Bidda. That's nice. I say EV to sales, right? Then I want to see what else grow down FF, free cash flow to the firm, free cash flow to equity. And let's see free cash flow to the firm. I need more interesting data. So you can see how easy it is to just pick things. Let's say they return on equity, right? Return on equity, that's good. Great. So now that I have my table all set up and all very nice and dynamic, how do we populate it automatically pulling data from the data set? So we'll see that next. How do we pull out data from the data set using X lookup? Right. So I'll write my X lookup. So X lookup. I'm going to pull out data from the data set. So X lookup. So what's X lookup? The lookup value. Well, this is the lookup value. Now watch this syntax very well because this is a wonderful syntax that would just work for you every day. Super. And it used to be much more difficult to do what we're about to do. So X lookup. You're looking this up. We need to lock the column, right? Because we're going to drag it right. I do a comma lookup array. Where are we looking this up? Obviously you should know that it's in K. You know we defined K, didn't we? So it's going to look up in K. Let me just bring this down a bit so you see that. So it's looking looking lookup array is K because we defined it as the key column of our table. We said we need to define three things in our table. The D which is the entire table, the entire table excluding the headings, then the headings themselves, and then K the key column. So K is there, comma. Now the next thing after comma is return array. Return array. What's the return array? Interesting. So we have this and then what's our return array? The return array well is saying that look this up in K, then return Bloomberg symbol. Unfortunately, we need to highlight Bloomberg symbol. We need to highlight the Bloomberg symbol column in the major table. So it's something like this. We need to come to this major table and highlight this, right? I need to highlight this. And if I highlight that and I close my bracket, I'll be able to get it. But really that's not what we want. If I enter, guess what? I do get it. I get it, right? It's not dynamic. It can drag this right. It's not going to work. So it's just not going to give me the right values. So what do we do? We need to make it dynamic. How do we do that? Well, we delete this and we need to bring in the services of XLOOKUP again. So we do another XLOOKUP right tab. So XLOOKUP another XLOOKUP to give us the dynamic list. So you remember the first XLOOKUP was looking up the company name in K. And now this XLOOKUP is going to look up this Bloomberg symbol, F4, F4, F4 until we lock the row. And guess where we're looking it up? Obviously, you know it's H. We're looking for it in H, right? The headings, right? I want to look for it in the headings. What we want it to return after looking for it in the headings. So your lookup value you have your lookup value here. You have your H, which is the lookup array. And then your return array is actually the entire D. D is the whole table. So D is the whole table. Since we said we're going to H, right? So to look up this H, to get this know the exact column H is in and then give us the entire H column, right? And then if I close the close this XLOOKUP and close this XLOOKUP and enter, I get my Bloomberg symbol. Now if I copy this all the way down, I get everything for all the companies. I copy this all the way for this entire thing. I get exactly what I need to get. I can format it this way. And then now I can do I mean this is just wonderful, isn't it? Look at it. Everything got. I mean I can come here and change this to a bidder. So you see this is a bidder. I now see it's exactly the same as picking up a bidder, right? I can change this to EV to sales, EV to sales. So now I've built a dynamic array. I've built a dynamic table. A complete table extractor. It just extracts information from a table. A mini table. I can change my company all super. Come here and do my other metric. You can say I want the average of this. Let me just lock F4, F4, lock the row. Close that. I can drag this all the way down and come here and make this the median. So the median of it's the median, right? And then I can make this the max, which is the highest. And I can now make this one the min, right? Which is the lowest. And I just drag all this metrics to the right. And there you have it. I have my dynamic lookup table using dynamic arrays at the bottom. I have a list of dynamic array lists using my dynamic array sort and unique. I have my key identifier and I have my headings all nicely listed. And then I use them in my data validation to give me a dynamic list. I use the headings in my data validations. I used edge my data validations. And now I have my bloombox symbol. I can just align that left and I can just pick everything up using this formula which I can just copy and paste all through, right? Copy and paste that all through it works. Just works perfectly. So that is the new and wonderful lookup formulas in Excel. And this is your new keyed on the block which is your X lookup. Pretty super. Yeah. Right. Thank you for watching another webinar from D Brown Consulting. And this is our monthly webinar on Power BI in Excel. And you can always reach us at Power BI PBI user group PBI user group dot com slash Lagos. So I can give you that here. So try and register register and join us. That is PBI user group dot com slash Lagos for our live lab. We do live labs every third Saturday of the month, 9am to 12 noon. And we do most of it. We have sponsors for venue. Our latest sponsors are Lagos Business School. And we have the webinars live labs on live labs, not just webinars, live labs every third Saturday of the month. The webinars every third Thursday of the month, every third Thursday of the month from 9am to 10am every third Thursday of the month. Thanks for watching the guys and we see you next month.


Hi friends, I'm quickly about to give you 5 very important tips for IS speaking section. These tips will make you get a 9 band score. So take out a pen and paper and start writing. The first tip is to enjoy the conversation with the examiner. This relaxes your brain and also improves the quality of the content you are speaking as well as will impact your fluency. The second important tip is to understand that your knowledge is not being tested but your language is. Even if you don't know something factually, don't worry. Just focus on how you are communicating with the examiner. Third, please don't give one word answers. Elaborate your answers. This will tell the examiner about your proficiency in the language. Fourth, think of your own experiences related to a topic and be genuine in what you are saying. Simple idea will explain an excellent English can fetch you 9 band score. Also, your English automatically gets better when you are speaking about personal experiences. And fifth, please do not put on a fake accent. There are no extra marks for accents. Speak in regular accent. I hope this was helpful.


Meanwhile, Ashwaju Ha-mei-Tinumbu says he's the only one running the presidential race in the forthcoming 2023 elections. He disclosed this today while addressing delegates of the APC of the United States Government House Miller. I heard of the forthcoming primary election. Tinumbu explained that his developmental strides as a former governor of Lagos State were track records that only he could save the country from the present situation. Hello, hope you enjoyed the news. Please do subscribe to our YouTube channel and don't forget to hit the notification button so you get notified about fresh news updates.


Let's go ahead and hop in So my topic this afternoon is errors business cycles and government stimulus So before I get too far I want to say like this topic I'm going to give a little bit of background of kind of how I started looking into this as I really think this topic specifically the first part about errors and business cycles is probably the first topic that I did any work on where I made a However very very small, but possibly positive contribution. It's the literature My first non-negative Contribution so anyway, so I'm really interested in this idea of errors But but how did this come about what started because I became very convinced of Austrian business cycle theory And that it is in fact true and descriptive of what happens in the economy Of course, not everybody agrees with us right and there are critics out there that try to criticize the theory along various lines all right, so for example, Paul Krugman, I likes to criticize our theory and It's very frustrating to read Paul Krugman criticizing the Austrian theory is it's obvious that he has not read much about it Or even bothered to look at professor Garrison's PowerPoints So like five minutes of those PowerPoints and most of those objections are gone Right because he clearly doesn't actually understand the theory The one that stands out to me is he makes this claim that all the Austrian business cycle theory Suggests that we should have this negative correlation between consumption and investment That's one of his favorite lines to pull out But of course you look at professor Garrison's PowerPoints The only time you'd see a negative correlation between consumption and investment is if our time preferences have changed So we're moving along the production possibility frontier toward either a faster slower longer and rate of growth, right? Of course once the growth happens now both consumption and investment are growing together at some rate or say a business cycle is starting Then we're moving outside of that. It's stable sustainable production possibility frontier with both consumption and investment growing And then when things turn they both fall So what Paul Krugman says is just flatly not true, which makes it remarkably difficult to respond to Just let's say look at the PowerPoints Anyway, or For example, there's also Friedman's plucking model And so Friedman as he kind of moved on in life thinking about the issue of business cycles He became convinced or the business cycles are not a boom and bust, but rather a bust in recovery Who in this obviously would move against the Austrian theory where the bust is the result of the boom rather than the other way around Well here Friedman isn't really understanding what's actually happening. He's thinking on too aggregate of a level And so it might be that during the boom We don't see this unusual extremely high rate of overall economic growth But we still do have actual physical limits how much we can grow, right? But we still have this misallocation of capital We're moving out of at those middle stages right being stretched in both directions toward higher consumption and also higher long-run investment But so we might not see this enormous growth In terms of things like GDP nonetheless we get this misallocation which will result in the collapse eventually And so he's overly aggregating and missing the actual story But the best criticism that I found For the Austrian business cycle theory that I thought was actually worth more of a response is the rational expectations for you Now here we have now the first benefit is the people that author these types of Criticisms tend to be more familiar right with the Austrian business cycle theory For example, there is one person fairly well-known economist. His name is Ludwig von Mises And he authored this Book theory of money and credit and what he suggests right that if people could actually come to expect what the effects would be of this monetary Injection then they would act in such a way as to offset the grand majority of the effects, right? Okay That suggests that maybe this is a pretty good criticism possibly Other people like Brian Kaplan also like to bring out this kind of thing now And we could very easily respond by saying well, maybe expectations just aren't rational Problem solved Three guys just an unrealistic assumption that you're throwing on here, but then Kaplan is very clever, right? He says but right right Austrians believe right that entrepreneurs are generally pretty good at what they do right and By our perspective what entrepreneurs do right is for see what demand is going to be for a good and plan Ahead of time for that demand so that we can actually produce the good on time for when people need it Right, so the idea of having good foresight is actually built into our view of how entrepreneurs work as they're using their judgment They're they're good at foresight in order to use their judgment. Well, if this is true, and then that suggests that maybe Entrepreneurs actually could possibly foresee what's going to happen Hmm, and if entrepreneurs are driving the production process now we have a problem We actually have to deal with right. It's not so easy as just saying expectations aren't rational Because in some places well, we may not see that strictly we come very close right to saying this Well, the first thing I want to point out is we're responding to this criticism is this idea that error Right that is making plans that don't actually work out having expectations that don't actually get fulfilled in the future This is not at all unique to the Austrian theory of the business cycle In fact, virtually every business cycle theory is built on this idea that there are errors being made By entrepreneurs as they're looking toward the future even as somebody like say John Maynard Keynes When you start looking into the way equilibrium works in his system when things start moving It's because our inventories are different than what our planned inventories were This suggests there was an error in entrepreneurs minds. We had some idea of what we wanted We wanted inventories to be they didn't end up there because we overestimated or underestimated what consumption was going to be And now we have to adjust production in order to get inventories where we would like them to be We're adjusting to the error. We made right or we also have say just kind of a standard This might be some kind of heresy to draw this graph here, but we'll do it Can't forget that a or if we look at the standard aggregate supply Agri-demand the way that is currently taught we have this nice long run aggregate supply curve in there I and suppose that something happens. See there's an increase in aggregate demand for some reason Just call it a shock. There we go. That's the reason that it increased. There's a shock Maybe people were more confident for no reason. Yeah, right. So we have this increase in aggregate demand, right? There we go. Now we get this boom right price levels start going up at the same time We get a boom in the economy in real GDP Of course, then we know the story is right the once people realize right prices have gone up And we start asking for higher levels of wages Well now the costs of production are increasing, right? As a result suppliers find it not worth actually worthwhile to produce anymore as their costs have gone up I say fall back right toward that long run aggregate supply point Now the observation of the new classicals was that if people have good foresight about what prices are going to do Right, then as aggregate demand is increasing right moving to the right aggregate supply will simultaneously right shift to the left So we don't actually get a boom in the economy whatsoever, right? So there must be something else going on, right? There's an air, right? The only reason we got this was that somehow prices rose without us realizing it and thinking to ask for higher wages along the way Now I would note here kind of the modern I would say inheritors of this view would be the real business cycle theorist, right? And the real business cycle theorist is probably one of the most ironically named Theories out there and I will admit I have certain sympathies for it Because I had a couple people that really advocate this view on my dissertation committee and and they let me pass Right, it's one of those ironically named views what they really are claiming right is that business cycles Aren't really real Rather there are these real changes that happen in the economy and it's not necessarily cyclical Just kind of we in our own minds. We like to see patterns Right, so all we had some kind of positive technology shocks now We're producing more and then there's some kind of negative technology shock and now we're producing less and we interpret this Right as being a boom followed by a bust and so on now Sometimes they do use things like the way that capital adjusts to these to get something more cyclical out of it Right, but still when you look at what is happening here What was it that started that this positive technology shock and this negative technology shock? Well, this is an error right this we had this shock come along that people did not foresee right There's this assumption right these shocks have some kind of distribution Distribution centers around zero or one depending on how you want to formulate this thing and we expect right things to be average Because on average things are average Of course, we never actually experience in any particular day the average day right so we're shocked every time Oh, wow things weren't actually average today. This is amazing, right? You know, I know that the average height for people is 510 so for for males in America But it's amazing how few people I meet that are actually 510 So weird It's okay So we're shocked all the time in these models and it's these shocks that are resulting in what we at least interpret as business cycle Okay, right so error being a part of business cycle theory is not unique to us But let's look a little bit more deeply into exactly how does error play out What is kind of the Austrian view of how errors play into explaining the business cycle and then we'll get into what? Implication does this have for things like if we want to stimulate the economy in the course of a recession? And so here when I look at the roles of the role of errors in business in our business cycle theory I do want to recommend a paper that I'm going to disagree with on some points or rather going to ignore on some points That's Gito Holtzman's general theory of error cycles In this paper, I read it when I read it. I was very very impressed Right, and then I went on and did my research totally ignoring what dr. Holtzman said, right Holtzman suggests right that we can break kind of the way that error is treated in Austrian Approaches into kind of two different ways, right? One is what he calls the consequentialist theory of error where we see that there are conditions that lead to error So error is a consequence of things and then there is Holtzman's theory Which I don't know that he would claim it existed before him right is the essentialist view of error Which is that if we really believe that economics should be based on an analysis of human action, right? Well, we never plan for error, right? So error is never a part of the end. We are shooting at G I really want to make an error is kind of his claim it rather It's just something we have to accept as a given, right? So it's just something that will show up occasionally So we should just take as given that errors will happen and then what we can do as economists is for example We can analyze institutions, right to see if they may be inherently erroneous That is that they will not possibly achieve the ends that they are shooting for and he lays out a few different Institutions that would apply to things like fiat money things like fractional reserve banking, right and so on Yeah, and so he can kind of suggest we shouldn't go down this consequentialist path So I'm going to go down this consequentialist path All right, and so in Austrian business cycle theory business cycles are really driven right by this cluster of errors They were all making right these errors in our investments We were investing at the wrong place in the production structure based on what is eventually going to make sense Now the reason this happens as professor Garrison explained is that when we have credit expansion And in effect our interest rates are lying to us So as Investors trying to decide what I want to invest in now interest rates are very low It makes sense for me to undertake very long structures of production Now normally this would be the case if savings are high savings are high interest rates fall With the credit expansion though savings aren't actually high in fact They're less than before exactly as professor Garrison described So people are trying to consume more at the same time as we're trying to invest more and this creates an issue So one way that we can think of this I really like Mises's parable here Regarding the home builder right so he kind of imagines So suppose you have a home builder building this housing development, right? And there's a certain stock of resources available and there's somebody in charge of keeping track of say how many bricks we have Right so the home builder goes to the person in charge of the brick inventory and says well How many bricks do we have and he gets some number right from them say okay? Well in that case I know that I can build say 10 houses right in this development and start building these 10 houses But then it's discovered right as they have built say the first story of each of these houses They're planning to be at least two stories tall they find out they actually don't have as many bricks as they thought This is gonna be an issue right it's physically we don't have the resources to finish what we were planning We are trying to do more than our sustainable level of resources would allow right so in that case. What do we have to do? We're gonna have to change our plans right we found this error We're gonna have to change our plans So some of these houses are not going to be able to be completed right some of them We might decide it's actually best just to scrap and try to save whatever bricks We can to move them towards some of the other houses to make sure those are two stories Maybe some of these are only going to be one story now right either way We see this error and it results in us adjusting our plans Now in as far as we try to move say some of the bricks from the houses that are already halfway built to the others We can expect there will be loss and it's very difficult to disassemble a brick house and not lose anything in the process I've not tried to do it myself, but I'm gonna trust this is the case right whenever I've seen buildings torn down We tend not to end up with very nice neat orderly piles of bricks It's it's very broken up very hard to use in the future This is kind of capturing the fact that we can't just easily move right capital from one stage to another Once we've started using it for something it becomes somewhat specific toward that particular use and that makes it very difficult To move towards something else. This is going to be a very important part of our story Okay, so this is where air comes in we're investing in the wrong things The things we're investing in though are somewhat specific Which means when these errors are revealed and we have to adjust we're going to lose something in the process All right So how are we going to explain these errors happening in light of the fact that generally entrepreneurs are pretty good at what they do So now we could just say that you know foresight is hard In particular foresight for business cycles is hard. It seems like macroeconomists who you think it's our job to predict business cycles Don't always do it very well Well, this is the case right it might be that somebody that say as a home builder They may be very good at predicting say on average what the demand for houses will be but that doesn't mean they can predict the overall State of the economy These are two different connected the different things right so maybe foresight is specific into a particular area Whereas macroeconomic foresight is very different. It's not actually the same skill right this is possible So that's one possibility another out there is that a Walter Bloch suggests that we could think of the low interest rates as an effect being bribes Right, so I may know that eventually this isn't going to work out But at the same time even if I know Austrian business cycle theory, I know that the boom does not immediately end right Okay, I might be convinced right to go along with this for a little while Because in effect by giving me a low interest that you're bribing me to participate in the boom Even if I know it's going to end I can hang on for a bit So it's me be a possibility another view out there which I Criticized is a Krillian Dempster. They suggested that there's this conflict between private and social incentives That is that in this low interest rate environment, even if I know for sure that there is this Boom that is going to end up collapsing, but I might go ahead and participate anyway despite the fact that that makes the boom happen Oh, the reason that I criticize them those who have heard my talk on game theory. If not, I'm sure it's on YouTube I go go look it up right is that if we all collectively know we can do better, right if we plan together and make different choices It seems like there's an incentive there to plan together and make different choices, right? So it's not immediately obvious to me why we wouldn't do that, right? So what I suggest and actually this was not at all original to me this particular idea Original as far as I can tell to back some day on the Evans That is that what monetary expansion does is it changes the quality of entrepreneurs by changing who is an entrepreneur? All right, so we could imagine all of our entrepreneurs say lining up in a line All right, so we have our best entrepreneurs on one side are worst at the end right and say they're they're getting bank loans All right, so they come to the bank if we review their application. Oh, yes This is a very good plan. It looks like it's going to succeed I we give this particular entrepreneur alone Next entrepreneur comes in and so on until we've run out of money and okay everybody at the end of the line doesn't get Any money right so the worst entrepreneurs are ruled out by the financial system making reasonably good choices about who to fund Know what happens though, and we have this credit expansion Well two things right first. I now have more money to hand out in loans So I can fund this somewhat lower quality, but still probably has a good chance We hope right types of on projects, but another thing happens That is those people that were the front of the line say I know how this works I know this investment very well may not pay off at some point things are going to collapse So I'm not going to stay in the line It's just abandoned the line right so if this line of people we've lost the people at the front right that have decided They would rather wait for the crash and pick things up there I and we've picked up people from the end So we've had we have this very systematic decline in the quality of the entrepreneur All right, so even if we believe that say foresight is foresight It's not just about foresight in your field, but foresight macroeconomically We still end up with errors here Precisely because good foresight is actually keeping some people out But not everybody has the same level of foresight if you want proof that there are people with bad foresight you can look at me I Have I've been investing in the stock market for a very long time. I first got started I think it's like seventh grade at the planet at the time. I was planning to go to law school and be a lawyer right Bad foresight, right? Okay. I can't even predict my own actions in the future. This is not good Right, so I was planning to go to law school. So I was investing in the stock market to pay for law school eventually And I did very very well at first the first stock that I bought was air-touch communications Which nobody here remembers because you were probably born after it got bought out by Vodafone Which are European friends may know right here in America? We know Vodafone not as Vodafone, but they're in partnership with Verizon, right? Yeah Yeah, this stock did very well. I sold it. I think I think it had roughly doubled I think it was roughly a year. I saw there's eighth grade me I have good foresight right move over Warren Buffett But it turns out what was actually happening was that it was just the mid to late 90s Right, and if you bought stock in a cell phone company odds were pretty good It was gonna get bought out by somebody and the stock was going to double me a year I got a little bit lucky, but stock was gonna double sometime within a couple years, right? So what happened then? Well, I went to college, right? You know young Warren Buffett majoring in economics, right? I decided to open in a merit rate account. This is great because now I can trade very cheaply This means I can use that wonderful force that I have really really fast and I can buy I Can buy these stocks and and watch them go bankrupt one after another It seemed like for a while that was the best predictor a stock would go bankrupt as I would buy it And I have literally one dollar and 19 cents in that account now. I Put in much more than that, right, but I keep the account, right so that I remind myself How good my foresight is right so if some people they're bad that have bad foresight They get sucked into this right and my biography is it is a case of that, okay? All right, so we can see have we have some idea then okay on average our entrepreneurs are pretty good at what they do Right and most of the time they have pretty good foresight. Maybe even pretty good at the macroeconomic foresight But when we start having that credit expansion We lose the good entrepreneurs we bring in worse entrepreneurs and we start making these errors As soon as you recognize that people are not all the same when it comes to foresight Suddenly we get this rational expectations claim right should fall to the wayside, right? We don't even need to say that nobody is rational We don't even need to say the normal rat the normal entrepreneurs aren't rational as long as there's a group out there That isn't that might possibly take their place. That's good enough All right, so let's move then and to thinking about right what kind of conclusions does this create for us when we Start thinking about government stimulus So let's talk about government stimulus is right here. I'm not thinking about say Janet Yellen when we have you know things collapsing right I'm not talking about her deciding to print more money. We've already talked about business cycles We know what the eventual consequence of that would be rather. I'm thinking about What seems to always happen? This happened to me. I was in graduate school at the time that the housing market collapsed here. There we go Right, so I was in graduate school at the time the housing market turned and started falling My wife was working at the library at the time And you know we went out with some of her coworkers and their family and I got into this discussion with one of her co-workers wives right and you know things are really bad and she turned she said oh you study economics What should we do and I said nothing? She said but we have to do something right now this is one of those cases where I've gotten someone interested in classical education Right, and there's this very useful term. I'm a Distinguo. I don't I'm pronouncing it very wrong because my Latin is horrific Right, but but it basically means let's define our terms. Right. What exactly do we mean there? We have to do something Okay, who is we? Right, I suspect we did not specifically include my office This is a really roundabout way to say somebody my wife's co-worker's wife I Suspect she did not actually include herself as someone that needs to actively do something right about the recession I don't think she was planning for example to go out and start a business and hire up all these unemployed people I don't think that was what she had in mind right we probably did not include her personally It seems though anytime people say this we have to do something They often mean the government in this kind of situation right so it's not we have to do something We the people right acting through Congress is which really just means Congress right right has to pass a law Right, right that is going to do something right. Well. What is something? Well, something generally means has to spend a lot of money right to hire up all these people that just lost their jobs Okay, so that that's really what we mean. We have to do something not we that's what they mean Right, we have to do something Congress has to pass a law the government has to sign and it's going to hire up a bunch of these people or lead to them being hired In order to create jobs right to hire these unemployed people Okay, so that's what we have in mind by government stimulus or we would call fiscal stimulus stimulus through spending So what we want this stimulus to look like right given what we know about the way Austrian capital theory works, right? well It would be really nice right if we could immediately start producing stuff that people would like to buy Which means would be really nice if we had a bunch of capital sitting around that wasn't being used Right, and then all we have to do right is hire some people right have them right start working We're going to produce stuff, right? I know kind of the story you often hear Say related in some way to the Keynesian cycle theory as well Why is it that we have all these unemployed people without shoes in this empty shoe factory? That's kind of the image we have in mind The solution is obvious right? Let's open this shoe factory's doors have them hire those people than the people have an income And they can buy shoes This is wonderful right and it ends up but we have good news as Austrians right for our government Since we believe that the cycle was caused by these misallocations of capital and once these errors are revealed We have to abandon some of this capital We have some empty shoe factories This sounds nice right? We're actually ready right for some stimulus to happen Except these aren't actually shoe factories right so I think the the much better Analogy is something like lead baseball factories Lead baseballs that that example is not original to me. I'm pretty sure I took that idea from Walter Block As you know what what uses a lead baseball right? Not very much right so I suspect there's a very low demand for these goods But but let's imagine that for example the government decided right that we needed to produce Lead baseballs right so they say start some kind of stimulus program for producing lead baseballs Right, so we start subsidizing the production of lead baseballs the government buys these things up Okay, well we can imagine then that even though it's not socially useful whatsoever. They're going to be entrepreneurs that say There's money to be made right the government will hand it to me. I'm gonna start producing lead baseballs I don't care if they sit in a government warehouse somewhere forever I I can spend this money as well as anything else right so we start producing lead baseballs I Would suggest that all of the all that misallocated Capital that we had during the boom is basically the equivalent of these lead baseball factories Now some of it we might be able to reallocate to something else, okay We'll grant that right but some of it is going to be very specific And if we want to bring that capital back into production We're going to have to have people produce those things right that we're planning to be produced now you actually hear Although maybe you didn't hear you may not have been paying attention at the time as ten years ago at this point When we hit up here, we have this is the housing prices We can see very clearly where the housing boom happened and collapsed right right around the time that the housing boom was Collapsing and the economy was falling apart Okay, we have to do something All right, so what should we do? Well, we really want to fund shovel ready projects That was the phrase right it was all it was also around this time Shortly thereafter that one of my favorite phrases from President Obama. I got spoken. We need to repair our crumbling roads and bridges right which as someone who has visited countries that are not as wealthy as the US calling our roads and bridges crumbling I've been on much worse But anyway, I might be that I just live in my serve the US. I don't know Right so That's what we're gonna do. We're going to look for shovel ready projects Right, that is where we've already made some investments right in the planning Right, so really all we need is the funding for these projects It just so happens that a lot of state governments have just sitting around waiting for funding things like infrastructure projects We want to do we want to build roads and bridges because if we don't who else will all right, so That's what we have ready to go. We've done the actually the earliest stages of production here would be the planning So we've done those early stages of production these plans are just sitting there right there shovel ready All we have to do is put the shovels in people's hands and we can start doing the excavation start doing the construction. We're good to go All right, so What do we do we fund shovel ready projects? This makes perfect sense. We're using the capital. It's already been accumulated But what does this end up doing economically? Well creates lots of jobs, right? Oh, this is wonderful In fact, it was estimated that these stimulus programs at the time we're going to create something like four million jobs Wow four million jobs now. I'm because I'm a human being Illion words start to get confusing Right when you talk to an average person, they don't actually understand the difference between a trillion and a million Right, but because I'm an economist. I am actually used to these differences Right, so four million I start comparing like four million people. I come from Ohio We have a population about 10 million. Wow, right four million people is basically the entire workforce of Ohio I that is supposed to be able to be employed thanks to the stimulus That's a lot of people. This feels like a pretty good program if we really have these people that need work Sounds good, right? Of course, then you start looking at the cost Well, it's 800 billion. Well, this doesn't sound that bad right 800. Illion divided by 4.  I have my doubts right that these four million people are making two hundred thousand dollars each in these jobs If they were I'm in the wrong field Right So okay, so the first reality we have to recognize right is that The stimulus programmer this money that's going to create jobs is not actually all going to the people It's supposed to help right there's there's other things that have to be done because we recognize that if really all we did was take This 800 billion dollars and divide it by four million and hand 200,000 dollar checks right to these four million people were previously unemployed We're not going to produce anything, right? Okay, we could actually have a very similar program in terms of creating jobs and also expense if we paid them $200,000 a year to sit at home and watch YouTube cat videos This would have exactly right the same cost exactly the same effect on it Unemployment and actually perhaps be more reliable in the effect on unemployment But we kind of recognize this is gonna be wasteful because even politicians recognize that some things are obviously wasteful right, so as much as Keynes may have recommended that we should bury What is like pots of money or jars of money and have people go and dig them up as a stimulus program He didn't really mean that right he did suggest though that it would work as a stimulus program But we all recognize this this doesn't feel like a good use of resources, right? All right, so we want to go back right what are those plans we have for something that looks productive Right, so let's build the roads and bridges right then we get some real thing out of it Of course when we do this then that means we're having to divert resources right and not just the labor But also other resources toward building these roads and bridges right now These resources likely have other uses as certainly these workers would potentially have other uses So what are we doing then with this stimulus program? We're simply taking these projects right that have been proven right to be erroneous That's why we had the capital started and didn't finish it right. It was something we abandoned right So you have something that was proven to be erroneous and we want to make sure that gets finished right This may feel productive, but I doubt that is actually productive. I'm gonna tell a story here Which before I got tenure I would not have told but one of my favorite examples of waste On my campus. We have a windmill Now windmills are not inherently unproductive But when they were thinking about doing this the Dean of our campus right had about had our campus Evaluated right for various types of green energy that we could potentially produce right so maybe we should you know put up some Solar panels or something like that Anybody who is familiar with the climate in Northeast Ohio suggest solar panels may not be the best idea And we don't have enough Sun for that to really be productive probably or maybe right We are well suited for things like windmills so we're evaluating and this is a reasonable thing to do right And so he had it evaluated and he announced at a faculty meeting at the beginning of the school year He said we did this evaluation. We found out we are not a good location for a windmill Okay But solar panels might actually be okay There are actually ways to do this that it might be all right if we say use them say to shade cars in the parking Law then we get the benefit of the shade and also produce some electricity. Okay It doesn't sound horrible, right? So if you if you want to do this, that's fine I'll happily park under one of these solar panels, right? It doesn't bother me But no, I think we're gonna build a windmill All right in order to show that we are devoted right to sustainability Okay, no isn't isn't sustainability about using resources in a wise fashion To make sure we get value out of them that we're not just wasting resources All right, you tell me I should recycle so that paper doesn't end up right in a dump somewhere All right, it comes back into use right right So what what we're really doing here is building a dump on campus with a functioning windmill on it That's not producing much electricity, right? I actually are one of our secretaries Apparently somehow got hold of the data for how much electricity we've produced and I believe in the first year after the windmill was built It produced something like five dollars worth of electricity All right, this windmill cost I believe At least fifty thousand dollars to put up All right, fortunately the math there isn't hard to do. It's a point zero one percent rate of return right, which admittedly is what I'm getting on my savings account at the moment, but This is not a great rate of return for an investment if we have the fifty thousand dollars Maybe we should do something else with it, right? There might be better investments. I I will take part of it I'm happy to do that if we're going to waste this money It turns out though. We had a grant so I didn't matter. We can waste it. That's fine Okay, all right, so this would be one of those kinds of examples We're just taking resources that could clearly be used somewhere else So there are places where it does make sense to build windmills Instead we're using them here all right on our campus where it doesn't actually make sense similarly when we make these kind of stimulus payments to try to get these Industries going again. We want to make sure those lead baseball factories don't shut down so we can actually get lead baseballs out of the end Right, we're diverting resources away from things that could actually be produced productively All right, we have workers that now aren't having jobs producing things people actually want We have other forms of capital that could actually move out of these lead baseball factories into productive lines might be costly We're gonna lose something in the process right, but we don't lose everything by producing something. Nobody wants right So that's the idea behind this behind with the effect of government stimulus We are taking away From these productive lines moving it into unproductive lines right and in fact the Because we know Austrian capital theory. It's going to be most tempting to put these investments Right the stimulus investment in those lines that we are most sure are unproductive This feels like a problem Right, so then what we've just proven right is that if we're going to have effective stimulus right that is going to produce Something right we're going to put it in those lines that are least productive in terms of value