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.
I am having trouble using this functionality as when the check box selection (FALSE), it will show zero which then shows my data as zero on the graph rather than not showing anything. Is this the intent of this or is there a way to not show the line at all?
Hi Judy Green, the most common method to remove unwanted data in the chart (i.e. data outside the calculation period or data that has been switched off) is to introduce a #N/A in the model. As best practice, we would avoid introducing any form of error in the output at all costs, be it #REF!, #N/A, or #VALUE!. However, for charting purposes, if the situation calls for it, using #N/A is required as long as these #N/As do not appear in the summary output sheet and is strictly limited to data used for charting. You might consider a further step to grey out #N/A values using conditional formatting. Excel will not plot any data points if it contains #N/A for that particular data point.
Changing the formula to =IF(CheckBox = TRUE, Value, NA()) would remove the data points altogether but note that the legend for that particular series will still appear in the chart.
cet article est interessant pouriez vous m’envoyer un model comme example
merci pour votre aide