Do plenty of sheets and a lot of charts in a financial model turn you off from learning the concepts used in the model?
It definitely turns me off whenever I see such a model. Grappling with this commonly found problem, I have come up with a simple solution of consolidating the maximum in the minimum.
The purpose of most models is to make the tedious task of analyzing an issue simple and comprehensible. This task is normally done using various charts for different aspects of the issue.
Poor Practice: Making 4-5 charts for ratio analysis. Making a different chart for profitability, solvency, activity and return on investment ratios.
Good Practice: Consolidating all the ratios in one chart and giving the user an option to see and compare various ratios.
Now lets get down to business and see how we can build an all-in-one chart to increase the usability of our model.
Step 1: To assist with this I have developed a model with dummy numbers of various ratios. Open the spread sheet called Exercise Book:
Step 2: Link all the ratios to the sheet where you want to make the chart:
Step 3: Make a chart (Scatter Plot) selecting the profitability ratios first:
- After getting a chart, highlight the chart that shows the select data button in the design button.
- Press the select data button which will give a pop-up window from where you can add the remaining ratios separated by a comma.
Step 4: Now all the ratios are there on the chart and we want to give our viewers an option to choose the data they want to see on the chart.
- For this we need the check box form control in the developer tab.
- Make a check box and link the output of the check box to some cell.
If the box is checked then the cell will show TRUE otherwise FALSE.
Step 5: Linking the output of check box to the ratios.
- Depending on the check box selection (TRUE), it will show ratios on the chart otherwise all the ratios will be zero implying nothing on the chart.
- Similarly doing it for the other ratios by making a similar check box and linking it to the respective cells.
Step 6: All the technical stuff is done and it is time to enhance the presentation of the chart.
- Increase the size of the chart
- Since activity numbers are always in days and profitability etc are in %, we need separate “Y” axis for both these numbers
- Select the ratio→ Right Click→ Format Data Series→ Plot Series On→ Secondary Axis
- Till now all the chart types are Histograms. Select the chart and change it to different types of line, dot charts etc.
- Done, we now have a wonderful looking chart that contains the data hidden inside its buttons.
Beware: Looking at more than two kinds of ratio (profitability & activity simultaneously) can reduce the comparability of the ratios.