Creating a RAG (Red, Amber, Green) Score in Account Reconciliation

Introduction

This 30-minute tutorial shows you how to create a calculated attribute, a standard attribute, and a custom rule to identify a RAG (Red, Amber, Green) score on a reconciliation.

Background

A RAG score in accounting provides a simple yet effective visual assessment of various financial aspects, offering at-a-glance insights into performance and risk. RAG scores can be based off a company's reconciliation policy to highlight areas requiring immediate attention (Red), those needing monitoring (Amber), and those performing satisfactorily (Green).

You can use RAG scores with calculated attributes to ensure that they capture all necessary information. For instance, if a RAG score is red, a calculated attribute can reference the red score and require a calculated attribute to be filled out prior to submission.

The Preparer can manually update RAG scores when they create and place a standard attribute on the transactions in the reconciliation.

You can use RAG scores in rules to ensure that certain criteria are met before submitting the reconciliation. For instance, you can create a Prevent Submission rule when the RAG score is red to ensure that the reconciliation includes a completed Action Plan.

Prerequisites

Before starting this tutorial, you should have:

A working knowledge about Account Reconciliation
Access to Administrator-based functionalities in Account Reconciliation

Creating a standard attribute

In Account Reconciliation, you can create a custom attribute by completing the following steps:

From the Account Reconciliation home page, click the Application card, and then select Configuration.

Click the Attributes tab.

Click (New) to create an attribute.

Give the new attribute a name, select List for Type, and click the List Values tab.

On the List Values tab, click (Add) to create a new entry.

Type Green, and then create two more lines with Amber and Red as your values. Click OK when finished.

Creating a calculated attribute

In Account Reconciliation, you can create a calculated attribute by completing the following steps:

From the Attributes tab, click (New) to create a new attribute.

In the Properties tab, enter an attribute name, select Text as Type, and select the Calculation checkbox.

In the Calculation tab, select Conditional as the Calculation Type.

In Calculation Definition, select the If line, and then click (Edit Condition) to edit it.

Note:

You can now create an IF_THEN_ELSE statement by creating your own conditions from system and custom attributes.

To group the conditions together, click Create Condition Group.

To create conditions for this group, click Create Condition. You can create as many conditions as you want.

Create three conditions that must be met in order for the attribute to be given a Red value:

a. The transactions amount must be greater than 100,000

b. The age must be greater than 60

c. The action plan must be blank

Note:

Each time you create a condition, click Create Condition to add a new condition to your group.

The value type is constant and set the value as Red. Click OK when finished.

Click (Add) to add a new If statement.

Create an Amber criteria by following the steps 5 through 7.

After creating the Red and Amber criteria, create the Green criteria by using the Else statement. Select Else and then click (Edit Condition) to edit it.

Type Green as the value for your condition. This marks all transactions that don't fit the Red or Amber criteria as Green. Click OK.

Creating a rule

Rules need to be run periodically during the day to ensure that the RecRAG is updated with the latest information on the reconciliation. This can be done manually or by using the EPM Automate importReconciliationAttributes command with Rules=SET_ATTR_VAL. If the rules are not run, and a preparer submits the reconciliation, the RecRAG score will not be updated.

Rules are built in order to set the reconciliation's RecRAG based on the reconciliation's transaction LineRAGs. The order of colors in the rule is important. Red is always the last rule. So, if a red color appears in your set of items, the RecRAG is red. For instance, if there are three transactions and all LineRAGs are green, then the RecRAG is green. But, if there are two green LineRAGs and one red LineRAG, the third rule is run last and applies a RecRAG of red since one of those transactions has a red LineRAG.

In Account Reconciliation, you can create a rule by completing the following steps:

From Configuration, click the Formats.

Create a new format by clicking (New), or select an existing format and click (Edit) to apply a new rule.

Select one of the transaction tabs in order to add the LineRAG attribute to the Transaction Detail section.

Add the newly created LineRAG attribute to the Transaction Detail by clicking (New).

Select the LineRAG attribute. Click OK when done.

Click the Attributes tab and then click (New) to add an attribute.

Select your RecRAG attribute from the drop-down list and then click OK.

Click the Rules tab and click (New) to create a new rule.

Select the Set Attribute Value rule and then set the following values:

Source: Profile

Attribute: RecRAG

Value: Green

Run When: Before Profile is copied to period

Click Create Condition and select the following values:

Source: Transaction

Period: Currently Displayed Period

Attribute: LineRAG

Operator: Equals