In my last post I talked about how important it is for boards to pay attention to financial ratios and I provided a dashboard to keep track of the most important ratios. It is also important to follow the trends in these financial rations. Are they getting better or worse? To that end I am providing another dashboard called Ratio_Charts that board members can use to track the historic trends in the most important ratios, looking back three years, on a monthly basis. We want to look at the historic data side-by-side in order to detect possible seasonal changes. Each ratio has its own chart (in this dashboard 12 charts), with all three years are represented including the current year, up to the current month.
I came up with 12 small charts that look like the one below representing the Delinquency Ratio. The oldest data in this example is for the year 2013 (light beveled gray) and is in the background, while 2014 (beveled green) is in front of 2013. The green is 65% transparent, allowing 2013 to still be seen. The current data (2015) is a bright red line.
Below you can see what all twelve charts on the dashboard look like. If PowerPoint is being used to display the charts, it might be easier to read if it is broken up into two pages of six charts each.
First pick a years in the “Current Year” cell. The year chosen will need at least two previous years of data in the “Ratio_History” sheet.
Each chart has a number (1-12). The description column cells are drop down boxes that are linked to the Ratio_History sheet. Next you can adjust the Y axis scale for the highest and lowest numbers for each chart. Over to the far right are the actual highs and lows for the Y axis, to help you determine which scale to select.
Next, you have the choice of formatting each chart either as a percent or dollars. After new data are entered, click the appropriate “Update” button to update that chart.
The Ratio_History sheet will accept data in any yellow cell. When adding data for the first time, start with a 1/31/???? date. The dates will automatically expand to the end of each month from that point on. Data can formatted either as a percentage or dollar amounts.