The article talks about the Relative risk grid and why do we use that.
The Relative Risk Grid (or risk by account) breaks down the timing and risk of your client’s transactions by allowing you to view the risk level of your client’s accounts for every month of a fiscal year. It combines the two graphs found on the Risk Overview dashboard into a single working paper document.
The two grids that are combined to create a Relative Risk Grid are the Risk breakdown by Month and Risk by Account:
Here's an example of a Relative Risk Grid in Excel, which this article will teach you how to create:
Exporting the Data From MindBridge
From the Reports tab, scroll to down the page and click Download within the Trended Account Details tile.
The Trended Account Details report will begin downloading.
The file will download to your browser’s default download location.
Manipulating the Data in Excel
Once you've downloaded and opened the report, and navigate to the tab containing data for the most recent year (In this example, Year 5).
Click row 10 to select the entire row. Once you make the selection, the row will be highlighted with green borders, indicating it’s been selected.
Once row 10 is selected, scroll to the last entry in the sheet (in this example, row 1942). While holding the SHIFT button, click on the final row header to select all of the rows in between row 10 and the final row. Once you make the selection, the rows will be highlighted with green borders, indicating that the data has been selected.
With all of the data selected, right click your mouse and select Copy. Alternatively, you can use the Ctrl + C keys to copy the data.
Once the data has been copied, click the plus button in the tab area at the to create a new tab.
You should be taken to the new tab automatically. (if not, click the tab to make sure that the tab is opened). While the new tab is open, right click your mouse and select Paste as Values.
All of your data will now be pasted as values only.
Creating a Pivot Table
Begin by selecting all of the data in your new sheet. You can use the “row selection” method outlined above (select the first and then the last row while holding shift), or click the leftmost column header to select all the data in the sheet.
Once all of the data is selected, click PivotTable in the ribbon’s Insert tab.
The Create PivotTable Form will appear on your screen. Ensure that New Worksheet is selected and click OK.
You will then be taken to a new blank sheet. Click anywhere in the PivotTable image to begin.
The PivotTable Fields section will appear on your screen.
Ensure the following columns are checked in the list area of the PivotTable section:
• Period End
• Average Risk (be sure this is a “sum”)
These columns will now be in the lower section. Use your mouse to drag each field to the corresponding sections:
• Columns = Period End
• Rows = Description
• Values = Average Risk (be sure this is a “sum”)
When adding Average Risk to the Values section, it may show up as a Sum of Average Risk. To change this, click Sum of Average Risk and select Value Field Settings.
The Value Fields Settings form will appear on your screen. Select Average from the list and click OK.
Click Update to confirm.
Once the Pivot Table is built, highlight all the risk scores by clicking the top-left cell in the data (in this case, cell B5) and then, while holding shift, clicking the bottom-right cell in the data (in this case, cell m26).
Click Conditional Formatting in the ribbon's Home tab and click the second option in Color Scales, which will color-code your cells by risk level (red being highest).
Your data will now be color-coded by risk (red being high risk).
To change your data into percentages, select cells containing data using the same method as above, and select Percentage in the numbers section’s drop-down on the Home ribbon.
All of your data will now display percentages.
To simplify the Relative Risk Grid’s dates, Select row 4 and chose Short Date in the Numbers section’s drop-down on the Home ribbon.
Your dates will now follow the “short date” format.
Removing the Grand Totals
Begin by selecting any cell that contains data. This will allow you to click the Design tab in the ribbon.
From the Design tab, click the Grand Totals drop-down and select Off for Rows and Columns.
You will now have a Relative Risk Grid containing the risk level of each account over the course of a year.
Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.