Summary

Learn about the relative risk grid and follow the steps below to create one in Excel using data found in MindBridge.

• Step 1: Export the data From MindBridge
• Step 2: Manipulate the data in Excel
• Step 3: Create a pivot table
• Step 4: Remove the grand totals 

Go to the Reports tab

1. Select the icon in the sidebar.
   You will go to the Organizations page.
2. Select View in line with the desired organization, or click anywhere in the organization row.
   You will go to the Engagements page.
3. Select View in line with the desired engagement, or click anywhere in the engagement row.
   You will go to the Data page.
4. Select View Analysis on the desired analysis.
   You will go to your default analysis landing page.
5. Go the Reports tab.

Overview

The data found in the trended account detail report can be used to create a relative risk grid.

The relative risk grid breaks down the timing and risk of your client’s transactions by allowing you to view the risk level of their accounts over every month of a fiscal year. It combines the 2 graphs found on the Risk overview dashboard into a single working paper document.

Below is an example of a relative risk grid in Excel. Keep reading to learn how to create one.

Step 1: Export the data From MindBridge

1. Find the trended account details report beneath the Reports section.
2. Export the document.
   The file will be exported to your default download location.

Step 2: Manipulate the data in Excel

1. Open the report you exported and navigate to the tab containing data for the most recent year (in the example below, this is Year 5).
   
   
   
2. Select row 10 to highlight the entire row.
   
   
   
3. With row 10 selected, scroll to the last entry in the sheet (in the example below, this is row 1942).
4. Press and hold SHIFT on your keyboard and select the final row header to highlight the rows between row 10 and the final row.
   
   
   
5. With all of the data selected, right click your mouse and select Copy. Alternatively, press Ctrl+C on your keyboard to copy the data.
6. Once the data has been copied, select the plus icon (+) to the right of the worksheet tabs to create a new tab.
   
   
   
7. You should be taken to the new tab automatically. If not, select the tab to go to it.
8. With the new tab open, right click your mouse and select Paste as Values. Alternatively, press Ctrl+V on your keyboard to paste the data into the new worksheet.
   The data will be pasted as values only. 
   
   
   

Step 3: Create a pivot table

1. Select all of the data in the new sheet.
   
   
   
2. Once the data is selected, select PivotTable in the ribbon’s Insert tab.
   A pop-up window appears on your screen.
   
   
   
3. Ensure that New Worksheet is selected, then select OK.
   You will go to a new blank sheet.
   
   
   
4. Select anywhere in the PivotTable image to begin.
   The PivotTable Fields section appears on your screen.
   
   
   
5. Ensure the following columns are checked in the list area of the PivotTable section. These columns will appear below the checklist.
   • Description
   • Period End
   • Average Risk (ensure this is a “sum”)
6. Drag and drop each field to the corresponding sections:
   • Columns = Period End
   • Rows = Description
   • Values = Average Risk (ensure this is a “sum”)
     
     
     
7. When adding Average Risk to the Values section, it may appear as a Sum of Average Risk. To change this, select Sum of Average Risk and select Value Field Settings from the menu.
   The Value Fields Settings form appears.
   
   
   
8. Select Average from the list.
9. Select OK.
   
   
   
10. When you are satisfied, Update the table.
    
    
    
11. Once the pivot table is built, highlight the risk scores by selecting the top-left cell in the data (in the example below, this is cell B5).
12. Press and hold SHIFT on your keyboard then select the bottom-right cell in the data (in the example below, this is cell m26).
    
    
    
13. Select Conditional Formatting in the ribbon's Home tab.
14. Open the Color Scales menu and select the second option.
15. The data will be color-coded by risk, with red being high risk and green being low risk.
    
    
    
16. To change the data into percentages, select cells containing data using the same method as above.
17. In the Home ribbon, find the Numbers section and select Percentage.
    The data will be displayed in percentages.
    
    
    
18. To simplify the dates, select row 4.
19. In the Home ribbon, find the Numbers section and select Short Date.
    The dates will now follow the “short date” format.
    
    

Step 4: Remove the grand totals 

1. Select any cell that contains data.
   This will allow you to use the Design tab in the ribbon.
   
   
2. Open the Design tab.
3. Select the Grand Totals menu and select Off for Rows and Columns.
   
   
4. The result is a relative risk grid containing the risk level of each account over the course of a year.
   
   

Anything else on your mind? Chat with us or submit a request for further assistance.