ModelOff Finalist Vlad Smirnov works for a bank and is setting up reserves for outstanding debt based on risk class. But, there are some months where the pivot table does not include all risk classes. Since the SUMPRODUCT formulas are counting on all risk classes being there, Vlad shows how to force the pivot table to always show each risk class for each month.
Transcript of the video:
Okay, hey, welcome back! Here we've got the ModelOff finals in New York City. This is 2013.
I'm here with one of the finalists,Vlad Smirnov.
Bill: Vlad where are you from?
Vlad: From Moscow, Russia.
Bill: Moscow Russia. All right!
What are you doing in Moscow?
Vlad: I am a financial analyst at TCS bank.
Bill: At a bank, okay great! what's your trick for us today?
Vlad: I'll show you how to how to show items with no data in Pivot Table.
Bill: All right, so what do you have there? You have...
Vlad: Okay, we have here Vintage of loans, each month, its Risk Status and Balance.
In order to calculate the provisions for these loans, we need to multiply Balances by each Probabilities of default but it in, as you see, these Probabilities of Default exist for each Months and for each Risk Status, but not for each months, there exists each Risk Status.
Bill: Okay, alright great!
Vlad: So, We'll do it through the Pivot Table.
We launch our Pivot Table, to this sheet.
Good, okay.
Now, we have Vintage and Risk Stages in Row Labels and Months in Column labels Balance but somewhere else.
So, we see that for January, we have three week statuses 0, 1, 2 and 3 and we see how their credit evolves through, through the Months but for example, for April, we have just zero Risk Status But we need to each to multiply and we need to multiply by each probability.
So, we go to Fill Settings, and then Layout and Print and then Show items with no data.
So, now we have each Risk Status for each Months.
Okay, so we need to calculate provisions for each Months and for example, January provisions for Month February, it's SUMPRODUCT of this credit balance, multiplied by It's probability, it's all default There are only three risk statuses there.
Bill: Oh, yeah, okay.
Vlad: So, network...
Bill: It's a network, so if there was a Risk status missing then that formula doesn't work and you can copy the formula anywhere.
Vlad: Yeah!
Bill: Yeah, I got you. That's really cool.
Now, one thing I noticed when you type that formula, you initially want to put it in semicolon but here you have to use a comma Bill: that's, Vlad: Yes.
Bill: that's an issue with some international versions of Excel, instead of commas you use semicolon.
Vlad: Yes, for example, in Russia, I use semicolon and I also use point, dot for decimal, for decimal points.
So, it's up to regional settings.
Bill: Okay, so now tomorrow for the model of finals, would you be using your own laptop then, yes, the Vlad: Yes.
Bill: otherwise, you'd be at a disadvantage, you have to switch over Vlad:Yes, sure Bill: I appreciate, you are translating here on the podcast from using my podcast laptop. All right Vlad, well hey, welcome to New York, and I hope you do great at the model of finals.
Thanks for that great tip, "How to show items with no data" Vlad: Thank you bill Bill: All right!
I'm here with one of the finalists,Vlad Smirnov.
Bill: Vlad where are you from?
Vlad: From Moscow, Russia.
Bill: Moscow Russia. All right!
What are you doing in Moscow?
Vlad: I am a financial analyst at TCS bank.
Bill: At a bank, okay great! what's your trick for us today?
Vlad: I'll show you how to how to show items with no data in Pivot Table.
Bill: All right, so what do you have there? You have...
Vlad: Okay, we have here Vintage of loans, each month, its Risk Status and Balance.
In order to calculate the provisions for these loans, we need to multiply Balances by each Probabilities of default but it in, as you see, these Probabilities of Default exist for each Months and for each Risk Status, but not for each months, there exists each Risk Status.
Bill: Okay, alright great!
Vlad: So, We'll do it through the Pivot Table.
We launch our Pivot Table, to this sheet.
Good, okay.
Now, we have Vintage and Risk Stages in Row Labels and Months in Column labels Balance but somewhere else.
So, we see that for January, we have three week statuses 0, 1, 2 and 3 and we see how their credit evolves through, through the Months but for example, for April, we have just zero Risk Status But we need to each to multiply and we need to multiply by each probability.
So, we go to Fill Settings, and then Layout and Print and then Show items with no data.
So, now we have each Risk Status for each Months.
Okay, so we need to calculate provisions for each Months and for example, January provisions for Month February, it's SUMPRODUCT of this credit balance, multiplied by It's probability, it's all default There are only three risk statuses there.
Bill: Oh, yeah, okay.
Vlad: So, network...
Bill: It's a network, so if there was a Risk status missing then that formula doesn't work and you can copy the formula anywhere.
Vlad: Yeah!
Bill: Yeah, I got you. That's really cool.
Now, one thing I noticed when you type that formula, you initially want to put it in semicolon but here you have to use a comma Bill: that's, Vlad: Yes.
Bill: that's an issue with some international versions of Excel, instead of commas you use semicolon.
Vlad: Yes, for example, in Russia, I use semicolon and I also use point, dot for decimal, for decimal points.
So, it's up to regional settings.
Bill: Okay, so now tomorrow for the model of finals, would you be using your own laptop then, yes, the Vlad: Yes.
Bill: otherwise, you'd be at a disadvantage, you have to switch over Vlad:Yes, sure Bill: I appreciate, you are translating here on the podcast from using my podcast laptop. All right Vlad, well hey, welcome to New York, and I hope you do great at the model of finals.
Thanks for that great tip, "How to show items with no data" Vlad: Thank you bill Bill: All right!
