Questions? We have answers.

How to create a relative risk grid

  • Updated

Summary

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


Configuration details

To access the Reports tab in a general ledger analysis, you must have run a successful analysis.


Go to the Reports tab

  1. Select the MindBridge icon in the sidebar to go to the Organizations page.
  2. Select View in line with the desired organization, or click anywhere in the organization row to go to the Engagements page.
  3. Select View in line with the desired engagement, or click anywhere in the engagement row to go to the Data page.
  4. Select View Analysis on the desired analysis, or use the sidebar to open the Analyze menu, then select the name of the analysis you wish to see. You will go to the Analyze page.
  5. Select the Reports tab.

Tip: If you are already in an analysis, simply use the sidebar to open the Analyze menu, then select the name of the analysis you want to see and go to 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.

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


Export the data From MindBridge

  1.  Find the trended account details report beneath the Reports section.
  2. Export the document.
    The file will export to your computer's default  download location.


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).
    84578e17-61e4-4827-aa9e-29f05d75dd5e.png

  2. Select row 10 to highlight the entire row.
    9b0e68de-5b9a-4113-9241-123d5a375a78.png

  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.
    7fb0b197-f73b-4de7-ba99-ef2f289d97da.png

  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.
    26b094fb-e53d-46cf-9e30-7c80a324f171.png

  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
    5c6e0cc9-7c09-4368-a006-32ccd8b5a8df.png
 

Create a pivot table

  1. Select all of the data in the new sheet. 
    1cc2e778-f4aa-44f3-acd5-a8f52069c7bf.png

  2. Once he data is selected, select PivotTable in the ribbon’s Insert tab.
    A pop-up window appears on your screen.
    7fa16e23-dfb8-40ad-a9ed-23507d0287a5.png

  3. Ensure that New Worksheet is selected, then select OK.
    You will go to a new blank sheet.
    f9abe83b-75f4-4d8f-9944-20874bd99ce6.png

  4.  Select anywhere in the PivotTable image to begin.
    The PivotTable Fields section appears on your screen.
    92548e87-b94d-42a5-acc7-180e82adf568.png

  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”)
      image_-_2021-01-08T161500.870.png

  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.
    d5cb5472-07ac-47b8-9d72-5659bbb0e6d2.png

  8. Select Average from the list.
  9. Select OK.
    3fcd4dee-abd4-4f9e-bf3a-da650d92e56c.png

  10. When you are satisfied, Update the table.
    image_-_2021-01-08T161547.491.png

  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).
    a42b071a-743b-4921-9077-7366524a7dee.png

  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.
    24c73022-bf4c-4293-9572-45c5eb3d9e6b.png

  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.
    cf87796c-224a-4fef-8adb-6e2a5e548c1f.png
  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. c49c1e63-2619-4bcf-8e6b-68e065eae95f.png
 

Remove the grand totals 

  1. Select any cell that contains data.
    This will allow you to use the Design tab in the ribbon.ff949c9d-352c-4ebe-82bd-058f9477474c.png

  2. Open the Design tab.
  3. Select the Grand Totals menu and select Off for Rows and Columns.0621540c-8a5c-425f-a48d-7a24c6eb6808.png

  4. The result is 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?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.

Was this article helpful?