November 26, 2013

A former student of EduPristine who underwent the Financial Modeling Training session, which includes Excel Modeling, asked me a very relevant question. Relevant because of the implications of the same as it can be used in various walks of one’s professional life.

Take a look!

Now that you have read this student’s question, we can state that what is given below is the sample he wanted to create:

Let’s notch the heat up a bit! To chalk out a systematic plan on how to design the map, I took some random data and wrote down the steps to see how it will be plotted. Given below is the data that I took:

Let’s have a little more fun! If I create the scatter plot from the two variables, I will get:

However, this chart seems incomplete and cluttered. I would want to ideally add one vertical and one horizontal line to turn it into quadrants. So, I created four columns to create these two lines as following:

So, on our current chart if I right click and add the above data as two new series, I will get the following graph:

We are on the right track! But still it is cluttered. So, first I will delete the legend and the gridlines, and I will have the following result:

It is infinitely cleaner now. But if I notice carefully, the axes are going off-limits. So, to control them I will select the individual axis and will limit them (manually) to 1 in the case of x-axis, i.e, Impact on Satisfaction and 100 in the case of y-axis, i.e, Component Score respectively. I also see that the vertical and horizontal lines have scattered data points. So I will change them to the scatter lines and following is the result after adjustment:

Much better! But I want to make these lines dotted and thinner, so I will select them and go for “Format Series”. I will select width as 1pt and line style as “Dash”. I also want to see the axis title, which I can select under “Chart Tools – Layout – Axis Title”. I will add the boundary too, so that it looks like a box. After these adjustments, following is the result:

Now, I am left with one more thing. I need to label the data points (A, B, C, etc.) Now there is a tool available as an excel add-in which can be installed. You can download the add-in from this link. After installing you will see the excel ribbon as following:

Now within “XY Chart Labels” you can go to “Add labels” and select the labels. Then boom! You get the following as your result: (Do not forget to add Text boxes to name the quadrants)

The Financial Modeling course teaches you the art of constructing an integrated financial model from scratch. You will be able to build models that are robust and will provide you with flexible projections that can be used to thoroughly analyze a company from multiple standpoints: Planning, Investment, Financing and Valuation.

EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.