Skills level: Intermediate Charts are a great way of displaying data visually. The reader is able to quickly spot trends and locate out-of-the-ordinary results. I often need to work with rolling year on year growth, and I assumed that there was a quick fix in Excel to include a YOY growth figure in a chart, but turns out you need to be quite creative to get the desired result. While it is quite easy to calculate the growth rate with a few columns and formula’s, you need to know some charting skills to bring it into a chart. Below are turnover figures per month from January 2014 to Aug 2016 for a business. As the financial manager, it is important to know how turnover increases over time, to know how well the business is doing. The year on year growth can only be calculated after the business has been operating for at least 24 months, since you need total T/O for one year to compare with the total turnover from a previous year to get a YOY growth rate. Firstly, start by calculating a rolling 12-month total. That means, adding the T/O figures from Jan 2014 to Dec 2014, then Feb 2014 to Jan 2015, and so on. Simply enter a sum function, next to the 12’th turnover figure (the figure for Dec 2014 in row 13) like below: Then drag the formula down to fill the rest of the fields. Because the reference is relative, the sum range will change to the next 12-month period (rolled forward by 1 month) by excluding January 2014 and including Jan 2015 as you copy the formula down. As soon as you have your rolling annual total figures, you can use the next helper column to calculate the YOY growth. Cell C13 now contains the total T/O for 2014 (Jan – Dec 2014), and cell C25 contains the total T/O for 2015 (Jan – Dec 2015). Now you can see how you can calculate the growth in turnover from 2014 to 2015. In the next column, insert the formula in Cell D25 to calculate the first growth figure (only possible after 24 months) by using one of the following formulas (I prefer the first, but there is more than one way to calculate growth) =C25/C13-1 =(C25-C13)/C13 The result may be displayed as a decimal. If so, you need to change the formatting in Column D to percentage (%). Drag the formula down to calculate a rolling YOY growth rate. Your data is now ready to be charted. The best way to illustrate this data visually is to use a bar chart to show turnover per month, and then a line graph to show the Year on Year growth rate. Column C is only a helper column, used to calculate the year on year growth in Column D. It is not necessary to display this data in you chart, you can therefore hide it. You can also choose not to use a helper column and instead adapt your formula in column D to include the formula in Column C. It would look something like this: =SUM(B14:B25)/SUM(B2:B13)-1 So, for your chart, you only need the dates, turnover figures and YOY growth. If you select this data, and click on Recommended Charts, the second option is exactly what we are going for. This is quite a handy short cut, but I am going to show you the steps to build a chart from scratch so that you can see how you can get a chart to do what you want it to do. So instead of selecting this chart, select All Charts in the adjacent tab and Combo at the bottom of the list of chart types. Since Excel is so intelligent, it predicts what type of chart would be suitable for certain types of data. In this case, it defaults to a Clustered Column Chart to display the turnover figures and a Line Chart to display the YOY growth data. However, the above chart only uses 1 axis, which means that the turnover data (in the 100 thousand) and the percentage data (all below 1 in value) is plotted using the same axis to the left. That is why you will see a feint little grey line at the bottom right of the graph. That is your line displaying the percentage data. Not very useful, is it? The percentage data therefore needs to be plotted on a secondary axis; an axis to the right which is used to display a secondary set of data using a different data series. It is used when the data series uses a range of values of a different type or of a different range of values than the data represented on the primary axis. Tick the box next to YOY Growth placing the values on a secondary axis. You will notice how the little grey line moves up and has its own axis to the right. Select OK to create your chart. You can make various changes to make your graph look good. The rest of this blog will show you how to change a few of these settings. Firstly, you can give your chart a title if you like, simply click on the chart title to edit. You can change the bar size by adjusting the width between the columns (Decrease the gap width to increase the bar size). You can also change the colour of the bar chart. Simply right click on the bars in your chart once (the first right click will select all bars, the second will select only 1 bar at a time. Be sure not to click too many times), select Format data series to open the chart settings to the right. Change the width, and then select the “Fill & line” tab (the icon with the little paint can) to change the colour of the bars. If you prefer not to have gridlines in your chart, right click on any one of the gridlines (once) to highlight them all and then press delete on your keyboard. Alternatively, while your chart is selected, select the green plus sign to the top right outside of your chart (Chart Elements button) and untick the box next to gridlines. I prefer to show the YOY growth data as beacons instead of a line. Select the line in your chart to open the Format Data Series options again. Select No Line to remove the line between the data points. Select Marker, and then Marker Options. Tick the Built-in option and choose one of the shapes to display the data points. You can change the size of it if you prefer. If you want to change the colour of your beacons, select a different colour under Fill. The shape automatically has a border which you can remove if you like. To add data labels to the beacons, select it and click on Chart Elements again (green plus sign). Hover over the Data Labels option to see the dropdown of further options. Select Above to display the data label above each individual data beacon. Click on More Options lower down the menu if you want to change the colour of the text or make any other changes. To add data labels to the bar chart, select the bars, click on Chart Elements again (same process as above), and hover over Data labels. You will notice that the options are slightly different for a bar chart than that of a line chart. In this example, I will place the labels inside the base of the bars (Inside Base). Notice however how the text is not very clear against the dark background, and how it overlaps. Firstly, change the colour of the text to white to display it clearly within the dark bars by clicking on More Options lower down on the list, then Text Options and select white from the colour palette. Then, to rotate the text, select the Text Box icon (last tab under Text Options) and then select the desired text direction. And there you have it. These charting skills will help you alot when building all kinds of other charts too. As always, there are a few different ways to achieve the same result in Excel. If you have a different method to do the same thing, please feel free to share!
0 Comments
Leave a Reply. |
Carine HoughHi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog. Categories
All
|