logo
  • Home
  • Blog-Navigation
  • Art
  • About
  • Contact

Data Visualization – Banking Case Lab : Microsoft Excel – use Secondary Axis to Create Two Y Axes

· Roopam Upadhyay

Excel Man - by Roopam

Excel Man – by Roopam

Analytics Lab

Malcolm Gladwell in his book ‘Outliers‘ says that it takes approximately 10,000 hours of training or practice for anybody to master a subject. There is nothing in the world that could beat hands-on practice for learning. Keeping this in mind let me start a new series on YOU CANalytics called ‘Analytics Lab’ where we will practice some of the case studies we have discussed in the past on YOU CANalytics. We will use R and Microsoft Excel (both easily available tools) for this.

It’s a bird…It’s a plane…It’s Excel man! Did I ever tell you what a super hero Excel is. In my opinion Excel is the best tool to start learning advanced analytics! Today we will start the ‘Analytics Lab’ using concepts we have discussed in the banking case study. This time we will create a two Y axes plot by using the secondary axis function in Excel. In the next article we will create the same plot on R.

Banking Case 

Recall the banking case: in that you were playing the role of the Chief Risk Officer (CRO) for CyndiCat bank. The bank had disbursed 60816 auto loans in the quarter between April–June 2012. Additionally, you had noticed around 2.5% of overall bad rate for these loans. The idea was to identify customer segments with distinct bad rates. Bad rate, by the way, is percentage of customer defaulted on their payments. You did some exploratory data analysis (EDA) using tools of data visualization and found a relationship between age with bad rates (Part 1). If you recall, you have observed the following age groups wise population histogram and bad rate trend.

Presentation1

Using Secondary Axis to Create Two Y Axes in Excel

A good practice is to present these two graphs in a single chart. This is useful because you and your audience don’t need to jump from one graph to another during analysis. Let me present the data we are going to use for our purpose. I recommend you copy past this data on Excel and evaluate for yourself the merits of a single chart in comparison to two.

Age Groups Number of Loans Bad Loans Good Loans Bad Rate
21-24 310 14 296 4.5%
24-27 511 20 491 3.9%
27-30 4000 172 3828 4.3%
30-33 4568 169 4399 3.7%
33-36 5698 188 5510 3.3%
36-39 8209 197 8012 2.4%
39-42 8117 211 7906 2.6%
42-45 9000 216 8784 2.4%
45-48 7600 152 7448 2.0%
48-51 6000 84 5916 1.4%
51-54 4000 64 3936 1.6%
54-57 2000 26 1974 1.3%
57-60 788 9 779 1.1%

 

The last column i.e. ‘Bad Rate’ is the percentage of ‘Bad Loans’ to ‘Number of Loans’ i.e. for the age group 21-24 it is 14/310=4.5%.

Slide1

 

The first thing you need to do is create a bar plot of the first two columns i.e. ‘Age Groups’ and ‘Number of Loans’. Use Column in Charts within Insert tab (as shown above) to create a vertical bar plot. The default excel bar plot will look different. I have done some additional formatting for the bar plot to make it appropriate for my purpose. You could leave the default excel format for most part. The only thing you want to change is the color of the bars from default excel blue to something mellow like grey.

Slide3

 

The next step is to add the trend line for the last column i.e. ‘Bad Rate’ on top of the above chart. For this, right click you mouse anywhere on top of the above bar chart in Excel and then choose Select Data…You will notice a pop up window similar to the one shown belowSlide4

 

In the above pop-up window click on Add below Legend Entries (Series). You will get a new pop-up window similar to the one shown below. For series name choose the cell $E$1 (the cell that contain the name ‘Bad Loans’) and for series values select cells $E$2 to $E$14 i.e. the values of ‘Bad Rate’  for all the ‘Age Groups’.

Slide5

 

Once you will click OK in the above Edit Series pop-up window, you will get additional bars for ‘Bad Loans’. Notice, these bars are really tiny in comparison to ‘Number of Loans’ bars.

Presentation4

 

Select any of these tiny bars for ‘Bad Rate’ by a mouse click. Then right click your mouse and choose Format Data Series…You will see a pop-up window similar to the one shown below.

Slide6

 

In the Plot Series On section choose Secondary Axis and press Close in the pop-up window. You will get a bar plot similar to the one shown below. Notice the secondary Y Axis at the right side of the plot with ‘Bad Rate’. Remember, I have re-formatted the default Excel plot so your plot will have different appearance. Now we are left with just one final thing.
Presentation2

 

We need to now convert the above orange bars for ‘Bad Rate’ to a line plot. This is really simple, click on the orange ‘Bad Rate’ bars and convert them to line by choosing Line in Charts from Insert tabSlide2

Presentation3Great! We are done. You might appreciate how useful it is to present these two plots in one single chart with 2 Y axes or the secondary axis in Excel. In the above chart, it is much easier to see where you have thinner data and modify your bad rates accordingly. Now, you could easily create coarse classes as discussed in the banking case study.

Sign-off Note

Hope you had fun performing the above task on Excel. In the next article we will do the exactly same thing on R. See you soon!

The following are the parts of the banking case study for risk scoring
- Part 1: Data visualization for scoring
- Part 2: Creating ratio variables for better scoring
- Part 3: Logistic regression
- Part 4: Information Value and Weights of Evidence
- Part 5: Reject inference
- Part 6: Population stability index for scorecard monitoring
  • Share
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Related

Posted in Analytics Labs, Risk Analytics | Tags: Banking and Insurance Analytics, Business Analytics, Predictive Analytics, Roopam Upadhyay |
« In Conversation with Eric Siegel: Author ‘Predictive Analytics’
Data Visualization – Banking Case Lab: 2 Axes Risk Plots in R »

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog

Provide your email address to receive notifications of new posts

Must Read

Career in Data Science - Interview Preparation - Best Practices

Free Books - Machine Learning - Data Science - Artificial Intelligence

Case-Studies

- Marketing Campaign Management - Revenue Estimation & Optimization

Customer Segmentation - Cluster Analysis - Segment wise Business Strategy

- Risk Management - Credit Scorecards

- Sales Forecasting - Time Series Models

Credit

I must thank my wife, Swati Patankar, for being the editor of this blog.

Pages

  • Blog-Navigation
  • Art
  • About
  • Contact
© Roopam Upadhyay
  • Blog-Navigation
  • Art
  • About
  • Contact
 

Loading Comments...