Excel is a necessity in this industry as credit unions aim to compile a wealth of information about tens of thousands of members in a flexible format. Compiling this information is a challenge on its own, but then we have to find a solution to analyze it. Because we know you value the ability to generate reports that may not be defined in a software model, delivery of Twenty Twenty’s risk model utilizes Pivot Tables to analyze your data. I wanted to provide a quick and easy Pivot Table tutorial so you can start using this tool to make your life easier.
Let’s say you have a file with 100,000 loans. It contains member number, loan type, current balance and FICO score. You want to see the total current balance of each loan type, average current balance, and average current FICO score. The easiest way to do that is using a Pivot Table. Here’s how (Using Excel 2007).
1) Click the “Insert” toolbar and click “PivotTable.”
2) It will ask you to select a table or range for your data. That is the data you want to summarize, including headings. To select a wide range of data quickly, click the top left cell in your table. Hold Ctrl + Shift and hit the down arrow until you get to the bottom of your table, then hit the right arrow until you have selected your whole table. Then click “OK.”
3) You should have a blank pivot table and a toolbar to the right titled “PivotTable Field List.” If you don’t have this toolbar, right click your pivot table and click “Show Field List.”
4) Now you’re ready to populate your table. Since you want to summarize your data by loan type, drag your “Loan Type” field into the “Row Labels” area of your field list.
5) Now, drag “Current Balance” into the “Values” area of your field list. It should show the “Sum of Current Balance” of each loan type.
6) You also want to see average balances. Drag “Current Balance” into the “Values” area again. Click on your second “Sum of Current Balance” drop down menu in the area of your field list and click “Value Field Settings.” This will allow you to make calculations a variety of different ways such as Sum, Count, or Average. Click Average to calculate an average value for each loan type.
7) Finally, drag the FICO score into your “Values” area. Adjust the “Value Field Settings” by clicking on the FICO score field in the value area to calculate averages.
The above example is simple, but the relationships you can analyze using Pivot Tables are only limited by the data available and your imagination.
Have any questions on Pivot Tables? Email Me.
-Dan Price, CPA
Twenty Twenty Analytics Blogger