Questions? We have answers.

Creating a Relative Risk Grid

Article author
Jonathon Plowman-Samson
  • Updated

Summary

The article talks about the Relative risk grid and why do we use that.


Overview

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:

 

a3c936fe-4dba-4874-b29f-68acacf899e8.png
299211d7-1c93-4457-9555-29221aa71e6d.png

Here's an example of a Relative Risk Grid in Excel, which this article will teach you how to create:

 

fd2cb18f-e0ae-40bb-8bb7-9c8d915ecd12.png

Exporting the Data From MindBridge

 

From the Reports tab, scroll to down the page and click Download within the Trended Account Details tile. 

 

aa7a793b-74e0-4d58-8e30-63d15875da09.png

The Trended Account Details report will begin downloading. 

 

7a12b38e-a3c9-4554-98b7-82ec643b0ad5.png

The file will download to your browser’s default download location.

 

875ff6b7-b455-4522-a8cf-69c494aa043b.png

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). 

 

84578e17-61e4-4827-aa9e-29f05d75dd5e.png

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. 

 

9b0e68de-5b9a-4113-9241-123d5a375a78.png

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. 

 

7fb0b197-f73b-4de7-ba99-ef2f289d97da.png

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. 

 

27de4d64-90e5-472b-b92d-5fa901c22ec2.png

Once the data has been copied, click the plus button in the tab area at the to create a new tab.

 

26b094fb-e53d-46cf-9e30-7c80a324f171.png

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. 

 

5b174f54-2527-46de-878b-444153227e10.png

All of your data will now be pasted as values only. 

 

5c6e0cc9-7c09-4368-a006-32ccd8b5a8df.png

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. 

 

1cc2e778-f4aa-44f3-acd5-a8f52069c7bf.png

Once all of the data is selected, click PivotTable in the ribbon’s Insert tab. 

 

7fa16e23-dfb8-40ad-a9ed-23507d0287a5.png

The Create PivotTable Form will appear on your screen. Ensure that New Worksheet is selected and click OK. 

 

f9abe83b-75f4-4d8f-9944-20874bd99ce6.png

You will then be taken to a new blank sheet. Click anywhere in the PivotTable image to begin. 

 

70676e3d-51b5-4d78-ab7b-44e0d79113cf.png

The PivotTable Fields section will appear on your screen. 

 

92548e87-b94d-42a5-acc7-180e82adf568.png

Ensure the following columns are checked in the list area of the PivotTable section: 

 

• Period End

 

• Description

 

• Average Risk (be sure this is a “sum”)

 

d48a076c-a5cc-4684-97d5-3cbb9d01b8aa.png

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”) 

 

image_-_2021-01-08T161500.870.png

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. 

 

d5cb5472-07ac-47b8-9d72-5659bbb0e6d2.png

The Value Fields Settings form will appear on your screen. Select Average from the list and click OK. 

 

3fcd4dee-abd4-4f9e-bf3a-da650d92e56c.png

Click Update to confirm. 

 

image_-_2021-01-08T161547.491.png

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).

 

a42b071a-743b-4921-9077-7366524a7dee.png

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). 

 

2153a34a-e22d-463c-bf6a-07178991cca7.png

Your data will now be color-coded by risk (red being high risk).

 

24c73022-bf4c-4293-9572-45c5eb3d9e6b.png

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.

 

cf87796c-224a-4fef-8adb-6e2a5e548c1f.png

All of your data will now display percentages. 

 

25de9ee5-7ebe-4703-b279-a83421f38769.png

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. 

 

c49c1e63-2619-4bcf-8e6b-68e065eae95f.png

Your dates will now follow the “short date” format. 

 

53fe58d6-45f9-48ca-bead-62a2545b9f41.png

Removing the Grand Totals 

Begin by selecting any cell that contains data. This will allow you to click the Design tab in the ribbon. 

 

ff949c9d-352c-4ebe-82bd-058f9477474c.png

From the Design tab, click the Grand Totals drop-down and select Off for Rows and Columns. 

 

0621540c-8a5c-425f-a48d-7a24c6eb6808.png

You will now have a Relative Risk Grid containing the risk level of each account over the course of a year. 

 

4c5ae3f3-46b6-49af-b8d3-a62d1248c1af.png

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.

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.