This week, Bill is in New York City to judge the ModelOff Worldwide Excel Championship! In Today's Episode, Bill shows us trick that he picked up from Lead Judge, Professor Simon Benninga.
Now, in the past, Bill has shown us how to create a 2 Variable Data Table to run scenarios. In today's Episode #1612 we will learn how to model a Penny Pitching competition, a Random Walk, and then a Data Table based on... a blank cell! Bill also looks at the New Paintbrush Icon in Excel 2013 Charts. A very full Episode today! So, get your keyboard, open Excel and Learn Excel from MrExcel and Simon Benninga!
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Now, in the past, Bill has shown us how to create a 2 Variable Data Table to run scenarios. In today's Episode #1612 we will learn how to model a Penny Pitching competition, a Random Walk, and then a Data Table based on... a blank cell! Bill also looks at the New Paintbrush Icon in Excel 2013 Charts. A very full Episode today! So, get your keyboard, open Excel and Learn Excel from MrExcel and Simon Benninga!
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen --all this week, talking about the ModelOff Competition.
Now, professor Simon Benninga provided today's tip -- I didn't actually get him to record this but I'm going to replicate this trick that he showed me, which I think is pretty amazing: Data Table from a blank cell.
All right, so if you're a lot younger than I am there's a good chance that you had an Excel course in school and it was taught using a textbook by Simon Benninga.
He has a couple of textbooks that are the leading textbooks in the classroom principles of Finance with Excel and Financial Modeling.
So if you've used either one of those, I had a chance to spend a couple of days with the professor this week.
He talked about growing up, he went to a boarding school where you weren't allowed to smoke and you weren't allowed to play a game he called penny pitching -- two contestants flip a coin, if both coins match I win both coins, if they don't match one in his head and one his tails, you win both coins.
And he built a simple little model here to track the winnings of penny pitching, plays 100 rounds of penny pitching.
So what it does is it says: =IF(RAND()>0.5,1,-1) -- plus the previous balance, and we'll copy that down.
All right so there's a hundred trials and you see in this particular trial I ended up six coins in the hole, but we can see how we did over the course of the whole thing.
So we'll come here to Insert, and see what the Recommended Chart is, that's new.
I want to do a Scatter Chart -- note we're going to go with a line chart and no markers that one right there, that's good, click OK, and let's get that.
Hey this is new in Excel 2013, they don't give us a legend when there's a single series -- that's really, really good so we'll delete that.
So here you know, I was ahead for a while as even up three coins but then plummeted down; but the cool thing is every time we press the F9 key, we play these hundred games over.
So there's F9 you know, hey I was a hotshot, I was really up here but then things turned and I went into the negative.
F9, F9, ah there's a good one -- you know I may end up four coins ahead at the end.
So this is this is interesting right?
We can we can run a hundred simulations and see where we were throughout the whole thing.
Oh hey, let's take a look at the paintbrush icon this is new in Excel 2013 great way to format a chart -- let's see if there are any interesting charts here, kind of the blue charts oh, with the gradients.
Mm-hmm that one, yeah let's go with that -- what the heck that's good.
I like these new charts, it's not anything new you could have done all this in Excel 2010; it's just a quick one, quick way to format a chart very, very quickly.
So F9 and oh, that was a bad one.
All right, so you see that each time we play these hundred, hundred rounds things either go you know very well very bad.
So I want to play the hundred rounds many different times.
All right now, we are going to pause for a second here -- let's just take a look at some statistics.
This is the hundred times my average balance: 3.68.
In this case I was as much as 8 ahead, never behind standard deviation and where I ended up in the final.
So I'll press F9, F9, F9, F9, F9 – now, can I use a data table to solve this?
Somewhere in the past I've done a podcast that talked about creating loan scenarios, and I want to take a look at this loan, figure out what the payment is for all of these different combinations -- and so we go to Data, What-If analysis just outside of your view there, and then Data Table, and across the top row we have a bunch of values if we want to plug into the term, and down the left hand side we have a bunch of values we want to plug into the rate, and click OK.
And it runs my model, the model represented by that formula, you know -- it's 24 different times.
Cool right?
Let's see how we can now take that concept, and professor Benninga showed me how to create a Data Table from a blank cell -- so that cell right there, that blank cell at the top is the cell that we're going to model.
Isn't that crazy?
But amazingly, it works.
So I want to see these, each one of these statistics is a result of a hundred coin flips or a hundred rounds of the penny pitching -- Data, What-If analysis, Data Table.
This time we go to Column input cell and check this out, we just go to some completely blank cell that's outside of the Data Table and click OK.
And it runs our hundred, so every row is a hundred different rounds of the penny-pinching.
I could have made this bigger and very easily modeled a million coin flips in no time at all.
This is, this is crazy -- both Dan Mayoh one of the contestants, and professor Benninga showed me this, this amazing trick with the blank cell Data Table, something I had never seen before.
So just a great weekend that I had out there you know, you know the cab driver on the way said -- well what are you here for?
I said, this sounds so geeky -- I'm here to watch the 16 best Excel modelers in the world compete.
I had a great, great time.
You should check it out, go to modeloff.com, and you can be a contestant for next year's.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Now, professor Simon Benninga provided today's tip -- I didn't actually get him to record this but I'm going to replicate this trick that he showed me, which I think is pretty amazing: Data Table from a blank cell.
All right, so if you're a lot younger than I am there's a good chance that you had an Excel course in school and it was taught using a textbook by Simon Benninga.
He has a couple of textbooks that are the leading textbooks in the classroom principles of Finance with Excel and Financial Modeling.
So if you've used either one of those, I had a chance to spend a couple of days with the professor this week.
He talked about growing up, he went to a boarding school where you weren't allowed to smoke and you weren't allowed to play a game he called penny pitching -- two contestants flip a coin, if both coins match I win both coins, if they don't match one in his head and one his tails, you win both coins.
And he built a simple little model here to track the winnings of penny pitching, plays 100 rounds of penny pitching.
So what it does is it says: =IF(RAND()>0.5,1,-1) -- plus the previous balance, and we'll copy that down.
All right so there's a hundred trials and you see in this particular trial I ended up six coins in the hole, but we can see how we did over the course of the whole thing.
So we'll come here to Insert, and see what the Recommended Chart is, that's new.
I want to do a Scatter Chart -- note we're going to go with a line chart and no markers that one right there, that's good, click OK, and let's get that.
Hey this is new in Excel 2013, they don't give us a legend when there's a single series -- that's really, really good so we'll delete that.
So here you know, I was ahead for a while as even up three coins but then plummeted down; but the cool thing is every time we press the F9 key, we play these hundred games over.
So there's F9 you know, hey I was a hotshot, I was really up here but then things turned and I went into the negative.
F9, F9, ah there's a good one -- you know I may end up four coins ahead at the end.
So this is this is interesting right?
We can we can run a hundred simulations and see where we were throughout the whole thing.
Oh hey, let's take a look at the paintbrush icon this is new in Excel 2013 great way to format a chart -- let's see if there are any interesting charts here, kind of the blue charts oh, with the gradients.
Mm-hmm that one, yeah let's go with that -- what the heck that's good.
I like these new charts, it's not anything new you could have done all this in Excel 2010; it's just a quick one, quick way to format a chart very, very quickly.
So F9 and oh, that was a bad one.
All right, so you see that each time we play these hundred, hundred rounds things either go you know very well very bad.
So I want to play the hundred rounds many different times.
All right now, we are going to pause for a second here -- let's just take a look at some statistics.
This is the hundred times my average balance: 3.68.
In this case I was as much as 8 ahead, never behind standard deviation and where I ended up in the final.
So I'll press F9, F9, F9, F9, F9 – now, can I use a data table to solve this?
Somewhere in the past I've done a podcast that talked about creating loan scenarios, and I want to take a look at this loan, figure out what the payment is for all of these different combinations -- and so we go to Data, What-If analysis just outside of your view there, and then Data Table, and across the top row we have a bunch of values if we want to plug into the term, and down the left hand side we have a bunch of values we want to plug into the rate, and click OK.
And it runs my model, the model represented by that formula, you know -- it's 24 different times.
Cool right?
Let's see how we can now take that concept, and professor Benninga showed me how to create a Data Table from a blank cell -- so that cell right there, that blank cell at the top is the cell that we're going to model.
Isn't that crazy?
But amazingly, it works.
So I want to see these, each one of these statistics is a result of a hundred coin flips or a hundred rounds of the penny pitching -- Data, What-If analysis, Data Table.
This time we go to Column input cell and check this out, we just go to some completely blank cell that's outside of the Data Table and click OK.
And it runs our hundred, so every row is a hundred different rounds of the penny-pinching.
I could have made this bigger and very easily modeled a million coin flips in no time at all.
This is, this is crazy -- both Dan Mayoh one of the contestants, and professor Benninga showed me this, this amazing trick with the blank cell Data Table, something I had never seen before.
So just a great weekend that I had out there you know, you know the cab driver on the way said -- well what are you here for?
I said, this sounds so geeky -- I'm here to watch the 16 best Excel modelers in the world compete.
I had a great, great time.
You should check it out, go to modeloff.com, and you can be a contestant for next year's.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
