At the ModelOff Financial Modeling Championships, I captured some live podcasts with the finalists. Mack Wilk from episode 1616 and 1617 is back this year with a cool trick for elasticity in 3D Spearing formulas in Excel.
Transcript of the video:
Hey, welcome back to MrExcel netcast. I'm Bill Jelen live here with the MODELOFF finalist 2013.
Remember Mack Wilk from last year. He's back.
Mack What do you have for us this year?
Well last year we've been talking about elasticity in Excel.
And I would like to show you some more functions on it.
All right great.
All right.
So folks, but probably you know about the function of summing, number of sheets at the same time.
as you can see right here in the Summary sheet.
We have some Revenues. Which is a Multiplication of Volume and Price and a 5 years period.
In the sheet ABC we've got the same thing.
5 years Revenues which is multiplication of Volume and Price.
In the sheet EDF we have exactly the same but some other assumptions.
Let's just assume these are the two sales channels or whatever and as I was saying, you all probably are aware of the function that we can make the SUM as like this.
We can SUM all C3 fields from the sheets ABC to EDF and we can put it right like that, make a nice division right here and have the average price within the two sheets.
But now as I know, as you know I love elasticity in Excel, so what I do.
I add two sheets.
One would be called for example, Start and one would be called End.
I would hide everything in here just to show everybody that there is nothing to do, to find here.
Same as here.
hide and hide and then maybe we can color those two sheets just to differ them from every others.
And now look at that. We can make them not from ABC to EDF but from Start to the End.
Apostrophe to the End. All right.
We've got the same thing, but now we can for example, take the EDF move it outside and have completely different different numbers.
We can take the ABC out and we've got nothing.
Nice So this is the elasticity and Well Well, that's it basically Yes, that's a great trick because now it's completely flexible.
And you can always tell from the Start and the End what's included and what's not included.
Exactly ah that's beautiful.
I love it. What a great trick. So hey Mack.
This is your, you were one of the 16 finalists last year, and you are back again as a repeat finalist.
That's fantastic.
Oh, good luck.
Good Luck.
Thank you.
I'm going to need it. It's something very good. Very Good.Allright.
Thank you
Remember Mack Wilk from last year. He's back.
Mack What do you have for us this year?
Well last year we've been talking about elasticity in Excel.
And I would like to show you some more functions on it.
All right great.
All right.
So folks, but probably you know about the function of summing, number of sheets at the same time.
as you can see right here in the Summary sheet.
We have some Revenues. Which is a Multiplication of Volume and Price and a 5 years period.
In the sheet ABC we've got the same thing.
5 years Revenues which is multiplication of Volume and Price.
In the sheet EDF we have exactly the same but some other assumptions.
Let's just assume these are the two sales channels or whatever and as I was saying, you all probably are aware of the function that we can make the SUM as like this.
We can SUM all C3 fields from the sheets ABC to EDF and we can put it right like that, make a nice division right here and have the average price within the two sheets.
But now as I know, as you know I love elasticity in Excel, so what I do.
I add two sheets.
One would be called for example, Start and one would be called End.
I would hide everything in here just to show everybody that there is nothing to do, to find here.
Same as here.
hide and hide and then maybe we can color those two sheets just to differ them from every others.
And now look at that. We can make them not from ABC to EDF but from Start to the End.
Apostrophe to the End. All right.
We've got the same thing, but now we can for example, take the EDF move it outside and have completely different different numbers.
We can take the ABC out and we've got nothing.
Nice So this is the elasticity and Well Well, that's it basically Yes, that's a great trick because now it's completely flexible.
And you can always tell from the Start and the End what's included and what's not included.
Exactly ah that's beautiful.
I love it. What a great trick. So hey Mack.
This is your, you were one of the 16 finalists last year, and you are back again as a repeat finalist.
That's fantastic.
Oh, good luck.
Good Luck.
Thank you.
I'm going to need it. It's something very good. Very Good.Allright.
Thank you
