I will start off by saying that this template only works with Excel 2013 or higher, because we need to use slicers with a table. This dashboard template might be of interest to the CFO or financial analyst that prepares the monthly presentations for the board, finance or ALCO (Asset/Liability Management ) committees. Loans and deposits make up the bulk of a financial firms balance sheet and the change in those balances compared to the previous month and compared to the previous year are of upmost interest.
One of the challenges for the CFO is how deep the analysis for loan and deposit growth should go. For the board, it might be at a higher level than for committee meetings of senior management. This template, using slicers, allows for only two charts to go as deep into the loans and deposits as necessary. Lets see how it works:
Start by picking “Loans” on the far left slicer. The first chart shows growth from the end of last year, through the current month (June) for all loans. The other chart shows the growth in all loans compared to the previous month.
Now we pick “Real Estate” in the second slicer and the charts show changes for all real estate loans.
Now we have a choice of ether Firsts or Seconds. Pick Seconds:
I think this is enough to get the idea. With only two charts, we can dig as deep as needed to show loan or deposit growth. The “Start Over” button” resents the slicers back to all loans and deposits.
Here is a partial view of the data. The data is a table. The first 5 columns are the “ChartLevels”, 1 through 5. The Level 1 column can only be a loan or deposit. If you need to go deeper than 5 levels, insert a column after “ChartLevel5” and add a slicer to the “LoansAndDeposits” sheet. Enter the last month you have data (cell D3). Enter the date for the end of last year (Cell G4) and the balances for the end of last year. The dates will automatically populate the rest of the year. Then at the end of each month, update the balances.
For the data for the charts I used the AGGREGATE function using 9 for sum and 5 to ignore hidden rows.