Learn Excel - Vlad Smirnov - See all Pivot Items - ModelOff - Podcast #1839

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Dec 19, 2013.
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.
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top