Collaborate on Fooding Bill Format for Finance with Ease Using airSlate SignNow

Fooding bill format for Finance

So I had this joke that I'm making my live Excel seminars. Somewhere pretty early, around topic one, I asked who in the room uses particular method 5 keyboard shortcuts to quickly copy a formula down. And out of 100 people, there will be one or two, right? And I always make a joke. I say, well look, you know I'm working on getting Excel as a sport in the Olympics. There is laughter from the room. And then I say, you know if I ever succeed you two will be on our national team because you're faster than everyone in this room. I am recording this on Sunday, December 5th. Yesterday, December 4th, 2021 is a red-letter day in the world of Excel. This is the first day that an Excel competition was on TV. Now for my US viewers, it's ESPN3. You understand the ESPN family. For people in Europe, I know that ESPN is not big there. But it is our premier sports network - 24 hours times three channels here in the US. Now, for those of you in the US, you're like, “oh, I wonder if my cable system has ESPN3”? They probably don't. You have ESPN 1 and you have ESPN 2. ESPN3 is part of the the app - the WatchESPN app. It's where you can find obscure sports. Live soccer matches from Europe in the middle of the night. The Australian Open in the middle of the night. And an Excel competition. December 4th, 2021 was the first day that an Excel competition, the Financial Modeling World Cup Open was featured on TV. The first session was about 3:00 in the morning and that was the Asia competition. Hosted by Danielle and Andrew, they're like the Neil Armstrong and Buzz Aldrin of Excel. Eleven hours later, Szilvia Juhasz and myself hosted the Americas competition. Being the second person to do something amazing? Yes, no one really remembers Alan Bean and Pete Conrad. For our part of the competition, Szilvia and I commented as Darren Wright and Stephanie Annerose went head-to-head on a Excel card game. Stephanie advanced and then was beaten by Joseph Palisoc on something that was very difficult called Feeding Excelopolis. Now I want to show you my solution to Feeding Excelopolis, at least for the first 60% of it. This is oddly fascinating to watch people competing in Excel and you have two more opportunities. On Wednesday, December 8th, 2021, they'll go from the round of 32 down to 8. And then on Saturday, December 11th, 2021 from 8 down to the championships. The winner takes home a $10,000 prize thanks to sponsors AG Capital and Microsoft. Links to all of this that are down in the YouTube description below. Let's get on with the video. Learn Excel from MrExcel Podcast episode 2447 Feeding Excelopolis, levels one to three from the Financial Modeling World Cup Open. Hey, welcome back to the MrExcel netcast, I am Bill Jelen. Alright so here is the problem. It's called Feeding Excelopolis. There's a little village. Currently, population: one. Each year, each person gets to choose one plot of land that they're going to plant. Some land is more fertile than others, so here this will grow 5 units of food versus this one that grows 2 units of food. So in year one you're limited to the red cells. And of course you're going to choose to grow 5 units of food Now during that year, you're going to consume 2 units of food, so you grew 5 and consumed 2. That means you have three leftover. You preserve it and throw it in the warehouse. In year 2, again, you're going to plant the exact same plot again and now your warehouse grows to six. And then year three. Now, here's the interesting one. After year four, you now have 12 units in the warehouse. And as soon as the units in the warehouse is 10 times more than the population. Population one, as soon as we get more than 10, then you get to hire another employee. Another nomad goes walking past and you get to say “hey come join me at Excelopolis”. Now there's a cost to that. You actually lose 10 units in the warehouse. So you had 12. You go down to two right? But now it gets interesting. Because now we have two people, right? So the person one they're going to choose to plant plot #5. And then person number two they choose to plant plot #4. When the next person shows up, however many years that takes. They're going to choose one of these threes. And eventually the last person that you would add would have to be stuck out here in plot 2. You might as well not add that person because he's going to grow two, consume two. It's a wash. With six people or less in your village, : you can only plant the nine cells in the red ring. When your population gets to 7 on up to 16, then you can plant the orange or red ring. Then at 17 people or beyond, you can farm all three rings, all 49 cells. Levels 1 through 3 are similar. How many years will it take to grow to 10 people, 26 people, and 50 people. If we can solve for level three, you will be able to solve levels 2 and level 1 and have 310 points. That would be enough to win Excelopolis. Level 4 and level 5, we'll talk about those later. They are considerably more difficult. And I'm making a conscious decision right now that it will never be solved in 30 minutes. Let's take a look at how they take this data, the 49 cells and show it to us. Here in that 7 by 7 grid, A1 to A17 are the numbers down the left hand side, then the second column, then the third column. I actually appreciate that they've unwound this for me because one of the first things I was thinking is how am I going to unwind this data right? So a simple TRANSPOSE here gets me the address. And then the thing that took me way too much time. I could not figure out a good way to figure out. I call them rings. Which ring are we in? Are we in the red ring? The orange and red ring? Or the green, orange and red ring? I renamed them 1, 2, and 3. I basically just manually fill this in with a couple of tricks along the way. We know that A1 A1 starts in column six. We can see that back here where A1 is in column F. So a simple conversion. There a SEQUENCE function 49,1,6. So that tells me what column we're in. The next thing I have to do to get the fertility is to figure out what row we are in. So I have the single input cell here that I get to put the game number. Putting 30.1 in the cell that's row 101 from a simple little MATCH function there. And now I know which column and which row. I am able to retrieve using INDEX of all of the cells on the case study using that row and that column. I can now get the fertility for each individual plot. The population in year one is simple, that's one. Which rings are available? So if we have less than seven people, we only have the first ring available. If we have 7 to 16 second ring available. 17 and up is the third ring available. So a simple little VLOOKUP there with a comma TRUE at the end, which is the giving the value just less than what I'm looking for. But then this cell was the hardest cell to figure out. How many units are were going to retrieve. How many units are we going to harvest based on the population. So out here. The first thing I want to know is of all the 49 cells, which ones are we allowed to farm right? And because we have only one ring available. Here in C3, I use the FILTER function. So filter all of these fertility values for the ring being less than or equal to 1. In this case it gets me 9 cells. How many people do we have? Just hypothetically let's say 4 because it's more interesting than saying one. And then what will we farm? Well, if you look at it, we are in 5, 4, 3, 3. Those are the best of the 9 available to us. So here let's go to person five. You see that we get an extra three there. So you all know that if you had these nine cells and you need to figure out the largest cell, that would be the MAX function, right? MAX gets you the largest. But how do you get the second largest? We switch over from MAX to a function called LARGE. LARGE(, 1) is going to be exactly the same as MAX. But, it's really good when you do LARGE(,2) because we will then get the second largest. So it all comes down to if we have five people growing and it can be in these nine cells, which are the five largest of those cells? And then sum them up. Thanks to dynamic arrays, it's pretty cool. I don't have to put in five individual formulas here. I can just put in a SEQUENCE of five and that gives me 1,2,3,4,5 which generates the five largest cells 5,4,3,3,3. And then send that whole thing into a SUM function like that to get a single number. That's the logic that we have to encapsulate back in D3, right? So this whole little dance over here. Condensed it down to a single number using this formula. The FILTER on the inside, the LARGE with the SEQUENCE. This formula right here is this solves it. Once you've solved that, then it's all pretty simple. How much do we eat? We ate two times the number of people. What goes into the warehouse? It's the SUM of what used to be in the warehouse plus how much we grew minus how much we ate. Notice I use the SUM function there, otherwise that “Warehouse” would have given me a error if I would have just done F2 plus D3 minus E3. That is a great little trick that I learned from Zach Barresse. And then the big question is, are we allowed to hire a new person or we allowed to get a new nomad? So equal Is the food in the warehouse greater than 10 times the population like that alright? And if that's true? Equal IF if this is true, then we get one extra person. Otherwise zero extra people. Plus what we had last year, right? So then we can kind of copy this and double click to copy it down, right? So we can see our population grow But there's one thing that I have not factored in here yet. That's when we get an extra person for some weird reason. I can't explain it. I don't know if it's a payment we have to make to the state to get a license for an extra employee. We lose 10 units in the warehouse times the old population, right? So right here this 17 I need to adjust that 17 to basically remove 10. Now, remember you only have 30 minutes to solve this and on the day that I was trying to solve it, the formula I came up with was not a formula that I'm very proud of. It seems insanely awkward. But because that's the formula that I came up with in the heat of battle, it's the formula that I'm sticking with. There has to be a more efficient way to write this. I'm keeping it there to show that the problem with trying to solve a complex problem in 30 minutes. And at this point, it's possible that my competitor already has some points on the board and I'm starting to sweat. You're going to go with a horrible formula if it works right? So if the formula works, we went from 12. Then we had to deduct 10. To get back to 7. Alright based on the game number, there's a goal, right? And that goal is do we have 10 people, 26 people were 50 people. So a second little VLOOKUP table here. So if you look up at what game we are on. And then the question is, have we grown to enough people to meet the goal for the case. And in this one, it is actually year 448 that we get there. So I'm looking for the 1. And what's really funny is they give you the right answer. We know that the answer is 448. I don't agree with that. I think the answer is 449. Because to me, I'm starting the new employee on January 1st of the next year, right? I don't know if the 448 is saying that they would hire him on December 31st or you're just saying it took one year to get from January 1st of year one to January 1st of year two. It didn't matter. I saw exactly what was happening. I just said I'm going to subtract 1 from that to come up with an answer that matches their answer. Now we should be able to test this with the other levels, so up here we have level 15.1 should give us 184 And then the game 0.1. Let's see we got 54. Alright good so that means it's handling all three cases. Now in real life I was 26 minutes in. Until I scored basically my first point. But then the beauty of this setting up this huge model all based on this one single input cell with one single output cell. Is that I can now use an incredibly powerful function. Back on the Data tab called What-if analysis and the Data Table. To set this up along the top I need to have a formula that points to my single result. So I want to know the first year met. And then down the left hand side the values they're going to go in that input cell. So =SEQUENCE(50) will give you the numbers 1 to 50 down the left hand side like that. What-If Analysis, Data table. We're going to take that number along the left hand column, those game numbers, and we're going to plug them into that cell right there, click OK. And. There are all of the answers. It takes a good long time to calculate. Every time we run that table, how many years do I have here? I have 1647 years, so it's doing 9 times 1647 calculations for every row. This is going to become very very sluggish. And on the formulas tab I want to be careful to go into Calculation Options and say “Automatic Except For Data Tables”. That setting is talking about this. Alright, so there's all my answers. I just need to get them back into the answer key. After solving levels 1, 2, and 3, I would have 310 points. In the competition yesterday the most that anyone got was 72, right? I'm not saying that I would have got 310 points in the competition, but this is the approach that I think would allow you to get to 310 points in the 30 minutes or less. I'm curious if you've stuck with me this far. If anyone out there knows how you could solve Level 4 and level 5. Let me explain to you how level 4 and level five are different and they're completely different from each other as well. And see if anyone out there has any kind of a inkling of how you would ever solve this. I mean right here I have one simple formula and when we get to level 4, that formula has to become an entire table in order to figure out what's going on. Alright, so let's go take a look at level 4. All 49 cells are in play. How long will it take Excelopolis to grow to a population to 50 units? And right here, this sentence: land plot changes are not possible. Oh my gosh, alright. So what does that mean? That means that employee number one, the first person who started working that, they're going to get stuck there. And that's fine because that's a 5. And that's a great plot to have. But potentially like employee number three who gets stuck here in farming 3 units and eating 2 units. Very inefficient scenario. When we have enough people. When person seven shows up and now we're allowed to choose from the orange ring. They're not allowed to move, right? They're not allowed to go out to five. They have to be stuck here in three. And then eventually when we get to the point where we have 17 people and the green becomes available. Now in this case there's no fives out there in the green, so no one would want to move, perhaps. Right, so how do you keep track of that? How this person chose this plot first, and then they're never allowed to move right? So that's level 4. Level 5. This one runs for 500 years with again different fertility in each of the plots, but something called Land Plot Degradation is active. So here are the rules. Consider all of the cells. You're not locked in with less fertile cells, but land degradation should be factored in. If a cell if the same cell is used for 10 consecutive years. So let's say you started in that five. After 10 years it will drop to four. After 20 years it'll drop to three, right? And we're going out 500 years. Eventually it's going to drop to zero. And what you would do then is not use it for the next year and its fertility will reset. You know you come up with a situation like when I had a tie. If I had a choice between this 5 and this other 5. If one of those fives had been used for just one year and wasn't about to degrade. And the other one is about to degrade, I would want to be smart enough to not use the one that's about to degrade, right? Both of these cause my head to explode. But the great news is I only had 30 minutes to solve it and I got pretty far in 30 minutes, so that's my so that's my take on feeding Exelopolis. If you have ideas on level 4 level 5 please down in the YouTube comments below, let me know. If you like these videos, please down below Like, Subscribe, Ring the bell. Feel free to post any questions or comments down in the comments below. You have two more opportunities to watch this great competition. The FMWC Open Wednesday, December 8th, Saturday, December 11th, on ESPN3 or at the YouTube channel for the FMWC. I will put links to both of those down in the YouTube description below. If you watch this far, it is great TV. I want to thanks you for stopping by. We'll see you next time for another netcast from MrExcel. Let’s hear you, Nancy!