Share with your network!

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!

#Financial Modeling Using Excel

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

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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:

#Financial Modeling Using Excel

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)

#Financial Modeling Using Excel

So if you want to play with the numbers and create something new, please download the file from this link!

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.

To read more about Financial Modeling, just follow the link!