June 10, 2013
We all are experimental by nature. And at times, I feel its good that we don’t have optional or alternative ways of doing few things. Options and alternatives available with us, at times force us to cross check. And surprisingly cross checking throws light on some of the facts we would have otherwise ignored.
This article explores lesser known but useful facts about some of the excel functions and charts.
Many a times, in financial modeling, you will come across raw data that needs to be grouped into various bins. Lets say credit scores of individuals are given to you as below:
How are you going to do this excel model? Has your mind already started working?
Think about how many nests you will have to build?
AND function inside IF function? It’s definitely an improvement over the nested IF function but still the effort required is enormous.
Have you come across a function called ROUNDUP? Have you ever noticed how powerful this function is?
Excel definition of ROUNDUP – Rounds a number up, away from 0 (zero).
Syntax is: ROUNDUP(number,num_digits)
Please examine the bar chart below. Can you guess the values corresponding to variables A, B, C, D & E from the chart below?
Suppose a data in following format is given to you. You are required to make a simple bar chart plotting years on x-axis and Sales on y-axis.
If you blindly select the data and plot the 2D bar chart in excel, this is what you will get:
Exhibit 3: An Attempt to Plot Data on x-axis directly
Surprisingly excel has displayed the years also by way of a bar. You can now do jugglery to rectify the graph. But whats the problem with excel?
E can fix this easily in a following manner
Make the column you want to be read as x-axis heading less. Excel will automatically make it x-axis in the chart.
Suppose you have to plot the operating expenses as a pie chart as shown in the example below. If you simply select the data and do a pie chart, this is what you will get:
Exhibit 4: An Ordinary Pie Chart
Slices corresponding to Small expenses like Rent, Electricity, Stationery, Telephone & Internet and Housekeeping are so thin that they are hard to distinguish.
When we encounter such a situation, we should try to enhance the quality of our graph by grouping the smaller expenses in head of Others and then splitting them into another pie chart. A pie of pie chart in excel does exactly this. If we choose that graph, see the output below. Once you obtain the pie of pie chart, right click on it and say format data series. There you will see an option to choose as many values from the bottom to group into Others. Excel groups the values from bottom by default. Hence, if you intend to use this function, you need to arrange lower value heads towards the bottom in your excel table.
Exhibit 5: A Pie of Pie Chart
This series gives you a flavor of useful tips of some of excels functions and charts which are extensively used in financial modeling. To learn more about financial modeling consider joining our Financial Modeling course.
I have created a template for you, where the subheadings are given and you have to link the model to get right numbers or charts! You can download the same from here. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
For any queries regarding financial modeling, feel free to put the comments on the blog.