Bill 'MrExcel' Jelen: [Almost] Live From New York City to Judge the ModelOff Worldwide Excel Championship! With 16 Excel Rock Stars, from around the Globe, in NYC -- and Bill having access to them all -- why not do some Podcasting On-Site!
When pondering the word 'Elasticity' Microsoft Excel seldom seems a logical connection - that is, unless, you are Mack Wilk, from Poland.
Bill states that, "I find that the only time I actually use the Group feature is as a result of using the Subtotal command." However, Today - in Episode #1616, Mack Wilk shows us how to add Elasticity to a Worksheet by using the Group feature.
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!
When pondering the word 'Elasticity' Microsoft Excel seldom seems a logical connection - that is, unless, you are Mack Wilk, from Poland.
Bill states that, "I find that the only time I actually use the Group feature is as a result of using the Subtotal command." However, Today - in Episode #1616, Mack Wilk shows us how to add Elasticity to a Worksheet by using the Group feature.
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:
Bill Jelen: Hey, welcome back to another MrExcel netcast, I'm Bill Jelen.
This is so cool; we’re here in New York City at the World Modeling Competition.
I have Mack Wilk here all the way from Poland – this is called Unlimited Number of Credits.
All right Mack.
So Mack, how long have you been using Excel?
Mack Wilk: Six years at PwC and more than ten years in total.
Bill Jelen: Oh that's cool.
Where'd you hear about the competition?
Mack Wilk: Well from one of my clients.
Bill Jelen: Oh really?
Mack Wilk: Told me to take part in the competition.
Bill Jelen: So two rounds, started out with 2000 people and you're down to one of the final 16 who got to fly to New York City.
So what's your trick today?
Mack Wilk: Okay my trick is to add some basic elasticity to the model.
We'll do that on the examples of loans and credits and we built, we built a template which will enable you to add unlimited number of loans and credits, and you can do the same with provisions, with costs, with certain contracts, with model revenues -- it has very broad functionality.
Okay so let's see on the screen, as you can see here.
Bill Jelen: I can’t see down the row 14, so scroll down for us.
Mack Wilk: Okay.
Bill Jelen: Okay so you've built a nice little template.
Mack Wilk: Yeah here's the template of the of the credit, you have the name of the credit -- we can change it to anything, anything we want.
Here you have some basic modeling of the balance sheet positions; opening balance, increases, increases of credit balance, loans and credits closing balance.
Here is the calculation of interest; now we can go up to the summary section and right here we put =SUMIF formula, so here it is.
=SUMIF($A$11:$A$25,$A7,E$11:E$25) Bill Jelen: Okay Paste Special there was Paste Special formulas, right?
Mack Wilk: Yeah.
Bill Jelen: You did all E+S+F.
Mack Wilk: E+S+F , yeah.
Bill Jelen: E+S+F. All right, okay.
Mack Wilk: And well, we can also group the rows right here too, uhm yeah and we can.
Bill Jelen: Okay now, scroll down so we can see that.
Mack Wilk: Yeah.
Bill Jelen: All right so you selected from which road, which row?
Mack Wilk: We selected from the beginning of the first credit, down to line end of credits.
Bill Jelen: Okay.
Mack Wilk: Which, which finished that the calculation area where we can add the credits.
I also grouped the rows in order to make it look simple.
We can also use the option to change the outline settings, right here -- it will make the whole thing give even more readable.
And right now the main feature, right now we can add as many credits as we want.
Bill Jelen: Oh, so you're always adding them above the end of credits bar.
Mack Wilk: Exactly, and you can see on the on the summary section that it changes all the time -- and if I delete three of them, or if I delete the first two it will change automatically.
And that's t how my model has all this elasticity -- my clients can add one, two, three, five, ten, or even more credits.
They can do the same with certain costs, with certain revenue lines; it can be used in many areas in the model.
Bill Jelen: Okay this is great.
Now, that happened so fast, say that you want to add another credit -- I want to see slowly how you add a credit.
So you're going from row 11?
Mack Wilk: I just grabbed all the lines of the template -- or more easy, I can group the rows and just.
Bill Jelen: So you went from 11 to 24, so you're including everything in this group plus the row below it.
Mack Wilk: Yes.
Bill Jelen: That's why we everything, okay got it -- and then Ctrl-C?
Mack Wilk: Yeah copy, and Insert Copied Cells.
Bill Jelen: Copied Cells inserts that whole block, which is all now nicely formatted exactly like the original one.
Mack Wilk: Yeah.
Bill Jelen: They can just add, it looks like you're adding a row but you're really adding a whole section there.
That’s a great, great trick -- all right.
Mack Wilk: Exactly, and of course I can model more about the credit.
I can add different currencies, different FX differences.
Bill Jelen: It can as complicated that as you want.
Mack Wilk: Sky is the limit.
Bill Jelen: But again, with that grouping then you can just add more, and more, and more –and it always adds up at the top.
Mack Wilk: Exactly.
Bill Jelen: Very cool trick, all right.
All right Mack.
Mack Wilk: Thank you.
Bill Jelen: That's good.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
This is so cool; we’re here in New York City at the World Modeling Competition.
I have Mack Wilk here all the way from Poland – this is called Unlimited Number of Credits.
All right Mack.
So Mack, how long have you been using Excel?
Mack Wilk: Six years at PwC and more than ten years in total.
Bill Jelen: Oh that's cool.
Where'd you hear about the competition?
Mack Wilk: Well from one of my clients.
Bill Jelen: Oh really?
Mack Wilk: Told me to take part in the competition.
Bill Jelen: So two rounds, started out with 2000 people and you're down to one of the final 16 who got to fly to New York City.
So what's your trick today?
Mack Wilk: Okay my trick is to add some basic elasticity to the model.
We'll do that on the examples of loans and credits and we built, we built a template which will enable you to add unlimited number of loans and credits, and you can do the same with provisions, with costs, with certain contracts, with model revenues -- it has very broad functionality.
Okay so let's see on the screen, as you can see here.
Bill Jelen: I can’t see down the row 14, so scroll down for us.
Mack Wilk: Okay.
Bill Jelen: Okay so you've built a nice little template.
Mack Wilk: Yeah here's the template of the of the credit, you have the name of the credit -- we can change it to anything, anything we want.
Here you have some basic modeling of the balance sheet positions; opening balance, increases, increases of credit balance, loans and credits closing balance.
Here is the calculation of interest; now we can go up to the summary section and right here we put =SUMIF formula, so here it is.
=SUMIF($A$11:$A$25,$A7,E$11:E$25) Bill Jelen: Okay Paste Special there was Paste Special formulas, right?
Mack Wilk: Yeah.
Bill Jelen: You did all E+S+F.
Mack Wilk: E+S+F , yeah.
Bill Jelen: E+S+F. All right, okay.
Mack Wilk: And well, we can also group the rows right here too, uhm yeah and we can.
Bill Jelen: Okay now, scroll down so we can see that.
Mack Wilk: Yeah.
Bill Jelen: All right so you selected from which road, which row?
Mack Wilk: We selected from the beginning of the first credit, down to line end of credits.
Bill Jelen: Okay.
Mack Wilk: Which, which finished that the calculation area where we can add the credits.
I also grouped the rows in order to make it look simple.
We can also use the option to change the outline settings, right here -- it will make the whole thing give even more readable.
And right now the main feature, right now we can add as many credits as we want.
Bill Jelen: Oh, so you're always adding them above the end of credits bar.
Mack Wilk: Exactly, and you can see on the on the summary section that it changes all the time -- and if I delete three of them, or if I delete the first two it will change automatically.
And that's t how my model has all this elasticity -- my clients can add one, two, three, five, ten, or even more credits.
They can do the same with certain costs, with certain revenue lines; it can be used in many areas in the model.
Bill Jelen: Okay this is great.
Now, that happened so fast, say that you want to add another credit -- I want to see slowly how you add a credit.
So you're going from row 11?
Mack Wilk: I just grabbed all the lines of the template -- or more easy, I can group the rows and just.
Bill Jelen: So you went from 11 to 24, so you're including everything in this group plus the row below it.
Mack Wilk: Yes.
Bill Jelen: That's why we everything, okay got it -- and then Ctrl-C?
Mack Wilk: Yeah copy, and Insert Copied Cells.
Bill Jelen: Copied Cells inserts that whole block, which is all now nicely formatted exactly like the original one.
Mack Wilk: Yeah.
Bill Jelen: They can just add, it looks like you're adding a row but you're really adding a whole section there.
That’s a great, great trick -- all right.
Mack Wilk: Exactly, and of course I can model more about the credit.
I can add different currencies, different FX differences.
Bill Jelen: It can as complicated that as you want.
Mack Wilk: Sky is the limit.
Bill Jelen: But again, with that grouping then you can just add more, and more, and more –and it always adds up at the top.
Mack Wilk: Exactly.
Bill Jelen: Very cool trick, all right.
All right Mack.
Mack Wilk: Thank you.
Bill Jelen: That's good.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
